CODEWITHSUNDEEP
c#openxmlxlsx
eidylon
eidylon

Reputation: 7238

How to add rows/cells in DocumentFormat.OpenXml Spreadsheet?

I have a program which needs to take in a set of data, and write it out to a new XLSX spreadsheet. I'm currently using the DocumentFormat.OpenXml package from NuGet. The program is running fine to completion, not receiving any errors. However when I check the XLSX file, the workbook exists, the worksheet exists, and the worksheet has gotten the name assigned to it in code.

BUT... none of the rows/cell data has actually been written to the file. All the cells in the worksheet are empty.

Here is my current code that does the actual XLSX stuff...
(file, being passed to SpreadsheetDocument.Create is a string, with the full path.)

using(SpreadsheetDocument doc = SpreadsheetDocument.Create(file, SpreadsheetDocumentType.Workbook))
{
    WorkbookPart wbp = doc.AddWorkbookPart();
    wbp.Workbook = new Workbook();
    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
    wsp.Worksheet = new Worksheet(new SheetData());
    var ws = wsp.Worksheet;
    Sheets sheets = wbp.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet() { Id = wbp.GetIdOfPart(wsp), SheetId = 1, Name = batchinfo.Field<string>("NAME") };
    sheets.Append(sheet);

    if(!exhdr) 
    {
        var row = ws.AppendChild(new Row());
        foreach(DataRow dr in hdrs) 
        {
            var cell = row.AppendChild(new Cell());
            cell.CellValue = new CellValue(dr.Field<string>("COLHEADER"));
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
        }
    }

    var loans = (from DataRow dr in data.Rows
                 group dr by dr.Field<long>("L_ROWID") into g
                 select new { LoanId = g.Key, Rows = g.OrderBy(r => r.Field<double>("ORDERIDX")).ToList()}
                 );

    foreach(var loan in loans) 
    {
        var row = ws.AppendChild(new Row());
        foreach (DataRow dr in loan.Rows)
        {
            var cell = row.AppendChild(new Cell());
            cell.CellValue = new CellValue(dr.Field<string>("DATAOUT"));
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
        }
    }
    wbp.Workbook.Save();
}

I have also tried modifying the code to create the row or cell object, assign all the properties, and THEN append it to the parent, like so... 

var row = new Row();
foreach (DataRow dr in loan.Rows) {
    var cell = new Cell();
    cell.CellValue = new CellValue(dr.Field<string>("DATAOUT"));
    cell.DataType = new EnumValue<CellValues>(CellValues.String);
    row.Append(cell);
}
ws.Append(row);

Any clue as to what I am doing wrong here?
Like I said, the file and the worksheet in it both seem to get created fine.
It's just the cells are all missing in action.

UPDATE: I saw in one example I found, there is also a Save() method on the Worksheet object. I added that in just before the Workbook.Save(), but it didn't make any difference.

Or maybe there is a better library to use?

Upvotes: 2

Views: 6039

Answers (1)

eidylon
eidylon

Reputation: 7238

Got it! Here is the solution in case anyone else comes looking...

This line... 

var row = ws.AppendChild(new Row());

needs to actually read as so... 

var row = ws.GetFirstChild<SheetData>().AppendChild(new Row());

Upvotes: 3

Related Questions