Asp.net中Excel数据导入到SQL Server

来源:互联网 发布:java求某年某月的天数 编辑:程序博客网 时间:2024/03/28 22:46

1、首先在前台添加一个upfileload文件上传控件和一个button按钮,upfileload上传控件用于读取Excel文件!

2、GetExcelData()函数用于读取Excel文件并填写到一个DateSet中,由于微软对07以后的Excel的数据结构作了修改,所以针对07以前的版本和07及其以后的版本使用不同的连接串。

        string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;        string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
由于Excel文件可能无法读取到,在读取之前最好先将文件上传:

inputFile.SaveAs(Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName)));string filePath = Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName));

3、InsertDB()函数用于将存储Excel数据的DateSet中的数据导入到SQL Server中。

具体代码如下:

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.SqlClient;using System.Text;using System.Data.OleDb;public partial class _Default : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {    }    /// <summary>    /// 该方法实现从Excel中导出数据到DataSet中,其中filepath为Excel文件的绝对路径,sheetname为表示那个Excel表,此用Sheet1;    /// </summary>    /// <param name="ds">ds</param>    private void InsertDB(DataSet ds)    {        string CONNECTION_STRING = @"Data Source=(local); Initial Catalog=test;Integrated Security=false;User ID=sa;Password=sa;";        SqlConnection _con = new SqlConnection(CONNECTION_STRING);        SqlCommand cmd = new SqlCommand();        cmd.Connection = _con;        StringBuilder sb = new StringBuilder();        if (ds.Tables[0].Rows.Count > 0)        {            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)            {                sb.Append(" insert into ynzt6(time,cul1,cul2,cul3,cul4) values('");                sb.Append(ds.Tables[0].Rows[i].ItemArray[0].ToString() + "','");                sb.Append(ds.Tables[0].Rows[i].ItemArray[1].ToString() + "','");                sb.Append(ds.Tables[0].Rows[i].ItemArray[2].ToString() + "','");                sb.Append(ds.Tables[0].Rows[i].ItemArray[3].ToString() + "','");                sb.Append(ds.Tables[0].Rows[i].ItemArray[4].ToString() + "' ) ");                cmd.CommandText = sb.ToString();            }        }        _con.Open();        int j = cmd.ExecuteNonQuery();        _con.Close();        if (j > 0)        {            lblMessage.Text = "Insert into PDB_YN_BEF table Sucessfully!";        }    }    /// <summary>    /// get data source from excel file    /// </summary>    /// <returns>dataset ds</returns>    private DataSet GetExcelData()    {        DataSet ds = new DataSet();        inputFile.SaveAs(Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName)));        string filePath = Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName));        string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;        string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";        string queryStr = "SELECT * FROM [Sheet1$]";        OleDbConnection conn03 = new OleDbConnection(connStr03);        OleDbConnection conn07 = new OleDbConnection(connStr07);        if (inputFile.HasFile)        {            string fileExt = System.IO.Path.GetExtension(inputFile.FileName);            if (fileExt == ".xls")            {                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);                myAdapter.Fill(ds);            }            else if (fileExt == ".xlsx")            {                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn07);                myAdapter.Fill(ds);            }            else            {                lblMessage.Text = "The file is not exist!";            }        }        return ds;    }    protected void btnUpload_Click1(object sender, EventArgs e)    {        DataSet ds = GetExcelData();        InsertDB(ds);    }}


0 0
原创粉丝点击