C# 操作 Excel 生成報表（三之五）：完整圖表生成代碼總覽

完整按鈕點擊事件代碼

private void BtnGenerate_Click(object sender, EventArgs e)
{
    Excel.Application app = null;
    Excel.Workbooks wbs = null;
    Excel._Workbook wb = null;
    Excel.Worksheet ws = null;

    try
    {
        app = new Excel.Application();
        wbs = app.Workbooks;
        wb = (Excel._Workbook)wbs.Add(Missing.Value);
        app.DisplayAlerts = false;

        DeleteExtraSheets();
        AddDataSheet();
        LoadData();
        CreateChart();

        wb.SaveAs(@"z:\Book2.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
    }
    catch (Exception ex) { MessageBox.Show(ex.Message); }
    finally
    {
        wb?.Close(Missing.Value, Missing.Value, Missing.Value);
        app?.Workbooks.Close();
        app?.Quit();
        Marshal.ReleaseComObject(wb);
        Marshal.ReleaseComObject(app);
        wb = null; app = null;
        GC.Collect(); GC.WaitForPendingFinalizers();
        Close();
    }
}

// 字段
Excel.Application ThisApplication;
Excel.Workbooks ThisWorkbooks;
Excel._Workbook ThisWorkbook;
Excel.Worksheet XlSheet;

輔助方法

// 刪除多餘 Sheet 與 Chart
void DeleteExtraSheets()
{
    foreach (Excel.Worksheet s in ThisWorkbook.Worksheets)
        if (s != ThisApplication.ActiveSheet) s.Delete();
    foreach (Excel.Chart c in ThisWorkbook.Charts) c.Delete();
}

// 添加數據 Sheet
void AddDataSheet()
{
    XlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.Add(Missing.Value, ThisWorkbook.ActiveSheet, Missing.Value, Missing.Value);
    XlSheet.Name = "數據";
}

// 生成隨機測試數據
void LoadData()
{
    Random r = new Random();
    for (int i = 1; i <= 12; i++)
    {
        XlSheet.Cells[i, 1] = i + "月";
        XlSheet.Cells[i, 2] = r.Next(2000).ToString();
    }
}