将Excel文件导入到Asp.net后台数据库中

实例1（自己设计）：

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.SqlClient;
using  System.Data.OleDb;

public   partial   class  exam_ExcelImport : System.Web.UI.Page
... {
    baseclass bc = new baseclass();

    protected OleDbConnection olecon;
    protected OleDbCommand olecmd;
    protected OleDbDataReader oledr;

    protected SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbexam"].ToString());
    protected SqlCommand sqlcmd;

    protected void Page_Load(object sender, EventArgs e)
    ...{
        Button1.Attributes.Add("onclick", "javascript:return confirm('请在导入前保证导入的数据格式正确！')");
    }
    protected void Button1_Click(object sender, EventArgs e)
    ...{
        if (File1.Value != "")
        ...{
            /**//*连接到 Excel
             * Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中，Extended Properties 关键字设置 Excel 特定的属性。
             * “HDR=Yes;”指示第一行中包含列名，而不是数据
             * “IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。
             * Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';Persist Security Info=false
            */
            try
            ...{
                olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + File1.Value + "';Extended Properties='Excel 5.0;HDR=Yes;IMEX=1';Persist Security Info=false");

                olecon.Open();
                olecmd = new OleDbCommand("select * from [Sheet1$]", olecon);
                oledr = olecmd.ExecuteReader();
                sqlcon.Open();
                sqlcmd = new SqlCommand("insert into question values(@subjectname,@professionname,@examname,@type,@optionA,@optionB,@optionC,@optionD,@que_answer,@note)", sqlcon);
                sqlcmd.Parameters.Add("@subjectname", SqlDbType.VarChar, 300);
                sqlcmd.Parameters.Add("@professionname", SqlDbType.VarChar, 20);
                sqlcmd.Parameters.Add("@examname", SqlDbType.VarChar, 20);
                sqlcmd.Parameters.Add("@type", SqlDbType.Char, 10);
                sqlcmd.Parameters.Add("@optionA", SqlDbType.VarChar, 100);
                sqlcmd.Parameters.Add("@optionB", SqlDbType.VarChar, 100);
                sqlcmd.Parameters.Add("@optionC", SqlDbType.VarChar, 100);
                sqlcmd.Parameters.Add("@optionD", SqlDbType.VarChar, 100);
                sqlcmd.Parameters.Add("@que_answer", SqlDbType.Char, 10);
                sqlcmd.Parameters.Add("@note", SqlDbType.VarChar, 50);

                while (oledr.Read())
                ...{
                    sqlcmd.Parameters[0].Value = oledr[0].ToString().Trim();
                    sqlcmd.Parameters[1].Value = oledr[1].ToString().Trim();
                    sqlcmd.Parameters[2].Value = oledr[2].ToString().Trim();
                    sqlcmd.Parameters[3].Value = oledr[3].ToString().Trim();
                    sqlcmd.Parameters[4].Value = oledr[4].ToString().Trim();
                    sqlcmd.Parameters[5].Value = oledr[5].ToString().Trim();
                    sqlcmd.Parameters[6].Value = oledr[6].ToString().Trim();
                    sqlcmd.Parameters[7].Value = oledr[7].ToString().Trim();
                    sqlcmd.Parameters[8].Value = oledr[8].ToString().Trim();
                    sqlcmd.Parameters[9].Value = oledr[9].ToString().Trim();
                    
                    sqlcmd.ExecuteNonQuery();
                }                
            }
            catch(Exception ex)
            ...{
                Response.Write(bc.messagbox(ex.Message));
            }
            finally
            ...{
                olecon.Close();
                sqlcon.Close();
            }
        }
    }
}

实例2：

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.SqlClient;
using  System.Data.OleDb;
public   partial   class  _Default : System.Web.UI.Page
... {
    protected SqlConnection sqlcon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"]);
    protected SqlCommand sqlcom;
    protected OleDbConnection olecon;
    protected OleDbCommand olecom;
    protected OleDbDataReader olereader;
    protected void Page_Load(object sender, EventArgs e)
    ...{
        this.Button1.Attributes.Add("onclick","javascript:return confirm('导入的信息字段一定要符合要求,否则导入会出现错误!确定要导入吗?')");
        if (!this.IsPostBack)
        ...{
            if (Session["UserText"] == null)
            ...{
                Server.Transfer("index.aspx");
            }
            this.Label1.Text = "请选择要导入的题库类型...";
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    ...{
        if (this.file1.Value != "")
        ...{
            olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + this.file1.Value + "';Extended Properties=Excel 5.0;Persist Security Info=false");
            try
            ...{
                olecon.Open();
                olecom = new OleDbCommand("select * from [sheet1$]", olecon);
                olereader = olecom.ExecuteReader();
                while (olereader.Read())
                ...{
                    switch (this.radiobuttonlist1.SelectedIndex)
                    ...{
                        case 0:
                            sqlcon.Open();
                            sqlcom = new SqlCommand("Insert into 选择题表 values(@id,@name,@style,@a,@b,@c,@d,@result,@yesno)", sqlcon);
                            sqlcom.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(olereader[0].ToString().Trim());
                            sqlcom.Parameters.Add("@name", SqlDbType.VarChar).Value = olereader[1].ToString().Trim();
                            sqlcom.Parameters.Add("@style", SqlDbType.VarChar).Value = olereader[2].ToString().Trim();
                            sqlcom.Parameters.Add("@a", SqlDbType.VarChar).Value = olereader[3].ToString().Trim();
                            sqlcom.Parameters.Add("@b", SqlDbType.VarChar).Value = olereader[4].ToString().Trim();
                            sqlcom.Parameters.Add("@c", SqlDbType.VarChar).Value = olereader[5].ToString().Trim();
                            sqlcom.Parameters.Add("@d", SqlDbType.VarChar).Value = olereader[6].ToString().Trim();
                            sqlcom.Parameters.Add("@result", SqlDbType.VarChar).Value = olereader[7].ToString().Trim();
                            sqlcom.Parameters.Add("@yesno", SqlDbType.Bit).Value = byte.Parse(olereader[8].ToString().Trim());
                            sqlcom.ExecuteNonQuery();
                            sqlcon.Close();
                            break;
                        case 1:
                            sqlcon.Open();
                            sqlcom = new SqlCommand("Insert into 判断题表 values(@id,@style,@name,@result,@yesno)", sqlcon);
                            sqlcom.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(olereader[0].ToString().Trim());
                            sqlcom.Parameters.Add("@style", SqlDbType.VarChar).Value = olereader[1].ToString().Trim();
                            sqlcom.Parameters.Add("@name", SqlDbType.VarChar).Value = olereader[2].ToString().Trim();
                            sqlcom.Parameters.Add("@result", SqlDbType.VarChar).Value = byte.Parse(olereader[3].ToString().Trim());
                            sqlcom.Parameters.Add("@yesno", SqlDbType.Bit).Value = byte.Parse(olereader[4].ToString().Trim());
                            sqlcom.ExecuteNonQuery();
                            sqlcon.Close();
                            break;
                        default: Response.Write("<script>alert('请选择要导入的题库类型...')</script>"); break;
                    }
                }
                olereader.Close();
                olecon.Close();
                this.Label1.Text = "成功导入数据!";
            }
            catch
            ...{
                olecon.Close();
                Response.Write("<script>alert('导入数据失败!')</script>");
            }
        }
        else
        ...{
            Response.Write("<script>alert('请选择要导入的文件')</script>");
            return;
        }
    }
    protected void radiobuttonlist1_SelectedIndexChanged(object sender, EventArgs e)
    ...{
        switch (this.radiobuttonlist1.SelectedIndex)
        ...{
            case 0: this.Label1.Text = "导入字段:题目ID,题目,类型,答案A,答案B,答案C,答案D,结果,是否为必选题(1或0)"; break;
            case 1: this.Label1.Text = "导入字段:题目ID,题型,内容,结果,是否为必选题(1或0)"; break;
            default: this.Label1.Text = "请选择要导入的题库类型..."; break;
        }

    }
}