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