Production environment: 2008 R2 server, application pool running as a network service. I am facing this problem in production on a specific line, and I do not understand why it fails.
Stack Trace Log:
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 goes through this cycle several times successfully, then it seems to just break when accessing the sheet? WTF? This does not seem to happen on every server. Any thoughts appreciated as I am beyond ideas.
Also note: something seems to be blocking this file even after an exception. If I try to copy the file I receive: the action cannot be completed because the file is open in w3wp.exe. "So, somehow the document cannot be deleted?
Changing the application pool to "LocalSystem" prevents an exception from being thrown, but instead forces the system to simply block. It fails in the same place, but does not throw an exception and does not go any further (this file is the same size as other files with an error, and the log is the same).
Moving a try / catch to a using statement raises 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)