Custom column format not respected on excel export

[pquser]

Hi

On PQ 4.0.1 I'm using the German format for float cells "format = '#,###.00'".

This works in CSV but not in Excel.

The cells with 0,00 in Excel are formatted as "#,###00". What I need is: "#.##0,00".

Cells with values in Excel are formatted as "#,###00". What I need is: "#.###,00".

Any suggestions?

[paramvir]

In order to apply format correctly, float values should be valid float values.

0,00 is incorrect value, it should be 0.00

[pquser]

Thanks.

The data pqgrid gets are correctly formatted JSON values.

To make it easier for me to understand your code:
Could you please point out, which code (line number in pqgrid.dev.js ver 4.0.1) is executed to handle the "format" property of the colModel?
This would be great for my partners and me to study and understand.

Thanks again.

[paramvir]

I've created this jsfiddle for reference purpose.

http://jsfiddle.net/tv9b0bhh/

German format is displayed correctly in the grid, but I see and as you mentioned in first post, it doesn't work in Excel.

Reading the source code of grid might not help as format is controlled by Excel and grid only passes on the german format ("#.###,00") and numeric values to Excel. Ironically german format works correctly in grid but not in Excel.

Searching the web gives some insights into German formatting vis-a-vis Excel:

https://www.toytowngermany.com/forum/topic/120805-changing-the-decimal-separator-in-microsoft-excel/

[paramvir]

Here is a workaround to export Profits column as preformatted strings rather than passing numbers and format to Excel.

by use of formula, hidden column and beforeExport, export event.

Code: [Select]

{
            dataIndx: "ProfitsFormat",
            align:'right',
            title: "Profits",
            width: 160,
            hidden: true
}


Code: [Select]

        formulas:[
        ['ProfitsFormat', function(rd){
          return pq.formatNumber(rd.profits ,'#.###,00');
          }]
        ],


Code: [Select]

        beforeExport: function() {
          var column = this.getColumn({
              dataIndx: 'ProfitsFormat'
            });           
          column.hidden = false;
         
          this.one('workbookReady', function() {
            //restore hidden.
            column.hidden = true;
          })
        },

http://jsfiddle.net/tv9b0bhh/23/

[pquser]

This solution is good for displaying the German format the right way. But as it is String, our customers won't be able to use it for further calculation.

So question to you, Sir:
Would it be possible to provide a fix not needing this workaround? Eventually as a seperate .js file one could load after pqgrid.js so it works not only for the latest version?

Thank you in advance!

[paramvir]

It's possible to provide a fix only if German format ('#.###,00') is directly supported in Excel. As mentioned earlier, pqgrid passes the '#.###,00' format correctly to Excel but Excel doesn't recognize/accept that format.

Please let me know how you format the numbers in German in Excel file so that I check feasibility of a solution.

[pquser]

My partner sent me some debugging data for you. Please find it attached.

[paramvir]

please also share an Excel file with correct German format ( the way it's supposed to be ).

[pquser]

Here it is.