C#導出Excel方法有多種﹐常用的按照Microsoft的方法如下:
1﹑"UseAutomationtoTransferDataCellbyCell"
2﹑"UseAutomationtoTransferanArrayofDatatoaRangeonaWorksheet"
3﹑"UseAutomationtoTransfe
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<intSheetTotalSize;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<intSheetTotalSize;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內存的話。全部導
分享到:
相关推荐
C#大量数据导出到Excel,超过65536行时自动分页导出
1.Excel2003、2007及2010格式的数据导入导出。 2.将Excel2003、2007及2010格式的数据报表导入到datagridview中预览 3.将datagridview中的数据导出为Excel2003、2007及2010格式的数据保存或查看。
这是一个关于C#把数据导出为Excel格式的一个实例,很实用的,还请大家多多指教呀,呵呵,有什么缺陷还请大家及时指正呀,呵呵!!!
C# 将数据导出到Excel各种方法汇总
c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#) winform
C# datatable直接导出数据到Excel,(数据量百万级只需3秒)
根据Microsoft.Office.Interop.Excel,优化大数据量快速导出到 Excel 的方法
C# 将数据导出到excel模板 自定义字段 excel表格可复杂 解压密码: www.shcodetool.cn
C#下成功登录后datagridview数据导出excel
C# Treeview 数据导出到Excel,很好玩的.有注释....
C#程序访问数据库,并按照数据库查询字段导出到excel文件中。访问数据库程序代码采用工厂模式创建,可访问多个数据库,access、sql server2000(2005)、oracle,此程序已在实际工作中稳定运行半年,可当产品出售。...
C#快速导出Excel文件,3秒可导10万行数据
C#中把查询数据结果轻松导出到EXCEL表格中.
C# 从datagridview导出数据到excel表。注:没有使用npoi,直接导出excel数据。导出的数据不包含图片。
C#+NOPI导出DataGridView数据至Excel,在NOPI库支持的基础上,具体实现在代码中。
excel导入导出的问题并进行求和统计的问题困扰了好几天了,现在吐血分享给大家!!!!! 主要功能: 1.导出: 在页面加载的时候,将数据库数据绑定到页面gridview上, 按钮“导出excel”实现从页面导出excel文件, ...
C#将数据导出到Excel汇总 一、asp.net中导出Execl的方法: 在asp.net中导出Execl有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上;一种是将文件直接将文件输出流写给...
C# EXCEL导入导出类 /// /// 导入EXCEL到DataSet /// /// <param name="fileName">Excel全路径文件名 /// 导入成功的DataSet public DataSet ImportExcel(string fileName) /// /// 把DataTable导出...
把数据列表、集合导出到Excel表格 应用例子:Excel.down(list.ToList(), "查询数据", "当日数据", "Id|Code,编码|name,名称,20|num,数量|CreatedTime,时间,18|cause,备注,60");