The data in Excel is not the same as the data displayed on the grid

The data in Excel is not the same as the data displayed on the grid

Posted by: tvhau.dev on 10 May 2021, 4:17 am EST

• 

  tvhau.dev

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 10 May 2021, 4:17 am EST

  Hello Wijmo Team,
  
  I have used Wijmo 5.20192.631 version.
  
  Currently, I am using format = ‘g10’ so that the data displayed on the grid is the same as the data source

  data source: 300.00000000, 1000000.123456700, 1.2345600, 78610000.1234567000, 78610000.12345670, 0.0000
  
  data displayed on the grid: 300, 1000000.1234567, 1.23456, 78610000.1234567, 0

  However, when exporting Excel, the data in Excel is not the same as the data displayed on the grid

  data in Excel: 300.0000000000, 1000000.1234567000, 1.2345600000, 78610000.1234567000, 0.0000000000

  Can you show me any solution so that the values at excel display the same as on the grid?

  Refer to the sample below
  
  http://jsfiddle.net/tvhaudev/rh9esqpo/61/

  I’m waiting for your help.
  
  Thank you.

• 

  tvhau.dev

  • Post Options:
  • Link
    Copy

  Posted 10 May 2021, 4:43 am EST

  Desired results
  
  In Excel, it displays: 300, 1000000.1234567, 1.23456, 78610000.1234567, 0

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 11 May 2021, 1:30 pm EST

  Hi,

  The said issue is observed because .Net standard formats like g, c, are not supported by excel, and excel do not have an accounting formatter to equivalent to g10 formatter, the best closest match is #.0000000000 (which is set by the grid) or #.##########. In case of #.########## formatter, numbers with decimal values will display correctly but integers such as 300 will have a decimal(.) in the end like 300. which looks weird.
  
  Further, we have prepared a sample to demonstrate how you could modify the format of a cell when it is exported to excel, you may refer to the following sample and add the apply any excel supported formatter in a similar way. Please refer to the following code snippet and the updated fiddle which demonstrates the same and let us know if you face any issue:

  function exportFormatItem(args) {
          let p = args.panel, row = args.row, col = args.col, xlsxCell = args.xlsxCell;
          if (p.cellType == wijmo.grid.CellType.Cell) {
             if (p.columns[col].binding === 'amount' && xlsxCell.value % 1 == 0) {
                  xlsxCell.style.format = '0';
              }
                 else {
                  xlsxCell.style.format = '0.##########';
              }
          }
      }
  

  http://jsfiddle.net/go5tqkmd/1/

  ~sharad

• 

  tvhau.dev

  • Post Options:
  • Link
    Copy

  Posted 12 May 2021, 9:39 pm EST

  Hi sharad,
  
  Thank you so much
  
  I tried your solution, this has helped a lot.

• 

  tvhau.dev

  • Post Options:
  • Link
    Copy

  Posted 15 May 2021, 1:37 pm EST - Updated 3 October 2022, 1:55 pm EST

  Hi sharad,
  
  I have one more query
  
  In the case of the format #. ############, decimal numbers will display correctly. however, when the value is too big such as [ 383400003443446.2345600 ] there will be a decimal (.) at the end like [ 383400003443446. ]
  
  Is there any solution to remove the decimal point (.) at the end and export the Excel the same as on the grid?

  See image below
  
  

  

  712×455 19.9 KB

  Refer to the sample below
  
  http://jsfiddle.net/594zqcgj/9/

  If there is any solution please let me know asap.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 17 May 2021, 1:40 pm EST

  Hi,

  We are sorry but the issue is arising because of the excel limitation, in MS excel numbers could only have 15 significant digits, which in this case surpasses this limit and instead of recognizing the floating number, it treats it as an integer, causing the issue. You may confirm this by following these steps in MS excel:
  
  • Select a cell and set its format to number
  
  • Input 12345.56 in the cell
  
  Observe: 12345.56 is displayed
  
  • Input 123456789012345 in the cell
  
  Observe: 123456789012345 is displayed
  
  • Input 1234567890123456 in the cell
  
  Observe: 1234567890123450 is displayed

  Regards

• 

  tvhau.dev

  • Post Options:
  • Link
    Copy

  Posted 18 May 2021, 9:55 pm EST

  Hi,
  
  Thank you so much once again.
  
  I understood the problem