Excel导入数据库过程中遇到的问题

这次的excel与以往的不同，首先根据excel中的中文名称找到数据表中的列属性的说明属性，若一直，则excel中的列对应数据表的列；其次excel第一行是备注行，第二行是列名，后面才是数据行。

这样处理的好处是，以后客户更改excel模板，改变导入的信息，系统不需变更代码，比较灵活。

第一步:

从数据库中找出数据表列名与说明属性的对应关系：

private DataTable dtNameMapping;        public DataTable DtNameMapping        {            get            {                if (dtNameMapping == null)                {                    dtNameMapping = new DataTable();                    if (!string.IsNullOrEmpty(tableName))                    {    //用函数fn_listextendedproperty找出列名与说明属性的对应关系                        string sql = string.Format(@"SELECT cast(objname as varchar(64)) objname ,cast(value as varchar(64)) value                               FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default)", tableName);                        dtNameMapping = KPMSDatabase.KPMSDB.ExecuteDataSet(CommandType.Text, sql).Tables[0];                    }                }                return dtNameMapping;            }        }

 

第二步：

将excel读入到DataTable中，方便数据操作;

/// <summary>        /// 将Excel转化为一张表        /// </summary>        /// <param name="fudExcel">控件id</param>        /// <param name="sheetName">工作子表名</param>        /// <returns></returns>        public DataTable GetExcelDataTable(FileUpload fudExcel, string sheetName)        {            DataTable dt = new DataTable();            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fudExcel.PostedFile.FileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; ;            OleDbConnection Conn = new OleDbConnection(strConn);            string sheet = "[" + sheetName + "$]";            string strCom = string.Format("SELECT * FROM {0} WHERE 1=1 ", sheet);            if (!string.IsNullOrEmpty(ExcelRow))            {                strCom += ExcelRow;            }            try            {                Conn.Open();                OleDbDataAdapter comm = new OleDbDataAdapter(strCom, Conn);                comm.Fill(dt);                Conn.Close();                dt = IgnoreExcelRowA(dt);            }            catch (Exception ex)            {                Conn.Close();                //throw new Exception("Excel文件存在异常");                throw ex;            }            return dt;        }

/// <summary>        /// 去掉第一行，将第二行改为表的列名，        /// </summary>        /// <param name="DtExcel"></param>        /// <returns></returns>        private DataTable IgnoreExcelRowA(DataTable DtExcel)        {
            if (DtExcel.Columns[0].ColumnName.IndexOf("必填项") >= 0)            {
                if (DtExcel.Rows.Count > 0)                {                    DataRow dr = DtExcel.Rows[0];
                    for (int n = 0; n < DtExcel.Columns.Count; n++)                    {                        if (!string.IsNullOrEmpty(dr[n].ToString()))                        {                            DtExcel.Columns[n].ColumnName = dr[n].ToString().Trim().Replace("'","");                        }                    }                    DtExcel.Rows.RemoveAt(0);                    DtExcel.AcceptChanges();                }            }
            return DtExcel;        }

第三步：

根据excel表中的列名，对比数据库，找出对应的字段名：

 /// <summary>        /// 判断Excel中的列是否存在于数据库,即excel中数据是否有效,若有效则将excel表中的中文字段转换为数据库对应的英文字段        /// </summary>        /// <param name="DataTable">数据库中数据表的字段与描述的对应表</param>        /// <param name="dtExcel">excel数据表</param>        /// <returns></returns>        public DataTable ExcelCompareSQL(DataTable dtNameMapping, DataTable dtExcel)        {            bool isContain = true;            foreach (DataColumn dc in dtExcel.Columns)            {                DataRow[] drName = dtNameMapping.Select(string.Format("value='{0}'", dc.ColumnName.Trim().Replace("'","")));                if (drName.Length == 1)                {                    dc.ColumnName = drName[0]["objname"].ToString();                    dtExcel.AcceptChanges();                }                else                {                    isContain = false;                    break;                }            }            if (!isContain)            {                throw new Exception("Excel表中的某些列无效。");            }            return dtExcel;        }

问题：在这个步骤中会遇到问题：

DataRow[] drName = dtNameMapping.Select(string.Format("value='{0}'", dc.ColumnName.Trim().Replace("'","")));

如果获取对应表DtNameMapping时没有将数据字段进行类型转换直接写

SELECT objname , valueFROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'ProjectEntity', 'column', default)

因为value的数据类型是 sql_variant，所以在代码中用dt.select ("value='{0}'")是不起作用的，甚至跟踪时监视dt里的value也是不能选中的，类似以只有读的权限，没有增删改查的权限，将其转换类型，即可解决该问题

 

备注：

修改或增加字段的说明扩展属性，第一条为修改，第二天为增加

EXEC sp_updateextendedproperty 'MS_Description',N'项目业主','user',dbo,'table','ProjectEntity','column','CustomerName'EXEC sp_addextendedproperty 'MS_Description',N'备注','user',dbo,'table','ProjectEntity','column','Memo'