This project has moved. For the latest updates, please go here.

Worksheet-related bugs

Dec 17, 2012 at 8:50 AM
Edited Dec 17, 2012 at 12:16 PM

Hello, I've encountered a bug, where after saving the .xlsx file the sheet are magically linked to one another. I don't know why it happens, but here are the steps I used to reproduce the bug:

 

Step 1: Create a .xlsx file using Microsoft Excel and remove all but one sheet (and save)

Step 2:

 

XLWorkbook wb = new XLWorkbook(pathOfXLSXFile);

IXLWorksheet originalSheet = wb.Worksheet(1);
ws.Visibility = XLWorksheetVisibility.VeryHidden;
ws.Protect(); // not necessary, but makes detecting the bug easier

wb.AddWorksheet("New Sheet");

wb.Save();

 

Step 3: Open the .xlsx file with Microsoft Excel and try to edit the first (visible) sheet. Excel will complain that the hidden sheet is protected. If you didn't protect the sheet, you'll have to unhide it first (right click on the sheets tabs, "show code", select the hidden sheet, set "visibility" to "visible"), you'll see that all changes made to the visible sheet were also made on the hidden sheet.

 

EDIT:

Another bug I just encountered:

Deleting Worksheets will break any NamedRange that contains ranges within the deleted sheet. Trying to access such NamedRanges will throw an exception (Worksheet not found).

 

EDIT 2:

Since I'm already at it:

Worksheet.NamedRanges will always be empty, thus Worksheet.NamedRange(someValue) will always throw an exception. The documentation claims otherwise.

 

XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("New sheet");

ws.FirstCell().AddToNamed("range");

IXLRange r1 = wb.NamedRange("range"); // {New sheet!$A$1}
IXLRange r2 = ws.NamedRange("range"); // System.Collections.Generic.KeyNotFoundException

 

EDIT 3:

Another bug I encountered:

To avoid the linked-sheet bug, I made a copy of the original sheet and deleted said sheet. However, as soon as I add a cell to a NamedRange, the NamedRange references the old (deleted) sheet, instead of the sheet, where the cell originated from. Luckily, using a range (or using a cell with row > 1 and column > 1) seems to work wonders here:

 

XLWorkbook wb = new XLWorkbook(pathOfXLSXFile);

// Workaround
IXLWorksheet ws = wb.Worksheets.First().CopyTo("New sheet");
wb.Worksheets.First().Delete(); // Sheet1 deleted

ws.FirstCell().AddToNamed("range1"); // {'Sheet1'!$A$1:$A$1}
ws.FirstCell().AsRange().AddToNamed("range2"); // {'Sheet1'!A1:A1}
ws.Range(1,1,1,1).AddToNamed("range3"); // {'New sheet'!A1:A1}
ws.FirstCell.CellRight().AddToNamed("range4"); // {'Sheet1'!$B$1:$B$1}
ws.FirstCell.CellBelow().AddToNamed("range5"); // {'Sheet1'!$A$2:$A$2}
ws.FirstCell.CellRight().CellBelow().AddToNamed("range6"); // {'New sheet'!$B$2:$B$2}

Obviously, only "range3" and "range6" will work.

Coordinator
Dec 17, 2012 at 6:51 PM

I fixed the first issue. Pick up the latest source code. I'll deal with the others (edits) later.