/// <summary> /// Create a styles.xml file /// </summary> /// <param name="styles"></param> /// <returns></returns> internal static XmlFile CreateStyleXml(IList<XlsxCellStyle> styles) { var uniqueBorders = styles.Select(s => s.Border).Where(s => s != CellBorder.None).Distinct().ToList(); uniqueBorders.Insert(0, CellBorder.None); var numberFormats = styles.Select(s => s.Format).Distinct().ToList(); var uniqueFonts = styles.Select(s => s.Font).Distinct().ToList(); uniqueFonts.Insert(0, new XlsxFont()); var file = new XmlFile { ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml", Path = "xl/styles.xml" }; var doc = new XDocument(new XElement(Namespaces.workbook + "styleSheet", new XAttribute("xmlns", Namespaces.workbook))); StyleAddNumFmtsElement(doc, numberFormats); StyleAddFontsElement(doc, uniqueFonts); StyleAddFillsElement(doc); StyleAddBordersElement(doc, uniqueBorders); StyleAddCellStyleXfsElement(doc); StyleAddCellXfsElement(doc, styles, uniqueBorders, numberFormats, uniqueFonts); file.Content = doc; return file; }
/// <summary> /// Create the xl/_rels/workbook.xml.rels file /// </summary> /// <returns></returns> internal XmlFile WorkbookRelsXml() { var file = new XmlFile { ContentType = "application/vnd.openxmlformats-package.relationships+xml", Path = "xl/_rels/workbook.xml.rels" }; var content = new XDocument(new XElement(Namespaces.relationship + "Relationships", new XAttribute("xmlns", Namespaces.relationship))); foreach (var rel in WorkbookRelationships) { var elem = new XElement(Namespaces.relationship + "Relationship", new XAttribute("Target", "/" + rel.Target.Path), new XAttribute("Type", rel.Type), new XAttribute("Id", rel.Id)); content.Root.Add(elem); } file.Content = content; return(file); }
/// <summary> /// Create the XmlFile for the Package. /// </summary> /// <returns></returns> internal XmlFile ToXmlFile() { var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"; file.Path = "xl/sharedStrings.xml"; var sst = new XDocument(new XElement(Namespaces.x + "sst", new XAttribute("xmlns", Namespaces.x), new XAttribute("count", Count), new XAttribute("uniqueCount", UniqueCount) )); foreach (var kvp in _sharedStrings.OrderBy(k => k.Value)) { var str = _sanitizeRegex.Replace(kvp.Key, string.Empty); var se = new XElement(Namespaces.x + "si", new XElement(Namespaces.x + "t", str)); sst.Root.Add(se); } file.Content = sst; return file; }
/// <summary> /// Create the XmlFile for the Package. /// </summary> /// <returns></returns> internal XmlFile ToXmlFile() { var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"; file.Path = "xl/sharedStrings.xml"; var sst = new XDocument(new XElement(Namespaces.x + "sst", new XAttribute("xmlns", Namespaces.x), new XAttribute("count", Count), new XAttribute("uniqueCount", UniqueCount) )); foreach (var kvp in _sharedStrings.OrderBy(k => k.Value)) { var str = _sanitizeRegex.Replace(kvp.Key, string.Empty); var se = new XElement(Namespaces.x + "si", new XElement(Namespaces.x + "t", str)); sst.Root.Add(se); } file.Content = sst; return(file); }
/// <summary> /// Create the xl/worksheets/sheetX.xml file /// </summary> /// <param name="sheet"></param> /// <param name="sheetIndex"></param> /// <param name="relationshipCounter"></param> /// <param name="styles"></param> /// <param name="ignoredErrors"></param> /// <param name="sharedStrings"></param> /// <param name="sheetRels">If this worksheet needs an xl/worksheets/_rels/sheetX.xml.rels file</param> /// <returns></returns> private static Relationship CreateSheetFile(Worksheet sheet, int sheetIndex, RelationshipCounter relationshipCounter, IList <XlsxCellStyle> styles, XlsxIgnoredErrorCollection ignoredErrors, SharedStrings sharedStrings, out XmlFile sheetRels) { var rows = GetXlsxRows(sheet, styles, sharedStrings); var file = new XmlFile { ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", Path = "xl/worksheets/sheet" + sheetIndex + ".xml" }; var doc = new XDocument(new XElement(Namespaces.workbook + "worksheet", new XAttribute("xmlns", Namespaces.workbook), new XAttribute(XNamespace.Xmlns + "r", Namespaces.relationship), new XAttribute(XNamespace.Xmlns + "mc", Namespaces.mc), new XAttribute(XNamespace.Xmlns + "x14ac", Namespaces.x14ac), new XAttribute(XNamespace.Xmlns + "or", Namespaces.officeRelationships), new XAttribute(Namespaces.mc + "Ignorable", "x14ac") )); WriteSheetViews(sheet, doc); var sheetFormatPr = new XElement(Namespaces.workbook + "sheetFormatPr"); sheetFormatPr.Add(new XAttribute("defaultRowHeight", 15)); doc.Root?.Add(sheetFormatPr); if (sheet.ColumnWidths.Any()) { var cols = new XElement(Namespaces.workbook + "cols"); foreach (var cw in sheet.ColumnWidths) { var rowId = cw.Key + 1; var col = new XElement(Namespaces.workbook + "col", new XAttribute("min", rowId), new XAttribute("max", rowId), new XAttribute("width", (decimal)cw.Value + ExcelColumnWidthDifference), new XAttribute("customWidth", 1)); cols.Add(col); } doc.Root?.Add(cols); } var sheetData = new XElement(Namespaces.workbook + "sheetData"); foreach (var row in rows.OrderBy(rk => rk.Key)) { var re = new XElement(Namespaces.workbook + "row", new XAttribute("r", row.Value.RowIndex)); foreach (var cell in row.Value.Cells) { var ce = new XElement(Namespaces.workbook + "c", new XAttribute("r", cell.Reference), new XAttribute("t", cell.CellType), new XAttribute("s", cell.StyleIndex)); ce.Add(cell.CellType == XlsxCellTypes.FormulaString ? new XElement(Namespaces.workbook + "f", cell.Value) : new XElement(Namespaces.workbook + "v", cell.Value)); re.Add(ce); } sheetData.Add(re); } doc.Root?.Add(sheetData); sheetRels = null; var hyperlinks = sheet.Cells.Where(c => c.Value != null && !string.IsNullOrEmpty(c.Value.Hyperlink)) .ToList(); if (hyperlinks.Count > 0) { sheetRels = new XmlFile { Path = "xl/worksheets/_rels/sheet" + sheetIndex + ".xml.rels", ContentType = "application/vnd.openxmlformats-package.relationships+xml" }; var hlRelsElem = new XElement(Namespaces.relationship + "Relationships"); var hlElem = new XElement(Namespaces.workbook + "hyperlinks"); for (int i = 0; i <= hyperlinks.Count - 1; i++) { string hyperLinkRelId = "rId" + (i + 1); var link = hyperlinks[i]; var linkElem = new XElement(Namespaces.workbook + "hyperlink", new XAttribute("ref", link.Key.ToString()), new XAttribute(Namespaces.officeRelationships + "id", hyperLinkRelId) ); hlElem.Add(linkElem); hlRelsElem.Add(new XElement(Namespaces.relationship + "Relationship", new XAttribute("Id", hyperLinkRelId), new XAttribute("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"), new XAttribute("Target", link.Value.Hyperlink), new XAttribute("TargetMode", "External"))); } doc.Root?.Add(hlElem); sheetRels.Content = new XDocument(); sheetRels.Content.Add(hlRelsElem); } var pageSetup = new XElement(Namespaces.workbook + "pageSetup"); pageSetup.Add(new XAttribute("orientation", sheet.PageSetup.Orientation == Orientation.Portrait ? "portrait" : "landscape")); doc.Root?.Add(pageSetup); WritePageBreaks(sheet, doc); WriteIgnoredErrors(ignoredErrors, doc); file.Content = doc; var rel = new Relationship(relationshipCounter) { Target = file, Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" }; return(rel); }
/// <summary> /// Create the xl/workbook.xml file and associated relationship /// </summary> /// <param name="sheetInfos"></param> /// <param name="relationshipCounter"></param> /// <returns></returns> private static Relationship CreateWorkbookFile(List <SheetPackageInfo> sheetInfos, RelationshipCounter relationshipCounter) { var file = new XmlFile { ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", Path = "xl/workbook.xml" }; var doc = new XDocument(new XElement(Namespaces.workbook + "workbook", new XAttribute("xmlns", Namespaces.workbook), new XAttribute(XNamespace.Xmlns + "r", Namespaces.workbookRelationship) )); var sheets = new XElement(Namespaces.workbook + "sheets"); foreach (var si in sheetInfos) { sheets.Add(new XElement(Namespaces.workbook + "sheet", new XAttribute("name", si.SheetName), new XAttribute("sheetId", si.SheetId), new XAttribute(Namespaces.workbookRelationship + "id", si.RelationshipId) )); } doc.Root?.Add(sheets); var repeatInfos = sheetInfos .Where(si => !string.IsNullOrEmpty(si.RepeatRows) || !string.IsNullOrEmpty(si.RepeatCols)) .OrderBy(si => si.SheetId).ToList(); if (repeatInfos.Count > 0) { var dne = new XElement(Namespaces.workbook + "definedNames"); foreach (var re in repeatInfos) { var de = new XElement(Namespaces.workbook + "definedName", new XAttribute("name", "_xlnm.Print_Titles"), new XAttribute("localSheetId", re.SheetId - 1) ); if (!string.IsNullOrEmpty(re.RepeatCols) && !string.IsNullOrEmpty(re.RepeatRows)) { de.Add(new XText(re.RepeatCols + "," + re.RepeatRows)); } else if (!string.IsNullOrEmpty(re.RepeatCols)) { de.Add(new XText(re.RepeatCols)); } else if (!string.IsNullOrEmpty(re.RepeatRows)) { de.Add(new XText(re.RepeatRows)); } dne.Add(de); } doc.Root?.Add(dne); } file.Content = doc; var rel = new Relationship(relationshipCounter) { Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument", Target = file }; return(rel); }
/// <summary> /// Write an Xml File to the package /// </summary> /// <param name="pkg"></param> /// <param name="file"></param> private void WriteXmlFile(Package pkg, XmlFile file) { var part = pkg.CreatePart(new Uri("/" + file.Path, UriKind.Relative), file.ContentType, CompressionOption); byte[] content = Encoding.UTF8.GetBytes(file.Content.ToString()); using (var s = part.GetStream(FileMode.Create, FileAccess.ReadWrite)) { s.Write(content, 0, content.Length); } }
private void WriteInfoXmlFile(Package pkg) { var version = GetType().Assembly.GetName().Version; var infoXml = new XmlFile(); infoXml.Path = "simplexcel.xml"; infoXml.Content = new XDocument(new XElement(Namespaces.simplexcel + "docInfo", new XAttribute("xmlns", Namespaces.simplexcel))); infoXml.Content.Root.Add(new XElement(Namespaces.simplexcel + "version", new XAttribute("major", version.Major), new XAttribute("minor", version.Minor), new XAttribute("build", version.Build), new XAttribute("revision", version.Revision) )); infoXml.Content.Root.Add(new XElement(Namespaces.simplexcel + "created", DateTime.UtcNow)); WriteXmlFile(pkg, infoXml); }
/// <summary> /// Create the xl/_rels/workbook.xml.rels file /// </summary> /// <returns></returns> internal XmlFile WorkbookRelsXml() { var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-package.relationships+xml"; file.Path = "xl/_rels/workbook.xml.rels"; var content = new XDocument(new XElement(Namespaces.relationship + "Relationships", new XAttribute("xmlns", Namespaces.relationship))); foreach (var rel in WorkbookRelationships) { var elem = new XElement(Namespaces.relationship + "Relationship", new XAttribute("Target", "/" + rel.Target.Path), new XAttribute("Type", rel.Type), new XAttribute("Id", rel.Id)); content.Root.Add(elem); } file.Content = content; return file; }
internal void WriteXmlFile(XmlFile file) { CheckClosed(); _contentTypes["/" + file.Path] = file.ContentType; AddFile(file.Path, file.Content); }
/// <summary> /// Create a styles.xml file /// </summary> /// <param name="styles"></param> /// <returns></returns> internal static XmlFile CreateStyleXml(IList <XlsxCellStyle> styles) { var numberFormats = new List <string>(); var uniqueBorders = new List <CellBorder> { CellBorder.None }; var uniqueFonts = new List <XlsxFont> { new XlsxFont() }; // These two fills MUST exist as fill 0 (None) and 1 (Gray125) var uniqueFills = new List <PatternFill> { new PatternFill { PatternType = PatternType.None }, new PatternFill { PatternType = PatternType.Gray125 } }; foreach (var style in styles) { if (style.Border != CellBorder.None && !uniqueBorders.Contains(style.Border)) { uniqueBorders.Add(style.Border); } if (!numberFormats.Contains(style.Format)) { numberFormats.Add(style.Format); } if (style.Font != null && !uniqueFonts.Contains(style.Font)) { uniqueFonts.Add(style.Font); } if (style.Fill != null && !uniqueFills.Contains(style.Fill)) { uniqueFills.Add(style.Fill); } } var file = new XmlFile { ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml", Path = "xl/styles.xml" }; var doc = new XDocument(new XElement(Namespaces.workbook + "styleSheet", new XAttribute("xmlns", Namespaces.workbook))); StyleAddNumFmtsElement(doc, numberFormats); StyleAddFontsElement(doc, uniqueFonts); StyleAddFillsElement(doc, uniqueFills); StyleAddBordersElement(doc, uniqueBorders); StyleAddCellStyleXfsElement(doc); StyleAddCellXfsElement(doc, styles, uniqueBorders, numberFormats, uniqueFonts, uniqueFills); file.Content = doc; return(file); }
/// <summary> /// Create the xl/workbook.xml file and associated relationship /// </summary> /// <param name="sheetInfos"></param> /// <returns></returns> private Relationship CreateWorkbookFile(List<SheetPackageInfo> sheetInfos) { var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"; file.Path = "xl/workbook.xml"; var doc = new XDocument(new XElement(Namespaces.workbook + "workbook", new XAttribute("xmlns", Namespaces.workbook), new XAttribute(XNamespace.Xmlns + "r", Namespaces.workbookRelationship) )); var sheets = new XElement(Namespaces.workbook + "sheets"); foreach (var si in sheetInfos) { sheets.Add(new XElement(Namespaces.workbook + "sheet", new XAttribute("name", si.SheetName), new XAttribute("sheetId", si.SheetId), new XAttribute(Namespaces.workbookRelationship + "id", si.RelationshipId) )); } doc.Root.Add(sheets); var repeatInfos = sheetInfos.Where(si => !string.IsNullOrEmpty(si.RepeatRows) || !string.IsNullOrEmpty(si.RepeatCols)).OrderBy(si => si.SheetId).ToList(); if (repeatInfos.Count > 0) { var dne = new XElement(Namespaces.workbook + "definedNames"); foreach (var re in repeatInfos) { var de = new XElement(Namespaces.workbook + "definedName", new XAttribute("name", "_xlnm.Print_Titles"), new XAttribute("localSheetId", re.SheetId - 1) ); if (!string.IsNullOrEmpty(re.RepeatCols) && !string.IsNullOrEmpty(re.RepeatRows)) { de.Add(new XText(re.RepeatCols + "," + re.RepeatRows)); } else if (!string.IsNullOrEmpty(re.RepeatCols)) { de.Add(new XText(re.RepeatCols)); } else if (!string.IsNullOrEmpty(re.RepeatRows)) { de.Add(new XText(re.RepeatRows)); } dne.Add(de); } doc.Root.Add(dne); } file.Content = doc; var rel = new Relationship(_relationshipCounter) { Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument", Target = file }; return rel; }
/// <summary> /// Create the xl/worksheets/sheetX.xml file /// </summary> /// <param name="sheet"></param> /// <param name="sheetIndex"></param> /// <param name="sheetRels">If this worksheet needs an xl/worksheets/_rels/sheetX.xml.rels file</param> /// <returns></returns> private Relationship CreateSheetFile(Worksheet sheet, int sheetIndex, out XmlFile sheetRels) { var rows = GetXlsxRows(sheet); var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"; file.Path = "xl/worksheets/sheet" + sheetIndex + ".xml"; var doc = new XDocument(new XElement(Namespaces.workbook + "worksheet", new XAttribute("xmlns", Namespaces.workbook), new XAttribute(XNamespace.Xmlns + "r", Namespaces.relationship), new XAttribute(XNamespace.Xmlns + "mc", Namespaces.mc), new XAttribute(XNamespace.Xmlns + "x14ac", Namespaces.x14ac), new XAttribute(XNamespace.Xmlns + "or", Namespaces.officeRelationships), new XAttribute(Namespaces.mc + "Ignorable", "x14ac") )); var sheetFormatPr = new XElement(Namespaces.workbook + "sheetFormatPr"); sheetFormatPr.Add(new XAttribute("defaultRowHeight", 15)); doc.Root.Add(sheetFormatPr); if (sheet.ColumnWidths.Any()) { var cols = new XElement(Namespaces.workbook + "cols"); foreach (var cw in sheet.ColumnWidths) { var rowId = cw.Key + 1; var col = new XElement(Namespaces.workbook + "col", new XAttribute("min", rowId), new XAttribute("max", rowId), new XAttribute("width", (decimal)cw.Value + ExcelColumnWidthDifference), new XAttribute("customWidth", 1)); cols.Add(col); } doc.Root.Add(cols); } var sheetData = new XElement(Namespaces.workbook + "sheetData"); foreach (var row in rows.OrderBy(rk => rk.Key)) { var re = new XElement(Namespaces.workbook + "row", new XAttribute("r", row.Value.RowIndex)); foreach (var cell in row.Value.Cells) { var ce = new XElement(Namespaces.workbook + "c", new XAttribute("r", cell.Reference), new XAttribute("t", cell.CellType), new XAttribute("s", cell.StyleIndex), new XElement(Namespaces.workbook + "v", cell.Value)); re.Add(ce); } sheetData.Add(re); } doc.Root.Add(sheetData); var hyperlinks = sheet.Cells.Where(c => c.Value != null && !string.IsNullOrEmpty(c.Value.Hyperlink)).ToList(); if (hyperlinks.Count > 0) { sheetRels = new XmlFile(); sheetRels.Path = "xl/worksheets/_rels/sheet" + sheetIndex + ".xml.rels"; sheetRels.ContentType = "application/vnd.openxmlformats-package.relationships+xml"; var hlRelsElem = new XElement(Namespaces.relationship + "Relationships"); var hlElem = new XElement(Namespaces.workbook + "hyperlinks"); for (int i = 0; i <= hyperlinks.Count - 1; i++) { string hyperLinkRelId = "rId" + (i + 1); var link = hyperlinks[i]; var linkElem = new XElement(Namespaces.workbook + "hyperlink", new XAttribute("ref", link.Key.ToString()), new XAttribute(Namespaces.officeRelationships + "id", hyperLinkRelId) ); hlElem.Add(linkElem); hlRelsElem.Add(new XElement(Namespaces.relationship + "Relationship", new XAttribute("Id", hyperLinkRelId), new XAttribute("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"), new XAttribute("Target", link.Value.Hyperlink), new XAttribute("TargetMode", "External"))); } doc.Root.Add(hlElem); sheetRels.Content = new XDocument(); sheetRels.Content.Add(hlRelsElem); } else { sheetRels = null; } var pageSetup = new XElement(Namespaces.workbook + "pageSetup"); pageSetup.Add(new XAttribute("orientation", sheet.PageSetup.Orientation == Orientation.Portrait ? "portrait" : "landscape")); doc.Root.Add(pageSetup); file.Content = doc; var rel = new Relationship(_relationshipCounter) { Target = file, Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" }; return rel; }
/// <summary> /// Generated the docProps/core.xml which contains author, creation date etc. /// </summary> /// <returns></returns> private Relationship CreateCoreFileProperties() { var file = new XmlFile(); file.ContentType = "application/vnd.openxmlformats-package.core-properties+xml"; file.Path = "docProps/core.xml"; var dc = Namespaces.dc; var dcterms = Namespaces.dcterms; var xsi = Namespaces.xsi; var cp = Namespaces.coreProperties; var doc = new XDocument(); var root = new XElement(cp + "coreProperties", new XAttribute(XNamespace.Xmlns + "cp", cp), new XAttribute(XNamespace.Xmlns + "dc", dc), new XAttribute(XNamespace.Xmlns + "dcterms", dcterms), new XAttribute(XNamespace.Xmlns + "xsi", xsi) ); if (!string.IsNullOrEmpty(_workbook.Title)) { root.Add(new XElement(dc + "title", _workbook.Title)); } if (!string.IsNullOrEmpty(_workbook.Author)) { root.Add(new XElement(dc + "creator", _workbook.Author)); root.Add(new XElement(cp + "lastModifiedBy", _workbook.Author)); } root.Add(new XElement(dcterms + "created", DateTime.UtcNow, new XAttribute(xsi + "type", "dcterms:W3CDTF"))); root.Add(new XElement(dcterms + "modified", DateTime.UtcNow, new XAttribute(xsi + "type", "dcterms:W3CDTF"))); doc.Add(root); file.Content = doc; var rel = new Relationship(_relationshipCounter) { Target = file, Type = "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" }; return rel; }