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

最後更新：2018-12-03 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

代碼全部貼出，主要是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>");            }        }    }}

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More