Excel._Chart MakeChart(Excel._Workbook oWB, Excel.Range input, Excel.XlChartType type, Excel.XlRowCol rowcol, int numXValues, int numLabels, bool bLegend) { Excel._Chart oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); oChart.ChartWizard(input, type, Missing.Value, rowcol, numXValues, numLabels, bLegend, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oChart.ChartArea.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse; return(oChart); }
/// <summary> /// 插入图表 /// </summary> /// <param name="chartType"></param> /// <param name="ws"></param> /// <param name="dsRowIndex1"></param> /// <param name="dsColumnIndex1"></param> /// <param name="dsRowIndex2"></param> /// <param name="dsColumnIndex2"></param> /// <param name="chartDataType"></param> public void InsertActiveChart(Excel.XlChartType chartType, Excel.Worksheet ws, int dsRowIndex1, int dsColumnIndex1, int dsRowIndex2, int dsColumnIndex2, Excel.XlRowCol chartDataType) { Workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Range range = GetRange(ws, dsRowIndex1, dsColumnIndex1, dsRowIndex2, dsColumnIndex2); Workbook.ActiveChart.ChartType = chartType; Workbook.ActiveChart.SetSourceData(range, chartDataType); Workbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws.Name); }
/// <summary> /// 插入图表操作 /// </summary> /// <param name="chartType">图表类型</param> /// <param name="wsn">工作表名称</param> /// <param name="dataSourcesX1">数据来源开始x行</param> /// <param name="dataSourcesY1">数据来源开始y列</param> /// <param name="dataSourcesX2">数据来源结束x行</param> /// <param name="dataSourcesY2">数据来源结束y列</param> /// <param name="chartDataType">图表数据类型</param> public void InsertActiveChart(Excel.XlChartType chartType, string wsn, int dataSourcesX1, int dataSourcesY1, int dataSourcesX2, int dataSourcesY2, Excel.XlRowCol chartDataType) { //chartDataType = Excel.XlRowCol.xlColumns; _wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); { _wb.ActiveChart.ChartType = chartType; _wb.ActiveChart.SetSourceData(GetSheet(wsn).Range[GetSheet(wsn).Cells[dataSourcesX1, dataSourcesY1], GetSheet(wsn).Cells[dataSourcesX2, dataSourcesY2]], chartDataType); _wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, wsn); } }
//插入图片操作一 //后面的数字表示位置 //public void InsertPictures(string Filename, string ws) //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse,MsoTriState.msoTrue, 10, 10, 150, 150); //} //public void InsertPictures(string Filename, string ws, int Height, int Width) //插入图片操作二 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width) //插入图片操作三 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} //插入图表操作 public void InsertActiveChart(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType) { ChartDataType = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns; wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); { wb.ActiveChart.ChartType = ChartType; wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType); wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws); } }
//public void InsertPictures(string Filename, string ws, int Height, int Width) //插入图片操作二 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width) //插入图片操作三 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} public void InsertActiveChart(XlChartType chartType, string ws, int dataSourcesX1, int dataSourcesY1, int dataSourcesX2, int dataSourcesY2, XlRowCol chartDataType) //插入图表操作 { chartDataType = XlRowCol.xlColumns; Wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); { Wb.ActiveChart.ChartType = chartType; Wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[dataSourcesX1, dataSourcesY1], GetSheet(ws).Cells[dataSourcesX2, dataSourcesY2]), chartDataType); Wb.ActiveChart.Location(XlChartLocation.xlLocationAsObject, ws); } }
public void InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType) //插入图表操作 { ChartDataType = Excel.XlRowCol.xlColumns; _wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); { _wb.ActiveChart.ChartType = ChartType; _wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType); _wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws); } }
public void SetChartData(int dataCol, int legCol, int rowStart, int rowEnd, Excel.XlChartType chartType, bool showLegend) { Excel.Worksheet wss = (Excel.Worksheet)ewb.Sheets["Graph"]; Excel.ChartObjects cos = (Excel.ChartObjects)wss.ChartObjects(Type.Missing); Excel.ChartObject co = (Excel.ChartObject)cos.Item("Диаграмма 1"); Excel.Chart ch = (Excel.Chart)co.Chart; ch.HasLegend = showLegend; ch.ChartType = chartType; ch.SetSourceData(ewsh.Range[ewsh.Cells[rowStart, legCol], ewsh.Cells[rowEnd, dataCol]], Type.Missing); ReleaseObject(ch); ReleaseObject(co); ReleaseObject(cos); ReleaseObject(wss); }
private int GetChartFormat(Excel.XlChartType chartType) { switch (chartType) { case Excel.XlChartType.xl3DColumnClustered: return(1); case Excel.XlChartType.xl3DBarClustered: return(5); case Excel.XlChartType.xl3DPie: return(1); default: return(1); } }
/// <summary> /// 给工作表添加一个图表 /// </summary> /// <param name="firstRange">数据工作表</param> /// <param name="xData">x轴数据区域:格式A1:A14</param> /// <param name="ydata">y轴数据区域:格式B1:E14</param> /// <param name="chartName">图标名称</param> /// <param name="xname">y轴名称</param> /// <param name="yname">y轴名称</param> /// <param name="chartType">图表类型</param> public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName) { Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value); int cols = 0; foreach (ChartStuct chartStuct in charts) { cols += chartStuct.dataRange.Columns.Count; } Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350); if (charts.Count < 1) { return; //无数不处理 } //绘制第一维图表 Excel.Range firstRange = charts[0].dataRange; Excel.XlChartType chartType = charts[0].chartType; string yname = charts[0].yname; chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value); chartObject.Chart.ChartArea.ClearFormats(); chartObject.Chart.ChartType = chartType; chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景 //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor); //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB); chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2; //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB); //Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex); //chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19; chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景 Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); firstAxis.HasMajorGridlines = true;//不显示横向网格线 Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing); int sc = seriesCollection.Count; if (seriesCollection.Count > 0) { Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色 //seires1.Fill.BackColor.SchemeColor = 18; seires1.Fill.ForeColor.SchemeColor = 46; seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone; seires1.HasLeaderLines = false; } //绘制第二维图表 //修改第一维图表的各个序列的图例,这里有个bug,会用最后的标题覆盖前面的 // for (int i = 0; i < charts[0].seriesTitles.Length; i++) //{ //string stitle = charts[0].seriesTitles[i]; //Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1); //tmpSeires.Name = stitle; //} //绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时,抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常 for (int i = 1; i < charts.Count; i++) { ChartStuct cs = charts[i]; string stitle = cs.seriesTitles[0]; Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries(); xlSeries.Name = stitle; xlSeries.HasLeaderLines = true; xlSeries.Fill.ForeColor.SchemeColor = 46; try { xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary; } catch (Exception eee) { Console.WriteLine(eee.Message); } xlSeries.ChartType = cs.chartType; xlSeries.Values = cs.dataRange; Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary); valueAxis.HasTitle = true; valueAxis.AxisTitle.Text = cs.yname; } }
//Excel.XlChartType.xl3DColumn /// <summary> /// 给工作添加一个图标工作表 /// </summary> /// <param name="xlBook">工作簿</param> /// <param name="dataSheet">数据工作表</param> /// <param name="xData">x轴数据区域:格式A1:A14</param> /// <param name="ydata">y轴数据区域:格式B1:E14</param> /// <param name="chartName">图标名称</param> /// <param name="xname">y轴名称</param> /// <param name="yname">y轴名称</param> /// <param name="chartType">图表类型</param> public static void createSingleChart(Excel.Workbook xlBook, Excel.Worksheet dataSheet, string xData, string ydata, string chartName, string xname, string yname, Excel.XlChartType chartType) { Excel.Chart xlChart = (Excel.Chart)xlBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); Excel.Range chartRage = dataSheet.get_Range(xData, ydata); xlChart.ChartWizard(chartRage, chartType, Missing.Value, Excel.XlRowCol.xlRows, 1, 1, true, chartName, xname, yname, Missing.Value); }
/// <summary> /// 插入图表 /// </summary> /// <param name="chartType"></param> /// <param name="sheetname"></param> /// <param name="dsRowIndex1"></param> /// <param name="dsColumnIndex1"></param> /// <param name="dsRowIndex2"></param> /// <param name="dsColumnIndex2"></param> /// <param name="chartDataType"></param> public void InsertActiveChart(Excel.XlChartType chartType, string sheetname, int dsRowIndex1, int dsColumnIndex1, int dsRowIndex2, int dsColumnIndex2, Excel.XlRowCol chartDataType) { Excel.Worksheet ws = GetSheet(sheetname); InsertActiveChart(chartType, ws, dsRowIndex1, dsColumnIndex1, dsRowIndex2, dsColumnIndex2, chartDataType); }
public static void Graphics1(List <DataPoint> Values, string chartName = null, string legend = null, excel.XlChartType type = excel.XlChartType.xlColumnClustered) { if (Values.Count == 0) { return; } try { Supports.GetProfileForm().ChangeState(); word.Application app = new word.Application(); app.Visible = true; Thread.Sleep(500); var doc = app.Documents.Add(); word.InlineShape chartShape = doc.InlineShapes.AddChart2(212); chartShape.Chart.ChartTitle.Text = chartName; chartShape.Chart.ChartType = (Microsoft.Office.Core.XlChartType)type; excel.Workbook workbook = chartShape.Chart.ChartData.Workbook; excel.Worksheet sheert = workbook.Worksheets[1]; sheert.Cells.Range["A2", "E5"].Clear(); sheert.ListObjects.Item[1].Resize(sheert.Range["A1", "B" + (Values.Count + 1).ToString()]); sheert.Cells[1, 2] = legend; for (var i = 2; i < Values.Count + 2; i++) { sheert.Cells[i, 1] = DateTime.FromOADate(Values[i - 2].XValue); sheert.Cells[i, 2] = Values[i - 2].YValues[0]; } } catch (Exception e) { MessageBoxTi.Show("Documentation.Graphics " + e.Message); return; } finally { Supports.GetProfileForm().ChangeState(true); } }
public static void Graphics(List <DataPoint> Values, string chartName = null, string legend = null, excel.XlChartType type = excel.XlChartType.xlColumnClustered) { if (Values.Count == 0) { return; } try { Supports.GetProfileForm().ChangeState(); word.Application app = new word.Application(); app.Visible = true; Thread.Sleep(500); var doc = app.Documents.Add(); word.InlineShape chartShape = doc.InlineShapes.AddOLEObject((Microsoft.Office.Core.XlChartType)type); chartShape.AlternativeText = "Chart1"; word.Chart chart = chartShape.OLEFormat.Object as word.Chart; dynamic book = chart.ChartData.Workbook; //dynamic bookTable = book.Sheets[1].ListObjects("Table1"); //bookTable.DataBodyRange.ClearContents(); //Thread.Sleep(500); //excel.Chart chart = exApp.Charts[0]; //chart.SetSourceData(exApp.Cells.Range["A2", "E15"]); //exApp.Cells.Range["A2", "E5"].Clear(); //sheert.ListObjects.Item[1].Resize(sheert.Range["A1", "B" + (Values.Count + 1).ToString()]); //sheert.Cells[1, 2] = legend; for (var i = 2; i < Values.Count + 2; i++) { //exApp.Cells[i, 1] = DateTime.FromOADate(Values[i - 2].XValue); //exApp.Cells[i, 2] = Values[i - 2].YValues[0]; } } catch (Exception e) { MessageBoxTi.Show("Documentation.Graphics " + e.Message); return; } finally { Supports.GetProfileForm().ChangeState(true); } }
public ExcelChartInfo ChartType(Excel.XlChartType type) { m_ChartType = type; return(this); }