/// <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(); } }