public void SetChart(string start, string end, XlChartType type)
{
Range chartRange;
ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(500, 80, 350, 350);
Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(start, end);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
internal static void AddChart(
Excel.Worksheet pivotWorkSheet,
string myTitle,
Excel.Range pivotData,
XlChartType type)
{
var chartObjects = (Excel.ChartObjects)pivotWorkSheet.ChartObjects();
var pivotChart = chartObjects.Add(Left: 60, Top: 250, Width: 325, Height: 275);
pivotChart.Chart.ChartWizard(Source: pivotData,
Gallery: type,
Title: myTitle,
HasLegend: true,
CategoryLabels: 6,
SeriesLabels: 0);
pivotChart.Chart.Location(Where: Excel.XlChartLocation.xlLocationAsNewSheet, Name: Type.Missing);
}
public void DrawStat(int dataBRow, int dataBCol, int dataERow, int dataECol, string workSheetName, string title, string xName, string yName, masExcelDrawStatType charType, bool displayDataLabel, byte baseCol, bool hasLegend)
{
Excel.Worksheet xlSheet = null;
foreach (Worksheet sheet in workbook.Worksheets)
{
if (sheet.Name == workSheetName)
{
xlSheet = sheet;
break;
}
}
if (xlSheet == null)
{
xlSheet = AddNewSheet(workSheetName);
}
if (xlSheet == null)
{
//MsgBox.ShowInfo("Excel添加新工作薄失败!");
return;
}
Excel.ChartObjects chartObjects = (Excel.ChartObjects)xlSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjects.Add(5, 300 * _count + 20, (dataECol - dataBCol) * GetWidth() * (dataERow - dataBRow) + 200, 250);
Excel.Chart xlChart = chartObj.Chart;
XlChartType xlCharType = GetXlCharTypeFromDrawStatType(charType);
Range range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol] as Excel.Range);
xlChart.ChartWizard(range, xlCharType, Type.Missing, baseCol == 1 ? Excel.XlRowCol.xlColumns : Excel.XlRowCol.xlRows,
1, 1, hasLegend, title, xName, yName, "");
//xlChart.SetSourceData(range, Excel.XlRowCol.xlColumns);
//xlChart.ChartTitle.Text = title;
//xlChart.ChartTitle.Caption
//xlChart.HasLegend = true;
if (displayDataLabel)
{
for (int i = 0; i < dataERow - dataBRow; i++)
{
((Excel.Series)xlChart.SeriesCollection(i + 1)).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue,
missing, missing, missing);
}
}
_count++;
}
/// <summary>
/// Add a chart to the table
/// </summary>
/// <param name="ws">Current worksheet</param>
/// <param name="scores">List of calculated scores</param>
/// <param name="chartType">Type of chart</param>
static void addChart(Worksheet ws, List <int> scores, XlChartType chartType)
{
var charts = ws.ChartObjects() as ChartObjects;
var chartObject = charts.Add(0, 50, 800, 300) as ChartObject;
var chart = chartObject.Chart;
var range = ws.Range[ws.Cells[1, 1], ws.Cells[3, scores.Count]];
// Set up the chart:
chart.SetSourceData(range);
chart.ChartType = chartType;
chart.HasLegend = false;
chart.HasTitle = true;
chart.ChartTitle.Text = "All Scores";
// Define the content of the x axis:
Range xValRange = ws.Range[ws.Cells[1, 1], ws.Cells[2, scores.Count]];
Axis xAxis = (Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.CategoryNames = xValRange;
}
private string CreateChart(XlChartType chartType, System.Data.DataTable dataTable, string chartName, string outputDirectory, Worksheet xlSheet, Worksheet xlChartSheet)
{
string chartFilePath = string.Empty;
int rowIndex = dataTable.Rows.Count + 1;
int columnIndex = dataTable.Columns.Count;
ChartObjects xlCharts = (ChartObjects)xlChartSheet.ChartObjects(missing);
ChartObject chartObj = xlCharts.Add(0, 0, 348, 268);
Chart chart = chartObj.Chart;
Range chartRange = xlSheet.Range[xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, columnIndex]];
chart.SetSourceData(chartRange, missing);
chart.ChartType = chartType;
chart.ChartStyle = 261;
chart.HasTitle = true;
chart.ChartTitle.Text = chartName;
//chart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowPercent, XlDataLabelsType.xlDataLabelsShowNone, true, false, false, true, false, true);
//chart.HasLegend = false;
string chartDirectoryName = outputDirectory + @"\chart";
string chartFileName = "DataChart" + chartType.ToString() + ".Jpeg";
if (!Directory.Exists(chartDirectoryName))
{
Directory.CreateDirectory(chartDirectoryName);
}
chartFilePath = chartDirectoryName + @"\" + chartFileName;
Console.WriteLine(chartFilePath);
chart.Export(chartFilePath, "JPEG", false);
//chart.Delete();
//chart = null;
return(chartFilePath);
}
private static void AddChart2(_Worksheet pivotWorkSheet, string myTitle, Range pivotData, XlChartType type)
{
var chartObjects = (ChartObjects)pivotWorkSheet.ChartObjects();
var pivotChart = chartObjects.Add(Left: 60, Top: 250, Width: 325, Height: 275);
pivotChart.Chart.SetSourceData(pivotData);
pivotChart.Chart.ChartType = type;
if (myTitle.Equals(string.Empty) == false)
{
pivotChart.Name = myTitle;
}
pivotChart.Chart.Location(XlChartLocation.xlLocationAsNewSheet);
}
/// <summary>
/// add a chart with dimensions and referencing range
/// </summary>
/// <returns>void</returns>
public void AddChart(String chartSheetName,
String title,
System.Object valueRange,
XlChartType typeOfChart)
{
System.Object charts;
System.Object chartObj;
System.Object chart;
System.Object chartTitle;
System.Object characters;
System.Object seriesCollection;
System.Object series;
System.Int32 x1;
System.Int32 y1;
System.Int32 x2;
System.Int32 y2;
x1 = 150;
y1 = 20;
x2 = 500;
y2 = 300;
object[] parameters = new object[4];
parameters[0] = (Object)(x1);
parameters[1] = (Object)(y1);
parameters[2] = (Object)(x2);
parameters[3] = (Object)(y2);
// office.ChartObjects charts = (office.ChartObjects)ws.ChartObjects(missing);
// office.ChartObjectchartObj = charts.Add(150, 20, 500, 300);
charts = MSExcelType.InvokeMember("ChartObjects", BindingFlags.GetProperty, null, sheet, null);
chartObj = MSExcelType.InvokeMember("Add", BindingFlags.InvokeMethod, null, charts, parameters);
chart = MSExcelType.InvokeMember("Chart", BindingFlags.GetProperty, null, chartObj, null);
// chartObj.Chart.ChartType = office.XlChartType.xlLine;
parameters = new object[1];
parameters[0] = (Object)(typeOfChart);
MSExcelType.InvokeMember("ChartType", BindingFlags.SetProperty, null, chart, parameters);
// chartObj.Chart.SetSourceData(chartRange, missing);
parameters = new object[2];
parameters[0] = valueRange;
parameters[1] = Missing.Value;
MSExcelType.InvokeMember("SetSourceData", BindingFlags.InvokeMethod, null, chart, parameters);
seriesCollection = MSExcelType.InvokeMember("SeriesCollection", BindingFlags.GetProperty, null, chart, null);
parameters = new object[1];
parameters[0] = (Object)(1);
series = MSExcelType.InvokeMember("Item", BindingFlags.GetProperty, null, seriesCollection, parameters);
/*
* // for Office 2003:
* setLength(parameters, 10);
* parameters[0] := Missing.Value; // System.Object(XlDataLabelsType.xlDataLabelsShowLabelAndPercent ); // Type, XlDataLabelsType
* parameters[1] := System.Object(false); // Legend key
* parameters[2] := System.Object(false); // AutoText
* parameters[3] := System.Object(true); // has Leader Lines
* parameters[4] := System.Object(false); // show series name
* parameters[5] := System.Object(withCategoryName); // show category name
* parameters[6] := System.Object(withValue); // show value
* parameters[7] := System.Object(withPercentage); // show percentage
* parameters[8] := System.Object(false); // show bubble size
* parameters[9] := Missing.Value; // separator
*/
// for Office 2000:
parameters = new object[1];
// Type, XlDataLabelsType
parameters[0] = (Object)XlDataLabelsType.xlDataLabelsShowLabelAndPercent;
MSExcelType.InvokeMember("ApplyDataLabels", BindingFlags.InvokeMethod, null, series, parameters);
/* ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C1"
* seriesCollection := MSExcelType.InvokeMember(
* 'SeriesCollection', BindingFlags.GetProperty, nil, chart, nil);
* SetLength(parameters, 1);
* parameters[0] := System.Object(1);
* series := chart.GetType().InvokeMember(
* 'Item', BindingFlags.InvokeMethod, nil, seriesCollection, parameters);
*
* SetLength(parameters, 1);
* parameters[0] := '=Sheet1!A1A1';
* series.GetType().InvokeMember(
* 'Name', BindingFlags.SetProperty, nil, series, parameters);
*/
// chartObj.Chart.HasLegend := true;
parameters = new object[1];
parameters[0] = (Object)(true);
chart.GetType().InvokeMember("HasLegend", BindingFlags.SetProperty, null, chart, parameters);
// chartObj.Chart.HasTitle := true;
parameters = new object[1];
parameters[0] = (Object)(true);
chart.GetType().InvokeMember("HasTitle", BindingFlags.SetProperty, null, chart, parameters);
// chartObj.Chart.ChartTitle.Characters.Text := title;
chartTitle = chart.GetType().InvokeMember("ChartTitle", BindingFlags.GetProperty, null, chart, null);
parameters = new object[2];
parameters[0] = Missing.Value;
parameters[1] = Missing.Value;
characters = chart.GetType().InvokeMember("Characters", BindingFlags.GetProperty, null, chartTitle, parameters);
parameters = new object[1];
parameters[0] = title;
characters.GetType().InvokeMember("Text", BindingFlags.SetProperty, null, characters, parameters);
// move into its own sheet, must be called last (!!!)
// chart.Location((Excel.XlChartLocation)xlLocationAsNewSheet, "test");
parameters = new object[2];
chartSheetName = FixSheetName(chartSheetName);
parameters[0] = (Object)XlChartLocation.xlLocationAsNewSheet;
parameters[1] = (String)(chartSheetName);
MSExcelType.InvokeMember("Location", BindingFlags.InvokeMethod, null, chart, parameters);
}
public PPT.Chart CreateChart(XlChartType chartType, PPT.Slide slide, string[] xAxisPoints, List<ChartSeries> datasets)
{
var chart = slide.Shapes.AddChart(chartType, 10f, 10f, 900f, 400f).Chart;
var workbook = (EXCEL.Workbook)chart.ChartData.Workbook;
workbook.Windows.Application.Visible = false;
var dataSheet = (EXCEL.Worksheet)workbook.Worksheets[1];
dataSheet.Cells.ClearContents();
dataSheet.Cells.Clear();
// dataSheet.Calculate();
var sc = (PPT.SeriesCollection)chart.SeriesCollection();
do
{
var seriesToDelete = sc.Item(1);
seriesToDelete.Delete();
chart.Refresh();
}
while (sc.Count != 0);
//Build out the X-Axis Data Categories
for (var i = 1; i != (xAxisPoints.Count() + 1); i++)
{
dataSheet.Cells.Range["A" + i].Value2 = xAxisPoints[(i - 1)];
chart.Refresh();
}
var intLetter = 1;
var cellNumber = 1;
for (var j = 0; j < datasets.Count; j++)
{
var letter = IntToLetters((intLetter + 1));
// each one of these is a dataset.
foreach (var value in datasets[j].seriesData)
{
var cellPosition = letter + cellNumber.ToString();
dataSheet.Cells.Range[cellPosition].Value2 = value;
cellNumber++;
chart.Refresh();
}
// we have populate the sheet with new values, now we need to create a series for it!
var columnsRange = dataSheet.UsedRange.Columns;
var rowsRange = dataSheet.UsedRange.Rows;
var columnCount = columnsRange.Columns.Count;
var rowCount = rowsRange.Rows.Count;
var lastColumnLetter = IntToLetters(columnCount);
var newSeries = sc.NewSeries();
newSeries.Name = datasets[j].name;
newSeries.XValues = "'Sheet1'!$A$1:$A$" + rowCount;
newSeries.Values = "'Sheet1'!$" + lastColumnLetter + "$1:$" + lastColumnLetter + "$" + rowCount;
newSeries.ChartType = datasets[j].seriesType;
intLetter++;
cellNumber = 1;
chart.Refresh();
}
chart.HasTitle = false;
chart.HasLegend = false;
chart.Refresh();
return chart;
}
public void DrawStat(int dataBRow, int dataBCol, int dataERow, int dataECol, string workSheetName, string title, string xName, string yName, masExcelDrawStatType charType, bool displayDataLabel, bool isUnion, int unionBRow, int unionBCol, byte baseCol)
{
bool addNewSheet = false;
Excel.Worksheet xlSheet = null;
foreach (Worksheet sheet in workbook.Worksheets)
{
if (sheet.Name == workSheetName)
{
xlSheet = sheet;
break;
}
}
if (xlSheet == null)
{
xlSheet = AddNewSheet(workSheetName);
addNewSheet = true;
}
if (xlSheet == null)
{
MsgBox.ShowInfo("Excel添加新工作薄失败!");
return;
}
Range range = null;
if (baseCol != 1)
{
range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol] as Excel.Range);
}
else
{
range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol + 1] as Excel.Range);
}
int rangCount = range.Rows.Count;
if (isUnion)
{
if (baseCol != 1)
{
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, dataECol] as Excel.Range, worksheet.Cells[unionBRow, unionBCol] as Excel.Range), range);
}
else
{
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, dataECol] as Excel.Range, worksheet.Cells[unionBRow, dataBCol + 1] as Excel.Range), range);
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, unionBCol] as Excel.Range, worksheet.Cells[unionBRow, unionBCol] as Excel.Range), range);
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[dataERow, unionBCol] as Excel.Range, worksheet.Cells[dataBRow, unionBCol] as Excel.Range), range);
}
rangCount += 1;
}
Excel.ChartObjects chartObjects = (Excel.ChartObjects)xlSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjects.Add(5, 300 * _count + 20, (dataECol - dataBCol) * GetWidth() * (rangCount - 1) + 200, 250);
Excel.Chart xlChart = chartObj.Chart;
XlChartType xlCharType = GetXlCharTypeFromDrawStatType(charType);
xlChart.ChartWizard(range, xlCharType, Type.Missing, baseCol == 1 ? Excel.XlRowCol.xlColumns : Excel.XlRowCol.xlRows,
1, 1, true, title, xName, yName, "");
if (displayDataLabel)
{
for (int i = 0; i < rangCount - 1; i++)
{
((Excel.Series)xlChart.SeriesCollection(i + 1)).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue,
missing, missing, missing);
}
}
if (!addNewSheet)
{
_count++;
}
}
private static void MakeExcel(
string pivotStyle,
string tableStyle,
XlChartType charType,
DataTable dataSource,
Excel.Workbook eWorkbook,
int counter,
string sheetTitle,
string pivotTableName,
List <string> pivotRowFields,
List <string> pivotValueFields,
List <string> pivotColumnFields,
List <string> pivotReportFields,
List <string> sliderFields)
{
var startRow = 1;
var endIndex = dataSource.Rows.Count + startRow;
var sheets = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets;
var sheet = (Excel.Worksheet)sheets.Add();
sheet.Name = sheetTitle + (++counter);
for (var i = 1; i < dataSource.Columns.Count + 1; i++)
{
sheet.Cells[startRow, i] = dataSource.Columns[i - 1].ColumnName;
if (dataSource.Columns[i - 1].DataType == Type.GetType("System.DateTime"))
{
sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
Cthread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
}
else if (dataSource.Columns[i - 1].DataType == Type.GetType("System.Decimal"))
{
sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
$"#{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator}##0{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator}00";
}
}
try
{
sheet.Range[sheet.Cells[startRow + 1, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Value2 =
General.Convert(dataSource);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
General.FormatAsTable(sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]],
"SourceData" + counter, tableStyle, false);
//eApplication.Visible = true;
//sheet.Activate();
//sheet.Application.ActiveWindow.SplitRow = 1;
//sheet.Application.ActiveWindow.SplitColumn = 1;
//sheet.Application.ActiveWindow.FreezePanes = true;
//// Now apply autofilter
//var firstRow = (Excel.Range)sheet.Rows[1];
//firstRow.AutoFilter(1,
// Type.Missing,
// Excel.XlAutoFilterOperator.xlAnd,
// Type.Missing,
// true);
sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Columns.AutoFit();
//sheet.Columns.AutoFit();
var pivotData = sheet.Range["A1",
General.GetColumnName(dataSource.Columns.Count) + (dataSource.Rows.Count + 1)];
var pivotWorkSheet = General.AddPivot(
pivotStyle,
pivotData,
dataSource,
counter,
pivotTableName,
pivotRowFields,
pivotColumnFields,
pivotValueFields,
pivotReportFields);
General.AddChart(pivotWorkSheet, sheetTitle, pivotData, charType);
General.AddSlicers(eWorkbook, (Excel.PivotTable)pivotWorkSheet.PivotTables(pivotTableName),
pivotWorkSheet, sliderFields);
pivotWorkSheet.Select();
//Excel.Application oApp = Globals.ThisAddIn.Application;
//oApp.Visible = true;
//oApp.ScreenUpdating = true;
//oApp.UserControl = true;
//oApp.Interactive = true;
}
public void ApplyCustomType(XlChartType chartType)
{
object[] paramArray = new object[1];
paramArray[0] = chartType;
InstanceType.InvokeMember("ApplyCustomType", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
}
/// <summary>
/// 指定图表的类型，并创建图表
/// </summary>
/// <typeparam name="ChartType">图表的类型</typeparam>
/// <param name="Type">COM组件所能识别的图表类型</param>
/// <returns></returns>
private IExcelObj <ChartType> CreateChart <ChartType>(XlChartType Type)
where ChartType : IOfficeChart
=> (IExcelObj <ChartType>)PackShapes.AddChart2(XlChartType : Type).ToChart(Sheet);
/// <summary>
/// 设置Chart类型
/// </summary>
/// <param name="chart">Chart对象</param>
/// <param name="chartType">Chart类型，如柱形图等</param>
public void SetChartStyle(Chart chart, XlChartType chartType)
{
chart.ChartType = chartType;
}
protected void DrawChart(XlChartType ChartType)
{
// Variable declaration
object misValue = System.Reflection.Missing.Value;
ExcelObj.Range chartRange = null;
try
{
this.Validate();
// split the cell range for the work sheet range
string cell1 = strCellRange.Split(':')[0];
string cell2 = strCellRange.Split(':')[1];
base.InitWorkSheet();
if (base.SheetIndex > 0 && base.xlWorkSheet != null)
{
ExcelObj.ChartObjects xlCharts = (ExcelObj.ChartObjects)base.xlWorkSheet.ChartObjects(Type.Missing);
try
{
// Define the cell range to the work sheet
chartRange = xlWorkSheet.get_Range(cell1, cell2);
}
catch (Exception)
{
base.ClearObject();
throw new Exception("Invalid Range");
}
// fix chart position by left,top,width,height
ExcelObj.ChartObject chartObj = (ExcelObj.ChartObject)xlCharts.Add(dblChartLeft, dblChartTop, dblChartWidth, dblChartHeight);
// Define char object
ExcelObj._Chart chart = chartObj.Chart;
// assign the datasource for the chart
chart.SetSourceData(chartRange, misValue);
// set the chart type.
chart.ChartType = ChartType;
// Ledgend show / hide
chart.HasLegend = this.Options.ShowLedgend;
// assign the chart title based on empty
if (!string.IsNullOrEmpty(strChartTitle))
{
chart.HasTitle = true;
chart.ChartTitle.Text = strChartTitle;
}
else
{
chart.HasTitle = false;
}
XlDataLabelsType XlDataLabelsType = (XlDataLabelsType)this.Options.DataLabelsType;
XlDataLabelsType XlLedgendKey = (XlDataLabelsType)this.Options.LedgendKey;
// assign the data lable properties
chart.ApplyDataLabels(XlDataLabelsType, XlLedgendKey, this.Options.AutoText, this.Options.HasLeaderLines,
this.Options.ShowSeriesName, this.Options.ShowCategoryName, this.Options.ShowValue,
this.Options.ShowPercentage, this.Options.ShowBubbleSize, this.Options.Separator);
if (this.strImagecopy != null && !string.IsNullOrEmpty(this.strImagecopy))
{
string strExten = new FileInfo(this.strImagecopy).Extension;
strExten = strExten.Replace(".", "");
chart.Export(this.strImagecopy, strExten.ToUpper(), misValue);
}
base.SaveWorkBook(true);
}
}
catch (ValidationException Vex)
{
base.ClearObject();
throw Vex;
}
catch (Exception ex)
{
base.ClearObject();
throw new Exception("DrawChart : " + ex.Message);
}
}
public PPT.Chart CreateChart(XlChartType chartType, PPT.Slide slide, string[] xAxisPoints, List <ChartSeries> datasets)
{
var chart = slide.Shapes.AddChart(chartType, 10f, 10f, 900f, 400f).Chart;
var workbook = (EXCEL.Workbook)chart.ChartData.Workbook;
workbook.Windows.Application.Visible = false;
var dataSheet = (EXCEL.Worksheet)workbook.Worksheets[1];
dataSheet.Cells.ClearContents();
dataSheet.Cells.Clear();
// dataSheet.Calculate();
var sc = (PPT.SeriesCollection)chart.SeriesCollection();
do
{
var seriesToDelete = sc.Item(1);
seriesToDelete.Delete();
chart.Refresh();
}while (sc.Count != 0);
//Build out the X-Axis Data Categories
for (var i = 1; i != (xAxisPoints.Count() + 1); i++)
{
dataSheet.Cells.Range["A" + i].Value2 = xAxisPoints[(i - 1)];
chart.Refresh();
}
var intLetter = 1;
var cellNumber = 1;
for (var j = 0; j < datasets.Count; j++)
{
var letter = IntToLetters((intLetter + 1));
// each one of these is a dataset.
foreach (var value in datasets[j].seriesData)
{
var cellPosition = letter + cellNumber.ToString();
dataSheet.Cells.Range[cellPosition].Value2 = value;
cellNumber++;
chart.Refresh();
}
// we have populate the sheet with new values, now we need to create a series for it!
var columnsRange = dataSheet.UsedRange.Columns;
var rowsRange = dataSheet.UsedRange.Rows;
var columnCount = columnsRange.Columns.Count;
var rowCount = rowsRange.Rows.Count;
var lastColumnLetter = IntToLetters(columnCount);
var newSeries = sc.NewSeries();
newSeries.Name = datasets[j].name;
newSeries.XValues = "'Sheet1'!$A$1:$A$" + rowCount;
newSeries.Values = "'Sheet1'!$" + lastColumnLetter + "$1:$" + lastColumnLetter + "$" + rowCount;
newSeries.ChartType = datasets[j].seriesType;
intLetter++;
cellNumber = 1;
chart.Refresh();
}
chart.HasTitle = false;
chart.HasLegend = false;
chart.Refresh();
return(chart);
}
/// <summary>
/// 设置指定系列的图标类型
/// </summary>
/// <param name="chart"></param>
/// <param name="seriesIndex"></param>
/// <param name="chartType"></param>
public void SetChartSeriesCollectionChartType(Chart chart, int seriesIndex, XlChartType chartType)
{
Excel.Series se = (Excel.Series)chart.SeriesCollection(seriesIndex);
se.ChartType = chartType;
}
/// <summary>新建图表工作表，并返回 Chart 对象。
/// </summary>
/// <param name="Before">指定工作表的对象，新建的工作表将置于此工作表之前。</param>
/// <param name="After">指定工作表的对象，新建的工作表将置于此工作表之后。</param>
/// <param name="Count">要添加的工作表数。默认值为 1。</param>
/// <param name="Count">一个 XlChartType 常量，它代表要添加的图表类型。</param>
public dynamic Add(Worksheet Before = null, Worksheet After = null, int? Count = null, XlChartType? Type = null)
{
_objaParameters = new object[4] {
Before == null ? System.Type.Missing : Before._objWorksheet,
After == null ? System.Type.Missing : After._objWorksheet,
Count == null ? System.Type.Missing : Count,
Type == null ? System.Type.Missing : Type
};
return _objCharts.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, _objCharts, _objaParameters);
}
/// <summary>修改给定图表的属性。可使用本方法快速设置图表的格式，而不必逐个设置所有属性。本方法是非交互式的，并且仅更改指定的属性。
/// </summary>
/// <param name="Source">包含新图表源数据的区域。如果省略本参数，Microsoft Excel 将编辑活动图表工作表或活动工作表上处于选定状态的图表。</param>
/// <param name="Gallery">用于指定图表类型的 XlChartType 的常量之一。</param>
/// <param name="Format">内置自动套用格式的选项编号。可为从 1 到 10 的数字，其取值取决于库的类型。如果省略此参数，Microsoft Excel 将根据库的类型和数据源选择默认值。</param>
/// <param name="PlotBy">指定每个系列的数据是来自行还是来自列。可以是以下 XlRowCol 常量之一：xlRows 或 xlColumns。</param>
/// <param name="CategoryLabels">指定包含分类标签的源范围内的行数或列数的整数。合法值为从 0（零）至小于相应分类或系列的最大个数间的某一数字。</param>
/// <param name="SeriesLabels">指定包含系列标志的源范围内的行数或列数的整数。合法值为从 0（零）至小于相应分类或系列的最大个数间的某一数字。</param>
/// <param name="HasLegend">若要包括图例，则为 True。</param>
/// <param name="Title">图表标题文字。</param>
/// <param name="CategoryTitle">分类轴标题文字。</param>
/// <param name="ValueTitle">数值轴标题文字。</param>
/// <param name="ExtraTitle">三维图表的系列轴标题，或二维图表的次数值轴标题。</param>
public void ChartWizard(object Source = null, XlChartType? Gallery = null, int? Format = null, XlRowCol? PlotBy = null, int? CategoryLabels = null, int? SeriesLabels = null, bool? HasLegend = null, string Title = null, string CategoryTitle = null, string ValueTitle = null, string ExtraTitle = null)
{
_objaParameters = new object[11] {
Source == null ? System.Type.Missing : Source,
Gallery == null ? System.Type.Missing : Gallery,
Format == null ? System.Type.Missing : Format,
PlotBy == null ? System.Type.Missing : PlotBy,
CategoryLabels == null ? System.Type.Missing : CategoryLabels,
SeriesLabels == null ? System.Type.Missing : SeriesLabels,
HasLegend == null ? System.Type.Missing : HasLegend,
Title == null ? System.Type.Missing : Title,
CategoryTitle == null ? System.Type.Missing : CategoryTitle,
ValueTitle == null ? System.Type.Missing : ValueTitle,
ExtraTitle == null ? System.Type.Missing : ExtraTitle
};
_objChart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, _objChart, _objaParameters);
}
/// <summary>
/// </summary>
public void ApplyCustomType(XlChartType ChartType, object TypeName = null)
{
_objaParameters = new object[2] {
ChartType,
TypeName == null ? System.Type.Missing : TypeName
};
_objChart.GetType().InvokeMember("ApplyCustomType", BindingFlags.InvokeMethod, null, _objChart, _objaParameters);
}
/// <summary>在活动工作表上的指定位置创建图表。
/// 说明：
/// 如果省略图表类型，则使用应用程序的默认图表类型。如果省略位置，则使用应用程序的默认大小。
/// </summary>
/// <param name="Type">图表类型。</param>
/// <param name="Left">从对象左边界至 A 列左边界（在工作表上）或图表区左边界（在图表上）的距离，以磅为单位。 </param>
/// <param name="Top">从图形区域中最上端的图形的顶端到工作表顶端的距离，以磅为单位。</param>
/// <param name="Width">对象的宽度，以磅为单位。 </param>
/// <param name="Height">对象的高度，以磅为单位。 </param>
/// <returns></returns>
public Shape AddChart(XlChartType? Type = null, float? Left = null, float? Top = null, float? Width = null, float? Height = null)
{
_objaParameters = new object[5] {
Type == null ? System.Type.Missing : Type,
Left == null ? System.Type.Missing : Left,
Top == null ? System.Type.Missing : Top,
Width == null ? System.Type.Missing : Width,
Height == null ? System.Type.Missing : Height
};
object objShape = _objShapes.GetType().InvokeMember("AddChart", BindingFlags.InvokeMethod, null, _objShapes, _objaParameters);
return new Shape(objShape);
}
//插入图表操作
//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);
}
}
private void SetChart(string formatStr, string Name, object value, Bookmark tempBookmark)
{
try
{
if (value != null)
{
XlChartType xlChartType = XlChartType.xlColumnClustered;
if (formatStr != null)
{
if (!(formatStr == "CHARTLINE"))
{
if (formatStr == "CHARTPIE")
{
xlChartType = XlChartType.xl3DPieExploded;
}
}
else
{
xlChartType = XlChartType.xlLine;
}
}
System.Data.DataTable dataTable = value as System.Data.DataTable;
object obj = "MSGraph.Chart.8";
Microsoft.Office.Interop.Word.Range range = tempBookmark.Range;
InlineShape inlineShape = range.InlineShapes.AddOLEObject(ref obj, ref this.missing, ref this.missing, ref this.missing, ref this.missing, ref this.missing, ref this.missing, ref this.missing);
if (tempBookmark.Range.Cells.Count == 1)
{
inlineShape.Width = tempBookmark.Range.Cells[1].Width - 20f;
inlineShape.Height = tempBookmark.Range.Cells[1].Height;
}
object @object = inlineShape.OLEFormat.Object;
object obj2 = @object.GetType().InvokeMember("Application", System.Reflection.BindingFlags.GetProperty, null, @object, null);
object[] args = new object[]
{
4
};
@object.GetType().InvokeMember("ChartType", System.Reflection.BindingFlags.SetProperty, null, @object, args);
Microsoft.Office.Interop.Graph.Chart chart = (Microsoft.Office.Interop.Graph.Chart)inlineShape.OLEFormat.Object;
chart.ChartType = xlChartType;
chart.HasTitle = true;
chart.ChartTitle.Text = dataTable.TableName;
chart.PlotArea.Interior.Color = Color.White;
if (!string.IsNullOrEmpty(dataTable.TableName))
{
chart.ChartTitle.Font.Size = 12;
}
DataSheet dataSheet = chart.Application.DataSheet;
dataSheet.Columns.Clear();
dataSheet.Rows.Clear();
foreach (DataColumn dataColumn in dataTable.Columns)
{
dataSheet.Cells[1, dataTable.Columns.IndexOf(dataColumn) + 1] = dataColumn.ColumnName;
}
foreach (DataRow dataRow in dataTable.Rows)
{
foreach (DataColumn dataColumn in dataTable.Columns)
{
if (dataRow[dataColumn] == System.DBNull.Value)
{
dataRow[dataColumn] = "";
}
dataSheet.Cells[dataTable.Rows.IndexOf(dataRow) + 2, dataTable.Columns.IndexOf(dataColumn) + 1] = dataRow[dataColumn];
}
}
if (xlChartType != XlChartType.xl3DPieExploded)
{
chart.Legend.Position = Microsoft.Office.Interop.Graph.XlLegendPosition.xlLegendPositionTop;
for (int i = 1; i <= dataTable.Rows.Count; i++)
{
Microsoft.Office.Interop.Graph.Series series = (Microsoft.Office.Interop.Graph.Series)chart.SeriesCollection(i);
series.HasDataLabels = true;
Color chartColor = this.GetChartColor(i);
if (chartColor != Color.Empty)
{
series.Interior.Color = chartColor;
}
else
{
series.Interior.ColorIndex = 15 + i;
}
}
}
else
{
chart.HasLegend = false;
Microsoft.Office.Interop.Graph.Series series = (Microsoft.Office.Interop.Graph.Series)chart.SeriesCollection(1);
series.HasDataLabels = true;
series.HasLeaderLines = true;
}
chart.Application.Update();
obj2.GetType().InvokeMember("Update", System.Reflection.BindingFlags.InvokeMethod, null, obj2, null);
obj2.GetType().InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, obj2, null);
}
}
catch (System.Exception ex)
{
throw new System.Exception("图表[" + tempBookmark.Name + "]数据加载出错：" + ex.Message);
}
}