网上反应比较强烈。本人也因为工作需要的原因，将其封装了成了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
;
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
}
以上使用的是Excel 2002 英文版。2003有些方法稍有出入。可参照前篇的C#导出Excel源码。另外，如果可能的话，我将封装其它数据库类型的Excel导出。有兴趣的朋友，请继续关继！