using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
namespace ExcelRead
{
class ExcelHelper
{
private static string excelConstr;
private OleDbConnection conn = null;//操作数据库
private OleDbDataAdapter ada = null;//填充dataset
public ExcelHelper(string path)
{
excelConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0";
if (conn == null || conn.State == ConnectionState.Closed)
{
conn = new OleDbConnection(excelConstr);
}
}
public DataTable GetDataSource(string sheetName)
{
DataTable dt = new DataTable();
string sql = string.Empty;
sql = "select * from [" + sheetName + "]";
dt = GetDT(sql);
return dt;
}
/// <summary>
/// 获取excel数据
/// </summary>
/// <param name="sql">用于查询的sql</param>
/// <returns></returns>
public DataTable GetDT(string sql)
{
DataSet ds = new DataSet();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
ada = new OleDbDataAdapter(sql, conn);
ada.Fill(ds);
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}
return ds.Tables[0];
}
}
}
//获取Excel表里Sheet1的数据
//调用
ExcelHelper _excelhelper = new ExcelHelper("Excel文件路径");
//Excel的sheet名称,后面要跟$符号
_excelhelper.GetDataSource("Sheet1$");
第二种方式
/// <summary>
/// 根据excel的文件的路径提取其中表的数据,不需要传sheet名称只需
///excel路径即可
/// </summary>
/// <param name="Path">Excel文件的路径</param>
private void GetDataFromExcelWithAppointSheetName(string Path)
{
//连接串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
}
推荐使用第二种