Cannot parse String columns/data to Numeric while exporting Ag-Grid table to Excel using getDataAsExcel

Question

I am trying to export data from an ag-grid table into excel using the API getDataAsExcel.

I have only one column which is supposed to have string values whereas all other columns are numeric and are displayed in my ag-grid table like "6,999,500".

But when I try exporting it, all my columns have "General" or String format.

For example if my ag-grid looks like this:

TABLE

name | salary | fee

john | 10,000 | 14

dany | 50 | 1,000

where, name -> string

salary -> numeric

fee -> numeric

It's weird that when I export this table into excel, all values are saved as "String" except 14 and 50 in this case. I noticed the numbers which doesn't have a ',' separator (like 14, 50 here) are exported numeric and 10,000 and 1,000 (having NO ',' separator) are exported as string.

I used parseInt() function such as:

if value = 10,000

parseInt(value.replace(/,/g, ''));

I replaced 10,000 with its parsed value, but no luck since it still prints it as String.

How can I change the format of the complete column or atleast parse these values individually?

Any Help will be appreciated!

sandeep joshi · Accepted Answer · 2020-10-28 10:32:50Z

1

you have to define dataType explicitly for excel export.

var columnDef = {
    ...,
    cellClass: 'itsANumber'
};

ExcelStyles: [
        {
            id: "itsANumber",
            dataType: 'number', // can use string, number, boolean, dateTime
        },

dataType (optional): One of (string, number, boolean, dateTime, error). In most cases this is not necessary since this value is guessed based in weather the cell content is numeric or not. This is helpful if you want to fix the type of the cell. ie. If your cell content is 003, this cell will be default be interpreted as numeric, and in Excel, it will show up as 3. But if you want to keep your original formatting, you can do so by setting this property to string.

answered Oct 28, 2020 at 10:32

sandeep joshi

2,1891 gold badge10 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot parse String columns/data to Numeric while exporting Ag-Grid table to Excel using getDataAsExcel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related