写了一段 excel读取数据的方法
各位高人请过目 看看小弟这个有什么问题没。
public static ConversionItem GetConversionItem(string oldEmployeeID)
{
ConversionItem conversionItem = new ConversionItem();
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\\Emila\\HK Conversion File.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
OleDbConnection objConn = new OleDbConnection(strConn);
string sql = "select * from [sheet1$] where oldEmployeeID=@oldEmployeeID";
//OleDbDataAdapter da = new OleDbDataAdapter(sql, objConn);
//objConn.Open();
//DataSet ds = new DataSet();
//da.Fill(ds);
//objConn.Close();
OleDbCommand objCmd = new OleDbCommand(sql, objConn);
OleDbParameter para = new OleDbParameter("@oldEmployeeID", oldEmployeeID);
objCmd.Parameters.Add(para); objConn.Open();
try
{
using (OleDbDataReader reader = objCmd.ExecuteReader())
{
if (reader.Read())
{
conversionItem.EmployeeID = Convert.ToString(reader["Employee ID"]);
conversionItem.Name = Convert.ToString(reader["Name"]);
conversionItem.EmployeeStatus = Convert.ToString(reader["Employee Status"]);
conversionItem.DepartmentName = Convert.ToString(reader["Department Name"]);
conversionItem.Unit = Convert.ToString(reader["Unit"]);
conversionItem.OldEmployeeID = Convert.ToString(reader["Old Employee ID"]);
conversionItem.Folder = Convert.ToString(reader["Folder"]); return conversionItem;
}
else
{
throw new PException("Old Employee ID {0} does not exist.", oldEmployeeID);
}
}
}
finally
{
objConn.Close();
}
}
各位高人请过目 看看小弟这个有什么问题没。
public static ConversionItem GetConversionItem(string oldEmployeeID)
{
ConversionItem conversionItem = new ConversionItem();
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\\Emila\\HK Conversion File.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
OleDbConnection objConn = new OleDbConnection(strConn);
string sql = "select * from [sheet1$] where oldEmployeeID=@oldEmployeeID";
//OleDbDataAdapter da = new OleDbDataAdapter(sql, objConn);
//objConn.Open();
//DataSet ds = new DataSet();
//da.Fill(ds);
//objConn.Close();
OleDbCommand objCmd = new OleDbCommand(sql, objConn);
OleDbParameter para = new OleDbParameter("@oldEmployeeID", oldEmployeeID);
objCmd.Parameters.Add(para); objConn.Open();
try
{
using (OleDbDataReader reader = objCmd.ExecuteReader())
{
if (reader.Read())
{
conversionItem.EmployeeID = Convert.ToString(reader["Employee ID"]);
conversionItem.Name = Convert.ToString(reader["Name"]);
conversionItem.EmployeeStatus = Convert.ToString(reader["Employee Status"]);
conversionItem.DepartmentName = Convert.ToString(reader["Department Name"]);
conversionItem.Unit = Convert.ToString(reader["Unit"]);
conversionItem.OldEmployeeID = Convert.ToString(reader["Old Employee ID"]);
conversionItem.Folder = Convert.ToString(reader["Folder"]); return conversionItem;
}
else
{
throw new PException("Old Employee ID {0} does not exist.", oldEmployeeID);
}
}
}
finally
{
objConn.Close();
}
}
下边是我用过得一个程序
public DataSet ImportCSV(string filePath)
{
string fileName = System.IO.Path.GetFileName(filePath);
string path = System.IO.Path.GetDirectoryName(filePath);
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
DataSet ds = null;
try
{
using (OleDbConnection conn = new OleDbConnection(strCon))
{
conn.Open();
OleDbDataAdapter adpt = new OleDbDataAdapter("select * from " + fileName, conn);
adpt.Fill(ds);
}
}
catch(OleDbException ex)
{
log.Error("DataImportFrm:ImportCSV occurred error " + ex.Message);
} return ds;
}
/// <summary>
/// 导出EXCEL表中的数据到 myDataSet
/// </summary>
public static DataSet GetConnect(string path)
{
//创建一个数据链接
// string strCon = string.Format(" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties=Excel 8.0", path);
string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= {0} ;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';", path);
OleDbConnection myConn = new OleDbConnection(strCon);
//条件查询EXCEL表
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//创建一个 DataSet对象
DataSet myDataSet;
myDataSet = new DataSet();
//得到自己的DataSet对象
myCommand.Fill(myDataSet, "[Sheet1$]");
//关闭此数据链接
myConn.Close(); myConn.Dispose(); myCommand.Dispose();
return myDataSet;
}
http://topic.csdn.net/u/20111101/15/9a2033d1-a7e5-4e32-aed7-0aef4ad4fec1.html你先按别人跟你提供的办法,一步步调试,看看有问题没有?
你老这样问题,自己不动手,也不会有结果啊
我调试了阿没有发现错误。。之前 因为是excel的列名有空格 查询时少了个空格 出错 因为没发现一直报错。。后来解决了。。现在的问题是查得到但是经理说 这么写不能确定 employeeID不能确定。。我是不明白到底我哪里不能确定了。。