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
????????}?