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