OpenXML C# Excel

Jedidia
Jedidia used Ask the Experts™
on
When using open XML to create an excel file, do you need to create rows before you can add cell data to the row?

If I simply want to add data to A7, D7, E7 do I need to create row 7 first?

Any examples on how to achieve this would be helpful.

Regards
Jed-
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes, you need to create the row before you begin to add cells. Otherwise, it doesn't know where things are supposed to be located.

The following example is from a decent tutorial series found at http://www.dispatchertimer.com/ that mirrors what I recall when I last used the library.
public void CreateExcelDoc(string fileName)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet();

        Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

        Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" };

        sheets.Append(sheet);

        workbookPart.Workbook.Save();

        List<Employee> employees = Employees.EmployeesList;

        SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

        // Constructing header
        Row row = new Row();

        row.Append(
            ConstructCell("Id", CellValues.String),
            ConstructCell("Name", CellValues.String),
            ConstructCell("Birth Date", CellValues.String),
            ConstructCell("Salary", CellValues.String));

        // Insert the header row to the Sheet Data
        sheetData.AppendChild(row);

        // Inserting each employee
        foreach (var employee in employees)
        {
            row = new Row();

            row.Append(
                ConstructCell(employee.Id.ToString(), CellValues.Number),
                ConstructCell(employee.Name, CellValues.String),
                ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String),
                ConstructCell(employee.Salary.ToString(), CellValues.Number));

            sheetData.AppendChild(row);
        }

        worksheetPart.Worksheet.Save();
    }
}

private Cell ConstructCell(string value, CellValues dataType)
{
    return new Cell()
    {
        CellValue = new CellValue(value),
        DataType = new EnumValue<CellValues>(dataType)
    };
}

Open in new window

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial