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