[Excel] Export Excel Chart
{
excelBook1.Sheets.Add(Type.Missing, excelBook1.Worksheets[sheetNum], 1, System.Type.Missing); //add new sheet
Worksheet sheet = (Worksheet)excelBook1.Worksheets[sheetNum]; //get new sheet
sheet.Name = "Chart sheet"; // sheet name
object misValue = System.Reflection.Missing.Value;
ChartObjects chartObjs = (ChartObjects)sheet.ChartObjects(Type.Missing);
ChartObject chartObj = (ChartObject)chartObjs.Add(20, 160, 600, 400); //set chart size
Chart chart = chartObj.Chart;
//export data into sheet
sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[data.GetLength(0), data.GetLength(1)]).Value2 = data;
//設定第一條圖線的資料範圍
Range chartRange1 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[(data.GetLength(0) - 1), data.GetLength(1)]);
chart.SetSourceData(chartRange1, misValue);
((Series)chart.SeriesCollection(1)).AxisGroup =
XlAxisGroup.xlPrimary; //SeriesCollection = 第幾條線, xlPrimary表示左邊軸線刻度為此線圖之座標
((Series)chart.SeriesCollection(1)).ChartType = XlChartType.xlLineMarkers; //設定圖線種類
//設定圖線種類的資料點相關 Style
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).MarkerForegroundColor =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 255, 0));
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).MarkerBackgroundColor =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 0, 0));
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).MarkerSize = 6;
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).MarkerStyle = XlMarkerStyle.xlMarkerStyleTriangle;
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).Border.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 0, 0));
((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).Border.Weight = XlBorderWeight.xlMedium;
/*設定資料標籤相關資訊時, 若發生 Exception, 那可能表示此圖線不適合該資料標籤的相關資訊*/
//顯示資料標籤
((Series)chart.SeriesCollection(1)).HasDataLabels = true;
//設定資料標籤位置
DataLabel dl = (DataLabel)((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).DataLabels(0);
dl.Position = XlDataLabelPosition.xlLabelPositionInsideBase;
//設定資料標籤邊框顏色
dl.Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 255, 143, 25));
//設定資料標籤邊框粗細
dl.Border.Weight = 3; //value range = 1 - 4
//設定資料標籤背景顏色
dl.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 255, 143, 25));
//設定單一資料標籤相關資訊
Microsoft.Office.Interop.Excel.Point point1 =
(Microsoft.Office.Interop.Excel.Point)((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).Points(1); // Points(1) 表示第一個資料標籤
point1.DataLabel.Position = XlDataLabelPosition.xlLabelPositionLeft;
point1.DataLabel.Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//刪除單一資料標籤
Microsoft.Office.Interop.Excel.Point point2 =
(Microsoft.Office.Interop.Excel.Point)((Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1)).Points(2);
point2.DataLabel.Delete();
//設定軸線相關資訊
Axis axis1 = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
axis1.HasTitle = true;
axis1.AxisTitle.Text = "刻度名稱1";
//設定第二條圖線的資料範圍
Range chartRange2 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[data.GetLength(0), data.GetLength(1)]);
chart.SetSourceData(chartRange2, misValue);
((Series)chart.SeriesCollection(2)).AxisGroup = XlAxisGroup.xlSecondary; // xlSecondary 表示右邊軸線刻度為此線圖之座標
((Series)chart.SeriesCollection(2)).ChartType = XlChartType.xlColumnClustered; //設定圖線種類
((Series)chart.SeriesCollection(2)).HasDataLabels = true; //顯示資料標籤
/*========================設定趨勢線========================*/
Microsoft.Office.Interop.Excel.Series series = (Microsoft.Office.Interop.Excel.Series)chartPage.SeriesCollection(1);
Microsoft.Office.Interop.Excel.Trendlines trendlines = (Microsoft.Office.Interop.Excel.Trendlines)series.Trendlines(System.Type.Missing);
/*下列為 Microsoft.Office.Interop.Excel.Trendlines.Add 參數
Type : Optional XlTrendlineType. The trendline type.
Order : Optional Object. Required if Type is xlPolynomial.
The trendline order. Must be an integer from 2 to 6, inclusive.
多項式(Polynomial)的細部參數, 順序(Order)的數值範圍為: 2~6,
若趨勢種類非多項式, 則設定 System.Type.Missing
Period : Optional Object. Required if Type is xlMovingAvg. The trendline period.
Must be an integer greater than 1 and less than the number of
data points in the series you're adding a trendline to.
移動平均(MovingAvg)的細部參數, 週期(Period)的數值範圍為: 2~12,
若趨勢種類非移動平均, 則設定 System.Type.Missing
Forward : Optional Object. The number of periods (or units on a scatter chart) that the
trendline extends forward.
Backward : Optional Object. The number of periods (or units on a scatter chart) that the
trendline extends backward.
Intercept : Optional Object. The trendline intercept. If this argument is omitted, the
intercept is automatically set by the regression.
DisplayEquation : Optional Object. True to display the equation of the trendline on the
chart (in the same data label as the R-squared value).
The default value is False.
DisplayRSquared : Optional Object. True to display the R-squared value of the trendline on
the chart (in the same data label as the equation).
The default value is False.
Name : Optional Object. The name of the trendline as text. If this argument is omitted,
Microsoft Excel generates a name.*/
Microsoft.Office.Interop.Excel.Trendline trendline = trendlines.Add(
Microsoft.Office.Interop.Excel.XlTrendlineType.xlPolynomial, System.Type.Missing, 6
, System.Type.Missing, System.Type.Missing, System.Type.Missing,
true, true, System.Type.Missing);
trendline.DisplayEquation = false; //顯示方程式
trendline.DisplayRSquared = false; //顯示右方程式
trendline.Border.Color = series.Interior.Color; //設定線條顏色
trendline.Border.LineStyle = XlLineStyle.xlContinuous; //設定線條類型
trendline.Border.Weight = XlBorderWeight.xlHairline; //設定線條寬度
/*========================設定趨勢線========================*/
/*========================插入方框========================*/
Microsoft.Office.Interop.Excel.Shape markShape = newSheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRoundedRectangle, 20, 10, 100, 100);
markShape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
markShape.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 0, 0));
markShape.Line.Weight = 2f;
/*========================插入方框========================*/
//設定軸線相關資訊
Axis axis2 = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary);
axis2.HasTitle = true;
axis2.AxisTitle.Text = "刻度名稱2";
//設定圖表相關資訊
chart.HasLegend = true; //display Legend
chart.HasTitle = true;
chart.ChartTitle.Text = "Chart Title";
chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;
((Microsoft.Office.Interop.Excel.LegendEntry)chart.Legend.LegendEntries(1)).Delete(); //刪除第1條線說明
//set chart position
Range chartRange3 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[data.GetLength(0) + 2, data.GetLength(1)]);
chartObj.Left = Convert.ToDouble(chartRange3.Left);
chartObj.Top = Convert.ToDouble(chartRange3.Top) + Convert.ToDouble(chartRange3.Height);
sheet = null;
}