页面树结构
转至元数据结尾
转至元数据起始


Excel数据导入实现列表清单式excel数据的导入与更新,支持的功能有:

  • 支持导入xls(excel 2003)和xlsx(excel 2007、excel 2010及excel 2010以上的版本)的数据。
  • 支持excel中的数据插入、更新到数据库。
  • 支持清单列表中表头表尾的导入。
  • 支持只导入excel中的部分数据。
  • 支持某一列导入固定值、参数值、或者java类返回的值。
  • 支持对excel中要导入的每一列数据进行验证。
  • 支持出现异常的数据可以导出,并提示出错原因。
  • 支持扩展接口,实现自定义导入需求或验证需求。

需要了解的限制有:

  • 一个导入规则只能一个excel sheet页对应一张数据库表,但可以通过设置多个导入规则的方式实现一个excel sheet页的内容对应多张数据库表(例如主子表)。
  • 目前没有支持导入的同时把数据库以前的记录删掉,但能支持更新以前的记录。

需要了解的功能术语有:

  • Exel导入模板:Excel导入规则定义,一个excel文件对应一个excel导入模板,excel文件中的sheet页对应模板中的一个导入规则。
  • 导入规则:定义Excel中指定sheet页的数据导入到哪个库、哪张表、哪些字段,以及延伸的一些功能设置。
  • 值域范围:可选值列表的定义,用于验证excel中某一列的数据必须在指定的可选值列表内,目前仅支持字符串类型。

功能入口

创建/管理excel中每个sheet页的导入规则,用于多次导入规则相同的excel数据。

一个excel文件对应一个excel导入模板,excel文件中的sheet页对应模板中的一个导入规则。

  • 新建:在“分析展现” 资源目录区现有文件夹下的更多操作,选择 新建分析 > Excel导入模板 。
  • 执行导入:双击Excel导入模板(或者在更多操作 选择 打开 或 新窗口打开 )进入导入excel数据界面。
  • 编辑:点击Excel导入模板,在更多操作 选择 编辑,进入模板编辑界面。
  • 删除:点击Excel导入模板,在更多操作 选择 删除

值域范围

可选值列表的定义,用于验证excel中某一列的数据必须在指定的可选值列表内,目前仅支持字符串类型。

  • 新建入口:在模块左侧资源树,选择公共设置 值域范围 > 更多操作 新建 > 值域范围
  • 编辑:双击值域范围(或者在更多操作选择 打开)进入编辑界面。
  • 删除:点击值域范围,在更多操作 删除
  • 使用入口:“Excel导入模板的编辑界面”的 列设置 > 某一列的 检验设置 > 值域范围的 请选择

异常数据日志管理

查看导入异常日志,并可以导出导入异常的数据、及异常原因。

  • 入口:在“系统运维”界面,在主菜单选择 导入异常日志 。

出现在异常数据日志中的前置条件:Excel导入模板中需要设置“异常处理规则”为 继续执行 

演示数据准备

1、在demo库中的northwind执行附件中的“测试表及数据.sql”,这个脚本导入两张表:xxxgxzdw(学校相关行政单位)和dim_xxmc(学校名称维表)。导入成功后进入smartbi,将新增的表加入到数据源管理中("管理""系统管理""数据源""northwind""DEFAULT"-"同步schema")。

2、演示要导入的excel数据“表1-3学校相关行政单位.xls”。

新建Excel导入模板

下面以新建一个“演示带参数的导入模板”为例,详细说明每一项的配置。该示例实现为每个学校导入、更新行政单位及其负责人。

Excel数据如下:

目标表结构如下:

其中学校代码,需要根据用户选择的值导入,实现如下:

1、点击新建Excel导入模板

  • 入口:在“分析展现” 资源目录区现有文件夹下的更多操作,选择 新建分析 > Excel导入模板 。

2、进入Excel导入模板编辑界面

  • 区域1为导入规则管理:“添加导入规则”点击可在左侧添加或删除导入规则。
  • 区域2为Excel补录模板管理:需要上传Excel模板,以便补录用户可以下载模板并录入数据。
    “上传Excel补录模板”,是指上传满足”导入规则列表区“规则的excel文件,用于用户下载补录模板或异常数据日志管理中异常数据下载。选择模板文件之后,必须点击”上传“才能保存补录模板,这里选择”表1-3学校相关行政单位补录模板.xls“上传。
  • 区域3为导入规则设置:包括基本设置列设置扩展设置
  • 区域4为参数布局设置:可以对参数的位置进行调整。

3、进入规则编辑界面

  • 基本设置

    • 数据源:系统管理中定义的关系数据源,要导入的目标库。
    • 回写表:要导入的目标表。
    • Sheet名:对应Excel文件中要导入Sheet名称。
    • 起始行:设置开始执行导入的行,包含设置的值,不设缺省从1开始。
      1. 行号:通过设置行号的形式设置起始行。
      2. 出现字符:通过设置Excel中指定列开始出现指定字符设置起始行。
    • 结束行:设置结束导入的行,设置的结束行也会执行导入,缺省直到Excel结束。
      1. 行号:通过设置行号的形式设置结束行。
      2. 出现字符:通过设置Excel中指定列开始出现指定字符设置结束行。
      3. 直到空行:遇到空行终止导入。
    • 异常处理规则:设置出现校验异常时(如长度超长,类型不匹配、超值域范围、主键冲突等),是回滚整个sheet的导入还是继续执行下一条数据,将这条异常数据写入异常日志表。这里的异常是指不满足用户设置的校验规则导致的异常,并非数据库sql执行过程中产生的未知异常,后者是直接回滚抛出错误原因给用户的。
      1. 回滚:设置为出现校验异常时,回滚整个sheet中数据的导入,并抛出碰到的第一个校验异常原因。选择了回滚,异常日志中并不会记录所有的异常数据及原因,因为回滚意味着遇到第一个校验异常时,直接回滚,跳出导入该sheet。
      2. 继续执行:设置为出现校验异常时,继续执行导入下一条数据,并将这条数据写入到异常日志,供后面下载查看异常原因。
    • 数据处理模式:导入数据的时候是采取增量更新还是全量更新。
      1. 全量:把数据库中的表中原有的数据删除,重新导入数据。
      2. 增量:在数据库中的表原有的数据基础下继续导入数据。
    • 主键冲突处理规则:设置主键冲突时(是指Excel中数据出现和数据库表中的数据主键值一样时),是执行更新还是抛出主键冲突的校验异常,这里的"主键"是"列设置"中用户指定的主键,并不一定是数据库表的主键,但设置的主键也是需要数据满足唯一性。
            a. 更新:主键重复时,执行更新。
            b. 异常处理:主键重复时,抛出主键冲突的检验异常,由“异常处理规则”去处理这个异常。

    • 是否开启批量:用于配置处理数据库操作时是否使用数据库批处理,如果关闭批量,则会每次单独执行一条SQL,性能较差。当开启批量,则会每执行1000条SQL,或结束扫描Excel时提交一次事务,性能较好。
  • 列设置        
    • 字段名和字段类型:根据基本设置中设置的表自动读取。
    • 是否主键:缺省会自动从数据库读取,但用户可以更改,例如选择学校代码、单位号作为主键。
    • 是否允许为空:缺省会自动从数据库读取,但用户可以更改。绑定输入:设置数据库表列的数据来源。
      1. 不绑定:执行导入时,该列不插入值。
      2. 绑定excel列:绑定excel中指定列数据。
      3. 绑定excel单元格:绑定excel中指定单元格数据。
      4. 绑定参数:绑定参数,该参数为公共设置中定义的参数,选择绑定参数后,执行导入时,导入界面会显示该参数,用户导入前需要先选择参数的值再执行导入,这样这列导入的值就是用户所选的参数值,这里学校代码选择参数"学校名称",其中的学校名称参数是下拉框参数,数据源是northwind,备选值是:“select t_code as code, t_name as name from dim_xxmc",默认值是"select t_code as code, t_name as name from dim_xxmc limit 1”。
      5. 使用固定值:绑定固定值,用户输入一个固定值。使用JAVA类:绑定java类,系统内置了唯一id和系统时间两个常用的类,用户可通过扩展包,实现接口smartbi.daq.excelimport. ICellDataGenerator,并注册java类,详细请见 演示绑定自定义java类的导入模板
        1. 唯一id:32位长的唯一id。
        2. 系统时间:执行导入时的系统时间。
  • 按文件绑定
    • Excel配置文件:excel文件,该文件中必须有一行是数据库列名或列的别名,系统通过将这个名称和数据库表的列名、或列的别名匹配达到自动映射的目的。
    • 作为补录模板:勾选这一项后,系统会将上传的配置文件作为补录模板,相当于功能“上传Excel补录模板”功能。
    • Sheet名:用于匹配的Sheet页名称,如果勾选了”作为补录模板“,该名称必须和Excel导入模板中配置的Sheet名相同。
    • 名称所在行号:列名/列别名所在行号。
  • 校验设置
    • 支持的验证规则:字符长度限制、值域范围验证、数字范围验证、正则表达式验证(譬如某一列必须满足邮件的规则,就可以使用正则表达式验证)。
  •  扩展设置
    • 规则验证类:如果通过"校验设置",不能满足用户验证需求,用户通过该入口,可以自定义该类,实现自定义验证每一行数据规则的目的,该类必须实现smartbi.daq.excelimport.IRuleValidator接口,设置了该项,系统缺省的"校验设置"是不生效的,用户可以通过继承smartbi.daq.excelimport.DefaultRuleValidator保留缺省的校验设置。
    • 数据处理类:缺省情况下,系统使用smartbi.daq.excelimport.DefaultRowDataImporter处理每一行数据,如果用户需要实现自己的导入数据的逻辑,可设置该类,并必须实现smartbi.daq.excelimport.IRowDataImporter接口。

4、 保存模板

点击 保存 ,弹出“保存Excel导入模板”对话框,输入名称及描述,点击 确认 保存模板。

批量导入使用规则

"批量导入"和"异常处理规则"两个按钮需要配合使用,配合实际例子对逻辑描述如下:

假如,现在有1086行数据需要导入,前1000条称为【批次1】,后续的86条称为【批次2】

1.  开启批量+异常做回滚处理:

· 如批次1里的数据出现异常,回滚批次1,并且后续批次不导入。
· 如批次2里的数据出现异常,回滚批次2,并且后续批次不导入,但批次1的数据已经被提交。

2. 开启批量+异常做继续执行处理:

          共插入1085行,跳过错误的1行。

3. 关闭批量+异常做回滚处理:

          全部回滚。

4. 关闭批量+异常做继续执行处理:

           共插入1085行,跳过错误的1行。

执行导入Excel数据

  • 入口:双击Excel导入模板(或者在更多操作选择 打开 )进入导入excel数据界面,如“演示带参数的导入模板”,进入导入界面:

  • 下载补录模板:下载模板编辑界面上传的补录模板。
  • 发货城市:选择Excel数据所属发货城市,这是在Excel导入模板列设置中绑定的参数。
  • 请选择Excel文件:要导入的Excel数据文件。
  • 导入:点击该按钮执行导入,显示导入等待界面,导入完成后再下方显示导入结果:

新建值域范围

下面以新建“单位职能列表”为例,详细说明每一项的配置。

1、点击新建值域范围,选择数据源northwind

  • 入口一:在“系统导航栏”选择 公共设置,展开资源目录区,在“值域范围”或其节点下文件夹的更多操作中选择 新建 > 值域范围
  • 入口二:在“公共设置”界面主菜单选择 值域范围。

2、进入值域范围范围编辑界面:

  • 值域名称:值域的真实名称,保存后不可修改。
  • 值域别名:值域的别名。
  • 描述:值域的描述信息。
  • 值域类型:SQL支持和静态列表,和参数的备选值设置、默认值的设置相同。
    1. SQL:通过SQL的方式设置值域的可选值列表。
    2. 静态列表:通过手动设置值列表,本例选择这种方式。
    3. 批量导入:选择静态列表后激活,可导入UTF-8编码的文本格式文件,每一行代表一个值,如:      
  • 值:根据值域类型设置的不同,设置方式就不同,其中通过SQL方式设置值,请参见下拉框参数的备选值设置。
  • 值字段:值域类型选择SQL后,显示,设置定义的sql中哪一列作为值域值。

3、点击保存,保存值域范围。

异常日志数据管理

查看导入异常日志,并可以导出导入异常的数据、及异常原因。

  • 入口:在系统运维,单击 导入异常数据

出现在异常数据日志中的前置条件:Excel导入模板中需要设置“异常处理规则”为 继续执行 

应用示例

演示带参数的导入模板

请见“新建Excel导入模板”。

演示静态列表值域范围

值域范围的新建和下拉框参数的新建非常相像,但比下拉框参数更简单。
请见“新建值域范围 ”。

演示带值域范围验证的导入模板

该示例实现为每个学校导入、更新行政单位及其负责人。Excel数据如下:

目标表结构如下:

其中学校代码,需要根据用户选择的值导入,单位职能只有:学生管理、教学管理、质量监控、其他四个可选值。实现步骤如下:

1、新建“单位职能列表”值域范围,详见“演示静态列表值域范围”。

2、点击新建Excel导入模板。

3、点击列设置,设置列映射。

4、点击“单位职能”那一行的校验设置,设置值域范围校验,点击 确定。  

5、点击保存,弹出保存对话框,输入名称“演示带值域范围验证的导入模板”,保存excel导入模板。

6、双击“演示带值域范围验证的导入模板”执行导入,选择文件“表1-3学校相关行政单位 - 值域范围验证.xls”验证值域范围校验。

7、下载异常数据

1)如果提示“下载异常数据失败”,原因:模板文件不存在,请编辑模板,上传模板文件。请重新编辑改Excel模板,上传模板文件。

2)然后点击右下角的保存,就会显示成:

3)上传模板后,关闭模板,点击“异常数据日志管理”,可以看到刚导入的异常数据。

4)点击“下载异常”图标,下载异常数据。

否则可以看到导出的异常数据,显示值域范围验证失败。

演示绑定自定义java类的导入模板

该示例实现为每个学校导入、更新行政单位及其负责人。Excel数据如下:

目标表结构如下:

其中学校代码,需要根据用户选择的值导入,唯一id需要根据学校代码和单位号组合而成,实现步骤如下:

1)新建扩展包“DataImporterDemo”,extension.xml中设置depends="DataImporter",独立开发扩展包时,为了正常编译,需要将DataImporter.jar放到扩展包的lib-compile目录(打包时不需要打包到扩展包)。

   

2)新建行政单位ID类“smartbi.demo.XZDWID”

package smartbi.demo;
import smartbi.daq.excelimport.ICellDataGenerator;
import smartbi.daq.excelimport.ImportFileConfig;
import smartbi.daq.excelimport.RowData;
import smartbi.net.sf.json.JSONObject;
import smartbi.util.StringUtil;
public class XZDWID implements ICellDataGenerator {
@Override
public String getCellValue(JSONObject colConfig, RowData rowData,
ImportFileConfig config) {
String xxdm = rowData.getCell("C_XXDM", true); //学校代码
String dwh = rowData.getCell("C_DWH"); //单位号
if(!StringUtil.isNullOrEmpty(xxdm) && !StringUtil.isNullOrEmpty(dwh)){
return xxdm + "_" + dwh;
}
return null;
}
}

2)新建Module类"smartbi.demo.DAQDemoModule",注册自定义JAVA类,名为"学校行政单位id"

package smartbi.demo;
import smartbi.daq.DataAcquisitionModule;
import smartbi.framework.IModule;
public class DAQDemoModule implements IModule{
/** */
private static DAQDemoModule instance = new DAQDemoModule();
/**
*
* @return DataAcquisitionModule
*/
public static DAQDemoModule getInstance() {
return instance;
}
@Override
public void activate() {
DataAcquisitionModule.getInstance().registerExcelCellDataGenerator("学校行政单位id","学校行政单位id" , new XZDWID());
}
}

4)加入DataImporterDemo扩展包,重新启动smartbi服务器。

5)点击新建Excel导入模板,添加导入规则。

6)点击列设置,设置列映射。其中的“唯一id”选择绑定JAVA类,并选择“学校行政单位id”。
点击保存,弹出保存对话框,输入名称“演示绑定自定义java类的导入模板”,保存excel导入模板。

7)双击该模板执行导入,选择“表1-3学校相关行政单位.xls”。

8)到数据库中看结果:
    

扩展接口说明

请见“Excel数据导入_类文档注释”。

附件:测试表及数据.sql

原始文档:http://pan.baidu.com/s/1miIn4SS