`
isiqi
  • 浏览: 15947519 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论

C#Excel数据导出

阅读更多

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種方法:即利用ExcelQueryTable導出海量數據。
要利用ExcelQueryTable的方法﹐首先需要引用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內存的話。全部導

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics