private static void NSeed() { Database.DropTables(); Database.CreateTables(); int num = 10; SeedTaxes(num); SeedCustomers(num); SeedToys(num); SeedInvoices(num); InvoiceTemplateNoteDatabase.AddNote("Template Note"); }
public InvoiceNoteForm() { InitializeComponent(); Label invoiceNoteLabel = new Label(); invoiceNoteLabel.Text = "Invoice Note"; invoiceNoteLabel.Location = new Point(10, 10); invoiceNoteLabel.AutoSize = true; this.Controls.Add(invoiceNoteLabel); InvoiceNoteTextBox.Text = InvoiceTemplateNoteDatabase.GetNote(); InvoiceNoteTextBox.Location = new Point(10, 25); InvoiceNoteTextBox.Size = new Size(260, 50); this.Controls.Add(InvoiceNoteTextBox); Button okButton = new Button(); okButton.Location = new Point(10, 55); okButton.Size = new Size(50, 25); okButton.Text = "OK"; okButton.Click += OkButton_Click; this.Controls.Add(okButton); }
private void OkButton_Click(object sender, EventArgs e) { InvoiceTemplateNoteDatabase.EditNote(InvoiceNoteTextBox.Text); this.Close(); }
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(); }