Open Excel with Open XML SDK add a worksheet and fill it

[HansvB69]

Hi,


I want to open een excisting xlsx file, add a worksheet to it and then fille the worksheet.

See the code below. This adds a worksheet en gives is a name. But the is no data in it. (The newrow has data when ik look with de debugger).


What am i doing wrong?


private void ExportDataSetToOpenXLSX(DataTable table, string excelFileName)
{
using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(excelFileName, true))
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());

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

// sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(newWorksheetPart);

// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

// Give the new worksheet a name.
string sheetName = WorksheetName;

// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);

DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

// Construct column names
List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);

DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}

// Add the row values to the excel sheet
sheetData.AppendChild(headerRow);

foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow); // --> new row has data !
}
}
}

Continue reading...