private void devisToolStripMenuItem_Click(object sender, EventArgs e) { this.Hide(); Devis devis = new Devis(); devis.Show(); }
private void btSend_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(Directory.GetCurrentDirectory() + @"\ExempleDevis.xlsx"); Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; DirectoryInfo dirBeforeAppli = Directory.GetParent(Convert.ToString(Directory.GetParent(Convert.ToString(Directory.GetParent(Convert.ToString(Directory.GetParent(Directory.GetCurrentDirectory()))))))); Directory.CreateDirectory(dirBeforeAppli + @"\Devis" + DateTime.Today.Year.ToString()); //Compter le nombre de lignes Microsoft.Office.Interop.Excel.Range userRange = x.UsedRange; int countRecords = userRange.Rows.Count; int add = countRecords + 1; x.Cells[add, 1] = "Ligne Total " + countRecords; //Mettre en gras une ligne //x.Range["A1"].EntireRow.Font.Bold = true; //Mettre en gras une cell //x.Range["A1"].Font.Bold = true; //Num devis +1 et Excel x.Range["F3"].Value = Program.outils.getNumDevis(); Program.outils.setNumDevis(Convert.ToString(Program.outils.getNumDevis() + 1)); //Date du jour x.Range["E4"].Value = DateTime.Today.ToShortDateString(); //Adresse Program.outils.getConnection().Open(); string requete = "Select [LigneAdr1] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; string requete1 = "Select [LigneAdr2] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; string requete2 = "Select [LigneAdr3] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; string requete3 = "Select [LigneAdr4] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; string requete4 = "Select [CodePostal] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; string requete5 = "Select [Ville] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; OleDbCommand cmd = new OleDbCommand(requete, Program.outils.getConnection()); OleDbDataReader dr = cmd.ExecuteReader(); OleDbCommand cmd1 = new OleDbCommand(requete1, Program.outils.getConnection()); OleDbDataReader dr1 = cmd1.ExecuteReader(); OleDbCommand cmd2 = new OleDbCommand(requete2, Program.outils.getConnection()); OleDbDataReader dr2 = cmd2.ExecuteReader(); OleDbCommand cmd3 = new OleDbCommand(requete3, Program.outils.getConnection()); OleDbDataReader dr3 = cmd3.ExecuteReader(); OleDbCommand cmd4 = new OleDbCommand(requete4, Program.outils.getConnection()); OleDbDataReader dr4 = cmd4.ExecuteReader(); OleDbCommand cmd5 = new OleDbCommand(requete5, Program.outils.getConnection()); OleDbDataReader dr5 = cmd5.ExecuteReader(); while (dr.Read() && dr1.Read() && dr2.Read() && dr3.Read() && dr4.Read() && dr5.Read()) { if (dr2[0].ToString() == "" && dr3[0].ToString() == "") { x.Range["D8"].Value = dr[0].ToString(); x.Range["D9"].Value = dr1[0].ToString(); x.Range["D10"].Value = dr4[0].ToString() + " " + dr5[0].ToString(); } else if (dr3[0].ToString() == "") { x.Range["D8"].Value = dr[0].ToString(); x.Range["D9"].Value = dr1[0].ToString(); x.Range["D10"].Value = dr2[0].ToString(); x.Range["D11"].Value = dr4[0].ToString() + " " + dr5[0].ToString(); } else if (dr3[0].ToString() != "") { x.Range["D7"].Font.Bold = true; x.Range["D8"].Font.Bold = false; x.Range["D7"].Value = dr[0].ToString(); x.Range["D8"].Value = dr1[0].ToString(); x.Range["D9"].Value = dr2[0].ToString(); x.Range["D10"].Value = dr3[0].ToString(); x.Range["D11"].Value = dr4[0].ToString() + " " + dr5[0].ToString(); } } Program.outils.getConnection().Close(); switch (nbDate) { case 1: #region Suppression lignes en trop //Suppression des lignes en trop x.Range["A19"].Value = ""; x.Range["D19"].Value = ""; x.Range["D20"].Value = ""; x.Range["D20"].Value = ""; x.Range["B19"].Value = ""; x.Range["B20"].Value = ""; x.Range["E19"].Value = ""; x.Range["E20"].Value = ""; x.Range["A22"].Value = ""; x.Range["D22"].Value = ""; x.Range["D23"].Value = ""; x.Range["B22"].Value = ""; x.Range["B23"].Value = ""; x.Range["E22"].Value = ""; x.Range["E23"].Value = ""; x.Range["A25"].Value = ""; x.Range["D25"].Value = ""; x.Range["D26"].Value = ""; x.Range["B25"].Value = ""; x.Range["B26"].Value = ""; x.Range["E25"].Value = ""; x.Range["E26"].Value = ""; x.Range["A28"].Value = ""; x.Range["D28"].Value = ""; x.Range["D29"].Value = ""; x.Range["B28"].Value = ""; x.Range["B29"].Value = ""; x.Range["E28"].Value = ""; x.Range["E29"].Value = ""; x.Range["A31"].Value = ""; x.Range["D31"].Value = ""; x.Range["D32"].Value = ""; x.Range["B31"].Value = ""; x.Range["B32"].Value = ""; x.Range["E31"].Value = ""; x.Range["E32"].Value = ""; #endregion break; case 2: #region Suppression lignes en trop //Suppression des lignes en trop x.Range["A22"].Value = ""; x.Range["D22"].Value = ""; x.Range["D23"].Value = ""; x.Range["B22"].Value = ""; x.Range["B23"].Value = ""; x.Range["E22"].Value = ""; x.Range["E23"].Value = ""; x.Range["A25"].Value = ""; x.Range["D25"].Value = ""; x.Range["D26"].Value = ""; x.Range["B25"].Value = ""; x.Range["B26"].Value = ""; x.Range["E25"].Value = ""; x.Range["E26"].Value = ""; x.Range["A28"].Value = ""; x.Range["D28"].Value = ""; x.Range["D29"].Value = ""; x.Range["B28"].Value = ""; x.Range["B29"].Value = ""; x.Range["E28"].Value = ""; x.Range["E29"].Value = ""; x.Range["A31"].Value = ""; x.Range["D31"].Value = ""; x.Range["D32"].Value = ""; x.Range["B31"].Value = ""; x.Range["B32"].Value = ""; x.Range["E31"].Value = ""; x.Range["E32"].Value = ""; #endregion break; case 3: #region Suppression lignes en trop //Suppression des lignes en trop x.Range["A25"].Value = ""; x.Range["D25"].Value = ""; x.Range["D26"].Value = ""; x.Range["B25"].Value = ""; x.Range["B26"].Value = ""; x.Range["E25"].Value = ""; x.Range["E26"].Value = ""; x.Range["A28"].Value = ""; x.Range["D28"].Value = ""; x.Range["D29"].Value = ""; x.Range["B28"].Value = ""; x.Range["B29"].Value = ""; x.Range["E28"].Value = ""; x.Range["E29"].Value = ""; x.Range["A31"].Value = ""; x.Range["D31"].Value = ""; x.Range["D32"].Value = ""; x.Range["B31"].Value = ""; x.Range["B32"].Value = ""; x.Range["E31"].Value = ""; x.Range["E32"].Value = ""; #endregion break; case 4: #region Suppression lignes en trop //Suppression des lignes en trop x.Range["A28"].Value = ""; x.Range["D28"].Value = ""; x.Range["D29"].Value = ""; x.Range["B28"].Value = ""; x.Range["B29"].Value = ""; x.Range["E28"].Value = ""; x.Range["E29"].Value = ""; x.Range["A31"].Value = ""; x.Range["D31"].Value = ""; x.Range["D32"].Value = ""; x.Range["B31"].Value = ""; x.Range["B32"].Value = ""; x.Range["E31"].Value = ""; x.Range["E32"].Value = ""; #endregion break; case 5: #region Suppression lignes en trop //Suppression des lignes en trop x.Range["A31"].Value = ""; x.Range["D31"].Value = ""; x.Range["D32"].Value = ""; x.Range["B31"].Value = ""; x.Range["B32"].Value = ""; x.Range["E31"].Value = ""; x.Range["E32"].Value = ""; #endregion break; } while (nbDate > 0) { switch (nbDate) { case 1: x.Range["A16"].Value = dtpDate1.Value.ToShortDateString(); x.Range["D16"].Value = txbxPlage1.Text; x.Range["D17"].Value = txbxAccomp1.Text; nbDate -= 1; break; case 2: x.Range["A19"].Value = dtpDate2.Value.ToShortDateString(); x.Range["D19"].Value = txbxPlage2.Text; x.Range["D20"].Value = txbxAccomp2.Text; nbDate -= 1; break; case 3: x.Range["A22"].Value = dtpDate3.Value.ToShortDateString(); x.Range["D22"].Value = txbxPlage3.Text; x.Range["D23"].Value = txbxAccomp3.Text; nbDate -= 1; break; case 4: x.Range["A25"].Value = dtpDate4.Value.ToShortDateString(); x.Range["D25"].Value = txbxPlage4.Text; x.Range["D26"].Value = txbxAccomp4.Text; nbDate -= 1; break; case 5: x.Range["A28"].Value = dtpDate5.Value.ToShortDateString(); x.Range["D28"].Value = txbxPlage5.Text; x.Range["D29"].Value = txbxAccomp5.Text; nbDate -= 1; break; case 6: x.Range["A31"].Value = dtpDate6.Value.ToShortDateString(); x.Range["D31"].Value = txbxPlage6.Text; x.Range["D32"].Value = txbxAccomp6.Text; nbDate -= 1; break; } } string nomFichier = dirBeforeAppli + @"\Devis" + DateTime.Today.Year.ToString() + @"\" + "VEUILLEZ INDIQUER LA VILLE"; Program.outils.getConnection().Open(); requete5 = "Select [Ville] from DevisAdresse where [Ville] = \"" + cbxVille.Text + "\";"; cmd5.CommandText = requete5; dr5 = cmd5.ExecuteReader(); while (dr5.Read()) { nomFichier = dirBeforeAppli + @"\Devis" + DateTime.Today.Year.ToString() + @"\5860" + (DateTime.Today.Year - 2000) + "-" + Convert.ToString(Program.outils.getNumDevis() - 1) + " DEVIS " + dr5[0].ToString(); } Program.outils.getConnection().Close(); sheet.Close(true, nomFichier, Type.Missing); excel.Quit(); System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel"); foreach (System.Diagnostics.Process p in process) { if (!string.IsNullOrEmpty(p.ProcessName)) { p.Kill(); } } //Envoi Mail if (txbxMail.Text != "") { Program.outils.sendMail(txbxMail.Text, Convert.ToString(Program.outils.getNumDevis() - 1) + " DEVIS " + cbxVille.Text); } this.Hide(); Devis devis = new Devis(); devis.Show(); }