DataGridView或DataTable导出为Excel-处理了数字字符串去掉前面0的问题-支持进度条.doc

DataGridView或DataTable导出为Excel,处理了数字字符串的bug使用说明:/以前文档存在以下的Bug:数字字符串如部门编号001003将去掉前面的0变成1003.这是不合要求的。/对以前上传的文档出现的bug表示抱歉，现更正过来,只需将代码 /worksheet.Cellsr + 2, i + 1 = dt.DefaultViewri;/修改为(共需修改2处)/worksheet.Cellsr + 2, i + 1 = “” + dt.DefaultViewri.ToString();/加单引号为了表示单元格/一直是文本格式/编程语言: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 Channels / / 静态方法ExportToExcel提供四种参数的重载 / DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel进度 加单引号”,则表示单元格一直是文本,如001003显示为001003,而不是1003 / 需要使用进度条时,请在窗体中加入一个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.Columnsi.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+) drx = dv.Rowsj.Cellsx.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.Worksheets1;/取得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.Cells1, i + 1 = dt.Columnsi.ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells1, 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 1) range.BordersMicrosoft.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 excelProcm.StartTime) startTime = excelProcm.StartTime; killId = m; if (excelProckillId.HasExited = false) excelProckillId.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.Worksheets1;/取得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.Cells1, i + 1 = dt.Columnsi.ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells1, 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 1) range.BordersMicrosoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; try workbook.Saved = true; workbook.SaveCopyAs(fileName); catch (