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导入模板 。
Excel导入模板功能入口
创建/
管理excel中每个sheet页的导入规则，用于多次导入规则相同的excel数据。
一个excel文件对应一个excel导入模板，excel文件中的sheet页对应模板中的一个导入规则。
- 新建：
选择 新建分析 - 在“分析展现” 资源目录区现有文件夹下的更多操作，选择 新建 > Excel导入模板 。
- 执行导入：双击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导入模板 。
- 区域1："添加导入规则"点击可在左侧添加或删除导入规则。
- 区域2："上传Excel补录模板"，是指上传满足"导入规则列表区"规则的excel文件，用于用户下载补录模板或异常数据日志管理中异常数据下载。选择模板文件之后，必须点击"上传"才能保存补录模板，这里选择"表1-3学校相关行政单位补录模板.xls"上传。
- 区域3：导入规则编辑区域。
- 区域4：参数布局设置。
- 进入规则编辑界面
- 基本设置
2、进入Excel导入模板编辑界面
- 区域1为导入规则管理：“添加导入规则”点击可在左侧添加或删除导入规则。
- 区域2为Excel补录模板管理：需要上传Excel模板，以便补录用户可以下载模板并录入数据。
“上传Excel补录模板”，是指上传满足”导入规则列表区“规则的excel文件，用于用户下载补录模板或异常数据日志管理中异常数据下载。选择模板文件之后，必须点击”上传“才能保存补录模板，这里选择”表1-3学校相关行政单位补录模板.xls“上传。 - 区域3为导入规则设置：包括基本设置、列设置、扩展设置。
区域4为参数布局设置：可以对参数的位置进行调整。
3、进入规则编辑界面
- 基本设置
- 数据源：系统管理中定义的关系数据源，要导入的目标库。
- 回写表：要导入的目标表。
- Sheet名：对应Excel文件中要导入Sheet名称。
- 起始行：设置开始执行导入的行，包含设置的值，不设缺省从1开始。
- 行号：通过设置行号的形式设置起始行。
- 出现字符：通过设置Excel中指定列开始出现指定字符设置起始行。
- 结束行：设置结束导入的行，设置的结束行也会执行导入，缺省直到Excel结束。
- 行号：通过设置行号的形式设置结束行。
- 出现字符：通过设置Excel中指定列开始出现指定字符设置结束行。
- 直到空行：遇到空行终止导入。
- 异常处理规则：设置出现校验异常时（如长度超长，类型不匹配、超值域范围、主键冲突等），是回滚整个sheet的导入还是继续执行下一条数据，将这条异常数据写入异常日志表。这里的异常是指不满足用户设置的校验规则导致的异常，并非数据库sql执行过程中产生的未知异常，后者是直接回滚抛出错误原因给用户的。
回滚：设置为出现校验异常时，回滚整个sheet中数据的导入，并抛出碰到的第一个校验异常原因。选择了回滚，异常日志中并不会记录所有的异常数据及原因，因为回滚意味着遇到第一个校验异常时，直接回滚，跳出导入该sheet。
注意 若当前数据源不支持事务，则无法回滚数据，如SmartbiMPP、ClickHouse。导入文件时，系统会自动检测当前数据源是否支持事务，若不支持则会弹出提示窗告知用户，相应提示如下：
b. 继续执行：设置为出现校验异常时，继续执行导入下一条数据，并将这条数据写入到异常日志，供后面下载查看异常原因。
- 数据处理模式：导入数据的时候是采取增量更新还是全量更新。
- 全量：把数据库中的表中原有的数据删除，重新导入数据。
- 增量：在数据库中的表原有的数据基础下继续导入数据。
主键冲突处理规则：设置主键冲突时（是指Excel中数据出现和数据库表中的数据主键值一样时），是执行更新还是抛出主键冲突的校验异常，这里的"主键"是"列设置"中用户指定的主键，并不一定是数据库表的主键，但设置的主键也是需要数据满足唯一性。
- 更新：主键重复时，执行更新。
- 异常处理：主键重复时，抛出主键冲突的检验异常，由"异常处理规则"去处理这个异常。
a. 更新：主键重复时，执行更新。
b. 异常处理：主键重复时，抛出主键冲突的检验异常，由“异常处理规则”去处理这个异常。- 是否开启批量：用于配置处理数据库操作时是否使用数据库批处理，如果关闭批量，则会每次单独执行一条SQL，性能较差。当开启批量，则会每执行1000条SQL，或结束扫描Excel时提交一次事务，性能较好。
- 列设置
- 字段名和字段类型：根据基本设置中设置的表自动读取。
- 是否主键：缺省会自动从数据库读取，但用户可以更改，例如选择学校代码、单位号作为主键。
- 是否允许为空：缺省会自动从数据库读取，但用户可以更改。绑定输入：设置数据库表列的数据来源。
- 不绑定：执行导入时，该列不插入值。
- 绑定excel列：绑定excel中指定列数据。
- 绑定excel单元格：绑定excel中指定单元格数据。
- 绑定参数：绑定参数，该参数为公共设置中定义的参数，选择绑定参数后，执行导入时，导入界面会显示该参数，用户导入前需要先选择参数的值再执行导入，这样这列导入的值就是用户所选的参数值，这里学校代码选择参数"学校名称"，其中的学校名称参数是下拉框参数，数据源是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”。
- 使用固定值：绑定固定值，用户输入一个固定值。使用JAVA类：绑定java类，系统内置了唯一id和系统时间两个常用的类，用户可通过扩展包，实现接口smartbi.daq.excelimport.
- ICellDataGenerator，并注册java类，详细请见 演示绑定自定义java类的导入模板
- 。
- 唯一id：32位长的唯一id。
- 系统时间：执行导入时的系统时间。
- 。
- 按文件绑定
Sheet名：用于匹配的Sheet页名称，如果勾选了"作为补录模板"，该名称必须和Excel导入模板中配置的Sheet名相同。
- 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数据
双击Excel导入模板（或者右键 - 打开）进入导入excel数据界面，如"演示带参数的导入模板"，进入导入界面：
- 下载补录模板：下载模板编辑界面上传的补录模板。
- 学校名称：选择Excel数据所属学校，这是在Excel导入模板列设置中绑定的参数。
- 选择Excel文件：要导入的Excel数据文件。
- 导入：点击该按钮执行导入，显示导入等待界面，导入完成后再下方显示导入结果：
这个模板执行
如:http://localhost:8080/smartbi/vision/openresource.jsp?resid=I402881e7422f475c0146c3e43ec8035e&user=admin&password=manager
点击
保存 ，弹出“保存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数据文件。
- 导入：点击该按钮执行导入，显示导入等待界面，导入完成后再下方显示导入结果：
| 锚 | ||||
|---|---|---|---|---|
|
下面以新建"单位职能列表"为例，详细说明每一项的配置。
- 入口一：在"Excel数据导入"模块左侧资源目录区"值域范围"目录或子目录右键菜单选择 新建 > 值域范围。
- 入口二：在"Excel数据导入"模块右侧主界面，单击 新建值域范围。
下面以新建“单位职能列表”为例，详细说明每一项的配置。
1、点击新建值域范围，选择数据源northwind
- 入口一：在“系统导航栏”选择 公共设置，展开资源目录区，在“值域范围”或其节点下文件夹的更多操作
中选择 新建 > 值域范围。 - 入口二：在“公共设置”界面主菜单选择 值域范围。
2、进入值域范围范围编辑界面：
- 值域名称：值域的真实名称，保存后不可修改。
- 值域别名：值域的别名。
- 描述：值域的描述信息。
- 值域类型：SQL支持和静态列表，和参数的备选值设置、默认值的设置相同。
- SQL：通过SQL的方式设置值域的可选值列表。
- 静态列表：通过手动设置值列表，本例选择这种方式。
- 批量导入：选择静态列表后激活，可导入UTF-
- 8编码的文本格式文件，每一行代表一个值，如：
- 值：根据值域类型设置的不同，设置方式就不同，其中通过SQL方式设置值，请参见下拉框参数的备选值设置。
- 值字段：值域类型选择SQL后，显示，设置定义的sql中哪一列作为值域值。
3、点击保存，保存值域范围。
异常日志数据管理
查看导入异常日志，并可以导出导入异常的数据、及异常原因。
- 入口：在系统运维，单击 导入异常数据。
出现在异常数据日志中的前置条件：Excel导入模板中需要设置“异常处理规则”为 继续执行 。
应用示例
演示带参数的导入模板
请见“新建Excel导入模板
”。
| 锚 | ||||
|---|---|---|---|---|
|
值域范围的新建和下拉框参数的新建非常相像，但比下拉框参数更简单。
请见“新建值域范围
”。
演示带值域范围验证的导入模板
该示例实现为每个学校导入、更新行政单位及其负责人。Excel数据如下：
目标表结构如下：
其中学校代码，需要根据用户选择的值导入，单位职能只有：学生管理、教学管理、质量监控、其他四个可选值。实现步骤如下：
- 新建"单位职能列表"值域范围，详见"演示静态列表值域范围"。
- 点击新建Excel导入模板，添加导入规则。
- 点击列设置，设置列映射。
- 点击"单位职能"那一行的校验设置，设置值域范围校验，点击确定。
1、新建“单位职能列表”值域范围，详见“演示静态列表值域范围”。
2、点击新建Excel导入模板。
3、点击列设置，设置列映射。
4、点击“单位职能”那一行的校验设置，设置值域范围校验，点击 确定。
5、点击保存，弹出保存对话框，输入名称“演示带值域范围验证的导入模板”，保存excel导入模板。
6、双击“演示带值域范围验证的导入模板”执行导入，选择文件“表1-3学校相关行政单位 - 值域范围验证.
xls”验证值域范围校验。
- 下载异常数据
- 如果提示"下载异常数据失败，原因：模板文件不存在，请编辑模板，上传模板文件"。请重新编辑改Excel模板，上传模板文件。
然后点击右下角的保存，就会显示成：
上传模板后，关闭模板，点击"异常数据日志管理"，可以看到刚导入的异常数据。
- 否则可以看到导出的异常数据，显示值域范围验证失败。
7、下载异常数据
1）如果提示“下载异常数据失败”，原因：模板文件不存在，请编辑模板，上传模板文件。请重新编辑改Excel模板，上传模板文件。
2）然后点击右下角的保存，就会显示成：
3）上传模板后，关闭模板，点击“异常数据日志管理”，可以看到刚导入的异常数据。
4）点击“下载异常”图标，下载异常数据。
否则可以看到导出的异常数据，显示值域范围验证失败。
| 锚 | ||||
|---|---|---|---|---|
|
该示例实现为每个学校导入、更新行政单位及其负责人。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服务器。
点击保存，弹出保存对话框，输入名称"演示绑定自定义java类的导入模板"，保存excel导入模板。
5）点击新建Excel导入模板，添加导入规则。
6）点击列设置，设置列映射。其中的“唯一id”选择绑定JAVA类，并选择“学校行政单位id”。
点击保存，弹出保存对话框，输入名称“演示绑定自定义java类的导入模板”，保存excel导入模板。
7）双击该模板执行导入，选择“表1-3学校相关行政单位.
xls”。
8）到数据库中看结果：
扩展接口说明
| borderColor | #BBBBBB |
|---|---|
| bgColor | #F0F0F0 |
| borderWidth | 1 |
| borderStyle | solid |
