/// <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); }