public void Button2_Click(IRibbonControl control)
{
Excel.AddIn ThisAddin = (ExcelDnaUtil.Application as Excel.Application).AddIns["Excel_DNA_Template_CS"];
ThisAddin.Installed = false;
}
private void generateField(object sender, EventArgs e)
{
Excel.Application excelApp = null;
Excel.Workbooks books = null;
Excel.Workbook inputFile = null;
Excel.Workbook templateFile = null;
Excel.Worksheet inputSheets = null;
Excel.Worksheet templateSheet = null;
Excel.Worksheet exceedanceCurves = null;
Excel.Sheets templateSheets = null;
Excel.AddIn solver = null;
Excel.Range cellType1 = null;
Excel.Range cellType2 = null;
Excel.Range cellType3 = null;
notificationPanel.Show();
try {
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
books = excelApp.Workbooks;
inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\exceedance_model.xlsm"));
inputSheets = inputFile.Sheets["Input"];
solver = excelApp.AddIns["Solver Add-In"];
books.Open(solver.FullName);
cellType1 = inputSheets.UsedRange;
cellType2 = inputSheets.UsedRange;
cellType3 = inputSheets.UsedRange;
foreach (Control x in processUnitTable.Controls)
{
if (x is TextBox || x is ComboBox)
{
cellType1 = inputSheets.Cells[processUnitTable.GetRow(x) + 4, processUnitTable.GetColumn(x) + 1];
cellType1.Value = x.Text;
Marshal.FinalReleaseComObject(cellType1);
}
}
foreach (Control x in buildingDetailsTable.Controls)
{
if ((x is TextBox || x is ComboBox) && x.AccessibleName != "Ignore")
{
cellType2 = inputSheets.Cells[buildingDetailsTable.GetColumn(x), buildingDetailsTable.GetRow(x) + 8];
cellType2.Value = x.Text;
Marshal.FinalReleaseComObject(cellType2);
}
}
foreach (Control x in separationDistanceTable.Controls)
{
if ((x is TextBox || x is ComboBox) && x.AccessibleName != "Ignore")
{
cellType3 = inputSheets.Cells[separationDistanceTable.GetRow(x) + 4, separationDistanceTable.GetColumn(x) + 8];
cellType3.Value = x.Text;
Marshal.FinalReleaseComObject(cellType3);
}
}
excelApp.Run("'exceedance_model.xlsm'!exceedance");
templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Exceedance Results.xlsx"));
templateSheet = templateFile.Sheets["Exceedance_Data"];
templateSheets = templateFile.Sheets;
excelApp.DisplayAlerts = false;
inputSheets.Range["A1", "R24"].Copy(templateSheet.Range["A1", "R24"]);
exceedanceCurves = templateSheets.Add();
exceedanceCurves.Name = "Exceedance_Curves";
inputSheets.ChartObjects("Chart 2").Chart.CopyPicture();
exceedanceCurves.Paste();
templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Exceedance Results.xlsx"));
} finally {
// Clean up sheets
GC.Collect();
GC.WaitForPendingFinalizers();
if (cellType1 != null)
{
Marshal.FinalReleaseComObject(cellType1);
cellType1 = null;
}
if (cellType2 != null)
{
Marshal.FinalReleaseComObject(cellType2);
cellType2 = null;
}
if (cellType3 != null)
{
Marshal.FinalReleaseComObject(cellType3);
cellType3 = null;
}
Marshal.FinalReleaseComObject(templateSheets);
Marshal.FinalReleaseComObject(exceedanceCurves);
Marshal.FinalReleaseComObject(templateSheet);
Marshal.FinalReleaseComObject(inputSheets);
Marshal.FinalReleaseComObject(solver);
Marshal.FinalReleaseComObject(books);
templateSheets = null;
exceedanceCurves = null;
templateSheet = null;
inputSheets = null;
solver = null;
books = null;
inputFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
templateFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
Marshal.FinalReleaseComObject(inputFile);
Marshal.FinalReleaseComObject(templateFile);
inputFile = null;
templateFile = null;
excelApp.Application.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
notificationPanel.Controls.Find("btnClose", true)[0].Show();
};
}
