java读取Excel文件
本博文使用工具https://github.com/nvenky/excel-parser,其思想类似orm,数据源来于Excel文件,Excel文件
中的每一行对应java中的类实例,此工具使用注解绑定Excel每一行的中某列对应类的哪个属性(Hibernate也是这样做的,不过根据名字yin's,
myabtis使用的是配置的方式)。
拿某公司的差表规则Excel文件举例:
来源 | 费用类型 | APPLY_POINT | ACTION | RULE-RESULT | CURR | RULE | Warning Message | Remark Message | Memo | id |
微民 | 住宿费 | ER_SUBMIT | REJECT | TRUE | RMB | {EXP_LEVEL}IN[1] AND {ER_EXP_FEE}>{ER_DAYS} *600 | 您的<EXPENSE_TYPE>(<行号>)不符合标准 | 您的房费高于公司标准 | 8a24ef78-d1c4-4c7c-924f-f199fa3c5eb1 | |
微民 | 住宿费 | ER_SUBMIT | REJECT | TRUE | RMB | {EXP_LEVEL}IN[2] AND {ER_EXP_FEE}>{ER_DAYS}*700 | 您的<EXPENSE_TYPE>(<行号>)不符合标准 | 您的房费高于公司标准 | f9b17610-7917-4f71-b369-602224fdd20d | |
微民 | 住宿费 | ER_SUBMIT | REJECT | TRUE | RMB | {EXP_LEVEL}IN[3] AND {ER_EXP_FEE}>{ER_DAYS}*800 | 您的<EXPENSE_TYPE>(<行号>)不符合标准 | 您的房费高于公司标准 | 128e621a-832a-4876-81c8-a5a9624b6ca7 | |
微民 | 差旅补贴 | ER_SUBMIT | REJECT | TRUE | RMB | {ER_LOCATION_COUNTRY}IN[中国] AND {ER_EXP_FEE}>{ER_DAYS}*140 | 您的<EXPENSE_TYPE>(<行号>)不符合标准 | 您的补贴高于公司标准 | 06d1afc9-4a0e-4ad6-b0b1-01af068997fe |
我们可以实现其对应的java类:
package com.beaver.drools.exampl.travel.rule;
import org.javafunk.excelparser.annotations.ExcelField;
import org.javafunk.excelparser.annotations.ExcelObject;
import org.javafunk.excelparser.annotations.ParseType;
/**
* Created by beaver on 2017/5/20.
*/
@ExcelObject(parseType = ParseType.ROW, start = 2, end = 61)
public class TravelRule {
//公司名称
@ExcelField(position = 1)
private String companyName;
//费用类型
@ExcelField(position = 2)
private String expenseType;
//规则应用场景
@ExcelField(position = 3)
private String applyPoint;
//规则结果
@ExcelField(position = 4)
private String ruleAction;
//规则内容
@ExcelField(position = 7)
private String rule;
//提示信息
@ExcelField(position = 9)
private String alertMessage;
//规则ID
@ExcelField(position = 11)
private String ruleId;
//解析出的drools规则内容
private String droolsRule;
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getExpenseType() {
return expenseType;
}
public void setExpenseType(String expenseType) {
this.expenseType = expenseType;
}
public String getRule() {
return rule;
}
public void setRule(String rule) {
this.rule = rule;
}
public String getApplyPoint() {
return applyPoint;
}
public void setApplyPoint(String applyPoint) {
this.applyPoint = applyPoint;
}
public String getRuleAction() {
return ruleAction;
}
public void setRuleAction(String ruleAction) {
this.ruleAction = ruleAction;
}
public String getAlertMessage() {
return alertMessage;
}
public void setAlertMessage(String alertMessage) {
this.alertMessage = alertMessage;
}
public String getRuleId() {
return ruleId;
}
public void setRuleId(String ruleId) {
this.ruleId = ruleId;
}
public String getDroolsRule() {
return droolsRule;
}
public void setDroolsRule(String droolsRule) {
this.droolsRule = droolsRule;
}
@Override
public String toString() {
return "TravelRule{" +
"companyName='" + companyName + '\'' +
", expenseType='" + expenseType + '\'' +
", applyPoint='" + applyPoint + '\'' +
", ruleAction='" + ruleAction + '\'' +
", rule='" + rule + '\'' +
", alertMessage='" + alertMessage + '\'' +
", ruleId='" + ruleId + '\'' +
", droolsRule='" + droolsRule + '\'' +
'}';
}
}
利用此工具转换:
package com.beaver.drools.exampl.travel.rule;
import com.beaver.drools.util.KieSessionUtil;
import com.google.gson.Gson;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.javafunk.excelparser.SheetParser;
import org.kie.api.io.ResourceType;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;
/**
* Created by beaver on 2017/5/20.
*/
public class TravleRuelTest {
static String packageName = "package com.beaver.drools.exampl.travel.rule";
static List<String> importPackages = Arrays.asList(
"import java.math.*"
, "import com.beaver.drools.common.model.*");
static String sheetName = "工作表1";
static String fileName = "腾讯差标规则1.xlsx";
static String objectType = "MatchInvoiceTravelRuleRequestDto";
static String objectVar = "$invoice";
static String resultPropertyName = "result";
static String alertMessagePropertyName = "remarkMessage";
//规则内容属性别名,如{EXP_LEVEL}IN[一般员工],EXP_LEVEL 可以起别名
static Map<String, String> rulePropertyNameAlias = new HashMap<>();
static {
rulePropertyNameAlias.put("\\{.*LEVEL.*\\}", "EXPENSE_LEVEL");
rulePropertyNameAlias.put("\\{.*LOCATION.*\\}", "city");
rulePropertyNameAlias.put("\\{.*FEE.*\\}", "INVOICE_AMOUNT");
rulePropertyNameAlias.put("\\{.*DAYS.*\\}", "days");
}
public static void main(String[] args) throws IOException {
//Get the sheet using POI API.
SheetParser parser = new SheetParser();
InputStream inputStream = TravleRuelTest.class.getClassLoader().getResourceAsStream(fileName);
Sheet sheet = new XSSFWorkbook(inputStream).getSheet(sheetName);
//Invoke the Sheet parser.
List<TravelRule> entityList = parser.createEntity(sheet, TravelRule.class, t -> t.printStackTrace());
//
entityList = entityList.stream()
.filter(t -> t.getCompanyName() != null && !t.getCompanyName().isEmpty())
.collect(Collectors.toList());
System.out.println(new Gson().toJson(entityList));
}
}
[
{
"companyName": "微民",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[1] AND {ER_EXP_FEE}>{ER_DAYS} *600",
"alertMessage": "您的房费高于公司标准",
"ruleId": "8a24ef78-d1c4-4c7c-924f-f199fa3c5eb1"
},
{
"companyName": "微民",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[2] AND {ER_EXP_FEE}>{ER_DAYS}*700",
"alertMessage": "您的房费高于公司标准",
"ruleId": "f9b17610-7917-4f71-b369-602224fdd20d"
},
{
"companyName": "微民",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[3] AND {ER_EXP_FEE}>{ER_DAYS}*800",
"alertMessage": "您的房费高于公司标准",
"ruleId": "128e621a-832a-4876-81c8-a5a9624b6ca7"
},
{
"companyName": "微民",
"expenseType": "差旅补贴",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{ER_LOCATION_COUNTRY}IN[中国] AND {ER_EXP_FEE}>{ER_DAYS}*140",
"alertMessage": "您的补贴高于公司标准",
"ruleId": "06d1afc9-4a0e-4ad6-b0b1-01af068997fe"
},
{
"companyName": "微民",
"expenseType": "差旅补贴",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{ER_LOCATION_COUNTRY}NOT IN[中国] AND {ER_EXP_FEE}>{ER_DAYS}*400",
"alertMessage": "您的补贴高于公司标准",
"ruleId": "922d99ff-c5bc-4f26-a365-d8b6e8a9a999"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[一般员工] AND {ER_EXP_FEE}>{ER_DAYS}*400",
"alertMessage": "您的房费高于公司标准",
"ruleId": "7c170ce0-080e-499a-9046-0dc7d226b86c"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[一般员工] AND {ER_LOCATION}NOT IN[北京/上海/深圳] AND {ER_EXP_FEE}>{ER_DAYS}*300",
"alertMessage": "您的房费高于公司标准",
"ruleId": "6135e677-867b-41ed-8d00-7512b5ec5c2a"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[总监] AND {ER_EXP_FEE}>{ER_DAYS}*550",
"alertMessage": "您的房费高于公司标准",
"ruleId": "636ebfda-6d78-4ec7-a441-234ff931fa57"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[总监] AND {ER_LOCATION}NOT IN[北京/上海/深圳] AND {ER_EXP_FEE}>{ER_DAYS}*400",
"alertMessage": "您的房费高于公司标准",
"ruleId": "b236a113-e547-4f65-9ddc-3b9549efa6ce"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[总裁] AND {ER_EXP_FEE}>{ER_DAYS}*700",
"alertMessage": "您的房费高于公司标准",
"ruleId": "8bb7e480-bf94-4c24-a46e-963ae60bee7e"
},
{
"companyName": "腾南",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[总裁] AND {ER_LOCATION}NOT IN[北京/上海/深圳] AND {ER_EXP_FEE}>{ER_DAYS}*500",
"alertMessage": "您的房费高于公司标准",
"ruleId": "f74d0d7c-d31d-49d9-84ff-c0889bf37771"
},
{
"companyName": "腾南",
"expenseType": "差旅补贴",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{ER_EXP_FEE}>{ER_DAYS}*80",
"alertMessage": "您的补贴高于公司标准",
"ruleId": "154866b0-fdc5-433f-9c3f-640e9a812564"
},
{
"companyName": "腾新",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}IN[总裁] AND {ER_EXP_FEE}>{ER_DAYS}*600",
"alertMessage": "您的房费高于公司标准",
"ruleId": "e4ece4c7-f9ca-46fc-bb7b-2a4443513daf"
},
{
"companyName": "腾新",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}NOT IN[总裁] AND {ER_EXP_FEE}>{ER_DAYS}*500",
"alertMessage": "您的房费高于公司标准",
"ruleId": "51768055-8a13-4d44-9c61-e261bcb74149"
},
{
"companyName": "腾新",
"expenseType": "住宿费",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{EXP_LEVEL}NOT IN[总裁] AND {ER_LOCATION}NOT IN[北京/上海/深圳/成都/广州/杭州] AND {ER_EXP_FEE}>{ER_DAYS}*400",
"alertMessage": "您的房费高于公司标准",
"ruleId": "0d3f86b6-f937-4544-916d-8fac5061c479"
},
{
"companyName": "腾新",
"expenseType": "差旅补贴",
"applyPoint": "ER_SUBMIT",
"ruleAction": "REJECT",
"rule": "{ER_EXP_FEE}>{ER_DAYS}*100",
"alertMessage": "您的补贴高于公司标准",
"ruleId": "a9c0f423-7880-4f86-a761-ba2f2ec83c66"
}
]
excel-parser的具体使用文档见官方地址。不过,此工具利用orm思想大大简化了我们的Excel文件向java实体类转换的工作量,也带有点小瑕疵。不过,知道其背后原理(excel读取,java反射)我们可以自己实现。
感谢作者的小工具