再现C#导出Excel源码

接我上一篇C#導出Excel源碼.
网上反应比较强烈。本人也因为工作需要的原因，将其封装了成了ExcelManager。企业当中，做报表的数据来源肯定就是数据库了。该ExcelManager目前只提供Ms Sql Server的支持，因为我们公司使用的就是ms sql server 2000 了。封装后的ExcelManager，你只需传入你的报表表头（一级表头、二级表头。大部分有两级也就够了。如果你有多个，可自行修改该类.）,并将对应的数据库表字段传入类库中的方法DeclareExcelApp即可。
同前一篇一样，你可将下面代码复制另存一个新类就可以了（不知为什么，我在家里上网老是传附件不上来！faint...）。随后，我会给出一个调用的方法的：
namespace ExportToExcel
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;

数据挖掘论坛

    /***************************
     ****Class Name :   ExcelManger
     ****Author:            KingNa
     ****Create Date :   2006-9-1
     ****CopyRight:     Reserve this info if you want to User this Class
    **************************/
    public class ExcelManager:IDisposable
    {
        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 = string.Empty;
        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 数据挖掘研究院

        /// <summary>
        /// 建构函数
        /// </summary>
        public ExcelManager(){} 数据挖掘实验室

        /// <summary>
        /// 建构函数
        /// </summary>
        /// <param name="dbHL">一个Excel表格的最大记录数</param>
        /// <param name="dbTotal">该数据库表共查询出多少条记录</param>
        /// <param name="intDivide">查询出的记录可分成几个Excel</param>
        /// <param name="conn">sqlConnection</param>
        public ExcelManager(Double dbHL,Double dbTotal,int intDivide,SqlConnection conn )
        {
            dbSheetSize = dbHL; 数据挖掘交友
            intSheetTotalSize = intDivide;
            dbTotalSize = dbTotal;
            sqlConn = conn;
        }
        /// <summary>
        /// 建构函数
        /// </summary>
        /// <param name="dbHL">一个Excel表格的最大记录数</param>
        /// <param name="strTableName">需查询的数据库的表名</param>
        /// <param name="conn">sqlConnection</param>
        public ExcelManager(Double dbHL,string strTableName,SqlConnection conn) 数据挖掘论坛
        {
            dbSheetSize = dbHL;
            sqlConn = conn;
            intSheetTotalSize = GetTotalSize(strTableName,sqlConn);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(bool disposing)
        {
            if(disposing)
            {
                // Dispose managed resources.
                Marshal.FinalReleaseComObject(m_objExcel);
                m_objRange = null;

数据挖掘实验室

数据挖掘工具

        /// <summary>
        /// 新建一个Excel实例
        /// </summary>
        /// <param name="strTitle">Excel表头上的文字</param>
        public void DeclareExcelApp(string[] strTitle,string strSql,string strTableName,string strMastTitle)
        {
            m_objExcel = new Excel.ApplicationClass();
            m_objExcel.Visible = true;
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 数据挖掘工具
            if (intSheetTotalSize <= 3)
            {
                if (this.dbTotalSize <= this.dbSheetSize)
                {
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
                else if (this.dbTotalSize <= this.dbSheetSize * 2)
                { 数据挖掘论坛
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
                    this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
                else
                {
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle ); 数据挖掘工具
                    this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
                    this.ExportDataByQueryTable(3, true,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
            }
            for (int i = 3; i < intSheetTotalSize; i++)
            {
                m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);

数据挖掘研究院

数据挖掘实验室

数据挖掘论坛

数据挖掘工具

            }
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));

数据挖掘交友

            m_objSheet.Cells[1,1] = strMastTitle;
            m_objSheet.Cells[2,1] = "打印日期"+DateTime.Now.ToShortDateString();
            for(int i = 1;i<=strTitle.Length;i++)
            {
                m_objSheet.Cells[4,i] = strTitle[i-1].ToString();
            }
            m_objRange = m_objSheet.get_Range("A5", m_objOpt);
            m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
            m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; 数据挖掘交友
            m_objQryTable.FieldNames = false;
            m_objQryTable.Refresh(false);
        }
    }
}

全盘复制另存新类后，调用方法示例如下：
        private void button2_Click(object sender, EventArgs e)
        {
            #region ExcelManager封装类导出Excel
            String strConnet ="Data Source="localhost";Password = ;User ID=sa;Initial Catalog=Northwind";
            System.Data.SqlClient.SqlConnection sqlConn =
                new System.Data.SqlClient.SqlConnection(strConnet);
            ExcelManager exc = new ExcelManager(65530, "Orders", sqlConn);
            try
            {

数据挖掘交友