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

 

前台设计界面如下：

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

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

/// 
        /// 上传导入按钮点击        /// 
        ///         ///         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...页面属性...        /// 
        /// 数据库连接字符串//web.config文件中数据库的联接串name        /// 
        private static string SqlCon        {            get            {                return "MES-Conn";            }        }        #endregion...页面属性...        #region...方法...        /// 
        /// 唯一需要注意的是，如果目标机器的操作系统，是64位的话。        /// 项目需要 编译为 x86，而不是简单的使用默认的 Any CPU.        /// 
        ///         /// 
             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;        }        /// 
        /// 获取Excel数据        /// 
        ///         /// 
             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...事件...        /// 
        /// 页面加载        /// 
        ///         ///         protected void Page_Load(object sender, EventArgs e)        {        }        /// 
        /// 上传导入按钮点击        /// 
        ///         ///         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);            }        }        /// 
        /// 关闭按钮点击        /// 
        ///         ///         protected void btnClose_Click(object sender, EventArgs e)        {            //关闭页面            Response.Write("");        }        #endregion...事件...    }}