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.

Answer 3

I had the same issue that you had and this article How to: Insert text into a cell in a spreadsheet document (Open XML SDK). I guess you need to insert a new Cell object into your worksheet and then insert the specified data (assuming it is a string or that it has already being cast into a string) into that cell.

Answer 4

Seems you define rowindex=10, there are two way to add rows. If row 10 is last row in your excel then you can simply append new row like:

foreach (var item in census)
       {
              //how to write the data in cell
              Row row = new Row(); 
              row.RowIndex = (UInt32)rowindex;
              Cell cell = new Cell()  
              {  

                DataType = CellValues.String,  
                CellValue = new CellValue("value")  
              };
              row.Append(cell);
              sheetData.Append(row);
              rowindex++;
       }

If there are rows after row 10 then you have to use insert,then manually change rows and cells after row 10 index to the right index value like:

    foreach (var item in census)
               {
                //how to write the data in cell
                Row refRow = GetRow(sheetData, rowIndex);
                ++rowIndex;

                Cell cell1 = new Cell() { CellReference = "A" + rowIndex };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = "";
                cell1.Append(cellValue1);
                Row newRow = new Row()
                {
                    RowIndex = rowIndex
                };
                newRow.Append(cell1);
                for (int i = (int)rowIndex; i <= sheetData.Elements<Row>().Count(); i++)
                {
                    var row = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == i).FirstOrDefault();
                    row.RowIndex++;
                    foreach (Cell c in row.Elements<Cell>())
                    {
                        string refer = c.CellReference.Value;
                        int num = Convert.ToInt32(Regex.Replace(refer, @"[^\d]*", ""));
                        num++;
                        string letters = Regex.Replace(refer, @"[^A-Z]*", "");
                        c.CellReference.Value = letters + num;
                    }
                }
                sheetData.InsertAfter(newRow, refRow);
                      rowindex++;
               }
static Row GetRow(SheetData wsData, UInt32 rowIndex)
    {
        var row = wsData.Elements<Row>().
        Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
        if (row == null)
        {
            row = new Row();
            row.RowIndex = rowIndex;
            wsData.Append(row);
        }
        return row;
    }

This is a prototype. You might need to change some code or variable name to fit your project.

References:

append rows in Excel by OpenXML

insert rows in Excel by OpenXML