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 ;
                m_objSheet  =   null ;
                m_objSheets  =   null ;
                m_objBooks  =   null ;
                m_objBook  =   null ;
                m_objExcel  =   null ;
            }
        }
         ///   <summary>
         ///  取得总记录数跟可分成几个Excel sheet.
         ///   </summary>
         ///   <param name="strTableName"> 被查询的数据库的表名 </param>
         ///   <param name="sqlConn"> sqlConnection </param>
         ///   <returns> 可分成Excel Sheet的个数 </returns>
         private   int  GetTotalSize( string  strTableName,SqlConnection sqlConn)
        {
             // sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
            sqlCmd  =   new  System.Data.SqlClient.SqlCommand( " Select Count(*) From  " + strTableName, sqlConn);
             if ( this .sqlConn.State  ==  ConnectionState.Closed) sqlConn.Open();
            dbTotalSize  =  ( int )sqlCmd.ExecuteScalar();
            sqlConn.Close();
             return  ( int )Math.Ceiling(dbTotalSize  /   this .dbSheetSize);
        }

         ///   <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);
            }
            ExportDataByQueryTable( 1 ,  false ,strTitle,strSql,strTableName,strMastTitle );
             for  ( int  i  =   2 ; i  <=  m_objSheets.Count; i ++ )
            {
                ExportDataByQueryTable(i,  true ,strTitle,strSql,strTableName,strMastTitle );
            }
        }
         ///   <summary>
         ///  以用户输入的文件名保存文件
         ///   </summary>
         public   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,m_objOpt);
             if  (m_objExcel  !=   null )
                m_objExcel  =   null ;
        }
         ///   <summary>
         ///  利用Excel的QueryTable导出数据
         ///   </summary>
         ///   <param name="intSheetNumber"> 导出第几个sheet </param>
         ///   <param name="blIsMoreThan"> 余下的数据是否大于指定的每个Sheet的最大记录数 </param>
         ///   <param name="strTitle"> 表头,需与查询sql语句对齐一致。 </param>
         ///   <param name="strSql"> 查询的sql语句，表头的文字需与该sql语句对齐一致。 </param>
         ///   <param name="strTablName"> 查询的表名 </param>     
         ///   <param name="strMastTitle"> 主标题 </param>
         ///   </summary>
         public   void  ExportDataByQueryTable( int  intSheetNumber,  bool  blIsMoreThan, string [] strTitle, string  strSql, string  strTablName, string  strMastTitle)
        {
             string  strQuery  =   string .Empty;
             if  (blIsMoreThan)
            {
                strQuery  =   " Select Top  "   +
                     this .dbSheetSize  +  strSql  +   "   From  "   +  strTablName  +   "  Where Not  OrderID In (Select Top  "   +
                    dbSheetSize  *  (intSheetNumber  -   1 )  +   "   OrderID From  "   +  strTablName  +   " ) " ;
            }
             else
            {
                strQuery  =   " Select Top  "   +   this .dbSheetSize  +  strSql +   "   From  " + strTablName;

            }
            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
            {
                exc.DeclareExcelApp( new   string [] {  " 编号 " , " 供应商编号 "  },  "  OrderID,CustomerID  " ,  " Orders " ,  " 报表标题 " );
                 // exc.SaveExcelApp();
            }
             catch (Exception E)
            {
                MessageBox.Show(E.ToString());
            }
             finally
            {
                exc.Dispose();
            }
             #endregion
        }