private void createDetailTotals(InvoiceDS.ClientInvoiceTableDataTable invoiceTable) { //Get worksheet Detail ws = global::Argix.Finance.Globals.Detail; //Totals int rowCount = invoiceTable.Rows.Count; object[,] totals = new object[1, 16]; int qty = 0, weight = 0; decimal fs = 0.0M, dt = 0.0M; for (int i = 0; i < rowCount; i++) { qty += invoiceTable[i].CtnQty; weight += invoiceTable[i].Weight; fs += invoiceTable[i].FuelSurcharge; dt += invoiceTable[i].DeliveryTotal; } totals[0, 0] = "TOTAL " + rowCount.ToString() + " DELIVERIES"; totals[0, 1] = totals[0, 2] = totals[0, 3] = ""; totals[0, 4] = qty; totals[0, 5] = totals[0, 6] = totals[0, 7] = ""; totals[0, 8] = weight; totals[0, 9] = totals[0, 10] = totals[0, 11] = totals[0, 12] = totals[0, 13] = ""; totals[0, 14] = fs; totals[0, 15] = dt; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 1], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]).Value2 = totals; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 5], ws.Cells[ROW0_DETAIL + rowCount + 1, 5]).NumberFormat = "#,###_);(#,###)"; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 5], ws.Cells[ROW0_DETAIL + rowCount + 1, 5]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 9], ws.Cells[ROW0_DETAIL + rowCount + 1, 9]).NumberFormat = "#,##0_);(#,##0)"; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 9], ws.Cells[ROW0_DETAIL + rowCount + 1, 9]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 15], ws.Cells[ROW0_DETAIL + rowCount + 1, 15]).NumberFormat = "$#,##0.00_);($#,##0.00)"; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 15], ws.Cells[ROW0_DETAIL + rowCount + 1, 15]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 16], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]).NumberFormat = "$#,##0.00_);($#,##0.00)"; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 1, 16], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //Footer ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 3, 1], ws.Cells[ROW0_DETAIL + rowCount + 3, 16]).Value2 = new object[1, 16] { { "PLEASE REFERENCE INVOICE# " + invoiceTable[0].InvoiceNumber + " WHEN REMITTING PAYMENT I.C.C. REGULATIONS REQUIRE THAT THIS BILL BE PAID WITHIN 7 DAYS", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" } }; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 4, 1], ws.Cells[ROW0_DETAIL + rowCount + 4, 16]).Value2 = new object[1, 16] { { "", "", "A SERVICE CHARGE OF 1.5% PER MONTH IS ADDED TO ALL PAST DUE INVOICES", "", "", "", "", "", "", "", "", "", "", "", "", "" } }; ws.get_Range(ws.Cells[ROW0_DETAIL + rowCount + 5, 1], ws.Cells[ROW0_DETAIL + rowCount + 5, 16]).Value2 = new object[1, 16] { { "", "", "REMIT TO: " + invoiceTable[0].RemitToName.Trim() + " " + invoiceTable[0].RemitToAddressLine1.Trim() + " " + invoiceTable[0].RemitToAddressLine2.Trim() + " " + invoiceTable[0].RemitToCity.Trim() + ", " + invoiceTable[0].RemitToState + " " + invoiceTable[0].RemitToZip + "-" + invoiceTable[0].RemitToZip4, "", "", "", "", "", "", "", "", "", "", "", "", "" } }; }
private void createDetailBody(InvoiceDS.ClientInvoiceTableDataTable invoiceTable) { //Create body of detail worksheet Excel.Worksheet ws = (Excel.Worksheet) this.Worksheets["Detail"]; Application.ScreenUpdating = false; //Insert a row at row0 + 1 (pushes down) for every row of data int rowCount = invoiceTable.Rows.Count; Excel.Range row0 = ws.get_Range(ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 21]).EntireRow; for (int i = 0; i < rowCount - 1; i++) { row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false); } //Populate entire data table into a range of worksheet cells object[,] values = new object[rowCount, 21]; for (int i = 0; i < rowCount; i++) { values[i, 0] = "'" + invoiceTable[i].PRONumber.Trim(); values[i, 1] = "'" + invoiceTable[i].StoreNumber.ToString(); values[i, 2] = "'" + invoiceTable[i].StoreName.Trim(); values[i, 3] = "'" + invoiceTable[i].StoreAddressLine1.Trim(); values[i, 4] = "'" + invoiceTable[i].StoreCity.Trim(); values[i, 5] = "'" + invoiceTable[i].StoreState.Trim(); values[i, 6] = "'" + invoiceTable[i].StoreZip; values[i, 7] = invoiceTable[i].CtnQty; values[i, 8] = invoiceTable[i].CartonRate; values[i, 9] = invoiceTable[i].PltQty; values[i, 10] = invoiceTable[i].PalletRate; values[i, 11] = invoiceTable[i].Weight; values[i, 12] = invoiceTable[i].RatedWeight; values[i, 13] = invoiceTable[i].WeightRate; values[i, 14] = invoiceTable[i].Surcharge; values[i, 15] = invoiceTable[i].IsConsolidationChargeNull() ? 0.0m : invoiceTable[i].ConsolidationCharge; values[i, 16] = invoiceTable[i].FuelRate; values[i, 17] = invoiceTable[i].FuelSurcharge; values[i, 18] = invoiceTable[i].DeliveryTotal; values[i, 19] = invoiceTable[i].StoreBLNumber; values[i, 20] = invoiceTable[i].MasterBLNumber; } ws.get_Range(ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 21]).Value2 = values; Application.ScreenUpdating = true; }
private void createDetailBody(InvoiceDS.ClientInvoiceTableDataTable invoiceTable) { //Get worksheet Detail ws = global::Argix.Finance.Globals.Detail; Application.ScreenUpdating = false; //Insert a row at row0 + 1 (pushes down) for every row of data int rowCount = invoiceTable.Rows.Count; Excel.Range row0 = ws.get_Range(ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 16]).EntireRow; for (int i = 0; i < rowCount - 1; i++) { row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false); } //Populate entire data table into a range of worksheet cells object[,] values = new object[rowCount, 16]; for (int i = 0; i < rowCount; i++) { values[i, 0] = "'" + invoiceTable[i].StoreName.Trim(); values[i, 1] = "'" + invoiceTable[i].StoreState; values[i, 2] = "'" + invoiceTable[i].StoreZip; values[i, 3] = "'" + invoiceTable[i].LocationCode; values[i, 4] = invoiceTable[i].CtnQty; values[i, 5] = invoiceTable[i].CartonRate; values[i, 6] = invoiceTable[i].PltQty; values[i, 7] = invoiceTable[i].PalletRate; values[i, 8] = invoiceTable[i].Weight; values[i, 9] = invoiceTable[i].RatedWeight; values[i, 10] = invoiceTable[i].WeightRate; values[i, 11] = invoiceTable[i].Surcharge; values[i, 12] = invoiceTable[i].ConsolidationCharge; values[i, 13] = invoiceTable[i].FuelRate; values[i, 14] = invoiceTable[i].FuelSurcharge; values[i, 15] = invoiceTable[i].DeliveryTotal; } ws.get_Range(ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]).Value2 = values; #region Column Formats ws.get_Range(ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ws.get_Range(ws.Cells[ROW0_DETAIL, 2], ws.Cells[ROW0_DETAIL + rowCount - 1, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ws.get_Range(ws.Cells[ROW0_DETAIL, 3], ws.Cells[ROW0_DETAIL + rowCount - 1, 3]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ws.get_Range(ws.Cells[ROW0_DETAIL, 4], ws.Cells[ROW0_DETAIL + rowCount - 1, 4]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ws.get_Range(ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]).NumberFormat = "#,###_);(#,###)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]).NumberFormat = "#,###.##_);(#,###.##);_(* _)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]).NumberFormat = "#,###_);(#,###)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]).NumberFormat = "#,###.##_);(#,###.##);_(* _)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]).NumberFormat = "#,##0_);(#,##0)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]).NumberFormat = "#,##0_);(#,##0)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]).NumberFormat = "#,##0.00_);(#,##0.00)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]).NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]).NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]).NumberFormat = "#,##0.0000_);(#,##0.0000)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]).NumberFormat = "$#,##0.00_);($#,##0.00)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; ws.get_Range(ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]).NumberFormat = "$#,##0.00_);($#,##0.00)"; ws.get_Range(ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; #endregion Application.ScreenUpdating = true; }