Dimension property not updated after modifying an Excel file with a frozen row

[nemkin]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• [x] Bug
• [ ] Feature
• [ ] Question

Did you test against the latest CI build?

• [x] Yes
• [ ] No

Tested against 0.95.999.2326, currently latest.

If you answered No, please test with the latest development build first.

Version of ClosedXML

0.95.4

What is the current behavior?

Small example:

• Use Excel to create an xlsx file, write the string "A1" to the A1 cell and "D6" to the D6 cell.
• Freeze the first row (this step is needed).
• Use ClosedXML to open the file, write the string "Generated" to cells between A1 and B10 and save the file.
• The created file displays correctly in Excel, however parsing it with another library, with e.g. https://oss.sheetjs.com/ will only parse between A1 and D6. The content under row D is missing.

Excel:

Parsed:

What is the expected behavior or new feature?

Third party parsers should be able to parse the output Excel file from ClosedXML below the last manually modified cell.

I understand that the third party parser I'm using could also have a bug, however since the issue happens below the last manually modified cell, I believe there is some information present in the Excel file about the last row / last column that is non-empty which is not overwritten by ClosedXML on save and this information could be used by the third party parsers to iterate the rows/columns, but not by Excel itself. I'm not sure why this only happens if the first row is frozen in the input file.

The reason why I'm trying to do this is that my team uses ClosedXML on the backend of a website to generate Excel worksheets from a template file and user data and we are trying to run end-to-end tests written in TypeScript on the frontend to validate the results, so I'm using another Excel library that is written in JavaScript to parse the results back.

Is this a regression from the previous version?

No, I have tested with 0.94.2 and it happens there too.

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be minimal complete and verifiable. Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

Code to reproduce problem:

using System.Linq;
using ClosedXML.Excel;

namespace ClosedXMLTest
{
    class Program
    {
        static void Main()
        {
            var workbook = new XLWorkbook(@"C:\...\test.xlsx");
            var sheet = workbook.Worksheets.First();
            for (int i=1; i<=10; ++i)
            {
                var row = sheet.Row(i);
                for(int j=1; j<=2; ++j)
                {
                    row.Cell(j).SetValue("Generated");
                }
            }
            workbook.SaveAs(@"C:\...\test_out.xlsx", true, true);
        }
    }
}

• [x] I attached a sample spreadsheet. (You can drag files on to this issue)

test.xlsx

[nemkin]

I forgot to add: If I then open test_out.xlsx in Excel, and save it again then it correctly parses on https://oss.sheetjs.com/. I think Excel fixes the issue on save.

[igitur]

To be honest, this sounds more like a bug in SheetJS. I recommend you log an issue with that project first and if it's proven that ClosedXML generates an invalid file, then I'll look into it.

[nemkin]

Okay, I inspected the test_out.xlsx file generated by ClosedXML with the code snippet above. Changed the xlsx extension to .zip and extracted the folder.

This is the contents from the xl/worksheets/sheet1.xml file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xr:uid="{DD8065D3-3D7D-47FB-92E8-CD722ACC16FC}" mc:Ignorable="x14ac xr xr2 xr3">
  <x:sheetPr>
    <x:outlinePr summaryBelow="1" summaryRight="1"/>
  </x:sheetPr>
  <x:dimension ref="A1:D6"/>
  <x:sheetViews>
    <x:sheetView tabSelected="1" workbookViewId="0">
      <x:pane xSplit="0" ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozenSplit"/>
      <x:selection activeCell="L23" sqref="L23"/>
      <x:selection pane="bottomLeft" activeCell="L23" sqref="L23"/>
    </x:sheetView>
  </x:sheetViews>
  <x:sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
  <x:cols>
    <x:col min="1" max="3" width="9.140625" style="0" customWidth="1"/>
    <x:col min="4" max="4" width="12.285156" style="0" customWidth="1"/>
  </x:cols>
  <x:sheetData>
    <x:row r="1" spans="1:12" x14ac:dyDescent="0.25">
      <x:c r="A1" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B1" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
    </x:row>
    <x:row r="2" spans="1:12">
      <x:c r="A2" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B2" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D2" s="0" t="s"/>
    </x:row>
    <x:row r="3" spans="1:12">
      <x:c r="A3" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B3" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D3" s="0" t="s"/>
    </x:row>
    <x:row r="4" spans="1:12">
      <x:c r="A4" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B4" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D4" s="0" t="s"/>
    </x:row>
    <x:row r="5" spans="1:12">
      <x:c r="A5" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B5" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D5" s="0" t="s"/>
    </x:row>
    <x:row r="6" spans="1:12" x14ac:dyDescent="0.25">
      <x:c r="A6" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B6" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D6" s="0" t="s">
        <x:v>1</x:v>
      </x:c>
    </x:row>
    <x:row r="7" spans="1:12">
      <x:c r="A7" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B7" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D7" s="0" t="s"/>
    </x:row>
    <x:row r="8" spans="1:12">
      <x:c r="A8" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B8" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D8" s="0" t="s"/>
    </x:row>
    <x:row r="9" spans="1:12">
      <x:c r="A9" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B9" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D9" s="0" t="s"/>
    </x:row>
    <x:row r="10" spans="1:12">
      <x:c r="A10" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="B10" s="0" t="s">
        <x:v>0</x:v>
      </x:c>
      <x:c r="D10" s="0" t="s"/>
    </x:row>
  </x:sheetData>
  <x:printOptions horizontalCentered="0" verticalCentered="0" headings="0" gridLines="0"/>
  <x:pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
  <x:pageSetup paperSize="1" scale="100" pageOrder="downThenOver" orientation="default" blackAndWhite="0" draft="0" cellComments="none" errors="displayed"/>
  <x:headerFooter/>
  <x:tableParts count="0"/>
</x:worksheet>

That <x:dimension ref="A1:D6"/> is incorrect, it should be A1:D10. This is not an issue in SheetJS.

If I open the xlsx file in Excel, save it, then do the same thing as I did above, that dimension property changes to <dimension ref="A1:D10"/>.

[igitur]

OK, good catch. I'll look into it.

[nemkin]

Thank you! I updated the title to reflect the issue better.