private void MakeWorksheets(ref Hashtable resultWorksheets)
{
XmlElement workbook = XLSX.GetChildElement(document, "Workbook");
int sheetNo = 0;
foreach (XmlElement worksheet in XLSX.GetChildElements(workbook, "Worksheet"))
{
string sheetName = worksheet.GetAttribute("Name");
if (sheetName.Length == 0)
{
throw new System.Exception("Cannot create a worksheet without a name");
}
int lastRowSeen = 0;
ResultWorksheet resultWorksheet = null;
if (resultWorksheets.Contains(sheetName))
{
resultWorksheet = (ResultWorksheet)resultWorksheets[sheetName];
}
else
{
resultWorksheet = new ResultWorksheet(sheetName, new ArrayList());
}
foreach (XmlElement row in XLSX.GetChildElements(worksheet, "Row"))
{
string rowIndex = row.GetAttribute("r");
int n = 0;
if (rowIndex.Length > 0 && ((n = Int32.Parse(rowIndex)) > 0))
{
lastRowSeen = n;
}
else
{
rowIndex = Convert.ToString(++lastRowSeen);
}
while (resultWorksheet.Matrix.Count <= lastRowSeen)
{
resultWorksheet.Matrix.Add(null);
}
ArrayList lastRow = (ArrayList)resultWorksheet.Matrix[lastRowSeen];
if (lastRow == null)
{
lastRow = new ArrayList();
}
MakeCells(row, ref lastRow, lastRowSeen);
resultWorksheet.Matrix[lastRowSeen] = lastRow;
}
resultWorksheet.Filename = "worksheets/Sheet" + Convert.ToString(sheetNo) + ".xml";
resultWorksheet.SheetId = "rId" + Convert.ToString(sheetNo++);
resultWorksheets[sheetName] = resultWorksheet;
}
}
private void MakeWorkBook()
{
XmlElement workBookNode = XLSX.GetChildElement(workBookDoc, "workbook");
XmlElement sheets = XLSX.GetChildElement(workBookNode, "sheets");
workSheetNames = new ArrayList();
foreach (XmlElement sheet in sheets)
{
if (sheet.NodeType == XmlNodeType.Element)
{
string sheetRelId = sheet.GetAttribute("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
string sheetName = sheet.GetAttribute("name");
workSheetNames.Add(sheetName);
string typeURI = FindRelationInfoForKey(workBookRels, "Id", sheetRelId, "Type");
if (typeURI == null || typeURI != "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet")
{
continue;
}
string sheetFileName = FindRelationTargetForKey(workBookRels, "Id", sheetRelId);
if (sheetFileName == null)
{
continue;
}
sheetFileName = ReplaceFilename(workBookFileName, sheetFileName);
XmlElement resultWorkSheet = (XmlElement)resultWorkBook.AppendChild(resultDoc.CreateElement("Worksheet"));
resultWorkSheet.SetAttribute("Name", sheetName);
MakeWorkSheet(LoadDocumentFromZip(sheetFileName), resultWorkSheet);
}
}
}
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);
}
}