private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
CodeFinder cf = new CodeFinder(prefaceNP, "Historique-s");
/* xlApp = new Excel.ApplicationClass();
* xlApp.Visible = true;
* xlApp.DisplayAlerts = false;
* xlWorkBook = xlApp.Workbooks.Open(prefaceNP, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false);
*
* Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique-s");*/
Excel.Range range = cf.XlsWorkSheet.UsedRange;
Excel.Range rangex1 = cf.XlsWorkSheet.Cells[1, 4] as Excel.Range;
Excel.Range rangex2 = cf.XlsWorkSheet.Cells[1, 5] as Excel.Range;
Excel.Range rangex3 = cf.XlsWorkSheet.Cells[1, 6] as Excel.Range;
//rangex1c.EntireColumn.Copy(misValue);
//rangex1.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
//rangex1c.EntireColumn.Copy(misValue);
//rangex2.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
//rangex1c.EntireColumn.Copy(misValue);
//rangex3.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex1.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex1.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex2.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex2.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex3.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangex3.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
Excel.Range rangex1c = cf.XlsWorkSheet.UsedRange.get_Range("EU1", "EV1") as Excel.Range;
rangex1c.EntireColumn.Copy(cf.XlsWorkSheet.UsedRange.get_Range("D1", "E1").EntireColumn);
rangex1c.EntireColumn.Copy(cf.XlsWorkSheet.UsedRange.get_Range("G1", "H1").EntireColumn);
rangex1c.EntireColumn.Copy(cf.XlsWorkSheet.UsedRange.get_Range("J1", "K1").EntireColumn);
Excel.Worksheet xlWorkSheet2 = cf.XlsWorkBook.Worksheets["Hist.Refer"] as Excel.Worksheet;
Excel.Range rangeC = xlWorkSheet2.Cells[1, 4] as Excel.Range;
Excel.Range rangeD = xlWorkSheet2.Cells[1, 5] as Excel.Range;
Excel.Range rangeE = xlWorkSheet2.Cells[1, 6] as Excel.Range;
rangeC.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangeC.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangeD.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangeD.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangeE.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
rangeE.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, misValue);
Excel.Range rangeCc = xlWorkSheet2.Cells[1, 3] as Excel.Range;
Excel.Range rangeCc2 = xlWorkSheet2.Cells[1, 4] as Excel.Range;
Excel.Range rangeCc3 = xlWorkSheet2.Cells[1, 5] as Excel.Range;
Excel.Range rangeDc = xlWorkSheet2.Cells[1, 6] as Excel.Range;
Excel.Range rangeDc2 = xlWorkSheet2.Cells[1, 7] as Excel.Range;
Excel.Range rangeDc3 = xlWorkSheet2.Cells[1, 8] as Excel.Range;
Excel.Range rangeEc = xlWorkSheet2.Cells[1, 9] as Excel.Range;
Excel.Range rangeEc2 = xlWorkSheet2.Cells[1, 10] as Excel.Range;
Excel.Range rangeEc3 = xlWorkSheet2.Cells[1, 11] as Excel.Range;
rangeCc.EntireColumn.Copy(rangeCc2.EntireColumn);
rangeCc.EntireColumn.Copy(rangeCc3.EntireColumn);
rangeDc.EntireColumn.Copy(rangeDc2.EntireColumn);
rangeDc.EntireColumn.Copy(rangeDc3.EntireColumn);
rangeEc.EntireColumn.Copy(rangeEc2.EntireColumn);
rangeEc.EntireColumn.Copy(rangeEc3.EntireColumn);
//Excel.Worksheet WorkSheetPreface = xlWorkBook.Worksheets["Hist.Preface"] as Excel.Worksheet;
Excel.Range rangex1cx1 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 4] as Excel.Range;
Excel.Range rangex1cx2 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 5] as Excel.Range;
Excel.Range rangex2cx1 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 7] as Excel.Range;
Excel.Range rangex2cx2 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 8] as Excel.Range;
Excel.Range rangex3cx1 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 10] as Excel.Range;
Excel.Range rangex3cx2 = cf.XlsWorkSheet.Cells[range.Rows.Count - 1, 11] as Excel.Range;
rangex1cx1.Value2 = "";
rangex1cx2.Value2 = "";
rangex2cx1.Value2 = "";
rangex2cx2.Value2 = "";
rangex3cx1.Value2 = "";
rangex3cx2.Value2 = "";
//tester EE pour Histo.refer//et parcourir historique
Excel.Range rangeRefer = xlWorkSheet2.UsedRange;
Excel.Range rangeHistorique = cf.XlsWorkSheet.UsedRange;
//petite corr
object[,] valuesRefer = (object[, ])rangeRefer.Value2;
object[,] valuesHistorique = (object[, ])rangeHistorique.Value2;
int rowCnt = 0;
int rowHistoCnt = 0;
string nomCol = "";
for (rowCnt = 1; rowCnt <= rangeRefer.Rows.Count; rowCnt++)
{
string valuecellabs = Convert.ToString(valuesRefer[rowCnt, 1]);
if (valuecellabs != "" && valuecellabs != "D" && valuecellabs != "D1" && valuecellabs != "d")
{
nomCol = valuecellabs;
for (rowHistoCnt = 1; rowHistoCnt <= rangeHistorique.Rows.Count; rowHistoCnt++)
{
string valuecellHisto = Convert.ToString(valuesHistorique[rowHistoCnt, 2]);
if (valuecellHisto == nomCol)
{
Excel.Range cellcopie = cf.XlsWorkSheet.Cells[rowHistoCnt, 3] as Excel.Range;
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 4]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 5]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 6]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 7]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 8]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 9]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 10]);
cellcopie.Copy(cf.XlsWorkSheet.Cells[rowHistoCnt, 11]);
}
}
}
}
///////////////////////////////Parcourir tous les cellule ergodiaue////////////////////////////////
//string str;
//int rCnt = 0;
//int cCnt = 0;
//for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
//{
// for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
// {
// str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
// MessageBox.Show(str);
// }
//}
///////////////////////////////fermer EXCEL automatiquement apres modification?//////////////////////
cf.XlsWorkSheet.SaveAs(prefaceNP, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
cf.XlsApp.DisplayAlerts = true;
cf.XlsWorkBook.Close(true, misValue, misValue);
cf.XlsApp.Quit();
//MessageBox.Show("jobs done!");
releaseObject(cf.XlsWorkSheet);
releaseObject(cf.XlsWorkBook);
releaseObject(cf.XlsApp);
}
public Form3()
{
InitializeComponent();
cf = new CodeFinder("D:\\tests\\PrefaceNP.xls", "Historique");
}