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