ASP.NET从Excel文件导入数据到数据库(笔记)

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

页面ＨＴＭＬ代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_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>Excel 数据导入 数据库</title>

    <script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>

    <script type="text/javascript">
        $(function(){ 
            $("#"+"<%=btnImport.ClientID %>").click(function(){
                var fileName = $("#"+"<%=excelFile.ClientID %>").val();
                if(fileName==""){
                    alert("请选择Excel文件！");
                    return false;
                }
                else{
                    var extension = fileName.substring(fileName.lastIndexOf('.')+1);
                    if(extension!="xlsx"&&extension!="xls"){
                        alert("上传的文件不是Excel文件，请重试！");
                        return false;
                    }
                }
                return true;    
            });
        });
    </script>

</head>
<body>
    <form >
        <div>
            <fieldset>
                <legend>数据导入：</legend>
                <table>
                    <tr>
                        <td style="width: 182px">
                            数据Excel：</td>
                        <td>
                            <asp:FileUpload ID="excelFile" runat="server" /></td>
                    </tr>
                    <tr>
                        <td colspan="2">
                            <asp:Button ID="btnImport" runat="server" Text="导入" OnClick="btnImport_Click" />
                            <input ' /></td>
                    </tr>
                </table>
            </fieldset>
        </div>
        <div >
            <fieldset>
                <legend>错误信息:</legend>
                <textarea ></textarea>
            </fieldset>
        </div>
        <div >
            <fieldset>
                <legend>导入确认:</legend>
                <asp:GridView ID="GVConfirm" runat="server" CssClass="grid">
                </asp:GridView>
                <div >
                    <asp:Button ID="btnConfirm" runat="server" Text="确定" OnClick="btnConfirm_Click" />
                    <input ' />
                </div>
            </fieldset>
        </div>
    </form>
</body>
</html>

Ｃ# 代码：

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.initPage();
    }
    /// <summary>
    /// 通过ViewState保存一个DataTable.用来在现实之后添加到数据库
    /// </summary>
    public DataTable SamplDataTable
    {
        get
        {
            if (ViewState["SamplDataTable"] == null)
                return new DataTable();
            return (DataTable)ViewState["SamplDataTable"];
        }
        set
        {
            ViewState["SamplDataTable"] = value;
        }
    }
    private void initPage()
    {
        this.errorDiv.Visible = false;
        this.confrimDiv.Visible = false;
    }
    protected void btnImport_Click(object sender, EventArgs e)
    {
        ///取得文件名
        string fileName = this.excelFile.FileName;
        //绝对路径
        string path = "~/Excel/" + fileName;
        path = Server.MapPath(path);
        try
        {
            //暂时保存在服务上
            this.excelFile.SaveAs(path);
            ///读取指定路径 Excel 文件中的内容转换成DataTable
            DataTable excelDT = ImportToDataSet(path);

            //声明错误信息字符串
            StringBuilder errorBuiler = new StringBuilder();
            ///检查 存储在内存中的 Excel DataTable
            if (Validate(excelDT, errorBuiler))
            {
                //如果检查通过,使用ViewState保存DataSet中数据,在保存到数据库的时候用到
                SamplDataTable = excelDT;
                //页面GridView数据绑定,用来显示从Excel读取出来的数据供用户确认
                this.GVConfirm.DataSource = SamplDataTable;
                this.GVConfirm.DataBind();
                this.confrimDiv.Visible = true;
                this.buttonDiv.Visible = true;
            }
            else
            {//如果检测没有通过,输出相关错误信息
                this.errorDiv.Visible = true;
                this.errorArea.Value = errorBuiler.ToString();
            }
        }
        catch (Exception ex)
        {
            this.errorDiv.Visible = true;
            this.errorArea.Value = ex.Message;
        }
        finally
        {
            //关闭,删除 文件
            if (File.Exists(path))
                File.Delete(path);
        }
    }
    /// <summary>
    /// 验证指定的Excel规则(列数)
    /// </summary>
    /// <param name="excelDT"></param>
    /// <param name="errorBuiler"></param>
    /// <returns></returns>
    public bool Validate(DataTable excelDT, StringBuilder errorBuiler)
    {
        bool result = true;
        if (excelDT.Columns.Count != 5)//假设是5列
        {
            result = false;
            int difference = excelDT.Columns.Count - 5;
            if (difference > 0)
                errorBuiler.AppendLine("要导入的Excel多" + difference.ToString() + "列");
            else
                errorBuiler.AppendLine("要导入的Excel少" + (-difference).ToString() + "列");
        }
        else
        {
            //foreach (DataRow row in excelDT.Rows)
            //{
            //    if (Exists(row[0].ToString()))
            //    {
            //        result = false;
            //        errorBuiler.AppendLine("内容 " + "'" + row[2].ToString() + "'" + "已存在！");
            //    }
            //}
        }
        return result;
    }

    protected void btnConfirm_Click(object sender, EventArgs e)
    {
        ///将GridView中显示的数据(其实是保存在VIewState中)写入到数据库中
        if (AddDataTable(SamplDataTable, 0))
        {
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "insertSuccess", "<script>alert('导入成功！');window.location.href='Default.aspx';</script>");
        }
        else
        {
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "insertFailure", "<script>alert('导入失败！请重试');</script>");
        }
    }
    private bool AddDataTable(DataTable dt, int p)
    {
        bool result = true;
        string sql = BuilderInsertSql(dt, p);
        try
        {
            //执行sql语句 DbHelper.ExecuteSql();
            return true;
        }
        catch
        {
            result = false;
        }
        return result; ;
    }

    private string BuilderInsertSql(DataTable dt, int p)
    {
        //遍历DataTable拼接添加字符串
        return "";
    }

    private bool AddDataTable(DataTable SamplDataTable, object p, int p_3)
    {
        throw new Exception("The method or operation is not implemented.");
    }
    /// <summary>
    /// 读取指定路径的Excel内容到DataTable中
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    public DataTable ImportToDataSet(string path)
    {
        string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
        OleDbConnection conn = new OleDbConnection(strConn);
        try
        {
            DataTable dt = new DataTable();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            string strExcel = "select * from [Sheet1$]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
            adapter.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }
    }
}