Export to Excel issues

[akraines]

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]

1. I understand that same format "##%" works differently in grid and Excel causing issue. You may fix it for now by dividing the values in that column in the js worksheet by 100 so that it appears as expected in Excel.

2. Please try groupModel.summaryInTitleRow = '' option

https://paramquery.com/pro/api#option-groupModel

[akraines]

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]

1. No need to make changes in pqgrid source code.

Please divide the cell values by 100 where cell.format == '##%' in intermediate JS spreadsheet created while export to Excel.

2. Please don't remove the option groupModel.summaryInTitleRow because its default value is "collapsed".

Just set it to empty string, groupModel.summaryInTitleRow = "" to override its default value.

If you are still facing issues, please share a jsfiddle.

[akraines]

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]

Ok, I guess it's making somewhat sense to me now.

You are deleting rows which is causing merged cells used by collapsed logic to get out of sync with rows thus showing summary cells in title rows which you don't want. It's to be expected because sheet.mergeCells is an independent array which doesn't get automatically adjusted when rows are deleted from worksheet.

Solution: Please try to remove summary cells from title rows in the worksheet and set sheet.mergeCells to empty array [] so that merge cells don't show up in unwanted locations.