/// <summary> /// Extract certain parts of the workbook that need special handling. /// </summary> /// <remarks> /// I don't want to loop over cells over and over again, so this is a way to run over all cells once and extract everything needed. /// </remarks> /// <param name="workbook"></param> /// <param name="styles">All unique Styles throughout the workbook</param> /// <param name="ignoredErrors">Any ignored errors. Key is the Worksheet, and the inner dictionary key is the cell range</param> private static void ExtractWorkbookSpecialXmlParts(Workbook workbook, out IList <XlsxCellStyle> styles, out IDictionary <Worksheet, XlsxIgnoredErrorCollection> ignoredErrors) { styles = new List <XlsxCellStyle>(); ignoredErrors = new Dictionary <Worksheet, XlsxIgnoredErrorCollection>(); foreach (var sheet in workbook.Sheets) { if (!ignoredErrors.ContainsKey(sheet)) { ignoredErrors[sheet] = new XlsxIgnoredErrorCollection(); } var sie = ignoredErrors[sheet]; foreach (var cellPair in sheet.Cells) { var cell = cellPair.Value; if (!styles.Contains(cell.XlsxCellStyle)) { styles.Add(cell.XlsxCellStyle); } sie.AddIgnoredError(cellPair.Key, cell.IgnoredErrors); } } }
private static void WriteIgnoredErrors(XlsxIgnoredErrorCollection ignoredErrors, XDocument doc) { if (ignoredErrors == null) { throw new ArgumentNullException(nameof(ignoredErrors)); } if (doc == null) { throw new ArgumentNullException(nameof(doc)); } if (ignoredErrors.DistinctIgnoredErrors.Count == 0) { return; } var igElem = new XElement(Namespaces.workbook + "ignoredErrors"); foreach (var ie in ignoredErrors.DistinctIgnoredErrors) { var elem = new XElement(Namespaces.workbook + "ignoredError"); elem.Add(new XAttribute("sqref", ie.GetSqRef())); if (ie.IgnoredError.EvalError) { elem.Add(new XAttribute("evalError", 1)); } if (ie.IgnoredError.TwoDigitTextYear) { elem.Add(new XAttribute("twoDigitTextYear", 1)); } if (ie.IgnoredError.NumberStoredAsText) { elem.Add(new XAttribute("numberStoredAsText", 1)); } if (ie.IgnoredError.Formula) { elem.Add(new XAttribute("formula", 1)); } if (ie.IgnoredError.FormulaRange) { elem.Add(new XAttribute("formulaRange", 1)); } if (ie.IgnoredError.UnlockedFormula) { elem.Add(new XAttribute("unlockedFormula", 1)); } if (ie.IgnoredError.EmptyCellReference) { elem.Add(new XAttribute("emptyCellReference", 1)); } if (ie.IgnoredError.ListDataValidation) { elem.Add(new XAttribute("listDataValidation", 1)); } if (ie.IgnoredError.CalculatedColumn) { elem.Add(new XAttribute("calculatedColumn", 1)); } igElem.Add(elem); } doc.Root?.Add(igElem); }
/// <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); }