C#快速从DataGridView(DataTable、DataView)导出到Excel

using System; using System.Collections.Generic; using System.Text; using System.Reflection; using Excel = Microsoft.Office.Interop.Excel; using System.Data; using DevComponents.DotNetBar.Controls; namespace WNOS.ModuleClass { ///经过对Excel深入了解，采用数据写入到range的方法，效率更高，更明显,改进后，效率提高N倍，8000条数据大约需要2秒。 public class ExcelHelper { //快速从DATAGRIDVIEW导出到EXCEL public static bool DataGridViewExportToExcel(DataGridViewX myDataGridView,String strFileName,ref String strMsg) { System.Data.DataTable tmpDataTable = null; //QIUXS取得dataGrid绑定的DataSet if ("System.Data.DataTable".Equals(myDataGridView.DataSource.GetType().ToString())) { tmpDataTable = (System.Data.DataTable)myDataGridView.DataSource;//////取得dataGrid绑定的DataSet } else if ("System.Data.DataView".Equals(myDataGridView.DataSource.GetType().ToString())) { tmpDataTable = ((System.Data.DataView)myDataGridView.DataSource).ToTable();//////取得dataGrid绑定的DataSet } strMsg = ""; // 创建Excel对象 Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { strMsg="Excel无法启动"; return false; } // 创建Excel工作薄 Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1]; Excel.Range range =null; /* // 设置标题 range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]); range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = p_ReportName; xlApp.ActiveCell.Font.Size = 20; xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; */ // 列索引，行索引，总列数，总行数 int colIndex = 0; int RowIndex = 0; int colCount = tmpDataTable.Columns.Count; int RowCount = tmpDataTable.Rows.Count; // 创建缓存数据 object[,] objData = new object[RowCount + 1, colCount]; // 获取列标题 foreach (DataColumn dc in tmpDataTable.Columns) { objData[RowIndex, colIndex++] = dc.ColumnName; } // 获取数据 for (RowIndex = 1; RowIndex < RowCount; RowIndex++) { for (colIndex = 0; colIndex < colCount; colIndex++) { objData[RowIndex, colIndex] = tmpDataTable.Rows[RowIndex - 1][colIndex].ToString(); } } // 写入Excel range= xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]); range.Value2 = objData; // 保存 try { xlBook.Saved = true; xlBook.SaveCopyAs(strFileName); xlApp.Quit(); System.Diagnostics.Process.Start("Excel.exe", strFileName); } catch(Exception ee) { strMsg=ee.Message; return false; } finally { xlApp = null; GC.Collect(); } return true; } } }