用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.youkuaiyun.com/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
}