C#導出Excel源碼

c#導出excel方法有多種﹐常用的按照microsoft 的方法如下:
1﹑"use automation to transfer data cell by cell "
2﹑"use automation to transfer an array of data to a range on a worksheet "
3﹑"use automation to transfer an ado recordset to a worksheet range "
4﹑"use automation to create a querytable on a worksheet"
5﹑"use the clipboard"
6﹑"create a delimited text file that excel can parse into rows and columns"
7﹑"transfer data to a worksheet using ado.net "
在此本人主要使用第4種方法:即利用excel的querytable導出海量數據。
要利用excel的querytable的方法﹐首先需要引用excel類庫。這個我就不多說了。
第二步就是新增一個窗體文件﹐為簡單起見﹐只在窗體上加一個button按鈕。在button click事件中加入操作的代碼。整個操作﹐我只在一個類中實現﹐源碼附貼如下(office2003)﹕
using system;
using system.collections.generic;
using system.componentmodel;
using system.data;
using system.drawing;
using system.text;
using system.windows.forms;

namespace windowsapplication3
{
    public partial class form1 : form
    {
        private microsoft.office.interop.excel.application m_objexcel = null;
        private microsoft.office.interop.excel.workbooks m_objbooks = null;
        private microsoft.office.interop.excel._workbook m_objbook = null;
        private microsoft.office.interop.excel.sheets m_objsheets = null;
        private microsoft.office.interop.excel._worksheet m_objsheet = null;
        private microsoft.office.interop.excel.range m_objrange = null;
        //private microsoft.office.interop.excel.font m_objfont = null;
        //private microsoft.office.interop.excel.querytables m_objqrytables = null;
        private microsoft.office.interop.excel._querytable m_objqrytable = null;
        // frequenty-used variable for optional arguments.
        private object m_objopt = system.reflection.missing.value;
        //database-used variable
        private system.data.sqlclient.sqlconnection sqlconn = null;
        private string strconnect = "data source='192.168.168.253';password = shs;user id=shs;initial catalog=test_km_erp";
        private system.data.sqlclient.sqlcommand sqlcmd = null;

        //sheets variable
        private double dbsheetsize = 65535;//the hight limit number in one sheet
        private int intsheettotalsize = 0;//total record can divied sheet number
        private double dbtotalsize = 0;//record total number

        public form1()
        {
            initializecomponent();
        }

        private int gettotalsize()
        {
            sqlconn = new system.data.sqlclient.sqlconnection(strconnect);
            sqlcmd = new system.data.sqlclient.sqlcommand("select count(*) from pd_workbil_mst", sqlconn);
            sqlconn.open();
            dbtotalsize = (int)sqlcmd.executescalar();
            sqlconn.close();
            return (int)math.ceiling(dbtotalsize / this.dbsheetsize);
        }
        private void declareexcelapp()
        {
            m_objexcel = new microsoft.office.interop.excel.application();
            m_objbooks = (microsoft.office.interop.excel.workbooks)m_objexcel.workbooks;
            m_objbook = (microsoft.office.interop.excel._workbook)(m_objbooks.add(m_objopt));
            m_objsheets = (microsoft.office.interop.excel.sheets)m_objbook.worksheets;
            intsheettotalsize = gettotalsize();
            if (intsheettotalsize <= 3)
            {
                if (this.dbtotalsize <= this.dbsheetsize)
                {
                    this.exportdatabyquerytable(1, false);
                    return;
                }
                else if (this.dbtotalsize <= this.dbsheetsize * 2)
                {
                    this.exportdatabyquerytable(1, false);
                    this.exportdatabyquerytable(2, true);
                    return;
                }
                else
                {
                    this.exportdatabyquerytable(1, false);
                    this.exportdatabyquerytable(2, true);
                    this.exportdatabyquerytable(3, true);
                    return;
                }
            }
            for (int i = 3; i < intsheettotalsize; i++)
            {
                m_objsheets.add(m_objopt, m_objsheets.get_item(i), m_objopt, m_objopt);
            }
            exportdatabyquerytable(1, false);
            for (int i = 2; i <= m_objsheets.count; i++)
            {
                exportdatabyquerytable(i,true);
            }
        }
        private void saveexcelapp()
        {
            string excelfilename = string.empty;
            savefiledialog sf = new savefiledialog();
            sf.filter = "*.xls|*.*";
            if (sf.showdialog() == dialogresult.ok)
            {
                excelfilename = sf.filename;
            }
            else
            {
                return;
            }
            m_objbook.saveas(excelfilename, m_objopt, m_objopt, m_objopt, m_objopt, m_objopt,
                microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange,
                m_objopt, m_objopt, m_objopt, m_objopt, m_objopt);
        }
        private void exportdatabyquerytable(int intsheetnumber,bool blismorethan)
        {
            string strquery = string.empty;
            if (blismorethan)
            {
                strquery = "select top " +
                this.dbsheetsize + " * from pd_workbil_mst where not  cmpid in (select top " +
                dbsheetsize * (intsheetnumber-1) + "  cmpid from pd_workbil_mst)";
            }
            else
            {
                strquery = "select top " +this.dbsheetsize + " * from pd_workbil_mst ";

            }
            m_objsheet = (microsoft.office.interop.excel._worksheet)(m_objsheets.get_item(intsheetnumber));
            m_objsheet.get_range("a1", m_objopt).set_value(m_objopt, "中文測試一");
            m_objsheet.get_range("b1", m_objopt).set_value(m_objopt, "中文測試二");
            m_objsheet.get_range("c1", m_objopt).set_value(m_objopt, "中文測試三");
            m_objsheet.get_range("d1", m_objopt).set_value(m_objopt, "中文測試四");
            m_objsheet.get_range("e1", m_objopt).set_value(m_objopt, "中文測試五");
            m_objrange = m_objsheet.get_range("a2", m_objopt);
            m_objqrytable = m_objsheet.querytables.add("oledb;provider=sqloledb.1;" + strconnect, m_objrange, strquery);
            m_objqrytable.refreshstyle = microsoft.office.interop.excel.xlcellinsertionmode.xlinsertentirerows;
            m_objqrytable.fieldnames = false;
            m_objqrytable.refresh(false);
        }
        private void button1_click(object sender, eventargs e)
        {
            declareexcelapp();
            saveexcelapp();
        }
    }
}
使用office2000的話﹐好像類庫有些不同﹐為給大家一個比較﹐也將源碼附貼如下﹕
using system;
using system.collections.generic;
using system.componentmodel;
using system.data;
using system.drawing;
using system.text;
using system.windows.forms;

namespace windowsapplication2
{
    public partial class form1 : form
    {
        excel.range m_objrange = null;
        excel.application m_objexcel = null;
        excel.workbooks m_objbooks = null;
        excel._workbook m_objbook = null;
        excel.sheets m_objsheets = null;
        excel._worksheet m_objsheet = null;
        excel.querytable m_objqrytable = null;
        object m_objopt = system.reflection.missing.value;
        //database-used variable
        private system.data.sqlclient.sqlconnection sqlconn = null;
        private string strconnect = "data source='192.168.168.253';password = shs;user id=shs;initial catalog=test_km_erp";
        private system.data.sqlclient.sqlcommand sqlcmd = null;

        //sheets variable
        private double dbsheetsize = 65535;//the hight limit number in one sheet
        private int intsheettotalsize = 0;//total record can divied sheet number
        private double dbtotalsize = 0;//record total number

        public form1()
        {
            initializecomponent();
        }

        private int gettotalsize()
        {
            sqlconn = new system.data.sqlclient.sqlconnection(strconnect);
            sqlcmd = new system.data.sqlclient.sqlcommand("select count(*) from pd_workbil_mst", sqlconn);
            sqlconn.open();
            dbtotalsize = (int)sqlcmd.executescalar();
            sqlconn.close();
            return (int)math.ceiling(dbtotalsize / this.dbsheetsize);
        }
        private void declareexcelapp()
        {
            m_objexcel = new excel.applicationclass();
            m_objbooks = (excel.workbooks)m_objexcel.workbooks;
            m_objbook = (excel._workbook)(m_objbooks.add(m_objopt));
            m_objsheets = (excel.sheets)m_objbook.worksheets;
            intsheettotalsize = gettotalsize();
            if (intsheettotalsize <= 3)
            {
                if (this.dbtotalsize <= this.dbsheetsize)
                {
                    this.exportdatabyquerytable(1, false);
                    return;
                }
                else if (this.dbtotalsize <= this.dbsheetsize * 2)
                {
                    this.exportdatabyquerytable(1, false);
                    this.exportdatabyquerytable(2, true);
                    return;
                }
                else
                {
                    this.exportdatabyquerytable(1, false);
                    this.exportdatabyquerytable(2, true);
                    this.exportdatabyquerytable(3, true);
                    return;
                }
            }
            for (int i = 3; i < intsheettotalsize; i++)
            {
                m_objsheets.add(m_objopt, m_objsheets.get_item(i), m_objopt, m_objopt);
            }
            exportdatabyquerytable(1, false);
            for (int i = 2; i <= m_objsheets.count; i++)
            {
                exportdatabyquerytable(i, true);
            }
        }
        private void saveexcelapp()
        {
            string excelfilename = string.empty;
            savefiledialog sf = new savefiledialog();
            sf.filter = "*.xls|*.*";
            if (sf.showdialog() == dialogresult.ok)
            {
                excelfilename = sf.filename;
            }
            else
            {
                return;
            }
            m_objbook.saveas(excelfilename, m_objopt, m_objopt, m_objopt, m_objopt, m_objopt,
                excel.xlsaveasaccessmode.xlnochange, m_objopt, m_objopt, m_objopt, m_objopt);
        }
        private void exportdatabyquerytable(int intsheetnumber, bool blismorethan)
        {
            string strquery = string.empty;
            if (blismorethan)
            {
                strquery = "select top " +
                this.dbsheetsize + " * from pd_workbil_mst where not  cmpid in (select top " +
                dbsheetsize * (intsheetnumber-1) + "  cmpid from pd_workbil_mst)";
            }
            else
            {
                strquery = "select top " + this.dbsheetsize + " * from pd_workbil_mst ";

            }
            m_objsheet = (excel._worksheet)(m_objsheets.get_item(intsheetnumber));
            m_objsheet.cells[1,1] = "中文測試一";
            m_objsheet.cells[1,2] = "中文測試二";
            m_objsheet.cells[1,3] = "中文測試三";
            m_objsheet.cells[1,4] = "中文測試四";
            m_objsheet.cells[1,5] = "中文測試五";
            m_objrange = m_objsheet.get_range("a2", m_objopt);
            m_objqrytable = m_objsheet.querytables.add("oledb;provider=sqloledb.1;" + strconnect, m_objrange, strquery);
            m_objqrytable.refreshstyle = excel.xlcellinsertionmode.xlinsertentirerows;
            m_objqrytable.fieldnames = false;
            m_objqrytable.refresh(false);
        }

        private void button1_click(object sender, eventargs e)
        {
            declareexcelapp();
            saveexcelapp();

        }

    }
}
在本篇中﹐是將13萬多條記錄分多個sheet導出。如果你的機器大概像我這樣:p4cpu,1g內存的話。全部導出包括保存也就是20秒左右就可以全部搞定。
也許大家會有更好的方法來實現﹐歡迎各位交流﹗