Search code examples
epplus

How do I append a new cell to a row?

I can't seem to find any examples that allow me to add to a row without specifying an index.

For example, to set the value of a cell you have to do:

sheet.Cells["A1"].Value = "Hello World!";

However, is there a way where I can just append a new cell to a row? Something like this:

row.Append("Hello World!");
Solution

  • It's unfortunately not that easy, but there is a way to achieve it.

    The first thing you need to do is find the last cell (row or column or both) that contains a value. EPPlus' ExcelRange object implements IEnumerable so you can use linq to get the cell address.

    Given row number 3, you can get the last cell in a row with a value like this:

    var lastCellInRowWithValue = sheet.Cells.Last(c => c.Start.Row == 3);

    Or column 4:

    var lastCellInColumnWithValue = sheet.Cells.Last(c => c.Start.Column == 4);

    This will give you the cells address e.g. "G3". You can use this address to extract the integer values for row/column, which can then be used to set the next empty cell to write a value in.

    int rowNumber = lastCellInRowWithValue.Start.Row;
int columnNumber = lastCellInRowWithValue.Start.Column;

    With these values, you can 'append' a value in the row's next empty cell, by adding 1 to the column value:

    sheet.Cells[rowNumber, columnNumber + 1].Value = "Hello World!";