public async Task <IActionResult> DownloadProductionDataAsync(ProductionSheetData productiondata)
{
if (productiondata == null)
{
throw new ArgumentNullException(nameof(productiondata));
}
else
{
string fileName = Path.GetTempFileName();
var data = productionDataSheetWriter.GenerateExcelSheet(productiondata);
SpreadsheetWriter.Write(fileName, data);
var memory = new MemoryStream();
using (var fileStream = new FileStream(fileName, FileMode.Open))
{
await fileStream.CopyToAsync(memory);
}
memory.Position = 0;
Response.Headers.Add("Content-Disposition",
"attachment; filename=ExcelFile.xlsx");
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
}
protected async override void OnExecute(object parameter)
{
try
{
if (parameter is ISelector selector && selector.SelectedItem is ListBoxItemViewModel viewModel && viewModel.Target is IDataBase dataBase)
{
if (viewModel is IInfoProvider provider)
{
var props = provider.Info;
var revision = (string)props["Revision"];
var dataBaseName = await dataBase.Dispatcher.InvokeAsync(() => dataBase.Name);
var dialog = new CommonSaveFileDialog();
dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
dialog.DefaultFileName = $"{dataBaseName}_{revision}";
dialog.DefaultExtension = "xlsx";
if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
{
var dataSet = await dataBase.GetDataSetAsync(this.authenticator, CremaDataSetFilter.Default, revision);
var writer = new SpreadsheetWriter(dataSet);
writer.Write(dialog.FileName);
await AppMessageBox.ShowErrorAsync(Resources.Message_Exported);
}
}
}
}
catch (Exception e)
{
await AppMessageBox.ShowErrorAsync(e);
}
}
protected async override void OnExecute(object parameter)
{
try
{
if (parameter is ListBoxItemViewModel viewModel && viewModel.Target is ITableItem tableItem)
{
if (viewModel is IInfoProvider provider)
{
var props = provider.Info;
var revision = (long)props["Revision"];
var tableName = await tableItem.Dispatcher.InvokeAsync(() => tableItem.Name);
var dialog = new CommonSaveFileDialog();
dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
dialog.DefaultFileName = $"{tableName}_{revision}";
dialog.DefaultExtension = "xlsx";
if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
{
var dataSet = await tableItem.Dispatcher.InvokeAsync(() => tableItem.GetDataSet(this.authenticator, revision));
var writer = new SpreadsheetWriter(dataSet);
writer.Write(dialog.FileName);
AppMessageBox.Show(Resources.Message_Exported);
}
}
}
}
catch (Exception e)
{
AppMessageBox.ShowError(e);
}
}
public void Write(IEnumerable <IRow> rows)
{
foreach (var row in rows)
{
var cellDfns = new List <CellDfn>();
var rowDef = new RowDfn {
Cells = cellDfns
};
cellDfns.AddRange(_fields.Select(field => new CellDfn {
CellDataType = field.ToCellDataType(),
Value = field.Type == "guid" ? row[field].ToString() : row[field],
FormatCode = field.Format.Replace("tt", "AM/PM")
}));
_rowDfns.Add(rowDef);
}
SpreadsheetWriter.Write(_fileInfo.FullName, _workbook);
}
public void Export(string itemPath, CremaDataSet dataSet)
{
if (dataSet.Tables.Any() == false)
{
return;
}
var spreadsheetWriterSettings = new SpreadsheetWriterSettings()
{
OmitAttribute = this.settings.OmitAttribute,
OmitSignatureDate = this.settings.OmitSignatureDate,
};
var writer = new SpreadsheetWriter(dataSet, spreadsheetWriterSettings);
var filename = this.OutputPath;
if (this.settings.IsSeparable == true)
{
var outputPath = itemPath.Trim(PathUtility.SeparatorChar).Replace(PathUtility.SeparatorChar, '.');
if (outputPath == string.Empty)
{
outputPath = this.cremaAppHost.DataBaseName;
}
if (this.settings.IsIncludeDate)
{
//------------------------------------------------------
// TableName_2017-02-17_05_11.xlsx
string szDate = DateTime.Now.ToString($"{this.settings.OutputDateFormat}");
filename = FileUtility.Prepare(this.outputPath, $"{outputPath}_{szDate}.xlsx");
}
else
{
filename = FileUtility.Prepare(this.outputPath, outputPath + ".xlsx");
}
}
using (var stream = new FileStream(filename, FileMode.Create))
{
writer.Write(stream);
}
this.configs.Commit(this.settings);
this.configs.Commit(this);
}
private void WriteTables(CremaDataSet dataSet, string filename)
{
var path = Path.Combine(this.CommandContext.BaseDirectory, filename);
var settings = new SpreadsheetWriterSettings()
{
OmitAttribute = this.OmitAttribute,
OmitSignatureDate = this.OmitSignatureDate,
Tags = (TagInfo)TagsProperties.Tags,
};
var progress = new ConsoleProgress(this.Out)
{
Style = ConsoleProgressStyle.None
};
using (var writer = new SpreadsheetWriter(dataSet, settings))
{
writer.Write(path, progress);
}
this.Out.WriteLine($"export: \"{path}\"");
}
public void ExportToExcel()
{
var dialog = new CommonSaveFileDialog();
dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
dialog.DefaultExtension = "xlsx";
if (dialog.ShowDialog() != CommonFileDialogResult.Ok)
{
return;
}
try
{
var dataTable = this.tableViewModel.Source.ExportTable2();
var writer = new SpreadsheetWriter(dataTable.DataSet);
writer.Write(dialog.FileName);
}
catch (Exception e)
{
AppMessageBox.ShowError(e);
}
}
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);
}
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);
}