/// <summary>
/// Writes a log file.
/// </summary>
/// <param name="reportData">The report data.</param>
/// <param name="filePath">The file path.</param>
/// <param name="progress">The progress reporter.</param>
public static void WriteLogFile(LogTable reportData, string filePath, IProgress <int> progress = null)
{
var excelApp = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false
};
var workbooks = excelApp.Workbooks;
var thisWorkBook = workbooks.Add();
// Initialize progress reporting
var rowsWritten = 0;
var totalRows = reportData.Rows.Count;
progress?.Report(0);
try
{
// Create collection of tables for worksheets
var dateRange = reportData.Rows.OfType <LogRow>().OrderByDescending(row => row.Date)
.Select(row => row.Date).Distinct();
var tables = new List <LogTable>();
foreach (var date in dateRange)
{
var newTable = new LogTable();
foreach (var dateRow in reportData.Rows.OfType <LogRow>().Where(row => row.Date.Date == date.Date))
{
newTable.ImportRow(dateRow);
}
tables.Add(newTable);
}
foreach (var thisDateReport in tables)
{
// Create new worksheet
var thisDate = thisDateReport.FirstDate;
Excel.Worksheet thisSheet = thisWorkBook.Sheets.Add();
thisSheet.Name = $"{Tools.GetWeekDayName(thisDate)} {thisDate?.ToString("d", Values.AppCulture).Replace("/", "-")}";
// Format column headers
var headerRange = thisSheet.Range["a1"];
headerRange.EntireRow.Font.Bold = true;
headerRange.Application.ActiveWindow.SplitRow = 1;
headerRange.Application.ActiveWindow.FreezePanes = true;
// Remember datatable rows/cols are 0-indexed, xl is 1-indexed
for (var col = 0; col < ExportLogByDateColumns.Count; col++)
{
switch (ExportLogByDateColumns[col])
{
case LogTable.AmountColumnName:
thisSheet.Range[$"{GetExcelColumnName(col + 1)}1"].EntireColumn.NumberFormat =
"#0.00;[Red]-#0.00";
break;
case LogTable.DoneColumnName:
thisSheet.Range[$"{GetExcelColumnName(col + 1)}1"]
.EntireColumn.HorizontalAlignment = Excel.Constants.xlCenter;
break;
case LogTable.TechColumnName:
thisSheet.Range[$"{GetExcelColumnName(col + 1)}1"]
.EntireColumn.HorizontalAlignment = Excel.Constants.xlCenter;
break;
default:
thisSheet.Range[$"{GetExcelColumnName(col + 1)}1"].EntireColumn.NumberFormat = "@";
break;
}
// Header contents
thisSheet.Cells[1, col + 1] = reportData.Columns[ExportLogByDateColumns[col]].Caption;
}
// Write rows
// Additional offset by 1 for header row
for (var row = 0; row < thisDateReport.Rows.Count; row++)
{
for (var col = 0; col < ExportLogByDateColumns.Count; col++)
{
var reportRow = thisDateReport.Rows[row] as LogRow;
// If the column has a bool value, use "X" and " " for true and false
var value = reportRow?[reportData.Columns.IndexOf(ExportLogByDateColumns[col])];
thisSheet.Cells[row + 2, col + 1] = thisDateReport.Columns[ExportLogByDateColumns[col]].DataType == typeof(bool)
? value as bool? ?? false ? Values.BoolExportString : string.Empty
: value;
}
// Report progress
rowsWritten++;
progress?.Report(100 * rowsWritten / totalRows);
}
thisSheet.UsedRange.EntireColumn.AutoFit();
}
// Remove initial sheet(s)
for (var s = 1; s <= excelApp.SheetsInNewWorkbook; s++)
{
thisWorkBook.Sheets.OfType <Excel.Worksheet>().ToList().LastOrDefault()?.Delete();
}
// Save workbook
thisWorkBook.SaveAs(filePath);
}
catch (Exception error)
{
Tools.DebugLog($"Error exporting file: {error.Message}");
}
finally
{
thisWorkBook.Close();
workbooks.Close();
excelApp.Quit();
}
}
