Programs Knowledge

public void DrawingChart(object[,] data, int sheetNum, ref _Workbook excelBook1, ref Worksheet excelSheet1)

{

    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;

    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 = 第幾條線

    ((Series)chart.SeriesCollection(1)).ChartType = XlChartType.xlColumnClustered;

    ((Series)chart.SeriesCollection(1)).HasDataLabels = true;

    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;

    ((Series)chart.SeriesCollection(2)).ChartType = XlChartType.xlLineMarkers;

    ((Series)chart.SeriesCollection(2)).HasDataLabels = true;

    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;

    //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;

}