public static void CreateXlsx(string path, string name, IDictionary <string, IEnumerable> sheets, Dictionary <string, IEnumerable <string> > propertyNames, Dictionary <string, Func <object, string, object> > getFields, Dictionary <string, Func <string, string> > getLabels, Dictionary <string, Func <string, CellDataType> > getCellTypes = null)
{
name = GetXlsxFileName(name);
var workSheets = new List <WorksheetDfn>();
foreach (var sheet in sheets)
{
var thesePropertyNames = propertyNames != null && propertyNames.ContainsKey(sheet.Key) ? propertyNames[sheet.Key] : throw new NullReferenceException("No Property Names For Sheet " + sheet.Key);
var getField = getFields != null && getFields.ContainsKey(sheet.Key) ? getFields[sheet.Key] : throw new NullReferenceException("No Get Field Method For Sheet " + sheet.Key);
var getLabel = getLabels != null && getLabels.ContainsKey(sheet.Key) ? getLabels[sheet.Key] : (s) => s;
var getCellType = getCellTypes != null && getCellTypes.ContainsKey(sheet.Key) ? getCellTypes[sheet.Key] : (s) => CellDataType.String;
var workSheet = new WorksheetDfn();
workSheet.Name = sheet.Key.Replace(" ", "_");
workSheet.TableName = sheet.Key.Replace(" ", "_");
workSheet.ColumnHeadings = thesePropertyNames.Select(p => new CellDfn()
{
Bold = true,
Value = getLabel(p)
}).ToArray();
var rows = new List <RowDfn>();
foreach (var e in sheet.Value)
{
rows.Add(new RowDfn
{
Cells = thesePropertyNames.Select(p => new CellDfn
{
Value = getField(e, p),
CellDataType = getCellType(p)
}).ToArray()
});
}
workSheet.Rows = rows.ToArray();
workSheets.Add(workSheet);
}
var wb = new WorkbookDfn();
wb.Worksheets = workSheets.ToArray();
var outXlsx = new FileInfo(Path.Combine(path, name));
SpreadsheetWriter.Write(outXlsx.FullName, wb);
}
public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData, int tableCounter)
{
Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");
if (!validSheetName.IsMatch(worksheetData.Name))
{
throw new InvalidSheetNameException(worksheetData.Name);
}
// throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
string UCName = worksheetData.Name.ToUpper();
XDocument wXDoc = sDoc.WorkbookPart.GetXDocument();
if (wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Attributes(SSNoNamespace.name)
.Select(a => ((string)a).ToUpper())
.Contains(UCName))
{
throw new WorksheetAlreadyExistsException(worksheetData.Name);
}
// create the worksheet with the supplied name
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
int?size = (int?)vector.Attribute(SSNoNamespace.size);
if (size == null)
{
size = 1;
}
else
{
size = size + 1;
}
vector.SetAttributeValue(SSNoNamespace.size, size);
vector.Add(
new XElement(VT.lpstr, worksheetData.Name));
XElement i4 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.Elements(VT.variant)
.Elements(VT.i4)
.FirstOrDefault();
if (i4 != null)
{
i4.Value = ((int)i4 + 1).ToString();
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
}
WorkbookPart workbook = sDoc.WorkbookPart;
string rId = "R" + Guid.NewGuid().ToString().Replace("-", "");
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);
XDocument wbXDoc = workbook.GetXDocument();
XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();
sheets.Add(
new XElement(S.sheet,
new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
new XAttribute(R.id, rId)));
workbook.PutXDocument();
string ws = S.s.ToString();
string relns = R.r.ToString();
using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
{
partXmlWriter.WriteStartDocument();
partXmlWriter.WriteStartElement("worksheet", ws);
partXmlWriter.WriteStartElement("sheetData", ws);
int numColumnHeadingRows = 0;
int numColumns = 0;
int numColumnsInRows = 0;
int numRows;
if (worksheetData.ColumnHeadings != null)
{
RowDfn row = new RowDfn
{
Cells = worksheetData.ColumnHeadings
};
SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
}
SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
out numRows);
int totalRows = numColumnHeadingRows + numRows;
int totalColumns = Math.Max(numColumns, numColumnsInRows);
if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
{
partXmlWriter.WriteEndElement();
string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
partXmlWriter.WriteStartElement("tableParts", ws);
partXmlWriter.WriteStartAttribute("count");
partXmlWriter.WriteValue(1);
partXmlWriter.WriteEndAttribute();
partXmlWriter.WriteStartElement("tablePart", ws);
partXmlWriter.WriteStartAttribute("id", relns);
partXmlWriter.WriteValue(rId2);
TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
XDocument tXDoc = tdp.GetXDocument();
XElement table = new XElement(S.table,
new XAttribute(SSNoNamespace.id, tableCounter),
new XAttribute(SSNoNamespace.name, worksheetData.TableName),
new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
new XAttribute(SSNoNamespace.totalsRowShown, 0),
new XElement(S.autoFilter,
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
new XElement(S.tableColumns,
new XAttribute(SSNoNamespace.count, totalColumns),
worksheetData.ColumnHeadings.Select((ch, i) =>
new XElement(S.tableColumn,
new XAttribute(SSNoNamespace.id, i + 1),
new XAttribute(SSNoNamespace.name, ch.Value)))),
new XElement(S.tableStyleInfo,
new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
new XAttribute(SSNoNamespace.showFirstColumn, 0),
new XAttribute(SSNoNamespace.showLastColumn, 0),
new XAttribute(SSNoNamespace.showRowStripes, 1),
new XAttribute(SSNoNamespace.showColumnStripes, 0)));
tXDoc.Add(table);
tdp.PutXDocument();
}
}
}
sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument();
sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
