/// <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)
{
// create the new row element
var rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
rowElement.Attributes.Append(WorksheetXml.CreateAttribute("r"));
rowElement.Attributes["r"].Value = position.ToString();
var sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
var renumberFrom = 1;
var nodes = sheetDataNode.ChildNodes;
var nodeCount = nodes.Count;
XmlNode insertAfterRowNode = null;
var insertAfterRowNodeID = 0;
for (var i = 0; i < nodeCount; i++)
{
var 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 (var i = insertAfterRowNodeID + 1; i < nodeCount; i++)
{
var 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
var 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)
{
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)
{
var sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
var nodes = sheetDataNode.ChildNodes;
var nodeCount = nodes.Count;
var rowNodeID = 0;
XmlNode rowNode = null;
for (var i = 0; i < nodeCount; i++)
{
var currentRowID = int.Parse(nodes[i].Attributes["r"].Value);
if (currentRowID == rowToDelete)
{
rowNodeID = i;
rowNode = nodes[i];
}
}
if (shiftOtherRowsUp)
{
// update the existing row ids
for (var i = rowNodeID + 1; i < nodeCount; i++)
{
var 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
var 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);
}
}
}
