Excel数据导入数据库

第一部分：将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;
    }

}