Unable to generate Excel file with over 695 rows

[Ruben]

Hi, I'm using the Spread Stream Processing to generate an excel file with 93 columns. All the columns are formatted as strings with an average size of approximately 10-20 characters. I implemented this feature because I need to create excel files of approximately 60k - 80k rows. Currently, I receive an exception when exporting data with 695 rows or more. 

This is the exception I receive: 

Data: System.Collections.ListDictionaryInternal
Message: The Writer is closed or in error state.
Source: System.Xml
Stack Trace: at System.Xml.XmlWellFormedWriter.AdvanceState(Token token) at System.Xml.XmlWellFormedWriter.WriteEndElement() at Telerik.Documents.SpreadsheetStreaming.ImportExport.Core.ConsecutiveElementBase.EndWriteElement() at Telerik.Documents.SpreadsheetStreaming.ImportExport.Core.ConsecutiveElementBase.EnsureWritingEnded() at Telerik.Documents.SpreadsheetStreaming.ImportExport.Core.ConsecutiveElementBase.EnsureWritingEnded() at Telerik.Documents.SpreadsheetStreaming.ImportExport.Core.ConsecutivePartWriterBase`1.EndWrite() at Telerik.Documents.SpreadsheetStreaming.ImportExport.Core.ConsecutivePartWriterBase`1.CompleteWriteOverride() at Telerik.Documents.SpreadsheetStreaming.Core.EntityBase.Dispose(Boolean disposing) at Telerik.Documents.SpreadsheetStreaming.XlsxWorksheetExporter.DisposeOverride() at Telerik.Documents.SpreadsheetStreaming.Core.EntityBase.Dispose(Boolean disposing) at Telerik.Documents.SpreadsheetStreaming.Core.EntityBase.Dispose() at ERMgtProgram.Controllers.DigitalMerchandising.ViewAllExportController.<GenerateDocument>d__4.MoveNext() in C:\Users\erit12u\source\repos\ERMgtProgram\ERMgtProgram\Controllers\DigitalMerchandising\Import\ViewAllExportController.cs:line 107
Target Site: Void AdvanceState(Token)

 

This is the code that generates the exception: 

01.// When this code is called for the 695th time it throws an exception at line 2
02.using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
03.{
04.    rowExporter.SetHeightInPoints(rowHeight);
05.
06.    foreach(var column in template)
07.    {
08.        using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
09.        {
10.            var val = entities[rowIndex].Properties.Keys.Contains(column.FinalColumnName) ? entities[rowIndex].Properties[column.FinalColumnName].StringValue : string.Empty;
11.            cellExporter.SetValue(val);
12.            cellExporter.SetFormat(normalFormat);
13.        }
14.    }
15.}

 

 

This is all of my relevant source code: 

001.[HttpPost]
002.public async Task<ActionResult> GenerateDocument()
003.{
004.    byte[] renderedBytes = null;
005.    var mimeType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet"; ;
006.    var fileExtension = "xlsx";
007.    try
008.    {
009.        // retrieve database data
010.        var tableName = _dmP3AzTableName;
011.        // retrieve all entities in azure table
012.        CloudStorageAccount storageAccount = _storageConn.GetStorageConnection();
013.        CloudTable table = storageAccount.CreateCloudTableClient().GetTableReference(tableName);
014.        table.CreateIfNotExists();
015.        var entities = await AzureOperation.RetrieveEntitiesAsync(table, null, 1000);
016.        if (entities == null)
017.        {
018.            TempData["ErrorMessage"] = "Unable to retrieve records. Please try again later.";
019.            return RedirectToAction("Index", "ImportPhaseThree");
020.        }
021.        var template = new List<TemplateSupport>();
022.        using (var db = new DataMgtEntities())
023.        {
024.            template = db.TemplateSupports.OrderBy(p => p.PageOrder).ToList();
025.        }
026.        var headerRowHeight = 15;
027.        var columnHeaders = template.Select(p => p.ImportColumnName).ToArray();
028.        var columnWidthsList = new List<double>();
029.        foreach (var header in columnHeaders)
030.        {
031.            columnWidthsList.Add(25.0);
032.        }
033.        var columnWidths = columnWidthsList.ToArray();
034.        var rowHeight = 15;
035.        MemoryStream documentStream = new MemoryStream();
036.        //documentStream.SetLength(int.MaxValue/10);
037.        var selectedDocumentFormat = SpreadDocumentFormat.Xlsx;
038.
039.        using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(selectedDocumentFormat, documentStream))
040.        {
041.            using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter("Phase 3 All Records"))
042.            {
043.                for (int i = 0; i < columnWidths.Length; i++)
044.                {
045.                    using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
046.                    {
047.                        columnExporter.SetWidthInCharacters(columnWidths[i]);
048.                    }
049.                }
050.
051.                ExportHeaderRows(worksheetExporter, headerRowHeight, columnHeaders);
052.                SpreadCellFormat normalFormat = new SpreadCellFormat
053.                {
054.                    FontSize = 11,
055.                    VerticalAlignment = SpreadVerticalAlignment.Bottom,
056.                    HorizontalAlignment = SpreadHorizontalAlignment.Left
057.                };
058.
059.                for (var rowIndex = 0; rowIndex < entities.Count(); rowIndex++)
060.                {
061.                    using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
062.                    {
063.                        rowExporter.SetHeightInPoints(rowHeight);
064.
065.                        foreach(var column in template)
066.                        {
067.                            using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
068.                            {
069.                                var val = entities[rowIndex].Properties.Keys.Contains(column.FinalColumnName) ?
070.                                    entities[rowIndex].Properties[column.FinalColumnName].StringValue : string.Empty;
071.                                cellExporter.SetValue(val);
072.                                cellExporter.SetFormat(normalFormat);
073.                            }
074.                        }
075.                    }
076.                }
077.            }
078.        }
079.        renderedBytes = documentStream.ToArray();
080.    }
081.    catch (Exception ex)
082.    {
083.        TempData["ErrorMessage"] = "An exception occurred. Please see error log for details, and try again later.";
084.        ErrLog.LogError(ex, "ViewAllExport.GenerateDocument");
085.        return RedirectToAction("ViewAll", "ImportPhaseThreeExports");
086.    }
087.    return File(renderedBytes, mimeType, "Phase3AllData." + fileExtension);
088.}
089.
090.private void ExportHeaderRows(IWorksheetExporter worksheetExporter, int HeaderRowHeight, string[] ColumnHeaders)
091.{
092.    try
093.    {
094.        using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
095.        {
096.            rowExporter.SetHeightInPoints(HeaderRowHeight);
097.
098.            SpreadCellFormat format = new SpreadCellFormat
099.            {
100.                FontSize = 11,
101.                Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(122, 122, 122)),
102.                ForeColor = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
103.                HorizontalAlignment = SpreadHorizontalAlignment.Left,
104.                VerticalAlignment = SpreadVerticalAlignment.Bottom
105.            };
106.
107.            for (int i = 0; i < ColumnHeaders.Length; i++)
108.            {
109.                using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
110.                {
111.                    cellExporter.SetFormat(format);
112.                    cellExporter.SetValue(ColumnHeaders[i]);
113.                }
114.            }
115.        }
116.    }
117.    catch(Exception ex)
118.    {
119.        ErrLog.LogError(ex, "ViewAllExport.ExportHeaderRows");
120.    }
121.}

 

Any and all help is appreciated. This is a time sensitive concern as I have to find a solution for this issue ASAP. Thank you for your time.

Best,

Ruben

[Tanya]

Hi Ruben,

Thank you for sharing the code.

I modified it a bit so I can build it without using the missing classes an created a sample project that generates a document containing 1000 rows with 100 cells. This way, I was able to successfully export and open the generated document. Could you please check the test project and let me know if I am missing something from the setup on your end?

Regards,
Tanya
Progress Telerik