Transposed grid - export as excel format issue

Transposed grid - export as excel format issue

Posted by: ronak.soni on 24 September 2021, 1:57 am EST

• 

  ronak.soni

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 24 September 2021, 1:57 am EST

  Hello Team,

  I am using wijmo transposed grid and it’s worked fine.
  
  But, when I am going to export the table using below code I am not getting formatted output. i.e If I have data in percentage like 5%, then in excel I am getting 0.5.
  
  Please look below code of the exporting file as excel:

        wjGridXlsx.FlexGridXlsxConverter.saveAsync(this.flexGrid, { includeColumnHeaders: false,
          includeRowHeaders: true, includeCellStyles: true }, 'FlexGrid Data');
      }
  

  If I am using below format item option then in the excel I am getting warning in the cell.

  Thanks

• 

  ronak.soni

  • Post Options:
  • Link
    Copy

  Posted 24 September 2021, 7:36 am EST

    wjGridXlsx.FlexGridXlsxConverter.saveAsync(this.flexGrid, { includeColumnHeaders: false,
          includeRowHeaders: true, includeCellStyles: true, formatItem: this.customContent ? this._exportFormatItem : null }, 'FlexGrid Data');
      }
  private _exportFormatItem(args: wjcGridXlsx.XlsxFormatItemEventArgs) {
      var p = args.panel,
        row = args.row,
        col = args.col,
        xlsxCell = args.xlsxCell,
        cell: HTMLElement;
  
      if (p.cellType === wjcGrid.CellType.Cell) {
  
        cell = args.getFormattedCell();
        xlsxCell.value = cell.textContent.trim();
        xlsxCell.style.hAlign = wjcXlsx.HAlign.Left;
  
      }
    }
  
  
  

• 

  sonu.pandey

  

  • Post Options:
  • Link
    Copy

  Posted 27 September 2021, 6:12 am EST

  Hello,

  This warning message in excel has occurred because in the exported excel file the cell number values are of string type with the percentage symbol as the string value. If you wish to export the FlexGrid formatted Cells values then you may change the format of the cells before exported. Please refer to the code snippet below and sample link for reference

  
    function exportFormatItem(args) {
      var p = args.panel,
        row = args.row,
        col = args.col,
        xlsxCell = args.xlsxCell,
        cell;
  
      if (p.cellType === wjcGrid.CellType.Cell) {
        cell = args.getFormattedCell();
        xlsxCell.style.hAlign = wjcXlsx.HAlign.Left;
        xlsxCell.style.format = '0.00%';
      }
    }
  
  sample link: https://stackblitz.com/edit/js-wubqk4?file=index.js
  
  
  

  Let us know if you still face any issues or have any doubts.

  Regards

• 

  ronak.soni

  • Post Options:
  • Link
    Copy

  Posted 27 September 2021, 6:20 am EST

  Hello,

  Thanks for the reply.

  As per your solution. It’s change all data in percentage format, but What if I have the multiple format like currency (NZ$,CA$,$), percentage, decimal values.
  
  At that time it will not work correct?

  I checked args object from my end. From that object I am unable to find the proper format for the data to be rendered.

  If I will have that format for the data then according to that we can change the format basis on the data format.

  Thank you

• 

  ronak.soni

  • Post Options:
  • Link
    Copy

  Posted 27 September 2021, 6:33 am EST

  Hello Team,

  Also, if you look into the example which was given by you, In which when we checked the Custom Cell Content then the exported all data is in percentage format no matter it is in currency format.

  Thank you

• 

  sonu.pandey

  

  • Post Options:
  • Link
    Copy

  Posted 28 September 2021, 12:08 am EST

  Hello,

  Sorry for the misunderstanding, You can use the format property of the row to get the wijmo format applied to the grid and convert it into the xlsx format using toXlsxNumberFormat static function of the Workbook class. Please refer to the API for more information: https://www.grapecity.com/wijmo/api/classes/wijmo_xlsx.workbookstyle.html#format

  You may also refer to the code snippet and sample given below:

  
    function exportFormatItem(args) {
      var p = args.panel,
        row = args.row,
        col = args.col,
        xlsxCell = args.xlsxCell,
        cell;
      if (p.cellType === wjcGrid.CellType.Cell) {
        xlsxCell.style.hAlign = wjcXlsx.HAlign.Left;
        xlsxCell.style.format = wjcXlsx.Workbook.toXlsxNumberFormat(
          `${args.panel.rows[row].format}`
        );
      }
    }
  
  sample: https://stackblitz.com/edit/js-45ygnv?file=index.js
  
  

  Hope this would resolve your issue, let us know if you still face any issues.

  Regards