Add Cell and Row in openXML

Question

I have pre define excel format i need to pass the data to excel.I'm able to get the particular sheet .But don't know how to pass the data to cell.

var excelDocument = new ExcelDocument();
var fileName = Guid.NewGuid();
string filePath = HttpContext.Current.Server.MapPath("~/Uploads/TemplateFiles/test.xlsx");

using (SpreadsheetDocument document =
       SpreadsheetDocument.Open(filePath, false))
{
       WorkbookPart workbookPart = document.WorkbookPart;
       Workbook workbook = document.WorkbookPart.Workbook;
       string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(1).Name;
       IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Census Template for Import");
       if (sheets.Count() == 0)
       {
              // The specified worksheet does not exist.
              return null;
       }
       WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
       SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
       var excelRows = sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Row>().ToList();
       int rowindex = 10;
       foreach (var item in census)
       {
              //how to write the data in cell
              rowindex++;
       }

       worksheetPart.Worksheet.Save();
       workbookPart.Workbook.Save();
       document.Close();
       //worksheetPart.Worksheet.Save();
 }
 return filePath;

Answer 1

Here is a method for getting a cell or adding a new one, if the cell does not exists, when you know both the row and column indexes.

Note that:

• rowIndex and columnIndex should start with 1
• property RowIndex of a Row should be initialized during the creation of the row
• property CellReference of a Cell should be initialized during the creation of the cell

If RowIndex or CellReference is null, then NullReferenceException will be thrown.

private Cell InsertCell(uint rowIndex, uint columnIndex, Worksheet worksheet)
{
    Row row = null;
    var sheetData = worksheet.GetFirstChild<SheetData>();

    // Check if the worksheet contains a row with the specified row index.
    row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
    if (row == null)
    {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
    }

    // Convert column index to column name for cell reference.
    var columnName = GetExcelColumnName(columnIndex);
    var cellReference = columnName + rowIndex;      // e.g. A1

    // Check if the row contains a cell with the specified column name.
    var cell = row.Elements<Cell>()
               .FirstOrDefault(c => c.CellReference.Value == cellReference);
    if (cell == null)
    {
        cell = new Cell() { CellReference = cellReference };
        if (row.ChildElements.Count < columnIndex)
            row.AppendChild(cell);
        else
            row.InsertAt(cell, (int)columnIndex);
    }

    return cell;
}

Here you will find the code of GetExcelColumnName() method.

Answer 2

Can't tell if its a new file your creating or appending into an existing one but:

 spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
 sheet.First().Last().AppendChild(new Cell() { CellValue = new CellValue("test") });

Should work for both cases but the new cell will be put on the last active row in the first sheet.