/// <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)
{
XmlNode rowNode = null;
// create the new row element
XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
rowElement.Attributes.Append(WorksheetXml.CreateAttribute("r"));
rowElement.Attributes["r"].Value = position.ToString();
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
int renumberFrom = 1;
XmlNodeList nodes = sheetDataNode.ChildNodes;
int nodeCount = nodes.Count;
XmlNode insertAfterRowNode = null;
int insertAfterRowNodeID = 0;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attributes["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].Attributes["r"].Value);
if (currentRowID >= renumberFrom)
{
nodes[i].Attributes["r"].Value = Convert.ToString(currentRowID + 1);
// now update any formula that are in the row
XmlNodeList formulaNodes = nodes[i].SelectNodes("./d:c/d:f", NameSpaceManager);
foreach (XmlNode formulaNode in formulaNodes)
{
formulaNode.InnerText = ExcelCell.UpdateFormulaReferences(formulaNode.InnerText, 1, 0, position, 0);
}
}
}
// now insert the new row
if (insertAfterRowNode != null)
{
rowNode = sheetDataNode.InsertAfter(rowElement, insertAfterRowNode);
}
}
}
/// <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)
{
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
XmlNodeList nodes = sheetDataNode.ChildNodes;
int nodeCount = nodes.Count;
int rowNodeID = 0;
XmlNode rowNode = null;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attributes["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].Attributes["r"].Value);
if (currentRowID > rowToDelete)
{
nodes[i].Attributes["r"].Value = Convert.ToString(currentRowID - 1);
// now update any formula that are in the row
XmlNodeList formulaNodes = nodes[i].SelectNodes("./d:c/d:f", NameSpaceManager);
foreach (XmlNode formulaNode in formulaNodes)
{
formulaNode.InnerText = ExcelCell.UpdateFormulaReferences(formulaNode.InnerText, -1, 0, rowToDelete, 0);
}
}
}
}
// delete the row
if (rowNode != null)
{
sheetDataNode.RemoveChild(rowNode);
}
}
// Update stored rows
Rows.Remove(rowToDelete);
ShiftRows(rowToDelete, -1);
}
