private void MakeRow(XmlNode xlsxRow, XmlElement resultRow)
{
int lastColSeen = 0;
foreach (XmlNode cn in xlsxRow)
{
if (cn.NodeType == XmlNodeType.Element && cn.LocalName == "c")
{
XmlElement c = (XmlElement)cn;
XmlElement resultCol = (XmlElement)resultRow.AppendChild(resultDoc.CreateElement("Cell"));
string columnIdentifier = c.GetAttribute("r");
if (columnIdentifier.Length > 0)
{
int i = 0;
while (Char.IsLetter(columnIdentifier[i]))
{
++i;
}
string colId = columnIdentifier.Substring(0, i);
lastColSeen = XLSX.ColumnName2Index(colId);
resultCol.SetAttribute("c", colId);
}
else
{
lastColSeen++;
resultCol.SetAttribute("c", XLSX.Index2ColumnName(lastColSeen));
}
resultCol.SetAttribute("n", Convert.ToString(lastColSeen));
minColumn = (lastColSeen < minColumn) ? lastColSeen : minColumn;
maxColumn = (lastColSeen > maxColumn) ? lastColSeen : maxColumn;
string cf1 = c.GetAttribute("t");
if (cf1.Length == 0)
{
cf1 = "n";
}
// InternalFormatNumber cf2 = InternalFormatNumber.Number;
if (cf1 == "inlineStr")
{
// cf2 = InternalFormatNumber.String;
resultCol.SetAttribute("t", "s");
XmlElement iss = XLSX.GetChildElement(c, "is");
if (iss != null)
{
resultCol.InnerText = iss.InnerText;
}
}
else
{
XmlElement v = XLSX.FindChildElement(c, "v");
if (v != null)
{
string textValue = v.InnerText;
if (cf1 == "s")
{
// cf2 = InternalFormatNumber.String;
resultCol.SetAttribute("t", "s");
int stringIndex = Int32.Parse(textValue);
if (stringIndex < sharedStrings.Count)
{
resultCol.InnerText = (string)sharedStrings[stringIndex];
}
}
else
{
if (cf1 == "str" || cf1 == "e")
{
resultCol.SetAttribute("t", "s");
}
else
{
resultCol.SetAttribute("t", cf1);
}
resultCol.InnerText = textValue;
}
}
}
}
}
}
private void MakeWorkSheet(XmlDocument sheetDoc, XmlElement resultWorkSheet)
{
if (sheetDoc == null)
{
throw new Exception("Worksheet document is null");
}
int lastRowSeen = 0;
XmlElement workSheetElement = XLSX.GetChildElement(sheetDoc, "worksheet");
XmlElement dimension = null;
bool mustCreateDimension = false;
dimension = (XmlElement)resultWorkSheet.AppendChild(resultDoc.CreateElement("Dimension"));
XmlElement dimensionElement = XLSX.FindChildElement(workSheetElement, "dimension");
if (dimensionElement == null)
{
mustCreateDimension = true;
}
else
{
string firstCell = dimensionElement.GetAttribute("ref");
if (firstCell.Length > 0)
{
Regex regex = new Regex("([A-Z]+)(\\d+)(:([A-Z]+)(\\d+))?");
Match matcher = regex.Match(firstCell);
if (!matcher.Success)
{
throw new Exception("makeWorkSheet: dimension " + firstCell + " doesn't match");
}
int firstColumnIndex = matcher.Groups[1].Value != null && matcher.Groups[1].Value.Length > 0 ? XLSX.ColumnName2Index(matcher.Groups[1].Value) : 1;
int firstRowIndex = matcher.Groups[2].Value != null && matcher.Groups[2].Value.Length > 0 ? Int32.Parse(matcher.Groups[2].Value) : 1;
int secondColumnIndex = matcher.Groups[4].Value != null && matcher.Groups[4].Value.Length > 0 ? XLSX.ColumnName2Index(matcher.Groups[4].Value) : firstColumnIndex;
int secondRowIndex = matcher.Groups[5].Value != null && matcher.Groups[5].Value.Length > 0 ? Int32.Parse(matcher.Groups[5].Value) : firstRowIndex;
WriteDimension(dimension, firstRowIndex, secondRowIndex, firstColumnIndex, secondColumnIndex);
}
else
{
mustCreateDimension = true;
}
}
XmlNode sheetDataElement = XLSX.GetChildElement(workSheetElement, "sheetData");
minColumn = Int32.MaxValue;
maxColumn = 0;
foreach (XmlNode rowNode in sheetDataElement)
{
if (rowNode.NodeType == XmlNodeType.Element && rowNode.LocalName == "row")
{
XmlElement rowElement = (XmlElement)rowNode;
XmlElement resultRow = (XmlElement)resultWorkSheet.AppendChild(resultDoc.CreateElement("Row"));
string rowNumber = rowElement.GetAttribute("r");
if (rowNumber.Length > 0)
{
lastRowSeen = Int32.Parse(rowNumber);
}
else
{
++lastRowSeen;
rowNumber = Convert.ToString(lastRowSeen);
}
resultRow.SetAttribute("r", rowNumber);
MakeRow(rowElement, resultRow);
}
}
if (mustCreateDimension)
{
int firstRowIndex = 1;
XmlElement firstRow = XLSX.GetChildElement(resultWorkSheet, "Row");
if (firstRow != null)
{
firstRowIndex = Int32.Parse(firstRow.GetAttribute("r"));
}
WriteDimension(dimension, firstRowIndex, lastRowSeen, minColumn, maxColumn);
}
}