用C#操作Excel以及发生Chart

简介

用一个小范例示范如何在C#中操作Excel

代码

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Excel = Microsoft.Office.Interop.Excel;namespace TestExcel{    public class CBExcel    {        Excel.Application xlApp;    // 声明需要的 Exccel Application 变量        Excel.Workbook xlWorkBook;  // 声明 work book        Excel.Worksheet xlWorkSheet;// 声明 excel 的sheet        object misValue = System.Reflection.Missing.Value;        public CBExcel()        {        }        // 设定数据        public void SetData(int i, int j, string data)        {            xlWorkSheet.Cells[i, j] = data;        }        // 插入一个图表到excel分页中        public void SetChart(string start, string end, Excel.XlChartType type)        {            Excel.Range chartRange;            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); // 建立图表            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); //加入图表及设定大小            Excel.Chart chartPage = myChart.Chart;            chartRange = xlWorkSheet.get_Range(start, end); // 设定图表数据的开始与结尾            chartPage.SetSourceData(chartRange, misValue);            chartPage.ChartType = type; // 设定图表的样式        }        // 释放用的函数        private void releaseObject(object obj)        {            try            {                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);                obj = null;            }            catch            {                obj = null;            }            finally            {                GC.Collect();            }        }        // 创建一个excel        public void Create()        {            xlApp = new Excel.ApplicationClass(); // 创建一个excel            xlWorkBook = xlApp.Workbooks.Add(misValue);            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // 取得第一页的分页        }        // 储存excel        public void SaveAs()        {            // 这样储存会跳出一个另存文件视窗            xlWorkBook.Close(true, misValue, misValue);            xlApp.Quit();        }        public void Release()        {            // 释放用到的excel实例            releaseObject(xlWorkSheet);            releaseObject(xlWorkBook);            releaseObject(xlApp);        }    }}

使用的代码

private void button1_Click(object sender, EventArgs e){    CBExcel excel = new CBExcel(); // 创建CBExcel类    excel.Create(); // 建立Excel    excel.SetData(1, 1, ""); // 设定数据    excel.SetData(1, 2, "Student1");    excel.SetData(1, 3, "Student2");    excel.SetData(1, 4, "Student3");    excel.SetData(2, 1, "Term1");    excel.SetData(2, 2, "80");    excel.SetData(2, 3, "65");    excel.SetData(2, 4, "45");    excel.SetData(3, 1, "Term2");    excel.SetData(3, 2, "81");    excel.SetData(3, 3, "61");    excel.SetData(3, 4, "41");    excel.SetData(4, 1, "Term3");    excel.SetData(4, 2, "82");    excel.SetData(4, 3, "62");    excel.SetData(4, 4, "42");    excel.SetChart("A1", "D4", Excel.XlChartType.xlLine); // 设定图表样式与开始结束位置    excel.SaveAs(); // 储存Excel    excel.Release();// 释放内存}

excel输出结果

范例下载

http://download.csdn.net/detail/cloudhsu/5072683

2013/02/20新增

支持Excel与CSV数据汇出的代码

因为在实际的需求中，公司中的一些计算机可能不会安装Excel，为了让汇出的工作能正常运作，因此我做了下列的修正

接口:IExcel用来作抽象

interface IExcel{    void Create();    void SetData(int i, int j, string data);    void SaveAs();    void Release();}

CBExcel的部份则实践IExcel接口

public class CBExcel : IExcel{    Excel.Application xlApp;    Excel.Workbook xlWorkBook;    Excel.Worksheet xlWorkSheet;    object misValue = System.Reflection.Missing.Value;    const string ChartStart = "A1";    string m_ChartEnd;    int m_MaxI;    int m_MaxJ;    public CBExcel()    {        m_ChartEnd = "A1";        m_MaxI = -1;        m_MaxJ = -1;    }    public void SetData(int i, int j, string data)    {        xlWorkSheet.Cells[i, j] = data;        CheckChartEnd(i, j);    }    private void CheckChartEnd(int i, int j)    {        if (m_MaxI <= i)            m_MaxI = i;        if (m_MaxJ <= j)            m_MaxJ = j;        const int a = 0x41;        int word = a + j - 1;        m_ChartEnd = string.Format("{0}{1}", Convert.ToChar(word), m_MaxI);    }    public void SetChart(Excel.XlChartType type)    {        Excel.Range chartRange;        Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);        Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);        Excel.Chart chartPage = myChart.Chart;        chartRange = xlWorkSheet.get_Range(ChartStart, m_ChartEnd);        chartPage.SetSourceData(chartRange, misValue);        chartPage.ChartType = type;    }    public void SetChart(string start, string end, Excel.XlChartType type)    {        Excel.Range chartRange;        Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);        Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);        Excel.Chart chartPage = myChart.Chart;        chartRange = xlWorkSheet.get_Range(start, end);        chartPage.SetSourceData(chartRange, misValue);        chartPage.ChartType = type;    }    private void releaseObject(object obj)    {        try        {            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);            obj = null;        }        catch        {            obj = null;        }        finally        {            GC.Collect();        }    }    public void Create()    {        xlApp = new Excel.ApplicationClass();        xlWorkBook = xlApp.Workbooks.Add(misValue);        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);    }    public void SaveAs()    {        SetChart(Excel.XlChartType.xlLine);        xlWorkBook.Close(true, misValue, misValue);        xlApp.Quit();    }    public void Release()    {        releaseObject(xlWorkSheet);        releaseObject(xlWorkBook);        releaseObject(xlApp);    }}

额外在实践一个CBExcelCSV

我用最偷懒的实践手法，直接开个够大的内存来存数据

public class CBExcelCSV : IExcel{    List<string[]> m_data = new List<string[]>();    const int MAX_COLUMN = 150;    const int MAX_LINE = 2000;    int m_MaxLine;    int m_MaxColumn;    #region IExcel Members    public void Create()    {        m_MaxColumn = -1;        m_MaxLine = MAX_LINE;        for (int i = 0; i < MAX_LINE; i++)        {            m_data.Add(new string[MAX_COLUMN]);        }    }    public void SetData(int i, int j, string data)    {        if (i-1 >= m_MaxLine)        {            m_data.Add(new string[MAX_COLUMN]);            m_MaxLine++;        }        if (m_MaxColumn < j)        {            m_MaxColumn = j;        }        m_data[i-1][j-1] = data;    }    public void SaveAs()    {        StringBuilder sb = new StringBuilder();        for (int i = 0; i < m_data.Count; i++ )        {            sb.Append(m_data[i][0]);            for (int j = 1; j < m_MaxColumn; j++)            {                sb.Append(",");                sb.Append(m_data[i][j]);            }            sb.Append("\r\n");        }        save(sb.ToString());    }    void save(string data)    {        SaveFileDialog saveFileDialog1 = new SaveFileDialog();        saveFileDialog1.Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*";        saveFileDialog1.FilterIndex = 1;        saveFileDialog1.RestoreDirectory = true;        if (saveFileDialog1.ShowDialog() == DialogResult.OK)        {            string fileName = saveFileDialog1.FileName;            File.WriteAllText(fileName, data);        }    }    public void Release()    {    }    #endregion}

再实践一个CBExcelWrapper来封装，里面有一个IExcel成员，并将所有的执行交给IExcel的实例执行。

public class CBExcelWrapper : IExcel{    IExcel excel;    bool m_IsExcelInstalled;    public CBExcelWrapper()    {        m_IsExcelInstalled = CheckExcelInstalled();        // 有安装Excel就使用CBExcel类，没有则使用CBExcelCSV类        if(m_IsExcelInstalled)        {            excel = new CBExcel();        }        else        {            excel = new CBExcelCSV();        }    }    bool CheckExcelInstalled()    {        // 这一段代码的重点就是判断到底有没有装Excel        bool installed = false;        Type officeType = Type.GetTypeFromProgID("Excel.Application");        if (officeType != null)        {            installed = true;        }        return installed;    }    #region IExcel Members    public void Create()    {        excel.Create();    }    public void SaveAs()    {        excel.SaveAs();    }    public void Release()    {        excel.Release();    }    public void SetData(int i, int j, string data)    {        excel.SetData(i, j, data);    }    #endregion}