将Excel表内容导进SQL2008中

本文章的代码装载的其它的文章，但是我还想在此仔细的给大家讲了过程，如果大家在做的过程中遇到这样的问题，也能方便的解决。

 

1.现在附上代码

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

namespace insert
{
    public partial class _Default : System.Web.UI.Page
    {
        string strConn = "Data Source=B02B53FA1901440;DataBase=OA_DB;Uid=sa;Pwd=123456";
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        public DataSet ExcelDs(string filnameul, string table)
        {
            string strConn = "Provider=Microsoft.Jet.Oledb.4.0;" + "Data source=" + filnameul + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter("Select * from [Sheet1$]", conn);
            odda.Fill(ds, table);
            return ds;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile == false)
        {
            Response.Write("<script>alert('请选择Excel文件')</script>");
            return;
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;
        }
        SqlConnection cn = new SqlConnection(strConn);
        cn.Open();//
        string filename = DateTime.Now.ToString("yyyymmddhhMMss") +FileUpload1.FileName;//(("~\\Uploads\\"))
        string savePath = Server.MapPath(filename);
        FileUpload1.SaveAs(savePath);
        DataSet ds = ExcelDs(savePath, filename);//调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();//定义个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空,无数据!')</script>");
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {

                string name = dr[i]["学员名称"].ToString();
                string depater = dr[i]["部门名称"].ToString();
                string project = dr[i]["课程名称"].ToString();
                string examName = dr[i]["试卷名称"].ToString();
                string examSorce = dr[i]["考试成绩"].ToString();
                string examSorces = dr[i]["补考成绩"].ToString();
                string Sorces = dr[i]["总分"].ToString();
                string pass = dr[i]["是否通过"].ToString();
                string sqlcheck="select count(*) from Messages where Name='"+name+"'and Depater='"+depater+"' and Poject='"+project+"'and ExamName='"+examName+"' and ExamSorce='"+examSorce+"' and ExamSorces='"+examSorces+"' and Sorces='"+Sorces+"' and Pass='"+pass+"'";//检查用户是否存在
                SqlCommand sqlcmd=new SqlCommand(sqlcheck,cn);
                int count=Convert.ToInt32(sqlcmd.ExecuteScalar());
                if (count < 1)
                {
                    string insert = "insert into Messages(Name,Depater,Poject,ExamName,ExamSorce,ExamSorces,Sorces,Pass)values('" + name + "','" + depater + "','" + project + "','" + examName + "','" + examSorce + "','" + examSorces + "','" + Sorces + "','" + pass + "')";
                    SqlCommand cmd = new SqlCommand(insert, cn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (MembershipCreateUserException ex)
                    {
                        Response.Write("<script>alet('导入的内容:" + ex.Message + "')</script>");
                    }
                }
                else
                {
                    Response.Write("<script>alert('内容重复!禁止导入');</script>");
                    continue;
                }

            }
            Response.Write("<script>alert('Excel导入成功!')</script>");
        }
        cn.Close();

    }        
    }
}

只要按照此程序，将Excel表导进SQL2008中，是没有错误的。

1.如果在上传过程中遇到“xxxx.xls”访问被拒绝，那就将此Excel文件的属性---安全----选择Everyone用户的权限设置成“所有

 现在附上截图

1.Excel表

2.数据库表