C#导出Excel方法(2)

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()