public override void CreateInvoice(Excel.Worksheet xlWorkSheet)
{
try
{
String SheetName = ObjSellerDetails.Name.Replace(":", "").Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").Replace("[", "").Replace("]", "");
xlWorkSheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
Int32 InvoiceHeaderStartRow = 0, InvoiceStartCol = 1;
Excel.Range xlRange = null;
#region Print Invoice Header
#region Print Seller Details
Int32 CustDetailsStartRow = 1 + InvoiceHeaderStartRow, CustDetailsStartCol = InvoiceStartCol;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, CustDetailsStartCol];
xlRange.Value = "Details of Receiver";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, CustDetailsStartCol], xlWorkSheet.Cells[CustDetailsStartRow, CustDetailsStartCol + 6]];
xlRange.Merge();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, CustDetailsStartCol];
xlRange.Value = "Name";
xlWorkSheet.Cells[CustDetailsStartRow + 1, CustDetailsStartCol + 2].Value = ObjSellerDetails.Name;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, CustDetailsStartCol];
xlRange.Value = "Address";
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, CustDetailsStartCol + 2];
xlRange.Value = ObjSellerDetails.Address;
xlRange.WrapText = true;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 3, CustDetailsStartCol];
xlRange.Value = "State";
xlWorkSheet.Cells[CustDetailsStartRow + 3, CustDetailsStartCol + 2].Value = ObjSellerDetails.State;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 4, CustDetailsStartCol];
xlRange.Value = "State code";
xlWorkSheet.Cells[CustDetailsStartRow + 4, CustDetailsStartCol + 2].Value = ObjSellerDetails.StateCode;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 5, CustDetailsStartCol];
xlRange.Value = "GSTIN";
xlWorkSheet.Cells[CustDetailsStartRow + 5, CustDetailsStartCol + 2].Value = ObjSellerDetails.GSTIN;
for (int i = 0; i < 5; i++)
{
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + 1 + i, CustDetailsStartCol], xlWorkSheet.Cells[CustDetailsStartRow + 1 + i, CustDetailsStartCol + 1]];
xlRange.Merge();
xlRange.Font.Bold = true;
xlRange.Font.Name = "Calibri";
xlRange.Font.Size = 10;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + 1 + i, CustDetailsStartCol + 2], xlWorkSheet.Cells[CustDetailsStartRow + 1 + i, CustDetailsStartCol + 6]];
xlRange.Merge();
}
#endregion
#region Print Date & Invoice#
xlWorkSheet.Cells[CustDetailsStartRow, CustDetailsStartCol + 7].Value = "Date";
xlWorkSheet.Cells[CustDetailsStartRow, CustDetailsStartCol + 9].Value = DateOfInvoice.ToString("dd-MMM-yyyy");
xlWorkSheet.Cells[CustDetailsStartRow + 1, CustDetailsStartCol + 7].Value = InvoiceNumberText;
xlWorkSheet.Cells[CustDetailsStartRow + 1, CustDetailsStartCol + 9].Value = SerialNumber;
for (int i = 0; i < 2; i++)
{
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + i, CustDetailsStartCol + 7], xlWorkSheet.Cells[CustDetailsStartRow + i, CustDetailsStartCol + 8]];
xlRange.Merge();
xlRange.Font.Bold = true;
xlRange.Font.Name = "Calibri";
xlRange.Font.Size = 10;
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + i, CustDetailsStartCol + 9], xlWorkSheet.Cells[CustDetailsStartRow + i, CustDetailsStartCol + 13]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + 2, CustDetailsStartCol + 7], xlWorkSheet.Cells[CustDetailsStartRow + 5, CustDetailsStartCol + 13]];
xlRange.Merge();
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow + 6, CustDetailsStartCol], xlWorkSheet.Cells[CustDetailsStartRow + 6, CustDetailsStartCol + 13]];
xlRange.Merge();
#endregion
#endregion
#region Item Details
Int32 InvoiceStartRow = InvoiceHeaderStartRow + 8;
#region Item Details Header
Dictionary <String, Int32> DictColNumbers = new Dictionary <String, Int32>();
String SlNoCol = "SL NO", ItemNameCol = "Description of Goods", HSNCol = "HSN Code", OrderQtyCol = "Ord Qty", SaleQtyCol = "Sale Qty", UnitsCol = "Units", RateCol = "Rate";
String ItemTotalCol = "Total", ItemDiscCol = "Discount", ItemTaxableValueCol = "Taxable Value";
String[] ArrHeader = new String[] { SlNoCol, ItemNameCol, HSNCol, OrderQtyCol, SaleQtyCol, UnitsCol, RateCol, ItemTotalCol, ItemDiscCol, ItemTaxableValueCol };
for (int i = 0; i < ArrHeader.Length; i++)
{
DictColNumbers.Add(ArrHeader[i], InvoiceStartCol + i);
}
String[] ArrTaxesHeader = new String[] { "CGSTRate", "CGSTAmount", "SGSTRate", "SGSTAmount" /*, "IGSTRate", "IGSTAmount"*/ };
for (int i = 0; i < ArrTaxesHeader.Length; i++)
{
DictColNumbers.Add(ArrTaxesHeader[i], InvoiceStartCol + ArrHeader.Length + i);
}
for (int i = 0; i < ArrHeader.Length; i++)
{
xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[ArrHeader[i]]].Value = ArrHeader[i];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[ArrHeader[i]]], xlWorkSheet.Cells[InvoiceStartRow + 1, DictColNumbers[ArrHeader[i]]]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
for (int i = 0; i < ArrTaxesHeader.Length; i += 2)
{
xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[ArrTaxesHeader[i]]].Value = ArrTaxesHeader[i].Replace("Rate", "");
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[ArrTaxesHeader[i]]], xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[ArrTaxesHeader[i + 1]]]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[InvoiceStartRow + 1, DictColNumbers[ArrTaxesHeader[i]]].Value = "Rate";
xlWorkSheet.Cells[InvoiceStartRow + 1, DictColNumbers[ArrTaxesHeader[i + 1]]].Value = "Amount";
}
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[InvoiceStartRow + 1, DictColNumbers[ArrTaxesHeader[ArrTaxesHeader.Length - 1]]]];
xlRange.Font.Bold = true;
xlRange.WrapText = true;
#endregion
Int32 ItemDetailsStartRow = InvoiceStartRow + 2;
for (int i = 0; i < ListProducts.Count; i++)
{
ProductDetailsForInvoice CurrProd = ListProducts[i];
xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[SlNoCol]].Value = CurrProd.SerialNumber;
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemNameCol]];
xlRange.Value = CurrProd.Description; xlRange.WrapText = true;
xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[HSNCol]].Value = CurrProd.HSNCode;
xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[OrderQtyCol]].Value = CurrProd.OrderQuantity;
xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[SaleQtyCol]].Value = CurrProd.SaleQuantity;
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[UnitsCol]];
xlRange.Value = CurrProd.UnitsOfMeasurement;
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[RateCol]];
xlRange.Value = CurrProd.Rate; xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemTotalCol]];
xlRange.NumberFormat = "#,##0.00";
Excel.Range xlRangeSaleQty = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[SaleQtyCol]];
Excel.Range xlRangePrice = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[RateCol]];
xlRange.Formula = "=(" + xlRangeSaleQty.Address[false, false] + "*" + xlRangePrice.Address[false, false] + ")";
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemDiscCol]];
xlRange.NumberFormat = "#,##0.00";
Excel.Range xlRangeTotal = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemTotalCol]];
if (CurrProd.DiscountGroup.DiscountType == DiscountTypes.PERCENT)
{
xlRange.Formula = "=" + xlRangeTotal.Address[false, false] + "*" + CurrProd.DiscountGroup.Discount + "/100";
}
else if (CurrProd.DiscountGroup.DiscountType == DiscountTypes.ABSOLUTE)
{
xlRange.Value = CurrProd.DiscountGroup.Discount;
}
else
{
xlRange.Formula = "=" + xlRangeTotal.Address[false, false];
}
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemTaxableValueCol]];
xlRange.NumberFormat = "#,##0.00";
Excel.Range xlRangeDiscount = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemDiscCol]];
xlRange.Formula = "=(" + xlRangeTotal.Address[false, false] + "-" + xlRangeDiscount.Address[false, false] + ")";
TaxDetails[] ArrTaxDetails = new TaxDetails[] { CurrProd.CGSTDetails, CurrProd.SGSTDetails, CurrProd.IGSTDetails };
for (int j = 0; j < ArrTaxesHeader.Length; j += 2)
{
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ArrTaxesHeader[j]]];
xlRange.Value = ArrTaxDetails[j / 2].TaxRate; xlRange.NumberFormat = "#0.0%";
xlRange = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ArrTaxesHeader[j + 1]]];
xlRange.NumberFormat = "#,##0.00";
Excel.Range xlRangeTaxableValue = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ItemTaxableValueCol]];
Excel.Range xlRangeRate = xlWorkSheet.Cells[ItemDetailsStartRow + i, DictColNumbers[ArrTaxesHeader[j]]];
xlRange.Formula = "=(" + xlRangeTaxableValue.Address[false, false] + "*" + xlRangeRate.Address[false, false] + ")";
}
}
#endregion
#region TotalSale & Other Details
Int32 ReportAppendRowsAtBottom = CommonFunctions.ObjApplicationSettings.ReportAppendRowsAtBottom;
Int32 TotalRowNum = ItemDetailsStartRow + ListProducts.Count + ReportAppendRowsAtBottom + 1;
xlRange = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[SlNoCol]];
xlRange.Value = "Total";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalRowNum, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[TotalRowNum, DictColNumbers[RateCol]]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
Excel.Range xlRangeSaleTotalFrom = xlWorkSheet.Cells[ItemDetailsStartRow, DictColNumbers[ItemTotalCol]];
Excel.Range xlRangeSaleTotalTo = xlWorkSheet.Cells[TotalRowNum - 1, DictColNumbers[ItemTotalCol]];
xlRange = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ItemTotalCol]];
xlRange.Formula = "=Sum(" + xlRangeSaleTotalFrom.Address[false, false] + ":" + xlRangeSaleTotalTo.Address[false, false] + ")";
xlRange.NumberFormat = "#,##0.00";
TotalSalesValue = "='" + SheetName + "'!" + xlRange.Address[false, false];
Excel.Range xlRangeDiscountFrom = xlWorkSheet.Cells[ItemDetailsStartRow, DictColNumbers[ItemDiscCol]];
Excel.Range xlRangeDiscountTo = xlWorkSheet.Cells[TotalRowNum - 1, DictColNumbers[ItemDiscCol]];
xlRange = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ItemDiscCol]];
xlRange.Formula = "=Sum(" + xlRangeDiscountFrom.Address[false, false] + ":" + xlRangeDiscountTo.Address[false, false] + ")";
xlRange.NumberFormat = "#,##0.00";
//TotalDiscount = "=Sum('" + SheetName + "'!" + xlRangeDiscountFrom.Address[false, false] + ":" + xlRangeDiscountTo.Address[false, false] + ")";
TotalDiscount = "='" + SheetName + "'!" + xlRange.Address[false, false];
Excel.Range xlRangeTaxableValFrom = xlWorkSheet.Cells[ItemDetailsStartRow, DictColNumbers[ItemTaxableValueCol]];
Excel.Range xlRangeTaxableValTo = xlWorkSheet.Cells[TotalRowNum - 1, DictColNumbers[ItemTaxableValueCol]];
xlRange = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ItemTaxableValueCol]];
xlRange.Formula = "=Sum(" + xlRangeTaxableValFrom.Address[false, false] + ":" + xlRangeTaxableValTo.Address[false, false] + ")";
xlRange.NumberFormat = "#,##0.00";
ArrTotalTaxes = new String[ArrTaxesHeader.Length / 2];
for (int j = 0; j < ArrTaxesHeader.Length; j += 2)
{
Excel.Range xlRangeTaxAmtFrom = xlWorkSheet.Cells[ItemDetailsStartRow, DictColNumbers[ArrTaxesHeader[j + 1]]];
Excel.Range xlRangeTaxAmtTo = xlWorkSheet.Cells[TotalRowNum - 1, DictColNumbers[ArrTaxesHeader[j + 1]]];
xlRange = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ArrTaxesHeader[j]]];
xlRange.Formula = "=Sum(" + xlRangeTaxAmtFrom.Address[false, false] + ":" + xlRangeTaxAmtTo.Address[false, false] + ")";
xlRange.NumberFormat = "#,##0.00";
ArrTotalTaxes[j / 2] = "=Sum('" + SheetName + "'!" + xlRangeTaxAmtFrom.Address[false, false] + ":" + xlRangeTaxAmtTo.Address[false, false] + ")";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ArrTaxesHeader[j]]], xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ArrTaxesHeader[j + 1]]]];
xlRange.Merge();
}
Excel.Range xlRangeTaxFrom = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ArrTaxesHeader[0]]];
Excel.Range xlRangeTaxTo = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ArrTaxesHeader[ArrTaxesHeader.Length - 1]]];
TotalTax = "=Sum('" + SheetName + "'!" + xlRangeTaxFrom.Address[false, false] + ":" + xlRangeTaxTo.Address[false, false] + ")";
#endregion
#region Total Invoice Value
Int32 TotalInvoiceValueRow = TotalRowNum + 1, TotalInvoiceValueCol = DictColNumbers[ItemDiscCol], LastInvoiceCol = DictColNumbers[ArrTaxesHeader[ArrTaxesHeader.Length - 1]];
Int32 TotalInvoiceTextCol = DictColNumbers[ItemTotalCol];
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow, DictColNumbers[SlNoCol]];
xlRange.Value = "Total Invoice Value(in figures)";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceTextCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Excel.Range xlRangeTaxableValueCell = xlWorkSheet.Cells[TotalRowNum, DictColNumbers[ItemTaxableValueCol]];
Excel.Range xlRangeLastTotalTaxAmtCell = xlWorkSheet.Cells[TotalRowNum, LastInvoiceCol];
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceValueCol];
xlRange.Formula = "=Round(Sum(" + xlRangeTaxableValueCell.Address[false, false] + ":" + xlRangeLastTotalTaxAmtCell.Address[false, false] + "), 0)";
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceValueCol], xlWorkSheet.Cells[TotalInvoiceValueRow, LastInvoiceCol]];
xlRange.Merge();
//TotalInvoiceValue = "=Sum('" + SheetName + "'!" + xlRangeTaxableValueCell.Address[false, false] + ":" + xlRangeLastTotalTaxAmtCell.Address[false, false] + ")";
TotalInvoiceValue = "='" + SheetName + "'!" + xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceValueCol].Address[false, false];
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 1, DictColNumbers[SlNoCol]];
xlRange.Value = "Total Invoice Value(in words)";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 1, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[TotalInvoiceValueRow + 1, TotalInvoiceTextCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 1, TotalInvoiceValueCol];
xlRange.Formula = "=NumberToWords(" + xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceValueCol].Address[false, false] + ") & \" only\"";
xlRange.WrapText = true;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 1, TotalInvoiceValueCol], xlWorkSheet.Cells[TotalInvoiceValueRow + 1, LastInvoiceCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Int32 LastInvoiceRowNum = TotalInvoiceValueRow + 2;
#region Old Balance Row
if (PrintOldBalance)
{
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 2, DictColNumbers[SlNoCol]];
xlRange.Value = "Old Balance";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 2, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[TotalInvoiceValueRow + 2, TotalInvoiceTextCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 2, TotalInvoiceValueCol];
xlRange.Value = ObjSellerDetails.OldBalance;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 2, TotalInvoiceValueCol], xlWorkSheet.Cells[TotalInvoiceValueRow + 2, LastInvoiceCol]];
xlRange.Merge();
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 3, DictColNumbers[SlNoCol]];
xlRange.Value = "Net Total";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 3, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[TotalInvoiceValueRow + 3, TotalInvoiceTextCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Excel.Range xlRangeTotalInvoiceValueCell = xlWorkSheet.Cells[TotalInvoiceValueRow, TotalInvoiceValueCol];
Excel.Range xlRangeOBCell = xlWorkSheet.Cells[TotalInvoiceValueRow + 2, TotalInvoiceValueCol];
xlRange = xlWorkSheet.Cells[TotalInvoiceValueRow + 3, TotalInvoiceValueCol];
xlRange.Formula = "=Round((" + xlRangeTotalInvoiceValueCell.Address[false, false] + "+" + xlRangeOBCell.Address[false, false] + "), 0)";
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow + 3, TotalInvoiceValueCol], xlWorkSheet.Cells[TotalInvoiceValueRow + 3, LastInvoiceCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
LastInvoiceRowNum += 2;
}
#endregion
xlRange = xlWorkSheet.Cells[LastInvoiceRowNum, DictColNumbers[SlNoCol]];
xlRange.Value = "Amount of Tax Subject to Reverse Charges";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[LastInvoiceRowNum, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[LastInvoiceRowNum, TotalInvoiceTextCol]];
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlRange = xlWorkSheet.Cells[LastInvoiceRowNum, TotalInvoiceValueCol];
xlRange.Value = "0";
xlRange.WrapText = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[LastInvoiceRowNum, TotalInvoiceValueCol], xlWorkSheet.Cells[LastInvoiceRowNum, LastInvoiceCol]];
xlRange.Merge();
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[TotalInvoiceValueRow, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[LastInvoiceRowNum, LastInvoiceCol]];
xlRange.Font.Bold = true;
#endregion
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, DictColNumbers[SlNoCol]], xlWorkSheet.Cells[LastInvoiceRowNum, LastInvoiceCol]];
xlRange.Font.Name = "Calibri";
xlRange.Font.Size = 10;
SellerInvoiceForm.SetAllBorders(xlRange);
#region Set Column Width & Row Height
Double[] ArrColumnWidths = new Double[] { 3.14, 13.86, 6.29, 3.57, 3.57, 6.57, 5.71, 8.14, 8.14, 8.14, 4.86, 7.14, 4.86, 7.14 /*, 4.86, 7.14*/ };
for (int i = 0; i < ArrColumnWidths.Length; i++)
{
xlRange = xlWorkSheet.Columns[Char.ConvertFromUtf32(65 + i)];
xlRange.ColumnWidth = ArrColumnWidths[i];
}
xlWorkSheet.UsedRange.Rows.AutoFit();
xlRange = xlWorkSheet.Rows[TotalInvoiceValueRow + 1];
xlRange.RowHeight = 29.25;
#endregion
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlWorkSheet, CurrReportSettings.HeaderSubTitle, CurrReportSettings);
xlWorkSheet.PageSetup.Zoom = false;
xlWorkSheet.PageSetup.FitToPagesTall = false;
xlWorkSheet.PageSetup.FitToPagesWide = 1;
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("InvoiceGST.CreateInvoice()", ex);
throw;
}
}
private void CreateVendorPurchaseOrder(ReportType EnumReportType, DataRow[] drItems, DataRow[] drVendors,
String SelectedDateTimeString, Int32 StartRow, Int32 StartColumn,
List <Int32> ListItemIndexes, List <Int32> ListVendorIndexes, Excel.Workbook xlWorkbook)
{
try
{
Boolean PrintVATPercent = false;
ReportSettings CurrReportSettings = null;
String ReportTypeName = "", BillNumberText = "", SaveFileName = "";
switch (EnumReportType)
{
case ReportType.PURCHASEORDER:
CurrReportSettings = CommonFunctions.ObjPurchaseOrderSettings;
ReportTypeName = "Purchase Order";
PrintVATPercent = true;
BillNumberText = "PO#";
SaveFileName = txtBoxOutputFolder.Text + "\\PurchaseOrder_" + SelectedDateTimeString + ".xlsx";
break;
default:
return;
}
Excel.Worksheet xlPOWorksheet = xlWorkbook.Sheets[1];
#region Print PO Sheet for each Vendor
Double Quantity;
Int32 POHeaderStartRow = 0;
Int32 POStartRow = POHeaderStartRow + 5;
Int32 PONumber = CurrReportSettings.LastNumber;
Int32 ValidVendorCount = ListVendorIndexes.Where(s => (s >= 0)).ToList().Count;
Int32 ValidItemCount = ListItemIndexes.Where(s => (s >= 0)).ToList().Count;
Int32 ProgressBarCount = (ValidVendorCount * ValidItemCount);
Int32 Counter = 0;
Int32 SlNoColNum = 1, ItemNameColNum = 2, OrdQtyColNum = 3, RecdQtyColNum = 4, PriceColNum = 5, TotalColNum = 6;
Int32 ReportAppendRowsAtBottom = CommonFunctions.ObjApplicationSettings.ReportAppendRowsAtBottom;
Int32 OrderTotalRowOffset = 1 + ReportAppendRowsAtBottom, DiscountRowOffset = 2 + ReportAppendRowsAtBottom, OldBalanceRowOffset = 3 + ReportAppendRowsAtBottom, TotalCostRowOffset = 4 + ReportAppendRowsAtBottom;
Int32 VendorCount = 0, PODetailsCount = 5;
for (int i = 0; i < ListVendorIndexes.Count; i++)
{
if (ListVendorIndexes[i] < 0)
{
continue;
}
VendorCount++;
lblStatus.Text = "Creating " + ReportTypeName + " for Vendor " + VendorCount + " of " + ValidVendorCount;
Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets.Add(Type.Missing, xlWorkbook.Sheets[xlWorkbook.Sheets.Count]);
String SheetName = drVendors[ListVendorIndexes[i]]["VendorName"].ToString().Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
xlWorkSheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
VendorDetails ObjCurrentVendor = CommonFunctions.ObjVendorMaster.GetVendorDetails(drVendors[ListVendorIndexes[i]]["VendorName"].ToString());
#region Print PO Items
Int32 SlNo = 0;
#region Print PO Header
Excel.Range xlRange = xlWorkSheet.Cells[1 + POHeaderStartRow, 1];
Int32 CustDetailsStartRow = 1 + POHeaderStartRow;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum];
xlRange.Value = "Name";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["VendorName"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum];
xlRange.Value = "Address";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["Address"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1];
xlRange.WrapText = true;
if (drVendors[ListVendorIndexes[i]]["Address"].ToString().Length >= 25)
{
xlRange.EntireColumn.ColumnWidth = 25;
}
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "TIN#";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["TINNumber"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["Phone"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = "Date";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum].Value = DateTime.Today.ToString("dd-MMM-yyyy");
PONumber++;
xlRange = xlWorkSheet.Cells[1 + CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = BillNumberText;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, TotalColNum].Value = PONumber;
drVendors[ListVendorIndexes[i]]["PONumber"] = PONumber;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, 1], xlWorkSheet.Cells[CustDetailsStartRow + 3, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.Cells[POStartRow + 1, SlNoColNum].Value = "Sl.No.";
xlWorkSheet.Cells[POStartRow + 1, ItemNameColNum].Value = "Item Name";
xlWorkSheet.Cells[POStartRow + 1, OrdQtyColNum].Value = "Order Quantity";
xlWorkSheet.Cells[POStartRow + 1, RecdQtyColNum].Value = "Received Quantity";
xlWorkSheet.Cells[POStartRow + 1, PriceColNum].Value = "Price";
xlWorkSheet.Cells[POStartRow + 1, TotalColNum].Value = "Total";
xlWorkSheet.Range[xlWorkSheet.Cells[POStartRow + 1, SlNoColNum], xlWorkSheet.Cells[POStartRow + 1, TotalColNum]].Font.Bold = true;
for (int j = 0; j < ListItemIndexes.Count; j++)
{
if (ListItemIndexes[j] < 0)
{
continue;
}
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
if (xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value == null)
{
continue;
}
if (String.IsNullOrEmpty(xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value.ToString()))
{
continue;
}
Quantity = Double.Parse(xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value.ToString());
drItems[ListItemIndexes[j]]["Quantity"] = Double.Parse(drItems[ListItemIndexes[j]]["Quantity"].ToString()) + Quantity;
SlNo++;
xlWorkSheet.Cells[SlNo + POStartRow + 1, SlNoColNum].Value = SlNo;
xlWorkSheet.Cells[SlNo + POStartRow + 1, ItemNameColNum].Value = drItems[ListItemIndexes[j]]["StockName"].ToString();
xlWorkSheet.Cells[SlNo + POStartRow + 1, OrdQtyColNum].Value = Quantity;
if (chkBoxUseOrdQty.Checked == true)
{
xlWorkSheet.Cells[SlNo + POStartRow + 1, RecdQtyColNum].Value = Quantity;
}
xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum].Value = CommonFunctions.ObjProductMaster.GetPriceForProduct(drItems[ListItemIndexes[j]]["ItemName"].ToString(), ObjCurrentVendor.PriceGroupIndex);
xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum].NumberFormat = "#,##0.00";
Excel.Range xlRangeSaleQty = xlWorkSheet.Cells[SlNo + POStartRow + 1, RecdQtyColNum];
Excel.Range xlRangePrice = xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum];
Excel.Range xlRangeTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1, TotalColNum];
xlRangeTotal.Formula = "=(" + xlRangeSaleQty.Address[false, false] + "*" + xlRangePrice.Address[false, false] + ")";
xlWorkSheet.Cells[SlNo + POStartRow + 1, TotalColNum].NumberFormat = "#,##0.00";
}
Excel.Range xlRangeSaleQtyFrom = xlWorkSheet.Cells[1 + POStartRow + 1, RecdQtyColNum];
Excel.Range xlRangeSaleQtyTo = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset - 1, RecdQtyColNum];
Excel.Range xlRangeTotalCost = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSaleTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
drVendors[ListVendorIndexes[i]]["Total"] = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
drVendors[ListVendorIndexes[i]]["Quantity"] = "=Sum('" + xlWorkSheet.Name + "'!" + xlRangeSaleQtyFrom.Address[false, false] + ":" + xlRangeSaleQtyTo.Address[false, false] + ")";
#region Sales Total Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum - 1];
xlRange.Value = "Sales Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotalFrom = xlWorkSheet.Cells[1 + POStartRow + 1, TotalColNum];
Excel.Range xlRangeSalesTotalTo = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset - 1, TotalColNum];
xlRange.Formula = "=Sum(" + xlRangeSalesTotalFrom.Address[false, false] + ":" + xlRangeSalesTotalTo.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
#region Discount Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum - 1];
xlRange.Value = "Discount";
xlRange.Font.Bold = true;
DiscountGroupDetails ObjDiscountGroup = CommonFunctions.ObjVendorMaster.GetVendorDiscount(ObjCurrentVendor.VendorName);
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlSalesTotal1 = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
if (ObjDiscountGroup.DiscountType == DiscountTypes.PERCENT)
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false] + "*" + ObjDiscountGroup.Discount + "/100";
}
else if (ObjDiscountGroup.DiscountType == DiscountTypes.ABSOLUTE)
{
xlRange.Value = ObjDiscountGroup.Discount;
}
else
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false];
}
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
drVendors[ListVendorIndexes[i]]["TotalDiscount"] = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
if (PrintVATPercent)
{
#region VAT Percent Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "VAT Percent " + CurrReportSettings.VATPercent + "%";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum];
Excel.Range xlSalesTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlDiscount = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
xlRange.Formula = "=(" + xlSalesTotal.Address[false, false] + "-" + xlDiscount.Address[false, false] + ")*" + CurrReportSettings.VATPercent + "/100";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
drVendors[ListVendorIndexes[i]]["TotalTax"] = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
#region Total Cost Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum - 1];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlRangeDiscount = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlRangeOldBalance = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Formula = "=Round(" + xlRangeSalesTotal.Address[false, false] + "+" + xlRangeOldBalance.Address[false, false] + "-" + xlRangeDiscount.Address[false, false] + ", 0)";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[POStartRow + 1, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlWorkSheet, CurrReportSettings.HeaderSubTitle, CurrReportSettings);
}
#endregion
if (chkBoxCreateSummary.Checked)
{
CreateVendorSummarySheet(drVendors, xlWorkbook, CurrReportSettings);
}
xlApp.DisplayAlerts = false;
xlWorkbook.Sheets[SelectedDateTimeString].Delete();
xlApp.DisplayAlerts = true;
Excel.Worksheet FirstWorksheet = xlWorkbook.Sheets[1];
FirstWorksheet.Select();
#region Write PONumber to Settings File
CurrReportSettings.LastNumber = PONumber;
#endregion
ReportProgressFunc(100);
xlWorkbook.SaveAs(SaveFileName);
xlWorkbook.Close();
lblStatus.Text = "Completed creation of " + ReportTypeName + "s for all Vendors";
CommonFunctions.ReleaseCOMObject(xlWorkbook);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateVendorPurchaseOrder", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void CreateVendorSummarySheet(DataRow[] drVendors, Excel.Workbook xlWorkbook, ReportSettings CurrReportSettings)
{
try
{
lblStatus.Text = "Creating Vendor Summary Sheet";
#region Print Vendor Summary Sheet
Int32 SummaryStartRow = 0, CurrRow = 0, CurrCol = 0;
Excel.Worksheet xlVendorSummaryWorkSheet = xlWorkbook.Worksheets.Add(xlWorkbook.Sheets[1]);
xlVendorSummaryWorkSheet.Name = "Vendor Summary";
SummaryStartRow++;
Excel.Range xlRange1 = xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 1];
xlRange1.Value = "Date";
xlRange1.Font.Bold = true;
xlRange1 = xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 2];
xlRange1.Value = DateTime.Today.ToString("dd-MMM-yyyy");
xlRange1 = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 2], xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 3]];
xlRange1.Merge();
xlRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
CurrRow = SummaryStartRow + 1;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Sl#";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Bill#";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Vendor Name";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Purchase";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Cancel";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Return";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Discount";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Total Tax";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Net Sale";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Cash";
Int32 LastCol = CurrCol;
xlRange1 = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[CurrRow, 1], xlVendorSummaryWorkSheet.Cells[CurrRow, LastCol]];
xlRange1.Font.Bold = true;
Int32 VendorsCount = 0;
for (int i = 0; i < drVendors.Length; i++)
{
if (String.IsNullOrEmpty(drVendors[i]["PONumber"].ToString().Trim()))
{
continue;
}
VendorsCount++;
CurrRow = VendorsCount + SummaryStartRow + 1;
CurrCol = 0;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = VendorsCount;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = drVendors[i]["PONumber"].ToString();
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = drVendors[i]["VendorName"].ToString();
CurrCol++; Excel.Range xlRangePurchase = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangePurchase.Formula = drVendors[i]["Total"].ToString();
xlRangePurchase.NumberFormat = "#,##0.00";
CurrCol++; Excel.Range xlRangeCancel = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeReturn = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeDiscount = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeTotalTax = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeNetSale = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangeDiscount.Formula = drVendors[i]["TotalDiscount"].ToString();
if (CommonFunctions.ObjGeneralSettings.SummaryLocation == 0)
{
xlRangeTotalTax.Formula = drVendors[i]["TotalTax"].ToString();
}
xlRangeNetSale.Formula = "=Round(" + xlRangePurchase.Address[false, false]
+ "-" + xlRangeCancel.Address[false, false]
+ "-" + xlRangeReturn.Address[false, false]
+ "-" + xlRangeDiscount.Address[false, false]
+ "+" + xlRangeTotalTax.Address[false, false] + ", 0)";
xlRangeNetSale.NumberFormat = "#,##0.00";
CurrCol++; Excel.Range xlRangeCash = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangeCash.NumberFormat = "#,##0.00";
}
CurrRow = VendorsCount + SummaryStartRow + 2;
Excel.Range xlRange = xlVendorSummaryWorkSheet.Cells[CurrRow, 3];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange.Font.Color = Color.Red;
for (int i = 4; i <= LastCol; i++)
{
CurrCol = i;
Excel.Range xlRangeTotal = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
Excel.Range xlRangeTotalFrom = xlVendorSummaryWorkSheet.Cells[SummaryStartRow + 2, CurrCol];
Excel.Range xlRangeTotalTo = xlVendorSummaryWorkSheet.Cells[CurrRow - 1, CurrCol];
xlRangeTotal.Formula = "=Sum(" + xlRangeTotalFrom.Address[false, false] + ":" + xlRangeTotalTo.Address[false, false] + ")";
xlRangeTotal.NumberFormat = "#,##0.00";
xlRangeTotal.Font.Bold = true;
}
xlRange = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[SummaryStartRow + 1, 1], xlVendorSummaryWorkSheet.Cells[CurrRow + 1, LastCol]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlVendorSummaryWorkSheet.UsedRange.Columns.AutoFit();
xlRange = xlVendorSummaryWorkSheet.Columns["B"];
xlRange.ColumnWidth = 7;
xlRange = xlVendorSummaryWorkSheet.Columns["C"];
xlRange.ColumnWidth = 24;
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlVendorSummaryWorkSheet, "Vendorwise Summary", CurrReportSettings);
#endregion
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateVendorSummarySheet()", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void UpdateVendorHistoryFile(Excel.Application xlApp, DataRow[] drVendors, DateTime SummaryCreationDate)
{
try
{
ReportProgressFunc(0);
Excel.Workbook xlVendorHistoryWorkbook;
Excel.Worksheet xlVendorHistoryWorksheet;
Int32 ProgressBarCount = drVendors.Length, CurrVendorCount = 0;
List <String> ListVendorKeys;
String[] Header = new String[] { "Create Date", "Update Date", "Bill#", "Vendor Name", "Sale", "Cancel", "Return", "Discount", "Total Tax", "Net Sale", "Cash" };
if (!File.Exists(VendorHistoryFile))
{
xlVendorHistoryWorkbook = xlApp.Workbooks.Add();
xlVendorHistoryWorksheet = xlVendorHistoryWorkbook.Worksheets.Add();
xlVendorHistoryWorksheet.Name = "Vendor History";
for (int i = 0; i < Header.Length; i++)
{
xlVendorHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlVendorHistoryWorksheet.Range[xlVendorHistoryWorksheet.Cells[1, 1], xlVendorHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlVendorHistoryWorkbook.SaveAs(VendorHistoryFile);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
ListVendorKeys = new List <String>();
}
else
{
DataTable dtVendorHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Vendor History", VendorHistoryFile, "[Create Date], [Bill#], [Vendor Name]");
ListVendorKeys = dtVendorHistory.AsEnumerable().Select(s => s.Field <DateTime>("Create Date").ToString("dd-MMM-yyyy")
+ "||" + s.Field <Double>("Bill#").ToString()
+ "||" + s.Field <String>("Vendor Name").Trim().ToUpper()).Distinct().ToList();
xlVendorHistoryWorkbook = xlApp.Workbooks.Open(VendorHistoryFile);
xlVendorHistoryWorksheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Vendor History");
}
Int32 RowCount = xlVendorHistoryWorksheet.UsedRange.Rows.Count;
Int32 ColumnCount = xlVendorHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount, StartColumn = 1, LastRow = 0;
for (int i = 0; i < drVendors.Length; i++)
{
CurrVendorCount++;
DataRow dtRow = drVendors[i];
if (dtRow[0] == DBNull.Value)
{
continue;
}
if (String.IsNullOrEmpty(dtRow[0].ToString()))
{
continue;
}
if (ListVendorKeys.Contains(SummaryCreationDate.ToString("dd-MMM-yyyy")
+ "||" + dtRow["Bill#"].ToString().Trim().ToUpper()
+ "||" + dtRow["Vendor Name"].ToString().Trim().ToUpper()))
{
continue;
}
LastRow++;
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1].Value = DateTime.Now.ToString("dd-MMM-yyyy");
for (int j = 1; j < dtRow.ItemArray.Length; j++)
{
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].Value = dtRow[j].ToString();
if (j >= 3)
{
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].NumberFormat = "#,##0.00";
}
}
ReportProgressFunc((CurrVendorCount * 100) / ProgressBarCount);
lblStatus.Text = "Updated " + CurrVendorCount + " of " + ProgressBarCount + " Vendors data in Vendor History";
}
ReportProgressFunc(100);
Excel.Range xlRange = xlVendorHistoryWorksheet.Range[xlVendorHistoryWorksheet.Cells[StartRow, StartColumn], xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + Header.Length - 1]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlVendorHistoryWorkbook.Save();
xlVendorHistoryWorkbook.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateProductPurchasesForm.UpdateVendorHistoryFile()", ex);
throw;
}
}
private void UpdateSellerHistory(Excel.Application xlApp, DataRow[] drSellers, DateTime SummaryCreationDate)
{
try
{
ReportProgressFunc(0);
Excel.Workbook xlSellerHistoryWorkbook;
Excel.Worksheet xlSellerHistoryWorksheet;
Int32 ProgressBarCount = drSellers.Length, CurrSellerCount = 0;
Boolean SellerHistoryFileExists = File.Exists(SellerHistoryFilePath);
List <String> ListSellerKeys;
String[] Header = new String[] { "Create Date", "Update Date", "Bill#", "Seller Name", "Sale", "Cancel", "Return", "Discount", "Total Tax", "Net Sale", "OB", "Cash", "Balance" };
if (!SellerHistoryFileExists)
{
xlSellerHistoryWorkbook = xlApp.Workbooks.Add();
xlSellerHistoryWorksheet = xlSellerHistoryWorkbook.Worksheets.Add();
xlSellerHistoryWorksheet.Name = "Seller History";
for (int i = 0; i < Header.Length; i++)
{
xlSellerHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlSellerHistoryWorksheet.Range[xlSellerHistoryWorksheet.Cells[1, 1], xlSellerHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlSellerHistoryWorkbook.SaveAs(SellerHistoryFilePath);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
ListSellerKeys = new List <String>();
}
else
{
DataTable dtSellerHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Seller History", SellerHistoryFilePath, "[Create Date], [Bill#], [Seller Name]");
ListSellerKeys = dtSellerHistory.AsEnumerable().Select(s => s.Field <DateTime>("Create Date").ToString("dd-MMM-yyyy")
+ "||" + s.Field <Double>("Bill#").ToString()
+ "||" + s.Field <String>("Seller Name").Trim().ToUpper()).Distinct().ToList();
xlSellerHistoryWorkbook = xlApp.Workbooks.Open(SellerHistoryFilePath);
xlSellerHistoryWorksheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Seller History");
}
Int32 RowCount = xlSellerHistoryWorksheet.UsedRange.Rows.Count;
Int32 ColumnCount = xlSellerHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount + 1, StartColumn = 1, LastRow = 0;
Int32 OBColumnIndex = drSellers[0].Table.Columns.IndexOf("OB");
Boolean InsertRecord = false;
for (int i = 0; i < drSellers.Length; i++)
{
CurrSellerCount++;
InsertRecord = true;
DataRow dtRow = drSellers[i];
if (dtRow[0] == DBNull.Value)
{
continue;
}
if (String.IsNullOrEmpty(dtRow[0].ToString()))
{
continue;
}
if (ListSellerKeys.Contains(SummaryCreationDate.ToString("dd-MMM-yyyy")
+ "||" + dtRow["Bill#"].ToString().Trim().ToUpper()
+ "||" + dtRow["Seller Name"].ToString().Trim().ToUpper()))
{
InsertRecord = false;
}
if (InsertRecord)
{
InsertRecord = false;
for (int j = 4; j < dtRow.ItemArray.Length - 1; j++)
{
if (OBColumnIndex == j)
{
continue;
}
if (dtRow[j] != DBNull.Value && !String.IsNullOrEmpty(dtRow[j].ToString().Trim()) &&
Double.Parse(dtRow[j].ToString().Trim()) > 1E-4)
{
InsertRecord = true;
break;
}
}
}
if (InsertRecord)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1].Value = DateTime.Now.ToString("dd-MMM-yyyy");
for (int j = 1; j < dtRow.ItemArray.Length; j++)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].Value = dtRow[j].ToString();
if (j >= 3)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].NumberFormat = "#,##0.00";
}
}
LastRow++;
}
ReportProgressFunc((CurrSellerCount * 100) / ProgressBarCount);
lblStatus.Text = "Updated " + CurrSellerCount + " of " + ProgressBarCount + " Sellers data in Seller History";
}
ReportProgressFunc(100);
if (LastRow > 0)
{
Excel.Range xlRange = xlSellerHistoryWorksheet.Range[xlSellerHistoryWorksheet.Cells[StartRow, StartColumn], xlSellerHistoryWorksheet.Cells[StartRow + LastRow - 1, StartColumn + Header.Length - 1]];
SellerInvoiceForm.SetAllBorders(xlRange);
}
xlSellerHistoryWorkbook.Save();
xlSellerHistoryWorkbook.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateSellerHistory()", ex);
throw ex;
}
}
public override void CreateInvoice(Excel.Worksheet xlWorkSheet)
{
try
{
String SheetName = ObjSellerDetails.Name.Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
xlWorkSheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
Int32 InvoiceHeaderStartRow = 0;
Int32 InvoiceStartRow = InvoiceHeaderStartRow + 5;
Excel.Range xlRange = null;
#region Print Invoice Items
Int32 SlNo = 0;
Int32 SlNoColNum = 1, ItemNameColNum = 2, OrdQtyColNum = 3, SalQtyColNum = 4, PriceColNum = 5, TotalColNum = 6;
Int32 ReportAppendRowsAtBottom = CommonFunctions.ObjApplicationSettings.ReportAppendRowsAtBottom;
Int32 SalesTotalRowOffset = 1 + ReportAppendRowsAtBottom, DiscountRowOffset = 2 + ReportAppendRowsAtBottom, OldBalanceRowOffset = 3 + ReportAppendRowsAtBottom, TotalCostRowOffset = 4 + ReportAppendRowsAtBottom;
#region Print Invoice Header
xlRange = xlWorkSheet.Cells[1 + InvoiceHeaderStartRow, 1];
Int32 CustDetailsStartRow = 1 + InvoiceHeaderStartRow;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum];
xlRange.Value = "Name";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum + 1].Value = ObjSellerDetails.Name;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum];
xlRange.Value = "Address";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1].Value = ObjSellerDetails.Address;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1];
xlRange.WrapText = true;
if (ObjSellerDetails.Address.Length >= 25)
{
xlRange.EntireColumn.ColumnWidth = 25;
}
if (CurrReportSettings.Type == ReportType.INVOICE)
{
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "TIN#";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = ObjSellerDetails.TINNumber;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum + 1].Value = ObjSellerDetails.Phone;
}
else
{
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = ObjSellerDetails.Phone;
}
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = "Date";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum].Value = DateTime.Today.ToString("dd-MMM-yyyy");
xlRange = xlWorkSheet.Cells[1 + CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = InvoiceNumberText;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, TotalColNum].Value = SerialNumber;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, 1], xlWorkSheet.Cells[CustDetailsStartRow + 3, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.Cells[InvoiceStartRow + 1, SlNoColNum].Value = "Sl No";
xlWorkSheet.Cells[InvoiceStartRow + 1, ItemNameColNum].Value = "Item Name";
xlWorkSheet.Cells[InvoiceStartRow + 1, OrdQtyColNum].Value = "Order Quantity";
xlWorkSheet.Cells[InvoiceStartRow + 1, SalQtyColNum].Value = "Sales Quantity";
xlWorkSheet.Cells[InvoiceStartRow + 1, PriceColNum].Value = "Price";
xlWorkSheet.Cells[InvoiceStartRow + 1, TotalColNum].Value = "Total";
xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow + 1, SlNoColNum], xlWorkSheet.Cells[InvoiceStartRow + 1, TotalColNum]].Font.Bold = true;
for (int i = 0; i < ListProducts.Count; i++)
{
ProductDetailsForInvoice CurrProd = ListProducts[i];
SlNo++;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SlNoColNum].Value = SlNo;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, ItemNameColNum].Value = CurrProd.Description;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, OrdQtyColNum].Value = CurrProd.OrderQuantity;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SalQtyColNum].Value = CurrProd.SaleQuantity;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum].Value = CurrProd.Rate * (1 + CurrProd.CGSTDetails.TaxRate + CurrProd.SGSTDetails.TaxRate);
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum].NumberFormat = "#,##0.00";
Excel.Range xlRangeSaleQty = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SalQtyColNum];
Excel.Range xlRangePrice = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum];
Excel.Range xlRangeTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, TotalColNum];
xlRangeTotal.Formula = "=(" + xlRangeSaleQty.Address[false, false] + "*" + xlRangePrice.Address[false, false] + ")";
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, TotalColNum].NumberFormat = "#,##0.00";
}
Excel.Range xlRangeSaleQtyFrom = xlWorkSheet.Cells[1 + InvoiceStartRow + 1, SalQtyColNum];
Excel.Range xlRangeSaleQtyTo = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset - 1, SalQtyColNum];
Excel.Range xlRangeTotalCost = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSaleTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
if (CurrReportSettings.Type == ReportType.INVOICE)
{
TotalSalesValue = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
}
else if (CurrReportSettings.Type == ReportType.QUOTATION)
{
TotalSalesValue = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
}
#region Sales Total Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum - 1];
xlRange.Value = "Sales Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotalFrom = xlWorkSheet.Cells[1 + InvoiceStartRow + 1, TotalColNum];
Excel.Range xlRangeSalesTotalTo = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset - 1, TotalColNum];
xlRange.Formula = "=Sum(" + xlRangeSalesTotalFrom.Address[false, false] + ":" + xlRangeSalesTotalTo.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
#region Discount Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum - 1];
xlRange.Value = "Discount";
xlRange.Font.Bold = true;
DiscountGroupDetails ObjDiscountGroup = CommonFunctions.ObjSellerMaster.GetSellerDiscount(ObjSellerDetails.Name);
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlSalesTotal1 = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
if (ObjDiscountGroup.DiscountType == DiscountTypes.PERCENT)
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false] + "*" + ObjDiscountGroup.Discount + "/100";
}
else if (ObjDiscountGroup.DiscountType == DiscountTypes.ABSOLUTE)
{
xlRange.Value = ObjDiscountGroup.Discount;
}
else
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false];
}
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
TotalDiscount = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
if (PrintOldBalance)
{
#region Old Balance Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "Old Balance";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Value = ObjSellerDetails.OldBalance;
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
if (CurrReportSettings.Type == ReportType.INVOICE)
{
#region VAT Percent Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "VAT Percent " + CurrReportSettings.VATPercent + "%";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
Excel.Range xlSalesTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlDiscount = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
xlRange.Formula = "=(" + xlSalesTotal.Address[false, false] + "-" + xlDiscount.Address[false, false] + ")*" + CurrReportSettings.VATPercent + "/100";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
TotalTax = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
#region Total Cost Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum - 1];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlRangeDiscount = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlRangeOldBalance = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Formula = "=Round(" + xlRangeSalesTotal.Address[false, false] + "+" + xlRangeOldBalance.Address[false, false] + "-" + xlRangeDiscount.Address[false, false] + ", 0)";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow + 1, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlWorkSheet, CurrReportSettings.HeaderSubTitle, CurrReportSettings);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("InvoiceVAT.CreateInvoice()", ex);
throw;
}
}
private void CreateSellerReport()
{
Excel.Application xlApp = new Excel.Application();
try
{
ReportProgressFunc(0);
if (String.IsNullOrEmpty(SellerHistoryFilePath))
{
MessageBox.Show(this, "Please select Seller History file!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select Seller History file";
return;
}
if (String.IsNullOrEmpty(SaveFolderPath))
{
MessageBox.Show(this, "Please select Save Folder path!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select Save folder";
return;
}
if (cmbBoxSellerList.SelectedItem == null || String.IsNullOrEmpty(cmbBoxSellerList.SelectedItem.ToString()))
{
MessageBox.Show(this, "Please select valid Seller name from list!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select valid Seller";
return;
}
String SelectedSellerName = cmbBoxSellerList.SelectedItem.ToString().Trim();
if (ListSellerNames.FindIndex(e => e.Trim().Equals(SelectedSellerName, StringComparison.InvariantCultureIgnoreCase)) < 0)
{
MessageBox.Show(this, "Unable to find the Seller!!!\nPlease select valid Seller name from list.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select valid Seller name from list";
return;
}
ReportProgressFunc(1);
lblStatus.Text = "Reading Seller History.....";
DataTable dtSellerSummary = CommonFunctions.ReturnDataTableFromExcelWorksheet("Seller History", SellerHistoryFilePath, "*");
if (dtSellerSummary == null)
{
MessageBox.Show(this, "Provided Seller History file doesn't contain \"Seller History\" Sheet.\nPlease provide correct file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please provide file with \"Seller History\" sheet";
return;
}
ReportProgressFunc(10);
dtSellerSummary.CaseSensitive = false;
dtSellerSummary.DefaultView.RowFilter = "Trim([Seller Name]) = '" + SelectedSellerName + "'";
if (chkBoxDateFilter.Checked)
{
dtSellerSummary.DefaultView.RowFilter += " and [Create Date] >= '" + dateTimePickerStart.Value.ToString("dd-MMM-yyyy")
+ "' and [Create Date] <= '" + dateTimePickerEnd.Value.ToString("dd-MMM-yyyy") + "'";
}
lblStatus.Text = "Filtering Seller History.....";
DataRow[] drSellerRecords = dtSellerSummary.DefaultView.ToTable().Select("", "[Create Date] asc, [Bill#] asc");
if (drSellerRecords == null || drSellerRecords.Length == 0)
{
MessageBox.Show(this, "No data found for the given filters in Seller History.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
lblStatus.Text = "No data found";
return;
}
//Create Date Update Date Bill# Seller Name Sale Cancel Return Discount Total Tax Net Sale OB Cash Balance
Int32 ProgressBarCount = drSellerRecords.Length;
lblStatus.Text = "Creating Seller Report.....";
Excel.Workbook xlSellerReportWorkbook = xlApp.Workbooks.Add();
Excel.Worksheet xlSellerReportWorksheet = xlSellerReportWorkbook.Worksheets[1];
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlSellerReportWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerReportWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
Int32 StartRow = 1, StartCol = 1, LastCol = 0, SellerNameColIndex = -1;
String SheetName = SelectedSellerName.Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "").
Replace("<", "").Replace(">", "").
Replace("|", "").Replace("\"", "");
xlSellerReportWorksheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
for (int i = 0; i < dtSellerSummary.Columns.Count; i++)
{
if (dtSellerSummary.Columns[i].ColumnName.Equals("Seller Name"))
{
SellerNameColIndex = i;
continue;
}
xlSellerReportWorksheet.Cells[StartRow, StartCol + LastCol].Value = dtSellerSummary.Columns[i].ColumnName;
LastCol++;
}
Excel.Range xlRange = xlSellerReportWorksheet.Range[xlSellerReportWorksheet.Cells[StartRow, StartCol], xlSellerReportWorksheet.Cells[StartRow, LastCol]];
xlRange.Font.Bold = true;
for (int i = 0; i < drSellerRecords.Length; i++)
{
DataRow dtRow = drSellerRecords[i];
for (int j = 0, k = 0; j < dtRow.ItemArray.Length; j++)
{
if (j == SellerNameColIndex)
{
continue;
}
xlSellerReportWorksheet.Cells[StartRow + 1 + i, StartCol + k].Value = dtRow[j];
if (k >= 3)
{
xlSellerReportWorksheet.Cells[StartRow + 1 + i, StartCol + k].NumberFormat = "#,##0.00";
}
k++;
}
ReportProgressFunc(10 + ((i + 1) * 90 / ProgressBarCount));
}
xlRange = xlSellerReportWorksheet.Range[xlSellerReportWorksheet.Cells[StartRow, StartCol], xlSellerReportWorksheet.Cells[StartRow + drSellerRecords.Length, LastCol]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlSellerReportWorksheet.UsedRange.Columns.AutoFit();
xlSellerReportWorkbook.SaveAs(SaveFolderPath + @"\SellerReport_" + SheetName.Replace(" ", "_") + ".xlsx");
xlSellerReportWorkbook.Close();
ReportProgressFunc(100);
MessageBox.Show(this, "Created Seller Report for \"" + SelectedSellerName + "\"", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information);
lblStatus.Text = "Created Seller Report";
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("SellerHistoryReportForm.CreateSellerReport()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
public void UpdateProductStockHistoryFile(Excel.Application xlApp, DateTime SummaryCreationDate, String TransactionType, String ProductStockHistoryFile)
{
try
{
Excel.Workbook xlProductStockHistory;
Excel.Worksheet xlStockHistoryWorksheet;
String[] Header = new String[] { "PO Date", "Update Date", "Type", "Stock Name", "Order Qty", "Receive Qty", "Net Qty", "Total Cost", "Total Discount", "Total Tax", "Net Cost" };
if (!File.Exists(ProductStockHistoryFile))
{
xlProductStockHistory = xlApp.Workbooks.Add();
xlStockHistoryWorksheet = xlProductStockHistory.Worksheets.Add();
xlStockHistoryWorksheet.Name = "Stock History";
for (int i = 0; i < Header.Length; i++)
{
xlStockHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlStockHistoryWorksheet.Range[xlStockHistoryWorksheet.Cells[1, 1], xlStockHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlProductStockHistory.SaveAs(ProductStockHistoryFile);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
}
else
{
xlProductStockHistory = xlApp.Workbooks.Open(ProductStockHistoryFile);
xlStockHistoryWorksheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Stock History");
}
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
Int32 RowCount = xlStockHistoryWorksheet.UsedRange.Rows.Count, ColumnCount = xlStockHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount + 1, PODateColPos = 1, UpdateDateColPos = 2, TypeColPos = 3, StockNameColPos = 4,
OrderQtyColPos = 5, ReceiveQtyColPos = 6, NetQtyColPos = 7, TotalCostColPos = 8, TotalDiscountColPos = 9,
TotalTaxColPos = 10, NetCostColPos = 11;
//PO Date\tUpdate Date\tType\tStock Name\tOrder Qty\tReceive Qty\tNet Qty\tTotal Cost\tTotal Discount\tTotal Tax\tNet Cost
for (int i = 0; i < ColumnCount; i++)
{
if (xlStockHistoryWorksheet.Cells[1, 1 + i].Value == null)
{
break;
}
String ColName = xlStockHistoryWorksheet.Cells[1, 1 + i].Value.ToString().Trim().ToUpper();
switch (ColName)
{
case "PO DATE": PODateColPos = i + 1; break;
case "UPDATE DATE": UpdateDateColPos = i + 1; break;
case "TYPE": TypeColPos = i + 1; break;
case "STOCK NAME": StockNameColPos = i + 1; break;
case "ORDER QTY": OrderQtyColPos = i + 1; break;
case "RECEIVE QTY": ReceiveQtyColPos = i + 1; break;
case "NET QTY": NetQtyColPos = i + 1; break;
case "TOTAL COST": TotalCostColPos = i + 1; break;
case "TOTAL DISCOUNT": TotalDiscountColPos = i + 1; break;
case "TOTAL TAX": TotalTaxColPos = i + 1; break;
case "NET COST": NetCostColPos = i + 1; break;
default: break;
}
}
Int32 StockCounter = 0;
for (int i = 0, j = 0; i < ObjProductMaster.ListStockProducts.Count; i++)
{
StockProductDetails ObjStockProduct = ObjProductMaster.ListStockProducts[i];
if (!ObjStockProduct.IsUpdated)
{
continue;
}
xlStockHistoryWorksheet.Cells[StartRow + j, PODateColPos].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlStockHistoryWorksheet.Cells[StartRow + j, UpdateDateColPos].Value = DateTime.Now.Date.ToString("dd-MMM-yyyy");
xlStockHistoryWorksheet.Cells[StartRow + j, TypeColPos].Value = TransactionType;
xlStockHistoryWorksheet.Cells[StartRow + j, StockNameColPos].Value = ObjStockProduct.StockName;
xlStockHistoryWorksheet.Cells[StartRow + j, OrderQtyColPos].Value = ObjStockProduct.OrderQty;
xlStockHistoryWorksheet.Cells[StartRow + j, ReceiveQtyColPos].Value = ObjStockProduct.RecvdQty;
xlStockHistoryWorksheet.Cells[StartRow + j, NetQtyColPos].Value = ObjStockProduct.NetQty;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalCostColPos].Value = ObjStockProduct.TotalCost;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalDiscountColPos].Value = ObjStockProduct.TotalDiscount;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalTaxColPos].Value = ObjStockProduct.TotalTax;
xlStockHistoryWorksheet.Cells[StartRow + j, NetCostColPos].Value = ObjStockProduct.NetCost;
StockCounter = j;
j++;
}
Excel.Range xlRange = xlStockHistoryWorksheet.Range[xlStockHistoryWorksheet.Cells[StartRow, PODateColPos], xlStockHistoryWorksheet.Cells[StartRow + StockCounter, NetCostColPos]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlProductStockHistory.Save();
xlProductStockHistory.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("ProductMaster.UpdateProductStockHistoryFile()", ex);
throw;
}
}