private void btSend_Click(object sender, EventArgs e)
{
if (txbxBDC.Text == "")
{
MessageBox.Show("Merci de remplir le numéro de bon de commande.", "Veuillez indiquer le numéro de BDC :", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
else
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(Directory.GetCurrentDirectory() + @"\ExempleFacture.xls");
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 + @"\Facture" + 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 Facture +1 et Excel
if (Program.outils.getNumFacture() < 100)
{
x.Range["F3"].Value = "0" + Program.outils.getNumFacture();
}
else
{
x.Range["F3"].Value = Program.outils.getNumFacture();
}
Program.outils.setNumFacture(Convert.ToString(Program.outils.getNumFacture() + 1));
//Numéro BDC
x.Range["A6"].Value = "Selon votre bon de commande n° " + txbxBDC.Text;
//Date du jour
x.Range["E4"].Value = DateTime.Today.ToShortDateString();
//Adresse
Program.outils.getConnection().Open();
string requete = "Select [LigneAdr1] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
string requete1 = "Select [LigneAdr2] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
string requete2 = "Select [LigneAdr3] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
string requete3 = "Select [LigneAdr4] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
string requete4 = "Select [CodePostal] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
string requete5 = "Select [Ville] from FactureAdresse 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["A18"].Value = "";
x.Range["D18"].Value = "";
x.Range["D19"].Value = "";
x.Range["D19"].Value = "";
x.Range["B18"].Value = "";
x.Range["B19"].Value = "";
x.Range["E18"].Value = "";
x.Range["E19"].Value = "";
x.Range["A21"].Value = "";
x.Range["D21"].Value = "";
x.Range["D22"].Value = "";
x.Range["B21"].Value = "";
x.Range["B22"].Value = "";
x.Range["E21"].Value = "";
x.Range["E22"].Value = "";
x.Range["A24"].Value = "";
x.Range["D24"].Value = "";
x.Range["D25"].Value = "";
x.Range["B24"].Value = "";
x.Range["B25"].Value = "";
x.Range["E24"].Value = "";
x.Range["E25"].Value = "";
x.Range["A27"].Value = "";
x.Range["D27"].Value = "";
x.Range["D28"].Value = "";
x.Range["B27"].Value = "";
x.Range["B28"].Value = "";
x.Range["E27"].Value = "";
x.Range["E28"].Value = "";
x.Range["A30"].Value = "";
x.Range["D30"].Value = "";
x.Range["D31"].Value = "";
x.Range["B30"].Value = "";
x.Range["B31"].Value = "";
x.Range["E30"].Value = "";
x.Range["E31"].Value = "";
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 2:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A21"].Value = "";
x.Range["D21"].Value = "";
x.Range["D22"].Value = "";
x.Range["B21"].Value = "";
x.Range["B22"].Value = "";
x.Range["E21"].Value = "";
x.Range["E22"].Value = "";
x.Range["A24"].Value = "";
x.Range["D24"].Value = "";
x.Range["D25"].Value = "";
x.Range["B24"].Value = "";
x.Range["B25"].Value = "";
x.Range["E24"].Value = "";
x.Range["E25"].Value = "";
x.Range["A27"].Value = "";
x.Range["D27"].Value = "";
x.Range["D28"].Value = "";
x.Range["B27"].Value = "";
x.Range["B28"].Value = "";
x.Range["E27"].Value = "";
x.Range["E28"].Value = "";
x.Range["A30"].Value = "";
x.Range["D30"].Value = "";
x.Range["D31"].Value = "";
x.Range["B30"].Value = "";
x.Range["B31"].Value = "";
x.Range["E30"].Value = "";
x.Range["E31"].Value = "";
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 3:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A24"].Value = "";
x.Range["D24"].Value = "";
x.Range["D25"].Value = "";
x.Range["B24"].Value = "";
x.Range["B25"].Value = "";
x.Range["E24"].Value = "";
x.Range["E25"].Value = "";
x.Range["A27"].Value = "";
x.Range["D27"].Value = "";
x.Range["D28"].Value = "";
x.Range["B27"].Value = "";
x.Range["B28"].Value = "";
x.Range["E27"].Value = "";
x.Range["E28"].Value = "";
x.Range["A30"].Value = "";
x.Range["D30"].Value = "";
x.Range["D31"].Value = "";
x.Range["B30"].Value = "";
x.Range["B31"].Value = "";
x.Range["E30"].Value = "";
x.Range["E31"].Value = "";
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 4:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A27"].Value = "";
x.Range["D27"].Value = "";
x.Range["D28"].Value = "";
x.Range["B27"].Value = "";
x.Range["B28"].Value = "";
x.Range["E27"].Value = "";
x.Range["E28"].Value = "";
x.Range["A30"].Value = "";
x.Range["D30"].Value = "";
x.Range["D31"].Value = "";
x.Range["B30"].Value = "";
x.Range["B31"].Value = "";
x.Range["E30"].Value = "";
x.Range["E31"].Value = "";
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 5:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A30"].Value = "";
x.Range["D30"].Value = "";
x.Range["D31"].Value = "";
x.Range["B30"].Value = "";
x.Range["B31"].Value = "";
x.Range["E30"].Value = "";
x.Range["E31"].Value = "";
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 6:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A33"].Value = "";
x.Range["D33"].Value = "";
x.Range["D34"].Value = "";
x.Range["B33"].Value = "";
x.Range["B34"].Value = "";
x.Range["E33"].Value = "";
x.Range["E34"].Value = "";
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
case 7:
#region Suppression lignes en trop
//Suppression des lignes en trop
x.Range["A36"].Value = "";
x.Range["D36"].Value = "";
x.Range["D37"].Value = "";
x.Range["B36"].Value = "";
x.Range["B37"].Value = "";
x.Range["E36"].Value = "";
x.Range["E37"].Value = "";
#endregion
break;
}
while (nbDate > 0)
{
switch (nbDate)
{
case 1:
x.Range["A15"].Value = dtpDate1.Value.ToShortDateString();
x.Range["D15"].Value = txbxPlage1.Text;
x.Range["D16"].Value = txbxAccomp1.Text;
nbDate -= 1;
break;
case 2:
x.Range["A18"].Value = dtpDate2.Value.ToShortDateString();
x.Range["D18"].Value = txbxPlage2.Text;
x.Range["D19"].Value = txbxAccomp2.Text;
nbDate -= 1;
break;
case 3:
x.Range["A21"].Value = dtpDate3.Value.ToShortDateString();
x.Range["D21"].Value = txbxPlage3.Text;
x.Range["D22"].Value = txbxAccomp3.Text;
nbDate -= 1;
break;
case 4:
x.Range["A24"].Value = dtpDate4.Value.ToShortDateString();
x.Range["D24"].Value = txbxPlage4.Text;
x.Range["D25"].Value = txbxAccomp4.Text;
nbDate -= 1;
break;
case 5:
x.Range["A27"].Value = dtpDate5.Value.ToShortDateString();
x.Range["D27"].Value = txbxPlage5.Text;
x.Range["D28"].Value = txbxAccomp5.Text;
nbDate -= 1;
break;
case 6:
x.Range["A30"].Value = dtpDate6.Value.ToShortDateString();
x.Range["D30"].Value = txbxPlage6.Text;
x.Range["D31"].Value = txbxAccomp6.Text;
nbDate -= 1;
break;
case 7:
x.Range["A33"].Value = dtpDate7.Value.ToShortDateString();
x.Range["D33"].Value = txbxPlage7.Text;
x.Range["D34"].Value = txbxAccomp7.Text;
nbDate -= 1;
break;
case 8:
x.Range["A36"].Value = dtpDate8.Value.ToShortDateString();
x.Range["D36"].Value = txbxPlage8.Text;
x.Range["D37"].Value = txbxAccomp8.Text;
nbDate -= 1;
break;
}
}
string nomFichier = dirBeforeAppli + @"\Facture" + DateTime.Today.Year.ToString() + @"\" + "VEUILLEZ INDIQUER LA VILLE";
Program.outils.getConnection().Open();
requete5 = "Select [Ville] from FactureAdresse where [Ville] = \"" + cbxVille.Text + "\";";
cmd5.CommandText = requete5;
dr5 = cmd5.ExecuteReader();
while (dr5.Read())
{
if (Program.outils.getNumFacture() < 100)
{
nomFichier = dirBeforeAppli + @"\Facture" + DateTime.Today.Year.ToString() + @"\5860" + (DateTime.Today.Year - 2000) + "-0" + Convert.ToString(Program.outils.getNumFacture() - 1) + " " + dr5[0].ToString();
}
else
{
nomFichier = dirBeforeAppli + @"\Facture" + DateTime.Today.Year.ToString() + @"\5860" + (DateTime.Today.Year - 2000) + "-" + Convert.ToString(Program.outils.getNumFacture() - 1) + " " + 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();
}
}
this.Hide();
Facture facture = new Facture();
facture.Show();
}
}
