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)
Shawn1 Answer
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.
Currently you need to build the SDK from the GitHub repo because there is no offical build yet.
answered on Stack Overflow Jul 22, 2015 by 
Robert Muehsig
Robert MuehsigUser contributions licensed under CC BY-SA 3.0