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