How do I handle adding rows to the Worksheet Sheet Data and modify the Table's Reference range (boundaries) without destroying the Excel file?
In this entry, Vincent Tan mentions an important point about navigating the Sheet Data within the boundary of a Worksheet Table.
Let's say from the Table object, you know the range is B2:D3. Then you'll have to iterate through cells B2 to D3 from the SheetData to get the cell value. It can be tedious. -- Vincent Tan Jan 23, 2013 at 13:05
My current challenge is how to grow the table reference range as new rows are added to the Sheet Data. I'm assigning the Table object's Reference range, at the bottom of the following code block, but it is damaging the Excel file. How do I handle adding rows to the Worksheet Sheet Data and modify the Table's Reference range (boundaries) without destroying the Excel file?
public static Cell GetTableCellReference(ref Worksheet ws, string cellAddress)
{
string tableName = "Table1";
TableDefinitionPart tdPart = ws.WorksheetPart.TableDefinitionParts.FirstOrDefault(t => t.Table.Name.Value == tableName);
Table tblObj = tdPart.Table;
tblObj.InsertRowShift = true;
/*
** \\D - non-digit character matching
** \\d - digit-character matching
** <col> - a RegEx local variable to store the non-digit match
** <row> - a RegEx local variable to store the digit match
*/
Regex rx = new Regex("^(?<col>\\D+)(?<row>\\d+)");
Match mt = rx.Match(cellAddress);
// The RegEx local variable for the row match
uint rowNum = uint.Parse(mt.Result("${row}"));
// The RegEx local variable for the column match
string colLtr = mt.Result("${col}");
// The RegEx variables will NOT fail so long as the cell address parameter is 'wellformed'
// an improvement could be wrapping the RegEx processing in a Try...Catch
// Obtain a reference to the referenced Worksheet SheetData
SheetData sd = ws.GetFirstChild<SheetData>();
// From the SheetData, we can get the collection of Rows in the Worksheet
// It's strange that the collection of Rows is built with a loos
var rows = sd.Elements<Row>();
// A var can be 'nullable' so you can test this to verify the row exists.
var newRow = rows.FirstOrDefault<Row>(r => r.RowIndex.Value == rowNum);
if (newRow == null)
{
// If the ROW does NOT exist, then create a new one. Once created, the Cell object needs to be created
// Creating a Row is simple
newRow = new Row();
newRow.RowIndex = rowNum;
// This new row needs to be placed in order within the Worksheet
// More analysis is required to ensure the row is placed in the right location/order
Row refRow = null;
foreach (Row rw in rows)
{
// The PluralSight tutorial is looking to go beyond the highest row by one.
// What's interesting, and what explains the n+1 Row is the use of InsertBefore
// I would have tested (rw.RowIndex == rowNum-1) for a 0-index row number which would require InsertAfter
// The reason why InsertBefore might be the better method is that, if the 'previous' row is also NULL, the
// new row will be inserted regardless.
if (rw.RowIndex > rowNum)
{
refRow = rw;
break;
}
}
// CAUTION: make sure you are targeting the correct object, think about it
// here, we are inserting a NEW ROW, so we need to use the SheetData object to do so.
// OpenXml is real loose, you could call InsertBefore against the Worksheet, and it will NOT
// complain, it will simply fail silently.
sd.InsertBefore(newRow, refRow);
}
else
{
// At this point we know the Row exists
}
// Now we analyze that new row to insert a new Cell, however, we treat the process as though the
// Cell may exist to avoid Exceptions
var cells = newRow.Elements<Cell>();
var newCell = cells.FirstOrDefault<Cell>(c => c.CellReference == cellAddress);
if (newCell == null)
{
// the Cell does NOT exist, we need to create it
newCell = new Cell();
// The CellReference assignment is based on the passed-in cell address
// However, remember that, like a Row, a Cell needs to be inserted at the
// correct location in the Row.
newCell.CellReference = cellAddress;
// We begin the process with a reference Cell and analyze
Cell refCell = null;
foreach (Cell c in cells)
{
if (string.Compare(c.CellReference.Value, cellAddress, true) > 0)
{
/*
** Int string.Compare(string strA, string strB, bool ignoreCase)
** Returns a 32-bit signed integer that indicates the lexical relationship
** between the two comparands.
** 1.) When the Value Condition < zero, strA precedes strB.
** 2.) When the Value Condition == zero, strA is in the same position as strB in the sort order.
** 3.) When the Value Condition > zero, strA follows strB in the sort order.
*/
// At this point we test, in the IF statement, to see if there is a cell address
// at the location specified... if there is assign the cell to the reference cell and BREAK loop;
refCell = c;
break;
}
}
// When the for each loop is done, the potential exists for the refCell to be null
// If the refCell IS null, the InsertBefore behavior will append to the Excel structure for the new Row.
newRow.InsertBefore(newCell, refCell);
}
else
{
// the Cell exists
}
tblObj.Reference = "A1:C" + (rowNum);
return newCell;
}
I thought perhaps the explicit assignment of the Table object's Reference range, seen at the bottom of the code above, would handle it. However, it causes an error resulting in the removal of the table1.xml definition from the Excel file's structure.
In a smaller test project the above appeared to work for only that content in the first row under the headers. For a new row, that would have increased the table size, it fails with the following errors.
Errors Generated:
Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table)
Removed Feature: Table from /xl/tables/table1.xml part (Table)