/// <summary>
/// Inserts a new row into the spreadsheet. Existing rows below the insersion position are
/// shifted down. All formula are updated to take account of the new row.
/// </summary>
/// <param name="position">The position of the new row</param>
public void InsertRow(int position)
{
XElement rowNode = null;
// create the new row element
XElement rowElement = ExtensonMethods.NewElement("row").SetAttrValue("r", position.ToString());
var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
int renumberFrom = 1;
var nodes = sheetDataNode.Nodes().Cast <XElement>().ToList();
int nodeCount = nodes.Count;
XElement insertAfterRowNode = null;
int insertAfterRowNodeID = 0;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID < position)
{
insertAfterRowNode = nodes[i];
insertAfterRowNodeID = i;
}
if (currentRowID >= position)
{
renumberFrom = currentRowID;
break;
}
}
// update the existing row ids
for (int i = insertAfterRowNodeID + 1; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID >= renumberFrom)
{
nodes[i].Attribute("r").Value = Convert.ToString(currentRowID + 1);
// now update any formula that are in the row
var formulaNodes = nodes[i].XPathSelectElements("./d:c/d:f", NameSpaceManager);
foreach (var formulaNode in formulaNodes)
{
formulaNode.Value = ExcelCell.UpdateFormulaReferences(formulaNode.Value, 1, 0, position, 0);
}
}
}
// now insert the new row
insertAfterRowNode?.AddAfterSelf(rowElement);
}
// Update stored rows
ShiftRows(position, 1);
Rows.Add(position, new ExcelRow(this, rowElement));
}
/// <summary>
/// Deletes the specified row from the worksheet.
/// If shiftOtherRowsUp=true then all formula are updated to take account of the deleted row.
/// </summary>
/// <param name="rowToDelete">The number of the row to be deleted</param>
/// <param name="shiftOtherRowsUp">Set to true if you want the other rows renumbered so they all move up</param>
public void DeleteRow(int rowToDelete, bool shiftOtherRowsUp)
{
var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
var nodes = sheetDataNode.Nodes().Cast <XElement>().ToList();
int nodeCount = nodes.Count;
int rowNodeID = 0;
XElement rowNode = null;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID == rowToDelete)
{
rowNodeID = i;
rowNode = nodes[i];
}
}
if (shiftOtherRowsUp)
{
// update the existing row ids
for (int i = rowNodeID + 1; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID > rowToDelete)
{
nodes[i].Attribute("r").Value = Convert.ToString(currentRowID - 1);
// now update any formula that are in the row
var formulaNodes = nodes[i].XPathSelectElements("./d:c/d:f", NameSpaceManager);
foreach (var formulaNode in formulaNodes)
{
formulaNode.Value = ExcelCell.UpdateFormulaReferences(formulaNode.Value, -1, 0, rowToDelete, 0);
}
}
}
}
// delete the row
if (rowNode != null)
{
rowNode.Remove();
}
}
// Update stored rows
Rows.Remove(rowToDelete);
ShiftRows(rowToDelete, -1);
}