private void btnImport_Click(object sender, EventArgs e) { Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet activeSheet = (Excel.Worksheet)WB.ActiveSheet; List <int> deletes = new List <int>(); string connString = Utilities.Constants.EXCEL_CONNSTRING; for (int i = 0; i < checkedListBox2.Items.Count; i++) { if (checkedListBox2.GetItemChecked(i)) { SourceFile fl = (SourceFile)checkedListBox2.Items[i]; string flnm = fl.FileName.Substring(fl.FileName.LastIndexOf("\\")).Replace("\\", ""); // create new worksheet string newSheetNm = ExcelHelpers.CreateValidWorksheetName(WB, fl.ShortName, 0); Excel.Worksheet newSheet = (Excel.Worksheet)WB.Sheets.Add(System.Type.Missing, WB.Sheets[WB.Sheets.Count], System.Type.Missing, System.Type.Missing); newSheet.Name = newSheetNm; // add custom properties ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_ROLLUP, "TRUE"); ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_SRCFILE, flnm); // import the data DataTable dt = new DataTable(); connString = connString.Replace("{0}", fl.FileName); System.Data.OleDb.OleDbConnection aConn = new System.Data.OleDb.OleDbConnection(connString); aConn.Open(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(fl.SQLStatement, aConn); try { da.Fill(dt); } catch { MessageBox.Show("An error was encountered while attempting to import sheets from workbook " + this.openFileDialog1.FileNames.GetValue(i).ToString() + ". Please re-run the data in a new workbook before including it in the corporate roll-up."); break; } aConn.Close(); aConn.Dispose(); // copy the data to the new sheet Excel.Range rng = newSheet.Rows.get_Resize(dt.Rows.Count + 1, dt.Columns.Count + 1).get_Offset(3, 0); //rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name"; for (int c = 0; c < dt.Columns.Count; c++) { rng.get_Offset(0, c).get_Resize(1, 1).Value2 = dt.Columns[c].ColumnName; } rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt); //rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = fl.ShortName;//"=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")"; object[,] tmp1 = (object[, ])rng.Value2; bool hasProd = false; bool hasBuildSqFt = false; fromEnergyCost = false; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString().Contains("Total Production Output") || dr[0].ToString().Contains("Production Energy Intensity (MMBtu/unit production)")) { hasProd = true; } if (dr[0].ToString().Contains("Building Energy Intensity")) { hasBuildSqFt = true; } if (dr[0].ToString().Contains("Estimated Cost Savings")) { fromEnergyCost = true; } } //----------------------------------------------- newSheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);//.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing); DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName, fl.numOfSources, fl.fromActual, hasProd, hasBuildSqFt, true, fromEnergyCost); newdt.DisplayName = newSheetNm + " (" + flnm + ")"; tables.Add(newdt); } } files.Clear(); checkedListBox2.Refresh(); checkedListBox2.Visible = false; btnImport.Visible = false; // reload the tables box AddTablesToList(); activeSheet.Activate(); // // copy the data to the new sheet // int jslkdf = dt.Rows.Count; // Excel.Range rng = newSheet.get_Range("A1").get_Resize(((dt.Rows.Count + 1)/2)+1, dt.Columns.Count + 1); // rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name"; // for (int c = 0; c < dt.Columns.Count; c++) // { // rng.get_Offset(0, c + 1).get_Resize(1,1).Value2 = dt.Columns[c].ColumnName; // } // rng.get_Offset(1, 1).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt); // rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = "=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")"; // object[,] tmp1 = (object[,])rng.Value2; // newSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing); // DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName); // newdt.DisplayName = newSheetNm + " (" + flnm + ")"; // tables.Add(newdt); // } //} //files.Clear(); //checkedListBox2.Refresh(); //checkedListBox2.Visible = false; //btnImport.Visible = false; //// reload the tables box //AddTablesToList(); //activeSheet.Activate(); }