Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Excel openxml
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.
Posted 26-Apr-13 0:33am
Comments
CHill60 at 26-Apr-13 10:29am
   
Use the Improve question link to state what you problem is and exactly what the error message is that's reported

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 480
1 Maciej Los 304
2 Richard MacCutchan 225
3 BillWoodruff 169
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,759
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,269
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100