public DataSet getExcelData(string strPath,string strSheetName,string strTableName)
{ //从Excel中读取数据到DataSet中
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=/"Excel 8.0;IMEX=1;HDR=YES/";" +
"data source=" + strPath;
string sql = "";
sql = sql + " select JAN ";
sql = sql + ",商品名 ";
sql = sql + ",部門 ";
sql = sql + ",状態 ";
sql = sql + ",棚卸単位 ";
sql = sql + ",棚卸単位入数 ";
sql = sql + ",棚卸原単 ";
sql = sql + ",棚卸売単 ";
sql = sql + "FROM [" + strSheetName + "$] ";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, strConn);
da.Fill(ds,strTableName);
return ds;
}
catch(Exception ex)
{
throw ex;
}
}
public void setExcelToDatabase(DataSet ds,string strTableName)
{//将从Excel中读取的DataSet导入到数据库中
try
{
if(ds.Tables[strTableName].Rows.Count>0)
{
string strConn=System.Configuration.ConfigurationSettings.AppSettings["strCon"];
string sql="";
sql = sql + "SELECT JAN ";
sql = sql + ",ProductName ";
sql = sql + ",SectionCode ";
sql = sql + ",Status ";
sql = sql + ",StockUnit ";
sql = sql + ",StockCase ";
sql = sql + ",StockPrice ";
sql = sql + ",StockRetail ";
sql = sql + "FROM tableTest";
SqlConnection oCon=new SqlConnection(strConn);
SqlDataAdapter oDataAdapter=new SqlDataAdapter();
oDataAdapter.SelectCommand=new SqlCommand(sql,oCon);
SqlCommandBuilder oCb=new SqlCommandBuilder(oDataAdapter);
DataSet oDs=new DataSet();
oCon.Open();
oDataAdapter.Fill(oDs,strTableName);
int iRow=ds.Tables[strTableName].Rows.Count;
for(int i=0;i<iRow;i++)
{
DataRow currentDR=oDs.Tables[strTableName].NewRow();
currentDR["JAN"]=ds.Tables[strTableName].Rows[i][0];
currentDR["ProductName"]=ds.Tables[strTableName].Rows[i][1];
currentDR["SectionCode"]=ds.Tables[strTableName].Rows[i][2];
currentDR["Status"]=ds.Tables[strTableName].Rows[i][3];
currentDR["StockUnit"]=ds.Tables[strTableName].Rows[i][4];
currentDR["StockCase"]=ds.Tables[strTableName].Rows[i][5];
currentDR["StockPrice"]=ds.Tables[strTableName].Rows[i][6];
currentDR["StockRetail"]=ds.Tables[strTableName].Rows[i][7];
oDs.Tables[strTableName].Rows.Add(currentDR);
}
//結構ですか??
// oDs =ds;
//
oDataAdapter.Update(oDs,strTableName);
oCon.Close();
}
}
catch(Exception ex)
{
throw ex;
}
}