public static void Write(string fileName, WorkbookDfn workbook)
{
try
{
if (fileName is null)
{
throw new ArgumentNullException(nameof(fileName));
}
if (workbook is null)
{
throw new ArgumentNullException(nameof(workbook));
}
var fi = new FileInfo(fileName);
if (fi.Exists)
{
fi.Delete();
}
// create the blank workbook
File.WriteAllBytes(fi.FullName, CreateBlankWorkbook());
// open the workbook, and create the TableProperties sheet, populate it
using var sDoc = SpreadsheetDocument.Open(fi.FullName, true);
SaveWorkbookToSpreadSheet(sDoc, workbook);
}
catch (Exception e)
{
Console.WriteLine($"Unhandled exception: {e} in {e.Source}");
throw;
}
}
public WorkbookDfn GenerateExcelSheet(DailyReportData dailyReportSheetData)
{
WorkbookDfn wb = new WorkbookDfn();
WorksheetDfn ws = new WorksheetDfn();
Rows = new List <RowDfn>();
SetWorksheetNames(ws, dailyReportSheetData.SheetName);
SetColumnHeaders(ws);
SetDailyReportInfo(ws, dailyReportSheetData.ReportInfo);
Rows.Add(new RowDfn {
Cells = new CellDfn[] { new CellDfn {
Value = ""
} }
});
SetActivityLogHeader(ws, dailyReportSheetData.ActivityLogHeaders);
SetActivityLogRows(ws, dailyReportSheetData.ActivityLog);
Rows.Add(new RowDfn {
Cells = new CellDfn[] { new CellDfn {
Value = ""
} }
});
SetReportBudgetHeader(ws, dailyReportSheetData.ReportBudgetHeaders);
SetReportBudgetRows(ws, dailyReportSheetData.ReportBudget);
ws.Rows = Rows;
List <WorksheetDfn> worksheetDfns = new List <WorksheetDfn> {
ws
};
wb.Worksheets = worksheetDfns;
return(wb);
}
public WorkbookDfn GenerateExcelSheet(ProductionSheetData productionSheetData)
{
WorkbookDfn wb = new WorkbookDfn();
WorksheetDfn ws = new WorksheetDfn();
SetWorksheetNames(ws);
SetColumnHeaders(ws, productionSheetData);
SetRowData(ws, productionSheetData);
List <WorksheetDfn> worksheetDfns = new List <WorksheetDfn> {
ws
};
wb.Worksheets = worksheetDfns;
return(wb);
}
public ExcelWriter(IConnectionContext context)
{
_fileInfo = new FileInfo(context.Connection.File);
_fields = context.Entity.GetAllOutputFields().Where(f => !f.System).ToArray();
_rowDfns = new List <RowDfn>();
_workbook = new WorkbookDfn {
Worksheets = new[] { new WorksheetDfn {
Name = context.Entity.Alias.Left(32),
ColumnHeadings = _fields.Select(field =>
new CellDfn {
Value = field.Label,
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Center
}
),
Rows = _rowDfns
} }
};
}
public static void WriteTo(this WorkbookDfn workbook, Stream stream)
{
if (stream is null)
{
throw new ArgumentNullException(nameof(stream));
}
if (workbook is null)
{
throw new ArgumentNullException(nameof(workbook));
}
// create the blank workbook
foreach (var b in CreateBlankWorkbook())
{
stream.WriteByte(b);
}
stream.Position = 0;
// open the workbook, and create the TableProperties sheet, populate it
using (var sDoc = SpreadsheetDocument.Open(stream, true))
SaveWorkbookToSpreadSheet(sDoc, workbook);
stream.Position = 0;
}
private static void Main()
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
var wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "DataType",
Bold = true,
},
new CellDfn
{
Value = "Value",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = true,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = false,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "String",
},
new CellDfn {
CellDataType = CellDataType.String,
Value = "A String",
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int?",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (int?)100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int? (is null)",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = null,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "uint",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (uint)101,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "long",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = long.MaxValue,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "float",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (float)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "double",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "decimal",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 8),
FormatCode = "mm-dd-yy",
},
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 9),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Center,
},
}
},
}
}
}
};
SpreadsheetWriter.Write(Path.Combine(tempDi.FullName, "Test2.xlsx"), wb);
}
static void Main(string[] args)
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
TableName = "NamesAndRates",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "Name",
Bold = true,
},
new CellDfn
{
Value = "Age",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
},
new CellDfn
{
Value = "Rate",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
}
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Eric",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 50,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)45.00,
FormatCode = "0.00",
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Bob",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 42,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)78.00,
FormatCode = "0.00",
},
}
},
}
}
}
};
SpreadsheetWriter.Write(Path.Combine(tempDi.FullName, "Test1.xlsx"), wb);
}
public void Sample2()
{
var wb = new WorkbookDfn
{
Worksheets = new[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
ColumnHeadings = new[]
{
new CellDfn
{
Value = "DataType",
Bold = true,
},
new CellDfn
{
Value = "Value",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
},
Rows = new[]
{
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = true,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = false,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "String",
},
new CellDfn {
CellDataType = CellDataType.String,
Value = "A String",
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (int)100,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int?",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (int?)100,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int? (is null)",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = null,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "uint",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (uint)101,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "long",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = Int64.MaxValue,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "float",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (float)123.45,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "double",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (double)123.45,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "decimal",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)123.45,
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 8),
FormatCode = "mm-dd-yy",
},
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 9),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Center,
},
}
},
}
}
}
};
var fileName = Path.Combine(TempDir, "Sw_Example2.xlsx");
using var stream = File.Open(fileName, FileMode.OpenOrCreate);
wb.WriteTo(stream);
}
public void Sample1()
{
var wb = new WorkbookDfn
{
Worksheets = new[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
TableName = "NamesAndRates",
ColumnHeadings = new[]
{
new CellDfn
{
Value = "Name",
Bold = true,
},
new CellDfn
{
Value = "Age",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
},
new CellDfn
{
Value = "Rate",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
}
},
Rows = new[]
{
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Eric",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 50,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)45.00,
FormatCode = "0.00",
},
}
},
new RowDfn
{
Cells = new[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Bob",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 42,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)78.00,
FormatCode = "0.00",
},
}
},
}
}
}
};
var fileName = Path.Combine(TempDir, "Sw_Example1.xlsx");
using var stream = File.Open(fileName, FileMode.OpenOrCreate);
wb.WriteTo(stream);
}
static void Main(string[] args)
{
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "DataType",
Bold = true,
},
new CellDfn
{
Value = "Value",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = true,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = false,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "String",
},
new CellDfn {
CellDataType = CellDataType.String,
Value = "A String",
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (int)100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int?",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (int?)100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int? (is null)",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = null,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "uint",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (uint)101,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "long",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = Int64.MaxValue,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "float",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (float)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "double",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (double)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "decimal",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 8),
FormatCode = "mm-dd-yy",
},
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 9),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Center,
},
}
},
}
}
}
};
SpreadsheetWriter.Write("Test2.xlsx", wb);
}
public void SW002_AllDataTypes()
{
var wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "DataType",
Bold = true,
},
new CellDfn
{
Value = "Value",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = true,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Boolean",
},
new CellDfn {
CellDataType = CellDataType.Boolean,
Value = false,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "String",
},
new CellDfn {
CellDataType = CellDataType.String,
Value = "A String",
HorizontalCellAlignment = HorizontalCellAlignment.Right,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int?",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 100,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "int? (is null)",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = null,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "uint",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 101,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "long",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = long.MaxValue,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "float",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (float)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "double",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "decimal",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)123.45,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "date (t:str)",
},
new CellDfn {
Value = new DateTime(2012, 1, 8).ToOADate(),
FormatCode = "mm-dd-yy",
Bold = true,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "date (t:str)",
},
new CellDfn {
Value = new DateTime(2012, 1, 9).ToOADate(),
FormatCode = "mm-dd-yy",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Center,
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "date (t:d)",
},
new CellDfn {
CellDataType = CellDataType.Date,
Value = new DateTime(2012, 1, 11).ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff"),
},
}
},
}
}
}
};
var outXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, "SW002-DataTypes.xlsx"));
SpreadsheetWriter.Write(outXlsx.FullName, wb);
Validate(outXlsx);
}
public void SW001_Simple()
{
var wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
TableName = "NamesAndRates",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "Name",
Bold = true,
},
new CellDfn
{
Value = "Age",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
},
new CellDfn
{
Value = "Rate",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
}
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Eric",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 50,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)45.00,
FormatCode = "0.00",
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Bob",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 42,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)78.00,
FormatCode = "0.00",
},
}
},
}
}
}
};
var outXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, "SW001-Simple.xlsx"));
SpreadsheetWriter.Write(outXlsx.FullName, wb);
Validate(outXlsx);
}
static void Main(string[] args)
{
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
new WorksheetDfn
{
Name = "MyFirstSheet",
TableName = "NamesAndRates",
ColumnHeadings = new CellDfn[]
{
new CellDfn
{
Value = "Name",
Bold = true,
},
new CellDfn
{
Value = "Age",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
},
new CellDfn
{
Value = "Rate",
Bold = true,
HorizontalCellAlignment = HorizontalCellAlignment.Left,
}
},
Rows = new RowDfn[]
{
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Eric",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 50,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)45.00,
FormatCode = "0.00",
},
}
},
new RowDfn
{
Cells = new CellDfn[]
{
new CellDfn {
CellDataType = CellDataType.String,
Value = "Bob",
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = 42,
},
new CellDfn {
CellDataType = CellDataType.Number,
Value = (decimal)78.00,
FormatCode = "0.00",
},
}
},
}
}
}
};
SpreadsheetWriter.Write("../../Test1.xlsx", wb);
}
public async Task <bool> SaveLargeExcel2Async(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
{
AllScores = composite;
// string path = Path.GetDirectoryName(myfile);
bool isgen = false;
string n = " n = " + AllScores.Count() + ".xlsx";
string filename = intake.Year.ToString();
filename += " intake cycle Logistics Composite ";
// Get the date today
DateTime dt = new DateTime();
dt = DateTime.Now;
string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");
filename = filename + myDay + n;
filename = Path.Combine(folder, filename);
await LoadNBTVenuesAsync(); // loads all venues into memory
//generate a collection of Logistics objects
await CompositToLogisticsCompositeAsync();
composite.Clear();
AllScores.Clear();
// column heading for excel
string[] ColumnHeadings = new[]
{
"Test Session ID", "NBT Reference", "Surname", "First Name",
"Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
"Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
"Venue", "Gender", "AL Score",
"AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
"AQL TEST LANGUAGE", "MATHS TEST LANGUAGE", "Test Type", "Venue Province",
};
// Size of spreadsheet
var data = new[]
{
new {
SheetName = "Logistics Composite",
NumberCols = ColumnHeadings.Count(),
Numberrows = LComposite.Count(),
},
};
// generate the workbook and spreadsheets
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = data
.Select(d => new WorksheetDfn
{
Name = d.SheetName,
ColumnHeadings = Enumerable.Range(0, d.NumberCols)
.Select(c => new CellDfn
{
Value = ColumnHeadings[c],
Bold = true,
}),
Rows = Enumerable.Range(0, d.Numberrows)
.Select(r => new RowDfn
{
Cells = Enumerable.Range(0, d.NumberCols)
.Select(c =>
new CellDfn
{
Value =
c == 0 ? LComposite.ElementAt(r).SessionId :
c == 1 ? LComposite.ElementAt(r).NBT :
c == 2 ? LComposite.ElementAt(r).Surname :
c == 3 ? LComposite.ElementAt(r).Name :
c == 4 ? LComposite.ElementAt(r).MiddleInitials :
c == 5 ? LComposite.ElementAt(r).SouthAfricanID :
c == 6 ? LComposite.ElementAt(r).Passport :
c == 7 ? LComposite.ElementAt(r).Birth :
c == 8 ? LComposite.ElementAt(r).WroteAL :
c == 9 ? LComposite.ElementAt(r).WroteQL :
c == 10 ? LComposite.ElementAt(r).WroteMat :
c == 11 ? LComposite.ElementAt(r).StudentNo :
c == 12 ? LComposite.ElementAt(r).Faculty :
c == 13 ? LComposite.ElementAt(r).Programme :
c == 14 ? LComposite.ElementAt(r).TestDate :
c == 15 ? LComposite.ElementAt(r).Venue :
c == 16 ? LComposite.ElementAt(r).Sex :
c == 17 ? LComposite.ElementAt(r).ALScore :
c == 18 ? LComposite.ElementAt(r).ALLevel :
c == 19 ? LComposite.ElementAt(r).QLScore :
c == 20 ? LComposite.ElementAt(r).QLLevel :
c == 21 ? LComposite.ElementAt(r).MatScore :
c == 22 ? LComposite.ElementAt(r).MatLevel :
c == 23 ? LComposite.ElementAt(r).AQLLanguage :
c == 24 ? LComposite.ElementAt(r).MatLanguage :
c == 25 ? LComposite.ElementAt(r).TestType :
c == 26 ? LComposite.ElementAt(r).VenueProvince : "",
HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
c == 8 ? HorizontalCellAlignment.Center :
c == 9 ? HorizontalCellAlignment.Center :
c == 10 ? HorizontalCellAlignment.Center :
c == 16 ? HorizontalCellAlignment.Center :
c == 17 ? HorizontalCellAlignment.Center :
c == 19 ? HorizontalCellAlignment.Center :
c == 21 ? HorizontalCellAlignment.Center :
c == 23 ? HorizontalCellAlignment.Center :
c == 24 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
// FormatCode = c == 5 ? "0000000000000" :"",
})
}),
})
};
// stream write to excel file
SpreadsheetWriter.Write(filename, wb);
// SpreadsheetWriter.Write()
isgen = true;
LComposite.Clear();
LComposite = null;
return(isgen);
}
// Open document for saving excel data
public async Task <bool> SaveLargeExcelAsync(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
{
AllScores = composite;
// string path = Path.GetDirectoryName(folder);
bool isgen = false;
string n = " n = " + AllScores.Count() + ".xlsx";
string filename = intake.Year.ToString();
filename += " intake cycle Composite ";
// Get the date today
DateTime dt = new DateTime();
dt = DateTime.Now;
string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");
filename = filename + myDay + n;
filename = Path.Combine(folder, filename);
// make a copy of the template
//File.Copy("intake cycle Composite.xlsx", filename, true);
//using (SpreadsheetDocument SpDoc = SpreadsheetDocument.Open(filename, true))
//{
await CompositToFullCompositeAsync();
composite.Clear();
AllScores.Clear();
// column heading for excel
string[] ColumnHeadings = new[] {
"Ref No", "Barcode", "Last Name", "First_Name", "INITIALS", "ID NUMBER",
"ID_Foreign", "Date of Birth", "ID Type", "Citizenship", "Classification",
"Gender 1", "Faculty 1", "DATE", "Test Centre Code", "Venue Name",
"Home Lang", "GR12 Language", "AQL LANG", "AQL CODE", "MAT LANG", "MAT CODE",
"Faculty 2", "Faculty 3", "Test Session ID", "NBT Reference", "Surname", "First Name",
"Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
"Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
"Venue", "Gender", "Street and Number", "Street Name", "Suburb", "City/Town", "Province/Region",
"Postal Code", "e-mail Address", "Landline Number", "Mobile Number", "AL Score",
"AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
"AQL TEST LANGUAGE", "MATHS TEST LANGUAGE",
};
// Size of spreadsheet
var data = new[]
{
new {
SheetName = "Composite",
NumberCols = ColumnHeadings.Count(),
Numberrows = FComposite.Count(),
},
};
// generate the workbook and spreadsheets
WorkbookDfn workbook = new WorkbookDfn
{
Worksheets = data
.Select(d => new WorksheetDfn
{
Name = d.SheetName,
ColumnHeadings = Enumerable.Range(0, d.NumberCols)
.Select(c => new CellDfn
{
Value = ColumnHeadings[c],
Bold = true,
}),
Rows = Enumerable.Range(0, d.Numberrows)
.Select(r => new RowDfn
{
Cells = Enumerable.Range(0, d.NumberCols)
.Select(c =>
new CellDfn
{
Value = c == 0 ? FComposite.ElementAt(r).RefNo :
c == 1 ? FComposite.ElementAt(r).Barcode :
c == 2 ? FComposite.ElementAt(r).LastName :
c == 3 ? FComposite.ElementAt(r).FirstName :
c == 4 ? FComposite.ElementAt(r).Initials :
c == 5 ? FComposite.ElementAt(r).SAID :
c == 6 ? FComposite.ElementAt(r).FID :
c == 7 ? FComposite.ElementAt(r).DOB :
c == 8 ? FComposite.ElementAt(r).IDType :
c == 9 ? FComposite.ElementAt(r).Citizenship :
c == 10 ? FComposite.ElementAt(r).Classification :
c == 11 ? FComposite.ElementAt(r).Gender :
c == 12 ? FComposite.ElementAt(r).Faculty1 :
c == 13 ? FComposite.ElementAt(r).DOT :
c == 14 ? FComposite.ElementAt(r).VenueCode :
c == 15 ? FComposite.ElementAt(r).VenueName :
c == 16 ? FComposite.ElementAt(r).HomeLanguage :
c == 17 ? FComposite.ElementAt(r).SchLanguage :
c == 18 ? FComposite.ElementAt(r).AQLLang :
c == 19 ? FComposite.ElementAt(r).AQLCode :
c == 20 ? FComposite.ElementAt(r).MATLang :
c == 21 ? FComposite.ElementAt(r).MATCode :
c == 22 ? FComposite.ElementAt(r).Faculty2 :
c == 23 ? FComposite.ElementAt(r).Faculty3 :
c == 24 ? FComposite.ElementAt(r).SessionId :
c == 25 ? FComposite.ElementAt(r).NBT :
c == 26 ? FComposite.ElementAt(r).Surname :
c == 27 ? FComposite.ElementAt(r).Name :
c == 28 ? FComposite.ElementAt(r).MiddleInitials :
c == 29 ? FComposite.ElementAt(r).SouthAfricanID :
c == 30 ? FComposite.ElementAt(r).Passport :
c == 31 ? FComposite.ElementAt(r).Birth :
c == 32 ? FComposite.ElementAt(r).WroteAL :
c == 33 ? FComposite.ElementAt(r).WroteQL :
c == 34 ? FComposite.ElementAt(r).WroteMat :
c == 35 ? FComposite.ElementAt(r).StudentNo :
c == 36 ? FComposite.ElementAt(r).Faculty :
c == 37 ? FComposite.ElementAt(r).Programme :
c == 38 ? FComposite.ElementAt(r).TestDate :
c == 39 ? FComposite.ElementAt(r).Venue :
c == 40 ? FComposite.ElementAt(r).Sex :
c == 41 ? FComposite.ElementAt(r).Street1 :
c == 42 ? FComposite.ElementAt(r).Street2 :
c == 43 ? FComposite.ElementAt(r).Suburb :
c == 44 ? FComposite.ElementAt(r).City :
c == 45 ? FComposite.ElementAt(r).Province :
c == 46 ? FComposite.ElementAt(r).Postal :
c == 47 ? FComposite.ElementAt(r).Email :
c == 48 ? FComposite.ElementAt(r).Landline :
c == 49 ? FComposite.ElementAt(r).Mobile :
c == 50 ? FComposite.ElementAt(r).ALScore :
c == 51 ? FComposite.ElementAt(r).ALLevel :
c == 52 ? FComposite.ElementAt(r).QLScore :
c == 53 ? FComposite.ElementAt(r).QLLevel :
c == 54 ? FComposite.ElementAt(r).MatScore :
c == 55 ? FComposite.ElementAt(r).MatLevel :
c == 56 ? FComposite.ElementAt(r).AQLLanguage :
c == 57 ? FComposite.ElementAt(r).MatLanguage : "",
CellDataType =
c == 7 ? CellDataType.String :
c == 2 ? CellDataType.String :
c == 3 ? CellDataType.String : CellDataType.String,
HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
c == 33 ? HorizontalCellAlignment.Center :
c == 34 ? HorizontalCellAlignment.Center :
c == 32 ? HorizontalCellAlignment.Center :
c == 40 ? HorizontalCellAlignment.Center :
c == 18 ? HorizontalCellAlignment.Center :
c == 20 ? HorizontalCellAlignment.Center :
c == 50 ? HorizontalCellAlignment.Center :
c == 52 ? HorizontalCellAlignment.Center :
c == 54 ? HorizontalCellAlignment.Center :
c == 56 ? HorizontalCellAlignment.Center :
c == 57 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
// FormatCode = c == 5 ? "0000000000000" :"",
})
}),
})
};
// stream write to excel file
SpreadsheetWriter.Write(filename, workbook);
// SpreadsheetWriter.Write()
isgen = true;
FComposite.Clear();
FComposite = null;
return(isgen);
}
private static void SaveWorkbookToSpreadSheet(SpreadsheetDocument sDoc, WorkbookDfn workbook)
{
var workbookPart = sDoc.WorkbookPart;
var wXDoc = workbookPart.GetXDocument();
var sheetElement = wXDoc.Root
.Elements(S.sheets)
.Elements(S.sheet)
.FirstOrDefault(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1");
if (sheetElement is null)
{
throw new SpreadsheetWriterInternalException();
}
var id = (string)sheetElement.Attribute(R.id);
sheetElement.Remove();
workbookPart.PutXDocument();
var sPart = (WorksheetPart)workbookPart.GetPartById(id);
workbookPart.DeletePart(sPart);
var appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
var vector = appXDoc.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
vector.SetAttributeValue(SSNoNamespace.size, 0);
var lpstr = vector.Element(VT.lpstr);
lpstr.Remove();
}
var vector2 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.FirstOrDefault();
var variant = vector2
.Descendants(VT.i4)
.FirstOrDefault();
if (variant != null)
{
variant.Value = "1";
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
if (workbook.Worksheets is not null)
{
foreach (var worksheet in workbook.Worksheets)
{
AddWorksheet(sDoc, worksheet);
}
}
workbookPart.WorkbookStylesPart.PutXDocument();
}
