public GroupSheet New()
{
GroupSheet sheet = new GroupSheet();
List.Add(sheet);
return(sheet);
}
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 loadStateData(Excel.Worksheet ws, Excel.Workbook Wb)
{
try
{
removeEntriesFromMasterGroupCollection(Wb.Name);
foreach (Excel.ListObject LO in ws.ListObjects)
{
GroupSheetCollection gsc = new GroupSheetCollection();
gsc.WB = Wb;
gsc.WBName = Wb.Name;
int rowCount = 0;
int r;
foreach (Excel.ListRow row in LO.ListRows)
{
rowCount++;
string sheetguid = row.Range.get_Resize(1, 1).Value2.ToString();
string iter = row.Range.get_Resize(1, 1).Value2.ToString() == "regressionIteration" ? row.Range.get_Offset(0, 1).get_Resize(1, 1).Value2.ToString() : null;
string pltnm = row.Range.get_Resize(1, 1).Value2.ToString() == "plantName" ? row.Range.get_Offset(0, 1).get_Resize(1, 1).Value2.ToString() : null;
Excel.Worksheet workSheet = ExcelHelpers.GetWorksheetbyGUID(Wb, sheetguid);
if (workSheet != null)
{
GroupSheet gs = new GroupSheet((Excel.Worksheet)workSheet, Convert.ToBoolean(((Excel.Range)row.Range.Cells[1, 3]).Value2.ToString()), Convert.ToBoolean(((Excel.Range)row.Range.Cells[1, 4]).Value2.ToString()), ws.Name);
if (gs.adjustedDataSheet && gs.WS.get_Range("A1").Value2 != null) //.ToString()
{
gs.Name = gs.WS.get_Range("A1").Value2.ToString();
}
gsc.Add(gs);
}
gsc.regressionIteration = int.TryParse(iter, out r) ? r : gsc.regressionIteration;
gsc.PlantName = pltnm == null ? gsc.PlantName : pltnm;
}
if (masterGroupCollection.IndexOf(gsc.WBName, gsc.regressionIteration) < 0)
{
masterGroupCollection.Add(gsc); // add the workbooks sheet collections to the master collection
}
}
//this will prevent the "would you like to save" dialog showing even when the user hasn't done anything
Wb.Saved = true;
}
catch (Exception ex)
{
//throw ex;
//TODO: Add Exception and Logging module
}
}
public ModelSheet Add(Utilities.EnergySource aSource)
{
Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.Add
(System.Type.Missing, Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.get_Item(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Count), 1, Excel.XlSheetType.xlWorksheet);
thisSheet.CustomProperties.Add("SheetGUID", System.Guid.NewGuid().ToString());
thisSheet.Name = Utilities.ExcelHelpers.CreateValidWorksheetName(Globals.ThisAddIn.Application.ActiveWorkbook, aSource.Name, Globals.ThisAddIn.groupSheetCollection.regressionIteration);
thisSheet.Tab.Color = 0x50CC11;
thisSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
string sheetString = thisSheet.ToString();
GroupSheet GS = new GroupSheet(thisSheet, false, false, thisSheet.Name);
Globals.ThisAddIn.groupSheetCollection.Add(GS);
ModelSheet aSheet = new ModelSheet(thisSheet);
aSheet.Source = aSource;
return(aSheet);
}
public void Populate()
{
Excel.Range rangeTitle = (Excel.Range)thisSheet.get_Range("A1", "H1");
((Excel.Range)rangeTitle[1, 1]).Value2 = EnPIResources.enpiSheetTitle;
((Excel.Range)rangeTitle[1, 1]).Font.Color = 0x008000;
((Excel.Range)rangeTitle[1, 1]).Font.Bold = true;
((Excel.Range)rangeTitle[1, 1]).Font.Size = 15;
rangeTitle.Merge();
//This is added to keep the sheets consistent
Excel.Range rangeBody = (Excel.Range)thisSheet.get_Range("A2", "H2");
((Excel.Range)rangeBody[1, 1]).Value2 = string.Empty;
rangeBody.Merge();
rangeBody.WrapText = true;
rangeBody.EntireRow.Hidden = true;
SourceData = Utilities.DataHelper.dataTableArrayObject(DS.SourceData);
SourceObject = Utilities.ExcelHelpers.GetListObject(SourceSheet, DS.ListObjectName);
AddTable();
AddSubtotalColumns();
ChartObj = newEnPIChart();
ChartObj2 = newEnPIChart2();
FormatSummaryData();
writeCharts();
GroupSheet GS = new GroupSheet(thisSheet, true, false, thisSheet.Name);
Globals.ThisAddIn.groupSheetCollection.Add(GS);
DetailDataSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
thisSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
thisSheet.Activate();
}
public void Add(GroupSheet sheet)
{
List.Add(sheet);
}