C#导出数据到Excel文件的方法
本文实例讲述了C#导出数据到Excel文件的方法。分享给大家供大家参考。具体实现方法如下:
///<summary> ///导出到Excel类,项目需引用Microsodt.Office.Interop.Excel, ///类文件需usingSystem.Data与System.Windows.Forms命名空间 ///</summary> publicclassCToExcel { ///<summary> ///导出到Excel ///</summary> ///<paramname="fileName">默认文件名</param> ///<paramname="listView">数据源,一个页面上的ListView控件</param> ///<paramname="titleRowCount">标题占据的行数,为0表示无标题</param> publicvoidExportExcel(stringfileName,System.Windows.Forms.ListViewlistView,inttitleRowCount) { stringsaveFileName=""; //boolfileSaved=false; SaveFileDialogsaveDialog=newSaveFileDialog(); saveDialog.DefaultExt="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName=fileName; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0)return;//被点了取消 Microsoft.Office.Interop.Excel.ApplicationxlApp; try { xlApp=newMicrosoft.Office.Interop.Excel.Application(); } catch(Exception) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } finally { } Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //写Title if(titleRowCount!=0) MergeCells(worksheet,1,1,titleRowCount,listView.Columns.Count,listView.Tag.ToString()); //写入列标题 for(inti=0;i<=listView.Columns.Count-1;i++) { worksheet.Cells[titleRowCount+1,i+1]=listView.Columns[i].Text; } //写入数值 for(intr=0;r<=listView.Items.Count-1;r++) { for(inti=0;i<=listView.Columns.Count-1;i++) { worksheet.Cells[r+titleRowCount+2,i+1]=listView.Items[r].SubItems[i].Text; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 //if(Microsoft.Office.Interop.cmbxType.Text!="Notification") //{ //Excel.Rangerg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]); //rg.NumberFormat="00000000"; //} if(saveFileName!="") { try { workbook.Saved=true; workbook.SaveCopyAs(saveFileName); //fileSaved=true; } catch(Exceptionex) { //fileSaved=false; MessageBox.Show("导出文件时出错,文件可能正被打开!n"+ex.Message); } } //else //{ //fileSaved=false; //} xlApp.Quit(); GC.Collect();//强行销毁 //if(fileSaved&&System.IO.File.Exists(saveFileName))System.Diagnostics.Process.Start(saveFileName);//打开EXCEL MessageBox.Show(fileName+"导出到Excel成功","提示",MessageBoxButtons.OK); } ///<summary> ///DataTable导出到Excel ///</summary> ///<paramname="fileName">默认的文件名</param> ///<paramname="dataTable">数据源,一个DataTable数据表</param> ///<paramname="titleRowCount">标题占据的行数,为0则表示无标题</param> publicvoidExportExcel(stringfileName,System.Data.DataTabledataTable,inttitleRowCount) { stringsaveFileName=""; //boolfileSaved=false; SaveFileDialogsaveDialog=newSaveFileDialog(); saveDialog.DefaultExt="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName=fileName; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0)return;//被点了取消 Microsoft.Office.Interop.Excel.ApplicationxlApp; try { xlApp=newMicrosoft.Office.Interop.Excel.Application(); } catch(Exception) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } finally { } Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //写Title if(titleRowCount!=0) MergeCells(worksheet,1,1,titleRowCount,dataTable.Columns.Count,dataTable.TableName); //写入列标题 for(inti=0;i<=dataTable.Columns.Count-1;i++) { worksheet.Cells[titleRowCount+1,i+1]=dataTable.Columns[i].ColumnName; } //写入数值 for(intr=0;r<=dataTable.Rows.Count-1;r++) { for(inti=0;i<=dataTable.Columns.Count-1;i++) { worksheet.Cells[r+titleRowCount+2,i+1]=dataTable.Rows[r][i].ToString(); } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 //if(Microsoft.Office.Interop.cmbxType.Text!="Notification") //{ //Excel.Rangerg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]); //rg.NumberFormat="00000000"; //} if(saveFileName!="") { try { workbook.Saved=true; workbook.SaveCopyAs(saveFileName); //fileSaved=true; } catch(Exceptionex) { //fileSaved=false; MessageBox.Show("导出文件时出错,文件可能正被打开!n"+ex.Message); } } //else //{ //fileSaved=false; //} xlApp.Quit(); GC.Collect();//强行销毁 //if(fileSaved&&System.IO.File.Exists(saveFileName))System.Diagnostics.Process.Start(saveFileName);//打开EXCEL MessageBox.Show(fileName+"导出到Excel成功","提示",MessageBoxButtons.OK); } ///<summary> ///合并单元格,并赋值,对指定WorkSheet操作 ///</summary> ///<paramname="sheetIndex">WorkSheet索引</param> ///<paramname="beginRowIndex">开始行索引</param> ///<paramname="beginColumnIndex">开始列索引</param> ///<paramname="endRowIndex">结束行索引</param> ///<paramname="endColumnIndex">结束列索引</param> ///<paramname="text">合并后Range的值</param> publicvoidMergeCells(Microsoft.Office.Interop.Excel.WorksheetworkSheet,intbeginRowIndex,intbeginColumnIndex,intendRowIndex,intendColumnIndex,stringtext) { Microsoft.Office.Interop.Excel.Rangerange=workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]); range.ClearContents();//先把Range内容清除,合并才不会出错 range.MergeCells=true; range.Value2=text; range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment=Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; } }
希望本文所述对大家的C#程序设计有所帮助。