public XlsxWriterInternal(Workbook workbook) { _workbook = workbook; _relationshipCounter = new RelationshipCounter(); _sharedStrings = new SharedStrings(); _package = new XlsxPackage(); _styles = GetXlsxStyles(); }
public XlsxWriterInternal(Workbook workbook, CompressionOption compressionOption) { _workbook = workbook; _relationshipCounter = new RelationshipCounter(); _sharedStrings = new SharedStrings(); _package = new XlsxPackage { CompressionOption = compressionOption }; _styles = GetXlsxStyles(); }
public XlsxWriterInternal(Workbook workbook, CompressionOption compressionOption) { _workbook = workbook; _relationshipCounter = new RelationshipCounter(); _sharedStrings = new SharedStrings(); _package = new XlsxPackage { CompressionOption = compressionOption }; _styles = GetXlsxStyles(); }
private static Dictionary <int, XlsxRow> GetXlsxRows(Worksheet sheet, IList <XlsxCellStyle> styles, SharedStrings sharedStrings) { var rows = new Dictionary <int, XlsxRow>(); if (!Enum.IsDefined(typeof(LargeNumberHandlingMode), sheet.LargeNumberHandlingMode)) { throw new InvalidOperationException( $"Invalid value for {nameof(Worksheet.LargeNumberHandlingMode)} in sheet {sheet.Name}: {sheet.LargeNumberHandlingMode}"); } // The order matters! foreach (var cell in sheet.Cells.OrderBy(c => c.Key.Row).ThenBy(c => c.Key.Column)) { if (!rows.ContainsKey(cell.Key.Row)) { rows[cell.Key.Row] = new XlsxRow { RowIndex = cell.Key.Row + 1 }; } var styleIndex = styles.IndexOf(cell.Value.XlsxCellStyle) + 1; var xc = new XlsxCell { StyleIndex = styleIndex, Reference = cell.Key.ToString() }; switch (cell.Value.CellType) { case CellType.Text: xc.CellType = XlsxCellTypes.SharedString; xc.Value = sharedStrings.GetStringIndex((string)cell.Value.Value); break; case CellType.Formula: xc.CellType = XlsxCellTypes.FormulaString; xc.Value = (string)cell.Value.Value; break; case CellType.Number: // Fun: Excel can't handle large numbers as numbers // https://support.microsoft.com/en-us/help/2643223/long-numbers-are-displayed-incorrectly-in-excel var numVal = Convert.ToDecimal(cell.Value.Value); if (sheet.LargeNumberHandlingMode != LargeNumberHandlingMode.None && Cell.IsLargeNumber(numVal)) { switch (sheet.LargeNumberHandlingMode) { case LargeNumberHandlingMode.StoreAsText: xc.CellType = XlsxCellTypes.SharedString; xc.Value = sharedStrings.GetStringIndex( numVal.ToString(System.Globalization.CultureInfo.InvariantCulture)); break; default: throw new InvalidOperationException( "Unhandled LargeNumberHandlingMode: " + sheet.LargeNumberHandlingMode); } } else { xc.CellType = XlsxCellTypes.Number; xc.Value = Convert.ToDecimal(cell.Value.Value) .ToString(System.Globalization.CultureInfo.InvariantCulture); } break; case CellType.Date: xc.CellType = XlsxCellTypes.Number; if (cell.Value.Value != null) { xc.Value = ((DateTime)cell.Value.Value).ToOleAutDate(); } break; default: throw new ArgumentException("Unknown Cell Type: " + cell.Value.CellType + " in cell " + cell.Key.ToString() + " of " + sheet.Name); } rows[cell.Key.Row].Cells.Add(xc); } return(rows); }
/// <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); }
// ReSharper disable once MemberHidesStaticFromOuterClass internal static void Save(Workbook workbook, Stream outputStream, bool compress) { var relationshipCounter = new RelationshipCounter(); var package = new XlsxPackage(); // Must be done before calling GetXlsxStyles as it may add styles foreach (var sheet in workbook.Sheets) { HandleLargeNumbers(sheet); } ExtractWorkbookSpecialXmlParts(workbook, out var styles, out var ignoredErrors); var sharedStrings = new SharedStrings(); // docProps/core.xml var cp = CreateCoreFileProperties(workbook, relationshipCounter); package.XmlFiles.Add(cp.Target); package.Relationships.Add(cp); // xl/styles.xml var stylesXml = StyleWriter.CreateStyleXml(styles); var stylesRel = new Relationship(relationshipCounter) { Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles", Target = stylesXml }; package.XmlFiles.Add(stylesXml); package.WorkbookRelationships.Add(stylesRel); // xl/worksheets/sheetX.xml var sheetPackageInfos = new List <SheetPackageInfo>(); var i = 0; foreach (var sheet in workbook.Sheets) { i++; var rel = CreateSheetFile(sheet, i, relationshipCounter, styles, ignoredErrors[sheet], sharedStrings, out var sheetRels); if (sheetRels != null) { package.XmlFiles.Add(sheetRels); } package.XmlFiles.Add(rel.Target); package.WorkbookRelationships.Add(rel); var sheetPackageInfo = new SheetPackageInfo { RelationshipId = rel.Id, SheetId = i, SheetName = sheet.Name }; if (sheet.PageSetup.PrintRepeatColumns > 0) { sheetPackageInfo.RepeatCols = "'" + sheet.Name + "'!$A:$" + CellAddressHelper.ColToReference(sheet.PageSetup.PrintRepeatColumns - 1); } if (sheet.PageSetup.PrintRepeatRows > 0) { sheetPackageInfo.RepeatRows = "'" + sheet.Name + "'!$1:$" + sheet.PageSetup.PrintRepeatRows; } sheetPackageInfos.Add(sheetPackageInfo); } // xl/sharedStrings.xml if (sharedStrings.Count > 0) { var ssx = sharedStrings.ToRelationship(relationshipCounter); package.XmlFiles.Add(ssx.Target); package.WorkbookRelationships.Add(ssx); } // xl/workbook.xml var wb = CreateWorkbookFile(sheetPackageInfos, relationshipCounter); package.XmlFiles.Add(wb.Target); package.Relationships.Add(wb); // xl/_rels/workbook.xml.rels package.SaveToStream(outputStream, compress); }