1

I implemented this method to create an Excel spreadsheet using openxml. It works 100% of the time when the number of rows is under around 15000 rows, otherwise the Excel will sometimes come out corrupted with Excel showing a message

The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt

Any explanations please?

    public static SaveResults CreateExcelDocument(Stream excelStream, List<ExportColumns> columns, IEnumerable data, string sheetName)
    {
        SaveResults results = new SaveResults();
        results.Status = SAVE_STATUS.ERROR;

        try
        {
            if ((sheetName == null) || (sheetName.Trim() == ""))
                sheetName = "Report";
        }
        catch { }

        try
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelStream, SpreadsheetDocumentType.Workbook))
            {

                // Add a WorkbookPart to the document.
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                SheetData sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                // Add Sheets to the Workbook.
                Sheets sheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                Sheet sheet = new Sheet()
                {
                    Id = workbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = sheetName
                };

                //get the type of the object in the list
                Type myListElementType = data.GetType().GetGenericArguments().Single();

                // Constructing header
                Row row = new Row();

                foreach (ExportColumns exportColumn in columns)
                {
                    if (!string.IsNullOrEmpty(exportColumn.display.Trim()))
                    {
                        row.Append(ConstructCell(exportColumn.display, CellValues.String));
                    }
                }

                // Insert the header row to the Sheet Data
                sheetData.AppendChild(row);


                foreach (var obj in data)
                {
                    row = new Row();

                    foreach (ExportColumns exportColumn in columns)
                    {
                        if (!string.IsNullOrEmpty(exportColumn.display.Trim()))
                        {
                            object cellObject = null;

                            try
                            {
                                if (myListElementType.GetProperty(exportColumn.name) == null)
                                {
                                    cellObject = ((IDictionary<string, object>)obj)[exportColumn.name];
                                }
                                else
                                {
                                    cellObject = myListElementType.GetProperty(exportColumn.name).GetValue(obj);
                                }

                                //if the value is null bad things happen so put in a cell with a null value string
                                if (cellObject == null)
                                {
                                    // create cell with data
                                    row.Append(
                                    ConstructCell(null, CellValues.String)
                                    );
                                    continue;
                                }

                                if (cellObject is DateTime)
                                {
                                    // create cell with data
                                    DateTime dtValue = (DateTime)cellObject;
                                    row.Append(
                                    ConstructCell(dtValue.ToOADate().ToString(), CellValues.String)
                                    );
                                }
                                else if (IsNumber(cellObject))
                                {
                                    // create cell with data
                                    row.Append(ConstructCell(cellObject.ToString(), CellValues.Number));
                                }
                                else
                                {
                                    // create cell with data
                                    row.Append(ConstructCell(SanitizeXmlString(cellObject.ToString()), CellValues.String));
                                }
                            }
                            catch (Exception ex2)
                            {
                                row.Append(ConstructCell(SanitizeXmlString(exportColumn.path.Trim()), CellValues.String));

                            }
                        }
                    }
                    sheetData.AppendChild(row);
                }

                sheets.Append(sheet);

                worksheetPart.Worksheet.Save();
                document.Close();
            }

            results.Status = SAVE_STATUS.OK;
        }
        catch (Exception ex)
        {
            Logger.Exception.Error("Export Error", ex);

            results.Exception = new ExceptionData(ex);
            results.Status = SAVE_STATUS.ERROR;
        }

        return results;
    }

    private static Cell ConstructCell(string value, CellValues dataType)
    {
        return new Cell()
        {
            CellValue = new CellValue(value),
            DataType = new EnumValue<CellValues>(dataType)
        };
    }
Slothman
  • 11
  • 2
  • Couple questions: Firstly, which version of the Open XML SDK are you using? Secondly, have you ever looked at the Open XML markup before and (!) after opening and saving the workbook with Excel? You would see that Excel is always using the shared string table to store strings, while you are using a cell data type that Excel would never use (although the Open XML standard clearly allows it). I am only guessing that Excel might run into a problem with large numbers of rows generated in this fashion. Thus, I'd try to do it the Excel way and see what happens. Can you do that? – Thomas Barnekow Feb 27 '20 at 18:49

0 Answers0