Declined
Last Updated: 05 Aug 2020 08:39 by ADMIN
Tamas
Created on: 31 Jul 2020 02:50
Category: Grid
Type: Bug Report
2
Columns with data are hidden in Excel Export spreadsheet
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.
7 comments
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