/// <summary>
/// 插入sheet级别的图表
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="range">数据所在坐标范围</param>
/// <param name="plotBy">指定绘制数据的方式，是以行为数据源（列为该数据源数据）还是以列为数据源（行为该数据源数据</param>
/// <returns>Chart对象</returns>
public Chart AddSheetLevelChart(Excel.Workbook workbook, Range range, Excel.XlRowCol plotBy = Excel.XlRowCol.xlColumns)
{
Chart chart = workbook.Charts.Add();
chart.SetSourceData(range, plotBy);
return(chart);
}
/// <summary>
/// 插入sheet子图表
/// </summary>
/// <param name="sheet">表对象</param>
/// <param name="range">数据范围对象</param>
/// <param name="plotBy">指定绘制数据的方式，是以行为数据源（列为该数据源数据）还是以列为数据源（行为该数据源数据</param>
/// <returns>Chart对象</returns>
public Chart AddShapeLevelChart(Excel.Worksheet sheet, Range range, Excel.XlRowCol plotBy = Excel.XlRowCol.xlColumns)
{
Shape shape = sheet.Shapes.AddChart();
Chart chart = shape.Chart;
chart.SetSourceData(range, plotBy);
return(chart);
}
//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);
}
}
/// <summary>
/// Creates a 2-DIMENSIONAL vertical bar chart using the specified data range.
/// </summary>
/// <param name="dataRange">The data to use in the vertical bar chart must represent the x and y axes</param>
/// <param name="startCol">The letter of the column the left side of the chart should start on.</param>
/// <param name="top">The topmost pixel of the chart</param>
/// <param name="width">The total width of the chart in pixels</param>
/// <param name="height">The total height of the chart in pixels</param>
/// <param name="rowCol">The XL row/col datatype to use?</param>
/// <param name="uniqueName">Unique chart name for this worksheet</param>
public string CreateVerticalBarChart(string dataRange, char startCol, double top, double width, double height, Microsoft.Office.Interop.Excel.XlRowCol rowCol)
{
Range vertBarChartData = ((Range)(ReportGlobals.XLMgr.GetRanges(dataRange))[0]);
// Get the active sheet
Worksheet operationalSheet = (Worksheet)(workbookIndex[ReportGlobals.defaultWorkbookName].Sheets[ReportGlobals.defaultSheetIndex]);
ChartObjects chartObjs = (ChartObjects)operationalSheet.ChartObjects(Type.Missing);
// Get the number of pixels from the left we are going to count to get to this column
int asciiColumn = Convert.ToInt32(startCol);
int asciiA = Convert.ToInt32('A');
int totalPixels = 0;
asciiColumn = 0 + asciiColumn;
for (int i = asciiA; i < asciiColumn; i++)
{
totalPixels += asciiColumn;
}
ChartObject chartObj = chartObjs.Add((double)totalPixels, top, width, height);
Chart xlChart = chartObj.Chart;
xlChart.SetSourceData(vertBarChartData, rowCol);
xlChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
return(chartObj.Name);
}
/// <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="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);
}
/// <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);
}
}
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);
}
public Chart AddShapeLevelChart(Excel.Worksheet sheet, Range range, float width, float height, Excel.XlRowCol plotBy = Excel.XlRowCol.xlColumns)
{
Shape shape = sheet.Shapes.AddChart();
shape.Width = width;
shape.Height = height;
Chart chart = shape.Chart;
chart.SetSourceData(range, plotBy);
return(chart);
}
//public void InsertPictures(string Filename, string ws, int Height, int Width)
//Insert picture 2.
//{
// 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)
//Insert picture 3.
//{
// 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(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType)
//Insert active chart.
{
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);
}
}
