C#導出Excel源碼

王朝c#·作者佚名  2008-05-30
窄屏简体版  字體: |||超大  

C#導出Excel方法有多種﹐常用的按照Microsoft 的方法如下:

1﹑"Use Automation to Transfer Data Cell by Cell "

2﹑"Use Automation to Transfer an Array of Data to a Range on a Worksheet "

3﹑"Use Automation to Transfer an ADO Recordset to a Worksheet Range "

4﹑"Use Automation to Create a QueryTable on a Worksheet"

5﹑"Use the Clipboard"

6﹑"Create a Delimited Text File that Excel Can Parse into Rows and Columns"

7﹑"Transfer Data to a Worksheet Using ADO.NET "

在此本人主要使用第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 = "Data Source='192.168.168.253';Password = SHS;User 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("Select Count(*) From PD_WORKBIL_MST", sqlConn);

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 = "*.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,

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 = "Select Top " +

this.dbSheetSize + " * From PD_WORKBIL_MST Where Not CMPID In (Select Top " +

dbSheetSize * (intSheetNumber-1) + " CMPID From PD_WORKBIL_MST)";

}

else

{

strQuery = "Select Top " +this.dbSheetSize + " * From PD_WORKBIL_MST ";

}

m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));

m_objSheet.get_Range("A1", m_objOpt).set_Value(m_objOpt, "中文測試一");

m_objSheet.get_Range("B1", m_objOpt).set_Value(m_objOpt, "中文測試二");

m_objSheet.get_Range("C1", m_objOpt).set_Value(m_objOpt, "中文測試三");

m_objSheet.get_Range("D1", m_objOpt).set_Value(m_objOpt, "中文測試四");

m_objSheet.get_Range("E1", m_objOpt).set_Value(m_objOpt, "中文測試五");

m_objRange = m_objSheet.get_Range("A2", m_objOpt);

m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + strConnect, m_objRange, 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 = "Data Source='192.168.168.253';Password = SHS;User 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("Select Count(*) From PD_WORKBIL_MST", sqlConn);

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 = "*.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);

}

private void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan)

{

string strQuery = string.Empty;

if (blIsMoreThan)

{

strQuery = "Select Top " +

this.dbSheetSize + " * From PD_WORKBIL_MST Where Not CMPID In (Select Top " +

dbSheetSize * (intSheetNumber-1) + " CMPID From PD_WORKBIL_MST)";

}

else

{

strQuery = "Select Top " + this.dbSheetSize + " * From PD_WORKBIL_MST ";

}

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("A2", m_objOpt);

m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + strConnect, m_objRange, 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秒左右就可以全部搞定。

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

http://www.cnblogs.com/jinliangliu/archive/2006/08/18/480391.html

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航