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

将DataGridView或DataTable导出为Excel支持进度条使用说明://编程语言:C#主要用于Winforms应用程序的报表导出操作。//使用时，请添加引用微软的Microsoft.Office.Interop.Excel组件//在以前的上传文档基础上增加两个重载方法，共四种参数的重载：//重载方法1:ExportToExcel(System.Data.DataTabledt)//重载方法2:ExportToExcel(DataGridViewdgv)//重载方法3:ExportToExcel(System.Data.DataTabledt,ProgressBarpb)//重载方法4:ExportToExcel(DataGridViewdgv,ProgressBarpb)//最后两个重载方法支持进度条控件源代码如下:usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingMicrosoft.Office.Interop.Excel;usingSystem.Windows.Forms;namespaceDoorControl{///summary///静态方法ExportToExcel提供四种参数的重载///DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel进度///需要使用进度条时,请在窗体中加入一个ProgressBar控件progressBar1,并设置progressBar1.Visible=false////summarypublicclassDataTableToExcel{///summary///将DataGridView转化为DataTable////summary///paramname=dv/param///returns/returnspublicstaticSystem.Data.DataTabledvtodt(DataGridViewdv){System.Data.DataTabledt=newSystem.Data.DataTable();System.Data.DataColumndc;for(inti=0;idv.Columns.Count;i++){dc=newSystem.Data.DataColumn();dc.ColumnName=dv.Columns[i].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++){dr[x]=dv.Rows[j].Cells[x].Value;}dt.Rows.Add(dr);}returndt;}///summary///将DataGridView导出为Excel文件(.xls)////summary///paramname=dgv要导出的DatGridView/parampublicstaticvoidExportToExcel(DataGridViewdgv){//获得DataTableSystem.Data.DataTabledt=dvtodt(dgv);ExportToExcel(dt);}///summary///将DataGridView导出为excel文件,导出过程中显示进度条////summary///paramname=dgv要导出的DatGridView/param///paramname=pb与dgv在同一窗体的ProgressBar控件,pb初始化时Visible=false/parampublicstaticvoidExportToExcel(DataGridViewdgv,ProgressBarpb){//获得DataTableSystem.Data.DataTabledt=dvtodt(dgv);ExportToExcel(dt,pb);}///summary///将DataTable导出为Excel文件(.xls)////summary///paramname=dt要导出的DataTable/parampublicstaticvoidExportToExcel(System.Data.DataTabledt){if(dt==null)return;Microsoft.Office.Interop.Excel.ApplicationxlApp=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(yyyyMMddHHmmss));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);Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1Microsoft.Office.Interop.Excel.Rangerange=null;longtotalCount=dt.Rows.Count;longrowRead=0;floatpercent=0;stringfileName=saveDia.FileName;//写入标题for(inti=0;idt.Columns.Count;i++){worksheet.Cells[1,i+1]=dt.Columns[i].ColumnName;range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1,i+1];//range.Interior.ColorIndex=15;//背景颜色range.Font.Bold=true;//粗体range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.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;idt.Columns.Count;i++){worksheet.Cells[r+2,i+1]=dt.DefaultView[r][i];range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r+2,i+1];range.Font.Size=9;//字体大小//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,null);range.EntireColumn.AutoFit();//自动调整列宽}rowRead++;percent=((float)(100*rowRead))/totalCount;////进度条//pb.Maximum=(int)totalCount;//pb.Value=(int)rowRead;System.Windows.Forms.Application.DoEvents();}range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;if(dt.Columns.Count1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workbook.Saved=true;workbook.SaveCopyAs(fileName);}catch(Exceptionex){MessageBox.Show(导出文件时出错,文件可能正被打开！\n+ex.Message);return;}workbooks.Close();if(xlApp!=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.Process[]excelProc=System.Diagnostics.Process.GetProcessesByName(EXCEL);System.DateTimestartTime=newDateTime();intm,killId=0;for(m=0;mexcelProc.Length;m++){if(startTimeexcelProc[m].StartTime){startTime