Excel Export Formatting

[fmusignac]

How do you format excel numbers?

I have tried data type float,  replacing commas dollar and dollar signs, etc. On every attempt, the exported file asks to convert text to numbers. I also tried adding colModel.format ='$###,###' and the data does not show with this option.

Using version 3.3.5.

[paramvir]

In v3, formatted numbers with colModel.format ='$###,###.0' are always exported as strings.

In latest versions, formatted numbers ( e.g., in Freight column ) are exported as numbers when possible.

https://paramquery.com/pro/demos/export_local

[fmusignac]

So the only option is to upgrade? How's the compatibility, much effort to do so?

I know my company has upgraded to a later version but I am not seeing it on my download page. Can you help with that too?

[paramvir]

The upgrade guide for v4 and v5 would assist you in upgrading from v3. The effort required depends upon amount of code written related to breaking changes.

Could you please share Transaction Id of latest renewal for your company so that your account can be upgraded accordingly.

[fmusignac]

I don't have it with me but I was able to find v4 from our repository. The main breaking change I see is when we have an html table converted into a pq grid using $.paramquery.tableToArray, the html within some cells is being rendered as text and not the html controls (span, checkbox, etc.) that was there before. Is there a new option that I have to use to enable that rendering?

[fmusignac]

Looks like triggering the render with the same content works, but why would we have to do this when the same content is already there. This used to work on 3.3.5.
//...looping through colModel...
        colModel.render = function (ui) {
            var rowData = ui.rowData, dataIndx = ui.dataIndx;
            rowData.pq_cellcls = rowData.pq_cellcls || {};
            return rowData[dataIndx];
        }

[paramvir]

The following is mentioned in the upgrade guide for v4.

Html entities in cell data with dataType: 'string' are escaped while rendering for security. If they are needed on purpose, then use dataType: 'html'. Any html entities added by render or postRender callbacks are not affected.

https://paramquery.com/pro/upgrade/Index40

[fmusignac]

How about exporting numbers as strings? I'm having trouble with exporting numbers like 000111000, 111122222, 11100000, and have them show exactly like they need to be (acct numbers). Only way so far has been adding a "'" to the string but that's not acceptable. It used to work too.

[paramvir]

Numbers with leading zeros like 000111000 can't be stored in number columns, otherwise leading zeros might get removed.

Please keep the column.dataType as "string" and then "000111000" is also exported as string.

[fmusignac]

I tried the same initially but the data is changed when exported. It removes all leading zeroes and displays in scientific notation.

This is my sample data, logged in the javascript console for verification.

ALL
0004062104611461575
0004062107110507813
0004062109655336315
055007425063700110658115
100890100018982003312100
1044372000749303326
10443720008563710304
Account Group

[paramvir]

It works fine in v3 export demo

https://paramquery.com/pro/demos33/export_local

Step 1: Change the Freight column dataType as "string".

Step 2: Remove format from Freight column definition.

Step 3: Enter 000002341121 in one of the Freight cells.

Step 4: Export

It appears as 000002341121 in Excel file.

[fmusignac]

I had to upgrade to v4, as suggested, to get the number formatting to work correctly.