/// <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> /// 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(); //} }
public IEnumerable <ExcelCell> GetUsedCells() { // process each row foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) { var row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value); // process each cell in current row foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager)) { var column = Convert.ToInt32(colNode.Attributes[tempColumnNumberTag].Value); yield return(new ExcelCell(this, row, column)); } } }
/// <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; var formula = startCell.Formula; if (formula == "") { throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!"); } // find or create a shared formula ID var sharedID = -1; foreach (XmlNode node in WorksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager)) { var curID = int.Parse(node.Value); if (curID > sharedID) { sharedID = curID; } } sharedID++; // first value must be zero for (var row = startCell.Row; row <= endCell.Row; row++) { for (var col = startCell.Column; col <= endCell.Column; col++) { var 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)); }
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); }