C#导出Excel方法

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 {

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库C#导出Excel方法在线全文阅读。