網(wǎng)上反應(yīng)比較強(qiáng)烈。本人也因?yàn)楣ぷ餍枰脑颍瑢⑵浞庋b了成了ExcelManager。企業(yè)當(dāng)中，做報(bào)表的數(shù)據(jù)來源肯定就是數(shù)據(jù)庫了。該ExcelManager目前只提供Ms Sql Server的支持，因?yàn)槲覀児臼褂玫木褪莔s sql server 2000 了。封裝后的ExcelManager，你只需傳入你的報(bào)表表頭（一級(jí)表頭、二級(jí)表頭。大部分有兩級(jí)也就夠了。如果你有多個(gè)，可自行修改該類.）,并將對(duì)應(yīng)的數(shù)據(jù)庫表字段傳入類庫中的方法DeclareExcelApp即可。
同前一篇一樣，你可將下面代碼復(fù)制另存一個(gè)新類就可以了（不知為什么，我在家里上網(wǎng)老是傳附件不上來！faint...）。隨后，我會(huì)給出一個(gè)調(diào)用的方法的：
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>
????????
///
?建構(gòu)函數(shù)
????????
///
?
</summary>
????????
public
?ExcelManager(){}
????????
///
?
<summary>
????????
///
?建構(gòu)函數(shù)
????????
///
?
</summary>
????????
///
?
<param?name="dbHL">
一個(gè)Excel表格的最大記錄數(shù)
</param>
????????
///
?
<param?name="dbTotal">
該數(shù)據(jù)庫表共查詢出多少條記錄
</param>
????????
///
?
<param?name="intDivide">
查詢出的記錄可分成幾個(gè)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>
????????
///
?建構(gòu)函數(shù)
????????
///
?
</summary>
????????
///
?
<param?name="dbHL">
一個(gè)Excel表格的最大記錄數(shù)
</param>
????????
///
?
<param?name="strTableName">
需查詢的數(shù)據(jù)庫的表名
</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>
????????
///
?取得總記錄數(shù)跟可分成幾個(gè)Excel?sheet.
????????
///
?
</summary>
????????
///
?
<param?name="strTableName">
被查詢的數(shù)據(jù)庫的表名
</param>
????????
///
?
<param?name="sqlConn">
sqlConnection
</param>
????????
///
?
<returns>
可分成Excel?Sheet的個(gè)數(shù)
</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>
????????
///
?新建一個(gè)Excel實(shí)例
????????
///
?
</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導(dǎo)出數(shù)據(jù)
????????
///
?
</summary>
????????
///
?
<param?name="intSheetNumber">
導(dǎo)出第幾個(gè)sheet
</param>
????????
///
?
<param?name="blIsMoreThan">
余下的數(shù)據(jù)是否大于指定的每個(gè)Sheet的最大記錄數(shù)
</param>
????????
///
?
<param?name="strTitle">
表頭,需與查詢sql語句對(duì)齊一致。
</param>
????????
///
?
<param?name="strSql">
查詢的sql語句，表頭的文字需與該sql語句對(duì)齊一致。
</param>
????????
///
?
<param?name="strTablName">
查詢的表名
</param>
????
????????
///
?
<param?name="strMastTitle">
主標(biāo)題
</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
);
????????}
????}
}
全盤復(fù)制另存新類后，調(diào)用方法示例如下：
????????
private
?
void
?button2_Click(
object
?sender,?EventArgs?e)
????????{
????????????
#region
?ExcelManager封裝類導(dǎo)出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
[]?{?
"
編號(hào)
"
,
"
供應(yīng)商編號(hào)
"
?},?
"
?OrderID,CustomerID?
"
,?
"
Orders
"
,?
"
報(bào)表標(biāo)題
"
);
????????????????
//
exc.SaveExcelApp();
????????????}
????????????
catch
(Exception?E)
????????????{
????????????????MessageBox.Show(E.ToString());
????????????}
????????????
finally
????????????{
????????????????exc.Dispose();
????????????}
????????????
#endregion
????????}?
以上使用的是Excel 2002 英文版。2003有些方法稍有出入。可參照前篇的C#導(dǎo)出Excel源碼。另外，如果可能的話，我將封裝其它數(shù)據(jù)庫類型的Excel導(dǎo)出。有興趣的朋友，請(qǐng)繼續(xù)關(guān)繼！