asp.net OpenXML insert data to cell

Question

I've been following this guide https://msdn.microsoft.com/EN-US/library/office/cc861607.aspx to insert a string to a cell in an excel file. The example is really confusing, and even when i copy/paste it doesn't work. I'm looking for a very simple example to insert a a value into a cell like:

spredSheet.InsertCell("A", 1, string value)

I could really use a simple code example showing me how to insert data into a cell using OpenXML in asp.net.

I tried the code from this post Using OpenXML to insert a datatable into excel, but it creates a broken excel file. This is how my code look without the helper functions from the link

using (SpreadsheetDocument myDoc = SpreadsheetDocument.
            Create(Server.MapPath("/data.xls"), SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookpart = myDoc.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            //add column names to the first row  
            Row header = new Row();
            header.RowIndex = (UInt32)1;
            SheetData sheetData = new SheetData();


                Cell headerCell = createTextCell(1, 1, text);
                header.AppendChild(headerCell);

            sheetData.AppendChild(header);
            // Add a WorkbookPart to the document.
            worksheetPart.Worksheet = new Worksheet(sheetData);
        }

Answer 1

The MSDN example is using SpreadsheetDocument.Open which opens an existing file but you are creating a brand new file with SpreadsheetDocument.Create. When you create a brand new file there are certain elements you must create in order for the file to be valid. The elements you are missing are the Sheets and Sheet elements.

Sheets are stored separately from the SheetData so you need to create a Sheet inside a Sheets and then associate the Sheets with the WorksheetPart.

This can be done like so:

Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
    Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()),
    SheetId = 1,
    Name = "Sheet1"
});

So your full code listing would be something like:

using (SpreadsheetDocument myDoc = SpreadsheetDocument.
       Create(Server.MapPath("/data.xls"), SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workbookpart = myDoc.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    //add column names to the first row  
    Row header = new Row();
    header.RowIndex = (UInt32)1;
    SheetData sheetData = new SheetData();

    Cell headerCell = createTextCell(1, 1, text);
    header.AppendChild(headerCell);

    sheetData.AppendChild(header);
    // Add a WorkbookPart to the document.
    worksheetPart.Worksheet = new Worksheet(sheetData);

    //this is the part that was missing from your code
    Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
    sheets.AppendChild(new Sheet()
    {
        Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()),
        SheetId = 1,
        Name = "Sheet1"
    });
}