我写过的代码,不过Excel表是固定，你看看吧，应该有点帮助。
string strPath = File1.PostedFile.FileName;
if(strPath == "")
{
Response.Write("<script>alert('请先选择文件')</script>");
return;
}
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strPath + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);
try
{
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM[sheet1$]", objConn);
/*************************************************************
**********************测试读取单列****************************
OleDbDataReader dr = objCmdSelect.ExecuteReader();
while(dr.Read())
{
Response.Write("产品类别"+dr[2].ToString());
}
dr.Close();
***************************************************************/
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet dsolb = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(dsolb);
// Bind data to DataGrid control.
DataGrid1.DataSource = dsolb;
DataGrid1.DataBind();
}
catch(Exception ee)
{
Response.Write("<script>alert('请先关闭文件')</script>");
return;
}
finally
{
// Clean up objects.
objConn.Close();
}
//从配置文件中取得数据库连接
string confstring = ConfigurationSettings.AppSettings["SQLConnectionString"].ToString();
string stringzhang = Session["Zhangce"].ToString();
string stringYear = Session["Year2"].ToString();
sqlconn = confstring+stringzhang+"_"+stringYear;
SqlConnection cnn = new SqlConnection(sqlconn);
string sqlcommand="insert into Inventory(cInvCode,cInvName,cInvStd,cInvCCode,dSDate,bsale,bpurchase,bself,bComSume,bProducing,itaxRate,bInvQuality,bInvBatch,bInvEntrust,bInvoverstock,bfree1,bfree2,bInvType,cGroupCode ,cComUnitCode,dModifyDate) "+
"values( @number,@name,@interface,@type,@date,@bsale,@bpurchase,@bself,@bComsume,@bProducing,@itaxRate,@binvquality,@bInvBatch,@binventrust,@binvoverstock,@bfree1,@bfree2,@bInvtype,@cGroupcode,@cComUnitCode,@deModifyDate)";
SqlCommand cmd = new SqlCommand(sqlcommand,cnn);
#region 遍历sql表
cmd.Parameters.Add(new SqlParameter("@type",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@interface",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@number",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@date",SqlDbType.SmallDateTime,8,"yyyy-mm-dd"));//设定时间的格式
cmd.Parameters.Add(new SqlParameter("@bsale",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bpurchase",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bself",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bComsume",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bProducing",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@itaxRate",SqlDbType.Real));
cmd.Parameters.Add(new SqlParameter("@binvquality",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bInvBatch",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@binventrust",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@binvoverstock",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bfree1",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bfree2",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bInvtype",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@cGroupcode",SqlDbType.VarChar,10));
cmd.Parameters.Add(new SqlParameter("@cComUnitCode",SqlDbType.VarChar,10));
cmd.Parameters.Add(new SqlParameter("@deModifyDate",SqlDbType.DateTime,8,"yyyy-mm-dd"));
#endregion
#region 循环更新到数据库
int numcols = DataGrid1.Items.Count;
for(int i=0;i<=numcols-1;i++)
{
TableCell Name = DataGrid1.Items[i].Cells[0];
TableCell Interface = DataGrid1.Items[i].Cells[1];
TableCell Number = DataGrid1.Items[i].Cells[2];
cmd.Parameters["@type"].Value ="CCPP";
cmd.Parameters["@number"].Value = Number.Text.Trim();
cmd.Parameters["@name"].Value = Name.Text.Trim();
cmd.Parameters["@interface"].Value = Interface.Text.Trim();
cmd.Parameters["@date"].Value = DateTime.Now.ToShortDateString().ToString();
cmd.Parameters["@bsale"].Value = -1;
cmd.Parameters["@bpurchase"].Value = -1;
cmd.Parameters["@bself"].Value = -1;
cmd.Parameters["@bComsume"].Value = -1;
cmd.Parameters["@bProducing"].Value = -1;
cmd.Parameters["@itaxRate"].Value = 17;
cmd.Parameters["@binvquality"].Value = 0;
cmd.Parameters["@bInvBatch"].Value = 0;
cmd.Parameters["@binventrust"].Value = 0;
cmd.Parameters["@binvoverstock"].Value = 0;
cmd.Parameters["@bfree1"].Value = 0;
cmd.Parameters["@bfree2"].Value = 0;
cmd.Parameters["@bInvtype"].Value = 0;
cmd.Parameters["@cGroupcode"].Value = "SL";
cmd.Parameters["@cComUnitCode"].Value = "J";
cmd.Parameters["@deModifyDate"].Value = "2006-11-1";
cnn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ee)
{
Response.Write("<script>alert('错误：导入了相同的表或Excel表头位置错误，请改成产品名称、产品规格、产品编号的顺序！')</script>");
return;
}
finally
{
cnn.Close();
}
#endregion
