public InvoiceCompleted(int invoiceID) { InitializeComponent(); invoice = new Invoice(invoiceID); invoiceContentsList = InvoiceContentsDatabase.GetInvoiceContents(invoiceID); this.customerID = invoice.customer.StoreID; Customer c = CustomerDatabase.SearchCustomersByID(customerID); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(c.Province); if (provinceTax.pst == 0) { PST = false; } else { PST = true; } panel1.Location = new Point(30, 135); panel1.Size = new Size(900, 360); panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle; panel1.AutoScroll = true; panel1.BackColor = Color.DarkGray; this.Controls.Add(panel1); AddLabels(customerID); AddTotalBoxes(customerID); AddItemBoxes(); }
public InvoiceForm(int customerID) { InitializeComponent(); finished = false; this.customerID = customerID; customer = CustomerDatabase.SearchCustomersByID(customerID); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(customer.Province); if (provinceTax.pst == 0) { PST = false; } else { PST = true; } panel1.Location = new Point(30, 145); panel1.Size = new Size(800, 330); panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle; panel1.AutoScroll = true; panel1.BackColor = Color.DarkGray; this.Controls.Add(panel1); AddLabels(customerID); AddTotalBoxes(customerID); AddFirstRow(); }
private void SubtotalAmount_TextChanged(object sender, EventArgs e) { Customer c = CustomerDatabase.SearchCustomersByID(customerID); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(c.Province); float gstRate = (float)provinceTax.gst / 100; this.Controls["gst"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * gstRate).ToString("0.00"); this.Controls["invoiceTotal"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * (1 + gstRate)).ToString("0.00"); if (PST) { float pstRate = (float)provinceTax.pst / 100; this.Controls["pst"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * pstRate).ToString("0.00"); this.Controls["invoiceTotal"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * (1 + gstRate + pstRate)).ToString("0.00"); } }
private void AddLabels(int customerID) { int x = 30; int y = 120; Customer cust = CustomerDatabase.SearchCustomersByID(customerID); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(cust.Province); //customer labels Label storeNameLabel = new Label(); if (cust.StoreDetails.Length != 0) { storeNameLabel.Text = "Store name: " + cust.StoreName + " - " + cust.StoreDetails; } else { storeNameLabel.Text = "Store name: " + cust.StoreName; } storeNameLabel.Location = new Point(30, 10); storeNameLabel.AutoSize = true; this.Controls.Add(storeNameLabel); Label officeLabel = new Label(); officeLabel.Text = "Billing Address: " + cust.BillingAddress; officeLabel.Location = new Point(30, 25); officeLabel.AutoSize = true; this.Controls.Add(officeLabel); Label shippingLabel = new Label(); shippingLabel.Text = "Shipping Address: " + cust.ShippingAddress; shippingLabel.Location = new Point(30, 40); shippingLabel.AutoSize = true; this.Controls.Add(shippingLabel); Label contactLabel = new Label(); contactLabel.Text = "Store Contact: " + cust.StoreContact; contactLabel.Location = new Point(30, 55); contactLabel.AutoSize = true; this.Controls.Add(contactLabel); Label emailLabel = new Label(); emailLabel.Text = "Email: " + cust.Email; emailLabel.Location = new Point(30, 70); emailLabel.AutoSize = true; this.Controls.Add(emailLabel); Label phoneLabel = new Label(); phoneLabel.Text = "Phone: " + cust.PhoneNumber; phoneLabel.Location = new Point(500, 10); phoneLabel.AutoSize = true; this.Controls.Add(phoneLabel); Label provinceLabel = new Label(); provinceLabel.Text = "Province Tax: " + cust.Province + " - GST/PST(" + provinceTax.gst + "%/" + provinceTax.pst + "%)"; provinceLabel.Location = new Point(500, 25); provinceLabel.AutoSize = true; this.Controls.Add(provinceLabel); Label paymentLabel = new Label(); paymentLabel.Text = "Payment Terms: " + cust.PaymentTerms; paymentLabel.Location = new Point(500, 40); paymentLabel.AutoSize = true; this.Controls.Add(paymentLabel); Label shippingInstructionsLabel = new Label(); shippingInstructionsLabel.Text = "Shipping Instructions: " + cust.ShippingInstructions; shippingInstructionsLabel.Location = new Point(500, 55); shippingInstructionsLabel.AutoSize = true; this.Controls.Add(shippingInstructionsLabel); Label invoiceIDLabel = new Label(); invoiceIDLabel.Text = "Local Invoice ID: " + invoice.InvoiceID; invoiceIDLabel.Location = new Point(500, 70); invoiceIDLabel.AutoSize = true; this.Controls.Add(invoiceIDLabel); Label purchaseOrderLabel = new Label(); purchaseOrderLabel.Text = "PO#:" + invoice.PurchaseOrder; purchaseOrderLabel.Location = new Point(30, 85); purchaseOrderLabel.AutoSize = true; this.Controls.Add(purchaseOrderLabel); Label invoiceSpecialNotesLabel = new Label(); invoiceSpecialNotesLabel.Text = "Special Notes: " + invoice.SpecialNotes; invoiceSpecialNotesLabel.Location = new Point(180, 85); invoiceSpecialNotesLabel.AutoSize = true; this.Controls.Add(invoiceSpecialNotesLabel); Label invoiceNumberLabel = new Label(); invoiceNumberLabel.Text = "Invoice #: " + invoice.InvoiceNo; invoiceNumberLabel.Location = new Point(700, 10); invoiceNumberLabel.AutoSize = true; this.Controls.Add(invoiceNumberLabel); Label backorderInvoiceNotesLabel = new Label(); backorderInvoiceNotesLabel.Text = "Backorder Invoice Notes: " + invoice.BackorderNotes; backorderInvoiceNotesLabel.Location = new Point(30, 100); backorderInvoiceNotesLabel.AutoSize = true; this.Controls.Add(backorderInvoiceNotesLabel); //Invoice column headers Label qtyLabel = new Label(); qtyLabel.Text = "Qty"; qtyLabel.Location = new Point(x, y); qtyLabel.AutoSize = true; qtyLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(qtyLabel); Label itemNoLabel = new Label(); itemNoLabel.Text = "Item Number"; itemNoLabel.Location = new Point(x + 50, y); itemNoLabel.AutoSize = true; itemNoLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(itemNoLabel); Label locLabel = new Label(); locLabel.Text = "Location"; locLabel.Location = new Point(x + 170, y); locLabel.AutoSize = true; locLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(locLabel); Label descLabel = new Label(); descLabel.Text = "Description"; descLabel.Location = new Point(x + 240, y); descLabel.AutoSize = true; descLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(descLabel); Label cartonLabel = new Label(); cartonLabel.Text = "Pack"; cartonLabel.Location = new Point(x + 460, y); cartonLabel.AutoSize = true; cartonLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(cartonLabel); Label costLabel = new Label(); costLabel.Text = "Cost"; costLabel.Location = new Point(x + 510, y); costLabel.AutoSize = true; costLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(costLabel); Label amountLabel = new Label(); amountLabel.Text = "Amount"; amountLabel.Location = new Point(x + 580, y); amountLabel.AutoSize = true; amountLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(amountLabel); Label specialNotesLabel = new Label(); specialNotesLabel.Text = "Special Notes"; specialNotesLabel.Location = new Point(x + 640, y); specialNotesLabel.AutoSize = true; specialNotesLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(specialNotesLabel); Label backorderLabel = new Label(); backorderLabel.Text = "B.O."; backorderLabel.Location = new Point(x + 790, y); backorderLabel.AutoSize = true; backorderLabel.BackColor = System.Drawing.Color.LightGray; this.Controls.Add(backorderLabel); Button cancelButton = new Button(); cancelButton.Location = new Point(720, 620); cancelButton.Size = new Size(50, 25); cancelButton.Text = "Cancel"; cancelButton.Click += CancelButton_Click; this.Controls.Add(cancelButton); Button okButton = new Button(); okButton.Location = new Point(780, 620); okButton.Size = new Size(50, 25); okButton.Text = "OK"; okButton.Click += OkButton_Click; this.Controls.Add(okButton); Button printButton = new Button(); printButton.Location = new Point(665, 620); printButton.Size = new Size(50, 25); printButton.Text = "Print"; printButton.Click += PrintButton_Click; this.Controls.Add(printButton); }
private void AddTotalBoxes(int customerID) { Customer cust = CustomerDatabase.SearchCustomersByID(customerID); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(cust.Province); Label subtotalLabel = new Label(); subtotalLabel.Text = "Subtotal"; subtotalLabel.Location = new Point(562, 500); subtotalLabel.AutoSize = true; this.Controls.Add(subtotalLabel); TextBox subtotalAmount = new TextBox(); subtotalAmount.Location = new Point(610, 500); subtotalAmount.Size = new Size(50, 25); subtotalAmount.ReadOnly = true; subtotalAmount.Name = "subtotalAmount"; subtotalAmount.AccessibleName = "subtotalAmount"; subtotalAmount.TextChanged += SubtotalAmount_TextChanged; this.Controls.Add(subtotalAmount); Label gstLabel = new Label(); gstLabel.Text = "GST " + provinceTax.gst + "%"; gstLabel.Location = new Point(560, 530); gstLabel.Size = new Size(50, 25); gstLabel.TextAlign = ContentAlignment.TopRight; this.Controls.Add(gstLabel); TextBox gst = new TextBox(); gst.Location = new Point(610, 530); gst.Size = new Size(50, 25); gst.ReadOnly = true; gst.Name = "gst"; gst.AccessibleName = "gst"; this.Controls.Add(gst); if (PST) { Label pstLabel = new Label(); pstLabel.Text = "PST " + provinceTax.pst + "%"; pstLabel.Location = new Point(560, 560); pstLabel.Size = new Size(50, 25); pstLabel.TextAlign = ContentAlignment.TopRight; this.Controls.Add(pstLabel); TextBox pst = new TextBox(); pst.Location = new Point(610, 560); pst.Size = new Size(50, 25); pst.ReadOnly = true; pst.Name = "pst"; pst.AccessibleName = "pst"; this.Controls.Add(pst); Label invoiceTotalLabel = new Label(); invoiceTotalLabel.Text = "Invoice Total"; invoiceTotalLabel.Location = new Point(540, 590); invoiceTotalLabel.AutoSize = true; this.Controls.Add(invoiceTotalLabel); TextBox invoiceTotal = new TextBox(); invoiceTotal.Location = new Point(610, 590); invoiceTotal.Size = new Size(50, 25); invoiceTotal.ReadOnly = true; invoiceTotal.Name = "invoiceTotal"; invoiceTotal.AccessibleName = "invoiceTotal"; this.Controls.Add(invoiceTotal); } else //no pst { Label invoiceTotalLabel = new Label(); invoiceTotalLabel.Text = "Invoice Total"; invoiceTotalLabel.Location = new Point(540, 560); invoiceTotalLabel.AutoSize = true; this.Controls.Add(invoiceTotalLabel); TextBox invoiceTotal = new TextBox(); invoiceTotal.Location = new Point(610, 560); invoiceTotal.Size = new Size(50, 25); invoiceTotal.ReadOnly = true; invoiceTotal.Name = "invoiceTotal"; invoiceTotal.AccessibleName = "invoiceTotal"; this.Controls.Add(invoiceTotal); } }
private void PrintInvoiceProgress_Load(object sender, EventArgs e) { // Creating File directory & save files Warning[] warnings; string[] streamids; string mimeType, encoding, extension; string ExcelFileName = @"C:\Invoices\Excel\" + _invoice.InvoiceID + ".xlsx"; string PDFFileName = @"C:\Invoices\PDF\" + _invoice.InvoiceID + ".pdf"; Directory.CreateDirectory(Path.GetDirectoryName(ExcelFileName)); Directory.CreateDirectory(Path.GetDirectoryName(PDFFileName)); //Init data source for tables InvoiceItemDetailBindingSource.DataSource = _list; String address = _invoice.CustomerAddress; String[] words = address.Split(','); String customerStreet = words[0].TrimStart(); String customerProvince = words[1].TrimStart(); String customerPostal = words[2].TrimStart(); ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(_invoice.customer.Province); // Create & Set report parameter data Microsoft.Reporting.WinForms.ReportParameter[] p = new Microsoft.Reporting.WinForms.ReportParameter[] { new Microsoft.Reporting.WinForms.ReportParameter("pCompanyName", _invoice.CompanyName), new Microsoft.Reporting.WinForms.ReportParameter("pCompanyAddress", _invoice.CompanyAddress), new Microsoft.Reporting.WinForms.ReportParameter("pCompanyPhoneNumber", _invoice.CompanyPhoneNumber), new Microsoft.Reporting.WinForms.ReportParameter("pCompanyFax", _invoice.CompanyFax), new Microsoft.Reporting.WinForms.ReportParameter("pCompanyTollFree", _invoice.CompanyTollFree), new Microsoft.Reporting.WinForms.ReportParameter("pInvoiceNumber", _invoice.InvoiceNo.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pInvoiceID", _invoice.InvoiceID.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pStoreName", _invoice.CustomerName), new Microsoft.Reporting.WinForms.ReportParameter("pStoreContact", _invoice.CustomerContact), new Microsoft.Reporting.WinForms.ReportParameter("pTerms", _invoice.CustomerTerms), new Microsoft.Reporting.WinForms.ReportParameter("pShippingTerms", _invoice.CustomerShippingTerms), new Microsoft.Reporting.WinForms.ReportParameter("pPurchaseOrder", _invoice.PurchaseOrder), new Microsoft.Reporting.WinForms.ReportParameter("pSpecialNotes", _invoice.SpecialNotes), new Microsoft.Reporting.WinForms.ReportParameter("pStoreStreet", customerStreet), new Microsoft.Reporting.WinForms.ReportParameter("pStoreProvince", customerProvince), new Microsoft.Reporting.WinForms.ReportParameter("pStorePostal", customerPostal), new Microsoft.Reporting.WinForms.ReportParameter("pStorePhone", _invoice.CustomerPhone), new Microsoft.Reporting.WinForms.ReportParameter("pSubtotal", _invoice.SubTotal.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pGSTPercent", provinceTax.gst.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pGST", _invoice.Gst.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pPSTPercent", provinceTax.pst.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pPST", _invoice.Pst.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pTotal", _invoice.NetTotal.ToString()), new Microsoft.Reporting.WinForms.ReportParameter("pGSTno", _invoice.GSTNo), new Microsoft.Reporting.WinForms.ReportParameter("pBackorderNotes", _invoice.BackorderNotes), new Microsoft.Reporting.WinForms.ReportParameter("pSalesRep", _invoice.customer.Rep), new Microsoft.Reporting.WinForms.ReportParameter("pBackorderNotes", _invoice.BackorderNotes), new Microsoft.Reporting.WinForms.ReportParameter("pFreight", _invoice.freight.ToString()), }; this.reportViewer1.LocalReport.SetParameters(p); this.reportViewer1.RefreshReport(); // Create Excel FileStream newFile = new FileStream(ExcelFileName, FileMode.Create); string renderFormat = (ExcelFileName.EndsWith(".xlsx") ? "EXCELOPENXML" : "Excel"); byte[] bytes = this.reportViewer1.LocalReport.Render(renderFormat, null, out mimeType, out encoding, out extension, out streamids, out warnings); newFile.Write(bytes, 0, bytes.Length); newFile.Close(); // Create PDF Byte[] mybytes = this.reportViewer1.LocalReport.Render("PDF"); using (FileStream fs = File.Create(PDFFileName)) { fs.Write(mybytes, 0, mybytes.Length); } }
internal void SaveToExcel() { int rows = 0; int tallRow = 14; int medRow = 12; IWorkbook wb = new XSSFWorkbook(); IRow row; ICell cell; wsheet = wb.CreateSheet("Sheet1"); IFont font; font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; ICellStyle DefaultStyle = wb.CreateCellStyle(); DefaultStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; DefaultStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 14; font.FontName = "Arial"; font.IsBold = true; ICellStyle TitleStyle = wb.CreateCellStyle(); TitleStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 12; font.FontName = "Arial"; font.IsBold = true; ICellStyle InvoiceStyle = wb.CreateCellStyle(); InvoiceStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 8; font.FontName = "Arial"; ICellStyle SmallStyle = wb.CreateCellStyle(); SmallStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; ICellStyle MoneyStyle = wb.CreateCellStyle(); MoneyStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; MoneyStyle.SetFont(font); MoneyStyle.DataFormat = 2; font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; font.IsBold = true; ICellStyle MoneyBoldStyle = wb.CreateCellStyle(); MoneyBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; MoneyBoldStyle.SetFont(font); MoneyBoldStyle.DataFormat = 2; font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; font.IsBold = true; ICellStyle BoldStyle = wb.CreateCellStyle(); BoldStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; font.IsBold = true; ICellStyle RightAlignBoldStyle = wb.CreateCellStyle(); RightAlignBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; RightAlignBoldStyle.SetFont(font); font = wb.CreateFont(); font.FontHeight = 10; font.FontName = "Arial"; ICellStyle RightAlignStyle = wb.CreateCellStyle(); RightAlignStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; RightAlignStyle.SetFont(font); wsheet.SetColumnWidth(0, CharW * 5); wsheet.SetColumnWidth(1, CharW * 3); wsheet.SetColumnWidth(2, CharW * 3); wsheet.SetColumnWidth(3, CharW * 8); wsheet.SetColumnWidth(4, CharW * 7); wsheet.SetColumnWidth(5, CharW * 20); wsheet.SetColumnWidth(6, CharW * 2 + 100); wsheet.SetColumnWidth(7, CharW * 2 + 100); wsheet.SetColumnWidth(8, CharW * 6); wsheet.SetColumnWidth(9, CharW * 9); wsheet.SetColumnWidth(10, CharW); wsheet.DefaultRowHeight = 250; row = wsheet.CreateRow(rows++); row.HeightInPoints = 15; cell = row.CreateCell(0); cell.CellStyle = TitleStyle; cell.SetCellValue("GREAT WEST WHOLESALE LTD"); cell = row.CreateCell(8); cell.CellStyle = DefaultStyle; cell.SetCellValue(InvoiceID); cell = row.CreateCell(9); cell.CellStyle = SmallStyle; cell.SetCellValue("gst no. R102186178"); row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(0); cell.CellStyle = BoldStyle; cell.SetCellValue("1670 PANDORA ST. VANCOUVER, BC V5L 1L6"); cell = row.CreateCell(9); cell.CellStyle = InvoiceStyle; cell.SetCellValue("INVOICE"); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(0); cell.CellStyle = DefaultStyle; cell.SetCellValue("604-255-9588 fax 604-255-9589 1-800-901-9588 [email protected]"); rows++; row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(0); cell.CellStyle = DefaultStyle; cell.SetCellValue("Ship to:"); cell = row.CreateCell(3); cell.CellStyle = BoldStyle; cell.SetCellValue(customer.StoreName); cell = row.CreateCell(6); cell.CellStyle = DefaultStyle; cell.SetCellValue("INVOICE NO."); cell = row.CreateCell(9); cell.CellStyle = BoldStyle; row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; if (customer.StoreDetails.Length != 0) { cell.SetCellValue(customer.StoreDetails); } else { cell.SetCellValue(customer.StreetAddress); } cell = row.CreateCell(6); cell.CellStyle = DefaultStyle; cell.SetCellValue("DATE:"); cell = row.CreateCell(9); cell.CellStyle = DefaultStyle; //Char[] delim = { ' ' }; //String[] date = System.DateTime.Today.ToLongDateString().Split(delim, 2); //String zz = date[1].ToUpper(); cell.SetCellValue(System.DateTime.Today.ToLongDateString().ToUpper()); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; if (customer.StoreDetails.Length != 0) { cell.SetCellValue(customer.StreetAddress); } else { cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress); } cell = row.CreateCell(6); cell.CellStyle = DefaultStyle; cell.SetCellValue("TERMS:"); cell = row.CreateCell(9); cell.CellStyle = DefaultStyle; cell.SetCellValue(customer.PaymentTerms); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; if (customer.StoreDetails.Length != 0) { cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress); } else { cell.SetCellValue("tel: " + customer.PhoneNumber); } cell = row.CreateCell(6); cell.CellStyle = DefaultStyle; cell.SetCellValue("SHIP:"); cell = row.CreateCell(9); cell.CellStyle = DefaultStyle; cell.SetCellValue(customer.ShippingInstructions); row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; if (customer.StoreDetails.Length != 0) { cell.SetCellValue("tel: " + customer.PhoneNumber); } else { if (customer.Rep.Length != 0) { cell.CellStyle = BoldStyle; cell.SetCellValue("rep: " + customer.Rep); } } cell = row.CreateCell(6); cell.CellStyle = BoldStyle; cell.SetCellValue("PURCHASE ORDER: PO " + PurchaseOrder); if (customer.StoreDetails.Length != 0) { row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; if (customer.Rep.Length != 0) { cell = row.CreateCell(3); cell.CellStyle = BoldStyle; cell.SetCellValue("rep: " + customer.Rep); } } else { rows++; } row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(1); cell.CellStyle = InvoiceStyle; cell.SetCellValue(" ** " + InvoiceTemplateNoteDatabase.GetNote()); row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(1); cell.CellStyle = InvoiceStyle; cell.SetCellValue(" " + customer.StoreSpecialNotes); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(0); cell.CellStyle = DefaultStyle; cell.SetCellValue("QUANTITY"); cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; cell.SetCellValue("ITEM NO."); cell = row.CreateCell(5); cell.CellStyle = DefaultStyle; cell.SetCellValue("DESCRIPTION"); cell = row.CreateCell(8); cell.CellStyle = DefaultStyle; cell.SetCellValue("COST"); cell = row.CreateCell(9); cell.CellStyle = DefaultStyle; cell.SetCellValue("AMOUNT"); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(0); cell.CellStyle = BoldStyle; cell.SetCellValue("-------------------------------------------------------------------------------------------------------------------"); int itemCount = Items.Count; ///////////////////////////////////////////////////////////////////////////////////////////////////////// Items = Items.OrderBy(o => o.Location).ToList(); for (int i = 0; i < itemCount; i++) { row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(0); cell.CellStyle = RightAlignStyle; cell.SetCellValue(Items[i].Quantity); cell = row.CreateCell(9); cell.CellStyle = MoneyStyle; cell.SetCellFormula("ROUND(A" + rows + "*I" + rows + ",2)"); if (Items[i].Quantity / Items[i].PerCarton >= 1) { cell = row.CreateCell(2); cell.CellStyle = DefaultStyle; //cell.SetCellFormula("ROUND(" + Items[i].Quantity + "/" + Items[i].PerCarton + ",2)"); cell.SetCellValue(((Items[i].Quantity * 1.0) / (Items[i].PerCarton * 1.0)).ToString("0.0")); } cell = row.CreateCell(3); cell.CellStyle = DefaultStyle; cell.SetCellValue(Items[i].ItemNo); cell = row.CreateCell(4); cell.CellStyle = DefaultStyle; cell.SetCellValue(Items[i].Location); cell = row.CreateCell(5); cell.CellStyle = DefaultStyle; cell.SetCellValue(Items[i].ItemDesc); cell = row.CreateCell(7); cell.CellStyle = RightAlignStyle; cell.SetCellValue(Items[i].PerCarton); cell = row.CreateCell(8); cell.CellStyle = MoneyStyle; cell.SetCellValue(Items[i].SellPrice); if (Items[i].SpecialNotes.Length != 0) { cell = row.CreateCell(11); cell.CellStyle = DefaultStyle; cell.SetCellValue(Items[i].SpecialNotes); } } row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(9); cell.CellStyle = RightAlignBoldStyle; cell.SetCellValue("---------------"); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(9); cell.CellStyle = MoneyBoldStyle; cell.SetCellFormula(String.Format("SUM(J15:J" + (rows - 2) + ")")); ProvinceTax pt = ProvinceTaxDatabase.GetProvinceByName(customer.Province); row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(5); cell.CellStyle = BoldStyle; cell.SetCellValue("GST " + pt.gst + "%"); cell = row.CreateCell(9); cell.CellStyle = MoneyBoldStyle; cell.SetCellFormula("ROUND(J" + (rows - 1) + "*" + (pt.gst / 100.0) + ",2)"); //cell.SetCellFormula(String.Format("J" + (rows - 1) + " * " + (pt.gst/ 100.0))); if (pt.pst != 0) { row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(5); cell.CellStyle = BoldStyle; cell.SetCellValue("PST " + pt.pst + "%"); cell = row.CreateCell(9); cell.CellStyle = MoneyBoldStyle; cell.SetCellFormula("ROUND(J" + (rows - 2) + "*" + (pt.pst / 100.0) + ",2)"); //cell.SetCellFormula(String.Format("J" + (rows - 2) + " * " + (pt.pst / 100.0))); } row = wsheet.CreateRow(rows++); row.HeightInPoints = medRow; cell = row.CreateCell(9); cell.CellStyle = RightAlignBoldStyle; cell.SetCellValue("---------------"); row = wsheet.CreateRow(rows++); row.HeightInPoints = tallRow; cell = row.CreateCell(5); cell.CellStyle = BoldStyle; cell.SetCellValue("INVOICE TOTAL"); cell = row.CreateCell(9); cell.CellStyle = MoneyBoldStyle; if (pt.pst != 0) { cell.SetCellFormula(String.Format("SUM(J" + (rows - 4) + ":J" + (rows - 2) + ")")); } else { cell.SetCellFormula(String.Format("SUM(J" + (rows - 3) + ":J" + (rows - 2) + ")")); } int rowEnd = 400; int lastColumn = 25; IRow r; for (int rowNum = 0; rowNum < rows; rowNum++) { if ((r = wsheet.GetRow(rowNum)) != null) { for (int cn = 0; cn < lastColumn; cn++) { if ((cell = r.GetCell(cn)) == null) { cell = r.CreateCell(cn); cell.CellStyle = DefaultStyle; } } } else { r = wsheet.CreateRow(rowNum); //getRow(rowNum); r.HeightInPoints = medRow; for (int cn = 0; cn < lastColumn; cn++) { cell = r.CreateCell(cn); cell.CellStyle = DefaultStyle; } } } for (int rowNum = rows; rowNum < rowEnd; rowNum++) { r = wsheet.CreateRow(rowNum); //getRow(rowNum); r.HeightInPoints = medRow; for (int cn = 0; cn < lastColumn; cn++) { cell = r.CreateCell(cn); cell.CellStyle = DefaultStyle; } } string deskPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); FileStream stream = new FileStream(@"" + deskPath + "\\Invoices\\Progress\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write); wb.Write(stream); FileStream stream2 = new FileStream(@"" + deskPath + "\\Invoices\\Original\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write); wb.Write(stream2); PrintExcel(); }