OpenXML Spreadsheet: Excel gives an error, then mends it and it works. (C#)

c#, excel, openxml

Solution

I went through your code and found 4 places that needed change in order to generate a fault-free excel document.

- 1. Replaced `stylesPart.Stylesheet = s.GenerateStyleSheet();` by `stylesPart.Stylesheet = new Stylesheet();`

- 2. Commented out the following : `//headerCell.StyleIndex = 1;`

- 3. Commented out the following : `//StyleIndex = 6,`

- 4. Added the following : `aantalAutosCell.DataType = CellValues.String;`

The code posted below works for me and generates an error free excel document. Few pointers you need to know

- 1. You cannot arbitrarily assign style indices when working with Open XML Docs

- 2. When ever you have a string data type to be stored you should use CellValues.String (since you don't want to use Shared String)

- 3. It's really worth while to read about SharedStrings when working with Open XML SDK. Shared Strings reduce the overall size of your file and make things so much better. See http://msdn.microsoft.com/en-us/library/office/gg278314.aspx

using System;
using System.Data;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace TestOpenXmlSDK
{
    class Excel
    {
        public void convertToExcel(string padXml)
        {
            //maak dataset en vul met ingevoerde xml
            DataSet dsXML = new DataSet();
            dsXML.ReadXml(padXml);
            DataTable tblXML = dsXML.Tables[2];

            //opslaan
            string padXlsx = Path.GetDirectoryName(padXml) + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + ".xlsx";

            //spreadsheet
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(padXlsx, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                Workbook workbook = new Workbook();
                FileVersion fv = new FileVersion();
                fv.ApplicationName = "Microsoft Office Excel";
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();

                //styles
                WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                stylesPart.Stylesheet = new Stylesheet();
                stylesPart.Stylesheet.Save();

                //kolommen
                DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns();
                for (int i = 1; i < tblXML.Columns.Count; i++)
                {
                    Column column = new Column();
                    column.Min = Convert.ToUInt32(i);
                    column.Max = Convert.ToUInt32(i + 1);
                    int lengte = tblXML.Rows[13][i - 1].ToString().Length;
                    if (lengte < 10)
                    {
                        lengte = 10;
                    }
                    else if (lengte > 35)
                    {
                        lengte = 35;
                    }
                    else
                    {
                        lengte += 5;
                    }
                    column.Width = lengte;
                    column.BestFit = true;
                    columns.Append(column);
                }
                worksheet.Append(columns);

                //header
                Row header = new Row();
                header.RowIndex = (UInt32)1;

                foreach (DataColumn columnInDataTable in tblXML.Columns)
                {
                    Cell headerCell = createTextCell(tblXML.Columns.IndexOf(columnInDataTable) + 1, 1, columnInDataTable.ColumnName);
                    //headerCell.StyleIndex = 1;
                    header.AppendChild(headerCell);
                }
                sheetData.AppendChild(header);

                // Add a row to the cell table.
                for (int i = 1; i < tblXML.Rows.Count; i++)
                {
                    Row row;
                    row = new Row() { RowIndex = Convert.ToUInt32(i) + 1 };

                    for (int j = 0; j < tblXML.Columns.Count; j++)
                    {
                        Cell newCell = new Cell()
                        {
                            CellReference = getColumnName(i),
                            DataType = CellValues.Number,
                            //StyleIndex = 6,
                            CellValue = new CellValue(tblXML.Rows[i][j].ToString()),
                        };
                        row.Append(newCell);
                    }
                    sheetData.Append(row);
                }

                //autofilter
                string laatsteKolom = zoekLaatsteKolom(tblXML);
                AutoFilter autoFilter = new AutoFilter();
                autoFilter.Reference = "A1:" + laatsteKolom + "1";

                //aantal auto's
                Row aantalAutosRow = new Row();
                aantalAutosRow.RowIndex = Convert.ToUInt32((tblXML.Rows.Count) + 2);

                Cell aantalAutosCell = new Cell();
                aantalAutosCell.CellReference = getColumnName(1);
                aantalAutosCell.CellValue = new CellValue("Hoi");
                aantalAutosCell.DataType = CellValues.String;
                //CellFormula berekenAantalAutos = new CellFormula();
                //berekenAantalAutos.Text = "=COUNTA(A2:A" + tblXML.Rows.Count.ToString();

                //aantalAutosCell.Append(berekenAantalAutos);
                aantalAutosRow.Append(aantalAutosCell);
                sheetData.Append(aantalAutosRow);

                worksheet.Append(sheetData);
                worksheet.Append(autoFilter);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet();
                sheet.Name = "Voorraad";
                sheet.SheetId = 1;
                sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
                sheets.Append(sheet);
                workbook.Append(fv);
                workbook.Append(sheets);

                spreadsheetDocument.WorkbookPart.Workbook = workbook;
                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();
            }
        }

        //kolom naam (letter) ophalen
        private string getColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > 0)
            {
                modifier = (dividend - 1) % 26;
                columnName =
                    Convert.ToChar(65 + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / 26);
            }

            return columnName;
        }

        private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;
            cell.CellReference = getColumnName(columnIndex) + rowIndex;

            InlineString inlineString = new InlineString();
            Text t = new Text();

            t.Text = cellValue.ToString();
            inlineString.AppendChild(t);
            cell.AppendChild(inlineString);

            return cell;
        }

        string zoekLaatsteKolom(DataTable tblXML)
        {
            string kolom;
            kolom = getColumnName(tblXML.Columns.Count);
            return kolom;
        }
    }
}

Problem

I narrowed it down to: ``` //aantal auto's Row aantalAutosRow = new Row(); aantalAutosRow.RowIndex = Convert.ToUInt32((tblXML.Rows.Count) + 2); Cell aantalAutosCell = new Cell(); aantalAutosCell.CellReference = getColumnName(1); aantalAutosCell.CellValue = new CellValue("Hoi"); aantalAutosRow.Append(aantalAutosCell); sheetData.Append(aantalAutosRow); ``` This generates the Cell after I click yes when Excel prompts to fix the file (sheet.xml) Complete class: ``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.IO; using System.Xml; using System.Data; namespace XML_naar_Excel { class Excel { public void convertToExcel(string padXml) { //maak dataset en vul met ingevoerde xml DataSet dsXML = new DataSet(); dsXML.ReadXml(padXml); DataTable tblXML = dsXML.Tables[2]; //opslaan string padXlsx = Path.GetDirectoryName(padXml) + "\\voorraad " + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + ".xlsx"; //spreadsheet using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(padXlsx, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); Workbook workbook = new Workbook(); FileVersion fv = new FileVersion(); fv.ApplicationName = "Microsoft Office Excel"; Worksheet worksheet = new Worksheet(); SheetData sheetData = new SheetData(); //styles Style s = new Style(); WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>(); stylesPart.Stylesheet = s.GenerateStyleSheet(); stylesPart.Stylesheet.Save(); //kolommen Columns columns = new Columns(); for (int i = 1; i < tblXML.Columns.Count; i++) { Column column = new Column(); column.Min = Convert.ToUInt32(i); column.Max = Convert.ToUInt32(i + 1); int lengte = tblXML.Rows[13][i-1].ToString().Length; if (lengte < 10) { lengte = 10; } else if (lengte > 35) { lengte = 35; } else { lengte += 5; } column.Width = lengte; column.BestFit = true; columns.Append(column); } worksheet.Append(columns); //header Row header = new Row(); header.RowIndex = (UInt32)1; foreach (DataColumn columnInDataTable in tblXML.Columns) { Cell headerCell = createTextCell(tblXML.Columns.IndexOf(columnInDataTable) + 1, 1, columnInDataTable.ColumnName); headerCell.StyleIndex = 1; header.AppendChild(headerCell); } sheetData.AppendChild(header); // Add a row to the cell table. for (int i = 1; i < tblXML.Rows.Count; i++) { Row row; row = new Row() { RowIndex = Convert.ToUInt32(i) + 1 }; for (int j = 0; j < tblXML.Columns.Count; j++) { Cell newCell = new Cell() { CellReference = getColumnName(i), DataType = CellValues.String, StyleIndex = 6, CellValue = new CellValue(tblXML.Rows[i][j].ToString()), }; row.Append(newCell); } sheetData.Append(row); } //autofilter string laatsteKolom = zoekLaatsteKolom(tblXML); AutoFilter autoFilter = new AutoFilter(); autoFilter.Reference = "A1:" + laatsteKolom + "1"; //aantal auto's Row aantalAutosRow = new Row(); aantalAutosRow.RowIndex = Convert.ToUInt32((tblXML.Rows.Count) + 2); Cell aantalAutosCell = new Cell(); aantalAutosCell.CellReference = getColumnName(1); aantalAutosCell.CellValue = new CellValue("Hoi"); //CellFormula berekenAantalAutos = new CellFormula(); //berekenAantalAutos.Text = "=COUNTA(A2:A" + tblXML.Rows.Count.ToString(); //aantalAutosCell.Append(berekenAantalAutos); aantalAutosRow.Append(aantalAutosCell); sheetData.Append(aantalAutosRow); worksheet.Append(sheetData); worksheet.Append(autoFilter); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); Sheets sheets = new Sheets(); Sheet sheet = new Sheet(); sheet.Name = "Voorraad"; sheet.SheetId = 1; sheet.Id = workbookPart.GetIdOfPart(worksheetPart); sheets.Append(sheet); workbook.Append(fv); workbook.Append(sheets); spreadsheetDocument.WorkbookPart.Workbook = workbook; spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } } //kolom naam (letter) ophalen private string getColumnName(int columnIndex) { int dividend = columnIndex; string columnName = String.Empty; int modifier; while (dividend > 0) { modifier = (dividend - 1) % 26; columnName = Convert.ToChar(65 + modifier).ToString() + columnName; dividend = (int)((dividend - modifier) / 26); } return columnName; } private Cell createTextCell(int columnIndex, int rowIndex, object cellValue) { Cell cell = new Cell(); cell.DataType = CellValues.InlineString; cell.CellReference = getColumnName(columnIndex) + rowIndex; InlineString inlineString = new InlineString(); Text t = new Text(); t.Text = cellValue.ToString(); inlineString.AppendChild(t); cell.AppendChild(inlineString); return cell; } string zoekLaatsteKolom(DataTable tblXML) { string kolom; kolom = getColumnName(tblXML.Columns.Count); return kolom; } } } ``` Can someone tell me what I'm doing that is illegal to Excel? I think excel makes SharedStrings when mending it. I never worked with SharedStrings and I hope I don't have to.

Original source