1 | #region License Information
|
---|
2 | /* HeuristicLab
|
---|
3 | * Copyright (C) 2002-2013 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
|
---|
4 | *
|
---|
5 | * This file is part of HeuristicLab.
|
---|
6 | *
|
---|
7 | * HeuristicLab is free software: you can redistribute it and/or modify
|
---|
8 | * it under the terms of the GNU General Public License as published by
|
---|
9 | * the Free Software Foundation, either version 3 of the License, or
|
---|
10 | * (at your option) any later version.
|
---|
11 | *
|
---|
12 | * HeuristicLab is distributed in the hope that it will be useful,
|
---|
13 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
---|
14 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
---|
15 | * GNU General Public License for more details.
|
---|
16 | *
|
---|
17 | * You should have received a copy of the GNU General Public License
|
---|
18 | * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
|
---|
19 | */
|
---|
20 | #endregion
|
---|
21 |
|
---|
22 | using System;
|
---|
23 | using System.Collections.Generic;
|
---|
24 | using System.ComponentModel;
|
---|
25 | using System.IO;
|
---|
26 | using System.Linq;
|
---|
27 | using System.Windows.Forms;
|
---|
28 | using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding.Views;
|
---|
29 | using HeuristicLab.MainForm;
|
---|
30 | using HeuristicLab.MainForm.WindowsForms;
|
---|
31 | using HeuristicLab.Optimizer;
|
---|
32 | using OfficeOpenXml;
|
---|
33 | using OfficeOpenXml.Drawing.Chart;
|
---|
34 |
|
---|
35 | namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Views {
|
---|
36 | public class ExportSymbolicSolutionToExcelMenuItem : MainForm.WindowsForms.MenuItem, IOptimizerUserInterfaceItemProvider {
|
---|
37 | private const string TRAININGSTART = "TrainingStart";
|
---|
38 | private const string TRAININGEND = "TrainingEnd";
|
---|
39 | private const string TESTSTART = "TestStart";
|
---|
40 | private const string TESTEND = "TestEnd";
|
---|
41 |
|
---|
42 | public override string Name {
|
---|
43 | get { return "Export Symbolic Solution To Excel"; }
|
---|
44 | }
|
---|
45 | public override IEnumerable<string> Structure {
|
---|
46 | get { return new string[] { "&Data Analysis" }; }
|
---|
47 | }
|
---|
48 | public override int Position {
|
---|
49 | get { return 5200; }
|
---|
50 | }
|
---|
51 | public override string ToolTipText {
|
---|
52 | get { return "Create excel file of symbolic data analysis solutions."; }
|
---|
53 | }
|
---|
54 |
|
---|
55 | protected override void OnToolStripItemSet(EventArgs e) {
|
---|
56 | base.OnToolStripItemSet(e);
|
---|
57 | ToolStripItem.Enabled = false;
|
---|
58 | var menuItem = ToolStripItem.OwnerItem as ToolStripMenuItem;
|
---|
59 | if (menuItem != null)
|
---|
60 | menuItem.DropDownOpening += menuItem_DropDownOpening;
|
---|
61 | }
|
---|
62 |
|
---|
63 | private void menuItem_DropDownOpening(object sender, EventArgs e) {
|
---|
64 | IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
|
---|
65 | Control control = activeView as Control;
|
---|
66 | activeView = control.GetNestedControls((c) => c.Visible)
|
---|
67 | .OfType<IContentView>().FirstOrDefault(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
|
---|
68 | ToolStripItem.Enabled = activeView != null;
|
---|
69 | }
|
---|
70 |
|
---|
71 | public override void Execute() {
|
---|
72 | IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
|
---|
73 | Control control = activeView as Control;
|
---|
74 | activeView = control.GetNestedControls((c) => c.Visible)
|
---|
75 | .OfType<IContentView>().First(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
|
---|
76 | var solution = (ISymbolicDataAnalysisSolution)activeView.Content;
|
---|
77 | var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
|
---|
78 | var formula = formatter.Format(solution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
|
---|
79 | control = (Control)activeView;
|
---|
80 |
|
---|
81 |
|
---|
82 | SaveFileDialog saveFileDialog = new SaveFileDialog();
|
---|
83 | saveFileDialog.Filter = "Excel Workbook|*.xlsx";
|
---|
84 | saveFileDialog.Title = "Save an Excel File";
|
---|
85 | if (saveFileDialog.ShowDialog() == DialogResult.OK) {
|
---|
86 | string fileName = saveFileDialog.FileName;
|
---|
87 | using (BackgroundWorker bg = new BackgroundWorker()) {
|
---|
88 | MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().AddOperationProgressToView(control, "Exportion solution to " + fileName + ".");
|
---|
89 | bg.DoWork += (b, e) => ExportChart(fileName, solution, formula);
|
---|
90 | bg.RunWorkerCompleted += (o, e) => MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().RemoveOperationProgressFromView(control);
|
---|
91 | bg.RunWorkerAsync();
|
---|
92 | }
|
---|
93 | }
|
---|
94 | }
|
---|
95 |
|
---|
96 | private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
|
---|
97 | FileInfo newFile = new FileInfo(fileName);
|
---|
98 | if (newFile.Exists) {
|
---|
99 | newFile.Delete();
|
---|
100 | newFile = new FileInfo(fileName);
|
---|
101 | }
|
---|
102 | var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
|
---|
103 |
|
---|
104 | using (ExcelPackage package = new ExcelPackage(newFile)) {
|
---|
105 | ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
|
---|
106 | FormatModelSheet(modelWorksheet, solution, formulaParts);
|
---|
107 |
|
---|
108 | ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
|
---|
109 | WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
|
---|
110 |
|
---|
111 | ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
|
---|
112 | WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
|
---|
113 |
|
---|
114 | if (solution is IRegressionSolution) {
|
---|
115 | ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
|
---|
116 | WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
|
---|
117 |
|
---|
118 | ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
|
---|
119 | AddCharts(chartsWorksheet);
|
---|
120 | }
|
---|
121 | package.Workbook.Properties.Title = "Excel Export";
|
---|
122 | package.Workbook.Properties.Author = "HEAL";
|
---|
123 | package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
|
---|
124 |
|
---|
125 | package.Save();
|
---|
126 | }
|
---|
127 | }
|
---|
128 |
|
---|
129 | private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
|
---|
130 | int row = 1;
|
---|
131 | modelWorksheet.Cells[row, 1].Value = "Model";
|
---|
132 | modelWorksheet.Cells[row, 2].Value = solution.Name;
|
---|
133 |
|
---|
134 | foreach (var part in formulaParts) {
|
---|
135 | modelWorksheet.Cells[row, 4].Value = part;
|
---|
136 | row++;
|
---|
137 | }
|
---|
138 |
|
---|
139 | row = 2;
|
---|
140 | modelWorksheet.Cells[row, 1].Value = "Model Depth";
|
---|
141 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
|
---|
142 | row++;
|
---|
143 |
|
---|
144 | modelWorksheet.Cells[row, 1].Value = "Model Length";
|
---|
145 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
|
---|
146 | row += 2;
|
---|
147 |
|
---|
148 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
|
---|
149 | modelWorksheet.Cells[row, 2].Value = solution.Model.LowerEstimationLimit;
|
---|
150 | modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
|
---|
151 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
152 | row++;
|
---|
153 |
|
---|
154 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
|
---|
155 | modelWorksheet.Cells[row, 2].Value = solution.Model.UpperEstimationLimit;
|
---|
156 | modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
|
---|
157 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
158 | row += 2;
|
---|
159 |
|
---|
160 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
|
---|
161 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
|
---|
162 | modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
|
---|
163 | row++;
|
---|
164 |
|
---|
165 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
|
---|
166 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
|
---|
167 | modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
|
---|
168 | row++;
|
---|
169 |
|
---|
170 | modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
|
---|
171 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
|
---|
172 | modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
|
---|
173 | row++;
|
---|
174 |
|
---|
175 | modelWorksheet.Cells[row, 1].Value = "Test Partition End";
|
---|
176 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
|
---|
177 | modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
|
---|
178 | row += 2;
|
---|
179 |
|
---|
180 | string excelTrainingTarget = Indirect("B", true);
|
---|
181 | string excelTrainingEstimated = Indirect("C", true);
|
---|
182 | string excelTrainingAbsoluteError = Indirect("D", true);
|
---|
183 | string excelTrainingRelativeError = Indirect("E", true);
|
---|
184 | string excelTrainingMeanError = Indirect("F", true);
|
---|
185 | string excelTrainingMSE = Indirect("G", true);
|
---|
186 |
|
---|
187 | string excelTestTarget = Indirect("B", false);
|
---|
188 | string excelTestEstimated = Indirect("C", false);
|
---|
189 | string excelTestAbsoluteError = Indirect("D", false);
|
---|
190 | string excelTestRelativeError = Indirect("E", false);
|
---|
191 | string excelTestMeanError = Indirect("F", false);
|
---|
192 | string excelTestMSE = Indirect("G", false);
|
---|
193 |
|
---|
194 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
|
---|
195 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
|
---|
196 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
197 | row++;
|
---|
198 |
|
---|
199 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
|
---|
200 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
|
---|
201 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
202 | row++;
|
---|
203 |
|
---|
204 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
|
---|
205 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
|
---|
206 | modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
|
---|
207 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
208 | row++;
|
---|
209 |
|
---|
210 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
|
---|
211 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
|
---|
212 | modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
|
---|
213 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
214 | row++;
|
---|
215 |
|
---|
216 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
|
---|
217 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
|
---|
218 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
219 | row++;
|
---|
220 |
|
---|
221 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
|
---|
222 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
|
---|
223 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
224 | row++;
|
---|
225 |
|
---|
226 | modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
|
---|
227 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
|
---|
228 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
229 | row++;
|
---|
230 |
|
---|
231 | modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
|
---|
232 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
|
---|
233 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
234 | row++;
|
---|
235 |
|
---|
236 | modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
|
---|
237 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
|
---|
238 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
239 | row++;
|
---|
240 |
|
---|
241 | modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
|
---|
242 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
|
---|
243 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
244 | row++;
|
---|
245 |
|
---|
246 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
|
---|
247 | modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
|
---|
248 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
249 | row++;
|
---|
250 |
|
---|
251 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (test)";
|
---|
252 | modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
|
---|
253 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
254 |
|
---|
255 | modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
|
---|
256 |
|
---|
257 | AddModelTreePicture(modelWorksheet, solution.Model);
|
---|
258 | }
|
---|
259 |
|
---|
260 | private string Indirect(string column, bool training) {
|
---|
261 | if (training) {
|
---|
262 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TRAININGSTART, TRAININGEND);
|
---|
263 | } else {
|
---|
264 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TESTSTART, TESTEND);
|
---|
265 | }
|
---|
266 | }
|
---|
267 |
|
---|
268 | private void AddCharts(ExcelWorksheet chartsWorksheet) {
|
---|
269 | chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
|
---|
270 | chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
|
---|
271 | chartsWorksheet.Names.AddFormula("AllEstimated", "OFFSET('Estimated Values'!$C$1,1,0, COUNTA('Estimated Values'!$C:$C)-1)");
|
---|
272 | chartsWorksheet.Names.AddFormula("TrainingId", "OFFSET('Estimated Values'!$A$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
273 | chartsWorksheet.Names.AddFormula("TrainingTarget", "OFFSET('Estimated Values'!$B$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
274 | chartsWorksheet.Names.AddFormula("TrainingEstimated", "OFFSET('Estimated Values'!$C$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
275 | chartsWorksheet.Names.AddFormula("TestId", "OFFSET('Estimated Values'!$A$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
276 | chartsWorksheet.Names.AddFormula("TestTarget", "OFFSET('Estimated Values'!$B$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
277 | chartsWorksheet.Names.AddFormula("TestEstimated", "OFFSET('Estimated Values'!$C$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
278 |
|
---|
279 | var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
|
---|
280 | scatterPlot.SetSize(800, 400);
|
---|
281 | scatterPlot.SetPosition(0, 0);
|
---|
282 | scatterPlot.Title.Text = "Scatter Plot";
|
---|
283 | var seriesAll = scatterPlot.Series.Add("AllTarget", "AllEstimated");
|
---|
284 | seriesAll.Header = "All";
|
---|
285 | var seriesTraining = scatterPlot.Series.Add("TrainingTarget", "TrainingEstimated");
|
---|
286 | seriesTraining.Header = "Training";
|
---|
287 | var seriesTest = scatterPlot.Series.Add("TestTarget", "TestEstimated");
|
---|
288 | seriesTest.Header = "Test";
|
---|
289 |
|
---|
290 | var lineChart = chartsWorksheet.Drawings.AddChart("lineChart", eChartType.XYScatterLinesNoMarkers);
|
---|
291 | lineChart.SetSize(800, 400);
|
---|
292 | lineChart.SetPosition(400, 0);
|
---|
293 | lineChart.Title.Text = "LineChart";
|
---|
294 | var lineTarget = lineChart.Series.Add("AllTarget", "AllId");
|
---|
295 | lineTarget.Header = "Target";
|
---|
296 | var lineAll = lineChart.Series.Add("AllEstimated", "AllId");
|
---|
297 | lineAll.Header = "All";
|
---|
298 | var lineTraining = lineChart.Series.Add("TrainingEstimated", "TrainingId");
|
---|
299 | lineTraining.Header = "Training";
|
---|
300 | var lineTest = lineChart.Series.Add("TestEstimated", "TestId");
|
---|
301 | lineTest.Header = "Test";
|
---|
302 | }
|
---|
303 |
|
---|
304 | private void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
|
---|
305 | SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
|
---|
306 | modelTreePicture.Tree = model.SymbolicExpressionTree;
|
---|
307 | string tmpFilename = Path.GetTempFileName();
|
---|
308 | modelTreePicture.Width = 1000;
|
---|
309 | modelTreePicture.Height = 500;
|
---|
310 | modelTreePicture.SaveImageAsEmf(tmpFilename);
|
---|
311 |
|
---|
312 | FileInfo fi = new FileInfo(tmpFilename);
|
---|
313 | var excelModelTreePic = modelWorksheet.Drawings.AddPicture("ModelTree", fi);
|
---|
314 | excelModelTreePic.SetSize(50);
|
---|
315 | excelModelTreePic.SetPosition(2, 0, 6, 0);
|
---|
316 | }
|
---|
317 |
|
---|
318 | private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, IRegressionSolution solution) {
|
---|
319 | string preparedFormula = PrepareFormula(formulaParts);
|
---|
320 | int rows = solution.ProblemData.Dataset.Rows;
|
---|
321 | estimatedWorksheet.Cells[1, 1].Value = "Id";
|
---|
322 | estimatedWorksheet.Cells[1, 2].Value = "Target Variable";
|
---|
323 | estimatedWorksheet.Cells[1, 3].Value = "Estimated Values";
|
---|
324 | estimatedWorksheet.Cells[1, 4].Value = "Absolute Error";
|
---|
325 | estimatedWorksheet.Cells[1, 5].Value = "Relative Error";
|
---|
326 | estimatedWorksheet.Cells[1, 6].Value = "Error";
|
---|
327 | estimatedWorksheet.Cells[1, 7].Value = "Squared Error";
|
---|
328 | estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values";
|
---|
329 | estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values";
|
---|
330 |
|
---|
331 | estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
|
---|
332 |
|
---|
333 | int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
|
---|
334 | for (int i = 0; i < rows; i++) {
|
---|
335 | estimatedWorksheet.Cells[i + 2, 1].Value = i;
|
---|
336 | estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress;
|
---|
337 | estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
|
---|
338 | }
|
---|
339 | estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
340 |
|
---|
341 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
|
---|
342 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
343 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = "ABS(B2 - C2)";
|
---|
344 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
345 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "ABS(D2 / B2)";
|
---|
346 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
347 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "C2 - B2";
|
---|
348 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
349 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
|
---|
350 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
351 |
|
---|
352 | estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
353 | estimatedWorksheet.Cells["J2:J" + (rows + 1)].Formula = "IFERROR(IF(I2 > Model!EstimationLimitUpper, Model!EstimationLimitUpper, IF(I2 < Model!EstimationLimitLower, Model!EstimationLimitLower, I2)), AVERAGE(Model!EstimationLimitLower, Model!EstimationLimitUpper))";
|
---|
354 | estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
355 | }
|
---|
356 |
|
---|
357 | private string PrepareFormula(string[] formulaParts) {
|
---|
358 | string preparedFormula = formulaParts[0];
|
---|
359 | foreach (var part in formulaParts.Skip(2)) {
|
---|
360 | var varMap = part.Split(new string[] { " = " }, StringSplitOptions.None);
|
---|
361 | var columnName = "$" + varMap[1] + "1";
|
---|
362 | preparedFormula = preparedFormula.Replace(columnName, "Inputs!$" + varMap[1] + "{0}"); //{0} will be replaced later with the row number
|
---|
363 | }
|
---|
364 | return preparedFormula;
|
---|
365 | }
|
---|
366 |
|
---|
367 | private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
|
---|
368 | //remark the performance of EPPlus drops dramatically
|
---|
369 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
370 | var variableNames = dataset.VariableNames.Select((v, i) => new { variable = v, index = i + 1 }).ToDictionary(v => v.variable, v => v.index);
|
---|
371 | var nameMapping = list.Select(x => x.Split('=')[0].Trim()).ToArray();
|
---|
372 |
|
---|
373 | for (int row = 1; row <= dataset.Rows + 1; row++) {
|
---|
374 | for (int column = 1; column < nameMapping.Length + 1; column++) {
|
---|
375 | int variableIndex = variableNames[nameMapping[column - 1]];
|
---|
376 | inputsWorksheet.Cells[row, column].Formula = datasetWorksheet.Cells[row, variableIndex].FullAddress;
|
---|
377 | }
|
---|
378 | }
|
---|
379 | }
|
---|
380 |
|
---|
381 | private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
|
---|
382 | //remark the performance of EPPlus drops dramatically
|
---|
383 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
384 | Dataset dataset = problemData.Dataset;
|
---|
385 | var variableNames = dataset.VariableNames.ToList();
|
---|
386 | var doubleVariables = new HashSet<string>(dataset.DoubleVariables);
|
---|
387 |
|
---|
388 | for (int col = 1; col <= variableNames.Count; col++)
|
---|
389 | datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
|
---|
390 |
|
---|
391 | for (int row = 0; row < dataset.Rows; row++) {
|
---|
392 | for (int col = 0; col < variableNames.Count; col++) {
|
---|
393 | if (doubleVariables.Contains(variableNames[col]))
|
---|
394 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetDoubleValue(variableNames[col], row);
|
---|
395 | else
|
---|
396 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetValue(row, col);
|
---|
397 | }
|
---|
398 | }
|
---|
399 | }
|
---|
400 | }
|
---|
401 | }
|
---|