Read
这里主要是说 C#使用ADO.NET(OleDb数据驱动程序)操作Excel文件,读取数据到数据中。
提供一个ExcelHelper文件,提供数据访问支持,仅供初参考和急用。
ExcelHelper.cs
{
public static readonly ExcelHelper Default = new ExcelHelper();
/// <summary>
/// 数据源(Excel)链接对象
/// </summary>
public OleDbConnection getcon()
{
string path = @" C://Excel/Demo.xls " ;
string M_str_Oledbcon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties=\ " Excel 8.0 ;HDR = YES;IMEX = 1 \ "" ;
OleDbConnection myCon = new OleDbConnection(M_str_Oledbcon);
return myCon;
}
/// <summary>
/// 读取数据 存储在DataTable对象中
/// </summary>
/// <param name="M_str_sqlstr"> Sql 命令 </param>
public DataTable getTable( string M_str_sqlstr)
{
OleDbConnection oleConn = this .getcon();
OleDbDataAdapter myDa = new OleDbDataAdapter(M_str_sqlstr, oleConn);
DataTable dt = new DataTable();
myDa.Fill(dt);
return dt;
}
/// <summary>
/// 获取该数据源Excel的所有工作表(Sheet)
/// </summary>
public DataTable getSheets()
{
OleDbConnection oleConn = this .getcon();
DataTable dt = null ;
try
{
oleConn.Open();
dt = oleConn.GetSchema( " Tables " );
}
catch (Exception e)
{
throw new ApplicationException( " Erro: " + e.Message);
}
finally
{
oleConn.Close();
}
return dt;
}
/// <summary>
/// 获取数据 存储在OleDataReader中
/// </summary>
/// <param name="M_str_sqlstr"> Sql 语句 </param>
/// <returns></returns>
public OleDbDataReader getDataRead( string M_str_sqlstr)
{
OleDbConnection oleConn = this .getcon();
OleDbCommand myCom = new OleDbCommand(M_str_sqlstr, oleConn);
oleConn.Open();
OleDbDataReader myRead = myCom.ExecuteReader(CommandBehavior.CloseConnection);
return myRead;
}
}
使用Excel读取Excel中的数据:
1.获取所有的Sheets
SqlHelper sql = SqlHelper.Default;
DataTable dtOle = excel.getSheets();
DataTableReader dtReader = new DataTableReader(dtOle);
int index = 0 ;
while (dtReader.Read())
{
string temp = dtReader[ " Table_Name " ].ToString();
string sName = temp.Substring( 0 , temp.IndexOf( " $ " ));
if ( ! sql.HasSameRecord( " select * from listofsheets where name =' " + sName + " ' " ))
{
string sSql = " insert into listofsheets(name) values(' " + sName + " ') " ;
sql.Execute(sSql);
index ++ ;
}
}
dtReader.Close();
2.读取某一个工作表Sheet的数据:
ExcelHelper objExcel = new ExcelHelper();
SqlHelper objSql = new SqlHelper();
string str_Sql = "select * from [Sheet1$]";
OleDbDataReader myRead = objExcel.getDataRead(str_Sql);
int iCount=0;
while (myRead.Read())
{
str_No = myRead[0].ToString();
string strSql = @"insert into SqlTable(Filed_1) values('{0}')";
strSql=string.Format(strSql,str_No);
objSql.Execute(strSql);
iCout++;
}
这里同时用到一个SqlHelper.cs用于向Sql server中添加数据:
SqlHelper.cs 代码参考如下:
{
public static readonly SqlHelper Default = new SqlHelper();
private SqlConnection _DbConn;
public SqlConnection getcon()
{
if (_DbConn == null )
{
string M_str_sqlcon = " Data Source=LHDONG;Database=Mytable;User id=sa;PWD=sa " ;
_DbConn = new SqlConnection(M_str_sqlcon);
_DbConn.Open();
}
return _DbConn;
}
public void Execute( string M_str_sqlstr)
{
SqlConnection sqlcon = this .getcon();
SqlCommand myCom = new SqlCommand(M_str_sqlstr, sqlcon);
myCom.ExecuteNonQuery();
myCom.Dispose();
}
public bool HasSameRecord( string M_str_sqlstr)
{
SqlDataReader mydr = getRead(M_str_sqlstr);
if (mydr.HasRows)
{
mydr.Close();
return true ;
}
else
{
mydr.Close();
return false ;
}
}
public DataTable getDs( string M_str_sqlstr)
{
SqlConnection sqlcon = this .getcon();
SqlDataAdapter myDa = new SqlDataAdapter(M_str_sqlstr, sqlcon);
DataTable mydt = new DataTable();
myDa.Fill(mydt);
return mydt;
}
public SqlDataReader getRead( string M_str_sqlstr)
{
SqlConnection sqlcon = this .getcon();
SqlCommand myCom = new SqlCommand(M_str_sqlstr, sqlcon);
SqlDataReader myRead = myCom.ExecuteReader();
return myRead;
}
public SqlDataReader ExecuteReader( string query, params SqlParameter[] parameters)
{
SqlConnection conn = getcon();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.CommandTimeout = 0 ;
cmd.CommandType = CommandType.StoredProcedure;
for ( int i = 0 ; i <= parameters.Length - 1 ; i ++ )
{
cmd.Parameters.Add(parameters[i]);
}
return cmd.ExecuteReader();
}
}