C#导出Excel方法

更新时间：2024-05-03 14:25:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

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 {

this.ExportDataByQueryTable(1, false); this.ExportDataByQueryTable(2, true); this.ExportDataByQueryTable(3, true); 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);

for (int i = 2; i <= m_objSheets.Count; i++) {

ExportDataByQueryTable(i,true); } }

private void SaveExcelApp() {

string excelFileName = string.Empty; SaveFileDialog sf = new SaveFileDialog(); sf.Filter = \

if (sf.ShowDialog() == DialogResult.OK) {

excelFileName = sf.FileName; } else {

return; }

m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,

Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); }

private void ExportDataByQueryTable(int intSheetNumber,bool blIsMoreThan) {

string strQuery = string.Empty; if (blIsMoreThan) {

strQuery = \

this.dbSheetSize + \ CMPID In (Select Top \

dbSheetSize * (intSheetNumber-1) + \ CMPID From PD_WORKBIL_MST)\

} else {

strQuery = \

}

m_objSheet =

(Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber)); m_objSheet.get_Range(\中文測試一\ m_objSheet.get_Range(\中文測試二\ m_objSheet.get_Range(\中文測試三\ m_objSheet.get_Range(\中文測試四\ m_objSheet.get_Range(\中文測試五\ m_objRange = m_objSheet.get_Range(\ m_objQryTable =

m_objSheet.QueryTables.Add(\strQuery);

m_objQryTable.RefreshStyle =

Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.FieldNames = false; m_objQryTable.Refresh(false); }

private void button1_Click(object sender, EventArgs e)

{

DeclareExcelApp(); SaveExcelApp(); } } }

使用office2000的話﹐好像類庫有些不同﹐為給大家一個比較﹐也將源碼附貼如下﹕

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text;

using System.Windows.Forms;

namespace WindowsApplication2 {

public partial class Form1 : Form {

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 = \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 Excel.ApplicationClass();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (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 {

this.ExportDataByQueryTable(1, false); this.ExportDataByQueryTable(2, true); this.ExportDataByQueryTable(3, true); 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);

for (int i = 2; i <= m_objSheets.Count; i++)

{

ExportDataByQueryTable(i, true); } }

private void SaveExcelApp() {

string excelFileName = string.Empty; SaveFileDialog sf = new SaveFileDialog(); sf.Filter = \

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); }

private void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan) {

string strQuery = string.Empty; if (blIsMoreThan) {

strQuery = \

this.dbSheetSize + \ CMPID In (Select Top \

dbSheetSize * (intSheetNumber-1) + \ CMPID From PD_WORKBIL_MST)\ } else {

strQuery = \\

}

m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber)); m_objSheet.Cells[1,1] = \中文測試一\ m_objSheet.Cells[1,2] = \中文測試二\ m_objSheet.Cells[1,3] = \中文測試三\ m_objSheet.Cells[1,4] = \中文測試四\ m_objSheet.Cells[1,5] = \中文測試五\

m_objRange = m_objSheet.get_Range(\ m_objQryTable =

m_objSheet.QueryTables.Add(\strQuery);

m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.FieldNames = false; m_objQryTable.Refresh(false); }

private void button1_Click(object sender, EventArgs e) {

DeclareExcelApp(); SaveExcelApp(); } } }

在本篇中﹐是將13萬多條記錄分多個Sheet導出。如果你的機器大概像我這樣:P4CPU,1G內存的話。全部導出包括保存也就是20秒左右就可以全部搞定。

也許大家會有更好的方法來實現﹐歡迎各位交流﹗

本文来源：https://www.bwwdw.com/article/xvjg.html

相关文章：