/// <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 (var rowNode in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager)) { // remove the spans attribute. Excel simply recreates it when the file is opened. XAttribute attr = (XAttribute)rowNode.Attribute("spans"); if (attr != null) { attr.Remove(); } int row = Convert.ToInt32(rowNode.AttributeValue("r")); // process each cell in current row foreach (var colNode in rowNode.XPathSelectElements("./d:c", NameSpaceManager)) { XAttribute cellAddressAttr = (XAttribute)colNode.Attribute("r"); if (cellAddressAttr != null) { string cellAddress = cellAddressAttr.Value; int col = ExcelCell.GetColumnNumber(cellAddress); attr = new XAttribute(tempColumnNumberTag, ""); if (attr != null) { attr.Value = col.ToString(); colNode.Add(attr); // remove all cell Addresses like A1, A2, A3 etc. cellAddressAttr.Remove(); } } } } }
/// <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 (var rowNode in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager)) { int row = Convert.ToInt32(rowNode.AttributeValue("r")); int count = 0; // process each cell in current row foreach (var colNode in rowNode.XPathSelectElements("./d:c", NameSpaceManager)) { XAttribute colNumber = (XAttribute)colNode.Attribute(tempColumnNumberTag); if (colNumber != null) { count++; if (count > maxColumn) { maxColumn = count; } int col = Convert.ToInt32(colNumber.Value); string cellAddress = ExcelCell.GetColumnLetter(col) + row.ToString(); XAttribute attr = new XAttribute("r", ""); if (attr != null) { attr.Value = cellAddress; // the cellAddress needs to be the first attribute, otherwise Excel complains if (!colNode.Attributes().Any()) { colNode.Add(attr); } else { //var firstAttr =colNode.Attributes().First(); colNode.AddFirst(attr); } } // remove all numeric cell addresses added by AddNumericCellIDs colNumber.Remove(); } } } // process each row and add the spans attribute // TODO: Need to add proper spans handling. //foreach (var rowNode in XmlDoc.SelectNodes("//d:sheetData/d:row", NameSpaceManager)) //{ // // we must add or update the "spans" attribute of each row // XAttribute spans = (XAttribute)rowNode.Attributes.GetNamedItem("spans"); // if (spans == null) // { // spans = XmlDoc.CreateAttribute("spans"); // rowNode.Add(spans); // } // spans.Value = "1:" + maxColumn.ToString(); //} }
Dictionary <int, ExcelRow> ReadData() { Dictionary <int, ExcelRow> rows = new Dictionary <int, ExcelRow>(); foreach (var rowElement in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager)) { int rowNum = Convert.ToInt32(rowElement.Attribute("r").Value); ExcelRow row = new ExcelRow(this, rowElement); rows.Add(rowNum, row); // Get all cells for the row foreach (var cellElement in rowElement.XPathSelectElements("./d:c", NameSpaceManager)) { int colNum = Convert.ToInt32(cellElement.AttributeValue(ExcelWorksheet.tempColumnNumberTag)); ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum); row.Cells.Add(colNum, cell); } } return(rows); }