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;

Misunderstanding

Check this technet.weblineindia.com/web/… – Cheney

can you tell me what is the error u r getting – Cheney

index issue..but i try..not able to fix it – Misunderstanding

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.

Lipps

This is wrong. If columnIndex was 10, and the row already contained a cell where columnIndex is 100, this code appends the new cell at the end of the row where it should've been inserted before the existing cell. – Bylaw

Answer 2

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.

Pu

For me the top answer corrupted the Excel file, I couldn't get it to work. In the link you posted I just grabbed the InsertCellInWorksheet method that they provide, that did the trick, thanks. – Markle

Actually, although from Microsoft, that article is wrong too. It inserts cell alphabetically by their cell reference. That would put AA1 before Z1. In that case, Excel will complain the file is corrupt. Oh how hard it is to find good documentation on OpenXML! – Bylaw

Answer 3

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.

Henning

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

Inez