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