Пример #1
0
 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();
            };
        }