private void SetSources()
{
sources = new DetailTableCollection();
for (int j = 0; j < this.checkedListBox1.CheckedItems.Count; j++)
{
DetailTable st = (DetailTable)this.checkedListBox1.CheckedItems[j];
sources.Add(st);
}
}
public void AddFiles()
{
try
{
// loop through the list objects in the open files and add them to the list box
foreach (GroupSheetCollection gsc in Globals.ThisAddIn.masterGroupCollection)
{
if (gsc.WBName != Globals.ThisAddIn.Application.ActiveWorkbook.Name)
{
Excel.Workbook gsWB = ExcelHelpers.GetWorkbook(Globals.ThisAddIn.Application, gsc.WBName);
bool amisaved = gsWB.Saved;
for (int j = 0; j < gsc.Count; j++)
{
GroupSheet gs = gsc.Item(j);
if (ExcelHelpers.GetWorksheetbyGUID(gsWB, gs.WSGUID) != null)
{
if (gs.adjustedDataSheet)
{
string nm = gs.Name == null?gs.WSName() : gs.Name + " -- " + gs.WSName();
string disp = " (" + gsc.WBName + ") " + nm;
try
{
DetailTable dt = new DetailTable(gs.WS.ListObjects.get_Item(1), nm, numberOfSources, fromActual, hasProd, hasBuildSqFt, true, fromEnergyCost);
if (dt.SQLStatement != "")
{
SourceFile sf = new SourceFile(dt.SQLStatement, disp, gsWB.FullName, false);
if (!files.Contains(sf))
{
files.Add(sf);
}
}
}
catch
{
continue;
}
}
}
}
gsWB.Saved = amisaved;
}
}
AddFilesToList();
}
catch
{
}
}
private void btnTachBan_Click(object sender, EventArgs e)
{
if (ban != null && BLL.HOA_DON_BLL.Instance.checkHoaDon(ban.ID_BAN))
{
DetailTable f2 = new DetailTable(HOA_DON_BLL.Instance.GetIdByTable(ban.ID_BAN), user);
f2.ShowDialog();
button1_Click(new object(), new EventArgs());
}
else
{
MessageBox.Show("Vui long chon ban hoac ban chua duoc order!!!");
}
}
public StatusEntry(string id, string level, string label_enu, string detail_enu, string label_fra, string detail_fra)
{
int.TryParse(id, out int IdInt);
Id = IdInt;
int.TryParse(level, out int LevelInt);
Level = LevelInt;
string EnglishLabel = Language.ReplaceHtml(label_enu);
LabelTable.Add(LanguageStates.English, EnglishLabel);
string EnglishDetail = Language.ReplaceHtml(detail_enu);
DetailTable.Add(LanguageStates.English, EnglishDetail);
string FrenchLabel = Language.ReplaceHtml(label_fra);
LabelTable.Add(LanguageStates.French, FrenchLabel);
string FrenchDetail = Language.ReplaceHtml(detail_fra);
DetailTable.Add(LanguageStates.French, FrenchDetail);
}
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();
}
private void addRawDataSheets()
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
foreach (Excel.Worksheet ws in WB.Sheets)
{
fromActual = false;
fromEnergyCost = false;
string raw = ExcelHelpers.getWorksheetCustomProperty(ws, Utilities.Constants.WS_ROLLUP) ?? "FALSE";
string flnm = ExcelHelpers.getWorksheetCustomProperty(ws, Utilities.Constants.WS_SRCFILE) ?? "";
numberOfSources = 0;
if (bool.Parse(raw) && ws.ListObjects.Count > 0)
{
//parse through ws to populate numberOfSources, fromActual, hasProd and hasBuildSqFt
if (ws.Name.Contains("EnPI Actual Results"))
{
fromActual = true;
}
int parseRows = ws.ListObjects[1].ListRows.Count;
bool endOfSources = false;
for (int i = 5; i < parseRows + 4; i++)
{
try
{
string cellValue = ws.Range["A" + i.ToString()].Value2.ToString();
if (cellValue.Equals(Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName")))
{
endOfSources = true;
}
if (cellValue.Equals("Total Production Output"))
{
hasProd = true;
}
if (cellValue.Equals(Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName")))
{
hasBuildSqFt = true;
}
if (cellValue.Contains("Estimated Cost Savings"))
{
fromEnergyCost = true;
}
//This is a work around, as there is a problem in identifying whether the sheet is Actual or from Regression, but the below item is only in actual sheet so
//Considered this is as a differentiator.
if (cellValue.Contains("Total Savings Since Baseline Year (MMBtu/Year)"))
{
fromActual = true;
}
if (!endOfSources)
{
numberOfSources++;
}
}
catch (Exception e)
{
}
}
DetailTable newt = new DetailTable(ws.ListObjects[1], ws.Name, numberOfSources, fromActual, hasProd, hasBuildSqFt, false, fromEnergyCost);
newt.DisplayName = flnm == "" ? ws.Name : ws.Name + " (" + flnm + ")";
if (newt.SQLStatement != null)
{
tables.Add(newt);
}
}
}
}
