Author Topic: Export to Excel issues  (Read 82 times)

akraines

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 33
    • View Profile
Export to Excel issues
« on: February 12, 2020, 07:36:34 pm »
Hi,

We have an issue:
1. The data in the column is "-6.513158302558158" with a format string of "##%". The correct value of -7% appears in the grid. (just like the output of the function pq.formatNumber("-6.513158302558158","##%"))
However, when exporting to Excel, it changes the value to -651.315830255816%

2. Is there a way to not include hidden rows when exporting to excel? I tried
const $excludeHidden = $("#export_exclude_hidden");
             const excludeHidden = $excludeHidden.is(":checked");
             if (excludeHidden) {
                 const {sheets:[sheet]} = w1;
                 if (sheet){
                     sheet.rows = _.filter(sheet.rows,r=> !r.hidden);
                 }
             }
which worked pretty well, except that it shows the summary (for aggregate columns) both at the top and at the bottom of the group. Eg (I put the duplicate summaries in bold) - I want the summary to only appear at the bottom of the group when the group is expanded, the same way it appears in the grid.:
Healthcare (1)                  $.00   $.00   $76.00   -$76.00   %
   ABC   26110   MCV   abcdefg      $.00   $.00   $76.00   -$76.00   %
                  $.00   $.00   $76.00   -$76.00   %
Institutions (1)                  $.00   $.00   $164.67   -$164.67   %
   XYZ   27071   MCV   xyzwx      $.00   $.00   $164.67   -$164.67   %
                  $.00   $.00   $164.67   -$164.67   %

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 4315
    • View Profile
Re: Export to Excel issues
« Reply #1 on: February 12, 2020, 10:15:52 pm »
[ Only Pro members may read this post. ]

akraines

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 33
    • View Profile
Re: Export to Excel issues
« Reply #2 on: February 12, 2020, 10:29:03 pm »
1. How can I modify the format only when exporting to Excel? Is their a change I can make to the pqGrid.dev.js code itself to fix this exporting issue?
2. I removed that option and it still rendered the summary twice. I believe that the issue is caused because I'm deleting all hidden rows in the exported sheet (" sheet.rows = _.filter(sheet.rows,r=> !r.hidden);"). however I'm not familiar enough with the inner workings of exportExcel to solve this. Can you suggest a better method to not include hidden rows in the exported excel, that doesn't have this issue? I had a hunch that it has to do with merged cells, however I belive that I saw this issue even when exporting a sheet where sheet.mergedCells was undefined.

Thanks

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 4315
    • View Profile
Re: Export to Excel issues
« Reply #3 on: February 13, 2020, 12:28:31 pm »
[ Only Pro members may read this post. ]

akraines

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 33
    • View Profile
Re: Export to Excel issues
« Reply #4 on: February 13, 2020, 10:50:06 pm »
1. Thanks
2. We specifically want the behaviour of collapsed. I believe the issue is simply with how I remove rows. I'm simply deleting rows where hidden = true. However I have a feeling that there are factors that effect the export, eg updating the mergedCells array so that the row numbers are in sync with the cell-ranges. Does that make sense?

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 4315
    • View Profile
Re: Export to Excel issues
« Reply #5 on: February 14, 2020, 10:29:11 am »
[ Only Pro members may read this post. ]
« Last Edit: February 14, 2020, 10:33:36 am by paramvir »