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();
}
