Error message "entries cannot be opened multiple times in update mode." in Spreadsheet Lite SaveAs function

Question

Upon execution of the dBWorksheet.SaveAs(xlsFileSpec), in the code below, I am seeing an exception:

"entries cannot be opened multiple times in update mode."

        SLDocument dBWorksheet = new SLDocument();
        TimeSpan interval = new TimeSpan(0, 0, 2);

        dBWorksheet.SetCellValue(2, 1, "Hour");
        dBWorksheet.SetCellValue(3, 1, "Time");

        int Row = 3;

        // Create the hour and time of day columns.
        for(TimeSpan dBTime = new TimeSpan(0, 0, 0); dBTime.TotalHours < 24; dBTime = dBTime.Add(interval)) 
        {
            dBWorksheet.SetCellValue(Row, 1, dBTime.Hours);
            dBWorksheet.SetCellValue(Row, 2, dBTime.ToString());

            Row++;
        }

        // Save the new worksheet.
        dBWorksheet.SaveAs(xlsFileSpec);

Answer 1

Here's how I solved it.

1. Downloaded the source code for SpreadsheetLight (version 3.5). http://spreadsheetlight.com/downloads/SpreadsheetLight3.5.zip

2. Created a .NET Core library project with the name "SpreadsheetLight" and added necessary NuGet packages (DocumentFormat.OpenXML and System.Drawing.Common) to it. Copied and pasted all the downloaded source code files in this project.

3. Added the project "SpreadsheetLight" to my solution and referenced it in one of the existing projects.

4. In "SLDocument.cs" file, make the following changes in the method "LoadDocumentProperties()" so that the code looks like the following:

// XDocument xdoc = XDocument.Load(XmlReader.Create(xl.CoreFilePropertiesPart.GetStream()));
        
Stream stream = xl.CoreFilePropertiesPart.GetStream();
XDocument xdoc = XDocument.Load(XmlReader.Create(stream));
        
foreach (XElement xelem in xdoc.Descendants())
{
    // Code omitted.
}
        
stream.Close();

5. Build your solution and test it.

Answer 2

Roll-back from .NET Core 3.0 to .NET Framework 4.7.x

This is obviously not the most desirable solution.

However, the only solution that I have found is to roll-back the application from .NET Core 3.0 and SpreadsheetLight.Core to .NET Framework 4.7.x and SpreadsheetLight.

The code posted in the question above runs without modification.

I believe this has to do with a memory leak that was fixed in System.IO.Packaging in .NET Core 3.0. This will require further investigation and probably a fix to SpreadsheetLight.Core.

Answer 3

A bit late to the party but just bumped in to this problem. I solved this by create a new SLDocument and copied cell by cell from the old SLDocument. Might not work 100%, but it has covered my reports so far.

Code

using (var file = new SLDocument())
            {
                file.CopyFromTemplate(Path.Combine("ReportTemplates\\Tackningsbidrag_budget.xlsx"), maxCols: 20, maxRows: 10);

                // code

                using (var ms = new MemoryStream())
                {
                    file.SaveAs(ms);
                }
            }

Extension method:

public static void CopyFromTemplate(this SLDocument file, string pathToOrgFile, int? maxCols = null, int? maxRows = null)
    {
        using (var orgFile = new SLDocument(pathToOrgFile))
        {
            var page = orgFile.GetPageSettings();
            file.SetPageSettings(page);

            foreach (var cell in orgFile.GetWorksheetMergeCells())
            {
                file.MergeWorksheetCells(cell.StartRowIndex, cell.StartColumnIndex, cell.EndRowIndex, cell.EndColumnIndex);
            }

            var stats = orgFile.GetWorksheetStatistics();
            var endCol = stats.EndColumnIndex;
            if (maxCols.HasValue && maxCols < endCol)
            {
                endCol = maxCols.Value;
            }

            var endRow = stats.EndRowIndex;
            if (maxRows.HasValue && maxRows < endRow)
            {
                endRow = maxRows.Value;
            }
            for (int col = stats.StartColumnIndex; col <= endCol; col++)
            {
                file.SetColumnStyle(col, orgFile.GetColumnStyle(col));
                file.SetColumnWidth(col, orgFile.GetColumnWidth(col));

            }

            for (int row = stats.StartRowIndex; row <= endRow; row++)
            {
                file.SetRowStyle(row, orgFile.GetRowStyle(row));
                file.SetRowHeight(row, orgFile.GetRowHeight(row));
            }


            for (int row = stats.StartRowIndex; row <= endRow; row++)
            {
                for (int col = stats.StartColumnIndex; col <= endCol; col++)
                {
                    var formula = orgFile.GetCellFormula(row, col);
                    var stringValue = orgFile.GetCellValueAsString(row, col);
                    file.SetCellValue(row, col, !string.IsNullOrWhiteSpace(formula) ? ("=" + formula) : stringValue);

                    file.SetCellStyle(row, col, orgFile.GetCellStyle(row, col));
                }
            }
        }
    }

Answer 4

I had this error when I opened (by SpreadsheetLight) .xlsx file that was saved (by LibreOffice Calc) in "Excel 2007-365" format and then I tried use a SaveAs function. When I save (by LibreOffice Calc) the .xlsx file as "Office Open XML" then I can Open and SaveAs (by SpreadsheetLight) a .xlsx file without problems.

Answer 5

I was facing same issue after upgrading to .NET 7 from .NET 6 and resolved by using the new supported package - SpreadsheetLight.Cross.Platform 3.5.1

This thread helped me to solved it - https://github.com/dotnet/Open-XML-SDK/issues/909