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 Write(string fileName, WorkbookDfn workbook)
{
try
{
if (fileName == null)
{
throw new ArgumentNullException("fileName");
}
if (workbook == null)
{
throw new ArgumentNullException("workbook");
}
FileInfo fi = new FileInfo(fileName);
if (fi.Exists)
{
fi.Delete();
}
// create the blank workbook
char[] base64CharArray = _EmptyXlsx
.Where(c => c != '\r' && c != '\n').ToArray();
byte[] byteArray =
System.Convert.FromBase64CharArray(base64CharArray,
0, base64CharArray.Length);
File.WriteAllBytes(fi.FullName, byteArray);
// open the workbook, and create the TableProperties sheet, populate it
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true))
{
WorkbookPart workbookPart = sDoc.WorkbookPart;
XDocument wXDoc = workbookPart.GetXDocument();
XElement sheetElement = wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")
.FirstOrDefault();
if (sheetElement == null)
{
throw new SpreadsheetWriterInternalException();
}
string id = (string)sheetElement.Attribute(R.id);
sheetElement.Remove();
workbookPart.PutXDocument();
WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);
workbookPart.DeletePart(sPart);
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
vector.SetAttributeValue(SSNoNamespace.size, 0);
XElement lpstr = vector.Element(VT.lpstr);
lpstr.Remove();
}
XElement vector2 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.FirstOrDefault();
XElement variant = vector2
.Descendants(VT.i4)
.FirstOrDefault();
if (variant != null)
{
variant.Value = "1";
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
var tableCounter = 0;
if (workbook.Worksheets != null)
{
foreach (var worksheet in workbook.Worksheets)
{
AddWorksheet(sDoc, worksheet, ++tableCounter);
}
}
workbookPart.WorkbookStylesPart.PutXDocument();
}
}
catch (Exception e)
{
Console.WriteLine("Unhandled exception: {0} in {1}",
e.ToString(), e.Source);
throw e;
}
}
