Excel add data to WorksheetPart in code behind

.net, c#, excel, openxml, openxml-sdk

Solution

private static void InsertValuesInWorksheet(WorksheetPart worksheetPart, IEnumerable<string> values)
{
    var worksheet = worksheetPart.Worksheet;
    var sheetData = worksheet.GetFirstChild<SheetData>();
    var row = new Row { RowIndex = 1 };  // add a row at the top of spreadsheet
    sheetData.Append(row);

    int i = 0;
    foreach (var value in values)
    {
        var cell = new Cell
                            {
                                CellValue = new CellValue(value),
                                DataType = new EnumValue<CellValues>(CellValues.String)
                            };

        row.InsertAt(cell, i);
        i++;
    }
}

This method will add a new row to a specified worksheet and fill the row's cells with the values from an array. In your code sample, it could be called like this:

var values = new[] {"foo", "bar", "baz"};

InsertValuesInWorksheet(newWorksheetPart1, values);
InsertValuesInWorksheet(newWorksheetPart2, values);
InsertValuesInWorksheet(newWorksheetPart3, values);

Problem

Hello guys I am creating an Excel file with 3 worksheets in the following code. ``` using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(path + @"\UrlReport.xlsx", SpreadsheetDocumentType.Workbook)) { // create the workbook spreadSheet.AddWorkbookPart(); spreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // CREATE FIRST SHEET WorksheetPart newWorksheetPart1 = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart1.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(); // create sheet data newWorksheetPart1.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // save worksheet newWorksheetPart1.Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); spreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart1), SheetId = 1, Name = "Sheet1" }); //CREATE SECOND SHEET WorksheetPart newWorksheetPart2 = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart2.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(); // create sheet data newWorksheetPart2.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // save worksheet newWorksheetPart2.Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart2), SheetId = 2, Name = "Sheet2" }); //CREATE THIRD SHEET WorksheetPart newWorksheetPart3 = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart3.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(); // create sheet data newWorksheetPart3.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // save worksheet newWorksheetPart3.Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart3), SheetId = 3, Name = "Sheet3" }); spreadSheet.WorkbookPart.Workbook.Save(); } ``` I need to know how I can populate the cells of the 3 datasheets. I need to just add strings to some cells that are probably going to be in some datatable or multidimensional array.

Original source