C#导出Excel方法.docx

C#导出Excel方法2010Ssc第1章 C#导出Excel方法 C#導出Excel方法有多種常用的按照Microsoft 的方法如下:1Use Automation to Transfer Data Cell by Cell 2Use Automation to Transfer an Array of Data to a Range on a Worksheet 3Use Automation to Transfer an ADO Recordset to a Worksheet Range 4Use Automation to Create a QueryTable on a Worksheet5Use the Clipboard6Create a Delimited Text File that Excel Can Parse into Rows and Columns7Transfer 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=53;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=53;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 Whe