Export to Excel - long string of digits treated as number, truncated

[jm]

I've got a VARCHAR field that contains long account numbers, all decimal digits but not treated as "numbers" as such (never used in calculations, et cetera). The values are displayed in pqgrid correctly, but when I export to Excel the column shows all values in exponential/scientific notation. Examining the cells shows that the unformatted values stored in these cells have been truncated; they all end in one or more zeroes where (mostly) nonzero digits should be.

How can I force these to be exported as text rather than integers, and to be displayed in Excel with all characters? It's okay if the Excel cells show the corner flag that means "number formatted as text".

Best regards,
JM

[paramvir]

A small sample of numbers and a definition of your column could provide more insight into problem faced by you.

Generally you can use column.format callback variant instead of string to format the cell values which would export them as strings instead of number with formats.

i.e instead of column.format = '$##,###.00'

you can use

Code: [Select]

column.format = function(val){
  return pq.formatNumber( val, "$##,###.00" );
}


[jm]

Hi, and thanks for the reply.

A typical value for this field might be "8177216122984745", to be displayed exactly as it's stored in our database (as a string of ordinary Unicode characters that happen to represent decimal digits, not an oversized integer or other numeric type). Values for this field are being displayed correctly in the data grid itself, and they can be edited and saved without problems.

The column definition is:

    { title: "Acct Num", width: 150, dataType: "string", dataIndx: "AcctNum", filter: filterEqual, editable: true },

The best work-around I've tried so far involved putting a literal quotation mark in front by inserting this into the column definition:

    format: function (val) {
        return "'"+val;
    }

… but while this does force the value to export as a string, the quotation mark becomes part of the displayed string rather than serving to signal that the following value is a string rather than a number, as it would when typing values into Excel itself. I'd like to export (and display) just digits, but force the exported values to be recognized as strings as the dataType says.

I suspect I'm missing something simple and obvious, but if so it's in my blind spot.

[paramvir]

The long numbers I tested are being exported correctly by pqgrid, but not displayed correctly due an issue at Excel end.

https://support.microsoft.com/en-in/help/2643223/long-numbers-are-displayed-incorrectly-in-excel

I found this workaround works for me: prepend an empty string in front of the number.

Code: [Select]

format: function (val) {
        return " "+val;
    }


[jm]

I'll try that. Using a non-breaking space character didn't seem to work in a prior test.

I'm surprised the export code doesn't force the cell's format to text when the column definition is string. Is it possible to add this feature in the future, or is the limitation on Excel's side and so unavoidable?

Best regards,
JM

[edit] Adding a space to the beginning doesn't work; Excel still interprets the string as a number, truncates it, and displays the result in scientific notation. For now I'll prepend a single quotation mark or a # sign, but for professional (and correct) results it looks as if I'll have to code reports by hand instead of having pqgrid's export feature take care of it as we'd planned.

[paramvir]

Which version of grid are you using?

That workaround works fine in latest v5.6.1. Please check this example: https://paramquery.com/pro/demos/export

by adding

Code: [Select]

format: function(val){
return " "+val;
},

in Freight column.