private void SetHeaderValues(ReportDTO <AllOpenJobSummaryReportDTO> rpt, IXLWorksheet excelSheet)
{
//Employee total Hours Header
//Template contains one cell so we subtract one here to use it
excelSheet.Cell(1, 2).InsertCellsAfter(rpt.Data.EmployeeIdToNameMap.Count - 1);
excelSheet.Range(1, 3, 1, 3 + rpt.Data.EmployeeIdToNameMap.Count - 1).Merge()
.AssignValue("Employee Total Hours")
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetFontStyle(x => x.Bold = true);
//Employee Name column header
//Template contains one cell so we subtract one here to use it
excelSheet.Cell(2, 2).InsertCellsAfter(rpt.Data.EmployeeIdToNameMap.Count - 1);
var emps = rpt.Data.EmployeeIdToNameMap;
foreach (var(empInfo, index) in rpt.Data.EmployeeIdToNameMap.OrderBy(x => x.Key)
.Select((eInfo, index) => (eInfo, index)))
{
excelSheet.Cell(2, index + 3)
.AssignValue(empInfo.Value)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Medium)
.SetFontStyle(x => x.Bold = true);
excelSheet.Column(index + 3).Width = 16.25;
}
}
private static void WriteNonExemptEmployees(ReportDTO <PayPeriodReportDTO> rpt, IXLWorksheet excelSheet)
{
var exemptEmployeeRows = rpt.Data.Employees.Where(x => x.IsExempt).Count();
var nonExemptEmployees = rpt.Data.Employees.Where(x => !x.IsExempt).ToList();
var activeRow = INITIAL_NON_EXEMPT_TOTAL_ROW + exemptEmployeeRows;
if (nonExemptEmployees.Any())
{
var row = excelSheet.Row(activeRow).InsertRowsAbove(nonExemptEmployees.Count);
row.Style.Fill.BackgroundColor = XLColor.NoColor;
row.Style.Font.Bold = false;
row.Style.Font.FontColor = XLColor.Black;
foreach (var employeeRow in nonExemptEmployees.Select((e, index) => (index, e)))
{
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 1), employeeRow.e.EmployeeName);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 2), (double)employeeRow.e.Regular);
excelSheet.Cell(activeRow + employeeRow.index, 3).Style.Fill.BackgroundColor = XLColor.FromArgb(208, 206, 206);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 4), (double)employeeRow.e.Overtime);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 5), (double)employeeRow.e.PTO);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 6), (double)employeeRow.e.Holiday);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 7), (double)employeeRow.e.ExcusedWithPay);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 8), (double)employeeRow.e.ExcusedNoPay);
SetReportCell(excelSheet.Cell(activeRow + employeeRow.index, 9), (double)employeeRow.e.Combined);
}
PopulateSummaryRow(excelSheet, nonExemptEmployees.Count, activeRow, "C");
}
}
private void WriteExpenseRows(ReportDTO <QuickJobTimeReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 8 + report.Data.Employees.Count();
var originalRowStart = rowStart;
if (report.Data.Expenses.Any())
{
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.Expenses.Count);
}
foreach (var exp in report.Data.Expenses)
{
excelSheet.Range(rowStart, 1, rowStart, 3).Merge()
.AssignValue(exp.Key)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Range(rowStart, 4, rowStart, 5).Merge()
.AssignValue(exp.Value, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
rowStart++;
}
if (report.Data.Expenses.Any())
{
string[] columns = new string[] { "turningArrayInto1Based", "A", "B", "C", "D", "E", "F", "G", "H", "I" };
excelSheet.Range(rowStart, 1, rowStart, 3).Merge()
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.SetFontStyle(x => x.Bold = true);
var labelTotal = excelSheet.Range(rowStart, 4, rowStart, 5).Merge()
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
labelTotal.SetFontStyle(x => x.Bold = true);
labelTotal.FormulaA1 = $"SUM({columns[4]}{originalRowStart}:{columns[4]}{rowStart - 1})";
labelTotal.LastCell().CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
}
private static void WriteReportMetadata(ReportDTO <PayPeriodReportDTO> report, IXLWorksheet excelSheet, int exemptRows, int nonExemptRows)
{
var target = GRAND_TOTAL_ROW + exemptRows + nonExemptRows + 2;
excelSheet.Row(target).InsertRowsBelow(report.RunSettings.Count);
foreach (var item in report.RunSettings.Select((e, index) => (index, e)))
{
excelSheet.Cell(target + item.index, 1).Value = $"{item.e.Key}: {item.e.Value}";
}
}
private static void SetHeaderValues(ReportDTO <QuickJobTimeReportDTO> rpt, IXLWorksheet excelSheet)
{
excelSheet.Cell(1, 2).Value = (rpt.Data.JobCode);
excelSheet.Cell(1, 4).Value = (rpt.Data.JobName);
excelSheet.Cell(2, 2).Value = (rpt.Data.SiteName);
excelSheet.Cell(2, 5).Value = (rpt.Data.ClientName);
excelSheet.Cell(3, 5).Value = $"{rpt.Data.PeriodStart.ToShortDateString()} thru {rpt.Data.PeriodEnd.ToShortDateString()}";
}
private void WriteMainJobListSection(ReportDTO <AllOpenJobSummaryReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 3;
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.Rows.Count);
foreach (var row in report.Data.Rows.OrderBy(x => x.Key))
{
excelSheet.Cell(rowStart, 1)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AssignValue(row.Key)
.AddLeftBorder(XLBorderStyleValues.Medium)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
var expAmount = report.Data.ExpenseAmountForJob.TryGetValue(row.Key, out var temp) ? temp : 0.0m;
excelSheet.Cell(rowStart, 2)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AssignValue(expAmount, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
foreach (var(empInfo, index) in report.Data.EmployeeIdToNameMap.OrderBy(x => x.Key)
.Select((eInfo, index) => (eInfo, index)))
{
excelSheet.Cell(rowStart, 3 + index)
.AssignValue(row.Value.GetTotalHours(empInfo.Key))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
}
var labelTotal = excelSheet.Cell(rowStart, 3 + report.Data.EmployeeIdToNameMap.Count)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetFontStyle(x => x.Bold = true);
labelTotal.FormulaA1 = $"SUM({columns[3]}{rowStart}:{columns[3 + report.Data.EmployeeIdToNameMap.Count - 1]}{rowStart})";
rowStart++;
}
}
private void WriteMiscSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 23 + report.Data.TimeAndExpense.Count() + report.Data.CompanyVehicle.Count() + report.Data.SubContractor.Count() + report.Data.ArcFlashLabel.Count();
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.Misc.Count());
foreach (var sectionRow in report.Data.Misc)
{
excelSheet.Range(rowStart, 1, rowStart, 4).Merge()
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AssignValue(sectionRow.Description)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AssignValue(sectionRow.Cost)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.SetFontStyle(f => f.Bold = false)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
rowStart++;
}
excelSheet.Range(rowStart, 1, rowStart, 4).Merge()
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.SetFontStyle(x => x.Bold = true);
var labelTotal = excelSheet.Cell(rowStart, 5)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
labelTotal.SetFontStyle(x => x.Bold = true);
labelTotal.FormulaA1 = $"SUM({columns[5]}{originalRowStart}:{columns[5]}{rowStart - 1})";
labelTotal.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
private static void WriteReportMetadata(ReportDTO <QuickJobTimeReportDTO> report, IXLWorksheet excelSheet)
{
var row = 10 + report.Data.Employees.Count() + report.Data.Expenses.Count();
var settingIndex = 0;
foreach (var item in report.RunSettings)
{
excelSheet.Range(row, 1, row, 3).Merge()
.AssignValue($"{item.Key}: {item.Value}")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left);
row++;
}
}
private void RemoveEmployeeEntriesWithNoTimePresent(ReportDTO <AllOpenJobSummaryReportDTO> rpt)
{
var toRemove = new List <int>();
foreach (var emp in rpt.Data.EmployeeIdToNameMap)
{
if (!rpt.Data.Rows.Any(x => x.Value.GetTotalHours(emp.Key) > 0))
{
toRemove.Add(emp.Key);
}
}
foreach (var emp in toRemove)
{
rpt.Data.EmployeeIdToNameMap.Remove(emp);
}
}
public MemoryStream AsXls(ReportDTO <QuickJobTimeReportDTO> rpt)
{
var workbook = new XLWorkbook("docs/QuickJobTimeReportNew.xlsx", new LoadOptions()
{
});
var excelSheet = workbook.Worksheet(1);
SetHeaderValues(rpt, excelSheet);
WriteEmployeeRows(rpt, excelSheet);
WriteExpenseRows(rpt, excelSheet);
WriteReportMetadata(rpt, excelSheet);
var ms2 = new MemoryStream();
workbook.SaveAs(ms2);
ms2.Position = 0;
return(ms2);
}
private static void WriteReportMetadata(ReportDTO <AllOpenJobSummaryReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 6 + report.Data.Rows.Count;
foreach (var item in report.RunSettings)
{
excelSheet.Range(rowStart, 1, rowStart, 4).Merge()
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AssignValue($"{item.Key}: {item.Value}")
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddTopBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
rowStart++;
}
}
private static void WriteEmployeeRows(ReportDTO <QuickJobTimeReportDTO> report, IXLWorksheet excelSheet)
{
var columns = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I" };
var row = EMPLOYEE_ROW_START;
if (report.Data.Employees.Any())
{
excelSheet.Row(7).InsertRowsAbove(report.Data.Employees.Count());
}
foreach (var employeeRow in report.Data.Employees.OrderBy(x => x.TaskCategory).ThenBy(x => x.TaskName).ThenBy(x => x.EmployeeName))
{
SetCellContentWithBorder(excelSheet, row, 1, employeeRow.EmployeeName, alignLeft: true, addLeftBorder: true);
excelSheet.Cell(row, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
SetCellContentWithBorder(excelSheet, row, 3, employeeRow.TaskCategory, alignLeft: true);
SetCellContentWithBorder(excelSheet, row, 4, employeeRow.TaskName, alignLeft: true);
excelSheet.Cell(row, 5).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
SetCellContentWithBorder(excelSheet, row, 6, (double)employeeRow.Regular);
SetCellContentWithBorder(excelSheet, row, 7, (double)employeeRow.Overtime);
excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row },{columns[6]}{row})");
excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
row++;
}
if (report.Data.Employees.Any())
{
excelSheet.Cell(row, 6).SetFormulaA1($"SUM({columns[5]}{EMPLOYEE_ROW_START }:{columns[5]}{row - 1})");
excelSheet.Cell(row, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 6).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 6).Style.Font.Bold = true;
excelSheet.Cell(row, 7).SetFormulaA1($"SUM({columns[6]}{EMPLOYEE_ROW_START }:{columns[6]}{row - 1})");
excelSheet.Cell(row, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 7).Style.Font.Bold = true;
excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row }:{columns[6]}{row})");
excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Font.Bold = true;
}
}
public MemoryStream AsXls(ReportDTO <DetailedExpenseForJobReportDTO> rpt)
{
var workbook = new XLWorkbook("docs/DetailedExpenseReportTemplate.xlsx", new LoadOptions()
{
});
var excelSheet = workbook.Worksheet(1);
SetHeaderValues(rpt, excelSheet);
if (rpt.Data.TimeAndExpense.Any())
{
WriteTimeAndExpensesSection(rpt, excelSheet);
}
if (rpt.Data.CompanyVehicle.Any())
{
WriteCompanyVehicleSection(rpt, excelSheet);
}
if (rpt.Data.SubContractor.Any())
{
WriteSubContractorSection(rpt, excelSheet);
}
if (rpt.Data.ArcFlashLabel.Any())
{
WriteArcFlashLabelSection(rpt, excelSheet);
}
if (rpt.Data.Misc.Any())
{
WriteMiscSection(rpt, excelSheet);
}
WriteReportMetadata(rpt, excelSheet);
var ms2 = new MemoryStream();
workbook.SaveAs(ms2);
ms2.Position = 0;
return(ms2);
}
private static void WriteReportMetadata(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 25 + report.Data.TimeAndExpense.Count() + report.Data.CompanyVehicle.Count() + report.Data.SubContractor.Count() + report.Data.ArcFlashLabel.Count() + report.Data.Misc.Count();
var row = rowStart;
foreach (var item in report.RunSettings)
{
excelSheet.Range(rowStart, 1, rowStart, 4).Merge()
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AssignValue($"{item.Key}: {item.Value}")
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddTopBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
rowStart++;
}
}
public MemoryStream AsXls(ReportDTO <AllOpenJobSummaryReportDTO> rpt)
{
var workbook = new XLWorkbook("docs/AllOpenJobsSummaryReportTemplate.xlsx", new LoadOptions()
{
});
var excelSheet = workbook.Worksheet(1);
RemoveEmployeeEntriesWithNoTimePresent(rpt);
SetHeaderValues(rpt, excelSheet);
WriteMainJobListSection(rpt, excelSheet);
WriteReportMetadata(rpt, excelSheet);
var ms2 = new MemoryStream();
workbook.SaveAs(ms2);
ms2.Position = 0;
return(ms2);
}
public MemoryStream AsXls(ReportDTO <PayPeriodReportDTO> rpt)
{
var workbook = new XLWorkbook("docs/PayPeriodReport.xlsx", new LoadOptions()
{
});
var excelSheet = workbook.Worksheet(1);
WriteExemptEmployees(rpt, excelSheet);
WriteNonExemptEmployees(rpt, excelSheet);
var nonExemptRows = rpt.Data.Employees.Where(x => !x.IsExempt).Count();
var exemptRows = rpt.Data.Employees.Where(x => x.IsExempt).Count();
WriteGrandTotalRow(excelSheet, exemptRows, nonExemptRows);
WriteReportMetadata(rpt, excelSheet, exemptRows, nonExemptRows);
var ms2 = new MemoryStream();
workbook.SaveAs(ms2);
ms2.Position = 0;
return(ms2);
}
private static void WriteCompanyVehicleSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 11 + report.Data.TimeAndExpense.Count();
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.CompanyVehicle.Count());
excelSheet.Range($"{columns[1]}{rowStart - 1}:{columns[2]}{rowStart - 1}")
.AddBottomBorder(XLBorderStyleValues.Medium);
foreach (var sectionRow in report.Data.CompanyVehicle)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetDataType(XLDataType.DateTime)
.AssignValue(DateTimeWithZone.ConvertToEST(sectionRow.Date.UtcDateTime))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.Vehicle)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.EmployeeFirstLast)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AssignValue(sectionRow.NumberOfDays)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 6)
.AssignValue(sectionRow.TotalMiles)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false);
excelSheet.Cell(rowStart, 7)
.AssignValue(sectionRow.Cost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddBottomBorder(XLBorderStyleValues.Thin);
//HACK: set cell in column 7's right border to be thin, not sure why to do so we must touch cell 7 + 8, but meh
excelSheet.Cell(rowStart, 8).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 4)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 5)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 6).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var totalCell = excelSheet.Cell(rowStart, 7)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[7]}{originalRowStart}:{columns[7]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
private static void WriteTimeAndExpensesSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 7;
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.TimeAndExpense.Count());
excelSheet.Range($"{columns[1]}{rowStart - 1}:{columns[2]}{rowStart - 1}")
.AddBottomBorder(XLBorderStyleValues.Medium);
foreach (var sectionRow in report.Data.TimeAndExpense)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetDataType(XLDataType.DateTime)
.AssignValue(DateTimeWithZone.ConvertToEST(sectionRow.Date.UtcDateTime))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.EmployeeFirstLast)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.Cost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AddLeftBorder(XLBorderStyleValues.Thin);
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var totalCell = excelSheet.Cell(rowStart, 4)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[4]}{originalRowStart}:{columns[4]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
private void WriteSubContractorSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 15 + report.Data.TimeAndExpense.Count() + report.Data.CompanyVehicle.Count();
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.SubContractor.Count());
foreach (var sectionRow in report.Data.SubContractor)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AssignValue(sectionRow.Company)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.PONumber)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.SetFontStyle(f => f.Bold = false)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.ContractAmount, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var totalCell = excelSheet.Cell(rowStart, 4)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[4]}{originalRowStart}:{columns[4]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
