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