none
XLSX file is corrupt RRS feed

  • Question

  • This isn't quite production code, but trying to work through the examples to have something usable.

    This generates a file to save with an xlsx extension. It opens in open office, but excel provides an error, "The file is corrupt and cannot be opened."

    I'm running excel office 365 version 1902

    A sample method as part of an MVC controller.

    public ActionResult Export()
    {
            using (MemoryStream stream = new MemoryStream())
            {
                Spreadsheet sheet = new Spreadsheet(stream);
                sheet.TestSheet();
                sheet.SaveSheet();
                stream.Position = 0;
                byte[] xlsxBytes;
                xlsxBytes = stream.ToArray();
                return File(xlsxBytes, sheet.MimeType);
            }
    }

    Sample spreadsheet code

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
     
    namespace MyNamespace
    {
        public class Spreadsheet
        {
            public SpreadsheetDocument SpreadsheetDocument;
            public WorkbookPart WorkbookPart;
            public WorksheetPart WorksheetPart;
            public SharedStringTablePart ShareStringPart;
            public Sheets Sheets;
            public string MimeType = "	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            public Spreadsheet(MemoryStream stream)
            {
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
     
                // Add a WorkbookPart to the document.
                WorkbookPart = SpreadsheetDocument.AddWorkbookPart();
                WorkbookPart.Workbook = new Workbook();
     
                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart = InsertWorksheet(WorkbookPart);
                GetSharedStringItem();
                WorkbookPart.Workbook.Save();
            }
     
            // Given a WorkbookPart, inserts a new worksheet.
            private WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
     
                Sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
     
                if (Sheets == null)
                {
                    Sheets = SpreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
     
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
     
                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (Sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = Sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
     
                string sheetName = "Sheet" + sheetId;
     
                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                Sheets.Append(sheet);
                workbookPart.Workbook.Save();
     
                return newWorksheetPart;
            }
     
            public void GetSharedStringItem()
            {
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                if (SpreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                {
                    ShareStringPart = SpreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                }
                else
                {
                    ShareStringPart = SpreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
                }
            }
      
            public void TestSheet()
            {
                string[] columns = { "A""B""C""D""E""F""G""H""I""J""K""L""M" };
     
                for (int rowIndex = 0; rowIndex < 1; rowIndex++)
                {
                    Row row = new Row() { RowIndex = (UInt32)rowIndex };
                    string[] colValues =
                    {
                        "something"
                    };
                    for (int colIndex = 0; colIndex < colValues.Count(); colIndex++)
                    {
                        AddCell(colValues[colIndex], columns[colIndex], (uint)rowIndex);
                    }
                }
            }
     
            public void AddCell(string text, string colName, uint rowIndex)
            {
     
                // Insert the text into the SharedStringTablePart.
                int index = InsertSharedStringItem(text, ShareStringPart);
                Cell cell = InsertCellInWorksheet(colName, rowIndex, WorksheetPart);
     
                // Set the value of the cell
                cell.CellValue = new CellValue(index.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
     
                // Save the new worksheet.
                WorksheetPart.Worksheet.Save();
            }
     
            // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
     
                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row;
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
     
                // If there is not a cell with the specified column name, insert one.  
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                {
                    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
                }
                else
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true> 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
     
                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);
     
                    worksheet.Save();
                    return newCell;
                }
            }
     
            // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
            // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
            private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
     
                int i = 0;
     
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
     
                    i++;
                }
     
                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
     
                return i;
            }
     
            public void SaveSheet()
            {
                WorkbookPart.Workbook.Save();
                
                // Close the document.
                this.SpreadsheetDocument.Close();
            }
        }
    }


    • Edited by Joshua125 Wednesday, September 4, 2019 2:45 PM
    Tuesday, September 3, 2019 9:59 PM

All replies

  • I believe I found the issue here. The row numbers are a 1 based index not a 0 based index.
    Thursday, September 5, 2019 6:51 PM