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