static void CreateSchedule2(XLWorkbook workbook, AssesedClient client) { IXLWorksheet schedule2Sheet; if (workbook.TryGetWorksheet("Schedule 2", out schedule2Sheet)) { Console.WriteLine("Filling out schedule 2"); AmountTotal clientTotal = new AmountTotal(); var currentRow = 8; foreach (var invoice in client.Invoices.Values) { var dateCell = SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "B", invoice.Date); dateCell.SetDataType(XLCellValues.DateTime); var invoiceTotal = invoice.GetTotal(); SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "C", invoice.InvoiceId); SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "E", invoiceTotal.Amount); SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "H", invoiceTotal.Assesed); clientTotal.Amount += invoiceTotal.Amount; clientTotal.Assesed += invoiceTotal.Assesed; currentRow++; } client.LastCalculatedTotal = clientTotal; currentRow++; SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "C", "Grand Total"); var totalAmountCell = SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "E", clientTotal.Amount); totalAmountCell.Style.Font.Bold = true; var totalAssessedCell = SetCellValue(schedule2Sheet, _schedule2Styles, currentRow, "H", clientTotal.Assesed); totalAssessedCell.Style.Font.Bold = true; } }
static void CreateRyanInvoice(string outputFolder, AssesedClient client) { Console.WriteLine($"Creating invoice for {client.Id}"); var filePath = Path.Combine(outputFolder, $"Invoice {client.Id}.xlsx"); #if !DEBUG if (File.Exists(filePath)) { throw new FileLoadException($"{filePath} already exists"); } #endif File.Copy(_settings.InvoiceTemplateDocumentPath, filePath, true); using (var workbook = new XLWorkbook(filePath)) { IXLWorksheet invoiceSheet; if (workbook.TryGetWorksheet("Sheet1", out invoiceSheet)) { invoiceSheet.Cell(5, "F").SetValue(client.Id); invoiceSheet.Cell(10, "A").SetValue(client.Name); invoiceSheet.Cell(11, "A").SetValue(client.Address); invoiceSheet.Cell(12, "A").SetValue(client.City); invoiceSheet.Cell(13, "A").SetValue(client.PostalCode); invoiceSheet.Cell(4, "F").SetValue(client.RyanInvoiceId); var dateCell = invoiceSheet.Cell(3, "F"); dateCell.SetValue(DateTime.Today.ToString("M/d/yyyy")); dateCell.SetDataType(XLCellValues.DateTime); var dueDateCell = invoiceSheet.Cell(6, "F"); dueDateCell.SetValue(DateTime.Today.AddDays(30).ToString("M/d/yyyy")); dueDateCell.SetDataType(XLCellValues.DateTime); invoiceSheet.Cell(16, "F").SetValue(client.LastCalculatedTotal.Assesed); invoiceSheet.Cell(31, "F").SetValue(client.LastCalculatedTotal.Assesed); } workbook.Save(true); } }
static void CreateSchedule3(XLWorkbook workbook, AssesedClient client) { IXLWorksheet schedule3Sheet; if (workbook.TryGetWorksheet("Schedule 3", out schedule3Sheet)) { Console.WriteLine("Filling out schedule 3"); var currentRow = 9; var uniqueItems = new HashSet <string>(); List <InvoiceEntry> allEntries = new List <InvoiceEntry>(); foreach (var invoice in client.Invoices.Values) { foreach (var currentEntry in invoice.Entries) { if (string.IsNullOrEmpty(currentEntry.ItemId) || uniqueItems.Contains(currentEntry.ItemId)) { continue; } uniqueItems.Add(currentEntry.ItemId); allEntries.Add(currentEntry); } } allEntries = allEntries.OrderBy(x => x.ItemDescription).ToList(); foreach (var currentEntry in allEntries) { SetCellValue(schedule3Sheet, _schedule3Styles, currentRow, "B", currentEntry.ClientId); SetCellValue(schedule3Sheet, _schedule3Styles, currentRow, "C", currentEntry.ItemId); SetCellValue(schedule3Sheet, _schedule3Styles, currentRow, "D", currentEntry.ItemDescription); currentRow++; } } }
static void CreateSchedules(string outputFolder, AssesedClient client) { Console.WriteLine($"Creating package for {client.Id}"); var filePath = String.Format(Path.Combine(outputFolder, $"Schedule {client.Id}.xlsx")); #if !DEBUG if (File.Exists(filePath)) { throw new FileLoadException($"{filePath} already exists"); } #endif File.Copy(_settings.SchedulesTemplateDocumentPath, filePath, true); using (var workbook = new XLWorkbook(filePath)) { CreateSchedule2(workbook, client); CreateSchedule3(workbook, client); workbook.Save(true); } }