/// <summary> /// Рисует график public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/) { ObjWorkBooks = ObjWorkExcel.Workbooks; ObjSheets = ObjWorkBook.Worksheets; Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250); Excel.Chart chart = chartsobjrct.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing); Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing); newTrendline.Select(); chart.Legend.Delete(); series.XValues = ObjWorkSheet.get_Range("E7", "E26"); series.Values = ObjWorkSheet.get_Range("D7", "D26"); chart.ChartType = Excel.XlChartType.xlXYScatter; }
public void click_importData(Office.IRibbonControl control) { // Initialize Open File Dialog OpenFileDialog dialog_openFile = new OpenFileDialog(); dialog_openFile.Filter = "Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)| *.xls; *.csv; *.xlsx; *.xlsm|Unicode Text|*.txt"; dialog_openFile.Title = "Select Files To Import..."; dialog_openFile.Multiselect = true; // Set Active Elements Excel.Workbook workbook_active = GetActiveWorkbook(); // Set Default Directory SetDefaultDirectory(dialog_openFile, workbook_active); // Show Open File Dialog DialogResult result_openFile = dialog_openFile.ShowDialog(); if (result_openFile == System.Windows.Forms.DialogResult.OK) { DataSet dataset_import = new DataSet(); foreach (String fileName in dialog_openFile.FileNames) { try { DataTable datatable_temp = new DataTable(Path.GetFileNameWithoutExtension(fileName)); string ext = Path.GetExtension(fileName); using (TextFieldParser parser = new TextFieldParser(@fileName)) { parser.TextFieldType = FieldType.Delimited; string delimeter = ConfigurationManager.AppSettings[ext + "_delimeter"]; parser.SetDelimiters(delimeter); parser.HasFieldsEnclosedInQuotes = Convert.ToBoolean(ConfigurationManager.AppSettings[ext + "_quotes"]); if (ext == ".csv") { string str_checkName = ConfigurationManager.AppSettings[ext + "_name"]; string str_checkValue = ConfigurationManager.AppSettings[ext + "_value"]; string str_checkTitle = ConfigurationManager.AppSettings[ext + "_title"]; while (!parser.EndOfData) { // TODO: DO NOT HARDCODE FIRST VAL and TYPEOF VALUES string[] fields_temp = parser.ReadFields(); if (fields_temp[0] == str_checkName) { for (int i = 1; i < fields_temp.Length; i++) { datatable_temp.Columns.Add(new DataColumn(fields_temp[i], typeof(double))); } } else if (fields_temp[0] == str_checkValue) { double[] values_temp = Array.ConvertAll <string, double>(SubArray <string>(fields_temp, 1, fields_temp.Length - 1), Convert.ToDouble); if (values_temp.Length != datatable_temp.Columns.Count) { throw new Exception("Column mismatch."); } DataRow datarow_temp = datatable_temp.NewRow(); for (int i = 0; i < values_temp.Length; i++) { datarow_temp[i] = values_temp[i]; } datatable_temp.Rows.Add(datarow_temp); } else if (fields_temp[1] == str_checkTitle) { datatable_temp.TableName = fields_temp[2]; } } } } // End of 'using' dataset_import.Tables.Add(datatable_temp); } catch (Exception IE) { MessageBox.Show(IE.Message + " error: could not load file"); continue; } } Excel.Worksheet worksheet_new = CreateNewWorksheet(); string[] arr_names = new string[dataset_import.Tables.Count + 1]; DataTable voltage_table = dataset_import.Tables[0]; arr_names[0] = "Vgs"; double[] voltage_values = voltage_table.AsEnumerable().Select(r => r.Field <double>("Vgs")).ToArray(); for (int v = 0; v < voltage_table.Rows.Count; v++) { worksheet_new.Cells[v + 2, 1] = voltage_values[v]; } for (int i = 0; i < dataset_import.Tables.Count; i++) { DataTable datatable_temp = dataset_import.Tables[i]; arr_names[i + 1] = "Id of " + datatable_temp.TableName; double[] values_fromTable = datatable_temp.AsEnumerable().Select(r => r.Field <double>("Id")).ToArray(); for (int j = 0; j < datatable_temp.Rows.Count; j++) { worksheet_new.Cells[j + 2, i + 2] = -1 * values_fromTable[j]; } } for (int h = 0; h < arr_names.Length; h++) { worksheet_new.Cells[1, h + 1] = arr_names[h]; } object misValue = System.Reflection.Missing.Value; Excel.Range range_chart; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet_new.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 150, 500, 250); Excel.Chart chartPage = myChart.Chart; range_chart = worksheet_new.UsedRange; Excel.Range range_tempA = range_chart.Columns[1].Find("-40"); range_chart = range_chart.Resize[range_tempA.Row]; Excel.Range range_tempB = worksheet_new.Cells[range_chart.Columns[1].Find("-20").Row, 2]; Excel.Range c2 = worksheet_new.Cells[range_chart.Rows.Count, range_chart.Columns.Count]; Excel.Range range_trendline = (Excel.Range)worksheet_new.get_Range(range_tempB, c2); range_chart.Cells[1, 1].Value = ""; chartPage.SetSourceData(range_chart, misValue); //chartPage.SetSourceData(range_trendline, misValue); chartPage.ChartType = Excel.XlChartType.xlXYScatter; foreach (Excel.Series series in chartPage.SeriesCollection()) { Excel.Trendline trendline = series.Trendlines().Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } Excel.Range a1 = range_chart.Columns[1].Find("-20"); Excel.Range a2 = worksheet_new.Cells[range_chart.Rows.Count, 1]; Excel.Range a3 = (Excel.Range)worksheet_new.get_Range(a1, a2); for (int i = 1; i <= range_trendline.Columns.Count; i++) { Excel.Series series_temp = chartPage.SeriesCollection().Add(range_trendline.Columns[i]); series_temp.XValues = a3; Excel.Trendlines trendlines_temp = series_temp.Trendlines(); Excel.Trendline trendline_temp = trendlines_temp.Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline_temp.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } } }