excel-tools改进计划

最新推荐文章于 2020-08-19 14:32:18 发布

iteye_2252

最新推荐文章于 2020-08-19 14:32:18 发布

阅读量195

点赞数

分类专栏： SAGACITY(睿智)框架讲坛文章标签： Excel SQL Mobile F#

SAGACITY(睿智)框架讲坛专栏收录该内容

8 篇文章 0 订阅

订阅专栏

excel-tools是用来将excel文件导入到数据库、将数据库文件导出成excel以及数据库表数据库互导的强大工具

目前excel-tools改名为excelToy，excelToy刚刚经历建行项目数据移植的考验，成功将旧系统的数据库中（sqlserver2000）的数据移植到oracle10g中，因为系统是彻底重构，数据库表彻底重新设计，而原系统数据需要移植过来，excelToy正好派上用场。

简单介绍一下excelToy的诞生背景：

excelToy设计的目的用于项目试运行前的数据准备阶段，它是sagacity（睿智）j2ee开发平台中的一个组成部分。

一般情况下一个系统上线前需要做初始化一些系统数据，如数据字典、机构、人员、权限，以及一些历史的资料档案（如考试系统，需要初始化试卷库）等等，一般数据准备我们会以excel文件为介质，所以需要将excel数据导入到数据库中。

在此解答几个问题：

1、为什么不用界面维护呢？少量数据维护还好，大量数据维护客户一定会跟你急，另外项目可能只是测试阶段这些准备数据会经常被重新导入，让别人维护一次人家还好说，再维护一次肯定骂死你。

2、为什么不维护一次后从数据库中导出来，以后再重新导呗！

答:很多时候初始化的数据会不停的改变，如人员信息，过两天客户给你一个新的excel文件，客户只会给你提供excel，他不可能帮你改导出的sql文件的

3、写一个程序挨个读excel导呗

答:那可能得写一大堆代码，不通用，下一个项目又得改代码。

总之以上等等等的因素导致了excelToy的诞生。

excelToy包含以下几个功能：

1、将数据库的表按关联关系输出到xml文件中，此功能的目的在于为其他任务提供数据导入的顺序

2、数据导出，可以将sql语句查询结果以及表清单(当然最终也是sql，即select * from table)的数据全部导出存放于指定目录的excel文件中

3、数据导入,将excel文件数据通过eql语句写入到数据库中

4、数据库修改，以excel数据替换到sql语句中，并执行sql完成对数据库的修改

另外excelToy的最大特点是引入转换器概念，比方将excel中的男女转换成F、M，我们将引入一个枚举转换器

我们导入任务中的EQL语句定义，粗体部分：

(
     ${UserID},${EmployeeCode},${UserName},${level},${LoginName},
     ${priceType},@SexEnum(${Gender}),
              ${Description},${Email},
     ${PhoneNumber}, ${AttachDepartmentID},${useDeptId},@MobileSubStr(${celltelephone}),
     ${utmost},${person_code},
     ${ModifyPerson}, ${ModifyDate} , ${startdate},
              ${enddate},@deptEnum(${delFlag}),${pmslogin})
     into SYS_USER_INFO (
     USER_ID, USER_CODE,NAME,LEVEL_ID,ENGLISH_NAME,
     PRICE_TYPE, SEX,
              COMMENTS, EMAIL,
        TELEPHONE,DEPT_ID,WORK_DEPT_ID, MOBILE_NO,
     MAX_DAY_WORK_HOURS, IDENTITY_ID,
     UPDATE_BY, UPDATE_DATE,INDUTY_DATE,
     OUTDUTY_DATE , ENABLED,   PMS_ID)

基本配置效果如下：

<?xml version="1.0" encoding="UTF-8"?>

<property name="export.sheet.maxcount" value="4000"/>
<property name="resPool" value="21"/>
<property name="task.auto.disabled" value="false"/>
<convert id="organNoSeq" extend="sequence"/>
<convert id="organLineSeq" extend="sequence"/>
<convert id="SexEnum" extend="enum">
    <param name="enumKeys">0</param>
    <param name="enumValues">F</param>
    <param name="other">M</param>
</convert>
<convert id="DictDataTypeEnum" extend="enum">
    <param name="enumKeys">字符串,数字,日期</param>
    <param name="enumValues">0,1,2</param>
    <param name="other">0</param>
</convert>
<convert id="sagResourceeEnum" extend="enum">
    <param name="enumKeys">模块,功能菜单</param>
    <param name="enumValues">1,2</param>
    <param name="other">1</param>
</convert>
   <convert id="deptEnum" extend="enum">
    <param name="enumKeys">0,1</param>
    <param name="enumValues">1,0</param>
    <param name="other">1</param>
</convert>
<convert id="MobileSubStr" extend="substr">
    <param name="beginIndex">0</param>
    <param name="endIndex">18</param>
</convert>
<convert id="StatusSubStr" extend="substr">
    <param name="beginIndex">0</param>
    <param name="endIndex">1</param>
</convert>
<convert id="NullConvert" extend="enum">
    <param name="enumKeys">null</param>
    <param name="enumValues"/>
</convert>
<convert id="DeptTreeRoute" extend="treeRoute">
    <param name="rootId" value="-1"/>
    <param name="nodeRouteField" value="NODE_ROUTE"/>
    <param name="nodeLevelField" value="NODE_LEVEL"/>
    <param name="nodeRouteHasSelfField" value="NODE_ROUTE_SELF"/>
    <param name="isChar" value="false"/>
    <param name="size" value="8"/>
</convert>
   <convert id="sagResouceTreeRoute" extend="treeRoute">
    <param name="rootId" value="-1"/>
    <param name="nodeRouteField" value="TREE_ROUTE"/>
    <param name="nodeLevelField" value="NODE_LEVEL"/>
    <param name="isChar" value="false"/>
    <param name="size" value="8"/>
</convert>

<convert id="capabilityTreeRoute" extend="treeRoute">
    <param name="rootId" value="-1"/>
    <param name="nodeRouteField" value="NODE_ROUTE"/>
    <param name="nodeLevelField" value="NODE_LEVEL"/>
    <param name="nodeRouteHasSelfField" value="NODE_ROUTE_SELF"/>
    <param name="isChar" value="false"/>
    <param name="size" value="8"/>
    <param name="leafField" value="IS_LEAF"/>
</convert>
    <convert id="dateFormat" extend="dateFormat">
    <param name="format" value="yyyy-MM-dd"/>
    <param name="dataType" value="String"/>
</convert>
      <convert id="sysdateFormat" extend="dateFormat">
    <param name="format" value=" yyyy-mm-dd hh:mm:ss.fffffffff"/>
    <param name="dataType" value="String"/>
</convert>
<convert id="CalendarFlagEnum" extend="enum">
    <param name="enumKeys">1,2</param>
    <param name="enumValues">0,1</param>
    <param name="other">0</param>
</convert>
   <convert id="myTrim" extend="trim">
</convert>

<convert id="systreeMap" extend="treeMap">
<param name="idIndex" value="1"/>
<param name="dynId" value="true"/>
<param name="idNameIndex" value="2"/>
</convert>
<convert id="systreePoolMap" extend="treeMap">
<param name="idIndex" value="1"/>
<param name="dynId" value="true"/>
<param name="idNameIndex" value="3"/>
</convert>
<convert id="sagResouceMap" extend="treeMap">
<param name="idIndex" value="1"/>
<param name="dynId" value="true"/>
<param name="idNameIndex" value="4"/>
</convert>
<convert id="myTableSequence" extend="tableSequence">
</convert>
<convert id="mydict" extend="dict">
<param name="sql">
<![CDATA[select CAPABILITY_ID from SYS_CAPABILITY where name='#{keyParam}']]>
</param>
</convert>
<convert id="myCapdict" extend="dict">
<param name="sql">
<![CDATA[select DICT_KEY from sag_dict_detail where dict_type_code='CAPABILITY_LEVEL' and DICT_NAME='#{keyParam}']]>
</param>
</convert>
<convert id="myPoolId" extend="dict">
<param name="sql">
<![CDATA[select t1.pool_id from sys_res_pool t1 where t1.pool_name='#{keyParam}']]>
</param>
</convert>
<convert id="workCal" extend="dict">
<param name="sql">
<![CDATA[select DICT_KEY from sag_dict_detail where dict_type_code='SYS_WORKCALENDAR_NAME_TYPE' and DICT_NAME='#{keyParam}']]>
</param>
</convert>
<outputTables id="task0" datasource="pias" file="pias.xml" active="false"/>
<export id="exportXMLTables" datasource="old_pmccenter" blobSave="false" active="false" maxLimit="200000" mapping-tables="PMCCENTER_TABLE.xml" dist="deployData/system">select * from taskassignment b where b.taskid in (select a.taskid from tasksheet a)</export>

<export id="exportSagUser" datasource="old_pmccenter" blobSave="false" active="true" maxLimit="200000" dist="deployData/system/资源信息.xls"> <![CDATA[
         select t.* ,
     (select DepartmentID from UserWorkDepartment d where d.UserID=t.UserID)as useDeptId from UserInfo t
    ]]> </export>
         
<export id="exportUserLevel" datasource="old_pmccenter" blobSave="false" active="true" maxLimit="200000" dist="deployData/system/资源级别报价.xls"> <![CDATA[
         select * from sys_person_level
    ]]> </export>
     
<export id="exportDept" datasource="old_pmccenter" blobSave="false" active="true" maxLimit="200000" dist="deployData/system/部门信息.xls"> <![CDATA[
         select * from Department
    ]]> </export>
     
<export id="exportDeptMaster" datasource="old_pmccenter" blobSave="false" active="true" maxLimit="200000" dist="deployData/system/部门负责人.xls"> <![CDATA[
            select * from DepartmentAccess
    ]]> </export>


    
<import id="sys_workcallendar" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" fkFilter="true" dist="deployData/system" files="行政日历.xls$">
    <do>
      <mainTable><![CDATA[(
${日历编号},@workCal(keyParam:${日历名称}),${开始时间},${结束时间},@CalendarFlagEnum(${工作日标识})
) into SYS_WORK_CALENDAR(
CAL_ID,CAL_NAME,BEGIN_DATE,END_DATE,IS_HOLIDAY
)]]> </mainTable>
    </do>

   <end-do>
   @myTableSequence({keyName:SYS_WORK_CALENDAR})
</end-do>
</import>
    
<import id="sys_capability" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" fkFilter="true" dist="deployData/system" files="职能信息.xls$">

<pre-do>delete from SYS_RES_POOL_USER </pre-do>
       <pre-do>delete from SYS_CAPABILITY_LEVEL </pre-do>
       <pre-do>delete from SYS_RES_POOL_CAPABILITY </pre-do>
       <pre-do>delete from SYS_DEPARTMENT_MASTER </pre-do>
       <pre-do>delete from SYS_RES_POOL_MASTER </pre-do>
       <pre-do>delete from SYS_RES_POOL </pre-do>

<do>
      <mainTable><![CDATA[(
@organNoSeq(capabilityID),@myTrim(${职能名称}),@systreeMap(${上级职能名称}),${显示顺序},
"",0,1,${描述},${启用标志}
) into SYS_CAPABILITY(
CAPABILITY_ID,NAME,PARENT_CAPABILITY_ID,SHOW_ORDER,
NODE_ROUTE,NODE_LEVEL,IS_LEAF,COMMENTS,ENABLED
)]]> </mainTable>

    <subTables>
    <subTable loop-column="对应职级" split="," as="职级">
    <![CDATA[
    (@myCapdict(keyParam:#{职级}),#{CAPABILITY_ID},1,100,100) into SYS_CAPABILITY_LEVEL
    (CAPABILITY_LEVEL_ID,CAPABILITY_ID,ENABLED,CAPABILITY_WORK_RATE,CAPABILITY_LEVEL_NAME)
    ]]>
    </subTable>
    </subTables>
    </do>
      
   <end-do>@capabilityTreeRoute({tableName:SYS_CAPABILITY,idField:CAPABILITY_ID,pidField:PARENT_CAPABILITY_ID})</end-do>
    
   <end-do>
     update sys_capability_level t set t.capability_level_name=
     (select dict_name from sag_dict_detail d where d.dict_type_code='CAPABILITY_LEVEL' and d.dict_key=t.capability_level_id )
   </end-do>
     
     
   <end-do>
   @myTableSequence({keyName:SYS_CAPABILITY})
</end-do>
</import>

<import id="sys_resourcePool" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" fkFilter="true" dist="deployData/system" files="资源池信息.xls$">
<pre-do>delete from SYS_RES_POOL_USER </pre-do>
<pre-do>delete from SYS_RES_POOL_CAPABILITY </pre-do>

<do>
<mainTable><![CDATA[(
@organNoSeq(resourcePoolID),@organNoSeq(poolCode),@myTrim(${资源池名称}),@systreePoolMap(${上级资源池}),"",
0,1,1,${描述},${创建人},@dateFormat(${创建日期}),${启用标志}
) into SYS_RES_POOL(
POOL_ID,POOL_CODE,POOL_NAME,PARENT_POOL_ID,NODE_ROUTE,
NODE_LEVEL,IS_LEAF,NODE_ROUTE_SELF,COMMENTS,UPDATE_BY,UPDATE_DATE,ENABLED
)]]> </mainTable>

<subTables>
<subTable loop-column="对应职能" split="," as="职能">
<![CDATA[
(@mydict(keyParam:@myTrim(#{职能})),#{POOL_ID},1) into SYS_RES_POOL_CAPABILITY
(CAPABILITY_ID,POOL_ID,ENABLE)
]]>
</subTable>
</subTables>
    </do>
      
   <end-do>@capabilityTreeRoute({tableName:SYS_RES_POOL,idField:POOL_ID,pidField:PARENT_POOL_ID})</end-do>
     
   <end-do>
   @myTableSequence({keyName:SYS_RES_POOL})
</end-do>
</import>

<import id="sys_department" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" dist="deployData/system" files="部门信息.xls$">
    <pre-do>delete from SYS_USER_INFO</pre-do>
    <pre-do>delete from SYS_USER_LEVEL_PRICE</pre-do>
    <pre-do>delete from SYS_DEPARTMENT_MASTER</pre-do>
    <do>
<mainTable> <![CDATA[(${DepartmentID},${DepartmentCode},${SuperiorDepartmentID},
    ${DepartmentName},${simTitle},${type},${SequenceKey},${org_code},${Description},
    ${ModifyPerson},${ModifyDate},${contractcall},${contractfax},
    ${contractmail},@deptEnum(${delFlag}),"",0)
     into SYS_DEPARTMENT_INFO (DEPT_ID,DEPT_CODE,PARENT_DEPT_ID,
     NAME,SIMPLE_NAME,DEPT_TYPE,SHOW_ORDER,ORGAN_CODE,COMMENTS,
     UPDATE_BY,UPDATE_DATE,TELEPHONE,FAX_CODE,EMAIL,ENABLED,NODE_ROUTE,NODE_LEVEL)
   ]]> </mainTable>
    </do>
    
    <end-do>update SYS_DEPARTMENT_INFO set PARENT_DEPT_ID=-1 where DEPT_ID=1</end-do>
    
    <end-do>@DeptTreeRoute({tableName:SYS_DEPARTMENT_INFO,idField:DEPT_ID,pidField:PARENT_DEPT_ID})</end-do>
   
   <end-do>
   @myTableSequence({keyName:SYS_DEPARTMENT_INFO})
</end-do>
    
<end-do>
   update SYS_DEPARTMENT_INFO d set d.dept_type=3 where d.parent_dept_id in(1,28)
</end-do>
    <end-do>
    update SYS_DEPARTMENT_INFO d set d.dept_type=2 where d.parent_dept_id=6
</end-do>
    <end-do>
    update SYS_DEPARTMENT_INFO d set d.dept_type=4 where d.parent_dept_id=-1
</end-do>
    <end-do>
   update SYS_DEPARTMENT_INFO d set d.dept_type=1 where d.dept_id in(6,28)
</end-do>

</import>

<import id="sys_userleavePrice" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" fkFilter="true" dist="deployData/system" files="资源级别报价.xls$">
    <pre-do>delete from SYS_USER_INFO</pre-do>
    <do>
      <mainTable> <![CDATA[(${id},${departmentId},${level},${pay},@deptEnum(${is_delete}))
       into SYS_USER_LEVEL_PRICE (LEVEL_ID,DEPT_ID,APTITUDE_LEVEL,PRICE_VALUE,ENABLED)
       ]]> </mainTable>
    </do>
       
   <end-do>
   @myTableSequence({keyName:SYS_USER_LEVEL_PRICE})
</end-do>
</import>

<import id="sys_userInfo" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" fkFilter="true" dist="deployData/system" files="资源信息.xls$">

   <pre-do>
   delete from SYS_RES_POOL_USER
   </pre-do>
   <do>
   <mainTable> <![CDATA[
   (
     ${UserID},${EmployeeCode},${UserName},${level},${LoginName},
     ${priceType},@SexEnum(${Gender}),
              ${Description},${Email},
     ${PhoneNumber}, ${AttachDepartmentID},${useDeptId},@MobileSubStr(${celltelephone}),
     ${utmost},${person_code},
     ${ModifyPerson}, ${ModifyDate} , ${startdate},
              ${enddate},@deptEnum(${delFlag}),${pmslogin})
     into SYS_USER_INFO (
     USER_ID, USER_CODE,NAME,LEVEL_ID,ENGLISH_NAME,
     PRICE_TYPE, SEX,
              COMMENTS, EMAIL,
        TELEPHONE,DEPT_ID,WORK_DEPT_ID, MOBILE_NO,
     MAX_DAY_WORK_HOURS, IDENTITY_ID,
     UPDATE_BY, UPDATE_DATE,INDUTY_DATE,
     OUTDUTY_DATE , ENABLED,   PMS_ID)
   ]]> </mainTable>
    </do>
   
   <end-do>
   @myTableSequence({keyName:SYS_USER_INFO})
</end-do>
    
   <end-do>
   update sys_user_info d set d.level_id=
  (select t.aptitude_level from SYS_USER_LEVEL_PRICE t where t.level_id=d.level_id)
   </end-do>
      
     <end-do>
update sag_user t set t.enabled=
  (select d.enabled from sys_user_info d where d.user_id=t.user_id)
</end-do>
</import>

    

   <import id="poolUserInfo" datasource="pias" clear="true" active="true" startRow="2" endRow="836" titleRow="1" fkFilter="true" dist="deployData/system" files="资源池人员用户信息.xls$">
    <do>
   <mainTable> <![CDATA[(
     @organNoSeq(poolUserID),${用户ID},@myPoolId(keyParam:@myTrim(${所属资源池})),1,@myCapdict(keyParam:@myTrim(${等级})),100,1)
     into SYS_RES_POOL_USER (
     ID,USER_ID,POOL_ID,CAPABILITY_ID,
     CAPABILITY_LEVEL_ID,RATE,ENABLED
)]]> </mainTable>
    </do>
   
   <end-do>
   @myTableSequence({keyName:SYS_RES_POOL_USER})
   </end-do>
    <end-do>
   update SYS_RES_POOL_USER t set t.capability_id=
  (select t1.CAPABILITY_ID from SYS_RES_POOL_CAPABILITY t1 where t1.pool_id=t.pool_id)
</end-do>
   
     
<end-do>
    update SYS_RES_POOL_USER sss
set sss.enabled=
(select t.enabled from sag_user t where t.user_id=sss.user_id)
</end-do>
</import>

<import id="workType" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" dist="deployData/system" files="项目主阶段对应工作类型.xls$">
    <pre-do>
        delete from SYS_PJ_RES_DISTRIBUTE
    </pre-do>
    <do>
   <mainTable> <![CDATA[(
     ${WORK_TYPE},${WORK_TYPE_NAME})
     into SYS_PROJECT_WORK_TYPE (
     WORK_TYPE,WORK_TYPE_NAME
)]]> </mainTable>
    </do>
       
   <end-do>
   @myTableSequence({keyName:SYS_PROJECT_WORK_TYPE})
</end-do>
</import>

<import id="application" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" dist="deployData/system" files="应用系统.xls$">
    <do>
   <mainTable> <![CDATA[(
      ${APP_ID},${APP_CODE},${APP_NAME},${COMMENTS},${ENABLED},${DEPT_ID})
     into SYS_APPLICATION (
     APP_ID,APP_CODE,APP_NAME,COMMENTS,ENABLED,DEPT_ID
)]]> </mainTable>
    </do>
         
   <end-do>
   @myTableSequence({keyName:SYS_APPLICATION})
</end-do>
</import>

<import id="transLog" datasource="pias" clear="true" active="true" startRow="2" titleRow="1" dist="deployData/system" files="系统日志表.xls$">
    <do>
   <mainTable> <![CDATA[(
      ${TRANS_ID},${TRANS_CODE},${TRANS_NAME},${RUN_FREQUENCY},${COMMENTS},${ENABLED})
     into SYS_DAILY_TRANSACTION (
     TRANS_ID,TRANS_CODE,TRANS_NAME,RUN_FREQUENCY,COMMENTS,ENABLED
)]]> </mainTable>
    </do>
         
   <end-do>
   @myTableSequence({keyName:SYS_DAILY_TRANSACTION})
</end-do>

<end-do>
update sys_user_info d set d.enabled=0
where d.user_id in
(1501,1511,1281,53,54,55,1667,180,1273,1255,1258,1260,1261,1272,1298,1337,1352,1380,1382,1425,1481,1500,
1468,1455,1457,229,1078,1281,1357,1478,1485,1526,1527,1528,1529,1530,1634)
</end-do>
<end-do>
update sag_user d set d.enabled=0
where d.user_id in
(1501,1511,1281,53,54,55,1667,180,1273,1255,1258,1260,1261,1272,1298,1337,1352,1380,1382,1425,1481,1500,
1468,1455,1457,229,1078,1281,1357,1478,1485,1526,1527,1528,1529,1530,1634)
</end-do>
</import>
</sagacity>

iteye_2252

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
excel-tools改进计划

excel-tools是用来将excel文件导入到数据库、将数据库文件导出成excel以及数据库表数据库互导的强大工具目前excel-tools改名为excelToy，excelToy刚刚经历建行项目数据移植的考验，成功将旧系统的数据库中（sqlserver2000）的数据移植到oracle10g中，因为系统是彻底重构，数据库表彻底重新设计，而原系统数据需要移植过来，excelToy正好派上用...
复制链接

扫一扫