通用的高度可扩展的Excel导入实现.docx

通用的高度可扩展的Excel导入实现.docx

《通用的高度可扩展的Excel导入实现.docx》由会员分享，可在线阅读，更多相关《通用的高度可扩展的Excel导入实现.docx（15页珍藏版）》请在冰豆网上搜索。

通用的高度可扩展的Excel导入实现

背景

　　通过程序将excel导入到数据库中是一项非常常见的功能。

通常的做法是：

先将excel转成DataTable，然后将DataTable转换成List，最终通过List导入进数据库。

　　同时，一般的ExcelUtil基类也会提供类似ListGetEntityListFromFile（stringfileName）的实现，但是这些实现一般都是机械化地对应，缺少一些自定义的操作。

问题

　　在我的开发过程中，就导入excel这一块就碰到了以下的一些问题：

1.　　excel中的列名可能不止一个（客户可能没有下载模板或者模板的版本不对，导致同一列可能有多个列名，例如【ID】和【编号】可能都对应实体类中的【ID】列）

2.　　excel中的列可能对应着实体类中的多个列（例如实体类中有【ID】、【Name】两个字段，而excel模板中只有【ID】，我们需要通过excel中的【ID】从数据库中找到Name值去填充实体类的【Name】）

3.　　需要对excel中某些数据的有效性做验证，包括但不限于“数据类型有效”，“数据在数据库中的存在性”，“数据在数据库中的状态有效”等等

4.　　需要对excel中的某些列进行自定义的映射（例如实体类和excel中都有【Status】，我们需要将excel中的【Status】写“1”和写“激活”的都填充实体类的【Status】为“1”）

　　以上这些问题，仅仅通过基类提供的方法是无法实现的，所以本篇文章提供一种高度自定义的Excel导入通过模块的实现，期以解决以上问题。

Demo程序结构概览

ps：

要下班了，今日就先发这么多，

　　大家可以先下Demo看一下，功能都实现了，有问题可以大家一起交流，

　　如果大家想知道具体流程，我就再添加。

实现思路

　　首先，需要明确的是我们的需求：

1.使用c#技术，将excel导入到程序中，并以List的形式进行存在

2.可以自定义excel的列名和Entity中属性的对应关系，支持excel中多个列名对应Entity中同一个属性，也支持Entity中的多个属性对应excel中的一个列名

3.可以自定义excel中列的值转换函数，假设excel中有“状态”列对应到Entity中的Status属性，在excel中的“状态”列中写【激活】或者【1】，最终都对应到Status的【true】

4.可以自定义excel中列的值有效性验证函数

　　接下来，就简要讲述一下以上功能的实现：

∙类【publicclassImportUtilwhereEntity:

new（）】实现了excel的导入功能，Entity为实体类类型

///

///

publicclassImportUtilwhereEntity:

new（）

∙ImportUtil类中有唯一的构造函数【publicImportUtil（DictionaryallColumns）】

　　publicImportUtil（DictionaryallColumns）

{

AllColumns=allColumns;

}

　　　　　　　//实体类列和excel列的对应关系

DictionaryallColumns=newDictionary

{

{"ID","编号"},

{"StudentID","学生编号|学生ID"},{"StudentName","学生编号|学生ID"},

{"DisciplineID","学科编号"},{"DisciplineName","学科编号"},

{"Score","学科分数"},

{"MakeUpExamTime","补考日期"}

};

　　该构造函数传入参数allColumns，是一个Dictionary类型的，该参数实现了需求2，字典的Key是实体类Entity中的【属性名称】，字典的Value是excel中的【列名称】，使用|来支持excel的多列名。

∙ImportUtil类中有唯一的对外公开的方法【publicListGetEntityList】

///

///excel文件名

///导入过程中的任何错误信息都会放到这里面

///需要进行预定义类型转换的列

///需要进行自定义类型转换（key是实体类对应的列，value是包含excel中对应列的值和操作后的返回值的Func）

///在进行excel列映射到实体类之前，对excel列中的值进行的预检查

///实体类列表

publicListGetEntityList（stringfileName,

ListerrorInfo,

Dictionary>converterFields=null,

Dictionary>customConverts=null,

Dictionary>dataValidateChecks=null）

　　该方法有5个输入参数，其中fileName和errorInfo是必输的，方法最终返回的就是我们所需要的List，方法的执行过程就是先通过excel的地址将excel导入称为DataTable，然后将DataTable转换成List，在转换的过程中进行数据的有效性验证和数据值的类型转换。

下面简单介绍一下这5个参数：

1.fileName，string，需要导入的excel的地址；

2.errorInfo，List，导入过程中所发生的错误信息；

3.convertFields，Dictionary>，预定义的类型转换；excel中的列都是object类型的，而Entity中的属性却是强类型的，对于Int，Decimal，Double，DateTime已经定义好了相关的转换函数，仅需将Entity属性名称传入，在进行DataTable转List时就能够将对应Column的值进行预定义的类型转换

4.　　//预定义类型转换

5.Dictionary>convertFields=newDictionary>

6.{

7.{KnownDataType.Int,newList{"ID","StudentID","DisciplineID"}},

8.{KnownDataType.Double,newList{"Score"}}

};

9.customConverts，Dictionary>，自定义的类型转换；预定义的类型转换通常只是将excel中的列转成对应的类型或者给个默认值，是通用的操作，但是我们常常需要对excel中的列进行自定义的转换，例如：

excel中有“编号”列，设置同时对应到Entity的“ID”、“Name”属性，而“ID”属性可以直接使用预定义转换完成，“Name”列则需要通过“编号”从数据库中找到“Name”再赋值给“Name”。

因此，这时候我们需要自定义的转换。

写法如下：

//自定义类型转换

Dictionary>customConverts=newDictionary>

{

{

"StudentName",currColumn=>

{

intstudentID;

if（currColumn!

=null&&int.TryParse（currColumn.ToString（）,outstudentID））

{

varr=DataPool.EntireStudents.Find（c=>c.ID==studentID）;

if（r!

=null）

{

returnr.Name;

}

}

returnstring.Empty;

}

},

{

"MakeUpExamTime",currColumn=>

{

DateTimecolDateTime;

if（currColumn!

=null&&DateTime.TryParse（currColumn.ToString（）,outcolDateTime））

{

returncolDateTime;

}

returnnull;

}

}

};

10.dataValidateChecks，Dictionary>，excel列的数据有效性验证，在Entity的每个属性的赋值之前都会先验证一下excel的数据是否有效，Key是要验证的属性的名字，Value是一个Func，输入参数就是excel的列值，返回的是string，验证通过时是string.Empty，验证不通过返回的是错误信息。

写法如下：

//excel列的数据有效性验证以下完成了对excel中编号列的值必须为整数且不为空的验证的设定

Dictionary>dataValidateChecks=newDictionary>

{

{

"ID",currColumn=>

{

intidInt;

if（currColumn!

=null&&int.TryParse（currColumn.ToString（）,outidInt））

{

returnstring.Empty;

}

return"ID不能为空且必须为整数";

}

}

};

//执行excel导入

varres=importUtil.GetEntityList（excelPath,errors,convertFields,customConverts,dataValidateChecks）;

总结

　　以上就完成了一个excel的自定义导入的功能，谢谢。

一个通用的分页存储过程实现-SqlServer

使用前提

　　查询表必须有ID字段，且该字段不能重复，建议为自增主键

背景

　　如果使用ADO.NET进行开发，在查询分页数据的时候一般都是使用分页存储过程来实现的，本文提供一种通用的分页存储过程，只需要传入：

1.表名（以DBName.dbo.TableName）的形式 

2.Where条件（ID>0ANDID<100）

3.Select字段（ID,NAME,CreateDate）

4.Order字段（NAMEASC,CreateDateDESC）

5.PageSize（15）

6.PageIndex

（2）

7.TotalCount，此为output参数

　　这7个参数，存储过程就能够返回指定条件下的分页数据，和数据总数。

sql源码&测试环境搭建

--创建Util库

CREATEDATABASEUtil

GO

--创建通用的分页存储过程

USEUtil

GO

/******Object:

StoredProcedure[dbo].[UP_GeneralPagedQuery_v1]ScriptDate:

04/03/201417:

32:

07******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

--Author:

DeanZhou

--Createdate:

2013-09-24

--Description:

通用的分页存储过程

（一）

CREATEPROCEDURE[dbo].[UP_GeneralPagedQuery_v1]

@TableNameVARCHAR（100）,--表名称：

如MKT.dbo.UV_CouponInfo

@WhereFieldNVARCHAR（1000）='',--筛选条件：

如Status=1ANDCreateUser='admin'

@SelectFieldNVARCHAR（1500）='*',--需要查询的列：

如*

@OrderFieldNVARCHAR（1000）='',--需要进行排序的字段：

如CustomerNamedesc,StartDateasc,Statusdesc,idasc

@PageSizeINT=15,--页面大小：

如15

@PageIndexINT=1,--当前页面：

如1

@TotalCountINT=0OUT--记录总数：

输出值

AS

BEGIN

IF@OrderFieldISNULLOR@OrderField=''

BEGIN

SET@OrderField='ID'

END

IF@WhereFieldISNULLOR@WhereField=''

BEGIN

SET@WhereField='WHEREID>0'

END

ELSE

BEGIN

SET@WhereField='WHERE'+@WhereField

END

DECLARE@ExceptCountINT=@PageSize*（@PageIndex-1）

DECLARE@TakeCountINT=@PageSize

DECLARE@IsNeedSubQueryINT

DECLARE@SqlPreviewNVARCHAR（MAX）=

'SELECT@C=COUNT

（1）FROM'+@TableName+''+@WhereField+';'

+'IF@EC<0SET@EC=0IF@EC>=@CSET@EC=@C;'

+'IF@TC<0SET@TC=15IF（@EC+@TC）>@CSET@TC=@C-@EC;'

+'IF@EC>0AND@TC>0SET@NSQ=1ELSESET@NSQ=0;'

EXECsp_executesql@SqlPreview,

N'@CINTOUTPUT,@ECINTOUTPUT,@TCINTOUTPUT,@NSQINTOUTPUT',

@TotalCountOUTPUT,@ExceptCountOUTPUT,@TakeCountOUTPUT,@IsNeedSubQueryOUTPUT

DECLARE@MaxOrMinVARCHAR（3）='MAX'

DECLARE@DescOrAscVARCHAR（4）=''

IF@ExceptCount>@TotalCount/2

BEGIN

SET@MaxOrMin='MIN'

SET@DescOrAsc='DESC'

SET@ExceptCount=@TotalCount-@ExceptCount+1

END

DECLARE@SqlQueryNVARCHAR（MAX）=

'DECLARE@T_IDSTABLE（IDINT）'

+'IF@NSQ=1'+

'BEGIN'+

'SELECT@MD='+@MaxOrMin+'（ID）FROM'+

'（SELECTTOP'+CONVERT（VARCHAR（15）,@ExceptCount）+'IDFROM'+@TableName+''+@WhereField+'ORDERBY'+@OrderField+'）T1;'+

'INSERTINTO@T_IDS'+

'SELECTTOP'+CONVERT（VARCHAR（15）,@TakeCount）+'IDFROM'+@TableName+''+@WhereField+'ANDID>@MDORDERBY'+@OrderField+

'SELECT'+@SelectField+'FROM'+@TableName+'SWHEREIDIN（SELECTIDFROM@T_IDS）ORDERBY'+@OrderField+

'END'+

'ELSE'+

'BEGIN'+

'INSERTINTO@T_IDSSELECTIDFROM（SELECTTOP'+CONVERT（VARCHAR（15）,@TakeCount）+'IDFROM'+@TableName+''+@WhereField+'ORDERBY'+@OrderField+'）T;'+

'SELECT'+@SelectField+'FROM'+@TableName+'SWHEREIDIN（SELECTIDFROM@T_IDS）ORDERBY'+@OrderField+

'END'

EXECsp_executesql@SqlQuery,N'@MDINT,@NSQINT',0,@IsNeedSubQuery

END

GO

--创建测试库

CREATEDATABASETest

GO

--创建测试表

USE[Test]

CREATETABLETableTest（IDINT,NAMENVARCHAR（50）,CreateDateDATETIME）

GO

--插入测试数据

INSERTINTOTableTest

SELECT1,'dean1',GETDATE（）UNION

SELECT2,'dean2',GETDATE（）UNION

SELECT3,'dean3',GETDATE（）UNION

SELECT4,'dean4',GETDATE（）

　　打开您的sqlserver，在本地新建查询，并运行上面的代码，会在你的数据库中创建以下内容：

1.一个名称为【Util】的数据库，该库下面有一个名为【UP_GeneralPagedQuery_v1】的存储过程，这个存储过程就是通用的分页存储过程。

2.一个名称为【Test】的数据库，该库下面有一个名为【TableTest】的表，这个表里面有4条数据

请注意：

在执行sql之前确认一下没有重名数据库，以免出错

 测试

　　新建一个查询，执行下面sql，就完成了分页数据的获取

DECLARE@TotalCountint

EXECUtil.[dbo].[UP_GeneralPagedQuery_v1]

@TableName=N'Test.dbo.TableTest',

@WhereField=N'ID>0ANDID<100',

@SelectField=N'ID,NAME,CreateDate',

@OrderField=N'NAMEASC,CreateDateDESC',

@PageSize=2,

@PageIndex=2,

@TotalCount=@TotalCountOUTPUT

SELECT@TotalCountasN'@TotalCount'