Strange freeze behavior?

[ackerdev]

I'm having a fair bit of trouble replicating this bug, but I have a fairly large sheet of data (40-by-250 ish) where I am trying to freeze the first row and header at the same time, and they appear to be malformed somehow as on Excel 2007 the frozen row shows up twice (disappears on scroll, and is fixed on saving through excel), and on Excel 2013 it shows a message on startup that the file was malformed and it has been repaired, which removes the row's freezing.

I tried to create a test case replicating this behavior, but was unsuccessful.

Let me know if there's anything I can try or investigate for you to debug.

[natergj]

I usually start by running the output Excel file through the validator from http://ooxmlvalidator.codeplex.com when I run into errors like that.
Sometimes it will validate a file perfectly well, but Excel will still throw errors, though. If you'd like to send me a copy of the bad xlsx file, I can take a look at the XML that was generated to see if I can find what caused the error.
Also, I'm a Mac user, so my Excel works a little different, but which row are you stating to be frozen? If you want to make it so just the first row does not scroll, you should be specifying Row(2).Freeze(). I think this is really counter-intuitive, but I chose to do it that way since in the Excel app to make it so the first row doesn't move, you would highlight the second row and then choose the Freeze Pane option and I wanted to stay consistent with Excel.
In any case, the ooxml validator will hopefully let you know exactly what the issue is. If its a bug in the module, please let me know and I will get it fixed.

[ackerdev]

I ran the generated document through the OpenXML SDK 2.0 Validator and the only errors it showed for my file were color errors, which I've documented in #16 should they be worth fixing. Of course, I doubt that has anything to do with my problem.

With the SDK, I decided to compare the Source file generated by excel4node against the same file opened and saved in Excel 2007, allowing it to fix the errors. The most relevant diff I could find was in /xl/worksheets/sheet1.xml:

<!-- excel4node source version -->
<sheetView tabSelected="1" workbookViewId="0">
    <pane activePane="bottomLeft" state="frozen" topLeftCell="B1" ySplit="1" xSplit="1"/>
</sheetView>

<!-- Excel 2007 fixed version -->
<sheetView zoomScaleNormal="100" tabSelected="1" workbookViewId="0">
   <pane activePane="bottomRight" state="frozen" topLeftCell="B2" ySplit="1" xSplit="1"/>
   <selection pane="topRight"/>
   <selection pane="bottomLeft"/>
   <selection activeCell="B1" pane="bottomRight" sqref="B1"/>
</sheetView>

Note the pane node's activePane and topLeftCell attributes. I'm assuming that the topLeftCell attribute should refer to the top-left-most cell of the non-frozen pane, which would be B2, as seen in the Excel07 version. Looking at the source for Row.js and Column.js, it seems like both of their Freeze methods overwrite this attribute.

https://github.com/natergj/excel4node/blob/master/lib/Row.js#L256
https://github.com/natergj/excel4node/blob/master/lib/Column.js#L38

In my code, I was calling ws.Column(2).Freeze() last, and according to the source of Column, it would be writing B1, ignoring that ws.Row(2).Freeze() was already called.

Not sure why I didn't see this problem replicated in my smaller test cases, but perhaps I was doing something wrong. Hopefully this might help us find the problem.

[natergj]

OK. i'm seeing that same behavior if I call the Freeze function twice. In the Mac Excel, the application won't actually allow you to freeze both a row and column (at least I couldn't figure out how) so I didn't take that scenario into consideration. But I can replicate what you are seeing, too, so I should be able to come up with a fix for this. hopefully soon.

Thanks again for all your troubleshooting help.

[ackerdev]

I think it's just unintuitive because it took me googling to figure it out in Windows Excel too. If your version has a freeze panes dropdown/options menu that includes "Un/Freeze Panes", "Freeze First Row" and "Freeze First Column", I think you need to select cell B2 and then choose the "Freeze Panes" option.

Glad to hear you could replicate it on your side. Thanks a ton for your help. 😄

[natergj]

scrolling on freezing should work as expected now when freezing both rows and columns

[ackerdev]

Thanks for fixing this! Works perfectly now!
👍