xlsxRemove sheet

Michael Hawthorne

Hi, im using {{xlsxRemove "xl/workbook.xml" "workbook.sheets[0].sheet" 1}} to remove a sheet, it removes ok but the pdf has errors when I try and open it, the error its giving me is "Named range from /xl/workbook.xml part (Workbook)" I have checked a few solutions online none of them work, if I don't remove the sheet the excel opens perfectly.

whats the best way to remove a sheet and references

regards

bjrmatos

hi! hmm i never tried to remove a sheet using the helper, so it surely needs another remove calls in order to don't break the file. what you can try is the advice from the docs

When you are lost, it is always good idea to create a test xlsx file in excel, unzip it and analyze its content. Another approach we recommend is to create the whole excel the first, upload it into jsreport and then only replace its data, which is simple.

so basically make a copy of your xlsx template, then on the copied file remove the sheet using the office editor and then unzip it and inspect the content of the xml files. after that you can compare the contents of both the original file and the copied file (which has the xlsx sheet removed) to understand what are the exact things that you need to remove and then you can translate that using xlsxRemove calls

Michael Hawthorne

Hi, thanks for the reply, I done what you suggested, seems the content types were needing removed and the definedNames
The content types are in order so removing at the array index works ok but the defined names are not in any order, how can I use xlsxRemove to target a specific localSheetId.

the xml looks like this
<definedNames>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="5">ARCS!$A$4:$C$4</definedName>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="4">'Digital Publications'!$A$4:$H$4</definedName>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="2">'Paper Charts'!$A$4:$F$4</definedName>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="3">'Paper Publications'!$A$4:$H$4</definedName>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="1">Subscriptions!$A$4:$F$4</definedName>
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="0">Vessel!$A$4:$H$4</definedName>
</definedNames>

and im using this to remove the sheet, content types and defined names
{{#compare Valid false}}
{{#xlsxRemove "xl/workbook.xml" "workbook.sheets[0].sheet" 1}}
{{/xlsxRemove }}
{{#xlsxRemove "[Content_Types].xml" "Types.Override" 1}}
{{/xlsxRemove}}
{{#xlsxRemove "xl/workbook.xml" "workbook.definedNames[0].definedName" 3}}
{{/xlsxRemove}}
{{/compare}}

regards