C#导出Excel源码
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();
}
}
}
From:http://www.cnblogs.com/jinliangliu/archive/2006/08/18/480391.html
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(); } }}From:http://www.cnblogs.com/jinliangliu/archive/2006/08/18/480391.html
