Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OpenXml "Can not access a closed stream" only on production

Production Environment: Server 2008 R2, Application pool running as network service. I'm running into this issue in production on a specific line and I cannot understand why it is failing.

Log with stack trace:

2015-02-03 11:19:29,389,DEBUG,44,Before Test1
2015-02-03 11:19:29,389,DEBUG,44,Before Test2
2015-02-03 11:19:29,451,DEBUG,44,Before Getting Row
2015-02-03 11:19:29,451,DEBUG,44,After Getting Row
2015-02-03 11:19:29,826,DEBUG,44,Before Test1
2015-02-03 11:19:29,841,DEBUG,44,Before Test2
2015-02-03 11:19:29,841,DEBUG,44,Before Getting Row
2015-02-03 11:19:29,841,DEBUG,44,After Getting Row
2015-02-03 11:19:30,044,DEBUG,44,Before Test1
2015-02-03 11:19:30,060,DEBUG,44,Before Test2
2015-02-03 11:19:30,075,DEBUG,44,Before Getting Row
2015-02-03 11:19:30,075,DEBUG,44,After Getting Row
2015-02-03 11:19:30,138,DEBUG,44,Before Test1
2015-02-03 11:19:30,138,DEBUG,44,Before Test2
2015-02-03 11:19:30,356,DEBUG,44,Before Getting Row
2015-02-03 11:19:30,356,DEBUG,44,After Getting Row
2015-02-03 11:19:31,058,DEBUG,44,Before Test1
2015-02-03 11:19:31,074,DEBUG,44,Before Test2
2015-02-03 11:19:31,245,DEBUG,44,Before Getting Row
2015-02-03 11:19:31,245,DEBUG,44,After Getting Row
2015-02-03 11:19:31,729,DEBUG,44,Before Test1
2015-02-03 11:19:31,729,DEBUG,44,Before Test2
2015-02-03 11:19:31,745,DEBUG,44,Before Getting Row
2015-02-03 11:19:31,745,DEBUG,44,After Getting Row
2015-02-03 11:19:31,776,DEBUG,44,Before Test1
2015-02-03 11:19:31,791,DEBUG,44,Before Test2
2015-02-03 11:19:31,807,DEBUG,44,Before Getting Row
2015-02-03 11:19:31,807,DEBUG,44,After Getting Row
2015-02-03 11:19:31,869,DEBUG,44,Before Test1
2015-02-03 11:19:31,869,DEBUG,44,Before Test2
2015-02-03 11:19:31,885,DEBUG,44,Before Getting Row
2015-02-03 11:19:31,885,DEBUG,44,After Getting Row
2015-02-03 11:19:31,947,DEBUG,44,Before Test1
2015-02-03 11:19:31,947,DEBUG,44,Before Test2
2015-02-03 11:19:32,103,ERROR,44,Error exporting using template 
System.ObjectDisposedException: Can not access a closed Stream.
   at System.IO.Compression.DeflateStream.EnsureNotDisposed()
   at MS.Internal.IO.Packaging.CompressStream.Flush()
   at MS.Internal.IO.Zip.ZipIOLocalFileBlock.FlushExposedStreams()
   at MS.Internal.IO.Zip.ZipIOLocalFileBlock.UpdateReferences(Boolean closingFlag)
   at MS.Internal.IO.Zip.ZipIOBlockManager.SaveContainer(Boolean closingFlag)
   at MS.Internal.IO.Zip.ZipIOBlockManager.SaveStream(ZipIOLocalFileBlock blockRequestingFlush, Boolean closingFlag)
   at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Dispose(Boolean disposing)
   at System.IO.Stream.Close()
   at System.Xml.XmlUtf8RawTextWriter.Close()
   at System.Xml.XmlWellFormedWriter.Close()
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents()
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()
   at Metrico.DatumBase.BizLogic.ExporterBL.CreateSpreadsheetExportUsingTemplate(DataSet dataSet, String templatePath, String exportPath)

Code:

try
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(exportPath, true))
    {
        WorkbookPart workbookPart = document.WorkbookPart;

        // Iterate through the DataTables, and populate the existing worksheets with the same names
        for (int i = 1; i < dataSet.Tables.Count; i++)
        {
            // Grab the DataTable/Worksheet name from the first DataTable (like a Table of Contents)
            var worksheetName = dataSet.Tables[0].Rows[i - 1].Field<string>("FriendlyName");

            // Get the worksheet that has the same name
            Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == worksheetName);

            // Only attempt to process the sheet if it was found in the template
            if (theSheet != null)
            {
                // Get the worksheet to read from
                var worksheetPart = (WorksheetPart) workbookPart.GetPartById(theSheet.Id);
                string originalSheetID = workbookPart.GetIdOfPart(worksheetPart);

                // Make a copy of the worksheet to write to
                var replacementPart = workbookPart.AddNewPart<WorksheetPart>();
                string replacementPartID = workbookPart.GetIdOfPart(replacementPart);

                // Create the reader for the original and the writer for the replacement
                using (var reader = OpenXmlReader.Create(worksheetPart))
                {
                    using (var writer = OpenXmlWriter.Create(replacementPart))
                    {
                        // Get the cell formats for the first non-header row
                        WorkbookStylesPart stylesPart = workbookPart.GetPartsOfType<WorkbookStylesPart>().First();
                        Logger.Log(LogLevel.Debug, "Before Test1");
                        var test1 = worksheetPart;
                        Logger.Log(LogLevel.Debug, "Before Test2");
                        var test2 = worksheetPart.Worksheet;
                        Logger.Log(LogLevel.Debug, "Before Getting Row");
                        Row firstContentRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == 2);
                        Logger.Log(LogLevel.Debug, "After Getting Row");
                        Dictionary<string, uint> columnFormats = GetCellFormats(firstContentRow);
                        // Create a DateTime cell format style
                        var dateFormat = new CellFormat
                        {
                            NumberFormatId = UInt32Value.FromUInt32(22),
                            ApplyNumberFormat = BooleanValue.FromBoolean(true)
                        };
                        stylesPart.Stylesheet.CellFormats.Append(dateFormat);
                        UInt32Value dateStyleIndex = stylesPart.Stylesheet.CellFormats.Count;
                        stylesPart.Stylesheet.CellFormats.Count++;

                        // Read from the template worksheet and write to the new worksheet
                        while (reader.Read())
                        {
                            // We only care about altering the contents of the SheetData element
                            if (reader.ElementType == typeof (SheetData))
                            {
                                if (reader.IsEndElement)
                                {
                                    continue;
                                }

                                // Write the start of the SheetData element
                                writer.WriteStartElement(new SheetData());

                                // Add column names to the first Excel row
                                var headerRow = new Row();
                                foreach (DataColumn column in dataSet.Tables[i].Columns)
                                {
                                    var headerCell = CreateTextCell(
                                        dataSet.Tables[i].Columns.IndexOf(column) + 1, 1, column.ColumnName, null);
                                    headerRow.Append(headerCell);
                                }
                                // Write the head row element
                                writer.WriteElement(headerRow);

                                // Loop through each DataRow and populate the corresponding Excel row
                                for (int j = 0; j < dataSet.Tables[i].Rows.Count; j++)
                                {
                                    var contentRow = dataSet.Tables[i].Rows[j];

                                    Row row = CreateContentRow(contentRow, j + 2, columnFormats, dateStyleIndex);

                                    // Write the row element
                                    writer.WriteElement(row);

                                }

                                // Write the end of the SheetData element
                                writer.WriteEndElement();
                            }
                            else
                            {
                                // Automatically copy over all other elements
                                if (reader.IsStartElement)
                                {
                                    writer.WriteStartElement(reader);
                                }
                                else if (reader.IsEndElement)
                                {
                                    writer.WriteEndElement();
                                }
                            }
                        }
                    }
                }

                // Point the workbook to the new sheet and delete the old one
                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().First(s => s.Id.Value.Equals(originalSheetID));
                sheet.Id.Value = replacementPartID;
                workbookPart.DeletePart(worksheetPart);
            }
        }

        //This section will remove data from sheets that have not been populated, leaving the header row intact
        List<string> emptyTasks = new List<string>
        {
            "task",
        };

        var currentTasks =
            dataSet.Tables[0].AsEnumerable()
                .Select(x => x.Field<string>("FriendlyName").ToString(CultureInfo.InvariantCulture))
                .ToList();

        foreach (string task in currentTasks.Where(emptyTasks.Contains))
        {
            emptyTasks.Remove(task);
        }

        foreach (string task in emptyTasks)
        {
            Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == task);
            if (theSheet != null)
            {
                WorksheetPart worksheetPart = (WorksheetPart) workbookPart.GetPartById(theSheet.Id);

                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                sheetData.Elements<Row>().Where(r => r.RowIndex > 1).ToList().ForEach(x => x.Remove());
                theSheet.Elements<Row>().ToList().ForEach(x => x.Remove());

                worksheetPart.Worksheet.Save();
            }
        }

        workbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
        workbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
    }
}
catch (Exception ex)
{
    Logger.Log(LogLevel.Error, "Error exporting using template ", ex);
    throw;
}

As you can see it runs through this loop several times successfully, then it seems to just break when accessing a worksheet? WTF? This doesn't seem to happen on every server either. Any thoughts appreciated as I am out of ideas.

Also of note: It seems that something is keeping a lock on this file even after the exception is thrown. If I try to copy the file I get: The action can't be completed because the file is open in w3wp.exe." So somehow the document isn't getting disposed either?

Changing the app pool to "LocalSystem" prevents an exception from being thrown but instead causes the system to just lock up. It fails at the exact same spot but does not throw an exception and does not get any farther in the process (the file is the exact same size as the other failed files and the log is the same).

Moving the try/catch into the using statement exposes a new exception:

2015-02-04 09:45:23,577,ERROR,6,Error exporting using template 
System.IO.IsolatedStorage.IsolatedStorageException: Unable to create mutex. (Exception from HRESULT: 0x80131464)
   at System.IO.IsolatedStorage.IsolatedStorageFile.Open(String infoFile, String syncName)
   at System.IO.IsolatedStorage.IsolatedStorageFile.Lock(Boolean& locked)
   at System.IO.IsolatedStorage.IsolatedStorageFileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, IsolatedStorageFile isf)
   at System.IO.IsolatedStorage.IsolatedStorageFileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, IsolatedStorageFile isf)
   at MS.Internal.IO.Packaging.PackagingUtilities.SafeIsolatedStorageFileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, ReliableIsolatedStorageFileFolder folder)
   at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount, String& fileName)
   at MS.Internal.IO.Packaging.SparseMemoryStream.EnsureIsolatedStoreStream()
   at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary()
   at MS.Internal.IO.Packaging.DeflateEmulationTransform.Decompress(Stream source, Stream sink)
   at MS.Internal.IO.Packaging.CompressEmulationStream..ctor(Stream baseStream, Stream tempStream, Int64 position, IDeflateTransform transformer)
   at MS.Internal.IO.Packaging.CompressStream.ChangeMode(Mode newMode)
   at MS.Internal.IO.Packaging.CompressStream.Seek(Int64 offset, SeekOrigin origin)
   at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.Xml.XmlTextReaderImpl.InitStreamInput(Uri baseUri, String baseUriStr, Stream stream, Byte[] bytes, Int32 byteCount, Encoding encoding)
   at System.Xml.XmlTextReaderImpl.FinishInitStream()
   at System.Xml.XmlReaderSettings.CreateReader(Stream input, Uri baseUri, String baseUriString, XmlParserContext inputContext)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.LoadFromPart(OpenXmlPart openXmlPart, Stream partStream)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPart.LoadDomTree[T]()
   at DocumentFormat.OpenXml.Packaging.WorksheetPart.get_Worksheet()
   at Metrico.DatumBase.BizLogic.ExporterBL.CreateSpreadsheetExportUsingTemplate(DataSet dataSet, String templatePath, String exportPath)
like image 743
Shawn Avatar asked Feb 03 '15 16:02

Shawn


1 Answers

Today I read about a nasty bug inside the OpenXML SDK (2.5 and below) and it could be the source of those exceptions.

The bug only occure in a very specific scenario. System.IO.Packaging uses IsolatedStorage for files >10MB but will fail if two threads or executables try to access it.

There is a bad bug in System.IO.Packaging, which causes it in some cases to throw spurious ObjectDisposedException and NullReferenceException when used in a Web front end with Open XML functionality implemented using the Open XML SDK. The gist of this bug is that if internal memory usage of System.IO.Packaging goes above a threshold of 10MB, then System.IO.Packaging uses System.IO.IsolatedStorage, which malfunctions if two executables or threads simultaneously attempt to access it. This occurs when IsolatedStorage is used from multiple exes with the same strong name (as we will have with multiple web front end applications) or from a high-performance multi-threaded Open XML applications.

Source

Currently you need to build the SDK from the GitHub repo because there is no offical build yet.

like image 108
Robert Muehsig Avatar answered Nov 15 '22 00:11

Robert Muehsig