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

f4431456

贡献于2012-08-14

字数:8720 关键词: .NET开发

将DataGridView或DataTable导出为Excel支持进度条 使用说明: //编程语言:C# 主要用于Winforms应用程序的报表导出操作。 //使用时,请添加引用微软的Microsoft.Office.Interop.Excel组件 //在以前的上传文档基础上增加两个重载方法,共四种参数的重载: //重载方法1:ExportToExcel(System.Data.DataTable dt) //重载方法2:ExportToExcel(DataGridView dgv) //重载方法3:ExportToExcel(System.Data.DataTable dt, ProgressBar pb) //重载方法4:ExportToExcel(DataGridView dgv, ProgressBar pb) //最后两个重载方法支持进度条控件 源代码如下: using System; using System.Collections.Generic; using System.Text; using Microsoft.Office.Interop.Excel; using System.Windows.Forms; namespace DoorControl { /// /// 静态方法ExportToExcel提供四种参数的重载 /// DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel进度 /// 需要使用进度条时,请在窗体中加入一个ProgressBar控件progressBar1,并设置progressBar1.Visible=false /// public class DataTableToExcel { /// /// 将DataGridView转化为DataTable /// /// /// public static System.Data.DataTable dvtodt(DataGridView dv) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.DataColumn dc; for (int i = 0; i < dv.Columns.Count; i++) { dc = new System.Data.DataColumn(); dc.ColumnName = dv.Columns[i].HeaderText.ToString(); dt.Columns.Add(dc); } for (int j = 0; j < dv.Rows.Count; j++) { System.Data.DataRow dr = dt.NewRow(); for (int x = 0; x < dv.Columns.Count; x++) { dr[x] = dv.Rows[j].Cells[x].Value; } dt.Rows.Add(dr); } return dt; } /// /// 将DataGridView导出为Excel文件(.xls) /// /// 要导出的DatGridView public static void ExportToExcel(DataGridView dgv) { //获得DataTable System.Data.DataTable dt = dvtodt(dgv); ExportToExcel(dt); } /// /// 将DataGridView导出为excel文件,导出过程中显示进度条 /// /// 要导出的DatGridView /// 与dgv在同一窗体的ProgressBar控件,pb初始化时Visible=false public static void ExportToExcel(DataGridView dgv, ProgressBar pb) { //获得DataTable System.Data.DataTable dt = dvtodt(dgv); ExportToExcel(dt, pb); } /// /// 将DataTable导出为Excel文件(.xls) /// /// 要导出的DataTable public static void ExportToExcel(System.Data.DataTable dt) { if (dt == null) return; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel"); return; } System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog(); 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.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; string fileName = saveDia.FileName; //写入标题 for (int i = 0; i < dt.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 (int r = 0; r < dt.DefaultView.Count; r++) { for (int i = 0; i < dt.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.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); return; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = 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.DateTime startTime = new DateTime(); int m, killId = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killId = m; } } if (excelProc[killId].HasExited == false) { excelProc[killId].Kill(); } #endregion MessageBox.Show("导出成功!"); } } /// /// 将DataTable导出为excel文件,导出过程中显示进度条 /// /// 要导出的DataTable对象 /// 窗体中的ProgressBar控件,pb初始化时Visible=false public static void ExportToExcel(System.Data.DataTable dt, ProgressBar pb) { if (dt == null) return; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel"); return; } System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog(); 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.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; pb.Visible = true;//将进度条控件显示 pb.Maximum = (int)totalCount;//设置进度条控件的最大值 long rowRead = 0; float percent = 0; string fileName = saveDia.FileName; //写入标题 for (int i = 0; i < dt.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 (int r = 0; r < dt.DefaultView.Count; r++) { for (int i = 0; i < dt.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.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.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); return; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = 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.DateTime startTime = new DateTime(); int m, killId = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killId = m; } } if (excelProc[killId].HasExited == false) { excelProc[killId].Kill(); } #endregion pb.Visible = false;//再次将进度条隐藏 MessageBox.Show("导出成功!"); } } } }

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档