C#导出Excel方法.docx
- 文档编号:12814818
- 上传时间:2023-06-08
- 格式:DOCX
- 页数:13
- 大小:16.51KB
C#导出Excel方法.docx
《C#导出Excel方法.docx》由会员分享,可在线阅读,更多相关《C#导出Excel方法.docx(13页珍藏版)》请在冰点文库上搜索。
C#导出Excel方法
C#导出Excel方法
2010
Ssc
第1章C#导出Excel方法
C#導出Excel方法有多種﹐常用的按照Microsoft的方法如下:
1﹑"UseAutomationtoTransferDataCellbyCell"
2﹑"UseAutomationtoTransferanArrayofDatatoaRangeonaWorksheet"
3﹑"UseAutomationtoTransferanADORecordsettoaWorksheetRange"
4﹑"UseAutomationtoCreateaQueryTableonaWorksheet"
5﹑"UsetheClipboard"
6﹑"CreateaDelimitedTextFilethatExcelCanParseintoRowsandColumns"
7﹑"TransferDatatoaWorksheetUsingADO.NET"
在此本人主要使用第4種方法:
即利用Excel的QueryTable導出海量數據。
要利用Excel的QueryTable的方法﹐首先需要引用Excel類庫。
這個我就不多說了。
第二步就是新增一個窗體文件﹐為簡單起見﹐只在窗體上加一個button按鈕。
在ButtonClick事件中加入操作的代碼。
整個操作﹐我只在一個類中實現﹐源碼附貼如下(office2003)﹕
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
namespaceWindowsApplication3
{
publicpartialclassForm1:
Form
{
privateMicrosoft.Office.Interop.Excel.Applicationm_objExcel=null;
privateMicrosoft.Office.Interop.Excel.Workbooksm_objBooks=null;
privateMicrosoft.Office.Interop.Excel._Workbookm_objBook=null;
privateMicrosoft.Office.Interop.Excel.Sheetsm_objSheets=null;
privateMicrosoft.Office.Interop.Excel._Worksheetm_objSheet=null;
privateMicrosoft.Office.Interop.Excel.Rangem_objRange=null;
//privateMicrosoft.Office.Interop.Excel.Fontm_objFont=null;
//privateMicrosoft.Office.Interop.Excel.QueryTablesm_objQryTables=null;
privateMicrosoft.Office.Interop.Excel._QueryTablem_objQryTable=null;
//Frequenty-usedvariableforoptionalarguments.
privateobjectm_objOpt=System.Reflection.Missing.Value;
//DataBase-usedvariable
privateSystem.Data.SqlClient.SqlConnectionsqlConn=null;
privatestringstrConnect="DataSource='192.168.168.253';Password=SHS;UserID=SHS;InitialCatalog=TEST_KM_ERP";
privateSystem.Data.SqlClient.SqlCommandsqlCmd=null;
//Sheetsvariable
privatedoubledbSheetSize=65535;//thehightlimitnumberinonesheet
privateintintSheetTotalSize=0;//totalrecordcandiviedsheetnumber
privatedoubledbTotalSize=0;//recordtotalnumber
publicForm1()
{
InitializeComponent();
}
privateintGetTotalSize()
{
sqlConn=newSystem.Data.SqlClient.SqlConnection(strConnect);
sqlCmd=newSystem.Data.SqlClient.SqlCommand("SelectCount(*)FromPD_WORKBIL_MST",sqlConn);
sqlConn.Open();
dbTotalSize=(int)sqlCmd.ExecuteScalar();
sqlConn.Close();
return(int)Math.Ceiling(dbTotalSize/this.dbSheetSize);
}
privatevoidDeclareExcelApp()
{
m_objExcel=newMicrosoft.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;
}
elseif(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(inti=3;i { m_objSheets.Add(m_objOpt,m_objSheets.get_Item(i),m_objOpt,m_objOpt); } ExportDataByQueryTable(1,false); for(inti=2;i<=m_objSheets.Count;i++) { ExportDataByQueryTable(i,true); } } privatevoidSaveExcelApp() { stringexcelFileName=string.Empty; SaveFileDialogsf=newSaveFileDialog(); 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); } privatevoidExportDataByQueryTable(intintSheetNumber,boolblIsMoreThan) { stringstrQuery=string.Empty; if(blIsMoreThan) { strQuery="SelectTop"+ this.dbSheetSize+"*FromPD_WORKBIL_MSTWhereNotCMPIDIn(SelectTop"+ dbSheetSize*(intSheetNumber-1)+"CMPIDFromPD_WORKBIL_MST)"; } else { strQuery="SelectTop"+this.dbSheetSize+"*FromPD_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); } privatevoidbutton1_Click(objectsender,EventArgse) { DeclareExcelApp(); SaveExcelApp(); } } } 使用office2000的話﹐好像類庫有些不同﹐為給大家一個比較﹐也將源碼附貼如下﹕ usingSystem; usingSystem.Collections.Generic; usingSystem.ComponentModel; usingSystem.Data; usingSystem.Drawing; usingSystem.Text; usingSystem.Windows.Forms; namespaceWindowsApplication2 { publicpartialclassForm1: Form { Excel.Rangem_objRange=null; Excel.Applicationm_objExcel=null; Excel.Workbooksm_objBooks=null; Excel._Workbookm_objBook=null; Excel.Sheetsm_objSheets=null; Excel._Worksheetm_objSheet=null; Excel.QueryTablem_objQryTable=null; objectm_objOpt=System.Reflection.Missing.Value; //DataBase-usedvariable privateSystem.Data.SqlClient.SqlConnectionsqlConn=null; privatestringstrConnect="DataSource='192.168.168.253';Password=SHS;UserID=SHS;InitialCatalog=TEST_KM_ERP"; privateSystem.Data.SqlClient.SqlCommandsqlCmd=null; //Sheetsvariable privatedoubledbSheetSize=65535;//thehightlimitnumberinonesheet privateintintSheetTotalSize=0;//totalrecordcandiviedsheetnumber privatedoubledbTotalSize=0;//recordtotalnumber publicForm1() { InitializeComponent(); } privateintGetTotalSize() { sqlConn=newSystem.Data.SqlClient.SqlConnection(strConnect); sqlCmd=newSystem.Data.SqlClient.SqlCommand("SelectCount(*)FromPD_WORKBIL_MST",sqlConn); sqlConn.Open(); dbTotalSize=(int)sqlCmd.ExecuteScalar(); sqlConn.Close(); return(int)Math.Ceiling(dbTotalSize/this.dbSheetSize); } privatevoidDeclareExcelApp() { m_objExcel=newExcel.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; } elseif(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(inti=3;i { m_objSheets.Add(m_objOpt,m_objSheets.get_Item(i),m_objOpt,m_objOpt); } ExportDataByQueryTable(1,false); for(inti=2;i<=m_objSheets.Count;i++) { ExportDataByQueryTable(i,true); } } privatevoidSaveExcelApp() { stringexcelFileName=string.Empty; SaveFileDialogsf=newSaveFileDialog(); 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); } privatevoidExportDataByQueryTable(intintSheetNumber,boolblIsMoreThan) { stringstrQuery=string.Empty; if(blIsMoreThan) { strQuery="SelectTop"+ this.dbSheetSize+"*FromPD_WORKBIL_MSTWhereNotCMPIDIn(SelectTop"+ dbSheetSize*(intSheetNumber-1)+"CMPIDFromPD_WORKBIL_MST)"; } else { strQuery="SelectTop"+this.dbSheetSize+"*FromPD_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); } privatevoidbutton1_Click(objectsender,EventArgse) { DeclareExcelApp(); SaveExcelApp(); } } } 在本篇中﹐是將13萬多條記錄分多個Sheet導出。 如果你的機器大概像我這樣: P4CPU,1G內存的話。 全部導出包括保存也就是20秒左右就可以全部搞定。 也許大家會有更好的方法來實現﹐歡迎各位交流﹗
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- C# 导出 Excel 方法