将数据库表导出到Excel

将数据库表导出到Excel，并生成文件（C#实现） 关键词： C#, ASP.NET, Excel 需添加项目引用： 1. .NET->System.Data.OracleClient.dll 2. COM->Microsoft Excel 11.0 Object Library 代码如下： using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnostics; using System.Configuration; using System.Collections; using Excel;

namespace ProtoType { /// /// 套用模板输出Excel，生成xls文件和html文件 /// Author: Liu Wen /// Date Created: 2006-8 /// public class ExportExcel { #region variable member protected string templateFile = null; protected string excelFile = null; protected string htmlFile = null; protected object missing = Missing.Value; Excel.ApplicationClass app; Excel.Workbook book; Excel.Worksheet sheet; Excel.Range range; private DateTime beforeTime; //Excel启动之前时间 private DateTime afterTime; //Excel启动之后时间 #endregion /// /// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径 /// /// Excel模板文件路径 /// Excel输出文件路径 /// Html输出文件路径 public ExportExcel(string templateFile, string excelFile, string htmlFile) { if(templateFile == null) throw new Exception("Excel模板文件路径不能为空！");

if(excelFile == null) throw new Exception("Excel输出文件路径不能为空！");

if(htmlFile == null) throw new Exception("Html输出文件路径不能为空！"); if(!File.Exists(templateFile)) throw new Exception("指定路径的Excel模板文件不存在！");

this.templateFile = templateFile; this.excelFile = excelFile; this.htmlFile = htmlFile; //创建一个Application对象 beforeTime = DateTime.Now; app = new ApplicationClass(); //app.Visible = true; afterTime = DateTime.Now; //打开模板文件，得到WorkBook对象 try { book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing,missing,missing); } catch (Exception e) { throw e; } //得到WorkSheet对象 sheet = (Excel.Worksheet)book.Sheets.get_Item(1); }

/// /// 将DataTable数据导出到Excel（可动态插入行） /// /// DataTable /// 插入行的索引 /// 插入列的索引 public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex) { //range = sheet.get_Range("A7", missing); //range.Value2 = "raogerrr"; //string str = range.Text.ToString();

int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol;

//将数据导出到相应的单元格 for (iRow = 0; iRow < rowCount; iRow++) { //插入新行 this.InsertRows(sheet, iRow+rowIndex); //填充当前行 for (iCol = 0; iCol < colCount; iCol++) { sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString(); } } this.DeleteRows(sheet, rowCount+rowIndex); //Excel.QueryTables qts = sheet.QueryTables; //Excel.QueryTable qt = qts.Add(,,); //qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; //qt.Refresh(); } /// /// 将DataTable数据导出到Excel（可动态插入行） /// /// DataTable /// 插入数据的起始单元格 public void DataTableToExcel(System.Data.DataTable dt, string cellID) { int rowIndex = sheet.get_Range(cellID, missing).Row; int colIndex = sheet.get_Range(cellID, missing).Column; int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol;

//利用二维数组批量写入 string[,] array = new string[rowCount,colCount]; for (iRow = 0; iRow < rowCount; iRow++) { for (iCol = 0; iCol < colCount; iCol++) { array[iRow,iCol] = dt.Rows[iRow][iCol].ToString(); } }

for (iRow = 0; iRow < rowCount; iRow++) { this.InsertRows(sheet, iRow+rowIndex); } this.DeleteRows(sheet, rowCount+rowIndex);

range = sheet.get_Range(cellID, missing); range = range.get_Resize(rowCount, colCount); range.Value2 = array; }

/// /// 将DataTable数据导出到Excel（固定） /// /// DataTable /// 插入数据的起始单元格 public void DataTableToExcel2(System.Data.DataTable dt, string cellID) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol;

//利用二维数组批量写入 string[,] array = new string[rowCount,colCount]; for (iRow = 0; iRow < rowCount; iRow++) { for (iCol = 0; iCol < colCount; iCol++) { array[iRow,iCol] = dt.Rows[iRow][iCol].ToString(); } }

range = sheet.get_Range(cellID, missing); range = range.get_Resize(rowCount, colCount); range.Value2 = array; }

/// /// 输出生成的Excel, Html文件 /// public void OutputFile() { if (this.excelFile == null) throw new Exception("没有指定Excel输出文件路径！"); if (this.htmlFile == null)