将DataGridView或DataTable导出为Excel支持进度条

1、将DataGridView或DataTable导出为Excel支持进度条使用说明:编程语言:C#主要用于Winforms应用程序的报表导出操作。/使用时，请添加引用微软的Microsoft.Office.Interop.Excel组件/在以前的上传文档基础上增加两个重载方法，共四种参数的重载：/重载方法1:ExportToExcel(System.Data.DataTabledt)/重载方法2:ExportToExcel(DataGridViewdgv)/重载方法3:ExportToExcel(System.Data.DataTabledt,ProgressBarpb)/重载方法4:Expor

2、tToExcel(DataGridViewdgv,ProgressBarpb)/最后两个重载方法支持进度条控件源代码如下:usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingMicrosoft.Office.Interop.Excel;usingSystem.Windows.Forms;namespaceDoorControl/静态方法ExportToExcel提供四种参数的重载/DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel进度/需要使用进度条时,请在窗体

3、中加入一个ProgressBar控件progressBar1,并设置progressBar1.Visible=false/publicclassDataTableToExcel/将DataGridView转化为DataTable/publicstaticSystem.Data.DataTabledvtodt(DataGridViewdv)System.Data.DataTabledt=newSystem.Data.DataTable();System.Data.DataColumndc;for(inti=0;idv.Columns.Count;i+)dc=newSystem.Data.Data

4、Column();dc.ColumnName=dv.Columnsi.HeaderText.ToString();dt.Columns.Add(dc);for(intj=0;jdv.Rows.Count;j+)System.Data.DataRowdr=dt.NewRow();for(intx=0;xdv.Columns.Count;x+)drx=dv.Rowsj.Cellsx.Value;dt.Rows.Add(dr);returndt;/Ill将DataGridView导出为Excel文件(.xls)/Illvparamname=dgv要导出的DatGridViewpublicstatic

5、voidExportToExcel(DataGridViewdgv)获得DataTableSystem.Data.DataTabledt=dvtodt(dgv);ExportToExcel(dt);lllvsummaryIll将DataGridView导出为excel文件，导出过程中显示进度条lllvlsummarylllvparamname=dgv要导出的DatGridViewvlparamlllvparamname=pb与dgv在同一窗体的ProgressBar控件,pb初始化时Visible=falsevlparampublicstaticvoidExportToExcel(DataGr

6、idViewdgv,ProgressBarpb)获得DataTableSystem.Data.DataTabledt=dvtodt(dgv);ExportToExcel(dt,pb);lllvsummarylll将DataTable导出为Excel文件(.xls)lllvlsummarylllvparamname=dt要导出的DataTablevlparampublicstaticvoidExportToExcel(System.Data.DataTabledt)if(dt=null)return;Microsoft.Office.Interop.Excel.ApplicationxlApp=

7、newMicrosoft.Office.Interop.Excel.Application();if(xlApp=null)MessageBox.Show(”无法创建Excel对象，可能您的电脑未安装Excel);return;System.Windows.Forms.SaveFileDialogsaveDia=newSaveFileDialog();saveDia.Filter=Excel|*.xls;saveDia.Title=导出为Excel文件；saveDia.FileName=string.Format(0.xls,DateTime.Now.ToString(yyyyMMddHHmm

8、ss);if(saveDia.ShowDialog()=System.Windows.Forms.DialogResult.OK&!string.Empty.Equals(saveDia.FileName)Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);Microso

9、ft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheetsl;取得sheetlMicrosoft.Office.Interop.Excel.Rangerange=null;longtotalCount=dt.Rows.Count;longrowRead=0;floatpercent=0;stringfileName=saveDia.FileName;/写入标题for(inti=0;idt.Columns.Count;i+)worksheet.Ce

10、llsl,i+l=dt.Columnsi.ColumnName;range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cellsl,i+l;/range.Interior.ColorIndex=15;/背景颜色range.Font.Bold=true;/粗体range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;/居中/加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLine

11、Style.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,null);/range.ColumnWidth=4.63;/设置列宽/range.EntireColumn.AutoFit();/自动调整列宽return;rl.EntireRow.AutoFit();自动调整行高/写入内容for(intr=0;rdt.DefaultView.Count;r+)for(inti=0;il

12、)range.BordersMicrosoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical.Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;tryworkbook.Saved=true;workbook.SaveCopyAs(fileName);catch(Exceptionex)MessageBox.Show(”导出文件时出错,文件可能正被打开！n+ex.Message);if(dt=null)return;workbooks.Close();if(xlApp

13、!=null)xlApp.Workbooks.Close();xlApp.Quit();intgeneration=System.GC.GetGeneration(xlApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp=null;System.GC.Collect(generation);GC.Collect();强行销毁#region强行杀死最近打开的Excel进程System.Diagnostics.ProcessexcelProc=System.Diagnostics.Process.GetP

14、rocessesByName(EXCEL);System.DateTimestartTime=newDateTime();intm,killId=0;for(m=0;mexcelProc.Length;m+)if(startTimeexcelProcm.StartTime)startTime=excelProcm.StartTime;killId=m;if(excelProckillId.HasExited=false)excelProckillId.Kill();#endregionMessageBox.Show(”导出成功!);/将DataTable导出为excel文件，导出过程中显示进度

15、条/vparamname=dt要导出的DataTable对象/vparamname=pb窗体中的ProgressBar控件,pb初始化时Visible=falsev/parampublicstaticvoidExportToExcel(System.Data.DataTabledt，ProgressBarpb)return;Microsoft.Office.Interop.Excel.ApplicationxlApp=newMicrosoft.Office.Interop.Excel.Application();if(xlApp=null)MessageBox.Show(”无法创建Excel对

16、象，可能您的电脑未安装Excel);return;System.Windows.Forms.SaveFileDialogsaveDia=newSaveFileDialog();saveDia.Filter=Excel|*.xls;saveDia.Title=导出为Excel文件；saveDia.FileName=string.Format(0.xls,DateTime.Now.ToString(yyyyMMddHHmmss);if(saveDia.ShowDialog()=System.Windows.Forms.DialogResult.OK&!string.Empty.Equals(sav

17、eDia.FileName)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)work

18、book.Worksheetsl;取得sheetlMicrosoft.Office.Interop.Excel.Rangerange=null;longtotalCount=dt.Rows.Count;pb.Visible=true;/将进度条控件显示pb.Maximum=(int)totalCount;/设置进度条控件的最大值longrowRead=0;floatpercent=0;stringfileName=saveDia.FileName;/写入标题for(inti=0;idt.Columns.Count;i+)worksheet.Cellsl,i+l=dt.Columnsi.Colu

19、mnName;range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cellsl,i+l;/range.Interior.ColorIndex=15;/背景颜色range.Font.Bold=true;/粗体range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;/居中/加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Micros

20、oft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,null);/range.ColumnWidth=4.63;/设置列宽/range.EntireColumn.AutoFit();/自动调整列宽r1.EntireRow.AutoFit();自动调整行高/写入内容for(intr=0;rdt.DefaultView.Count;r+)for(inti=0;i1)range.BordersMicrosoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical.Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;tryworkbook.Saved=true;workbook.SaveCopyAs(fileName);ca