asp.net 操作Excel表資料匯入到SQL Server資料庫

暖楓無敵發表於2011-11-18
ASP.NETExcelSQLServer資料庫

程式碼全部貼出，主要是Excel表中的資料要和資料庫中的資料型別要匹配。

這裡Excel表中的欄位是：

姓名、性別、班級、學號、初始密碼

SQL Server表tb_Users中的欄位是;

RealName、 Sex、InClass、Question、Answer


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="TEST_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
    </div>
    </form>
</body>
</html>


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using USTC;
using System.Drawing;

public partial class TEST_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //獲取檔案路徑
        string filePath = this.FileUpload1.PostedFile.FileName;
        if (filePath != "")
        {
            if (filePath.Contains("xls"))//判斷檔案是否存在
            {
                InputExcel(filePath);
            }
            else
            {
                Response.Write("請檢查您選擇的檔案是否為Excel檔案！謝謝！");
            }
        }

        else
        {
            Response.Write("請先選擇匯入檔案後，再執行匯入！謝謝！");
        }
    }

    private void InputExcel(string pPath)
    {
         string conn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + pPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
        OleDbConnection oleCon = new OleDbConnection(conn);
        oleCon.Open();
        string Sql = "select * from [Sheet1$]";
        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
        DataSet ds = new DataSet();
        mycommand.Fill(ds, "[Sheet1$]");
        oleCon.Close();
        int count = ds.Tables["[Sheet1$]"].Rows.Count;
        for (int i = 0; i < count; i++)
        {
            string tRealName, tSex, tInClass, tQuestion, tAnswer;
            tRealName = ds.Tables["[Sheet1$]"].Rows[i]["姓名"].ToString().Trim();
            tSex = ds.Tables["[Sheet1$]"].Rows[i]["性別"].ToString().Trim();
            tInClass = ds.Tables["[Sheet1$]"].Rows[i]["班級"].ToString().Trim();
            tQuestion = ds.Tables["[Sheet1$]"].Rows[i]["學號"].ToString().Trim();
            tAnswer = ds.Tables["[Sheet1$]"].Rows[i]["初始密碼"].ToString().Trim();
            string excelsql = "insert into tb_Users(RealName, Sex, InClass,Question,Answer) values ('" + tRealName + "','" + tSex + "','" + tInClass + "','" + tQuestion + "','" + tAnswer + "')";
            try
            {
                //匯入到SQL Server中
                DM dm = new DM();
                dm.execsql(excelsql);
                Response.Write("<script language='javascript'>Alert('資料匯入成功!');window.location='Default.aspx'</script>");
            }
            catch(Exception)
            {
                Response.Write("<script language='javascript'>Alert('資料匯入失敗!');window.location='Default.aspx'</script>");
            }
        }
    }
}


相關文章