网上反应比较强烈。本人也因为工作需要的原因，将其封装了成了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实例 /本篇文章发表于www.xker.com(小新技术网)
/// </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;
共3页: 上一页 1 [2] [3] 下一页
