/// <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);
}
internal void Save(Stream outputStream, bool compress)
{
// docProps/core.xml
var cp = CreateCoreFileProperties();
_package.XmlFiles.Add(cp.Target);
_package.Relationships.Add(cp);
// xl/styles.xml
var styles = StyleWriter.CreateStyleXml(_styles);
var stylesRel = new Relationship(_relationshipCounter)
{
Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles",
Target = styles
};
_package.XmlFiles.Add(styles);
_package.WorkbookRelationships.Add(stylesRel);
// xl/worksheets/sheetX.xml
var sheetinfos = new List <SheetPackageInfo>();
int i = 0;
foreach (var sheet in _workbook.Sheets)
{
i++;
XmlFile sheetRels;
var rel = CreateSheetFile(sheet, i, out sheetRels);
if (sheetRels != null)
{
_package.XmlFiles.Add(sheetRels);
}
_package.XmlFiles.Add(rel.Target);
_package.WorkbookRelationships.Add(rel);
var sheetinfo = new SheetPackageInfo
{
RelationshipId = rel.Id,
SheetId = i,
SheetName = sheet.Name
};
if (sheet.PageSetup.PrintRepeatColumns > 0)
{
sheetinfo.RepeatCols = "'" + sheet.Name + "'!$A:$" + CellAddressHelper.ColToReference(sheet.PageSetup.PrintRepeatColumns - 1);
}
if (sheet.PageSetup.PrintRepeatRows > 0)
{
sheetinfo.RepeatRows = "'" + sheet.Name + "'!$1:$" + sheet.PageSetup.PrintRepeatRows;
}
sheetinfos.Add(sheetinfo);
}
// 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(sheetinfos);
_package.XmlFiles.Add(wb.Target);
_package.Relationships.Add(wb);
// xl/_rels/workbook.xml.rels
_package.SaveToStream(outputStream, compress);
}
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 sheetinfos = new List <SheetPackageInfo>();
int i = 0;
foreach (var sheet in workbook.Sheets)
{
i++;
var rel = CreateSheetFile(sheet, i, relationshipCounter, styles, ignoredErrors[sheet], sharedStrings, out XmlFile sheetRels);
if (sheetRels != null)
{
package.XmlFiles.Add(sheetRels);
}
package.XmlFiles.Add(rel.Target);
package.WorkbookRelationships.Add(rel);
var sheetinfo = new SheetPackageInfo
{
RelationshipId = rel.Id,
SheetId = i,
SheetName = sheet.Name
};
if (sheet.PageSetup.PrintRepeatColumns > 0)
{
sheetinfo.RepeatCols = "'" + sheet.Name + "'!$A:$" + CellAddressHelper.ColToReference(sheet.PageSetup.PrintRepeatColumns - 1);
}
if (sheet.PageSetup.PrintRepeatRows > 0)
{
sheetinfo.RepeatRows = "'" + sheet.Name + "'!$1:$" + sheet.PageSetup.PrintRepeatRows;
}
sheetinfos.Add(sheetinfo);
}
// 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(sheetinfos, relationshipCounter);
package.XmlFiles.Add(wb.Target);
package.Relationships.Add(wb);
// xl/_rels/workbook.xml.rels
package.SaveToStream(outputStream, compress);
}
/// <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);
}
internal void AddRelationship(Relationship rel)
{
CheckClosed();
_relationships.Add(rel);
}
internal void Save(Stream outputStream)
{
// docProps/core.xml
var cp = CreateCoreFileProperties();
_package.XmlFiles.Add(cp.Target);
_package.Relationships.Add(cp);
// xl/styles.xml
var styles = StyleWriter.CreateStyleXml(_styles);
var stylesRel = new Relationship(_relationshipCounter)
{
Type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles",
Target = styles
};
_package.XmlFiles.Add(styles);
_package.WorkbookRelationships.Add(stylesRel);
// xl/worksheets/sheetX.xml
var sheetinfos = new List<SheetPackageInfo>();
int i = 0;
foreach (var sheet in _workbook.Sheets)
{
i++;
XmlFile sheetRels;
var rel = CreateSheetFile(sheet, i, out sheetRels);
if (sheetRels != null)
{
_package.XmlFiles.Add(sheetRels);
}
_package.XmlFiles.Add(rel.Target);
_package.WorkbookRelationships.Add(rel);
var sheetinfo = new SheetPackageInfo
{
RelationshipId = rel.Id,
SheetId = i,
SheetName = sheet.Name
};
if (sheet.PageSetup.PrintRepeatColumns > 0)
{
sheetinfo.RepeatCols = "'" + sheet.Name + "'!$A:$" + CellAddressHelper.ColToReference(sheet.PageSetup.PrintRepeatColumns-1);
}
if (sheet.PageSetup.PrintRepeatRows > 0)
{
sheetinfo.RepeatRows = "'" + sheet.Name + "'!$1:$" + sheet.PageSetup.PrintRepeatRows;
}
sheetinfos.Add(sheetinfo);
}
// 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(sheetinfos);
_package.XmlFiles.Add(wb.Target);
_package.Relationships.Add(wb);
// xl/_rels/workbook.xml.rels
_package.SaveToStream(outputStream);
}
/// <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;
}
