网上反应比较强烈。本人也因为工作需要的原因,将其封装了成了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 );
}
}
}
{
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
}
{
#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导出。有兴趣的朋友,请继续关继!