C#导出Excel方法(3)

{

sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);

sqlCmd = new System.Data.SqlClient.SqlCommand(\PD_WORKBIL_MST\ 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 = \

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 = \

this.dbSheetSize + \ CMPID In (Select Top \

dbSheetSize * (intSheetNumber-1) + \ CMPID From PD_WORKBIL_MST)\ } else {

strQuery = \\

}

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(\ m_objQryTable =

m_objSheet.QueryTables.Add(\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(); } } }

在本篇中﹐是將13萬多條記錄分多個Sheet導出。如果你的機器大概像我這樣:P4CPU,1G內存的話。全部導出包括保存也就是20秒左右就可以全部搞定。

也許大家會有更好的方法來實現﹐歡迎各位交流﹗

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库C#导出Excel方法(3)在线全文阅读。