private Excel.Range getRange(Detail ws, int row, int column)
{
return(getRange(ws, row, column, row, column));
}
private Excel.Range getRange(Detail ws, int fromR, int fromC, int toR, int toC)
{
return(ws.Range[ws.Cells[fromR, fromC], ws.Cells[toR, toC]]);
}
private void createDetailBody(InvoiceDataset.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.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 38]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Calculate totals
int cartonTotal = 0;
int weightTotal = 0;
decimal invoiceTotal = 0.0M;
for (int i = 0; i < rowCount; i++)
{
cartonTotal += invoiceTable[i].CtnQty;
weightTotal += invoiceTable[i].Weight;
invoiceTotal += invoiceTable[i].DeliveryTotal;
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 38];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].StoreName.Trim();
values[i, 1] = "'" + invoiceTable[i].StoreAddressLine1.Trim();
values[i, 2] = "'" + invoiceTable[i].StoreCity.Trim();
values[i, 3] = "'" + invoiceTable[i].StoreState;
values[i, 4] = "'" + invoiceTable[i].StoreZip;
values[i, 5] = "'" + invoiceTable[i].LocationCode;
values[i, 6] = invoiceTable[i].CtnQty;
values[i, 7] = cartonTotal;
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] = weightTotal;
values[i, 13] = invoiceTable[i].RatedWeight;
values[i, 14] = invoiceTable[i].WeightRate;
values[i, 15] = invoiceTable[i].FreightCharge;
values[i, 16] = invoiceTable[i].Surcharge;
values[i, 17] = invoiceTable[i].ConsolidationCharge;
values[i, 18] = invoiceTable[i].FuelRate;
values[i, 19] = invoiceTable[i].FuelSurcharge;
values[i, 20] = invoiceTable[i].DeliveryTotal;
values[i, 21] = invoiceTotal;
values[i, 22] = "'" + invoiceTable[i].ShipperNumber.ToString();
values[i, 23] = "'" + invoiceTable[i].ShipperName.Trim();
values[i, 24] = "'" + invoiceTable[i].ShipperAddressLine1.Trim();
values[i, 25] = "'" + invoiceTable[i].ShipperCity.Trim();
values[i, 26] = "'" + invoiceTable[i].ShipperState.Trim();
values[i, 27] = "'" + invoiceTable[i].ShipperZip;
values[i, 28] = "'" + invoiceTable[i].RateNote;
values[i, 29] = "'" + invoiceTable[i].BillToName;
values[i, 30] = "'" + invoiceTable[i].BillToAddressline1;
values[i, 31] = "'" + invoiceTable[i].BillToCity;
values[i, 32] = "'" + invoiceTable[i].BillToState;
values[i, 33] = "'" + invoiceTable[i].BillToZip;
values[i, 34] = "'" + invoiceTable[i].InvoiceNumber;
values[i, 35] = "'" + invoiceTable[i].InvoiceDate.ToString("MM/dd/yyyy");
values[i, 36] = "'" + invoiceTable[i].StoreBLNumber;
values[i, 37] = "'" + invoiceTable[i].MasterBLNumber;
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 38]].Value2 = values;
#region Column Formats
//ws.Range[ws.Cells[ROW0_DETAIL,1],ws.Cells[ROW0_DETAIL + rowCount - 1,1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,2],ws.Cells[ROW0_DETAIL + rowCount - 1,2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,3],ws.Cells[ROW0_DETAIL + rowCount - 1,3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,4],ws.Cells[ROW0_DETAIL + rowCount - 1,4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,5],ws.Cells[ROW0_DETAIL + rowCount - 1,5]].NumberFormat = "#,###_);(#,###)";
//ws.Range[ws.Cells[ROW0_DETAIL,5],ws.Cells[ROW0_DETAIL + rowCount - 1,5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,6],ws.Cells[ROW0_DETAIL + rowCount - 1,6]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,6],ws.Cells[ROW0_DETAIL + rowCount - 1,6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,7],ws.Cells[ROW0_DETAIL + rowCount - 1,7]].NumberFormat = "#,###_);(#,###)";
//ws.Range[ws.Cells[ROW0_DETAIL,7],ws.Cells[ROW0_DETAIL + rowCount - 1,7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,8],ws.Cells[ROW0_DETAIL + rowCount - 1,8]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,8],ws.Cells[ROW0_DETAIL + rowCount - 1,8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,9],ws.Cells[ROW0_DETAIL + rowCount - 1,9]].NumberFormat = "#,##0_);(#,##0)";
//ws.Range[ws.Cells[ROW0_DETAIL,9],ws.Cells[ROW0_DETAIL + rowCount - 1,9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,10],ws.Cells[ROW0_DETAIL + rowCount - 1,10]].NumberFormat = "#,##0_);(#,##0)";
//ws.Range[ws.Cells[ROW0_DETAIL,10],ws.Cells[ROW0_DETAIL + rowCount - 1,10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,11],ws.Cells[ROW0_DETAIL + rowCount - 1,11]].NumberFormat = "#,##0.00_);(#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,11],ws.Cells[ROW0_DETAIL + rowCount - 1,11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,12],ws.Cells[ROW0_DETAIL + rowCount - 1,12]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,12],ws.Cells[ROW0_DETAIL + rowCount - 1,12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,13],ws.Cells[ROW0_DETAIL + rowCount - 1,13]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,13],ws.Cells[ROW0_DETAIL + rowCount - 1,13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,14],ws.Cells[ROW0_DETAIL + rowCount - 1,14]].NumberFormat = "#,##0.0000_);(#,##0.0000)";
//ws.Range[ws.Cells[ROW0_DETAIL,14],ws.Cells[ROW0_DETAIL + rowCount - 1,14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,15],ws.Cells[ROW0_DETAIL + rowCount - 1,15]].NumberFormat = "$#,##0.00_);($#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,15],ws.Cells[ROW0_DETAIL + rowCount - 1,15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,16],ws.Cells[ROW0_DETAIL + rowCount - 1,16]].NumberFormat = "$#,##0.00_);($#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,16],ws.Cells[ROW0_DETAIL + rowCount - 1,16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
#endregion
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;
}
private void showDetail(InvoiceDataset ds)
{
//Get worksheet
Detail detail = global::Argix.Finance.Globals.Detail;
//Header
int rows = ds.InvoiceDetailTable.Rows.Count;
if (rows == 0)
{
return;
}
detail.Manifests.Value = ds.InvoiceDetailTable[0].ManifestNumbers;
detail.PerGallon.Value = ds.InvoiceDetailTable[0].PerGallon;
detail.FSC.Value = ds.InvoiceDetailTable[0].FSCPercentage / 100;
//Detail
object[,] destZip = new object[rows, 1];
object[,] store = new object[rows, 1];
object[,] city = new object[rows, 1];
object[,] state = new object[rows, 1];
object[,] pieces = new object[rows, 1];
object[,] weight = new object[rows, 1];
object[,] delivrate = new object[rows, 1];
object[,] delivchgs = new object[rows, 1];
object[,] fuelsurchg = new object[rows, 1];
object[,] totaldelivchgs = new object[rows, 1];
object[,] lhalloc = new object[rows, 1];
object[,] totalchgs = new object[rows, 1];
for (int i = 0; i < rows; i++)
{
if (!ds.InvoiceDetailTable[i].IsDestinationZipNull())
{
destZip[i, 0] = ds.InvoiceDetailTable[i].DestinationZip;
}
if (!ds.InvoiceDetailTable[i].IsStoreNumberNull())
{
store[i, 0] = ds.InvoiceDetailTable[i].StoreNumber;
}
if (!ds.InvoiceDetailTable[i].IsStoreCityNull())
{
city[i, 0] = ds.InvoiceDetailTable[i].StoreCity;
}
if (!ds.InvoiceDetailTable[i].IsDestinationStateNull())
{
state[i, 0] = ds.InvoiceDetailTable[i].DestinationState;
}
if (!ds.InvoiceDetailTable[i].IsTotalPiecesNull())
{
pieces[i, 0] = ds.InvoiceDetailTable[i].TotalPieces;
}
if (!ds.InvoiceDetailTable[i].IsTotalWeightNull())
{
weight[i, 0] = ds.InvoiceDetailTable[i].TotalWeight;
}
if (!ds.InvoiceDetailTable[i].IsDeliveryRateNull())
{
delivrate[i, 0] = ds.InvoiceDetailTable[i].DeliveryRate;
}
if (!ds.InvoiceDetailTable[i].IsDeliveryChargesNull())
{
delivchgs[i, 0] = ds.InvoiceDetailTable[i].DeliveryCharges;
}
if (!ds.InvoiceDetailTable[i].IsFuelSurchargeNull())
{
fuelsurchg[i, 0] = ds.InvoiceDetailTable[i].FuelSurcharge;
}
if (!ds.InvoiceDetailTable[i].IsTotalDeliveryChargesNull())
{
totaldelivchgs[i, 0] = ds.InvoiceDetailTable[i].TotalDeliveryCharges;
}
if (!ds.InvoiceDetailTable[i].IsLHAllocationNull())
{
lhalloc[i, 0] = ds.InvoiceDetailTable[i].LHAllocation;
}
if (!ds.InvoiceDetailTable[i].IsTotalChargesNull())
{
totalchgs[i, 0] = ds.InvoiceDetailTable[i].TotalCharges;
}
}
detail.Range[detail.Cells[4, 1], detail.Cells[3 + rows, 1]].Value2 = destZip;
detail.Range[detail.Cells[4, 2], detail.Cells[3 + rows, 2]].Value2 = store;
detail.Range[detail.Cells[4, 3], detail.Cells[3 + rows, 3]].Value2 = city;
detail.Range[detail.Cells[4, 4], detail.Cells[3 + rows, 4]].Value2 = state;
detail.Range[detail.Cells[4, 5], detail.Cells[3 + rows, 5]].Value2 = pieces;
detail.Range[detail.Cells[4, 6], detail.Cells[3 + rows, 6]].Value2 = weight;
detail.Range[detail.Cells[4, 7], detail.Cells[3 + rows, 7]].Value2 = delivrate;
detail.Range[detail.Cells[4, 8], detail.Cells[3 + rows, 8]].Value2 = delivchgs;
detail.Range[detail.Cells[4, 9], detail.Cells[3 + rows, 9]].Value2 = fuelsurchg;
detail.Range[detail.Cells[4, 10], detail.Cells[3 + rows, 10]].Value2 = totaldelivchgs;
detail.Range[detail.Cells[4, 11], detail.Cells[3 + rows, 11]].Value2 = lhalloc;
detail.Range[detail.Cells[4, 12], detail.Cells[3 + rows, 12]].Value2 = totalchgs;
}