Number cell type when exporting to Excel

1 Answer 20 Views
Grid
Claude
Top achievements
Rank 1
Claude asked on 18 Jan 2022, 06:30 PM

I know how to do this, but I was wondering if there is a simpler way that I do not know. The solution I found seems a bit complicated for something that seemed simple at first.

 

I have a non-editable grid. For each item in Y, I have multiple cells in X displaying a counted number. I don't care about zeroes, so as to not draw attention to them I output a number only if it's not zero. To do that, the values I feed in my datasource needs to be in string format.

When I export this grid to Excel, by default all cells are of the "General" type. If I manually try to switch the cells to the "Number" type, it doesn't work right because the data is still a string.

I want my Excel export to have it's cells already in the "Number" type with it's data in the right format.

 

What I had to to is this :

- Change the values in my datasource to numbers, but now my grid displays zeroes.

- Create a template for my cells to control when to display numbers or not.

- Add a excelExport event handler to set my cells' format. The resulting cell type is always tagged as "Custom". Isn't there a way select one of the defaut Excel cell types? Is seems to work, but it feels weird to have to create custom formats to replicate default ones.

 

Did I miss something that would have made my life easier?

 

Thank you

 

 

1 Answer, 1 is accepted

Sort by
0
Accepted
Nikolay
Telerik team
answered on 21 Jan 2022, 01:12 PM

Hi Claude,

Indeed, this is the right way to go. Leave the values as numbers, set a template to show only non-zero values, and in the excelExport event modify the Excel workbook configuration object to export template values.

excelExport: function(e) {
            var sheet = e.workbook.sheets[0];
            var template = kendo.template(this.columns[2].template);
            for (var i = 1; i < sheet.rows.length; i++) {
              var row = sheet.rows[i];
              var dataItem = {
                UnitsOnOrder: row.cells[2].value
              };
              row.cells[2].value = template(dataItem);
            }
          }

Here is a Dojo demo I prepared demonstrating the above:

Regards,
Nikolay
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
Grid
Asked by
Claude
Top achievements
Rank 1
Answers by
Nikolay
Telerik team
Share this question
or