Is there any way that I can expand an existing table in EPPlus in C#. The way my program works is I create the table with only 2 rows and keep adding more. I just can't seem to find any sort of resize method in ExcelTable. Is there a dedicated method for this, or do I need to use some sort of alternative?
Edit: Okay I'm just going to clarify this. I already have EPPlus working and making an ExcelTable. My question is: How can I make an existing ExcelTable larger (add more rows)?
Answer
Unfortunately, there is no direct method. Not quite sure why but the developers of EPPlus choose to make the ExcelTable.Address property ready only. So the most obvious choose would be to create a new table, copy all of the properties (assuming you know all of them that are needed) and delete the old one. Not very ideal since you could miss something.
But I see two other others - next exactly pretty either but better then the copy/delete. Assume a table like so:
Option 1 you could fork the EPPlus source code and make the property needed read/write. You would do this in the file ExcelTable.cs by changing:
public ExcelAddressBase Address
{
get
{
return _address;
}
internal set //REMOVE internal KEYWORD
{
_address = value;
SetXmlNodeString("@ref",value.Address);
WriteAutoFilter(ShowTotal);
}
}
by removing the internal keyword. But of course you will have to be careful doing so does not break something else along the way. With this, you can do something like:
var fileInfo = new FileInfo(@"c:\temp\Expand_Table.xlsx");
using (var pck = new ExcelPackage(fileInfo))
{
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets.First();
//Added 11th data row assuming the table is from A1 to C11 (Header row + 10 data rows)
worksheet.Cells["A12"].Value = 10;
worksheet.Cells["B12"].Value = 100;
worksheet.Cells["C12"].Value = Path.GetRandomFileName();
var tbl = worksheet.Tables["TestTable1"];
var oldaddy = tbl.Address;
var newaddy = new ExcelAddressBase(oldaddy.Start.Row, oldaddy.Start.Column, oldaddy.End.Row + 1, oldaddy.End.Column);
tbl.Address = newaddy;
pck.Save();
}
Option 2 would be the safer option but most "dirty" by using a string replace on the XML. We cant assume there is only one reference to the address because, for example, the AutoFilter could be turned on. Here is what the default XML will look like when you create a table in excel (note the 2 references to the address):
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="3" name="TestTable1" displayName="TestTable1" ref="A1:C11" totalsRowShown="0">
<autoFilter ref="A1:C11" />
<tableColumns count="3">
<tableColumn id="1" name="Col1" />
<tableColumn id="2" name="Col2" />
<tableColumn id="3" name="Col3" />
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
</table>
So we can do a string replace like this:
var fileInfo = new FileInfo(@"c:\temp\Expand_Table.xlsx");
using (var pck = new ExcelPackage(fileInfo))
{
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets.First();
//Added 11th data row assuming the table is from A1 to C11 (Header row + 10 data rows)
worksheet.Cells["A12"].Value = 10;
worksheet.Cells["B12"].Value = 100;
worksheet.Cells["C12"].Value = Path.GetRandomFileName();
var tbl = worksheet.Tables["TestTable1"];
var oldaddy = tbl.Address;
var newaddy = new ExcelAddressBase(oldaddy.Start.Row, oldaddy.Start.Column, oldaddy.End.Row + 1, oldaddy.End.Column);
//Edit the raw XML by searching for all references to the old address
tbl.TableXml.InnerXml = tbl.TableXml.InnerXml.Replace(oldaddy.ToString(), newaddy.ToString());
pck.Save();
}