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