第一部分:将Excel保存到服务器上:
string strFileName, UploadedFileName;
DateTime mydatetime = new DateTime();
mydatetime = DateTime.Now;
strFileName = mydatetime.Year.ToString() + mydatetime.Month.ToString() + mydatetime.Day.ToString() + mydatetime.Hour.ToString() + mydatetime.Minute.ToString() + mydatetime.Second.ToString() + ".xls";
string path = HttpContext.Current.Server.MapPath("temp\\");
UploadedFileName = path + strFileName;
FileUpload1.SaveAs(UploadedFileName);
第二部分:读Excel数据到DataSet中
public DataSet open(string _filename)
{
// Response.Write(_filename);
string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _filename + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection con = new OleDbConnection();
con.ConnectionString = str;
con.Open();
OleDbCommand cmd = new OleDbCommand("select * from [sheet1$]", con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "sheet1$");
con.Close();
con.Open();
cmd = new OleDbCommand("select * from [sheet2$]", con);
da = new OleDbDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "sheet2$");
con.Close();
return ds;
}
第三部分:插入数据到DataSet中
//插入数据
DataSet mydataset = open(_filename);
string QYMC = QYMC_XanZe;
string EnterpriseID = QY_ID;
string Areanumber = QY_area;
string FZR = mydataset.Tables[0].Rows[7][3].ToString().Trim();
string LXR = mydataset.Tables[0].Rows[7][6].ToString().Trim();
string LXDH = mydataset.Tables[0].Rows[7][8].ToString().Trim();
string TBRDH = mydataset.Tables[0].Rows[17][8].ToString().Trim();
string TBR = mydataset.Tables[0].Rows[17][6].ToString().Trim();
string TBRQ = mydataset.Tables[0].Rows[4][8].ToString().Trim() + "-" + mydataset.Tables[0].Rows[4][10].ToString().Trim() + "-" + mydataset.Tables[0].Rows[4][12].ToString().Trim();
string CZY = User.Identity.Name.ToString().Trim();
string CZRQ = DateTime.Now.ToString();
string XSE07 = mydataset.Tables[0].Rows[8][3].ToString().Trim();
string XSE07SN = mydataset.Tables[0].Rows[8][8].ToString().Trim();
string XSE08 = mydataset.Tables[0].Rows[9][3].ToString().Trim();
string XSE08SN = mydataset.Tables[0].Rows[9][8].ToString().Trim();
string newID;
newID = "";
string sql = "insert into AY1 (EnterpriseName, EnterpriseID, AreaNumber, FuZR, LianXR, LianXDH, TianBRQ,TianBRXM, TianBDH, CZY, CZRQ, XiaoSE07, XiaoSE07SN, XiaoSE08, XiaoSE08SN) values ";
sql = sql + "('" + QYMC + "','" + EnterpriseID + "','" + Areanumber + "','" + FZR + "','" + LXR + "','" + LXDH + "','" + TBRQ + "','" + TBR + "','" + TBRDH + "','" + CZY + "','" + CZRQ + "','" + XSE07 + "','" + XSE07SN + "','" + XSE08 + "','" + XSE08SN + "')select @@identity";
SqlConnection myconn;
myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["BaseConnectionString"].ConnectionString);
SqlCommand mycommand = new SqlCommand(sql, myconn);
myconn.Open();
string ayid = mycommand.ExecuteScalar().ToString(); //取得新插入记录的ID值
myconn.Close();
//注意在需要得到插入记录ID时,sql语句(insert sql)select @@identity"和mycommand.ExecuteScalar().ToString()
附加说明:Excel导入数据,位置问题
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class Excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("aaa.xls") + "';Extended Properties=Excel 8.0";
//Response.Write(str);
DataSet mydataset = open(Server.MapPath("四季胖哥.xls"));
int i;
int j;
for (j = 0; j < 22; j++)
{
for (i = 0; i < 22; i++)
{
Response.Write(j.ToString() + "," + i.ToString() + ":" + mydataset.Tables["sheet2$"].Rows[j][i].ToString() + "<br>");
}
}
}
public DataSet open(string _filename)
{
string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _filename + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection con = new OleDbConnection();
con.ConnectionString = str;
con.Open();
OleDbCommand cmd = new OleDbCommand("select * from [sheet2$]", con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "sheet2$");
con.Close();
return ds;
}
}