本文基于讲述 .net 平台execl 文件的基本读取操作。
1:打开execl 文件
private OleDbConnection GetConnection(string Path)
{
try
{
string path = System.IO.Path.GetExtension(Path);
OleDbConnection cn = new OleDbConnection();
string conn = "";
if (path.IndexOf("xlsx") > 0) //2010(Microsoft.ACE.OLEDB.12.0)
conn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", Path);
else //2003(Microsoft.Jet.Oledb.4.0)
conn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", Path);
cn.ConnectionString = conn;
cn.Open();
return cn;
}
catch (Exception ex)
{
throw ex;
}
}
如果本机Open的时候报错 “Provider=Microsoft.ACE.OLEDB.12.0;组件未注册“,请下载AccessDatabaseEngine.exe,下载和开发环境对应的版本即可,网址https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255;
2:获取第一页sheet页数据,并保存为DataTable:
public DataTable ReadExeclTable(OleDbConnection dbconn)
{
DataTable ExeclTable = new DataTable();
try
{
OleDbConnection conn = dbconn;
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = dt.Rows[0][2].ToString().Trim();
//下面是导入指定的sheet到DataSet
DataSet ds = new DataSet();
OleDbCommand omd = new OleDbCommand(string.Format("select * from [{0}]", tableName), conn);
OleDbDataAdapter oda = new OleDbDataAdapter(omd);
oda.Fill(ds);
ExeclTable = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("连接Excel发生异常,请检查后台代码!");
}
return ExeclTable;
}
3:查询某列某个值的行,仅仅获取第一行
private DataRow DataTableSearch(DataTable tb, string Field, string value)
{
DataRow[] rows = tb.Select(Field + "= '" + value + "'");
if (rows != null)
{
if(rows.Length > 0)
return rows[0];
return null;
}
return null;
}
4:获取指定行的数据:
private DataRow ReadOneExeclRecord(int rowIndex, string sheetName,OleDbConnection dbconn)
{
DataRow reValue = null;
DataTable dt = new DataTable();
try
{
OleDbConnection conn = dbconn;
OleDbDataAdapter da = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), conn);
da.Fill(dt);
}
catch (Exception)
{
MessageBox.Show("连接Excel发生异常,请检查后台代码!");
}
if (dt != null && dt.Rows.Count > 0)
{
reValue = dt.Rows[rowIndex];
}
return reValue;
}