private DataSet FindInfoFromExcel(string stFilePath,string tableName,string stFiles,string stPrimaryKey,string stCondition,string stRows,string stBeginRowcount,int flag)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;"
+ "data source=" + stFilePath + ";"
+ "Extended Properties=Excel 8.0;";
String strSql = string.Empty;
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
if (stRows != "" && flag==1)
{
//strSql = "Select top(1) " + stFiles + " From [" + tableName + "] order by " + stPrimaryKey + "";
strSql = "Select top "+stRows+" * From [" + tableName + "$"+stFiles+"]";
}
else if(flag==2 && stBeginRowcount!="")
{
strSql = "select top " + stRows + " * from [" + tableName + "$" + stFiles + "] where idx not in (select top " + stBeginRowcount + " idx from [" + tableName + "$]) ";
}
else if(flag ==3)
{
//strSql = "Select " + stFiles + " From [" + tableName + "] order by " + stPrimaryKey + "";
//strSql = "Select " + stFiles + " From [" + tableName + "$A:B]";
strSql = "Select * From [" + tableName + "$" + stFiles + "]";
}
FindInfoFromExcel(Excel存放路径,Excel要导入的sheet表名,Excel表的列名,数据库表的主健,查询Excel的where条件,读取Excel的行数,从Excel的第几行开始读取,Flag就是灵活的执行下面的查找Excel的方式)
flag=1 为导出第一行 ,flag =2 指定从第几行开始导出 ,flag=3 导出整个Excel表
---------------------------------------------------------------------------------------------------------------------
我测试了下,好像第一行永远都取不到(不管第一行是表头或是表内容[XP+VS2005+excel2003])
用SQL语句取出时,第二行才算第一笔记录
设我的excel是三列(A,B,C)
//以下为查询第二行开始的,所有记录
string strExcel ="select * from [sheet1$A1:C65535]";//sheet1为表名
//以下为查询第五行开始的,所有记录
string strExcel ="select * from [sheet1$A4:C65535]";//sheet1为表名
从其它行只需改变AN中的N就可以了
也可以只取一列
select * from [sheet1$B1:B65535]
完整实现:
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/yyy.xls") + ";" + "Extended Properties=Excel 8.0;";//HDR=Yes,红色为EXCEL文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$A3:C65535]"; //EXCEL文件里的表
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
return ds;