方法一:采用OleDB读取EXCEL文件:
用这种方法读取Excel速度还是非常的快的,但这种方式读取数据的时候不太灵活,不过可以在DataTable 中对数据进行一些删减修改这种方式将Excel作为一个数据源,直接用Sql语句获取数据了。所以读取之前要知道此次要读取的Sheet(当然也可以用序号,类似dt.Row[0][0]。
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
OleDbConnection conn new OleDbConnection(connStr);
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
以上是读取Excel的Sheet名,xls和xlsx的连接字符串也不一样的,可以根据文件的后缀来区别。这里需要注意的一点,Excel里面只有一个Sheet,但通过这种方式读取Sheet可能会大于一个。
在使用过程中发现取出的Sheet和实际excel不一致, 会多出不少。目前总结后有两种情况:
1. 取出的名称中,包括了XL命名管理器中的名称(参见XL2007的公式--命名管理器, 快捷键Crtl+F3);
2. 取出的名称中,包括了FilterDatabase后缀的, 这是XL用来记录Filter范围的, 参见 http://www.mrexcel.com/forum/showthread.php?t=27225
对于第一点比较简单, 删除已有命名管理器中的内容即可;第二点处理起来比较麻烦, Filter删除后这些名称依然保留着,简单的做法是新增sheet然后将原sheet Copy进去
/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static DataSet ToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(fileName);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName= null;
DataSet ds = new DataSet();
try
{
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName .Contains("$") && !SheetName .Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName ), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, tblName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}