private void iconGreen_Click(object sender, EventArgs e) { if (statusCode == "GROEN") { MessageBox.Show("Deze offerte is al geplaatst als order", "Reeds order", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { DialogResult dr = MessageBox.Show("Ben u zeker dat u offerte " + offertenr + " van klant " + firmaNaam + " wilt toevoegen als order?", "Order toevoegen", MessageBoxButtons.YesNo, MessageBoxIcon.Information); switch (dr) { case DialogResult.Yes: iconGreen.Visible = false; iconEdit.Visible = false; iconDelete.Visible = false; iconNew.Visible = false; Laden.ShowSplashScreen(); OfferteToOrder offerteToOrder = new OfferteToOrder(offertenr, offerteartikelnr, firmaNaam, klantnr, "No"); offerteToOrder.MdiParent = this.main; Laden.CloseForm(); offerteToOrder.Show(); iconGreen.Visible = true; iconEdit.Visible = true; iconDelete.Visible = true; iconNew.Visible = true; break; case DialogResult.No: break; case DialogResult.Abort: break; } } }
private void btnLijst_Click(object sender, EventArgs e) { Laden.ShowSplashScreen(); Lijsten lijsten = new Lijsten(main, dataKwaliteiten(), "bewerkGondardennes"); lijsten.MdiParent = this.main; Laden.CloseForm(); lijsten.Show(); }
private void btnAddFactuur_Click(object sender, EventArgs e) { Laden.ShowSplashScreen(); AddFactuur addFactuur = new AddFactuur(); addFactuur.MdiParent = main; Laden.CloseForm(); addFactuur.Show(); }
private void terugContacterenToolStripMenuItem_Click(object sender, EventArgs e) { Laden.ShowSplashScreen(); TerugContacteren terugcontacteren = new TerugContacteren(this, codeUser); terugcontacteren.MdiParent = this; Laden.CloseForm(); terugcontacteren.Show(); }
//knoppen private void txtKwaliteit_Click(object sender, EventArgs e) { if (btnBereken.Visible == true) { Laden.ShowSplashScreen(); Lijsten lijsten = new Lijsten(main, dataKwaliteiten(), "gondardennes"); lijsten.MdiParent = this.main; Laden.CloseForm(); lijsten.Show(); } }
private void iconSearch_Click(object sender, EventArgs e) { if (lijsten == null) { Laden.ShowSplashScreen(); lijsten = new Lijsten(main, dataKlanten(), "klantenAdd"); lijsten.MdiParent = this.main; Laden.CloseForm(); } lijsten.BringToFront(); lijsten.Show(); }
private void opvragenToolStripMenuItem_Click(object sender, EventArgs e) { if (klanten == null) { Laden.ShowSplashScreen(); klanten = new Klanten(this, codeUser); klanten.MdiParent = this; Laden.CloseForm(); } klanten.BringToFront(); klanten.Show(); }
private void txtKwaliteitOpzoeken3_Click(object sender, EventArgs e) { if (lijstenkwal == null) { Laden.ShowSplashScreen(); lijstenkwal = new Lijsten(main, dataKwaliteiten(), "gondardennesAddOfferte3"); lijstenkwal.MdiParent = this.main; Laden.CloseForm(); } lijstenkwal.BringToFront(); lijstenkwal.Show(); }
private void facturenToolStripMenuItem_Click(object sender, EventArgs e) { if (facturen == null) { Laden.ShowSplashScreen(); facturen = new Facturen(this); facturen.MdiParent = this; Laden.CloseForm(); } facturen.BringToFront(); facturen.Show(); }
private void agendaToolStripMenuItem_Click(object sender, EventArgs e) { if (kalender == null) { Laden.ShowSplashScreen(); kalender = new Kalender(); kalender.MdiParent = this; Laden.CloseForm(); } kalender.BringToFront(); kalender.Show(); }
private void ordersToolStripMenuItem_Click(object sender, EventArgs e) { if (orders == null) { Laden.ShowSplashScreen(); orders = new Orders(this, codeUser); orders.MdiParent = this; Laden.CloseForm(); } orders.BringToFront(); orders.Show(); }
private void calculatorToolStripMenuItem_Click(object sender, EventArgs e) { if (calculator == null) { Laden.ShowSplashScreen(); calculator = new Calculator(this, codeUser); calculator.MdiParent = this; Laden.CloseForm(); } calculator.BringToFront(); calculator.Show(); }
//menustrip private void opvragenToolStripMenuItem1_Click(object sender, EventArgs e) { if (prospecties == null) { Laden.ShowSplashScreen(); prospecties = new Prospecties(this, codeUser); prospecties.MdiParent = this; Laden.CloseForm(); } prospecties.BringToFront(); prospecties.Show(); }
//knoppen private void btnOrder_Click(object sender, EventArgs e) { bestelbonnummer = txtBestelbonnr.Text; btnAnnuleren.Visible = false; btnOrder.Visible = false; dataNaarOrders(); dataNaarOrderArtikel(); if (reorder == "No") { dataUpdateOfferteStatus(); } DialogResult dr1 = MessageBox.Show("Wilt u hiervan een orderbevestiging?", "Orderbevestiging", MessageBoxButtons.YesNo, MessageBoxIcon.Information); switch (dr1) { case DialogResult.Yes: Laden.ShowSplashScreen(); dataOpvragenOrderbevestigingExcel(); makeExcell(); Laden.CloseForm(); this.Close(); break; case DialogResult.No: this.Close(); break; case DialogResult.Abort: break; } if (Application.OpenForms["Orders"] != null) { (Application.OpenForms["Orders"] as Orders).dataRefresh(); } }
private void makeExcellFactuur() { Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Columns["A"].ColumnWidth = 2; xlWorkSheet.Columns["B"].ColumnWidth = 12; xlWorkSheet.Columns["C"].ColumnWidth = 6; xlWorkSheet.Columns["D"].ColumnWidth = 27; xlWorkSheet.Columns["E"].ColumnWidth = 10; xlWorkSheet.Columns["F"].ColumnWidth = 10; xlWorkSheet.Columns["G"].ColumnWidth = 15; xlWorkSheet.get_Range("B33", "F40").RowHeight = 15; xlWorkSheet.get_Range("A4", "A15").RowHeight = 13; xlWorkSheet.get_Range("A46", "A49").RowHeight = 13; xlWorkSheet.Columns["B"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Columns["D"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[4, 6] = "Factuur"; xlWorkSheet.get_Range("F4").Font.Italic = true; xlWorkSheet.get_Range("F4").Font.Bold = true; xlWorkSheet.get_Range("F4", "G4").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F4", "G4").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("F4", "G4").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F4", "G4").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.Cells[5, 7] = firmaNaam; if (tav != "Geen") { xlWorkSheet.Cells[6, 7] = "T.a.v. " + tav; } xlWorkSheet.Cells[7, 7] = adres; xlWorkSheet.Cells[8, 7] = postcode + " " + gemeente; xlWorkSheet.Cells[9, 7] = land; xlWorkSheet.Cells[8, 2] = "Factuurnummer: " + txtFactuurnr.Text; xlWorkSheet.get_Range("B8").Font.Bold = true; xlWorkSheet.Cells[11, 2] = "Klantnummer: " + klantnr; xlWorkSheet.Cells[12, 2] = "BTWnummer: " + btwnummer; xlWorkSheet.Cells[14, 2] = "Datum"; xlWorkSheet.Cells[15, 2] = dtpDatum.Value.ToString("dd-MM-yyyy"); xlWorkSheet.Cells[14, 4] = "Vervaldatum"; xlWorkSheet.Cells[15, 4] = dtpDatum.Value.AddDays(30).ToString("dd-MM-yyyy"); xlWorkSheet.get_Range("B8").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B11").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B12").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B14").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B15").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("D14").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("D15").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int i = 5; i < 10; i++) { xlWorkSheet.get_Range("G" + i).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } xlWorkSheet.get_Range("B5", "G15").Font.Size = 10; xlWorkSheet.Cells[17, 1] = "Ordernr/Omschrijving"; xlWorkSheet.Cells[17, 5] = "Aantal"; xlWorkSheet.Cells[17, 6] = "Prijs/E."; xlWorkSheet.Cells[17, 7] = "Euro"; xlWorkSheet.get_Range("A17", "G17").Font.Size = 14; xlWorkSheet.get_Range("A17", "G17").Font.Bold = true; xlWorkSheet.get_Range("A17", "G17").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A17", "G17").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A17", "G17").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A17", "G17").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; //OfferteGegevens int j = 19; xlWorkSheet.Cells[j, 1] = "# Ordernr " + ordernr; xlWorkSheet.get_Range("A" + j).Font.Bold = true; xlWorkSheet.Cells[j, 5] = aantal; double stukprijs = (prijs / 1000); xlWorkSheet.Cells[j, 6] = stukprijs.ToString(); xlWorkSheet.get_Range("E" + j, "G" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[j, 7] = (aantal * stukprijs).ToString(); j++; if (Regex.Replace(omschrijving, @"\s+", "") != "") { xlWorkSheet.Cells[j, 2] = "Omschrijving"; xlWorkSheet.Cells[j, 4] = omschrijving; j++; } if (referentie != "") { xlWorkSheet.Cells[j, 2] = "Uw referentie"; xlWorkSheet.Cells[j, 4] = referentie; j++; } xlWorkSheet.Cells[j, 2] = "Fefco"; xlWorkSheet.Cells[j, 4] = fefco; j++; xlWorkSheet.Cells[j, 2] = "Afmetingen (in mm)"; if (fefco == "F110") { xlWorkSheet.Cells[j, 4] = lengte + " X " + breedte; } else { xlWorkSheet.Cells[j, 4] = lengte + " X " + breedte + " X " + hoogte; } j++; if (kwaliteit == "" || kwaliteit == "0") { } else { xlWorkSheet.Cells[j, 2] = "Kwaliteit"; xlWorkSheet.Cells[j, 4] = kwaliteit; j++; } xlWorkSheet.Cells[j, 2] = "Bedrukking"; xlWorkSheet.Cells[j, 4] = bedrukking; j++; if (Regex.Replace(stansmeskosten.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige stansmeskost"; xlWorkSheet.Cells[j, 6] = stansmeskosten; xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } if (Regex.Replace(clichekost.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige clichekost"; xlWorkSheet.Cells[j, 6] = clichekost; xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } j++; xlWorkSheet.get_Range("D35", "G35").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("D43", "G43").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("D43", "G43").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("D43", "G43").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("D43", "G43").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; double totaal = Math.Round((aantal * stukprijs) + clichekost + stansmeskosten, 2); double kmHeffing = Math.Round(totaal * 0.008, 2); totaalExclBtw = 0; if ((totaal + kmHeffing) > 500) { totaalExclBtw = Math.Round(totaal + kmHeffing, 2); } else { totaalExclBtw = Math.Round(totaal + kmHeffing + 45, 2); } btw21 = Math.Round(totaalExclBtw * 0.21, 2); totaalAlles = Math.Round(btw21 + totaalExclBtw, 2); xlWorkSheet.Cells[35, 5] = "Totaal"; xlWorkSheet.get_Range("E35").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[35, 6] = "€"; xlWorkSheet.get_Range("F35").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[35, 7] = totaal; xlWorkSheet.get_Range("E35").Font.Bold = true; xlWorkSheet.Cells[37, 5] = "Km heffing (0,8%)"; xlWorkSheet.get_Range("E37").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[37, 6] = "€"; xlWorkSheet.get_Range("F37").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[37, 7] = kmHeffing; xlWorkSheet.Cells[38, 5] = "Transportkosten"; xlWorkSheet.get_Range("E38").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[38, 6] = "€"; xlWorkSheet.get_Range("F38").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; if ((totaal + kmHeffing) > 500) { xlWorkSheet.Cells[38, 7] = "0"; } else { xlWorkSheet.Cells[38, 7] = "45"; } xlWorkSheet.Cells[40, 5] = "Totaal exclusief BTW"; xlWorkSheet.get_Range("E40").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[40, 6] = "€"; xlWorkSheet.get_Range("F40").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[40, 7] = totaalExclBtw; xlWorkSheet.get_Range("E40").Font.Bold = true; xlWorkSheet.Cells[41, 5] = "21% BTW"; xlWorkSheet.get_Range("E41").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[41, 6] = "€"; xlWorkSheet.get_Range("F41").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[41, 7] = btw21; xlWorkSheet.Cells[43, 5] = "TOTAAL"; xlWorkSheet.get_Range("E43").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[43, 6] = "€"; xlWorkSheet.get_Range("F43").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; xlWorkSheet.Cells[43, 7] = totaalAlles; xlWorkSheet.get_Range("E43").Font.Bold = true; xlWorkSheet.get_Range("E43").Font.Size = 12; //xlWorkSheet.Cells[34, 2] = "Leveringstermijn: " + txtLeveringsTermijn.Text; xlWorkSheet.get_Range("A46", "G46").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A46", "G46").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A51", "G51").MergeCells = true; xlWorkSheet.get_Range("A52", "G52").MergeCells = true; xlWorkSheet.Cells[46, 1] = "Leveringsvoorwaarden : Franco vanaf 500 euro (<45 euro)"; xlWorkSheet.Cells[47, 1] = "Betaling : 30 dagen netto"; xlWorkSheet.Cells[48, 1] = "Alle prijzen in EUR"; xlWorkSheet.Cells[49, 1] = "Algemene verkoopsvoorwaarden : www.willbox.be"; xlWorkSheet.get_Range("A46", "A49").Font.Italic = true; xlWorkSheet.get_Range("A46", "A49").Font.Size = 9; xlWorkSheet.get_Range("A51", "G52").Font.Size = 7; xlWorkSheet.Cells[51, 1] = "WillBox bvba | Zavelstraat 21a | 9190 Stekene | Mail: [email protected] | Tel. +32 (0)3 293 52 50 | Gsm: + 32 (0)472 97 49 46 | "; xlWorkSheet.Cells[52, 1] = "Btw: BE 0655 906 872 | Fortis Bank: BE31 0017 8705 5955 | BIC GEBABEBB"; xlWorkSheet.get_Range("A51", "G52").WrapText = true; xlWorkSheet.get_Range("A51", "G52").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // afbeelding toevoegen xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\willboxlogo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 180, 62); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\more.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 180, 45, 101, 18); string path = @"c:/Willbox/Facturen/" + firmaNaam.ToLower() + "/"; try { // Determine whether the directory exists. if (Directory.Exists(path)) { } else { // Try to create the directory. DirectoryInfo di = Directory.CreateDirectory(path); } } catch (Exception e) { Console.WriteLine("The process failed: {0}", e.ToString()); MessageBox.Show("Er is iets fout gelopen bij de aanmaak van het Bestand, probeer het later opnieuw.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } //Laden.CloseForm(); xlWorkBook.SaveAs("c:\\Willbox\\Facturen\\" + firmaNaam.ToLower() + "\\Factuur " + firmaNaam.ToLower() + " " + txtFactuurnr.Text + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Laden.CloseForm(); MessageBox.Show("Excel bestand gecreërd met de als naam: Factuur " + firmaNaam.ToLower() + " " + txtFactuurnr.Text + ".xls"); }
private void makeExcell() { Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Columns["A"].ColumnWidth = 2; xlWorkSheet.Columns["B"].ColumnWidth = 12; xlWorkSheet.Columns["C"].ColumnWidth = 6; xlWorkSheet.Columns["D"].ColumnWidth = 26; xlWorkSheet.Columns["E"].ColumnWidth = 10; xlWorkSheet.Columns["F"].ColumnWidth = 11; xlWorkSheet.Columns["G"].ColumnWidth = 15; xlWorkSheet.get_Range("B33", "F40").RowHeight = 15; xlWorkSheet.get_Range("A7", "A12").RowHeight = 13; xlWorkSheet.get_Range("A45", "A48").RowHeight = 13; xlWorkSheet.get_Range("A37", "A39").RowHeight = 18; //pagina 2 xlWorkSheet.get_Range("A57", "A62").RowHeight = 13; xlWorkSheet.get_Range("A95", "A97").RowHeight = 13; xlWorkSheet.get_Range("A86", "A89").RowHeight = 18; xlWorkSheet.Columns["B"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Columns["D"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[7, 6] = "Leveringsnota"; xlWorkSheet.get_Range("F7").Font.Italic = true; xlWorkSheet.get_Range("F7").Font.Bold = true; xlWorkSheet.get_Range("F7", "G7").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F7", "G7").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("F7", "G7").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F7", "G7").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.Cells[8, 7] = dgvOrders.Rows[0].Cells["naam"].Value.ToString(); if (dgvOrders.Rows[0].Cells["tav"].Value.ToString() != "Geen") { xlWorkSheet.Cells[9, 7] = "T.a.v. " + dgvOrders.Rows[0].Cells["tav"].Value.ToString(); } string adres = dgvOrders.Rows[0].Cells["adres"].Value.ToString().ToLower(); if (dgvOrders.Rows[0].Cells["adres"].Value.ToString() != "") { adres = adres.First().ToString().ToUpper() + String.Join("", adres.Skip(1)); } xlWorkSheet.Cells[10, 7] = adres; xlWorkSheet.Cells[11, 7] = dgvOrders.Rows[0].Cells["postcode"].Value.ToString() + " " + dgvOrders.Rows[0].Cells["gemeente"].Value.ToString(); xlWorkSheet.Cells[12, 7] = dgvOrders.Rows[0].Cells["land"].Value.ToString(); xlWorkSheet.Cells[11, 2] = "Klantnummer: " + klantnr; xlWorkSheet.Cells[12, 2] = "Datum: " + DateTime.Now.ToString("dd MMM yyyy"); xlWorkSheet.get_Range("B11").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B12").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int i = 8; i < 13; i++) { xlWorkSheet.get_Range("G" + i).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } xlWorkSheet.get_Range("B7", "G12").Font.Size = 10; xlWorkSheet.Cells[14, 1] = "Ordernr/Omschrijving"; xlWorkSheet.Cells[14, 7] = "Aantal"; xlWorkSheet.get_Range("A14", "G14").Font.Size = 14; xlWorkSheet.get_Range("A14", "G14").Font.Bold = true; xlWorkSheet.get_Range("A14", "G14").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A14", "G14").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A14", "G14").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A14", "G14").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; //OfferteGegevens int j = 16; int laatste = 16; int aantalwwp = 0; int aantalep = 0; for (int i = 0; i < dgvOrders.RowCount - 1; i++) { LOOP: aantalwwp += int.Parse(dgvOrders.Rows[i].Cells["aantalwwp"].Value.ToString()); aantalep += int.Parse(dgvOrders.Rows[i].Cells["aantalep"].Value.ToString()); xlWorkSheet.Cells[j, 1] = "# Ordernr " + dgvOrders.Rows[i].Cells["ordernr"].Value.ToString(); xlWorkSheet.get_Range("A" + j).Font.Bold = true; if (dgvOrders.Rows[i].Cells["aantalgeproduceerd"].Value.ToString() != "") { xlWorkSheet.Cells[j, 7] = dgvOrders.Rows[i].Cells["aantalgeproduceerd"].Value.ToString(); } else { xlWorkSheet.Cells[j, 7] = dgvOrders.Rows[i].Cells["aantal"].Value.ToString(); } xlWorkSheet.get_Range("E" + j, "G" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; if (dgvOrders.Rows[i].Cells["bestelbonnummer"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Bestelbonnummer"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[0].Cells["bestelbonnummer"].Value.ToString(); j++; } if (Regex.Replace(dgvOrders.Rows[i].Cells["omschrijving"].Value.ToString(), @"\s+", "") != "") { xlWorkSheet.Cells[j, 2] = "Omschrijving"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["omschrijving"].Value.ToString(); j++; } if (dgvOrders.Rows[i].Cells["ref"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Uw referentie"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["ref"].Value.ToString(); j++; } xlWorkSheet.Cells[j, 2] = "Fefco"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["fefco"].Value.ToString(); j++; xlWorkSheet.Cells[j, 2] = "Afmetingen (in mm)"; if (dgvOrders.Rows[i].Cells["fefco"].Value.ToString() == "F110") { xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOrders.Rows[i].Cells["breedte"].Value.ToString(); } else { xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOrders.Rows[i].Cells["breedte"].Value.ToString() + " X " + dgvOrders.Rows[i].Cells["hoogte"].Value.ToString(); } j++; if (dgvOrders.Rows[i].Cells["kwaliteit"].Value.ToString() == "" || dgvOrders.Rows[i].Cells["kwaliteit"].Value.ToString() == "0") { } else { xlWorkSheet.Cells[j, 2] = "Kwaliteit"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["kwaliteit"].Value.ToString(); j++; } if (dgvOrders.Rows[i].Cells["Bedrukking"].Value.ToString() != "Geen") { xlWorkSheet.Cells[j, 2] = "Bedrukking"; xlWorkSheet.Cells[j, 4] = dgvOrders.Rows[i].Cells["Bedrukking"].Value.ToString(); j++; } j++; if (j > 37 && j < 66) { xlWorkSheet.get_Range("A" + laatste, "G" + j).Cells.Clear(); xlWorkSheet.Cells[36, 7] = "Pagina 1 van 2"; j = 66; goto LOOP; } else { laatste = j; } } //Extra pagina if (j > 41) { xlWorkSheet.Cells[87, 1] = "Aantal paletten:"; xlWorkSheet.Cells[89, 1] = "Teruggave:"; xlWorkSheet.Cells[87, 4] = aantalwwp + " wegwerppaletten"; xlWorkSheet.Cells[88, 4] = aantalep + " europaletten"; xlWorkSheet.Cells[89, 4] = "Europaletten: ..........."; xlWorkSheet.Cells[90, 1] = "Voor akkoord ontvangst"; xlWorkSheet.Cells[90, 5] = "Naam in drukletters"; xlWorkSheet.get_Range("A87", "E90").Font.Bold = true; xlWorkSheet.get_Range("A90", "G90").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A87", "G87").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A90", "G90").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("D90", "D94").Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\willboxlogo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 745, 180, 62); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\more.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 180, 790, 101, 18); xlWorkSheet.Cells[57, 6] = "Leveringsnota"; xlWorkSheet.get_Range("F57").Font.Italic = true; xlWorkSheet.get_Range("F57").Font.Bold = true; xlWorkSheet.get_Range("F57", "G57").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F57", "G57").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("F57", "G57").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("F57", "G57").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.Cells[58, 7] = dgvOrders.Rows[0].Cells["naam"].Value.ToString(); if (dgvOrders.Rows[0].Cells["tav"].Value.ToString() != "Geen") { xlWorkSheet.Cells[59, 7] = "T.a.v. " + dgvOrders.Rows[0].Cells["tav"].Value.ToString(); } xlWorkSheet.Cells[60, 7] = dgvOrders.Rows[0].Cells["adres"].Value.ToString().ToLower(); xlWorkSheet.Cells[61, 7] = dgvOrders.Rows[0].Cells["postcode"].Value.ToString() + " " + dgvOrders.Rows[0].Cells["gemeente"].Value.ToString(); xlWorkSheet.Cells[62, 7] = dgvOrders.Rows[0].Cells["land"].Value.ToString(); xlWorkSheet.Cells[61, 2] = "Klantnummer: " + klantnr; xlWorkSheet.Cells[62, 2] = "OfferteDatum: " + DateTime.Now.ToString("dd MMM yyyy"); xlWorkSheet.get_Range("B61").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B62").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int k = 58; k < 63; k++) { xlWorkSheet.get_Range("G" + k).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } xlWorkSheet.get_Range("B57", "G63").Font.Size = 10; xlWorkSheet.Cells[64, 1] = "Offertenr/Omschrijving"; xlWorkSheet.Cells[64, 7] = "Aantal"; xlWorkSheet.Cells[86, 7] = "Pagina 2 van 2"; xlWorkSheet.get_Range("A64", "G64").Font.Size = 14; xlWorkSheet.get_Range("A64", "G64").Font.Bold = true; xlWorkSheet.get_Range("A64", "G64").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A64", "G64").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A64", "G64").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A64", "G64").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.get_Range("A95", "G95").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A95", "G95").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A99", "G99").MergeCells = true; xlWorkSheet.get_Range("A100", "G100").MergeCells = true; xlWorkSheet.Cells[95, 1] = "Leveringsvoorwaarden : Franco vanaf 500 euro (<45 euro)"; xlWorkSheet.Cells[96, 1] = "Km heffing: 0,8%"; xlWorkSheet.Cells[97, 1] = "Algemene verkoopsvoorwaarden : www.willbox.be"; xlWorkSheet.get_Range("A95", "A97").Font.Italic = true; xlWorkSheet.get_Range("A95", "A97").Font.Size = 9; xlWorkSheet.get_Range("A99", "G100").Font.Size = 7; xlWorkSheet.Cells[99, 1] = "WillBox bvba | Zavelstraat 21a | 9190 Stekene | Mail: [email protected] | Tel. +32 (0)3 293 52 50 | Gsm: + 32 (0)472 97 49 46 | "; xlWorkSheet.Cells[100, 1] = "Btw: BE 0655 906 872 | Fortis Bank: BE31 0017 8705 5955 | BIC GEBABEBB"; xlWorkSheet.get_Range("A99", "G100").WrapText = true; xlWorkSheet.get_Range("A99", "G100").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } xlWorkSheet.Cells[37, 1] = "Aantal paletten:"; xlWorkSheet.Cells[39, 1] = "Teruggave:"; xlWorkSheet.Cells[37, 4] = aantalwwp + " wegwerppaletten"; xlWorkSheet.Cells[38, 4] = aantalep + " europaletten"; xlWorkSheet.Cells[39, 4] = "Europaletten: ..........."; xlWorkSheet.Cells[40, 1] = "Voor akkoord ontvangst"; xlWorkSheet.Cells[40, 5] = "Naam in drukletters"; xlWorkSheet.get_Range("A37", "E40").Font.Bold = true; xlWorkSheet.get_Range("A40", "G40").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A37", "G37").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A40", "G40").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("D40", "D44").Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A45", "G45").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A45", "G45").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A49", "G49").MergeCells = true; xlWorkSheet.get_Range("A50", "G50").MergeCells = true; xlWorkSheet.Cells[45, 1] = "Leveringsvoorwaarden : Franco vanaf 500 euro (<45 euro)"; xlWorkSheet.Cells[46, 1] = "Km heffing: 0,8%"; xlWorkSheet.Cells[47, 1] = "Algemene verkoopsvoorwaarden : www.willbox.be"; xlWorkSheet.get_Range("A43", "A48").Font.Italic = true; xlWorkSheet.get_Range("A43", "A48").Font.Size = 9; xlWorkSheet.get_Range("A49", "G50").Font.Size = 7; xlWorkSheet.Cells[49, 1] = "WillBox bvba | Zavelstraat 21a | 9190 Stekene | Mail: [email protected] | Tel. +32 (0)3 293 52 50 | Gsm: + 32 (0)472 97 49 46 | "; xlWorkSheet.Cells[50, 1] = "Btw: BE 0655 906 872 | Fortis Bank: BE31 0017 8705 5955 | BIC GEBABEBB"; xlWorkSheet.get_Range("A49", "G50").WrapText = true; xlWorkSheet.get_Range("A49", "G50").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // afbeelding toevoegen xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\willboxlogo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 180, 62); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\more.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 180, 45, 101, 18); string path = @"c:/Willbox/Zendnota's/" + firmaNaam.ToLower() + "/"; try { // Determine whether the directory exists. if (Directory.Exists(path)) { } else { // Try to create the directory. DirectoryInfo di = Directory.CreateDirectory(path); } } catch (Exception e) { Console.WriteLine("The process failed: {0}", e.ToString()); MessageBox.Show("Er is iets fout gelopen bij de aanmaak van het Bestand, probeer het later opnieuw.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } lblInfo.Text = "Excel bestand gecreërd met de als naam: Zendnota " + DateTime.Now.ToString("yyyy-MM-dd HH mm") + " - " + firmaNaam.ToLower() + ".xls"; Laden.CloseForm(); xlWorkBook.SaveAs("c:\\Willbox\\Zendnota's\\" + firmaNaam.ToLower() + "\\Zendnota " + DateTime.Now.ToString("yyyy-MM-dd HH mm") + " - " + firmaNaam.ToLower() + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); //MessageBox.Show("Excel bestand gecreërd met de als naam: Zendnota " + DateTime.Now.ToString("yyyy-MM-dd HH mm") + " - " + firmaNaam.ToLower() + ".xls"); }
private void makeExcell() { Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { xlWorkSheet.Columns["A"].ColumnWidth = 2; xlWorkSheet.Columns["B"].ColumnWidth = 12; xlWorkSheet.Columns["C"].ColumnWidth = 6; xlWorkSheet.Columns["D"].ColumnWidth = 37; xlWorkSheet.Columns["E"].ColumnWidth = 11; xlWorkSheet.Columns["F"].ColumnWidth = 15; xlWorkSheet.get_Range("B33", "F40").RowHeight = 15; xlWorkSheet.get_Range("A7", "A12").RowHeight = 13; xlWorkSheet.get_Range("A43", "A48").RowHeight = 13; //pagina 2 xlWorkSheet.get_Range("A58", "A63").RowHeight = 13; xlWorkSheet.get_Range("A94", "A99").RowHeight = 13; xlWorkSheet.Columns["B"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Columns["D"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[7, 5] = "Offerte"; xlWorkSheet.get_Range("E7").Font.Italic = true; xlWorkSheet.get_Range("E7").Font.Bold = true; xlWorkSheet.get_Range("E7", "F7").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("E7", "F7").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("E7", "F7").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("E7", "F7").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.Cells[8, 6] = dgvOffertes.Rows[0].Cells["naam"].Value.ToString(); if (dgvOffertes.Rows[0].Cells["tav"].Value.ToString() != "Geen") { xlWorkSheet.Cells[9, 6] = "T.a.v. " + dgvOffertes.Rows[0].Cells["tav"].Value.ToString(); } string adres = dgvOffertes.Rows[0].Cells["adres"].Value.ToString().ToLower(); if (dgvOffertes.Rows[0].Cells["adres"].Value.ToString() != "") { adres = adres.First().ToString().ToUpper() + String.Join("", adres.Skip(1)); } xlWorkSheet.Cells[10, 6] = adres; xlWorkSheet.Cells[11, 6] = dgvOffertes.Rows[0].Cells["postcode"].Value.ToString() + " " + dgvOffertes.Rows[0].Cells["gemeente"].Value.ToString(); xlWorkSheet.Cells[12, 6] = dgvOffertes.Rows[0].Cells["land"].Value.ToString(); xlWorkSheet.Cells[11, 2] = "Klantnummer: " + klantnr; xlWorkSheet.Cells[12, 2] = "OfferteDatum: " + DateTime.Now.ToString("dd MMM yyyy"); xlWorkSheet.get_Range("B11").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B12").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int i = 8; i < 13; i++) { xlWorkSheet.get_Range("F" + i).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } xlWorkSheet.get_Range("B7", "F12").Font.Size = 10; xlWorkSheet.Cells[15, 1] = "Offertenr/Omschrijving"; xlWorkSheet.Cells[15, 5] = "Aantal"; xlWorkSheet.Cells[15, 6] = "Prijs/Eenheid"; xlWorkSheet.get_Range("A15", "F15").Font.Size = 14; xlWorkSheet.get_Range("A15", "F15").Font.Bold = true; xlWorkSheet.get_Range("A15", "F15").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A15", "F15").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A15", "F15").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A15", "F15").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; //OfferteGegevens int j = 17; int laatste = 17; for (int i = 0; i < dgvOffertes.RowCount - 1; i++) { LOOP: xlWorkSheet.Cells[j, 1] = "# Offertenr " + dgvOffertes.Rows[i].Cells["offertenr"].Value.ToString(); xlWorkSheet.get_Range("A" + j).Font.Bold = true; xlWorkSheet.Cells[j, 5] = dgvOffertes.Rows[i].Cells["aantal"].Value.ToString(); xlWorkSheet.Cells[j, 6] = (double.Parse(dgvOffertes.Rows[i].Cells["prijs"].Value.ToString()) / 1000); xlWorkSheet.get_Range("E" + j, "F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; if (Regex.Replace(dgvOffertes.Rows[i].Cells["omschrijving"].Value.ToString(), @"\s+", "") != "") { xlWorkSheet.Cells[j, 2] = "Omschrijving"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["omschrijving"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["ref"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Uw referentie"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["ref"].Value.ToString(); j++; } xlWorkSheet.Cells[j, 2] = "Fefco"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["fefco"].Value.ToString(); j++; xlWorkSheet.Cells[j, 2] = "Afmetingen (in mm)"; if (dgvOffertes.Rows[i].Cells["fefco"].Value.ToString() == "F110") { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString(); } else { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["hoogte"].Value.ToString(); } j++; if (dgvOffertes.Rows[i].Cells["kwaliteit"].Value.ToString() == "" || dgvOffertes.Rows[i].Cells["kwaliteit"].Value.ToString() == "0") { } else { xlWorkSheet.Cells[j, 2] = "Kwaliteit"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["kwaliteit"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString() != "Geen") { xlWorkSheet.Cells[j, 2] = "Bedrukking"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Leveringstermijn"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString(); j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige stansmeskost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige clichekost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } j++; if (j > 42 && j < 68) { xlWorkSheet.get_Range("A" + laatste, "F" + j).Cells.Clear(); xlWorkSheet.Cells[42, 6] = "Pagina 1 van 2"; j = 68; goto LOOP; } else { laatste = j; } //Kwaliteit2, aantal2, prijs2 if (dgvOffertes.Rows[i].Cells["kwaliteit2"].Value.ToString() == "" || (dgvOffertes.Rows[i].Cells["aantal2"].Value.ToString() == "0" || dgvOffertes.Rows[i].Cells["aantal2"].Value.ToString() == "") || (dgvOffertes.Rows[i].Cells["prijs2"].Value.ToString() == "" || dgvOffertes.Rows[i].Cells["prijs2"].Value.ToString() == "0")) { } else { xlWorkSheet.Cells[j, 1] = "# Offertenr " + dgvOffertes.Rows[i].Cells["offertenr"].Value.ToString(); xlWorkSheet.get_Range("A" + j).Font.Bold = true; xlWorkSheet.Cells[j, 5] = dgvOffertes.Rows[i].Cells["aantal2"].Value.ToString(); xlWorkSheet.Cells[j, 6] = (double.Parse(dgvOffertes.Rows[i].Cells["prijs2"].Value.ToString()) / 1000); xlWorkSheet.get_Range("E" + j, "F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; if (dgvOffertes.Rows[i].Cells["ref"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Uw referentie"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["ref"].Value.ToString(); j++; } xlWorkSheet.Cells[j, 2] = "Fefco"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["fefco"].Value.ToString(); j++; xlWorkSheet.Cells[j, 2] = "Afmetingen (in mm)"; if (dgvOffertes.Rows[i].Cells["fefco"].Value.ToString() == "F110") { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString(); } else { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["hoogte"].Value.ToString(); } j++; if (dgvOffertes.Rows[i].Cells["kwaliteit2"].Value.ToString() == "" || dgvOffertes.Rows[i].Cells["kwaliteit2"].Value.ToString() == "0") { } else { xlWorkSheet.Cells[j, 2] = "Kwaliteit"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["kwaliteit2"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString() != "Geen") { xlWorkSheet.Cells[j, 2] = "Bedrukking"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Leveringstermijn"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString(); j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige stansmeskost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige clichekost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } j++; } if (j > 42 && j < 68) { xlWorkSheet.get_Range("A" + laatste, "F" + j).Cells.Clear(); j = 68; goto LOOP; } else { laatste = j; } //Kwaliteit3, aantal3, prijs3 if (dgvOffertes.Rows[i].Cells["kwaliteit3"].Value.ToString() == "" || (dgvOffertes.Rows[i].Cells["aantal3"].Value.ToString() == "0" || dgvOffertes.Rows[i].Cells["aantal3"].Value.ToString() == "") || (dgvOffertes.Rows[i].Cells["prijs3"].Value.ToString() == "" || dgvOffertes.Rows[i].Cells["prijs3"].Value.ToString() == "0")) { } else { xlWorkSheet.Cells[j, 1] = "# Offertenr " + dgvOffertes.Rows[i].Cells["offertenr"].Value.ToString(); xlWorkSheet.get_Range("A" + j).Font.Bold = true; xlWorkSheet.Cells[j, 5] = dgvOffertes.Rows[i].Cells["aantal3"].Value.ToString(); xlWorkSheet.Cells[j, 6] = (double.Parse(dgvOffertes.Rows[i].Cells["prijs3"].Value.ToString()) / 1000); xlWorkSheet.get_Range("E" + j, "F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; if (dgvOffertes.Rows[i].Cells["ref"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Uw referentie"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["ref"].Value.ToString(); j++; } xlWorkSheet.Cells[j, 2] = "Fefco"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["fefco"].Value.ToString(); j++; xlWorkSheet.Cells[j, 2] = "Afmetingen (in mm)"; if (dgvOffertes.Rows[i].Cells["fefco"].Value.ToString() == "F110") { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString(); } else { xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["lengte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["breedte"].Value.ToString() + " X " + dgvOffertes.Rows[i].Cells["hoogte"].Value.ToString(); } j++; if (dgvOffertes.Rows[i].Cells["kwaliteit3"].Value.ToString() == "" || dgvOffertes.Rows[i].Cells["kwaliteit3"].Value.ToString() == "0") { } else { xlWorkSheet.Cells[j, 2] = "Kwaliteit"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["kwaliteit3"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString() != "Geen") { xlWorkSheet.Cells[j, 2] = "Bedrukking"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["Bedrukking"].Value.ToString(); j++; } if (dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString() != "") { xlWorkSheet.Cells[j, 2] = "Leveringstermijn"; xlWorkSheet.Cells[j, 4] = dgvOffertes.Rows[i].Cells["leveringstermijn"].Value.ToString(); j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige stansmeskost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["stansmeskost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } if (Regex.Replace(dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(), @"\s+", "") != "0") { xlWorkSheet.Cells[j, 2] = "Eenmalige clichekost"; xlWorkSheet.Cells[j, 6] = dgvOffertes.Rows[i].Cells["clichekost"].Value.ToString(); xlWorkSheet.get_Range("F" + j).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; j++; } j++; } if (j > 42 && j < 68) { xlWorkSheet.get_Range("A" + laatste, "F" + j).Cells.Clear(); xlWorkSheet.Cells[42, 6] = "Pagina 1 van 2"; j = 68; goto LOOP; } else { laatste = j; } } //Extra pagina if (j > 41) { xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\willboxlogo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 745, 180, 62); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\more.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 180, 790, 101, 18); xlWorkSheet.Cells[58, 5] = "Offerte"; xlWorkSheet.get_Range("E58").Font.Italic = true; xlWorkSheet.get_Range("E58").Font.Bold = true; xlWorkSheet.get_Range("E58", "F58").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("E58", "F58").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("E58", "F58").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("E58", "F58").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.Cells[59, 6] = dgvOffertes.Rows[0].Cells["naam"].Value.ToString(); if (dgvOffertes.Rows[0].Cells["tav"].Value.ToString() != "Geen") { xlWorkSheet.Cells[60, 6] = "T.a.v. " + dgvOffertes.Rows[0].Cells["tav"].Value.ToString(); } xlWorkSheet.Cells[61, 6] = dgvOffertes.Rows[0].Cells["adres"].Value.ToString().ToLower(); xlWorkSheet.Cells[62, 6] = dgvOffertes.Rows[0].Cells["postcode"].Value.ToString() + " " + dgvOffertes.Rows[0].Cells["gemeente"].Value.ToString(); xlWorkSheet.Cells[63, 6] = dgvOffertes.Rows[0].Cells["land"].Value.ToString(); xlWorkSheet.Cells[62, 2] = "Klantnummer: " + klantnr; xlWorkSheet.Cells[63, 2] = "OfferteDatum: " + DateTime.Now.ToString("dd MMM yyyy"); xlWorkSheet.get_Range("B63").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("B64").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int k = 58; k < 64; k++) { xlWorkSheet.get_Range("F" + k).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } xlWorkSheet.get_Range("B58", "F64").Font.Size = 10; xlWorkSheet.Cells[66, 1] = "Offertenr/Omschrijving"; xlWorkSheet.Cells[66, 5] = "Aantal"; xlWorkSheet.Cells[66, 6] = "Prijs/Eenheid"; xlWorkSheet.Cells[93, 6] = "Pagina 2 van 2"; xlWorkSheet.get_Range("A66", "F66").Font.Size = 14; xlWorkSheet.get_Range("A66", "F66").Font.Bold = true; xlWorkSheet.get_Range("A66", "F66").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A66", "F66").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A66", "F66").Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A66", "F66").Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d; xlWorkSheet.get_Range("A94", "F94").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A94", "F94").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A101", "F101").MergeCells = true; xlWorkSheet.get_Range("A102", "F102").MergeCells = true; xlWorkSheet.Cells[94, 1] = "Leveringsvoorwaarden : Franco vanaf 500 euro (<45 euro)"; xlWorkSheet.Cells[95, 1] = "Km heffing: 0,8%"; xlWorkSheet.Cells[96, 1] = "Betaling : 30 dagen netto"; xlWorkSheet.Cells[97, 1] = "Geldigheidsduur offerte : 30 dagen"; xlWorkSheet.Cells[98, 1] = "Alle prijzen in EUR ex. BTW"; xlWorkSheet.Cells[99, 1] = "Algemene verkoopsvoorwaarden : www.willbox.be"; xlWorkSheet.get_Range("A94", "A99").Font.Italic = true; xlWorkSheet.get_Range("A94", "A99").Font.Size = 9; xlWorkSheet.get_Range("A101", "F102").Font.Size = 7; xlWorkSheet.Cells[101, 1] = "WillBox bvba | Zavelstraat 21a | 9190 Stekene | Mail: [email protected] | Tel. +32 (0)3 293 52 50 | Gsm: + 32 (0)472 97 49 46 | "; xlWorkSheet.Cells[102, 1] = "Btw: BE 0655 906 872 | Fortis Bank: BE31 0017 8705 5955 | BIC GEBABEBB"; xlWorkSheet.get_Range("A101", "F102").WrapText = true; xlWorkSheet.get_Range("A101", "F102").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } //xlWorkSheet.Cells[34, 2] = "Leveringstermijn: " + txtLeveringsTermijn.Text; xlWorkSheet.get_Range("A43", "F43").Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); xlWorkSheet.get_Range("A43", "F43").Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3d; xlWorkSheet.get_Range("A50", "F50").MergeCells = true; xlWorkSheet.get_Range("A51", "F51").MergeCells = true; xlWorkSheet.Cells[43, 1] = "Leveringsvoorwaarden : Franco vanaf 500 euro (<45 euro)"; xlWorkSheet.Cells[44, 1] = "Km heffing: 0,8%"; xlWorkSheet.Cells[45, 1] = "Betaling : 30 dagen netto"; xlWorkSheet.Cells[46, 1] = "Geldigheidsduur offerte : 30 dagen"; xlWorkSheet.Cells[47, 1] = "Alle prijzen in EUR ex. BTW"; xlWorkSheet.Cells[48, 1] = "Algemene verkoopsvoorwaarden : www.willbox.be"; xlWorkSheet.get_Range("A43", "A48").Font.Italic = true; xlWorkSheet.get_Range("A43", "A48").Font.Size = 9; xlWorkSheet.get_Range("A50", "F51").Font.Size = 7; xlWorkSheet.Cells[50, 1] = "WillBox bvba | Zavelstraat 21a | 9190 Stekene | Mail: [email protected] | Tel. +32 (0)3 293 52 50 | Gsm: + 32 (0)472 97 49 46 | "; xlWorkSheet.Cells[51, 1] = "Btw: BE 0655 906 872 | Fortis Bank: BE31 0017 8705 5955 | BIC GEBABEBB"; xlWorkSheet.get_Range("A50", "F51").WrapText = true; xlWorkSheet.get_Range("A50", "F51").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // afbeelding toevoegen xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\willboxlogo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 180, 62); xlWorkSheet.Shapes.AddPicture("C:\\willbox\\data\\more.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 180, 45, 101, 18); string path = @"c:/Willbox/Offertes/" + firmaNaam.ToLower() + "/"; try { // Determine whether the directory exists. if (Directory.Exists(path)) { } else { // Try to create the directory. DirectoryInfo di = Directory.CreateDirectory(path); } } catch (Exception e) { Console.WriteLine("The process failed: {0}", e.ToString()); MessageBox.Show("Er is iets fout gelopen bij de aanmaak van het Bestand, probeer het later opnieuw.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } Laden.CloseForm(); xlWorkBook.SaveAs("c:\\Willbox\\Offertes\\" + firmaNaam.ToLower() + "\\Offerenr " + DateTime.Now.ToString("yyyy-MM-dd HH mm") + " - " + firmaNaam.ToLower() + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); lblInfo.Text = "Excel bestand gecreërd met de als naam: Offerenr " + DateTime.Now.ToString("yyyy - MM - dd HH mm") + " - " + firmaNaam.ToLower() + ".xls"; //MessageBox.Show("Excel bestand gecreërd met de als naam: Offerenr " + DateTime.Now.ToString("yyyy-MM-dd HH mm") + " - " + firmaNaam.ToLower() + ".xls"); } catch { lblInfo.Text = "Er is iets fout gelopen bij de aanmaak van het Bestand, probeer het later opnieuw."; //MessageBox.Show("Er is iets fout gelopen bij de aanmaak van het Bestand, probeer het later opnieuw.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Laden.CloseForm(); } }