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