Export with custom parameter to freeze panes

laloune

Hello,

I'm trying to freeze panes in my ExcelHtml5 export but it breaks the output file (Excel can't open it). I just append the code to the sheet object as such:

var sheet = xlsx.xl.worksheets['sheet1.xml']; 
var freezePanes = '<sheetViews><sheetView tabSelected="1" workbookViewId="0"><pane xSplit="1" ySplit="1" topLeftCell="B2" activePane="bottomRight" state="frozen"/></sheetView></sheetViews>';
                sheet.children[0].innerHTML += freezePanes;

any idea why it breaks it ?

thanks in advance!

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

laloune

hello colin,

sorry for the confusion. I reproduced the problem here:
https://jsfiddle.net/uo867xbm/

If I comment the line 20 out it works without problem.

to get the variable "freezePanes" I unpacked a file where I had previously frozen the panes to see which xml part I should use. But it seems that I am missing something here. Maybe the wrong xml node ? (sheet.children[0])

Best
laloune

colin

Thanks for the test case. That is odd, as it works fine on Open Office so it might be a quirk of MS Office. I haven't got a Windows OS to hand right now. I'll try it later and report back.

Colin

laloune

oh yeah that's right, in LibreOffice this works also smoothly...

maybe I should post on stackoverflow for the issue since this is no datatables issue anymore in this case... I will post the outcome here

colin

That's a good idea, and please let us know, I'd be interested to find out!

Colin

laloune

hello Colin,

I posted the issue here:
https://stackoverflow.com/questions/67718003/office-open-xml-freeze-panes-in-excel/67721007#67721007

A user there believes that it has to do with the order of the elements (SheetViews should be before cols and sheetData). I tried it out, see this new fiddle:
https://jsfiddle.net/30be68fm/

and here it goes! Also helpful to know that the order is important (to Excel, at least)

colin

That's some smart people on SO!

Glad you got it sorted, that's good to know. Thanks for reporting back,

Colin