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