Asp.net导入Excel数据文件

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

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

实现页面选择查找文件，并导入以如下结构为模板的Excel数据文件

前台设计界面如下：

在这里使用asp.net的FileUpload控件实现文件查找选择，前台代码如下：

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
    <base target="_self" />
</head>
<body style="height: 380px; width: 1000px; background-color: #B9D3EE;">
    <form id="form1" runat="server">
        <div style="margin-top: 50px; margin-left: 50px;">
            <div style="height: 38px">
                <asp:Label runat="server" Text="文件*：" Width="60px"></asp:Label>
                <asp:FileUpload ID="fulImport" runat="server" Height="20px" Width="450px" />
            </div>
            <div style="height: 68px; margin-top: 60px;">
                <div style="float: left; margin-left: 85px">
                    <asp:Button ID="btnImport" runat="server" Text="上传" Width="60px" Height="30px" OnClick="btnImport_Click" />
                </div>
                <div style="float: left; margin-left: 60px">
                    <asp:Button ID="btnClose" runat="server" Text="关闭" Width="60px" Height="30px" OnClick="btnClose_Click" />
                </div>
            </div>
        </div>
    </form>
</body>
</html>

选择文件后点击上传按钮触发后台点击事件。

      /// <summary>
        /// 上传导入按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //文件名
                string strFileName = fulImport.FileName;

                //验证是否选择了文件
                if ("" == strFileName.Trim())
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('请选择文件！');", true);
                    return;
                }

                //验证文件类型是不是Excel
                if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xlsx" && strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('文件类型错误！'", true);
                    return;
                }

                //获取上载文件内容
                string Attachment = "";//附件
                //string strFilePath = ConfigurationManager.AppSettings["tempFilePath"].ToString();
                string strFilePath = "./tem/";
                strFilePath = Server.MapPath(strFilePath);
                Attachment = fulImport.funString_FileUpLoadAttachmentABPath(strFileName, 50, strFilePath);

                string strPath = strFilePath + Attachment;
                fulImport.PostedFile.SaveAs(strPath);
                DataSet ds = GetExcelData(strPath);

                //判断文件内容是否为空
                if (ds == null)
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('这个文件没有数据！');", true);
                    return;
                }

                int i_count = 0;//计算行数
                IdioSoft.Common.Method.DbSQLAccess objDbSQLAccess = new IdioSoft.Common.Method.DbSQLAccess(SqlCon);
                //导入到数据库
                try
                {
                    int result = 0;
                    foreach (DataTable dt in ds.Tables)
                    {
                        try
                        {
                            string strRow = dt.Rows[0]["工厂"].ToString();//判断行是否存在
                        }
                        catch (Exception)
                        {
                            continue;
                        }

                        //行数存在,累加行数
                        i_count++;
                        string strFactory = "";
                        string strWorkshop = "";
                        string strQualityDoor = "";
                        string strFirstPartStructure = "";
                        string strSecondPartStructure = "";

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            strFactory = dt.Rows[i]["工厂"].ToString();
                            strWorkshop = dt.Rows[i]["车间"].ToString();
                            strQualityDoor = dt.Rows[i]["质量门"].ToString();
                            strFirstPartStructure = dt.Rows[i]["一级零部件结构"].ToString();
                            strSecondPartStructure = dt.Rows[i]["二级零部件结构"].ToString();

                            //判断行数据是否完整并给出提示
                            if (strFactory == "" || strWorkshop == "" || strQualityDoor == "" || strFirstPartStructure == "" || strSecondPartStructure == "")
                            {
                                int m = i + 1;
                                ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('第" + m + "行数据不完整！')", true);
                                return;
                            }
                            else//数据完整则插入数据库
                            {
                                string strSqlDelete = string.Format(@"
                                                                    DELETE FROM [Test].[dbo].[QMS_QualiryDoorAndParts]
                                                                            WHERE [Factory]=N'{0}' AND [Workshop]=N'{1}' AND [QualityDoor]=N'{2}' 
                                                                                    AND [FirstPartStructure]=N'{3}' AND [SecondPartStructure]=N'{4}'
                                                                    ", strFactory, strWorkshop, strQualityDoor, strFirstPartStructure, strSecondPartStructure);
                                objDbSQLAccess.funString_SQLExecuteScalar(strSqlDelete);
                                string strSqlInsert = string.Format(@"
                                                                    INSERT INTO [Test].[dbo].[QMS_QualiryDoorAndParts]
                                                                            ([Factory]
                                                                            ,[Workshop]
                                                                            ,[QualityDoor]
                                                                            ,[FirstPartStructure]
                                                                            ,[SecondPartStructure])
                                                                        VALUES
                                                                            (N'{0}'
                                                                            ,N'{1}'
                                                                            ,N'{2}'
                                                                            ,N'{3}'
                                                                            ,N'{4}')
                                                                            ",strFactory ,strWorkshop ,strQualityDoor,strFirstPartStructure, strSecondPartStructure);
                                result = objDbSQLAccess.funString_SQLExecuteScalar(strSqlInsert).funInt_StringToInt(0);
                            }
                        }
                    }
                    if (result == 0 && i_count > 0)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('导入成功。');", true);
                    }
                    else if (i_count == 0)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('EXEC中的列名不符合规则。');", true);
                    }
                }
                catch (Exception ex)
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('Fail:" + ex.Message + "');", true);
                }
            }
            catch (Exception exp)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('Fail:" + exp.Message + "');", true);
            }
        }

后台完整实现代码如下：

其中IdioSoft.Common.Method;是类似SQLHelper的一个引用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using IdioSoft.Common.Method;

namespace QualityDoorAndPartsCorrespondence
{
    public partial class QualityDoorAndPartsImport : System.Web.UI.Page
    {
        #region...页面属性...

        /// <summary>
        /// 数据库连接字符串//web.config文件中数据库的联接串name
        /// </summary>
        private static string SqlCon
        {
            get
            {
                return "MES-Conn";
            }
        }

        #endregion...页面属性...

        #region...方法...

        /// <summary>
        /// 唯一需要注意的是，如果目标机器的操作系统，是64位的话。
        /// 项目需要 编译为 x86，而不是简单的使用默认的 Any CPU.
        /// </summary>
        /// <param name="strExcelFileName"></param>
        /// <returns></returns>
        private string GetOleDbConnectionString(string strExcelFileName)
        {
            // Office 2007 以及 以下版本使用.
            string strJETConnString =
              String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strExcelFileName);
            // xlsx 扩展名 使用.
            string strASEConnXlsxString =
              String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"", strExcelFileName);
            // xls 扩展名 使用.
            string strACEConnXlsString =
              String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", strExcelFileName);
            //其他
            string strOtherConnXlsString =
              String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strExcelFileName);

            //尝试使用 ACE. 假如不发生错误的话，使用 ACE 驱动.
            try
            {
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strACEConnXlsString);
                cn.Open();
                cn.Close();
                // 使用 ACE
                return strACEConnXlsString;
            }
            catch (Exception)
            {
                // 启动 ACE 失败.
            }

            // 尝试使用 Jet. 假如不发生错误的话，使用 Jet 驱动.
            try
            {
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strJETConnString);
                cn.Open();
                cn.Close();
                // 使用 Jet
                return strJETConnString;
            }
            catch (Exception)
            {
                // 启动 Jet 失败.
            }

            // 尝试使用 Jet. 假如不发生错误的话，使用 Jet 驱动.
            try
            {
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strASEConnXlsxString);
                cn.Open();
                cn.Close();
                // 使用 Jet
                return strASEConnXlsxString;
            }
            catch (Exception)
            {
                // 启动 Jet 失败.
            }
            // 假如 ACE 与 JET 都失败了，默认使用 JET.
            return strOtherConnXlsString;
        }

        /// <summary>
        /// 获取Excel数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private DataSet GetExcelData(string strFilePath)
        {
            try
            {
                //获取连接字符串
                // @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;HDR=YES;";
                string strConn = GetOleDbConnectionString(strFilePath);

                DataSet ds = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    //打开连接
                    conn.Open();
                    System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    // 取得Excel工作簿中所有工作表  
                    System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    OleDbDataAdapter sqlada = new OleDbDataAdapter();

                    foreach (DataRow dr in schemaTable.Rows)
                    {
                        try
                        {
                            string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
                            if (strSql.Contains("$"))
                            {
                                OleDbCommand objCmd = new OleDbCommand(strSql, conn);
                                sqlada.SelectCommand = objCmd;
                                sqlada.Fill(ds, dr[2].ToString().Trim());
                            }
                        }
                        catch { }
                    }
                    //关闭连接
                    conn.Close();
                }
                return ds;
            }
            catch (Exception ex)
            {
                ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "err", "alert('" + ex.Message + "');", false);
                return null;
            }
        }



        #endregion...方法...

        #region...事件...

        /// <summary>
        /// 页面加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 上传导入按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //文件名
                string strFileName = fulImport.FileName;

                //验证是否选择了文件
                if ("" == strFileName.Trim())
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('请选择文件！');", true);
                    return;
                }

                //验证文件类型是不是Excel
                if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xlsx" && strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('文件类型错误！'", true);
                    return;
                }

                //获取上载文件内容
                string Attachment = "";//附件
                //string strFilePath = ConfigurationManager.AppSettings["tempFilePath"].ToString();
                string strFilePath = "./tem/";
                strFilePath = Server.MapPath(strFilePath);
                Attachment = fulImport.funString_FileUpLoadAttachmentABPath(strFileName, 50, strFilePath);

                string strPath = strFilePath + Attachment;
                fulImport.PostedFile.SaveAs(strPath);
                DataSet ds = GetExcelData(strPath);

                //判断文件内容是否为空
                if (ds == null)
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('这个文件没有数据！');", true);
                    return;
                }

                int i_count = 0;//计算行数
                IdioSoft.Common.Method.DbSQLAccess objDbSQLAccess = new IdioSoft.Common.Method.DbSQLAccess(SqlCon);
                //导入到数据库
                try
                {
                    int result = 0;
                    foreach (DataTable dt in ds.Tables)
                    {
                        try
                        {
                            string strRow = dt.Rows[0]["工厂"].ToString();//判断行是否存在
                        }
                        catch (Exception)
                        {
                            continue;
                        }

                        //行数存在,累加行数
                        i_count++;
                        string strFactory = "";
                        string strWorkshop = "";
                        string strQualityDoor = "";
                        string strFirstPartStructure = "";
                        string strSecondPartStructure = "";

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            strFactory = dt.Rows[i]["工厂"].ToString();
                            strWorkshop = dt.Rows[i]["车间"].ToString();
                            strQualityDoor = dt.Rows[i]["质量门"].ToString();
                            strFirstPartStructure = dt.Rows[i]["一级零部件结构"].ToString();
                            strSecondPartStructure = dt.Rows[i]["二级零部件结构"].ToString();

                            //判断行数据是否完整并给出提示
                            if (strFactory == "" || strWorkshop == "" || strQualityDoor == "" || strFirstPartStructure == "" || strSecondPartStructure == "")
                            {
                                int m = i + 1;
                                ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('第" + m + "行数据不完整！')", true);
                                return;
                            }
                            else//数据完整则插入数据库
                            {
                                string strSqlDelete = string.Format(@"
                                                                    DELETE FROM [Test].[dbo].[QMS_QualiryDoorAndParts]
                                                                            WHERE [Factory]=N'{0}' AND [Workshop]=N'{1}' AND [QualityDoor]=N'{2}' 
                                                                                    AND [FirstPartStructure]=N'{3}' AND [SecondPartStructure]=N'{4}'
                                                                    ", strFactory, strWorkshop, strQualityDoor, strFirstPartStructure, strSecondPartStructure);
                                objDbSQLAccess.funString_SQLExecuteScalar(strSqlDelete);
                                string strSqlInsert = string.Format(@"
                                                                    INSERT INTO [Test].[dbo].[QMS_QualiryDoorAndParts]
                                                                            ([Factory]
                                                                            ,[Workshop]
                                                                            ,[QualityDoor]
                                                                            ,[FirstPartStructure]
                                                                            ,[SecondPartStructure])
                                                                        VALUES
                                                                            (N'{0}'
                                                                            ,N'{1}'
                                                                            ,N'{2}'
                                                                            ,N'{3}'
                                                                            ,N'{4}')
                                                                            ",strFactory ,strWorkshop ,strQualityDoor,strFirstPartStructure, strSecondPartStructure);
                                result = objDbSQLAccess.funString_SQLExecuteScalar(strSqlInsert).funInt_StringToInt(0);
                            }
                        }
                    }
                    if (result == 0 && i_count > 0)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('导入成功。');", true);
                    }
                    else if (i_count == 0)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('EXEC中的列名不符合规则。');", true);
                    }
                }
                catch (Exception ex)
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('Fail:" + ex.Message + "');", true);
                }
            }
            catch (Exception exp)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "JsError", "alert('Fail:" + exp.Message + "');", true);
            }
        }

        /// <summary>
        /// 关闭按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnClose_Click(object sender, EventArgs e)
        {
            //关闭页面
            Response.Write("<script>window.opener=null;window.close();</script>");
        }

        #endregion...事件...

    }
}