接我上一篇C#導出Excel源碼.
網上反應比較強烈。本人也因為工作需要的原因，將其封裝了成了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導出。
