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);
}
