用c#读取excel中的数据插入到数据库中

 

   上传选择的xls文件,根据得到路径,读取文件信息,插入到数据库中

 

using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text; protected void Page_Load(object sender, EventArgs e)
 {

       
 }
 public void ExcelToDS(string path)
    {

        string strSheetName = "sheet1";


        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";

        //Sql语句 
        string strExcel = "select * from  [" + strSheetName + "$] ";

         
        DataSet ds = new DataSet();

        //连接数据源 
        OleDbConnection conn = new OleDbConnection(strConn);

        conn.Open();

        //适配到数据源 
        OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
        adapter.Fill(ds, "data");

        conn.Close();

        GridView1.DataSource = ds.Tables["data"];
        GridView1.DataBind();


        Label1.Text = ds.Tables[0].Rows.Count.ToString();
        SqlAccess.Sqldatabase sql = new SqlAccess.Sqldatabase();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {

            sb.Append("insert into data values('" + ds.Tables[0].Rows[i]["companyname"] + "','" + ds.Tables[0].Rows[i]["telname"] + "','" + ds.Tables[0].Rows[i]["qytel"] + "','" + ds.Tables[0].Rows[i]["fax"] + "');");

        }
        sql.ExecuteNonQuery(CommandType.Text, sb.ToString());

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fileName = null;
        try
        {
            Boolean fileOK = false;
            String path = Server.MapPath("./doc/");
            if (FileUpload2.HasFile)
            {
                String fileExtension =
                    System.IO.Path.GetExtension(FileUpload2.FileName).ToLower();
                String[] allowedExtensions = 
                { ".xls" };
                for (int i = 0; i < allowedExtensions.Length; i++)
                {
                    if (fileExtension == allowedExtensions[i])
                    {
                        fileOK = true;
                    }
                }
            }

            if (fileOK)
            {
               fileName = "r_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + "_" + DateTime.Now.Millisecond +
                    System.IO.Path.GetExtension(FileUpload2.FileName).ToLower();
                if (File.Exists(path + fileName))
                {
                    Random rnd = new Random(10000);
                    fileName = fileName + rnd.Next();
                }

                FileUpload2.PostedFile.SaveAs(path
                    + fileName);
              

            }
            else
            {
                
            }
        }
        catch (Exception exp)
        {
        }
        ExcelToDS(Server.MapPath(".") + "\\doc\\"+fileName);

    }

  

   

  excel:

 

 

companyname	telname	qytel	fax
xxxx		xxx	xxxx
xxxx		xxxx	xxxx