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