Unplanned
Last Updated: 18 Feb 2021 12:16 by ADMIN
Tamas
Created on: 31 Jul 2020 02:50
Category: Grid
Type: Feature Request
5
Excel export should work with columns whose width is not set in pixels

Using the Excel Export for Grid creates the Excel file, but on opening it, the columns that contain data are hidden. Unless I unhide the columns, the sprceadsheet looks empty.

---
ADMIN EDIT:
One idea to go about this is to expose an event that would allow users to modify the column width values before they get sent for export. The benefit of this approach is that if the columns are resized, the user will receive their current size in the specified unit e.g. rem, em or % and will be able to set the width in px.

Another idea is to default such column widths to some hardcoded value (say, 64px) or even the grid might try to calculate them (which can cause questionable results in stranger settings, but it is an idea - if an event gets exposed you will be able to do that in your application code).

---

8 comments
ADMIN
Lachezar Georgiev
Posted on: 18 Feb 2021 12:16

Hello Tamas,

We've decided to reopen this ticket and change it to a Feature Request. Extending the grid's excel export functionality to work with columns whose width isn't set in pixels is an ongoing internal discussion. I've edited the opener post with some ideas as to how this might work. Please note that we haven't fleshed out any concrete details yet, but we've agreed to reopen this item and track the demand for it. Based on the number of votes and other factors feature requests get included in our backlog and in future releases.

Regards,
Lachezar Georgiev
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

ADMIN
Marin Bratanov
Posted on: 05 Aug 2020 08:39

Hi Tamas,

Doing that would make it impossible to customize at a later stage when an event (or other approach) is implemented. The grid must not make such changes on its own, and when a way to customize the export is implemented, you will have a neater hook to do that than the workaround I posted.

 

Regards,
Marin Bratanov
Progress Telerik

Tamas
Posted on: 04 Aug 2020 21:47

Hi Marin,

Thanks for that suggestion. Yes, currently I am exporting grid data to file using the Spread Processing feature. It works fine. I just wanted to utilize the new Excel Export feature of the grid, in order to simplify the code a little. I did not expect that I have to abandon web development best practices in order to do so.

I am not sure what change of settings you are referring to. I simply said that if it is not possible to set the Excel spreadsheet column width (because it can't be calculated from "rem" units), then don't set it at all or set it to something other than zero.

Another possibility would be to have an option to the Excel Export to set columns using AutoFitWidth.

Thanks,
Tamas

ADMIN
Marin Bratanov
Posted on: 04 Aug 2020 09:02

Hi Tamas,

In a future version it is possible that an event before the export is exposed that would let you perform such customizations. The grid should not, on its own, change your settings. The export of the grid takes the settings you defined for the grid and uses them as much as possible. Another option you can consider is creating the file with your own code, you can see an example in my post from 13 Jan 2020  here.

 

Regards,
Marin Bratanov
Progress Telerik

Tamas
Posted on: 02 Aug 2020 21:55

Hi Marin,

Thanks for looking into this. It's too bad that using the Export Excel feature restricts the regular features that can be used for the grid. I understand that Excel doesn't understand em and rem units, but those are perfectly valid for CSS, so some kind of conversion would be useful/necessary.

It would be better if, at least, the export could set the spreadsheet column width to some default value, say 50 pixels, instead of 0. Then the user could just resize the column after opening the spreadsheet. The column width being set to 0 unfortunately hides the column, so the user might think that the spreadsheet is empty and the export didn't work.

Thanks,
Tamas

ADMIN
Marin Bratanov
Posted on: 02 Aug 2020 13:43

Hi Tamas,

The "rem" or "em" units (such as other valid CSS units like "vw") are not something that is valid for Excel, so it cannot read them and thus - the width of those columns becomes invalid. This is, however, not something the grid should touch, because it cannot know what your expected conversion would be.

With that said, I can recommend setting your column widths in pixels or percent.

Other options are:

  • Vote for and Follow a programmatic method to export so you can swap the widths with your code, export, then swap them back to those special units: https://feedback.telerik.com/blazor/1470774-trigger-grid-excel-export-from-code.
  • Try this workaround that relies on multithreading to achieve the same effect:
    the command button gets a click handler
    <GridCommandButton OnClick="@SetColWidth" Command="ExcelExport" Icon="@IconName.FileExcel">Export to Excel</GridCommandButton>
    and that handler does that work:
        string ColWidth { get; set; } = "10rem";
    
        async void SetColWidth()
        {
            ColWidth = "100px";
    
            // tweak this to be slightly lager then the typical time the exprot will take
            // the idea is to have the widths change, this method starts ticking and waiting
            // the multithreading allows the grid to export with the pixel widths
            // this timer ticks out and restores the desired widths
            // Note: this will not work for WASM, because it only has a single thread at the time of writing
            await Task.Delay(1000);
    
            ColWidth = "10rem";
            StateHasChanged();
        }
    on columns that use the variable for their width, something like this (based on my previous sample):
        <GridColumns>
            <GridColumn Field="@nameof(SampleData.ProductId)" Title="ID" Width="@ColWidth" />
            <GridColumn Field="@nameof(SampleData.ProductName)" Title="Product Name" Width="@ColWidth" />
            <GridColumn Field="@nameof(SampleData.UnitsInStock)" Title="In stock" />
            <GridColumn Field="@nameof(SampleData.Price)" Title="Unit Price" />
            <GridColumn Field="@nameof(SampleData.Discontinued)" Title="Discontinued" />
            <GridColumn Field="@nameof(SampleData.FirstReleaseDate)" Title="Release Date" />
        </GridColumns>

 

With all that said, I am marking this as something that needs a different approach as it is not a bug in the component, but a behavior of Excel and how web development cannot apply directly to it.

 

Regards,
Marin Bratanov
Progress Telerik

Tamas
Posted on: 01 Aug 2020 01:30

Hi Marin,

It looks like the width of each Excel column is determined by the Width of the GridColumn. However, if the Width is not specified in px, but in em or rem, the width of Excel column becomes zero.

<GridColumn Field="@nameof(SampleData.ProductId)" Title="ID" Width="7rem" />
<GridColumn Field="@nameof(SampleData.ProductName)" Title="Product Name" Width="20rem" />
<GridColumn Field="@nameof(SampleData.UnitsInStock)" Title="In stock" Width="7rem" />
<GridColumn Field="@nameof(SampleData.Price)" Title="Unit Price" Width="15rem" />
<GridColumn Field="@nameof(SampleData.Discontinued)" Title="Discontinued" Width="7rem" />
<GridColumn Field="@nameof(SampleData.FirstReleaseDate)" Title="Release Date" Width="20rem" />

ADMIN
Marin Bratanov
Posted on: 31 Jul 2020 07:43

Hi Tamas,

Can you reproduce this on our demo or with the code from our docs? I am asking, because I tested both and the file opens as expected for me. I am also attaching a small project with the docs sample so you can test it and compare against the problematic one that you have. If this does not help you resolve this, please modify this project to show the problem and send it back to me.

In the meantime, I suggest running a repair on your Office installation and also testing other files downloaded from the Internet - it is possible that some security feature/setting/issue is blocking data display and proper file manipulation on the actual OS, not in the Telerik-generated file.

 

Regards,
Marin Bratanov
Progress Telerik