/// <summary> /// Create empty rows and cols to improve performance. /// </summary> /// <param name="rowCount"></param> /// <param name="colCount"></param> internal void CreateEmptyCells(int rowCount, int colCount) { if (Rows.Count != 0) { throw new InvalidOperationException("Must be called before rows are filled"); } XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager); for (int rowNum = 1; rowNum <= rowCount; rowNum++) { // Add element XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain); rowElement.SetAttribute("r", rowNum.ToString()); sheetDataNode.AppendChild(rowElement); ExcelRow row = new ExcelRow(this, rowElement); Rows.Add(rowNum, row); for (int colNum = 1; colNum <= colCount; colNum++) { XmlElement cellElement = WorksheetXml.CreateElement("c", ExcelPackage.schemaMain); cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString()); rowElement.AppendChild(cellElement); ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum); row.Cells.Add(colNum, cell); } } }
/// <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> /// Inserts conditional formatting for the cell range. /// Currently only supports the dataBar style. /// </summary> /// <param name="startCell"></param> /// <param name="endCell"></param> /// <param name="color"></param> public void CreateConditionalFormatting(ExcelCell startCell, ExcelCell endCell, string color) { XmlNode formatNode = WorksheetXml.SelectSingleNode("//d:conditionalFormatting", NameSpaceManager); if (formatNode == null) { formatNode = WorksheetXml.CreateElement("conditionalFormatting", ExcelPackage.schemaMain); XmlNode prevNode = WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager); if (prevNode == null) { prevNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager); } WorksheetXml.DocumentElement.InsertAfter(formatNode, prevNode); } XmlAttribute attr = formatNode.Attributes["sqref"]; if (attr == null) { attr = WorksheetXml.CreateAttribute("sqref"); formatNode.Attributes.Append(attr); } attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress); XmlNode node = formatNode.SelectSingleNode("./d:cfRule", NameSpaceManager); if (node == null) { node = WorksheetXml.CreateElement("cfRule", ExcelPackage.schemaMain); formatNode.AppendChild(node); } attr = node.Attributes["type"]; if (attr == null) { attr = WorksheetXml.CreateAttribute("type"); node.Attributes.Append(attr); } attr.Value = "dataBar"; attr = node.Attributes["priority"]; if (attr == null) { attr = WorksheetXml.CreateAttribute("priority"); node.Attributes.Append(attr); } attr.Value = "1"; // the following is poor code, but just an example!!! XmlNode databar = WorksheetXml.CreateElement("databar", ExcelPackage.schemaMain); node.AppendChild(databar); XmlNode child = WorksheetXml.CreateElement("cfvo", ExcelPackage.schemaMain); databar.AppendChild(child); attr = WorksheetXml.CreateAttribute("type"); child.Attributes.Append(attr); attr.Value = "min"; attr = WorksheetXml.CreateAttribute("val"); child.Attributes.Append(attr); attr.Value = "0"; child = WorksheetXml.CreateElement("cfvo", ExcelPackage.schemaMain); databar.AppendChild(child); attr = WorksheetXml.CreateAttribute("type"); child.Attributes.Append(attr); attr.Value = "max"; attr = WorksheetXml.CreateAttribute("val"); child.Attributes.Append(attr); attr.Value = "0"; child = WorksheetXml.CreateElement("color", ExcelPackage.schemaMain); databar.AppendChild(child); attr = WorksheetXml.CreateAttribute("rgb"); child.Attributes.Append(attr); attr.Value = color; }