/// <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> /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments /// if they fall after the afterRow and afterColumn. /// Supports inserting rows and columns into existing templates. /// </summary> /// <param name="Formula">The Excel formula</param> /// <param name="rowIncrement">The amount to increment the cell reference by</param> /// <param name="colIncrement">The amount to increment the cell reference by</param> /// <param name="afterRow">Only change rows after this row</param> /// <param name="afterColumn">Only change columns after this column</param> /// <returns></returns> public static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn) { string newFormula = ""; Regex getAlphaNumeric = new Regex(@"[^a-zA-Z0-9]", RegexOptions.IgnoreCase); Regex getSigns = new Regex(@"[a-zA-Z0-9]", RegexOptions.IgnoreCase); string alphaNumeric = getAlphaNumeric.Replace(Formula, " ").Replace(" ", " "); string signs = getSigns.Replace(Formula, " "); char[] chrSigns = signs.ToCharArray(); int count = 0; int length = 0; foreach (string cellAddress in alphaNumeric.Split(' ')) { count++; length += cellAddress.Length; // if the cellAddress contains an alpha part followed by a number part, then it is a valid cellAddress int row = GetRowNumber(cellAddress); int col = GetColumnNumber(cellAddress); string newCellAddress = ""; if (ExcelCell.GetCellAddress(row, col) == cellAddress) // this checks if the cellAddress is valid { // we have a valid cell address so change its value (if necessary) if (row >= afterRow) { row += rowIncrement; } if (col >= afterColumn) { col += colIncrement; } newCellAddress = GetCellAddress(row, col); } if (newCellAddress == "") { newFormula += cellAddress; } else { newFormula += newCellAddress; } for (int i = length; i < signs.Length; i++) { if (chrSigns[i] == ' ') { break; } if (chrSigns[i] != ' ') { length++; newFormula += chrSigns[i].ToString(); } } } return(newFormula); }
/// <summary> /// Adds numeric cell identifiers so that it is easier to work out position of cells /// Private method, for internal use only! /// </summary> private void AddNumericCellIDs() { // process each row foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) { // remove the spans attribute. Excel simply recreates it when the file is opened. XmlAttribute attr = (XmlAttribute)rowNode.Attributes.GetNamedItem("spans"); if (attr != null) { rowNode.Attributes.Remove(attr); } int row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value); // process each cell in current row foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager)) { XmlAttribute cellAddressAttr = (XmlAttribute)colNode.Attributes.GetNamedItem("r"); if (cellAddressAttr != null) { string cellAddress = cellAddressAttr.Value; int col = ExcelCell.GetColumnNumber(cellAddress); attr = WorksheetXml.CreateAttribute(tempColumnNumberTag); if (attr != null) { attr.Value = col.ToString(); colNode.Attributes.Append(attr); // remove all cell Addresses like A1, A2, A3 etc. colNode.Attributes.Remove(cellAddressAttr); } } } } }
/// <summary> /// Returns the row number from the cellAddress /// e.g. D5 would return 5 /// </summary> /// <param name="cellAddress">An Excel format cell addresss (e.g. D5)</param> /// <returns>The row number</returns> public static int GetRowNumber(string cellAddress) { // find out position where characters stop and numbers begin int iPos = 0; bool found = false; foreach (char chr in cellAddress.ToCharArray()) { iPos++; if (char.IsNumber(chr)) { found = true; break; } } if (found) { string NumberPart = cellAddress.Substring(iPos - 1, cellAddress.Length - (iPos - 1)); if (ExcelCell.IsNumericValue(NumberPart)) { return(int.Parse(NumberPart)); } } return(0); }
/// <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> /// Replaces the numeric cell identifiers we inserted with AddNumericCellIDs with the traditional /// A1, A2 cell identifiers that Excel understands. /// Private method, for internal use only! /// </summary> private void ReplaceNumericCellIDs() { int maxColumn = 0; // process each row foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) { int row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value); int count = 0; // process each cell in current row foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager)) { XmlAttribute colNumber = (XmlAttribute)colNode.Attributes.GetNamedItem(tempColumnNumberTag); if (colNumber != null) { count++; if (count > maxColumn) { maxColumn = count; } int col = Convert.ToInt32(colNumber.Value); string cellAddress = ExcelCell.GetColumnLetter(col) + row.ToString(); XmlAttribute attr = WorksheetXml.CreateAttribute("r"); if (attr != null) { attr.Value = cellAddress; // the cellAddress needs to be the first attribute, otherwise Excel complains if (colNode.Attributes.Count == 0) { colNode.Attributes.Append(attr); } else { colNode.Attributes.InsertBefore(attr, (XmlAttribute)colNode.Attributes.Item(0)); } } // remove all numeric cell addresses added by AddNumericCellIDs colNode.Attributes.Remove(colNumber); } } } // process each row and add the spans attribute // TODO: Need to add proper spans handling. //foreach (XmlNode rowNode in XmlDoc.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) //{ // // we must add or update the "spans" attribute of each row // XmlAttribute spans = (XmlAttribute)rowNode.Attributes.GetNamedItem("spans"); // if (spans == null) // { // spans = XmlDoc.CreateAttribute("spans"); // rowNode.Attributes.Append(spans); // } // spans.Value = "1:" + maxColumn.ToString(); //} }
/// <summary> /// Provides access to an individual cell within the worksheet. /// </summary> /// <param name="rowNum">The row number in the worksheet</param> /// <param name="colNum">The column number in the worksheet</param> /// <returns></returns> public ExcelCell Cell(int rowNum, int colNum) { ExcelRow row = Row(rowNum); ExcelCell cell; if (row.Cells.TryGetValue(colNum, out cell)) { return(cell); } cell = new ExcelCell(this, rowNum, colNum); row.Cells.Add(colNum, cell); return(cell); }
/// <summary> /// Creates a ExcelWorkSheetDimension using a string with Cell Range representation like 'A1:B5'. /// </summary> /// <param name="dimension">a string with Cell Range representation like 'A1:B5'</param> public ExcelWorkSheetDimension(String dimension) { String[] dimensions = dimension.Split(':'); this.topLeft = dimensions[0]; this.bottomRight = dimensions[1]; if (!ExcelCell.IsValidCellAddress(topLeft) || (!ExcelCell.IsValidCellAddress(BottomRight))) { throw new ArgumentException("No valid excel sheet dimension!"); } firstRow = ExcelCell.GetRowNumber(topLeft); firstCol = ExcelCell.GetColumnNumber(topLeft); lastCol = ExcelCell.GetColumnNumber(bottomRight); lastRow = ExcelCell.GetRowNumber(bottomRight); }
/// <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); }
/// <summary> /// Creates a shared formula based on the formula already in startCell /// Essentially this supports the formula attributes such as t="shared" ref="B2:B4" si="0" /// as per Brian Jones: Open XML Formats blog. See /// http://blogs.msdn.com/brian_jones/archive/2006/11/15/simple-spreadsheetml-file-part-2-of-3.aspx /// </summary> /// <param name="startCell">The cell containing the formula</param> /// <param name="endCell">The end cell (i.e. end of the range)</param> public void CreateSharedFormula(ExcelCell startCell, ExcelCell endCell) { XmlElement formulaElement; string formula = startCell.Formula; if (formula == "") { throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!"); } // find or create a shared formula ID int sharedID = -1; foreach (XmlNode node in _worksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager)) { int curID = int.Parse(node.Value); if (curID > sharedID) { sharedID = curID; } } sharedID++; // first value must be zero for (int row = startCell.Row; row <= endCell.Row; row++) { for (int col = startCell.Column; col <= endCell.Column; col++) { ExcelCell cell = Cell(row, col); // to force Excel to re-calculate the formula, we must remove the value cell.RemoveValue(); formulaElement = (XmlElement)cell.Element.SelectSingleNode("./d:f", NameSpaceManager); if (formulaElement == null) { formulaElement = cell.AddFormulaElement(); } formulaElement.SetAttribute("t", "shared"); formulaElement.SetAttribute("si", sharedID.ToString()); } } // finally add the shared cell range to the startCell formulaElement = (XmlElement)startCell.Element.SelectSingleNode("./d:f", NameSpaceManager); formulaElement.SetAttribute("ref", string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress)); }
/// <summary> /// Get a range reference in Excel format /// e.g. GetRange("sheet", 1,5,10,2) => "sheet!$J$1:$K$5" /// </summary> /// <param name="worksheet"></param> /// <param name="startRow"></param> /// <param name="rowCount">Number of rows to include, >=1</param> /// <param name="startCol"></param> /// <param name="colCount">Number of columns to include, >=1</param> /// <returns></returns> public static String GetRangeRef(String worksheet, int startRow, int rowCount, int startCol, int colCount) { // Be tolerant if (rowCount <= 0) { rowCount = 1; } if (colCount <= 0) { colCount = 1; } return(worksheet + "!" + "$" + ExcelCell.GetColumnLetter(startCol) + "$" + startRow + ":" + "$" + ExcelCell.GetColumnLetter(startCol + colCount - 1) + "$" + (startRow + rowCount - 1)); }
internal ExcelCell Clone(ExcelWorksheet added, int row, int col) { ExcelCell newCell = new ExcelCell(added, row, col); if (_hyperlink != null) { newCell.Hyperlink = Hyperlink; } newCell._formula = _formula; newCell._formulaR1C1 = _formulaR1C1; newCell.IsRichText = IsRichText; newCell.Merge = Merge; newCell._sharedFormulaID = _sharedFormulaID; newCell._styleName = _styleName; newCell._styleID = _styleID; newCell._value = _value; return(newCell); }
Dictionary <int, ExcelRow> ReadData() { Dictionary <int, ExcelRow> rows = new Dictionary <int, ExcelRow>(); foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) { int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value); ExcelRow row = new ExcelRow(this, rowElement); rows.Add(rowNum, row); // Get all cells for the row foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager)) { int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value); ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum); row.Cells.Add(colNum, cell); } } return(rows); }
/// <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; }
internal ExcelCell Clone(ExcelWorksheet added, int row, int col) { ExcelCell newCell = new ExcelCell(added, row, col); if(_hyperlink!=null) newCell.Hyperlink = Hyperlink; newCell._formula = _formula; newCell._formulaR1C1 = _formulaR1C1; newCell.IsRichText = IsRichText; newCell.Merge = Merge; newCell._sharedFormulaID = _sharedFormulaID; newCell._styleName = _styleName; newCell._styleID = _styleID; newCell._value = _value; return newCell; }
internal ExcelCell Clone(ExcelWorksheet added) { ExcelCell newCell = new ExcelCell(added, _row, _col); //newCell.DataType = DataType; //if (Formula != "" && SharedFormulaID < 0) //{ // newCell.Formula = Formula; // added._formulaCells.Add(newCell); //} if(_hyperlink!=null) newCell.Hyperlink = Hyperlink; newCell._formula = _formula; newCell._formulaR1C1 = _formulaR1C1; newCell.IsRichText = IsRichText; newCell.Merge = Merge; newCell._sharedFormulaID = _sharedFormulaID; newCell._styleName = _styleName; newCell._styleID = _styleID; newCell._value = _value; return newCell; }
/// <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 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; }
/// <summary> /// Creates a shared formula based on the formula already in startCell /// Essentially this supports the formula attributes such as t="shared" ref="B2:B4" si="0" /// as per Brian Jones: Open XML Formats blog. See /// http://blogs.msdn.com/brian_jones/archive/2006/11/15/simple-spreadsheetml-file-part-2-of-3.aspx /// </summary> /// <param name="startCell">The cell containing the formula</param> /// <param name="endCell">The end cell (i.e. end of the range)</param> public void CreateSharedFormula(ExcelCell startCell, ExcelCell endCell) { XmlElement formulaElement; string formula = startCell.Formula; if (formula == "") throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!"); // find or create a shared formula ID int sharedID = -1; foreach (XmlNode node in _worksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager)) { int curID = int.Parse(node.Value); if (curID > sharedID) sharedID = curID; } sharedID++; // first value must be zero for (int row = startCell.Row; row <= endCell.Row; row++) { for (int col = startCell.Column; col <= endCell.Column; col++) { ExcelCell cell = Cell(row, col); // to force Excel to re-calculate the formula, we must remove the value cell.RemoveValue(); formulaElement = (XmlElement)cell.Element.SelectSingleNode("./d:f", NameSpaceManager); if (formulaElement == null) { formulaElement = cell.AddFormulaElement(); } formulaElement.SetAttribute("t", "shared"); formulaElement.SetAttribute("si", sharedID.ToString()); } } // finally add the shared cell range to the startCell formulaElement = (XmlElement)startCell.Element.SelectSingleNode("./d:f", NameSpaceManager); formulaElement.SetAttribute("ref", string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress)); }
internal OpenXmlExcelCell(ExcelCell obj) { _obj = obj; }
Dictionary<int, ExcelRow> ReadData() { Dictionary<int, ExcelRow> rows = new Dictionary<int,ExcelRow>(); foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) { int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value); ExcelRow row = new ExcelRow(this, rowElement); rows.Add(rowNum, row); // Get all cells for the row foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager)) { int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value); ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum); row.Cells.Add(colNum, cell); } } return rows; }
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe) { if (address.Start.Column == 0 || address.Start.Row == 0) { throw (new Exception("error address")); } //Columns else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows) { ExcelColumn column; //Get the startcolumn ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column); if (!ws._columns.ContainsKey(colID)) { column = ws.Column(address.Start.Column); } else { column = ws._columns[colID] as ExcelColumn; } var index = ws._columns.IndexOf(colID); while (column.ColumnMin <= address.End.Column) { if (column.ColumnMax > address.End.Column) { var newCol = ws.CopyColumn(column, address.End.Column + 1); newCol.ColumnMax = column.ColumnMax; column.ColumnMax = address.End.Column; } if (styleCashe.ContainsKey(column.StyleID)) { column.StyleID = styleCashe[column.StyleID]; } else { ExcelXfs st = CellXfs[column.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(column.StyleID, newId); column.StyleID = newId; } index++; if (index >= ws._columns.Count) { break; } else { column = (ws._columns[index] as ExcelColumn); } } if (column._columnMax < address.End.Column) { var newCol = ws.Column(column._columnMax + 1) as ExcelColumn; newCol._columnMax = address.End.Column; if (styleCashe.ContainsKey(newCol.StyleID)) { newCol.StyleID = styleCashe[newCol.StyleID]; } else { ExcelXfs st = CellXfs[column.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(newCol.StyleID, newId); newCol.StyleID = newId; } //column._columnMax = address.End.Column; } //Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first. foreach (ExcelCell cell in ws._cells) { if (cell.Column >= address.Start.Column && cell.Column <= address.End.Column) { if (styleCashe.ContainsKey(cell.StyleID)) { cell.StyleID = styleCashe[cell.StyleID]; } else { ExcelXfs st = CellXfs[cell.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cell.StyleID, newId); cell.StyleID = newId; } } } } //Rows else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns) { for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++) { ExcelRow row = ws.Row(rowNum); if (row.StyleID == 0 && ws._columns.Count > 0) { //TODO: We should loop all columns here and change each cell. But for now we take style of column A. foreach (ExcelColumn column in ws._columns) { row.StyleID = column.StyleID; break; //Get the first one and break. } } if (styleCashe.ContainsKey(row.StyleID)) { row.StyleID = styleCashe[row.StyleID]; } else { ExcelXfs st = CellXfs[row.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(row.StyleID, newId); row.StyleID = newId; } } //Get Start Cell ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row); int index = ws._cells.IndexOf(rowID); index = ~index; while (index < ws._cells.Count) { var cell = ws._cells[index] as ExcelCell; if (cell.Row > address.End.Row) { break; } if (styleCashe.ContainsKey(cell.StyleID)) { cell.StyleID = styleCashe[cell.StyleID]; } else { ExcelXfs st = CellXfs[cell.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cell.StyleID, newId); cell.StyleID = newId; } index++; } } else //Cellrange { for (int col = address.Start.Column; col <= address.End.Column; col++) { for (int row = address.Start.Row; row <= address.End.Row; row++) { ExcelCell cell = ws.Cell(row, col); if (styleCashe.ContainsKey(cell.StyleID)) { cell.StyleID = styleCashe[cell.StyleID]; } else { ExcelXfs st = CellXfs[cell.StyleID]; int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cell.StyleID, newId); cell.StyleID = newId; } } } } }
private void CloneCells(ExcelWorksheet Copy, ExcelWorksheet added) { bool sameWorkbook = (Copy.Workbook == _pck.Workbook); bool doAdjust = _pck.DoAdjustDrawings; _pck.DoAdjustDrawings = false; added.MergedCells.List.AddRange(Copy.MergedCells.List); //Formulas foreach (IRangeID f in Copy._formulaCells) { added._formulaCells.Add(f); } //Shared Formulas foreach (int key in Copy._sharedFormulas.Keys) { added._sharedFormulas.Add(key, Copy._sharedFormulas[key]); } Dictionary <int, int> styleCashe = new Dictionary <int, int>(); //Cells foreach (ExcelCell cell in Copy._cells) { if (sameWorkbook) //Same workbook == same styles { added._cells.Add(cell.Clone(added)); } else { ExcelCell addedCell = cell.Clone(added); if (styleCashe.ContainsKey(cell.StyleID)) { addedCell.StyleID = styleCashe[cell.StyleID]; } else { addedCell.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, cell.StyleID); if (cell.StyleName != "") //Named styles { if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(cell.StyleName)) { var ns = Copy.Workbook.Styles.CreateNamedStyle(cell.StyleName); ns.StyleXfId = addedCell.StyleID; } } styleCashe.Add(cell.StyleID, addedCell.StyleID); } added._cells.Add(addedCell); } } //Rows foreach (ExcelRow row in Copy._rows) { row.Clone(added); if (!sameWorkbook) //Same workbook == same styles { ExcelRow addedRow = added.Row(row.Row) as ExcelRow; if (styleCashe.ContainsKey(row.StyleID)) { addedRow.StyleID = styleCashe[row.StyleID]; } else { addedRow.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedRow.StyleID); if (row.StyleName != "") //Named styles { if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(row.StyleName)) { var ns = Copy.Workbook.Styles.CreateNamedStyle(row.StyleName); ns.StyleXfId = addedRow.StyleID; } } styleCashe.Add(row.StyleID, addedRow.StyleID); } } } //Columns foreach (ExcelColumn col in Copy._columns) { col.Clone(added); if (!sameWorkbook) //Same workbook == same styles { ExcelColumn addedCol = added.Column(col.ColumnMin) as ExcelColumn; if (styleCashe.ContainsKey(col.StyleID)) { addedCol.StyleID = styleCashe[col.StyleID]; } else { addedCol.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedCol.StyleID); if (col.StyleName != "") //Named styles { if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(col.StyleName)) { var ns = Copy.Workbook.Styles.CreateNamedStyle(col.StyleName); ns.StyleXfId = addedCol.StyleID; } } styleCashe.Add(col.StyleID, addedCol.StyleID); } } } added._package.DoAdjustDrawings = doAdjust; }
/// <summary> /// Provides access to an individual cell within the worksheet. /// </summary> /// <param name="rowNum">The row number in the worksheet</param> /// <param name="colNum">The column number in the worksheet</param> /// <returns></returns> public ExcelCell Cell(int rowNum, int colNum) { ExcelRow row = Row(rowNum); ExcelCell cell; if (row.Cells.TryGetValue(colNum, out cell)) { return cell; } cell = new ExcelCell(this, rowNum, colNum); row.Cells.Add(colNum, cell); return cell; }