//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM [Sheet1$] " ;
myConn.Open ( ) ;
file://打开数据链接，得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
file://创建一个 DataSet对象
myDataSet = new DataSet ( ) ;
file://得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
file://关闭此数据链接
myConn.Close ( ) ;
怎么样读取Excel表格中的数据其实和读取数据库中的数据没有什么实质上的区别。
注释：这里读取的是C盘根目录下的"Sample.xls"文件。
Feedback
将数据库表导出到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)
throw new Exception("没有指定Htmll输出文件路径！");
try
{
book.SaveAs(excelFile, missing, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);
book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
///
/// 在工作表中插入行，并调整其他行以留出空间
///
/// 当前工作表
/// 欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Excel.Range)sheet.Rows[rowIndex, missing];
//object Range.Insert(object shift, object copyorigin);
//shift: Variant类型，可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一：
//xlShiftToRight 或 xlShiftDown。如果省略该参数，Microsoft Excel 将根据区域形状确定调整方式。
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
}
///
/// 在工作表中删除行
///
/// 当前工作表
/// 欲删除的行索引
private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Range)sheet.Rows[rowIndex, missing];
r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
///
/// 退出Excel，并且释放调用的COM资源
///
private void Dispose()
{
book.Close(missing, missing, missing);
app.Workbooks.Close();
app.Quit();
if(range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if(sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
}
if(book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
book = null;
}
if(app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}
///
/// 结束Excel进程
///
private void KillExcelProcess()
{
DateTime startTime;
Process[] processes = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process process in processes)
{
startTime = process.StartTime;
if(startTime > beforeTime && startTime < afterTime)
process.Kill();
}
}
}
}
