private void MakeStyles()
{
styles = new XmlDocument();
string ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
XmlElement stylesheet = XLSX.AppendElement(styles, "styleSheet", ns);
stylesheet.SetAttribute("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
AddEverythingToStylesWhichExcelWantsToHaveToOpenTheCreatedFile(ref stylesheet);
XmlElement cellXfs = XLSX.AppendElement(stylesheet, "cellXfs", ns);
cellXfs.SetAttribute("count", "5");
bool anf = false;
foreach (GoodDateTimeFormat bestDateFormatEver in goodDateTimeFormats)
{
XmlElement xf = XLSX.AppendElement(cellXfs, "xf", ns);
xf.SetAttribute("numFmtId", bestDateFormatEver.id);
if (anf)
{
xf.SetAttribute("applyNumberFormat", "1");
}
anf = true;
}
XmlElement xf2 = XLSX.AppendElement(cellXfs, "xf", ns);
xf2.SetAttribute("applyAlignment", "1");
XmlElement alignment = XLSX.AppendElement(xf2, "alignment", ns);
alignment.SetAttribute("wrapText", "1");
}
private void MakeCells(XmlElement row, ref ArrayList resultRow, int lastRowIndex)
{
int lastColSeen = 0;
foreach (XmlElement cell in XLSX.GetChildElements(row, "Cell"))
{
string colIndex = cell.GetAttribute("n");
int n = 0;
if (colIndex.Length > 0 && ((n = Int32.Parse(colIndex)) > 0))
{
lastColSeen = n;
}
else
{
lastColSeen++;
}
if (lastColSeen > 16384)
{
throw new System.Exception("Cannot make more than 16384 columns; This is an Excel limitation");
}
while (resultRow.Count <= lastColSeen)
{
resultRow.Add(null);
}
if (resultRow[lastColSeen] == null)
{
resultRow[lastColSeen] = cell;
}
}
}
private void MakeWorkbookDefinition(Hashtable resultWorksheets)
{
resultWorkbookDefinition = new XmlDocument();
string ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
XmlElement workbook = XLSX.AppendElement(resultWorkbookDefinition, "workbook", ns);
workbook.SetAttribute("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
XmlElement fileVersion = XLSX.AppendElement(workbook, "fileVersion", ns);
fileVersion.SetAttribute("appName", "xl");
fileVersion.SetAttribute("lastEdited", "4");
fileVersion.SetAttribute("lowestEdited", "4");
XmlElement sheets = XLSX.AppendElement(workbook, "sheets", ns);
int iSheet = 1;
foreach (ResultWorksheet resultWorksheet in resultWorksheets.Values)
{
XmlElement sheet = XLSX.AppendElement(sheets, "sheet", ns);
sheet.SetAttribute("name", resultWorksheet.SheetName);
sheet.SetAttribute("sheetId", Convert.ToString(iSheet));
sheet.SetAttribute("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", resultWorksheet.SheetId);
iSheet++;
}
}
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 AddEverythingToStylesWhichExcelWantsToHaveToOpenTheCreatedFile(ref XmlElement stylesheet)
{
string ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
XmlElement fonts = XLSX.AppendElement(stylesheet, "fonts", ns);
fonts.SetAttribute("count", "1");
XmlElement font = XLSX.AppendElement(fonts, "font", ns);
XLSX.AppendElement(font, "sz", ns).SetAttribute("val", "10");
XLSX.AppendElement(font, "name", ns).SetAttribute("val", "Arial");
XmlElement fills = XLSX.AppendElement(stylesheet, "fills", ns);
fills.SetAttribute("count", "1");
XmlElement fill = XLSX.AppendElement(fills, "fill", ns);
XLSX.AppendElement(fill, "patternFill", ns).SetAttribute("patternType", "none");
XmlElement borders = XLSX.AppendElement(stylesheet, "borders", ns);
borders.SetAttribute("count", "1");
XmlElement border = XLSX.AppendElement(borders, "border", ns);
XLSX.AppendElement(border, "left", ns);
XLSX.AppendElement(border, "right", ns);
XLSX.AppendElement(border, "top", ns);
XLSX.AppendElement(border, "bottom", ns);
XLSX.AppendElement(border, "diagonal", ns);
XmlElement cellStyleXfs = XLSX.AppendElement(stylesheet, "cellStyleXfs", ns);
cellStyleXfs.SetAttribute("count", "1");
XmlElement xf = XLSX.AppendElement(cellStyleXfs, "xf", ns);
xf.SetAttribute("numFmtId", "0");
xf.SetAttribute("fontId", "0");
xf.SetAttribute("fillId", "0");
xf.SetAttribute("borderId", "0");
}
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 CreateResultSheetContentsAndClearMatrices(ref Hashtable resultWorksheets)
{
foreach (ResultWorksheet resultWorksheet in resultWorksheets.Values)
{
XmlDocument resultSheetDocument = new XmlDocument();
string ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
XmlElement worksheet = XLSX.AppendElement(resultSheetDocument, "worksheet", ns);
XmlElement sheetData = XLSX.AppendElement(worksheet, "sheetData", ns);
int i = 0;
foreach (ArrayList cells in resultWorksheet.Matrix)
{
if (cells == null || cells.Count == 0)
{
i++;
continue;
}
XmlElement row = XLSX.AppendElement(sheetData, "row", ns);
row.SetAttribute("r", Convert.ToString(i));
int j = 0;
foreach (XmlElement cell in cells)
{
if (cell == null)
{
j++;
continue;
}
string val = cell.InnerText;
XmlElement c = XLSX.AppendElement(row, "c", ns);
string colName = XLSX.Index2ColumnName(j);
c.SetAttribute("r", colName + Convert.ToString(i));
string t = cell.GetAttribute("t");
if (t == "s")
{
if (val.IndexOf('r') != -1 || val.IndexOf('n') != -1)
{
c.SetAttribute("s", "4");
}
c.SetAttribute("t", "inlineStr");
XmlElement iis = XLSX.AppendElement(c, "is", ns);
XmlElement tToo = XLSX.AppendElement(iis, "t", ns);
tToo.InnerText = val;
}
else
{
string attrName = "n";
bool checkForDateTimeStyle = false;
if (t == "b")
{
attrName = "b";
}
else if (t == "e")
{
attrName = "e";
}
else
{
checkForDateTimeStyle = true;
}
c.SetAttribute("t", attrName);
if (checkForDateTimeStyle)
{
string formatHint = cell.GetAttribute("f");
if (formatHint.Length > 0)
{
foreach (GoodDateTimeFormat gdf in goodDateTimeFormats)
{
if (formatHint == gdf.name)
{
c.SetAttribute("s", Convert.ToString(gdf.number));
}
}
}
}
XmlElement v = XLSX.AppendElement(c, "v", ns);
v.InnerText = val;
}
j++;
}
i++;
}
resultWorksheet.Matrix.Clear();
resultWorksheet.Contents = resultSheetDocument;
}
}
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);
}
}