public void Excel_Create(string file_name, int latin, int kiril, int num) { exel.Application application = new exel.Application(); exel.Workbook workbook = application.Workbooks.Add(); exel.Worksheet worksheet = (exel.Worksheet)workbook.ActiveSheet; worksheet.Name = file_name; worksheet.Cells[1, 1] = "Латинские буквы"; worksheet.Cells[2, 1] = "Кириллица"; worksheet.Cells[3, 1] = "Числа"; worksheet.Cells[1, 2] = latin; worksheet.Cells[2, 2] = kiril; worksheet.Cells[3, 2] = num; worksheet.Columns.AutoFit(); exel.ChartObjects chartObjects = (exel.ChartObjects)worksheet.ChartObjects(Type.Missing); exel.ChartObject chartObject = chartObjects.Add(50, 50, 250, 250); exel.Chart chart = chartObject.Chart; exel.SeriesCollection seriesCollection = (exel.SeriesCollection)chart.SeriesCollection(Type.Missing); exel.Series series = seriesCollection.NewSeries(); chart.ChartType = exel.XlChartType.xl3DPie; series.XValues = worksheet.get_Range("A1", "A3"); series.Values = worksheet.get_Range("B1", "B3"); workbook.SaveAs(); workbook.Close(); application.Quit(); }
void elementChart_MouseDown(int Button, int Shift, int x, int y) { if (flag == 1) { Excel.Chart chart = Globals.ThisAddIn.Application.ActiveChart; //Int32 ; Int32 elementID = 0; Int32 arg1 = 0; Int32 arg2 = 0; chart.GetChartElement(x, y, ref elementID, ref arg1, ref arg2); // string element = ((Excel.XlChartItem)elementID).ToString(); if (element == "xlSeries") { Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(arg1); Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); flag = 0; } } // MessageBox.Show("Chart element is: " + ((Excel.XlChartItem)elementID).ToString() // + "\n arg1 is: " + arg1.ToString() + "\n arg2 is: " + arg2.ToString()); }
static public void SetChartDataColor(string chartName, object[,] datas, Excel.ChartObjects chObjs, int colIdx) { foreach (Excel.ChartObject chObj in chObjs) { if (chObj.Name.Equals(chartName)) { Excel.Series oSeries = chObj.Chart.SeriesCollection(1); for (int i = 1; i <= datas.GetLength(0); i++) { object oVal = datas.GetValue(i, colIdx); double val = Convert.ToDouble(oVal); Excel.Point oPoint = oSeries.Points(i); if (val >= 1) { oPoint.Format.Fill.ForeColor.RGB = 0x0000FF; } else if ((val < 1) && (val >= 0.5)) { oPoint.Format.Fill.ForeColor.RGB = 0xCC99FF; } } return; } } }
public void FillExcel <T>(string name, IEnumerable <T> values, double step) { Excel.Application ex = new Excel.Application(); ex.Visible = true; ex.SheetsInNewWorkbook = 1; var workBook = ex.Workbooks.Add(Type.Missing); var sheet = (Excel.Worksheet)ex.Worksheets.Item[1]; sheet.Name = name; sheet.Cells[1, 1] = "x"; sheet.Cells[1, 2] = "y"; for (int i = 1; i < values.Count() + 1; i++) { sheet.Cells[i + 1, 1] = step * (i - 1); sheet.Cells[i + 1, 2] = values.ElementAt(i - 1); } Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(110, 0, 350, 250); Excel.Chart chart = myChart.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); series.XValues = sheet.get_Range("A2", "A" + (values.Count() + 1).ToString()); series.Values = sheet.get_Range("B2", "B" + (values.Count() + 1).ToString()); chart.ChartType = Excel.XlChartType.xlXYScatterSmooth; }
private void ButtonApply_Click(object sender, RoutedEventArgs e) { Excel.Worksheet workSheet2 = (Excel.Worksheet)_workBook.Sheets[2]; Excel.Worksheet workSheet3 = (Excel.Worksheet)_workBook.Sheets[3]; int rowIndex = 0; for (int x = 0; x <= 10; x++) { workSheet2.Cells[3, "F"].Value = x; double y = workSheet2.Cells[6, "F"].Value; workSheet3.Cells[rowIndex + 1, "H"].Value = x; workSheet3.Cells[rowIndex + 1, "I"].Value = y; rowIndex++; } Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet3.ChartObjects(Type.Missing); Excel.ChartObject myChart = chartObjs.Add(20, 60, 200, 200); Excel.Chart chart = myChart.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); series.XValues = workSheet3.get_Range("H1", "H10"); series.Values = workSheet3.get_Range("I1", "I10"); chart.ChartType = Excel.XlChartType.xlXYScatterSmooth; }
protected void CreateGraph(int columnIndex) { // Get the alphabetical column index... string columnAlphabetical = ((char)(columnIndex + 64)).ToString( ); // Activate the test Chart worksheet... this.testChartWorkSheet.Activate( ); // Get any charts currently existing in the worksheet... Excel.ChartObjects charts = (Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing); // Adds a chart at x = 100, y = 300, 500 points wide and 300 tall. Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1); Excel.Chart chart = chartObj.Chart; // Gets the data cells ( X values ) Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( ))); chart.SetSourceData(chartRange, Type.Missing); // Set the type of the chart... chart.ChartType = Excel.XlChartType.xlLine; // Get any existing series collection (there should be only one)... Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(seriesCollection.Count); // Gets the data cells ( Y values ) series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( )); }
public void addChartSeries(Excel.ChartObject chartObj, Excel.Range xRange, Excel.Range yRange, string name) { Excel.Series ser = chartObj.Chart.SeriesCollection(System.Reflection.Missing.Value).NewSeries(); ser.XValues = xRange; ser.Values = yRange; ser.Name = name; }
/// <summary> /// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs. /// </summary> /// <param name="values"></param> /// <param name="seriesTitle"></param> /// <param name="xAxis"></param> /// <param name="yAxis"></param> /// <param name="headers"></param> public static void PlotEachSeriesSeperately(List <List <double[]> > values, string seriesTitle = "", string xAxis = "", string yAxis = "", List <string> headers = null) { //Create a new excel workbook and sheet 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 = xlWorkbook.Worksheets.get_Item(1); //Export data to Excel Sheet from List<List<double>> //Set headers if given. if (headers != null) { for (var i = 0; i < headers.Count; i++) { xlWorksheet.Cells[1, i].Value = headers[i]; } } //Create Chart Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing); for (var i = 0; i < values.Count; i++) //For each series { var xValues = new double[values[i].Count]; var yValues = new double[values[i].Count]; for (var j = 0; j < values[i].Count; j++) //For each point in series { xValues[j] = values[i][j][0]; yValues[j] = values[i][j][1]; } //Create a new chart and offset from previous var offset = 300 * (i); Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300); Excel.Chart chart = myChart.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series series = seriesCollection.NewSeries(); series.Values = yValues; series.XValues = xValues; var title = seriesTitle + " " + i; chart.ChartWizard( Title: title, CategoryTitle: xAxis, ValueTitle: yAxis); } xlApp.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); }
public void ExcelDiagr2() { Excel.Application excelapp = new Excel.Application(); try { // excelapp.Visible = true; Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelapp.Workbooks.Open("book.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Sheets excelsheets = excelappworkbook.Worksheets; //excelappworkbook = excelappworkbooks[2]; //excelappworkbook.Activate(); //excelsheets = excelapp.ActiveWorkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelworksheet.Activate(); //Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Sheets[2]; Excel.Range excelcells = excelworksheet.get_Range("B1", "B12"); excelcells.Select(); Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelapp.ActiveChart.HasTitle = true; excelapp.ActiveChart.ChartTitle.Text = "Количество заказов"; DataForBD.iter = 5; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)).HasTitle = true; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Месяц"; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)).HasTitle = true; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Количество"; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)excelapp.ActiveChart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.Name = "Количество заказов"; //excelworksheet.Name = "Количество заказов"; excelapp.DisplayAlerts = false; excelappworkbook.SaveAs("book.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelapp.Quit(); } catch (Exception exc) { excelapp.Quit(); throw exc; } }
/// <summary> /// 设置系列的y坐标为副坐标 /// </summary> /// <param name="chart">chart</param> /// <param name="seriesIndex">index</param> public void SetChartSeriesCollectionY(Chart chart, int seriesIndex) { // chart.SeriesCollection(seriesIndex).Axes(XlAxisType.xlCategory, XlAxisGroup.xlSecondary); //Axis ysAxis = chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary); // ysAxis.HasTitle = string.IsNullOrEmpty(y) ? false : true; // ysAxis.AxisTitle.Text = y; Excel.Series se = (Excel.Series)chart.SeriesCollection(seriesIndex); se.AxisGroup = XlAxisGroup.xlSecondary; }
private void DrawFractionChart(Excel.Worksheet activeSheet, Excel.ChartObjects xlCharts, Excel.Range xRange, Excel.Range yRange) { Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 500, 200, 100); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); series1.XValues = activeSheet.Range["E1", "E3"]; series1.Values = activeSheet.Range["F1", "F3"]; chartPage.ChartType = Excel.XlChartType.xlDoughnut; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent, true, true, false, true, true, true, true); }
private void CreateChart(int length, Worksheet excelWorkSheet) { ChartObjects xlCharts = (ChartObjects)excelWorkSheet.ChartObjects(Type.Missing); ChartObject myChart = (ChartObject)xlCharts.Add(300, 0, length / 1.5, 350); Microsoft.Office.Interop.Excel.Chart chart = myChart.Chart; Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries(); series.XValues = excelWorkSheet.get_Range("B2", "B" + (length + 1)); series.Values = excelWorkSheet.get_Range("C2", "C" + (length + 1)); series.Name = "Fluctuation"; chart.ChartType = XlChartType.xlXYScatterSmooth; }
private void build_comp_graphs(Excel.Worksheet worksheet, Excel._Worksheet worksheet2, int irows, int orows) { for (int i = 1; i <= irows; i++) { string col1 = String.Format("A{0}", i); string col2 = String.Format("B{0}", i); string col3 = String.Format("C{0}", i); worksheet.Range[col1].Formula = String.Format("='yearly_steps'!{0}", col1); worksheet.Range[col2].Formula = String.Format("='yearly_steps'!{0}", col2); if (i == 4) { worksheet.Range[col3].Formula = String.Format("={0}", col2); } else if (i > 4) { worksheet.Range[col3].Formula = String.Format("=(({0} - {1}) * {2})+{3}", col1, String.Format("A{0}", i - 1), col2, String.Format("C{0}", i - 1)); } } Excel.ChartObjects charts = worksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400); Excel.Chart chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet2.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet2.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("B4:B{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; chartObject = charts.Add(200, 525, 750, 400); chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook wb = xlApp.Workbooks.Add(Missing.Value); Excel.Worksheet ws = wb.ActiveSheet; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[1, i + 2].Value = dataGridView1.Rows[i].Cells[0].Value.ToString(); } ws.Cells[2, 1].Value = "Amenities Sold"; ws.Cells[3, 1].Value = "Tickets Sold"; ws.Cells[4, 1].Value = "Commission Earned"; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[2, i + 2].Value = dataGridView1.Rows[i].Cells[1].Value.ToString(); ws.Cells[3, i + 2].Value = dataGridView1.Rows[i].Cells[2].Value.ToString(); ws.Cells[4, i + 2].Value = dataGridView1.Rows[i].Cells[3].Value.ToString(); } Excel.ChartObjects xlObj = ws.ChartObjects(Missing.Value); Excel.ChartObject obj = xlObj.Add(10, 50, 450, 250); Excel.Chart chart = obj.Chart; Excel.Series s = chart.SeriesCollection().Add(ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count]]); //s.XValues = ws.Range[ws.Cells[1, 2], ws.Cells[1, dataGridView1.Rows.Count - 1]]; //Excel.Range rangeSourceChart = ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count - 1]]; //chart.SetSourceData(rangeSourceChart, Excel.XlRowCol.xlRows); using (SaveFileDialog sf = new SaveFileDialog()) { sf.Filter = "Excel Files|*.xlsx"; sf.FileName = "CommissionReport"; if (sf.ShowDialog() == DialogResult.OK) { wb.SaveAs(sf.FileName); wb.Close(); xlApp.Quit(); } } }
//曲线删除 /// <summary> /// 移除指定的数据列及其依附的对象 /// </summary> /// <param name="DeletingSeriesIndex">要进行删除的数据列在集合中的索引下标值,注意:第一条曲线的下标值为1,而不是0。</param> /// <remarks>为了保存模板中的数据系列的格式,这里的删除并不是将数据列进行了真正的删除,而是将数据列的数据设置为空。 /// 这样的话,后期的数据曲线应该加载在最靠前而且没有数据的数据列中。</remarks> public virtual void DeleteSeries(int DeletingSeriesIndex) { SeriesTag with_1 = F_DicSeries_Tag[DeletingSeriesIndex]; Excel.Series with_2 = with_1.series; with_2.XValues = new object[] { null }; // in VB: with_2.XValues = {Nothing} //这里不能用.XValues = Nothing with_2.Values = new object[] { null }; //.Name = "" this.F_DicSeries_Tag.Remove(DeletingSeriesIndex); this.F_List_HasCurve[DeletingSeriesIndex - cst_LboundOfSeriesInCollection] = false; // ----------------------------------- 对图例的显示进行操作 LegendRefresh(F_List_HasCurve); this.F_CurvesCount--; }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Hrange = double.Parse(textBox_Bandwidth.Text); if (Hrange == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); int[] HSV0 = new int[3]; Graphic.RGB2HSV(RGB0, ref HSV0); int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } chart.Refresh(); worksheet.Activate(); }
private void build_orig_graphs(Excel.Worksheet worksheet, int orows) { Excel.ChartObjects charts = worksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400); Excel.Chart chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; chartObject = charts.Add(200, 525, 750, 400); chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", orows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", orows)]; series.Name = "Original Data"; }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } ratio = double.Parse(textBox_Bandwidth.Text);; if (ratio == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; int SquareSize; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2]))));// - Min_size) / (Max_size - Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2); //point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((double.Parse(str[i, 2]) - Min_size) / (Max_size - Min_size) * ratio) + 2; //PointSize[i - 1, 0] = SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize; point.MarkerSize = SquareSize; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Transparency = 0.3F; chart.Refresh(); worksheet.Activate(); }
private void ConfigureExcelChartFormat(string sheetName, string seriesName, Excel._Chart oChart) { //Serie format Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1); oSeries.Name = seriesName; oSeries.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; oSeries.Smooth = true; oSeries.Border.ColorIndex = 3; oSeries.Border.Weight = 3; //Plot area format oChart.PlotArea.Interior.ColorIndex = 0; oChart.PlotArea.Border.ColorIndex = 5; //X axis format Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); xAxis.MajorGridlines.Border.ColorIndex = 5; xAxis.Border.ColorIndex = 5; xAxis.TickLabels.Font.Name = "Verdana"; xAxis.TickLabels.Font.Size = 8.0f; xAxis.TickLabels.AutoScaleFont = false; //Y axis format Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); yAxis.TickLabels.Font.Name = "Verdana"; yAxis.TickLabels.Font.Size = 8.0f; yAxis.TickLabels.AutoScaleFont = false; yAxis.Border.ColorIndex = 5; yAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone; //Legend format oChart.Legend.Font.Name = "Verdana"; oChart.Legend.Font.Size = 9.0f; oChart.Legend.Font.Bold = true; oChart.Legend.AutoScaleFont = false; oChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionTop; //Chart format oChart.HasTitle = false; oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName); }
//曲线添加 /// <summary> /// 由指定的数据列复制出一个新的数据列来,并在图表中显示 /// </summary> /// <param name="SourceSeriesIndex">用来进行复制的原始数据列对象在集合中的下标值。 /// 注意:第一条曲线的下标值为1,而不是0。</param> /// <remarks>此方法主要完成两步操作: /// 1.在Excel图表中对数据列及其对应的对象进行复制; /// 2.在字典索引dicSreies_Tag中新添加一项; /// </remarks> public virtual System.Collections.Generic.KeyValuePair <int, SeriesTag> CopySeries(int SourceSeriesIndex) { //生成新的数据列 Excel.Series newSeries = default(Excel.Series); int NewSeriesIndex = 0; newSeries = AddSeries(ref NewSeriesIndex); //设置数据列格式 Excel.Series originalSeries = F_DicSeries_Tag[SourceSeriesIndex].series; //如果此处报错,则用 //Dim xv As Object = originalSeries.XValues, 然后将xv赋值给下面的XValue newSeries.XValues = originalSeries.XValues; newSeries.Values = originalSeries.Values; newSeries.Name = originalSeries.Name; //设置数据列对应的Tag信息 this.F_DicSeries_Tag.Add(NewSeriesIndex, new SeriesTag(newSeries, this.F_RollingDate)); // return(new System.Collections.Generic.KeyValuePair <int, SeriesTag>(NewSeriesIndex, new SeriesTag(newSeries, this.F_RollingDate))); }
/// <summary> /// 构造函数 /// </summary> /// <param name="Series_Static">表示基坑区域的坑底深度的数据系列</param> /// <param name="Series_Depth">表示基坑区域的即时开挖标高的数据系列</param> /// <param name="ChosenRegion">此绘图中所包含的矩形方块与对应的数据范围</param> /// <param name="textbox">记录信息的文本框</param> /// <param name="type">此图表所属的类型,由枚举DrawingType提供</param> public ClsDrawing_ExcavationElevation(Microsoft.Office.Interop.Excel.Series Series_Static, Series Series_Depth, List <clsData_ProcessRegionData> ChosenRegion, DateSpan DateSpan, Microsoft.Office.Interop.Excel.TextFrame2 textbox, DrawingType type) { ClsDrawing_ExcavationElevation with_1 = this; with_1.F_Series_Static = Series_Static; with_1.F_Series_Depth = Series_Depth; with_1.F_textbox_Info = textbox; with_1.P_Chart = Series_Static.Parent.Parent; with_1.P_Sheet_Drawing = this.P_Chart.Parent.parent; with_1.Application = this.P_Sheet_Drawing.Application; with_1.F_Regions = ChosenRegion; with_1.DateSpan = DateSpan; with_1.P_Type = type; with_1.Application.Caption = ""; with_1.P_UniqueID = GeneralMethods.GetUniqueID(); // ------------------------------------------------------------- GlobalApplication.Application.ElevationDrawing = this; }
/// <summary> /// 添加新的数据系列对象 /// </summary> /// <param name="NewSeriesIndex">新添加的数据曲线在集合中的下标值</param> /// <returns>一条新的数据曲线</returns> /// <remarks>此函数只创建出对于新的数据曲线的对象索引,以及设置曲线的UI样式, /// 并不涉及对于数据曲线的坐标点的赋值</remarks> protected Excel.Series AddSeries(ref int NewSeriesIndex) { Excel.Series NewS = null; var seriColl = this.Chart.SeriesCollection() as Excel.SeriesCollection; if (F_CurvesCount <= F_List_HasCurve.Count - 1) //直接从已经定义好的模板中去提取 { bool hasCurve = false; for (NewSeriesIndex = cst_LboundOfSeriesInCollection; NewSeriesIndex <= System.Convert.ToInt32(F_List_HasCurve.Count + cst_LboundOfSeriesInCollection) - 1; NewSeriesIndex++) { hasCurve = F_List_HasCurve[NewSeriesIndex - cst_LboundOfSeriesInCollection]; if (!hasCurve) { NewS = seriColl.Item(NewSeriesIndex); F_List_HasCurve[NewSeriesIndex - cst_LboundOfSeriesInCollection] = true; break; } } } else //如果图表中的曲线数据已经大于当前数据系列集合中的series数量,那么就要新建一个数据系列,并设置其格式 { NewS = seriColl.NewSeries(); NewSeriesIndex = System.Convert.ToInt32(F_List_HasCurve.Count + cst_LboundOfSeriesInCollection); F_List_HasCurve.Add(true); //'设置新数据系列的UI格式 //With NewS //End With } // ----------------------------------- 对图例的显示进行操作 LegendRefresh(F_List_HasCurve); // F_CurvesCount++; return(NewS); }
/// <summary> /// Рисует график public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/) { ObjWorkBooks = ObjWorkExcel.Workbooks; ObjSheets = ObjWorkBook.Worksheets; Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250); Excel.Chart chart = chartsobjrct.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing); Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing); newTrendline.Select(); chart.Legend.Delete(); series.XValues = ObjWorkSheet.get_Range("E7", "E26"); series.Values = ObjWorkSheet.get_Range("D7", "D26"); chart.ChartType = Excel.XlChartType.xlXYScatter; }
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height) { // Cria um Chart(Gráfico) Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing); Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height); Ex.Chart cp = cbc.Chart; Ex.Range valores = oSheet.get_Range(c1, c2); // Seta o título do gráfico cp.HasTitle = true; cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI"); cp.ChartTitle.Font.Name = "Arial"; // Seta os nomes das colunas Ex.SeriesCollection seriesCollection = cp.SeriesCollection(); Ex.Series series = seriesCollection.NewSeries(); series.Values = valores; series.XValues = oSheet.get_Range("B1", "J1"); // Oculta a legenda da serie cp.Legend.Clear(); }
public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table) { Configuration_class configuration_Class = new Configuration_class(); configuration_Class.Document_Configuration_Get(); switch (name != "" || name != null) { case true: switch (format) { case Document_Format.Word: word.Application application = new word.Application(); word.Document document = application.Documents.Add(Visible: true); try { word.Range range = document.Range(0, 0); document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_class.doc_Left_Merge); document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_class.doc_Right_Merge); document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_class.doc_Top_Merge); document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints((float)Configuration_class.doc_Bottom_Merge); range.Text = Configuration_class.Organiztion_Name; range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; range.ParagraphFormat.SpaceAfter = 1; range.ParagraphFormat.SpaceBefore = 1; range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle; range.Font.Name = "Times New Roman"; range.Font.Size = 12; document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph Document_Name = document.Paragraphs.Add(); Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; Document_Name.Range.Font.Name = "Times New Roman"; Document_Name.Range.Font.Size = 16; switch (type) { case Document_Type.Report: Document_Name.Range.Text = "ОТЧЁТ"; break; case Document_Type Statistic: Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ"; break; } document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph statparg = document.Paragraphs.Add(); word.Table stat_table = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count); stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle; stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle; stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter; stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; stat_table.Range.Font.Size = 12; stat_table.Range.Font.Name = "Times New Roman"; for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 1; col <= table.Columns.Count; col++) { stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString(); } } document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph Footparg = document.Paragraphs.Add(); Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy")); } catch { } finally { switch (format) { case Document_Format.Word: document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument); break; case Document_Format.PDF: document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF); break; } document.Close(); application.Quit(); } break; case Document_Format.Excel: excel.Application application_ex = new excel.Application(); excel.Workbook workbook = application_ex.Workbooks.Add(); excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet(); try { switch (type) { case Document_Type.Report: worksheet.Name = "Отчет"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]]; border.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); break; case Document_Type.Statistic: worksheet.Name = "Статистический отчет"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]]; border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing); excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250); excel.Chart chart = chartObject.Chart; excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing); excel.Series series = seriesCollection.NewSeries(); chart.ChartType = excel.XlChartType.xl3DColumn; series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1); series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1); break; } } catch { } finally { workbook.SaveAs(name, application_ex.DefaultSaveFormat); workbook.Close(); application_ex.Quit(); } break; } break; case false: System.Windows.Forms.MessageBox.Show("Введите название документа"); break; } }
private void GenerateReportTopGenres(Dictionary <string, double> dictionary) { excel.Application excelApp = new excel.Application(); excel.Workbook workbook; excel.Worksheet workSheet; workbook = excelApp.Workbooks.Add(); //workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1); workSheet = workbook.ActiveSheet; int i = 1; foreach (var word in dictionary.OrderByDescending(q => q.Value)) { workSheet.Cells[1, i] = word.Key; workSheet.Cells[2, i] = word.Value; i++; } excel.Range Erange = workSheet.Range["B3"]; Erange.Formula = "=SUM(A2:J2)"; Erange.FormulaHidden = false; excel.Borders border = Erange.Borders; border.LineStyle = excel.XlLineStyle.xlContinuous; excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects(); excel.ChartObject chOb = chObs.Add(5, 50, 300, 300); excel.Chart xlchart = chOb.Chart; excel.Range Erange2 = workSheet.Range["A1:J1"]; excel.Range Erange3 = workSheet.Range["A3:J1"]; xlchart.ChartType = excel.XlChartType.xlColumnClustered; excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing); excel.Series series = seriesCollection.NewSeries(); //series.XValues = workSheet.Range["A1:J1"]; string[] matrix = new string[10]; for (int j = 0; j < 10; j++) { matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value; } //series.XValues = workSheet.Range[workSheet.Cells[1, 3]]; series.XValues = matrix; series.Values = workSheet.get_Range("A2", "J2"); xlchart.HasTitle = true; xlchart.ChartTitle.Text = "Жанры и их рейтинги"; xlchart.HasLegend = true; series.Name = "Жанры"; excelApp.Visible = true; excelApp.UserControl = true; string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx"; workbook.SaveAs(outputPath); object misValue = System.Reflection.Missing.Value; xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue); string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc"; WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path); wordReport.GenerateReportTopGenres(dictionary); excelApp.Quit(); }
public ColumnFrequency() { InitializeComponent(); //hScrollBar_Parameter1.Value = 2; //Graphics Graphic = new Graphics(); Bandwidth = double.Parse(textBox_Bandwidth.Text); //if (Form_Parater.flag == 0) return; //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 0]) > Max_Value) { Max_Value = double.Parse(str[i, 0]); } if (double.Parse(str[i, 0]) < Min_Value) { Min_Value = double.Parse(str[i, 0]); } } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Value2 = "";; string ChartOrder = "FrequencyColumn" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnClustered; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); Sseries.Format.Fill.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 88, 180, 13).ToArgb(); Sseries.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; chart.HasLegend = false; chart.HasTitle = false; worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Bandwidth = double.Parse(textBox_Bandwidth.Text);; if (Bandwidth == 0) { return; } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Delete();; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); chart.Refresh(); worksheet.Activate(); }
// データテーブルをエクセルへエクスポート(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; }
// データテーブルをエクセルへエクスポート(箱ヒゲ図付き) 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; }