C#导出Excel方法
2010
Ssc
第1章 C#导出Excel方法
C#導出Excel方法有多種﹐常用的按照Microsoft 的方法如下: 1﹑\
2﹑\3﹑\4﹑\5﹑\
6﹑\7﹑\
在此本人主要使用第4種方法:即利用Excel的QueryTable導出海量數據。
要利用Excel的QueryTable的方法﹐首先需要引用Excel類庫。這個我就不多說了。
第二步就是新增一個窗體文件﹐為簡單起見﹐只在窗體上加一個button按鈕。在Button Click事件中加入操作的代碼。整個操作﹐我只在一個類中實現﹐源碼附貼如下(office2003)﹕
using System;
using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text;
using System.Windows.Forms;
namespace WindowsApplication3 {
public partial class Form1 : Form {
private Microsoft.Office.Interop.Excel.Application m_objExcel = null; private Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null; private Microsoft.Office.Interop.Excel._Workbook m_objBook = null; private Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; private Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; private Microsoft.Office.Interop.Excel.Range m_objRange = null; //private Microsoft.Office.Interop.Excel.Font m_objFont = null;
//private Microsoft.Office.Interop.Excel.QueryTables m_objQryTables = null; private Microsoft.Office.Interop.Excel._QueryTable m_objQryTable = null; // Frequenty-used variable for optional arguments.
private object m_objOpt = System.Reflection.Missing.Value; //DataBase-used variable
private System.Data.SqlClient.SqlConnection sqlConn = null;
private string strConnect = \ID=SHS;Initial Catalog=TEST_KM_ERP\
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
public Form1() {
InitializeComponent(); }
private int GetTotalSize() {
sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
sqlCmd = new System.Data.SqlClient.SqlCommand(\PD_WORKBIL_MST\ sqlConn.Open();
dbTotalSize = (int)sqlCmd.ExecuteScalar(); sqlConn.Close();
return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize); }
private void DeclareExcelApp() {
m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks =
(Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBook =
(Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; intSheetTotalSize = GetTotalSize(); if (intSheetTotalSize <= 3) {
if (this.dbTotalSize <= this.dbSheetSize) {
this.ExportDataByQueryTable(1, false); return; }
else if (this.dbTotalSize <= this.dbSheetSize * 2) {
this.ExportDataByQueryTable(1, false); this.ExportDataByQueryTable(2, true); return; } else {