ASP.Net Excel内容导入数据库

版权声明:转载原创文章请以超链接形式请注明原文章出处,尊重作者,尊重原创!


恰饭广告




实现效果:

实现效果

Excel表内容:

excel表内容

SQL Server建表

CREATE TABLE stuInfo(
stuNO int not null,
stuName varchar(20) not null,
)

注意:前台页面分别拖拽一个FileUpload、Button、Label控件

Default.aspx.cs代码:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 : System.Web.UI.Page
{
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        string query = null;
        string connString = "";
        OleDbConnection conn = new OleDbConnection();
        OleDbCommand cmd = new OleDbCommand();
        OleDbDataAdapter da = new OleDbDataAdapter();
        string strFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
        //string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
        string strFileType = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
        if (strFileType == ".xls" || strFileType == ".xlsx")
        {
            FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
        }
        else
        {
            return;
        }
        string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
        if (strFileType.Trim() == ".xls")
        {
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        else if (strFileType.Trim() == ".xlsx")
        {
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }
        query = "SELECT * FROM [Sheet1$]";
        conn = new OleDbConnection(connString);
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        try
        {
            cmd = new OleDbCommand(query, conn);
            da = new OleDbDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            da.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        SqlConnection conn = new SqlConnection(connStr);
        string sqlStr = "select * from stuInfo";
        SqlDataAdapter sda = new SqlDataAdapter(sqlStr, conn);
        try
        {
            conn.Open();
            DataTable dt2 = new DataTable();
            sda.Fill(dt2);
            SqlBulkCopy bulk = new SqlBulkCopy(conn);
            bulk.BatchSize = 100;
            bulk.BulkCopyTimeout = 60;
            bulk.DestinationTableName = "stuInfo";
            bulk.BatchSize = dt.Rows.Count;
            for (int i=0;i< bulk.BatchSize; i++)
            {
                for (int j = 0; j < dt2.Rows.Count; j++)
                {
                    if (dt.Rows[i]["学号"].ToString() == dt2.Rows[j]["stuNO"].ToString())
                    {
                        Label1.Text = dt.Rows[i]["学号"].ToString() + "已存在";
                        return;
                    }
                }
            }
            bulk.ColumnMappings.Add("学号", "stuNO");
            bulk.ColumnMappings.Add("姓名", "stuName");
            bulk.WriteToServer(dt);
            Label1.Text = "导入成功";
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
}

原文链接:https://www.idaobin.com/archives/1223.html

让我恰个饭吧.ヘ( ̄ω ̄ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告

发表评论

邮箱地址不会被公开。 必填项已用*标注

15 − 9 =