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);

score 2 · Accepted Answer · edited Mar 04 '23 at 21:21

Here's how I solved it.

Downloaded the source code for SpreadsheetLight (version 3.5). http://spreadsheetlight.com/downloads/SpreadsheetLight3.5.zip
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.
Added the project "SpreadsheetLight" to my solution and referenced it in one of the existing projects.
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();

Build your solution and test it.

I've contacted the owner of this lib to let him know this problem, but I am not sure that he is really active on this project. — mababin, Oct 20 '21 at 14:24
@mababin we have new nuget package which has these changes. - SpreadsheetLight.Cross.Platform 3.5.1 — H_H, Mar 23 '23 at 12:13

score 2 · Answer 2 · answered Dec 02 '21 at 07:45

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.

score 1 · Answer 3 · answered Nov 23 '19 at 15:21

1

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.

answered Nov 23 '19 at 15:21

Doug Kimzey

1,019
2
17
32

This appears to be true. I tried different types of streams, as well as saving to a file name. All produce this same error message in .SpreadSheetLight.Core. However, if you really want to move to .NET Core, EPPlus works well, https://epplussoftware.com/. The usage is similar, and similarly simple. There is a true LGPL version, thought the latest version requires paid licensing for commercial use. – Tony Isaac Jun 09 '20 at 01:08
for tracking https://github.com/dotnet/runtime/issues/23750 – BozoJoe Jun 30 '20 at 00:02

score 0 · Answer 4 · answered Apr 08 '21 at 14:45

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));
                }
            }
        }
    }

score 0 · Answer 5 · answered Mar 23 '23 at 12:08

0

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

answered Mar 23 '23 at 12:08

H_H

1,460
2
15
30

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

5 Answers5

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