private void AddDataPoints(Excel.Chart bubbleChart, Excel.Worksheet sourceWorksheet) { var dataMatrix = GetDataMatrixFromExcelWorksheet(sourceWorksheet); var seriesCollection = bubbleChart.SeriesCollection(); var minXValue = double.MaxValue; var minYValue = double.MaxValue; var maxXValue = double.MinValue; var maxYValue = double.MinValue; foreach (var row in dataMatrix) { var series = seriesCollection.NewSeries(); series.Has3DEffect = true; series.HasDataLabels = true; var dataLabelText = row[0]; var xValue = Convert.ToDouble(row[1]); var yValue = Convert.ToDouble(row[2]); var revenue = Convert.ToDouble(row[3]) / 1000; var projectTypeId = GetProjectTypeIdFromProjectTypeString(row[4]); var bubbleSize = GetBubbleSizeFromRevenue(revenue); series.Format.Fill.ForeColor.RGB = _projectTypeColors[projectTypeId]; series.XValues = new[] { xValue }; series.Values = new[] { yValue }; series.BubbleSizes = new[] { bubbleSize }; Excel.DataLabel dataLabel = series.DataLabels(1); dataLabel.Text = dataLabelText; if (xValue < minXValue) { minXValue = xValue; } if (yValue < minYValue) { minYValue = yValue; } if (xValue > maxXValue) { maxXValue = xValue; } if (yValue > maxYValue) { maxYValue = yValue; } } var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory); xAxis.MinimumScale = (Math.Floor(minXValue / 100) - 1) * 100; xAxis.MaximumScale = (Math.Ceiling(maxXValue / 100) + 1) * 100; var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue); yAxis.MinimumScale = (Math.Floor(minYValue * 10) - 1) / 10; yAxis.MaximumScale = (Math.Ceiling(maxYValue * 10) + 1) / 10; }
static OutputUnits makePerfMonGraphs(string file) { Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet dataSheet = null; Excel.Worksheet newSheet = null; Excel.ChartObjects xlChart = null; Excel.Range dataY = null; Excel.Chart memChart = null; Excel.Chart diskChart = null; Excel.Chart cpuChart = null; Excel.Chart netChart = null; Excel.Axis xAxis = null; OutputUnits csData = null; bool leaking = false; bool highcpu = false; string exitFile = ""; try { excelApp = new Excel.Application(); string dir = file.Substring(0, file.LastIndexOf("\\") + 1); string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1); workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",", true, false, 0, false, false, false); sheets = workbook.Sheets; dataSheet = sheets[1]; dataSheet.Name = "data"; dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing); newSheet.Name = "results"; xlChart = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing); memChart = xlChart.Add(20, 100, 450, 175).Chart; diskChart = xlChart.Add(20, 280, 450, 175).Chart; cpuChart = xlChart.Add(500, 100, 450, 175).Chart; netChart = xlChart.Add(500, 280, 450, 175).Chart; int rowTotal = dataSheet.UsedRange.Rows.Count; int colTotal = dataSheet.UsedRange.Columns.Count; dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm"; string ttime = dataSheet.Cells[2, 1].Value.ToString(); Array availMem = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value; Array cpuTotal = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value; Array diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value; dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)"; double[] netties = new double[rowTotal - 1]; for (int i = 2; i <= rowTotal; i++) { if (colTotal > 5) { Array netLine = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value; double netLineTotal = 0; for (int j = 1; j <= netLine.Length; j++) { netLineTotal += Convert.ToDouble(netLine.GetValue(1, j)); } netties[i - 2] = netLineTotal; dataSheet.Cells[i, colTotal + 1] = netLineTotal; } else { dataSheet.Cells[i, colTotal + 1] = "0"; } } #region BuildCounters double[] mems = ColToDouble(availMem); double[] cpus = ColToDouble(cpuTotal); double[] disks = ColToDouble(diskTotal); //netties[] double avgCPUs = cpus.Average(); PCounter CPU = new PCounter(avgCPUs, cpus.Max(), cpus.Min()); PCounter MEM = new PCounter(mems.Average(), mems.Max(), mems.Min()); PCounter DISK = new PCounter(disks.Average(), disks.Max(), disks.Min()); PCounter NETS = new PCounter(netties.Average(), netties.Max(), netties.Min()); if (avgCPUs > 40) { highcpu = true; } #endregion #region leakCheck double[] eqMB = new double[2]; List <double> memList = new List <double>(); int cX = availMem.Length; for (int i = 1; i < rowTotal - 1; i++) { memList.Add(Convert.ToDouble(availMem.GetValue(i, 1))); } eqMB = LeastSquares(memList); double stdD1 = StandardDev(memList); List <double> memList2 = sigma(memList, stdD1, eqMB); cX = memList2.Count(); eqMB = LeastSquares(memList2); double stdD2 = StandardDev(memList2) * 1.2; List <double> memList3 = sigma(memList2, stdD2, eqMB); eqMB = LeastSquares(memList3); if (eqMB[0] < 0) { leaking = true; newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato); } #endregion #region formatting string lan = xlStr(colTotal + 1); newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth = 12; newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight; newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter; newSheet.Cells[4, 5] = eqMB[0]; newSheet.Cells[2, 2] = "Avg"; newSheet.Cells[2, 3] = "Min"; newSheet.Cells[2, 4] = "Max"; newSheet.Cells[2, 5] = "Slope(3Sigma)"; newSheet.Cells[3, 1] = "CPU"; newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)"; newSheet.Cells[3, 3] = "=MIN(data!D:D)"; newSheet.Cells[3, 4] = "=MAX(data!D:D)"; newSheet.Cells[4, 1] = "Avail.RAM"; newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)"; newSheet.Cells[4, 3] = "=MIN(data!C:C)"; newSheet.Cells[4, 4] = "=MAX(data!C:C)"; newSheet.Cells[5, 1] = "LAN Usage"; newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")"; newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")"; newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")"; newSheet.Cells[6, 1] = "Disk Usage"; newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)"; newSheet.Cells[6, 3] = "=MIN(data!B:B)"; newSheet.Cells[6, 4] = "=MAX(data!B:B)"; #endregion #region memChart dataY = dataSheet.Range["C1", "C" + rowTotal]; memChart.SetSourceData(dataY, Type.Missing); memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers; memChart.HasLegend = false; xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); xAxis.MaximumScaleIsAuto = false; xAxis.MaximumScale = rowTotal + 1; xAxis.MinimumScaleIsAuto = false; xAxis.MinimumScale = 0; #endregion #region diskChart dataY = dataSheet.Range["B1", "B" + rowTotal]; diskChart.SetSourceData(dataY, Type.Missing); diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers; diskChart.HasLegend = false; xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); xAxis.MaximumScaleIsAuto = false; xAxis.MaximumScale = rowTotal + 1; xAxis.MinimumScaleIsAuto = false; xAxis.MinimumScale = 0; #endregion #region cpuChart dataY = dataSheet.Range["D1", "D" + rowTotal]; cpuChart.SetSourceData(dataY, Type.Missing); cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers; cpuChart.HasLegend = false; xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); xAxis.MaximumScaleIsAuto = false; xAxis.MaximumScale = rowTotal + 1; xAxis.MinimumScaleIsAuto = false; xAxis.MinimumScale = 0; #endregion #region netChart dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal]; netChart.SetSourceData(dataY, Type.Missing); netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers; netChart.HasLegend = false; xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); xAxis.MaximumScaleIsAuto = false; xAxis.MaximumScale = rowTotal + 1; xAxis.MinimumScaleIsAuto = false; xAxis.MinimumScale = 0; #endregion string host = Path.GetFileNameWithoutExtension(dir + fm); csData = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu); exitFile = dir + fm; excelApp.DisplayAlerts = false; workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); //releaseObject(sC); //releaseObject(myChart); } catch { Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?"); //using (StreamWriter outfile = File.AppendText("output.txt")) { // outfile.WriteLine("Did have issues interacting with Excel on " + file); //} } finally { releaseObject(xAxis); releaseObject(dataY); releaseObject(diskChart); releaseObject(memChart); releaseObject(cpuChart); releaseObject(netChart); releaseObject(xlChart); releaseObject(newSheet); releaseObject(dataSheet); releaseObject(sheets); releaseObject(workbook); releaseObject(excelApp); } return(csData); }
private void diagram_Click(object sender, EventArgs e) { KURS.allDataSet kursds = new KURS.allDataSet(); KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter(); zta.Fill(kursds.Zakaz); // object misValue = System.Reflection.Missing.Value; // Excel.Application excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(misValue); Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelappworkbooks[1]; Excel.Sheets excelsheets = excelappworkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); //excelworksheet.Activate(); // var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p; int count = 0; excelapp.Cells[1][1] = "date"; excelapp.Cells[2][1] = "summ"; foreach (var p in c) { count++; } // int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0; while (i < count) { foreach (var p in c) { a[i] = p.Data.Day; if (i == 0) { sum += p.Summ; } if (i > 0) { if (a[i] == a[i - 1]) { sum += p.Summ; } else { b[i - 1] = sum; sum = p.Summ; i++; } } else { i++; } } b[i - 1] = sum; count = i; i = 0; break; } Dictionary <int, double> dic = new Dictionary <int, double>(); while (i < count) { excelapp.Cells[1][i + 2] = a[i]; excelapp.Cells[2][i + 2] = b[i]; dic.Add(a[i], b[i]); i++; } // string str = (count + 1).ToString(); // //Excel.Window excelWindow = null; Excel.Range excelcells = null; //Определяем диаграммы как объекты Excel.ChartObjects Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing); //Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300); Excel.Chart chartPage = chartsobjrct.Chart; excelcells = excelworksheet.get_Range("B1", "B" + str); chartPage.SetSourceData(excelcells, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.HasTitle = true; axis.AxisTitle.Text = "Data"; // Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.XValues = a; // excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); excelappworkbook.Close(true, misValue, misValue); excelapp.Quit(); this.chart1.Titles.Add("Отчет за месяц"); this.chart1.Series[0].Points.Clear(); Series ser = this.chart1.Series[0]; ser.LegendText = "Summ"; foreach (KeyValuePair <int, double> pair in dic) { ser.Points.AddXY(pair.Key, pair.Value); } // }
private void btnConvert_Click(object sender, EventArgs e) { string inputPath = this.textBox2.Text; string outputPath = this.textBox3.Text; Object misValue = System.Reflection.Missing.Value; Excel.Application xls = new Excel.Application(); String excelExt = "xlsx"; Directory.CreateDirectory(outputPath); this.textBox4.Clear(); int numCheckedStations = checkedListBox2.CheckedItems.Count; int numStationsProcessed = 0; foreach (string stationCode in checkedListBox2.CheckedItems) { numStationsProcessed++; Excel.Workbook xlsWorkBook = xls.Workbooks.Add(misValue); Excel.Worksheet xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Sheets[1]; System.IO.StreamReader fileReader; String info1, info2; int nt = 0; double dt; String filePathPrefix = inputPath + "\\" + stationCode; String outputFile = outputPath + "\\" + stationCode + "." + excelExt; //this.textBox4.Text +="Processing "+stationCode + "("+numStationsProcessed+"/"+numCheckedStations+") "; this.textBox4.AppendText("Processing " + stationCode + "(" + numStationsProcessed + "/" + numCheckedStations + ") "); xlsWorkSheet.Cells[1, 1] = "Component"; xlsWorkSheet.Cells[2, 1] = "No. of timesteps"; xlsWorkSheet.Cells[3, 1] = "Size of timesteps (seconds)"; xlsWorkSheet.Cells[4, 1] = "Time (seconds)"; xlsWorkSheet.Cells[5, 1] = "Acceleration"; String[] componentsCode = { "000", "090", "ver" }; String[] componentsStr = { "X-axis (000)", "Y-axis (090)", "Z-axis (ver)" }; for (int k = 0; k < 3; k++) //Iterates each component file { this.textBox4.AppendText(Convert.ToString(k + 1) + "..."); String ext = componentsCode[k]; fileReader = new StreamReader(new FileStream(filePathPrefix + "." + ext, FileMode.Open)); info1 = fileReader.ReadLine(); info2 = fileReader.ReadLine(); int LastNonEmpty = -1; String[] info2Array = info2.Split(); for (int i = 0; i < info2Array.Length; i++) { if (info2Array[i] != "") { LastNonEmpty += 1; info2Array[LastNonEmpty] = info2Array[i]; } } nt = Convert.ToInt32(info2Array[0]); //number of time steps dt = Convert.ToDouble(info2Array[1]); //time step size Double[,] timeRange = new double[nt, 1]; xlsWorkSheet.Cells[1, 2 + k] = componentsStr[k]; xlsWorkSheet.Cells[2, 2 + k] = nt; xlsWorkSheet.Cells[3, 2 + k] = dt; LastNonEmpty = -1; String lines = fileReader.ReadToEnd(); String[] strArray = lines.Split(); Double[,] dblArray = new Double[nt, 1]; //needs to be 2-d to be able to bulk write for (int i = 0; i < strArray.Length; i++) { if (strArray[i] != "") { LastNonEmpty += 1; timeRange[LastNonEmpty, 0] = LastNonEmpty * dt; dblArray[LastNonEmpty, 0] = Convert.ToDouble(strArray[i].Replace("\n", "")); } } int nt2 = LastNonEmpty + 1; if (k == 0) { xlsWorkSheet.Range["A5"].Resize[nt2, 1].Value = timeRange; // first column filled with time steps } Excel.Range cell; cell = xlsWorkSheet.Cells[5, 2 + k]; xlsWorkSheet.Range[cell, cell].Resize[nt2, 1].Value = dblArray; //bulk write at column "B, C, D" } Excel.WorksheetFunction wsf = xls.WorksheetFunction; Double maxAcc = wsf.Max(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]); //find the maximum and minimum Double minAcc = wsf.Min(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]); Double maxAmpAcc = Math.Max(maxAcc, -1.0 * minAcc); //Select the max of absolute value of two for (int k = 0; k < 3; k++) // Plotting { String ext = componentsCode[k]; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlsWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 80 + 300 * k, 600, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 2 + k], xlsWorkSheet.Cells[nt + 4, 2 + k]]; chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlLine; chartPage.HasTitle = true; Excel.Series series = (Excel.Series)chartPage.SeriesCollection(1); Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); Microsoft.Office.Interop.Excel.Axis yAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = true; xAxis.AxisTitle.Text = "Time (sec)"; xAxis.CategoryNames = (Excel.Range)xlsWorkSheet.Range["A5"].Resize[nt, 1]; xAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow; yAxis.HasTitle = true; yAxis.AxisTitle.Text = "Acceleration (cm/s^2)"; yAxis.MinimumScale = (-1 * maxAmpAcc * 3.0); yAxis.MaximumScale = (maxAmpAcc * 3.0); series.Name = ext; switch (k) { case 0: chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along X-axis (" + ext + ")"; series.Border.Color = (int)Excel.XlRgbColor.rgbRed; break; case 1: chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Y-axis (" + ext + ")"; series.Border.Color = (int)Excel.XlRgbColor.rgbBlue; break; case 2: chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Z-axis (" + ext + ")"; series.Border.Color = (int)Excel.XlRgbColor.rgbGreen; break; } } this.textBox4.AppendText("Done..!" + Environment.NewLine); if (File.Exists(outputFile)) // delete file if it already exists { File.Delete(outputFile); } xlsWorkBook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue); xlsWorkBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook); } xls.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xls); //MessageBox.Show("Done!"); this.textBox4.AppendText("Finished!!" + Environment.NewLine); }
public void ExportToExcel(string mapname, int address, int length, byte[] mapdata, int cols, int rows, bool isSixteenbit, int[] xaxisvalues, int[] yaxisvalues, bool isupsidedown, string xaxisdescr, string yaxisdescr, string zaxisdescr) { //en-US CultureInfo tci = new CultureInfo("en-US"); Thread.CurrentThread.CurrentCulture = tci; try { try { if (xla == null) { xla = new Microsoft.Office.Interop.Excel.Application(); } } catch (Exception xlaE) { Console.WriteLine("Failed to create office application interface: " + xlaE.Message); } // turn mapdata upside down if (isupsidedown) { mapdata = TurnMapUpsideDown(mapdata, cols, rows, isSixteenbit); } xla.Visible = true; Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet); Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet; ws.Name = "symboldata"; // Now create the chart. ChartObjects chartObjs = (ChartObjects)ws.ChartObjects(Type.Missing); ChartObject chartObj = chartObjs.Add(100, 400, 400, 300); Microsoft.Office.Interop.Excel.Chart xlChart = chartObj.Chart; int nRows = rows; //if (isSixteenbit) nRows /= 2; int nColumns = cols; string upperLeftCell = "B3"; int endRowNumber = System.Int32.Parse(upperLeftCell.Substring(1)) + nRows - 1; char endColumnLetter = System.Convert.ToChar(Convert.ToInt32(upperLeftCell[0]) + nColumns - 1); string upperRightCell = System.String.Format("{0}{1}", endColumnLetter, System.Int32.Parse(upperLeftCell.Substring(1))); string lowerRightCell = System.String.Format("{0}{1}", endColumnLetter, endRowNumber); // Send single dimensional array to Excel: Range rg1 = ws.get_Range("B2", "Z2"); double[] xarray = new double[nColumns]; double[] yarray = new double[nRows]; ws.Cells[1, 1] = "Data for " + mapname; for (int i = 0; i < xarray.Length; i++) { if (xaxisvalues.Length > i) { xarray[i] = (int)xaxisvalues.GetValue(i); } else { xarray[i] = i; } //ws.Cells[i + 3, 1] = xarray[i]; ws.Cells[2, 2 + i] = xarray[i]; } for (int i = 0; i < yarray.Length; i++) { if (yaxisvalues.Length > i) { if (isupsidedown) { yarray[i] = (int)yaxisvalues.GetValue((yarray.Length - 1) - i); } else { yarray[i] = (int)yaxisvalues.GetValue(i); } } else { yarray[i] = i; } ws.Cells[i + 3, 1] = yarray[i]; //ws.Cells[2, 2 + i] = yarray[i]; } Range rg = ws.get_Range(upperLeftCell, lowerRightCell); rg.Value2 = AddData(nRows, nColumns, mapdata, isSixteenbit); Range chartRange = ws.get_Range("A2", lowerRightCell); xlChart.SetSourceData(chartRange, Type.Missing); if (yarray.Length > 1) { xlChart.ChartType = XlChartType.xlSurface; } // Customize axes: Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); xAxis.HasTitle = true; xAxis.AxisTitle.Text = yaxisdescr; try { Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlSeriesAxis, XlAxisGroup.xlPrimary); yAxis.HasTitle = true; yAxis.AxisTitle.Text = xaxisdescr; } catch (Exception E) { Console.WriteLine("Failed to set y axis: " + E.Message); } Axis zAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary); zAxis.HasTitle = true; zAxis.AxisTitle.Text = zaxisdescr; // Add title: xlChart.HasTitle = true; xlChart.ChartTitle.Text = mapname; // Remove legend: xlChart.HasLegend = false; // add 3d shade xlChart.SurfaceGroup.Has3DShading = true; /*if (File.Exists(m_currentfile + "~" + mapname + ".xls")) * { * * }*/ try { wb.SaveAs(Tools.Instance.m_currentfile + "~" + mapname + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, false, null, null, null, null); } catch (Exception sE) { Console.WriteLine("Failed to save workbook: " + sE.Message); } } catch (Exception E) { Console.WriteLine("Failed to export to excel: " + E.Message); Console.WriteLine("Failed to export to excel: " + E.Message); } tci = new CultureInfo("nl-NL"); Thread.CurrentThread.CurrentCulture = tci; }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; xlApp = new Excel.Application(); object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i < 10; i++) { xlWorkSheet.Cells[i + 2, 1] = x[i]; xlWorkSheet.Cells[i + 2, 2] = y[i]; } Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet. ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(100, 100, 300, 250); Excel.Chart chartPage = myChart.Chart; Excel.Range yRange; Excel.Range xRange; yRange = xlWorkSheet.get_Range("B2:B11"); xRange = xlWorkSheet.get_Range("A2:A11"); chartPage.SetSourceData(yRange, Excel.XlRowCol.xlColumns); chartPage.SeriesCollection(1).XValues = xRange; chartPage.SeriesCollection(1).Name = "y1-var"; chartPage.ChartType = Excel.XlChartType.xlXYScatter; Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType. xlCategory, Excel.XlAxisGroup.xlPrimary); Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType. xlValue, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = true; xAxis.AxisTitle.Text = "x-axis"; yAxis.HasTitle = true; yAxis.AxisTitle.Text = "y-axis"; chartPage.HasTitle = true; chartPage.ChartTitle.Text = "Example"; chartPage.HasLegend = true; xlWorkBook.SaveAs(path + "\\out.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); MessageBox.Show("Excel file created"); MessageBox.Show(path); }
private void buildXlsChart(Xls.Chart chart, Xls.Worksheet dataSheet, ChartInfo graph, int currentDataColumn, out int dataColumnsWritten) { chart.ChartTitle.Text = string.Format(Strings.ChartTitle, graph.PrimaryTitle, graph.SecondaryTitle); dataColumnsWritten = 0; double[,] xValues = null, yValues = null; int seriesCounter = 0; foreach (var s in graph.Series) { if (seriesCounter == 0) { dataSheet.Cells[2, currentDataColumn].Value = s.XUnitName; xValues = ConvertToTwoDimensionalArray(s.XValues); dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn]].Value2 = xValues; dataColumnsWritten += 1; } dataSheet.Cells[1, currentDataColumn + dataColumnsWritten].Value = s.Name; dataSheet.Cells[2, currentDataColumn + dataColumnsWritten].Value = s.YUnitName; yValues = ConvertToTwoDimensionalArray(s.YValues); dataSheet.Range[dataSheet.Cells[3, currentDataColumn + dataColumnsWritten], dataSheet.Cells[3 + yValues.Length - 1, currentDataColumn + dataColumnsWritten]].Value2 = yValues; dataColumnsWritten += 1; seriesCounter++; } if (seriesCounter > 0) { chart.SetSourceData(Source: dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn + dataColumnsWritten - 1]]); Xls.SeriesCollection collection = chart.SeriesCollection(); Xls.Axes axes = null; try { for (int i = 0; i < collection.Count; i++) { collection.Item(i + 1).Name = graph.Series.ElementAt(i).Name; if (graph.Series.ElementAt(i).ShowInSecondaryVerticalAxis&& graph.HasSecondaryVerticalAxis) { collection.Item(i + 1).AxisGroup = Xls.XlAxisGroup.xlSecondary; } } axes = chart.Axes(); axes.Item(Xls.XlAxisType.xlCategory, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetHorizontalAxisTitle(); axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetPrimaryVerticalAxisTitle(); if (graph.HasSecondaryVerticalAxis) { axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).HasTitle = true; axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).AxisTitle.Text = graph.GetSecondaryVerticalAxisTitle(); } } finally { if (axes != null) { Marshal.FinalReleaseComObject(axes); } Marshal.FinalReleaseComObject(collection); } } }
public void CreateChart(string pivotTableName, int chartTop, string cTitle) { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000"); selectedRange = IdentifyPivotRangesByName(pivotTableName); shapes = activeSheet.Shapes; // Width original 255 if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { // 204 is a nice style with shadow shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210, NewLayout: true).Select(); } else { shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210).Select(); } chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = true; chart.ChartTitle.Text = cTitle; chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray //chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); //chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels chart.SetSourceData(selectedRange); chart.HasLegend = false; chart.ApplyDataLabels(); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { chart.FullSeriesCollection(1).DataLabels.ShowValue = true; } else { chart.SeriesCollection(1).DataLabels.ShowValue = true; } if (pivotTableName == "PivotTableApplicationName") { chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward; } selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); // chart.Export(pivotTableName + ".png"); } catch (Exception ex) { ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message); throw; } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }
private void Exportar(String Empresa, Int32 Anio, String Titulo, String SubTitulo, String Nombre, System.Data.DataTable Reporte, System.Data.DataTable Comparacion1, System.Data.DataTable Comparacion2, Boolean MostrarRebate = false) { XLExcel.Application xlApplication; XLExcel.Workbook xlWorkbook; XLExcel.Worksheet xlWorksheetData; XLExcel.Worksheet xlWorksheetPivot; XLExcel.Worksheet xlWorksheetComparacion; XLExcel.Worksheet xlWorksheetPivotRebate = null; XLExcel.Worksheet xlWorksheetPivotFFVV = null; XLExcel.Worksheet xlWorksheetPivotFFVVServicios = null; XLExcel.Range xlRange; XLExcel.Range xlRangeComparacion; object misValue = System.Reflection.Missing.Value; xlApplication = new XLExcel.Application(); Int32 _WorksheetsIndex = 1; xlWorkbook = xlApplication.Workbooks.Add(misValue); xlWorksheetData = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetData.Name = "BD"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetData, misValue, misValue); } xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : ""); if (MostrarRebate) { _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE"; } _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotFFVV.Name = "FUERZA VENTAS"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotFFVVServicios.Name = "FFVV POR SERVICIOS"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetComparacion.Name = "COMPARATIVO"; try { String fuente = "Calibri"; int fontsize = 11; int _sheetIndex = 1; Int32 rowIndex = 0; Int32 colIndex = 0; #region [ Statment ] xlWorksheetData.Activate(); //HEADER var _columns = new Object[1, Reporte.Columns.Count]; foreach (System.Data.DataColumn _column in Reporte.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[1, Reporte.Columns.Count]]; xlRange.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS var _data = new Object[Reporte.Rows.Count, Reporte.Columns.Count]; foreach (System.Data.DataRow _row in Reporte.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Reporte.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[2, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlRange.Value2 = _data; #endregion #region [ Comparacion ] xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 6 : 5)]; xlWorksheetComparacion.Name = "COMPARATIVO"; xlWorksheetComparacion.Activate(); rowIndex = 0; colIndex = 0; //HEADER _columns = new Object[1, Comparacion1.Columns.Count]; foreach (System.Data.DataColumn _column in Comparacion1.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, Comparacion1.Columns.Count]]; xlRangeComparacion.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS _data = new Object[Comparacion1.Rows.Count, Comparacion1.Columns.Count]; foreach (System.Data.DataRow _row in Comparacion1.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Comparacion1.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[8, 1], xlWorksheetComparacion.Cells[Comparacion1.Rows.Count + 7, Comparacion1.Columns.Count]]; xlRangeComparacion.Value2 = _data; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 1]]; xlRangeComparacion.Value = (Anio - 1).ToString(); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 14]]; setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 14]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 1]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; rowIndex = 0; colIndex = 0; //HEADER _columns = new Object[1, Comparacion2.Columns.Count]; foreach (System.Data.DataColumn _column in Comparacion2.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, Comparacion2.Columns.Count]]; xlRangeComparacion.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS _data = new Object[Comparacion2.Rows.Count, Comparacion2.Columns.Count]; foreach (System.Data.DataRow _row in Comparacion2.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Comparacion2.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[13, 1], xlWorksheetComparacion.Cells[Comparacion2.Rows.Count + 12, Comparacion2.Columns.Count]]; xlRangeComparacion.Value2 = _data; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 1]]; xlRangeComparacion.Value = Anio.ToString(); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 14]]; setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 14]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 1]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; XLExcel.Chart XLChart1 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 250, 500, 300).Chart; XLExcel.SeriesCollection XLSeriesCollection1 = XLChart1.SeriesCollection(); XLExcel.Series XLSerie1 = XLSeriesCollection1.NewSeries(); XLSerie1.Name = "=COMPARATIVO!$A$7"; XLSerie1.Values = "=COMPARATIVO!$B$8:$M$8"; XLSerie1.XValues = "=COMPARATIVO!$B$7:$M$7"; XLExcel.Series XLSerie2 = XLSeriesCollection1.NewSeries(); XLSerie2.Name = "=COMPARATIVO!$A$12"; XLSerie2.Values = "=COMPARATIVO!$B$13:$M$13"; XLSerie2.XValues = "=COMPARATIVO!$B$7:$M$7"; XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart); XLChart1.ChartTitle.Text = String.Format("COMPARATIVO {0} - TEUS {1} VS. {2}", Titulo, (Anio - 1), Anio); XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); XLExcel.Axis XlAxis1 = XLChart1.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis; XlAxis1.HasTitle = true; XlAxis1.AxisTitle.Caption = "TEUS"; //XLChart1.Activate(); XLChart1.Refresh(); XLExcel.Chart XLChart2 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 600, 500, 300).Chart; XLExcel.SeriesCollection XLSeriesCollection2 = XLChart2.SeriesCollection(); XLExcel.Series XLSerie3 = XLSeriesCollection2.NewSeries(); XLSerie3.Name = "=COMPARATIVO!$A$7"; XLSerie3.Values = "=COMPARATIVO!$B$9:$M$9"; XLSerie3.XValues = "=COMPARATIVO!$B$7:$M$7"; XLExcel.Series XLSerie4 = XLSeriesCollection2.NewSeries(); XLSerie4.Name = "=COMPARATIVO!$A$12"; XLSerie4.Values = "=COMPARATIVO!$B$14:$M$14"; XLSerie4.XValues = "=COMPARATIVO!$B$7:$M$7"; XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart); XLChart2.ChartTitle.Text = String.Format("COMPARATIVO {0} - PROFIT {1} VS. {2}", Titulo, (Anio - 1), Anio); XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); XLExcel.Axis XlAxis2 = XLChart2.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis; XlAxis2.HasTitle = true; XlAxis2.AxisTitle.Caption = "PROFIT"; //XLChart2.Activate(); XLChart2.Refresh(); //TITLE xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[2, 1], xlWorksheetComparacion.Cells[2, 14]]; xlRangeComparacion.MergeCells = true; xlRangeComparacion.Value = Titulo + " - COMPARATIVO TEUS Y PROFIT"; xlRangeComparacion.Font.Bold = true; xlRangeComparacion.Font.Size = 16; xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[3, 1], xlWorksheetComparacion.Cells[3, 14]]; xlRangeComparacion.MergeCells = true; xlRangeComparacion.Value = SubTitulo + " - " + (Anio - 1).ToString() + " VS. " + Anio.ToString(); xlRangeComparacion.Font.Bold = true; xlRangeComparacion.Font.Size = 12; xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; #endregion #region [ TOTAL SIN REBATE ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[2]; xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : ""); xlWorksheetPivot.Activate(); XLExcel.Range xlRangePivot; //TITLE xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[2, 1], xlWorksheetPivot.Cells[2, 14]]; xlRangePivot.MergeCells = true; xlRangePivot.Value = Empresa + "-" + Titulo; xlRangePivot.Font.Bold = true; xlRangePivot.Font.Size = 16; xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[3, 1], xlWorksheetPivot.Cells[3, 14]]; xlRangePivot.MergeCells = true; xlRangePivot.Value = SubTitulo + " " + Anio.ToString(); xlRangePivot.Font.Bold = true; xlRangePivot.Font.Size = 12; xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[6, 1], xlWorksheetPivot.Cells[6, 1]];; // create Pivot Cache and Pivot Table XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTable = (XLExcel.PivotTable)xlWorksheetPivot.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivot, TableName: "SIN REBATE"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMES = (XLExcel.PivotField)XLPivotTable.PivotFields("MES"); XLPivotFieldMES.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMES.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDOR = (XLExcel.PivotField)XLPivotTable.PivotFields("VENDEDOR"); XLPivotFieldVENDEDOR.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDOR.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUS = (XLExcel.PivotField)XLPivotTable.PivotFields("TEUS"); XLPivotFieldTEUS.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUS.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUS.Name = "Sum TEUS"; XLExcel.PivotField XLPivotFieldRENT_TOTAL = (XLExcel.PivotField)XLPivotTable.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTAL.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTAL.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTAL.Name = "Sum RENT_TOTAL"; XLPivotTable.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTable.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTable.DataPivotField.Position = 2; XLPivotTable.TableStyle2 = "PivotStyleLight18"; XLPivotTable.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion if (MostrarRebate) { #region [ TOTAL CON REBATE ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[3]; xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE"; xlWorksheetPivotRebate.Activate(); XLExcel.Range xlRangePivotRebate; //TITLE xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[2, 1], xlWorksheetPivotRebate.Cells[2, 14]]; xlRangePivotRebate.MergeCells = true; xlRangePivotRebate.Value = Empresa + "-" + Titulo; xlRangePivotRebate.Font.Bold = true; xlRangePivotRebate.Font.Size = 16; xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[3, 1], xlWorksheetPivotRebate.Cells[3, 14]]; xlRangePivotRebate.MergeCells = true; xlRangePivotRebate.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotRebate.Font.Bold = true; xlRangePivotRebate.Font.Size = 12; xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[6, 1], xlWorksheetPivotRebate.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCacheRebate = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableRebate = (XLExcel.PivotTable)xlWorksheetPivotRebate.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotRebate, TableName: "CON REBATE"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("MES"); XLPivotFieldMESRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESRebate.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("VENDEDOR"); XLPivotFieldVENDEDORRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORRebate.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("TEUS"); XLPivotFieldTEUSRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSRebate.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSRebate.Name = "Sum TEUS"; XLExcel.PivotField XLPivotFieldRENT_TOTALRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALRebate.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALRebate.Name = "Sum RENTABILIDAD_CON_REBATE"; XLPivotTableRebate.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableRebate.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableRebate.DataPivotField.Position = 2; XLPivotTableRebate.TableStyle2 = "PivotStyleLight18"; XLPivotTableRebate.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion } #region [ FUERZA DE VENTAS ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 4 : 3)]; xlWorksheetPivotFFVV.Name = "FUERZA VENTAS"; xlWorksheetPivotFFVV.Activate(); XLExcel.Range xlRangePivotFFVV; //TITLE xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[2, 1], xlWorksheetPivotFFVV.Cells[2, 14]]; xlRangePivotFFVV.MergeCells = true; xlRangePivotFFVV.Value = Empresa + "-" + Titulo; xlRangePivotFFVV.Font.Bold = true; xlRangePivotFFVV.Font.Size = 16; xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[3, 1], xlWorksheetPivotFFVV.Cells[3, 14]]; xlRangePivotFFVV.MergeCells = true; xlRangePivotFFVV.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotFFVV.Font.Bold = true; xlRangePivotFFVV.Font.Size = 12; xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[6, 1], xlWorksheetPivotFFVV.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableFFVV = (XLExcel.PivotTable)xlWorksheetPivotFFVV.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVV, TableName: "FFVV"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("MES"); XLPivotFieldMESFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESFFVV.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("VENDEDOR"); XLPivotFieldVENDEDORFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORFFVV.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("TEUS"); XLPivotFieldTEUSFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSFFVV.Name = "Sum TEUS"; if (MostrarRebate) { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENTABILIDAD_CON_REBATE"; } else { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENT_TOTAL"; } XLPivotTableFFVV.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableFFVV.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableFFVV.DataPivotField.Position = 2; XLPivotTableFFVV.TableStyle2 = "PivotStyleLight18"; XLPivotTableFFVV.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion #region [ FUERZA DE VENTAS TRAFICO ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 5 : 4)]; xlWorksheetPivotFFVVServicios.Name = "FF VV Servicios"; xlWorksheetPivotFFVVServicios.Activate(); XLExcel.Range xlRangePivotFFVVServicios; //TITLE xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[2, 1], xlWorksheetPivotFFVVServicios.Cells[2, 14]]; xlRangePivotFFVVServicios.MergeCells = true; xlRangePivotFFVVServicios.Value = Empresa + "-" + Titulo; xlRangePivotFFVVServicios.Font.Bold = true; xlRangePivotFFVVServicios.Font.Size = 16; xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[3, 1], xlWorksheetPivotFFVVServicios.Cells[3, 14]]; xlRangePivotFFVVServicios.MergeCells = true; xlRangePivotFFVVServicios.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotFFVVServicios.Font.Bold = true; xlRangePivotFFVVServicios.Font.Size = 12; xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[6, 1], xlWorksheetPivotFFVVServicios.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableFFVVServicios = (XLExcel.PivotTable)xlWorksheetPivotFFVVServicios.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVVServicios, TableName: "FFVV Servicios"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("MES"); XLPivotFieldMESFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESFFVVServicios.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TRAFICO"); XLPivotFieldVENDEDORFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORFFVVServicios.Name = "TRAFICO"; XLExcel.PivotField XLPivotFieldSERVICIOFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("VENDEDOR"); XLPivotFieldSERVICIOFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldSERVICIOFFVVServicios.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TEUS"); XLPivotFieldTEUSFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSFFVVServicios.Name = "Sum TEUS"; if (MostrarRebate) { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENTABILIDAD_CON_REBATE"; } else { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENT_TOTAL"; } XLPivotTableFFVVServicios.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableFFVVServicios.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableFFVVServicios.DataPivotField.Position = 2; XLPivotTableFFVVServicios.TableStyle2 = "PivotStyleLight18"; XLPivotTableFFVVServicios.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion xlApplication.Visible = true; releaseObject(xlWorksheetData); releaseObject(xlWorksheetPivot); if (MostrarRebate) { releaseObject(xlWorksheetPivotRebate); } releaseObject(xlWorksheetComparacion); releaseObject(xlWorkbook); releaseObject(xlApplication); } catch (Exception ex) { releaseObject(xlWorksheetData); releaseObject(xlWorksheetPivot); if (MostrarRebate) { releaseObject(xlWorksheetPivotRebate); } releaseObject(xlWorksheetComparacion); releaseObject(xlWorkbook); releaseObject(xlApplication); throw ex; } }
public void newTry(Office.IRibbonControl control) { //Limpar as respostas existentes no Inquérito //this.addin.deleteResponses(globalUrl); JObject responses = this.addin.getFormResponses(globalUrl); List <Question> questions = parseResponses(responses); int i = 0; foreach (Excel.ChartObject xlsChart in xlsCharts) { xlsChart.Delete(); Excel.Worksheet dataSheet = dataWorkSheets[i]; //Setting the range of chart string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; string lowerRange = "1"; int upRange = questions[i].Choices.Count() + 1; string upperRange = upRange.ToString(); int lastCellUsed = 0; //Descobrir o initial range for (int x = 0; x < 70; x++) { if (dataSheet.Cells[2, x + 1].Value != null) { lastCellUsed = x; } } lowerRange = rangeids[lastCellUsed + 2] + lowerRange; lowerRangeGlobal = lowerRange[0].ToString(); if (questions[i].Type == "GRID") { int aux = 0; string categoria1 = questions[i].Choices[0].row; for (int y = 0; y < questions[i].Choices.Count; y++) { if (questions[i].Choices[y].row == categoria1) { aux++; } } upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString(); gridUpperRangeGlobal = upperRange[0].ToString(); } else { upperRange = rangeids[lastCellUsed + 3] + upperRange; upperRangeGlobal = upperRange[0].ToString(); } Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange); Excel.ListObject tbl1; string tableName = "Tabela"; //Applying the set range on chart data table DateTime date1 = DateTime.Now; dataSheet.Cells.get_Range("A50").FormulaR1C1 = tRange; tableName = date1.ToString(); dataSheet.Cells.get_Range("A51").FormulaR1C1 = tableName; dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName; tbl1 = dataSheet.ListObjects[tableName]; tbl1.Resize(tRange); //Setting values for categories and respective series data string option = rangeids[lastCellUsed + 2]; string count = rangeids[lastCellUsed + 3]; List <string> rows = new List <string>(); if (questions[i].Type == "GRID") { int iteraux = 2; for (var j = 0; j < questions[i].Choices.Count; j++) { string optionaux = option + iteraux; if (!rows.Contains(questions[i].Choices[j].row)) { dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row; rows.Add(questions[i].Choices[j].row); iteraux++; } } string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; int column_index = 0; bool series_written = false; for (int v = 0; v < rows.Count; v++) { string categoria = rows[v]; column_index = lastCellUsed + 2; for (int u = 0; u < questions[i].Choices.Count; u++) { if (questions[i].Choices[u].row == categoria) { string celula = columnids[column_index + 1] + (v + 2).ToString(); dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString(); if (!series_written) { dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option; } column_index++; } } series_written = true; } dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString(); } else if (questions[i].Type == "SCALE") { for (var j = 0; j < questions[i].Choices.Count; j++) { int index1 = j + 2; string optionaux = option + index1; dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -"; } for (var k = 0; k < questions[i].Choices.Count; k++) { int index2 = k + 2; string countaux = count + index2; dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString(); } dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria"; string auxUpperRange = upperRange[0] + "1"; dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString(); } else { for (var j = 0; j < questions[i].Choices.Count; j++) { int index1 = j + 2; string optionaux = option + index1; dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option; } for (var k = 0; k < questions[i].Choices.Count; k++) { int index2 = k + 2; string countaux = count + index2; dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString(); } dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria"; string auxUpperRange = upperRange[0] + "1"; dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString(); } // Insert graphic in Excel //Output do Gráfico float width = 500F; float left = 230F; if (questions[i].Choices.Count > 6) { width = 750F; left = 130F; } else if (questions[i].Choices.Count > 12) { width = 1100; left = 80F; } //Save value of last Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250); Excel.Chart chartPage = myChart.Chart; object paramMissing = Type.Missing; // Declare variables for the Chart.ChartWizard method. object paramChartFormat = 1; object paramCategoryLabels = 0; object paramSeriesLabels = 0; bool paramHasLegend = true; // Create a new chart of the data. myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows, paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing); chartRange = dataSheet.get_Range(lowerRange, upperRange); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); int maxScale = 0; for (int h = 0; h < questions[i].Choices.Count; h++) { if (questions[i].Choices[h].count > maxScale) { maxScale = questions[i].Choices[h].count; } } if (questions[i].Type != "GRID") { chartPage.ChartTitle.Delete(); } excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5; excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10; excelaxis.MinimumScale = 0; excelaxis.MaximumScale = maxScale + 10.0; PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide; pptSlides[i].Select(); try { myChart.Copy(); } catch (COMException e) { Thread.Sleep(500); myChart.Copy(); } shapeRange = pptSlides[i].Shapes.Paste(); PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2]; previousGraph.Delete(); shapeRange.Left = left; shapeRange.Top = 160F; shapeRange.Height = 350F; shapeRange.Width = width; xlsChartsTemp.Add(myChart); i++; } xlsCharts = xlsChartsTemp; xlsChartsTemp = new List <Excel.ChartObject>(); newTryBool = true; newTryCount++; // dataWorkbook.Save(); }
public void generateResponsesSlides(Office.IRibbonControl control) { JObject responses = this.addin.getFormResponses(globalUrl); string fileName = this.addin.getTitle(globalUrl); string paramWorkbookPath = @".\" + fileName + ".xlsx"; string paramPowerpointPath = @".\" + fileName + ".pptx"; List <Question> questions = parseResponses(responses); PowerPoint.CustomLayout layout = this.addin.Application.ActivePresentation.SlideMaster.CustomLayouts[6]; for (int i = 0; i < questions.Count(); i++) { //Adicionar novo slide e mudar slide de foco pptSlide = this.addin.Application.ActivePresentation.Slides.AddSlide(this.addin.currentSlide + 1, layout); pptSlides.Add(pptSlide); this.addin.Application.ActiveWindow.Presentation.Slides[this.addin.currentSlide + 1].Select(); this.addin.currentSlide++; //Output do Titulo da Pergunta this.addin.Application.ActiveWindow.View.Slide.Shapes.Title.TextFrame.TextRange.Text = questions[i].Title; Excel.Worksheet dataSheet; if (i == 0) { //Create instance to Excel workbook to work with chart data Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; if (!File.Exists(paramWorkbookPath)) { dataWorkbook = excelApp.Workbooks.Add(); dataWorkbooks.Add(dataWorkbook); dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized; //Accessing the data worksheet for chart dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]); dataWorkSheets.Add(dataSheet); } else { dataWorkbook = excelApp.Workbooks.Open(paramWorkbookPath); dataWorkbooks.Add(dataWorkbook); dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized; //Accessing the data worksheet for chart dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]); dataWorkSheets.Add(dataSheet); } } else { dataSheet = dataWorkbook.Worksheets.Add(); dataWorkSheets.Add(dataSheet); } //Setting the range of chart string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; string lowerRange = "1"; int upRange = questions[i].Choices.Count() + 1; string upperRange = upRange.ToString(); lowerRange = "A" + lowerRange; //TODO TIRAR ESTE "F" HARDCODED if (questions[i].Type == "GRID") { upperRange = "F" + upRange / 2; int aux = 0; string categoria1 = questions[i].Choices[0].row; for (int y = 0; y < questions[i].Choices.Count; y++) { if (questions[i].Choices[y].row == categoria1) { aux++; } } upperRange = rangeids[aux] + (questions[i].Choices.Count / 2 - 1).ToString(); gridUpperRangeGlobal = upperRange; dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange; } else { upperRange = "B" + upperRange; dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange; } Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange); Excel.ListObject tbl1; string tableName = "Tabela"; //Applying the set range on chart data table tableName = tableName + i; dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName; tbl1 = dataSheet.ListObjects[tableName]; tbl1.Resize(tRange); //Setting values for categories and respective series data string option = "A"; string count = "B"; List <string> rows = new List <string>(); if (questions[i].Type == "GRID") { int iteraux = 2; for (var j = 0; j < questions[i].Choices.Count; j++) { string optionaux = option + iteraux; if (!rows.Contains(questions[i].Choices[j].row)) { dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row; rows.Add(questions[i].Choices[j].row); iteraux++; } } string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H" }; int column_index = 0; bool series_written = false; for (int v = 0; v < rows.Count; v++) { string categoria = rows[v]; column_index = 0; for (int u = 0; u < questions[i].Choices.Count; u++) { if (questions[i].Choices[u].row == categoria) { string celula = columnids[column_index + 1] + (v + 2).ToString(); dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString(); if (!series_written) { dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option; } column_index++; } } series_written = true; } dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Try1"; } else if (questions[i].Type == "SCALE") { for (var j = 0; j < questions[i].Choices.Count; j++) { int index1 = j + 2; string optionaux = option + index1; dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -"; } for (var k = 0; k < questions[i].Choices.Count; k++) { int index2 = k + 2; string countaux = count + index2; dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString(); } dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria"; dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1"; } else { for (var j = 0; j < questions[i].Choices.Count; j++) { int index1 = j + 2; string optionaux = option + index1; dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option; } for (var k = 0; k < questions[i].Choices.Count; k++) { int index2 = k + 2; string countaux = count + index2; dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString(); } dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria"; dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1"; } // Insert graphic in Excel //Output do Gráfico float width = 500F; float left = 230F; if (questions[i].Choices.Count > 6) { width = 750F; left = 130F; } else if (questions[i].Choices.Count > 12) { width = 1100; left = 80F; } //Save value of last Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250); Excel.Chart chartPage = myChart.Chart; object paramMissing = Type.Missing; // Declare variables for the Chart.ChartWizard method. object paramChartFormat = 1; object paramCategoryLabels = 0; object paramSeriesLabels = 0; bool paramHasLegend = true; // Create a new chart of the data. myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows, paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing); chartRange = dataSheet.get_Range(lowerRange, upperRange); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; if (questions[i].Type != "GRID") { chartPage.ChartTitle.Delete(); } Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); int maxScale = 0; for (int h = 0; h < questions[i].Choices.Count; h++) { if (questions[i].Choices[h].count > maxScale) { maxScale = questions[i].Choices[h].count; } } excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5; excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10; excelaxis.MinimumScale = 0; excelaxis.MaximumScale = maxScale + 10.0; myChart.Copy(); shapeRange = pptSlide.Shapes.Paste(); // Position the chart on the slide. shapeRange.Left = left; shapeRange.Top = 160F; shapeRange.Height = 350F; shapeRange.Width = width; //Copy chart to xlsCharts xlsCharts.Add(myChart); } // Save excel file if (newTryBool == true) { //dataWorkbook.Save(); } else { //dataWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, //paramMissing, Excel.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing); } pptSlides[0].Select(); // Save the presentation. //this.addin.Application.ActivePresentation.SaveAs(paramPowerpointPath, PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Office.MsoTriState.msoTrue); }
/// <summary> /// Applies the trained neural network model to the selected predictor /// input data and displays the results in Excel. /// /// The results consists of 3 columns - the first contains the selected /// training set input (or predictor) values, the second the selected /// training set target values and the third contains the trained model /// output responses to the given input values. An XY-Scatter graph of /// the data is also produced. /// </summary> /// <param name="net">the trained neural network</param> /// private void ShowOutputInExcel(NeuralNet net) { List <double> dX = new List <double>(); List <double> dM = new List <double>(); // plot the model and target data in Excel Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; // open Excel and add a worksheet xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(Type.Missing); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // add a header line xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[1, 2] = "target"; xlWorkSheet.Cells[1, 3] = "model"; // add the model and training data for (int i = 0; i < mInputVecs.Count; i++) { // calculate the model response value given the predictor value from the training set dX = mInputVecs[i]; net.GetResponse(dX, dM); // the required values are stored in vectors and need re-scaling double xValue = dX[0] * ScaleFactor; double yValue = mTargetVecs[i][0] * ScaleFactor; double mValue = dM[0] * ScaleFactor; // write out the results xlWorkSheet.Cells[i + 2, 1] = xValue.ToString("G16"); xlWorkSheet.Cells[i + 2, 2] = yValue.ToString("G16"); xlWorkSheet.Cells[i + 2, 3] = mValue.ToString("G16"); } // plot the data as an xy-scatter graph Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject modelChart = (Excel.ChartObject)xlCharts.Add(200, 10, 450, 300); Excel.Chart chartPage = modelChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "C" + (mInputVecs.Count + 1).ToString()); chartPage.SetSourceData(chartRange); chartPage.ChartType = Excel.XlChartType.xlXYScatter; // set the axis labels List <string> colNames = new List <string>(); mDataTab.GetColumnNames(colNames); Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = true; xAxis.AxisTitle.Text = colNames[PredictorIdx]; Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); yAxis.HasTitle = true; yAxis.AxisTitle.Text = colNames[ResponseIdx]; // set the graph title chartPage.HasTitle = true; chartPage.ChartTitle.Font.Size = 12; chartPage.ChartTitle.Text = GraphTitle(); // make Excel visible to the user xlApp.Visible = true; // release resources ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlApp); }
/// <summary> /// Applies the trained neural network model to the selected predictor /// input data and outputs the results to an Excel .xls file. /// /// The output consists of 3 columns - the first contains the selected /// training set input (or predictor) values, the second the selected /// training set target values and the third contains the trained model /// output responses to the given input values. An XY-Scatter graph of /// the data is also produced. /// </summary> /// <param name="net">the trained neural network</param> /// <param name="fname">the name of the file to write the results to</param> /// private void GenerateExcelOutput(NeuralNet net, string fname) { List <double> dX = new List <double>(); List <double> dM = new List <double>(); // open Excel, add a workbook and obtain a worksheet Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(Type.Missing); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // stops Excel asking the user if they want to replace an existing file // the save file dialog will have already asked this question xlApp.DisplayAlerts = false; // add a header line xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[1, 2] = "target"; xlWorkSheet.Cells[1, 3] = "model"; // add the model and training data for (int i = 0; i < mInputVecs.Count; i++) { // calculate the model response value given the predictor value from the training set dX = mInputVecs[i]; net.GetResponse(dX, dM); // the required values are stored in vectors and need re-scaling double xValue = dX[0] * ScaleFactor; double yValue = mTargetVecs[i][0] * ScaleFactor; double mValue = dM[0] * ScaleFactor; // write out the results xlWorkSheet.Cells[i + 2, 1] = xValue.ToString("G16"); xlWorkSheet.Cells[i + 2, 2] = yValue.ToString("G16"); xlWorkSheet.Cells[i + 2, 3] = mValue.ToString("G16"); } // plot the data as an xy-scatter graph Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject modelChart = (Excel.ChartObject)xlCharts.Add(200, 10, 450, 300); Excel.Chart chartPage = modelChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "C" + (mInputVecs.Count + 1).ToString()); chartPage.SetSourceData(chartRange); chartPage.ChartType = Excel.XlChartType.xlXYScatter; // set the axis labels List <string> colNames = new List <string>(); mDataTab.GetColumnNames(colNames); Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = true; xAxis.AxisTitle.Text = colNames[PredictorIdx]; Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); yAxis.HasTitle = true; yAxis.AxisTitle.Text = colNames[ResponseIdx]; // set the graph title chartPage.HasTitle = true; chartPage.ChartTitle.Font.Size = 12; chartPage.ChartTitle.Text = GraphTitle(); // save the workbook and close Excel xlWorkBook.SaveAs(fname, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWorkBook.Close(true, Type.Missing, Type.Missing); xlApp.DisplayAlerts = true; xlApp.Quit(); // release resources ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlApp); }
public async Task <byte[]> GenerateChartImageFromXlsxFile <TListEntity>(string excelChartTemplateName, string outPutFolder, IEnumerable <TListEntity> inputItems, int seriesCount, int xAxisDataCount) { if (inputItems != null && inputItems.Count() > 0) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; outPutFolder = System.Configuration.ConfigurationManager.AppSettings["ChartOutputFolder"].ToString(); //"C:\\ReportsGenerated\\Chart\\OutPuts"; excelChartTemplateName = System.Configuration.ConfigurationManager.AppSettings["ChartTemplate"].ToString(); //"C:\\ReportsGenerated\\Chart\\InPuts\\FinalTemplate_Test_good_series_xaxis_1.xlsx"; // Console.WriteLine(excelChartTemplateName); Console.WriteLine(outPutFolder); object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); //xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkBook = xlApp.Workbooks.Open(excelChartTemplateName); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { string[] properties = new string[] { "Name", "ValuePrev1", "ValuePrev2", "ValuePrev3", "ValuePrev4", "ValuePrev5", "ValuePrev6" }; List <TListEntity> items = new List <TListEntity>(); foreach (var item in inputItems) { items.Add(item); } Excel.Range chartRangeXaxis; Excel.Range chartRange; var rangeToValue = ""; var xAxisRangeToValue = ""; int i = 0; var h = 0; int[] alphabetNumArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 }; int i_clear = 0; var h_clear = 0; //clearing existing values for (; i_clear < 4; i_clear++) //rows clearing { for (; h_clear < 4; h_clear++) //columns clearing { xlWorkSheet.Cells[(i_clear + 1), alphabetNumArray[h_clear]] = ""; } } for (i = 0; i < items.Count(); i++) { for (h = 0; h < xAxisDataCount + 1; h++) { string propertyName = properties[h]; var property = items[i].GetType().GetProperty(propertyName); if (property != null) { var value = property.GetValue(items[i], null); if (value != null)//required to check, for not allowing empty values { xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = value; //xlWorkSheet.Cells["A2"] = value;//this type was not works on microsoft.office.interop.excel //xlWorkSheet.Cells[1,2] = value;//this is correct one } //else // xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = ""; } } } string[] alphabetArray = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; rangeToValue = alphabetArray[xAxisDataCount] + i; xAxisRangeToValue = alphabetArray[xAxisDataCount] + 1; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); //Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);//for new one Excel.ChartObject chartObject = (Excel.ChartObject)xlCharts.Item(1); Excel.Chart chart = chartObject.Chart; chartRangeXaxis = xlWorkSheet.get_Range("B1", xAxisRangeToValue); chartRange = xlWorkSheet.get_Range("A2", rangeToValue); //chartPage.SetSourceData(chartRangeXaxis, Excel.XlRowCol.xlRows); chart.SetSourceData(chartRange, Excel.XlRowCol.xlRows);//sucess //chartPage.ChartType = Excel.XlChartType.xlLineMarkers;//if creating new chart uncomment this one Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = false; //xAxis.AxisTitle.Caption = "Time"; xAxis.CategoryNames = (Excel.Range)xlWorkSheet.get_Range("B1", xAxisRangeToValue); Excel.Range formatRange; formatRange = xlWorkSheet.get_Range("B2", rangeToValue); formatRange.NumberFormat = "#,##0.00"; //chartPage.ChartArea.Width = 200; //chartPage.ChartArea.Height = 100; // chartPage.CategoryLabelLevel = Excel.XlCategoryLabelLevel.xlCategoryLabelLevelCustom; //not working properly //Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(); //Excel.Series series = seriesCollection.NewSeries(); //series.XValues = chartRangeXaxis; //have to set for data labels number format as 0.00 //var series = chartObject.Chart.SeriesCollection() as Excel.SeriesCollection; //foreach (var ser in series) //{ // var DataLabels = ((Excel.Series)ser).DataLabels(1); // DataLabels. //} var imageFileFullName = outPutFolder + @"\ChartImage_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".png"; chart.Export(imageFileFullName, "PNG", false); try { //xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString(), Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue); //xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); await this.releaseObject(xlWorkSheet); await this.releaseObject(xlWorkBook); await this.releaseObject(xlApp); } catch (Exception ex) { Console.WriteLine("Error closing Excel object" + ex.Message); Console.WriteLine(ex.StackTrace); } finally { if (xlWorkSheet != null) { Marshal.ReleaseComObject(xlWorkSheet); } if (xlWorkBook != null) { Marshal.ReleaseComObject(xlWorkBook); } if (xlApp != null) { Marshal.ReleaseComObject(xlApp); } } try { Console.WriteLine("Reading image"); byte[] byteArray = File.ReadAllBytes(imageFileFullName); Console.WriteLine("Reading image 1"); System.IO.DirectoryInfo di = new DirectoryInfo(outPutFolder); foreach (FileInfo file in di.GetFiles()) { file.Delete(); } return(byteArray); } catch (Exception ex) { Console.WriteLine("Failed to delete " + ex.Message); } return(null); } catch (Exception ex) { await this.releaseObject(xlWorkSheet); await this.releaseObject(xlWorkBook); await this.releaseObject(xlApp); } } return(null); }
// データテーブルをエクセルへエクスポート(XR管理図・ヒストグラム付き) public void ExportToExcelWithXrChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString(); string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString(); string address3 = "AB41:AC53"; string address4 = "F1:J" + row.ToString(); // チャート1(X-R管理図1) Excel.Range chartRange1; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlLine; chartPage1.HasLegend = false; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s2 = SeriesCollection1.Item(2); s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; Excel.Series s3 = SeriesCollection1.Item(3); s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; // チャート2(X-R管理図2) Excel.Range chartRange2; Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250); Excel.Chart chartPage2 = myChart2.Chart; chartRange2 = xlWorkSheet.get_Range(address2); chartPage2.SetSourceData(chartRange2, misValue); chartPage2.ChartType = Excel.XlChartType.xlLine; chartPage2.HasLegend = false; chartPage2.HasTitle = true; chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale; // チャート3(ヒストグラム) Excel.Range chartRange3; Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250); Excel.Chart chartPage3 = myChart3.Chart; string[,] formulas = new string[13, 3]; string[] formula1 = new string[] { "BIN", "=MIN(" + address4 + ")", "=AA42+(AA$53-AA$42)/10", "=AA43+(AA$53-AA$42)/10", "=AA44+(AA$53-AA$42)/10", "=AA45+(AA$53-AA$42)/10", "=AA46+(AA$53-AA$42)/10", "=AA47+(AA$53-AA$42)/10", "=AA48+(AA$53-AA$42)/10", "=AA49+(AA$53-AA$42)/10", "=AA50+(AA$53-AA$42)/10", "=AA51+(AA$53-AA$42)/10", "=MAX(" + address4 + ")", }; string[] formula2 = new string[] { @"LABEL", @"=TEXT(AA42,""0.0"")", @"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")", @"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")", @"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")", @"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")", @"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")", @"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")", @"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")", @"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")", @"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")", @"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")", @"=TEXT(AA53,""0.0"")" }; string[] formula3 = new string[] { @"FREQUENCY", @"=COUNTIF(" + address4 + @",""<=""&AA42)", @"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)", @"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)", @"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)", @"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)", @"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)", @"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)", @"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)", @"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)", @"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)", @"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)", @"=COUNTIF(" + address4 + @","">=""&AA53)" }; for (int i = 0; i < 13; i++) { xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i]; xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i]; xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i]; } chartRange3 = xlWorkSheet.get_Range(address3); chartPage3.SetSourceData(chartRange3, misValue); chartPage3.ChartType = Excel.XlChartType.xlColumnClustered; chartPage3.HasLegend = false; chartPage3.HasTitle = true; chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1); ChartGroup1.GapWidth = 0; xlApp.Visible = true; }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; //xlApp.Visible = true; xlWorkBook = xlApp.Workbooks.Open(fileUrl); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); //Left, Top, Width, Height Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600); Excel.Chart chartPage = myChart.Chart; int lastRow = xlWorkSheet.UsedRange.Rows.Count; chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers; Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); horizAxis.MaximumScaleIsAuto = false; horizAxis.MaximumScale = lastRow; horizAxis.MinimumScaleIsAuto = false; horizAxis.MinimumScale = 0; horizAxis.HasTitle = false; //horizAxis.AxisTitle.Text = "across the bottom"; Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); vertAxis.HasMajorGridlines = false; // change this to whatever you wish vertAxis.HasTitle = true; vertAxis.AxisTitle.Text = "Volts"; vertAxis.MaximumScaleIsAuto = true; //vertAxis.MaximumScale = 4; // you can pick this based on your input vertAxis.MinimumScaleIsAuto = true; // vertAxis.MinimumScale = -4; //chartPage.ApplyChartTemplate("c:\\ctmp.crtx"); chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false); Image image = Image.FromFile(fileDir + "/Chart1.PNG"); pictureBox1.Image = image; pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; //xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
// データテーブルをエクセルへエクスポート(箱ヒゲ図付き) public void ExportToExcelWithBoxPlotChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString(); string address2 = "M2:M" + row.ToString(); string address3 = "N2:N" + row.ToString(); Excel.Range chartRange1; Excel.Range chartRange2; Excel.Range chartRange3; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartRange2 = xlWorkSheet.get_Range(address2); chartRange3 = xlWorkSheet.get_Range(address3); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlColumnStacked; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); chartPage1.HasLegend = false; Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s5 = oSeriesCollection.Item(5); s5.ChartType = Excel.XlChartType.xlLine; s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s4 = oSeriesCollection.Item(4); s4.ChartType = Excel.XlChartType.xlLine; s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s1 = oSeriesCollection.Item(1); s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; s1.Format.Fill.Transparency = 1; s1.Format.Line.Weight = 0; s1.HasErrorBars = true; s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2); Excel.Series s3 = oSeriesCollection.Item(3); s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s3.Format.Line.Weight = 1.0F; s3.HasErrorBars = true; s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3); Excel.Series s2 = oSeriesCollection.Item(2); s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s2.Format.Line.Weight = 1.0F; xlApp.Visible = true; }
private void graph(bool threeD) { loadSteps(threeD); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data int max = (int)((end - start) / interval); int ymax = (int)((yend - ystart) / yinterval); int index = 1; progressBar1.Maximum = max; for (int i = 1; i <= max; i++) { progressBar1.Value = i; progressBar1.Refresh(); double x = i * interval + start; for (int j = 1; j <= ymax; j++) { double y = j * yinterval + ystart; string xlEquation = "="; foreach (char c in T_Equation.Text) { Application.DoEvents(); if (c == 'X' || c == 'x') { xlEquation += x; } else if (threeD && (c == 'Y' || c == 'y')) { xlEquation += y; } else { xlEquation += c; } } ++index; try { if (!threeD) { xlWorkSheet.Cells[index, 1] = x.ToString(); xlWorkSheet.Cells[index, 2] = xlEquation; } else { xlWorkSheet.Cells[1, j + 1] = y.ToString(); xlWorkSheet.Cells[i + 1, 1] = x.ToString(); xlWorkSheet.Cells[i + 1, j + 1] = xlEquation; } } catch (Exception ex) { MessageBox.Show("Invalid equation.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } } Excel.Range chartYRange, chartXRange, chartZRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 465, 330); Excel.Chart chartPage = myChart.Chart; int last = max * ymax; chartXRange = xlWorkSheet.get_Range("A1", "A" + last); chartYRange = xlWorkSheet.get_Range("B1", "B" + last); if (!threeD) { chartPage.SetSourceData(chartYRange, misValue); } else { try { char endC = System.Convert.ToChar(Convert.ToInt32('B') + ymax - 1); chartZRange = xlWorkSheet.get_Range("A1", endC + "" + max); chartPage.SetSourceData(chartZRange, misValue); } catch (Exception ex) { MessageBox.Show("Chart y-range too large.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } chartPage.ChartType = (threeD) ? Excel.XlChartType.xlSurface : Excel.XlChartType.xlLine; chartPage.Legend.Clear(); if (!threeD) { chartPage.SeriesCollection(1).XValues = chartXRange; } Excel.Axis yaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); yaxis.HasTitle = true; yaxis.AxisTitle.Text = "Y"; Excel.Axis xaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xaxis.HasTitle = true; xaxis.AxisTitle.Text = "X"; if (threeD) { Excel.Axis zAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); zAxis.HasTitle = true; zAxis.AxisTitle.Text = "Z"; } //export chart as picture file if (pictureBox1.Image != null) { pictureBox1.Image.Dispose(); pictureBox1.Image = null; } chartPage.Export(AppDomain.CurrentDomain.BaseDirectory + "test.bmp", "BMP", misValue); //load picture to picturebox pictureBox1.Image = new Bitmap(AppDomain.CurrentDomain.BaseDirectory + "test.bmp"); xlWorkBook.Close(saveXL, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); releaseObject(chartPage); }