CODEWITHSUNDEEP
c#.netopenxml.net-standard
Joe Higley
Joe Higley

Reputation: 1842

OpenXML file needs repair when opened in Excel

I'm working on a function that will convert a DataTable to a .xlsx file using OpenXML. I'm able to create the .xlsx file just fine and export it, but when I open it in Excel, I get the following error:

Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

I've tried finding a solution from similar issues, but nothing has worked.

Here's my code:

public MemoryStream ConvertToXLSX(DataTable table, string sheetName)
{
    MemoryStream excelStream = new MemoryStream();

    using (var workbook = SpreadsheetDocument.Create(excelStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
        var workBookPart = workbook.AddWorkbookPart();
        workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
        workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

        uint sheetID = 1;

        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
        var sheetData = new SheetData();
        sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

        DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetID = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetID, Name = sheetName };
        sheets.Append(sheet);

        Row headerRow = new Row();

        List<string> columns = new List<string>();

        foreach (DataColumn column in table.Columns)
        {
            columns.Add(column.ColumnName);
            Cell cell = new Cell();
            cell.DataType = CellValues.SharedString;
            cell.CellValue = new CellValue(column.ColumnName);
            headerRow.AppendChild(cell);
        }

        sheetData.AppendChild(headerRow);

        foreach (DataRow tabRow in table.Rows)
        {
            Row newRow = new Row();

            foreach (String col in columns)
            {
                var val = tabRow[col];

                Cell cell = new Cell();
                cell.DataType = GetCellValueType(val.GetType());
                cell.CellValue = new CellValue(val.ToString());
                newRow.AppendChild(cell);
            }

            sheetData.AppendChild(newRow);
        }

        workbook.WorkbookPart.Workbook.Save();
    }

    return excelStream;
}

Here's an example of the data once it's been opened in Excel:

enter image description here

I have tried opening the file in the Open XML SDK 2.5 Productivity tool and I do not get any validation errors.

Here's an screenshot of the xml hierarchy:

enter image description here

Upvotes: 3

Views: 8509

Answers (1)

petelids
petelids

Reputation: 12815

You are outputting the wrong cell.DataType. In your headers you are outputting CellValues.SharedString but then adding the string directly to the cell. You need to set it to CellValues.String.

It's hard to tell what you're outputting for the rest of the cells as you're calling GetCellValueType(val.GetType()) which isn't listed but again, you will need to avoid using CellValues.SharedString if you are writing out a string.

The CellValues.SharedString should only be used if you write the value itself to the SharedStringTable.

Upvotes: 4

Related Questions