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 CreateItemSummarySheet(DataRow[] drItems, Excel.Workbook xlWorkbook, ReportSettings CurrReportSettings)
{
try
{
lblStatus.Text = "Creating Item Summary Sheet";
#region Print Item Summary Sheet
Int32 SummaryStartRow = 0;
Double Total = 0;
Excel.Worksheet xlSummaryWorkSheet = xlWorkbook.Worksheets.Add(xlWorkbook.Sheets[1]);
xlSummaryWorkSheet.Name = "Item Summary";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 1].Value = "Sl.No.";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 2].Value = "Stock Name";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 3].Value = "Vendor Name";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 4].Value = "Quantity";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 5].Value = "Price";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 6].Value = "Total";
Excel.Range xlRange1 = xlSummaryWorkSheet.Range[xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 1], xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 6]];
xlRange1.Font.Bold = true;
HashSet <String> ListItems = new HashSet <String>();
for (int i = 0, j = 0; i < drItems.Length; i++)
{
if (ListItems.Contains(drItems[i]["StockName"].ToString()))
{
continue;
}
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 1].Value = (j + 1);
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 2].Value = drItems[i]["StockName"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 3].Value = drItems[i]["VendorName"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 4].Value = drItems[i]["Quantity"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 5].Value = drItems[i]["PurchasePrice"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 5].NumberFormat = "#,##0.00";
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].Value = Double.Parse(drItems[i]["Quantity"].ToString()) * Double.Parse(drItems[i]["PurchasePrice"].ToString());
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].NumberFormat = "#,##0.00";
Total += Double.Parse(xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].Value.ToString());
j++;
ListItems.Add(drItems[i]["StockName"].ToString());
}
Excel.Range tmpxlRange = xlSummaryWorkSheet.Cells[drItems.Length + SummaryStartRow + 2, 5];
tmpxlRange.Value = "Total";
tmpxlRange.Font.Bold = true;
tmpxlRange = xlSummaryWorkSheet.Cells[drItems.Length + SummaryStartRow + 2, 6];
tmpxlRange.Value = Total;
tmpxlRange.Font.Bold = true;
tmpxlRange.NumberFormat = "#,##0.00";
xlSummaryWorkSheet.UsedRange.Columns.AutoFit();
xlApp.DisplayAlerts = false;
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlSummaryWorkSheet, "Itemwise Summary", CurrReportSettings);
xlApp.DisplayAlerts = true;
#endregion
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateItemSummarySheet()", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
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 CreateVendorOrderSheet()
{
Excel.Application xlApp = new Excel.Application();
try
{
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
ObjProductMaster.ResetStockProducts();
lblStatus.Text = "Loading Product Inventory file";
DataTable dtProductInventory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFile, "*");
if (dtProductInventory == null)
{
MessageBox.Show(this, "Product Inventory does not contain \"Inventory\" sheet", "Create Vendor Order Sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
ObjProductMaster.LoadProductInventoryFile(drProductsInventory);
lblStatus.Text = "Loading Product Sales from Stock History file";
DataTable dtProductStockHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Stock History", ProductStockHistoryFile, "[PO Date], [Type], [Stock Name], [Receive Qty]");
if (dtProductStockHistory == null)
{
MessageBox.Show(this, "Product Stock History does not contain \"Stock History\" sheet", "Create Vendor Order Sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ObjProductMaster.LoadProductPastSalesFromStockHistoryFile(dtProductStockHistory, DateTime.Today, (Int32)numUpDownTimePeriod.Value, ReportSettings.GetTimePeriodUnits(cmbBoxTimePeriodUnits.SelectedItem.ToString()));
lblStatus.Text = "Creating Vendor Order Sheet";
DataTable dtItemMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("ItemMaster", MasterFilePath, "*");
DataTable dtVendorMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("VendorMaster", MasterFilePath, "*");
List <String> ListVendors = dtItemMaster.AsEnumerable().Select(s => s.Field <String>("VendorName")).Distinct().ToList();
DataRow[] drItems = dtItemMaster.Select("", "SlNo asc");
Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();
Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets.Add();
xlWorkSheet.Name = dateTimePickerVendorOrderDate.Value.ToString("dd-MM-yyyy");
#region Print Header
List <String> HeaderItems = new List <String>();
HeaderItems.Add("Sl.No.");
HeaderItems.Add("Total Items");
HeaderItems.Add("Name");
HeaderItems.Add("Line");
HeaderItems.Add("Contact Details");
Int32 ProgressBarCount = HeaderItems.Count + (drItems.Length * 2) + dtVendorMaster.Rows.Count;
Int32 StartRow = 7, StartCol = 1, Counter = 0;
for (int i = 0; i < HeaderItems.Count; i++)
{
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + i];
xlRange.Value = HeaderItems[i];
if (!(HeaderItems[i].Equals("Name") || HeaderItems[i].Equals("Contact Details") || HeaderItems[i].Equals("Line")))
{
xlRange.Orientation = 90;
}
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
Dictionary <String, DataRow> ListProducts = new Dictionary <String, DataRow>();
for (int i = 0, j = 0; i < drItems.Length; i++)
{
if (ListProducts.ContainsKey(drItems[i]["StockName"].ToString().Trim().ToUpper()))
{
continue;
}
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + HeaderItems.Count + j];
xlRange.Value = drItems[i]["StockName"].ToString();
xlRange.Orientation = 90;
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
Counter++;
j++;
ListProducts.Add(drItems[i]["StockName"].ToString().Trim().ToUpper(), drItems[i]);
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Vendors
DataRow[] drVendors = dtVendorMaster.Select("", "SlNo asc");
for (int i = 0; i < drVendors.Length; i++)
{
xlWorkSheet.Cells[StartRow + i + 1, StartCol].Value = (i + 1);
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count + ListProducts.Count - 1];
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 1].Formula = "=Count(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 2].Value = drVendors[i]["VendorName"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 3].Value = drVendors[i]["Line"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 4].Value = ((drVendors[i]["Phone"] == DBNull.Value) ? "" : drVendors[i]["Phone"].ToString());
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Price, Last N Day Sale, Current stock & TotalQuantity
Int32 PriceRowNum = StartRow - 5, PastSaleRowNum = StartRow - 4, CurrStockRowNum = StartRow - 3, TotalQtyRowNum = StartRow - 2;
xlWorkSheet.Cells[PriceRowNum, StartCol + 2].Value = "Price";
xlWorkSheet.Cells[PastSaleRowNum, StartCol + 2].Value = "Last " + (Int32)numUpDownTimePeriod.Value + " " + cmbBoxTimePeriodUnits.SelectedItem + " of Sales";
xlWorkSheet.Cells[CurrStockRowNum, StartCol + 2].Value = "Current Stock";
Excel.Range tmpxlRange = xlWorkSheet.Cells[TotalQtyRowNum, StartCol + 2];
tmpxlRange.Value = "Total Quantity";
tmpxlRange.Font.Bold = true;
tmpxlRange.Interior.Color = Color.FromArgb(141, 180, 226);
for (int i = 0; i < HeaderItems.Count; i++)
{
xlWorkSheet.Cells[TotalQtyRowNum, StartCol + i].Interior.Color = Color.FromArgb(141, 180, 226);
}
Int32 ProductCount = 0;
foreach (KeyValuePair <String, DataRow> item in ListProducts)
{
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + 1, StartCol + HeaderItems.Count + ProductCount];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + drVendors.Length, StartCol + HeaderItems.Count + ProductCount];
Excel.Range xlRange = xlWorkSheet.Cells[TotalQtyRowNum, StartCol + HeaderItems.Count + ProductCount];
xlRange.Formula = "=Sum(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(141, 180, 226);
xlWorkSheet.Cells[PriceRowNum, StartCol + HeaderItems.Count + ProductCount].Value = item.Value["PurchasePrice"].ToString();
StockProductDetails ObjStockProductDetails = ObjProductMaster.GetStockProductDetails(item.Key.Trim());
if (ObjStockProductDetails != null)
{
xlWorkSheet.Cells[PastSaleRowNum, StartCol + HeaderItems.Count + ProductCount].Value = ObjStockProductDetails.SaleQty;
xlWorkSheet.Cells[CurrStockRowNum, StartCol + HeaderItems.Count + ProductCount].Value = ObjStockProductDetails.Inventory;
}
Counter++;
ProductCount++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
ReportProgressFunc(((ProgressBarCount - 1) * 100) / ProgressBarCount);
xlWorkbook.SaveAs(txtBoxSaveFolderPath.Text + "\\VendorOrder_" + xlWorkSheet.Name + ".xlsx");
xlWorkbook.Close();
CommonFunctions.ReleaseCOMObject(xlWorkbook);
ReportProgressFunc(100);
lblStatus.Text = "Completed creation of Vendor Order Sheet";
MessageBox.Show(this, "Created Vendor Order Sheet Successfully", "Status", MessageBoxButtons.OK);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorOrderSheetForm.CreateVendorOrderSheet()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
void LoadSettings()
{
try
{
//Load General Settings from CommonFunctions Module
ddlSummaryLocation.Items.Clear();
ddlSummaryLocation.Items.Add("Invoice");
ddlSummaryLocation.Items.Add("Quotation");
ddlSummaryLocation.SelectedIndex = CommonFunctions.ObjGeneralSettings.SummaryLocation;
//Load Invoice Settings from CommonFunctions Module
ReportSettings CurrSettings = CommonFunctions.ObjInvoiceSettings;
txtBoxHeaderTitleInv.Text = CurrSettings.HeaderTitle;
txtBoxHeaderSubTitleInv.Text = CurrSettings.HeaderSubTitle;
txtBoxHeaderTitleColorInv.BackColor = CurrSettings.HeaderTitleColor;
txtBoxHeaderSubTitleColorInv.BackColor = CurrSettings.HeaderSubTitleColor;
txtBoxFooterTitleInv.Text = CurrSettings.FooterTitle;
txtBoxFooterTitleColorInv.BackColor = CurrSettings.FooterTitleColor;
txtBoxFooterTextColorInv.BackColor = CurrSettings.FooterTextColor;
txtBoxAddressInv.Text = CurrSettings.Address;
txtBoxPhoneNumberInv.Text = CurrSettings.PhoneNumber;
txtBoxEMailIDInv.Text = CurrSettings.EMailID;
txtBoxVATPercentInv.Text = CurrSettings.VATPercent;
txtBoxTINNumberInv.Text = CurrSettings.TINNumber;
txtBoxGSTINumberInv.Text = CurrSettings.GSTINumber;
txtBoxLastInvoiceNumberInv.Text = CurrSettings.LastNumber.ToString();
//Load Quotation Settings from CommonFunctions Module
CurrSettings = CommonFunctions.ObjQuotationSettings;
txtBoxHeaderTitleQuot.Text = CurrSettings.HeaderTitle;
txtBoxHeaderSubTitleQuot.Text = CurrSettings.HeaderSubTitle;
txtBoxHeaderTitleColorQuot.BackColor = CurrSettings.HeaderTitleColor;
txtBoxHeaderSubTitleColorQuot.BackColor = CurrSettings.HeaderSubTitleColor;
txtBoxFooterTitleQuot.Text = CurrSettings.FooterTitle;
txtBoxFooterTitleColorQuot.BackColor = CurrSettings.FooterTitleColor;
txtBoxFooterTextColorQuot.BackColor = CurrSettings.FooterTextColor;
txtBoxAddressQuot.Text = CurrSettings.Address;
txtBoxPhoneNumberQuot.Text = CurrSettings.PhoneNumber;
txtBoxEMailIDQuot.Text = CurrSettings.EMailID;
txtBoxTINNumberQuot.Text = CurrSettings.TINNumber;
txtBoxGSTINumberQuot.Text = CurrSettings.GSTINumber;
txtBoxLastQuotationNumberQuot.Text = CurrSettings.LastNumber.ToString();
//Load Purchase Order Settings from CommonFunctions Module
CurrSettings = CommonFunctions.ObjPurchaseOrderSettings;
txtBoxHeaderTitlePO.Text = CurrSettings.HeaderTitle;
txtBoxHeaderSubTitlePO.Text = CurrSettings.HeaderSubTitle;
txtBoxHeaderTitleColorPO.BackColor = CurrSettings.HeaderTitleColor;
txtBoxHeaderSubTitleColorPO.BackColor = CurrSettings.HeaderSubTitleColor;
txtBoxFooterTitlePO.Text = CurrSettings.FooterTitle;
txtBoxFooterTitleColorPO.BackColor = CurrSettings.FooterTitleColor;
txtBoxFooterTextColorPO.BackColor = CurrSettings.FooterTextColor;
txtBoxAddressPO.Text = CurrSettings.Address;
txtBoxPhoneNumberPO.Text = CurrSettings.PhoneNumber;
txtBoxEMailIDPO.Text = CurrSettings.EMailID;
txtBoxVATPercentPO.Text = CurrSettings.VATPercent;
txtBoxTINNumberPO.Text = CurrSettings.TINNumber;
txtBoxGSTINumberPO.Text = CurrSettings.GSTINumber;
txtBoxLastPONumber.Text = CurrSettings.LastNumber.ToString();
numUpDownPeriodValue.Value = CurrSettings.PastSalePeriodValue;
cmbBoxPeriodUnits.SelectedIndex = (Int32)CurrSettings.PastSalePeriodUnits;
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("SettingsForm.LoadSettings", ex);
}
}
private void btnApplySettings_Click(object sender, EventArgs e)
{
try
{
//Apply General Settings to CommonFunctions Module
CommonFunctions.ObjGeneralSettings.SummaryLocation = ddlSummaryLocation.SelectedIndex;
//Apply Invoice Settings to CommonFunctions Module
ReportSettings CurrSettings = CommonFunctions.ObjInvoiceSettings;
CurrSettings.HeaderTitle = txtBoxHeaderTitleInv.Text;
CurrSettings.HeaderSubTitle = txtBoxHeaderSubTitleInv.Text;
CurrSettings.HeaderTitleColor = txtBoxHeaderTitleColorInv.BackColor;
CurrSettings.HeaderSubTitleColor = txtBoxHeaderSubTitleColorInv.BackColor;
CurrSettings.FooterTitle = txtBoxFooterTitleInv.Text;
CurrSettings.FooterTitleColor = txtBoxFooterTitleColorInv.BackColor;
CurrSettings.FooterTextColor = txtBoxFooterTextColorInv.BackColor;
CurrSettings.Address = txtBoxAddressInv.Text;
CurrSettings.PhoneNumber = txtBoxPhoneNumberInv.Text;
CurrSettings.EMailID = txtBoxEMailIDInv.Text;
CurrSettings.VATPercent = txtBoxVATPercentInv.Text;
CurrSettings.TINNumber = txtBoxTINNumberInv.Text;
CurrSettings.GSTINumber = txtBoxGSTINumberInv.Text;
CurrSettings.LastNumber = Int32.Parse(txtBoxLastInvoiceNumberInv.Text);
//Apply Quotation Settings to CommonFunctions Module
CurrSettings = CommonFunctions.ObjQuotationSettings;
CurrSettings.HeaderTitle = txtBoxHeaderTitleQuot.Text;
CurrSettings.HeaderSubTitle = txtBoxHeaderSubTitleQuot.Text;
CurrSettings.HeaderTitleColor = txtBoxHeaderTitleColorQuot.BackColor;
CurrSettings.HeaderSubTitleColor = txtBoxHeaderSubTitleColorQuot.BackColor;
CurrSettings.FooterTitle = txtBoxFooterTitleQuot.Text;
CurrSettings.FooterTitleColor = txtBoxFooterTitleColorQuot.BackColor;
CurrSettings.FooterTextColor = txtBoxFooterTextColorQuot.BackColor;
CurrSettings.Address = txtBoxAddressQuot.Text;
CurrSettings.PhoneNumber = txtBoxPhoneNumberQuot.Text;
CurrSettings.EMailID = txtBoxEMailIDQuot.Text;
CurrSettings.TINNumber = txtBoxTINNumberQuot.Text;
CurrSettings.GSTINumber = txtBoxGSTINumberQuot.Text;
CurrSettings.LastNumber = Int32.Parse(txtBoxLastQuotationNumberQuot.Text);
//Apply Purchase Order Settings to CommonFunctions Module
CurrSettings = CommonFunctions.ObjPurchaseOrderSettings;
CurrSettings.HeaderTitle = txtBoxHeaderTitlePO.Text;
CurrSettings.HeaderSubTitle = txtBoxHeaderSubTitlePO.Text;
CurrSettings.HeaderTitleColor = txtBoxHeaderTitleColorPO.BackColor;
CurrSettings.HeaderSubTitleColor = txtBoxHeaderSubTitleColorPO.BackColor;
CurrSettings.FooterTitle = txtBoxFooterTitlePO.Text;
CurrSettings.FooterTitleColor = txtBoxFooterTitleColorPO.BackColor;
CurrSettings.FooterTextColor = txtBoxFooterTextColorPO.BackColor;
CurrSettings.Address = txtBoxAddressPO.Text;
CurrSettings.PhoneNumber = txtBoxPhoneNumberPO.Text;
CurrSettings.EMailID = txtBoxEMailIDPO.Text;
CurrSettings.VATPercent = txtBoxVATPercentPO.Text;
CurrSettings.TINNumber = txtBoxTINNumberPO.Text;
CurrSettings.GSTINumber = txtBoxGSTINumberPO.Text;
CurrSettings.LastNumber = Int32.Parse(txtBoxLastPONumber.Text);
CurrSettings.PastSalePeriodValue = (Int32)numUpDownPeriodValue.Value;
CurrSettings.PastSalePeriodUnits = ReportSettings.GetTimePeriodUnits(cmbBoxPeriodUnits.SelectedItem.ToString());
CommonFunctions.WriteToSettingsFile(); //Save to Settings.xml file
MessageBox.Show(this, "Settings Updated Successfully", "Settings", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("SettingsForm.btnApplySettings_Click", ex);
throw;
}
}
