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