ClosedXML file will not open after adding a new worksheet

Jan 27, 2013 at 10:58 PM

First of all, thank you for creating ClosedXML! !  

Now onto the problem. I have created a new .xlsx file in VB.NET using the following code:

Dim wb As XLWorkbook        

wb = New XLWorkbook()          
Dim wksht = wb.Worksheets.Add("Worksheet1")

wksht.Cell(1, 1).Value = "Management Unit:"    
 

Dim saveName As String = "c:\folder\fileName.xlsx"       
wb.SaveAs(saveName)

This code creates the file and does not throw any errors. I have Excel 2003 so I have to use the add-on converter Microsoft provides to open .xlsx files, but the file opens fine and cell A1 is populated correctly.

Then I want to add another worksheet to the file (in another VB.net function block) so I use the following code to open the file, add the worksheet, and save the file with the original file name:

Dim wb As XLWorkbook
Dim newSheet As IXLWorksheet
wb = New XLWorkbook("c:\folder\fileName.xlsx")       

newSheet = wb.Worksheets.Add("addedWorksheet")       
newSheet.Cell(1, 1).Value = "New Text"

wb.SaveAs(fileName)

This code also appears to work correctly. The file is saved and the size of the file is larger which makes me believe that the new worksheet was successfully added.

But, when I try to open the modified workbook in Excel I get the following error message:

 "Save As"

 "The converter failed to save the file"

Is the code I wrote to add the second worksheet wrong in some fashion, or do you think it is a problem with Microsofts .xls/.xlsx converter?

I can add as many worksheets as I want if I am inside the function that constructs the original .xlsx file but if I try to add a worksheet from another function it throws the error.

Any suggestions to solve this problem would be greatly appreciated!

 

Jan 27, 2013 at 11:17 PM
Edited Jan 27, 2013 at 11:32 PM

I sent the file to a co-worker who has the newest version of Excel and he was able to open the files. This is what happened when he worked with the ClosedXML generated file:


"I tried to open it and got a message that Excel found "unreadable content" and then said "do you want to recover the contents"  then said something about trusting the source. I then got "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.""


After going through this process he then saved the file as both and .xls and .xlsx and both files work fine.

I don't want to make my end users go through this process so if you have any idea on how I might be able to avoid this strange behavior I would appreciate your thoughts.

Thanks again!

Coordinator
Jan 28, 2013 at 3:20 PM

How can I reproduce it? The following works fine (no errors when I open the file in Excel):

var file = @"Sandbox.xlsx";
 
var wb1 = new XLWorkbook();
var ws1 = wb1.AddWorksheet("Sheet1");
ws1.FirstCell().Value = "A1 on Sheet1";
wb1.SaveAs(file);
 
var wb2 = new XLWorkbook(file);
var ws2 = wb2.AddWorksheet("Sheet2");
ws2.FirstCell().Value = "A1 on Sheet2";
wb2.SaveAs(file);