The Shared Strings table in an Excel document
Exemple #1
0
 public XlsxWriterInternal(Workbook workbook)
 {
     _workbook            = workbook;
     _relationshipCounter = new RelationshipCounter();
     _sharedStrings       = new SharedStrings();
     _package             = new XlsxPackage();
     _styles = GetXlsxStyles();
 }
Exemple #2
0
 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();
 }
Exemple #4
0
            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);
            }
Exemple #5
0
            /// <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);
            }
Exemple #6
0
            // 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);
            }