public DataRetrieve() { DL_Self_Weight = new VDataCollection(); DL_Deck_Wet_Conc = new VDataCollection(); DL_Deck_Dry_Conc = new VDataCollection(); DL_Self_Deck = new VDataCollection(); SIDL_Crash_Barrier = new VDataCollection(); SIDL_Wearing = new VDataCollection(); LL_Moving = new VDataCollection(); }
public void Insert_Values_into_Excel_Long_Girder() { Excel.Application myExcelApp; Excel.Workbooks myExcelWorkbooks; Excel.Workbook myExcelWorkbook; object misValue = System.Reflection.Missing.Value; myExcelApp = new Excel.Application(); myExcelApp.Visible = true; //myExcelApp.Visible = false; myExcelWorkbooks = myExcelApp.Workbooks; String fileName = Excel_File_Name; // set this to your file you want //myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet; Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["8. Summ Force"]; String cellFormulaAsString = myExcelWorksheet.get_Range("A1", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string. int cel_index = 7; VDataCollection vdc = dr.DL_Self_Weight; #region DL_Self_Weight for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Self_Weight cel_index = 16; vdc = dr.DL_Deck_Wet_Conc; #region DL_Deck_Wet_Conc for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Deck_Wet_Conc cel_index = 25; vdc = dr.DL_Deck_Dry_Conc; #region DL_Deck_Dry_Conc for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Deck_Dry_Conc cel_index = 35; vdc = dr.DL_Self_Deck; #region DL_Self_Deck for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion DL_Self_Deck cel_index = 48; vdc = dr.SIDL_Crash_Barrier; #region SIDL_Crash_Barrier for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion SIDL_Crash_Barrier cel_index = 61; vdc = dr.SIDL_Wearing; #region SIDL_Wearing for (int i = 0; i < vdc.Count; i++) { myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support; myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening; myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8; myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4; myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8; myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid; } #endregion SIDL_Wearing cel_index = 75; vdc = dr.LL_Moving; #region LL_Moving for (int i = 0; i < vdc.Count; i++) { if (vdc[i].LoadText != "") { myExcelWorksheet.get_Range("L" + (cel_index + i), misValue).Formula = vdc[i].LoadText; } myExcelWorksheet.get_Range("M" + (cel_index + i), misValue).Formula = vdc[i].Support / 10; myExcelWorksheet.get_Range("N" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening / 10; myExcelWorksheet.get_Range("O" + (cel_index + i), misValue).Formula = vdc[i]._L_8 / 10; myExcelWorksheet.get_Range("P" + (cel_index + i), misValue).Formula = vdc[i]._L_4 / 10; myExcelWorksheet.get_Range("Q" + (cel_index + i), misValue).Formula = vdc[i]._3L_8 / 10; myExcelWorksheet.get_Range("R" + (cel_index + i), misValue).Formula = vdc[i].Mid / 10; } if ((cel_index + vdc.Count) <= 84) { //myExcelWorksheet.get_Range("A84" + (i), misValue).EntireRow.Hidden = true; cel_index = cel_index + vdc.Count; for (int i = cel_index; i <= 84; i++) { myExcelWorksheet.get_Range("A" + (i), misValue).EntireRow.Hidden = true; myExcelWorksheet.get_Range("L" + (i), misValue).Formula = "'"; myExcelWorksheet.get_Range("M" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("N" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("O" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("P" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("Q" + (i), misValue).Formula = 0.0; myExcelWorksheet.get_Range("R" + (i), misValue).Formula = 0.0; } } #endregion LL_Moving #region User Inputs if (Long_User_Inputs != null) { myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["1.Input"]; List <int> lst_F = new List <int>(); int cnt = 0; int i = 0; for (i = 5; i <= 71; i++) { if (i == 6 || i == 12 || i == 17 || i == 22 || i == 39 || i == 40 || i == 45 || i == 46 || i == 50 || i == 51 || i == 55 || i == 59 || i == 60 || i == 63 || i == 65) { continue; } lst_F.Add(i); Long_User_Inputs[cnt++].Excel_Cell_Reference = "F" + i; } Excel.Range ran = myExcelWorksheet.get_Range("F5:F71", misValue); //myExcelWorksheet.Range["F4:F46"].Locked = false; //if ((bool)ran.Locked) //{ // MessageBox.Show(""); //} myExcelWorksheet.Unprotect("2011ap"); ran.Locked = false; for (i = 0; i < Long_User_Inputs.Count; i++) { //myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value; myExcelWorksheet.get_Range(Long_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = Long_User_Inputs[i].Input_Value; } //ran.Locked = true; myExcelWorksheet.get_Range("F12", misValue).Formula = "=F10-" + No_Crash_Barrier_Sode + "*F15"; //myExcelWorksheet.Protect("2011ap"); } #endregion User Inputs myExcelWorkbook.Save(); //myExcelWorkbook.Close(true, fileName, null); Marshal.ReleaseComObject(myExcelWorkbook); }