java读取Excel文件
来源：互联网 发布：表白源码 编辑：程序博客网 时间：2024/06/05 16:18
java读取Excel文件
本博文使用工具https://github.com/nvenky/excel-parser，其思想类似orm，数据源来于Excel文件，Excel文件
中的每一行对应java中的类实例，此工具使用注解绑定Excel每一行的中某列对应类的哪个属性（Hibernate也是这样做的，不过根据名字yin's，myabtis使用的是配置的方式）。
拿某公司的差表规则Excel文件举例：
我们可以实现其对应的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反射）我们可以自己实现。
感谢作者的小工具
阅读全文
0 0
- java读取excel文件
- java读取excel 文件
- java读取excel文件
- Java读取Excel文件
- java读取Excel文件
- java 读取excel 文件
- java读取Excel文件
- Java 读取Excel文件
- JAVA读取excel文件
- java读取Excel文件.
- java读取excel文件
- java读取Excel文件
- Java读取Excel文件
- Java读取Excel文件
- java读取excel文件
- java 读取excel文件
- java读取Excel文件
- java读取excel文件
- 深入理解Java类加载器(1)：Java类加载原理解析
- A10负载均衡器基本概念
- linux进入中文路径的方法
- 2015蓝桥杯决赛 居民集会(分治法)
- 作别硬件拼杀时代，PPTV电视能否靠“内容”决胜千里？
- java读取Excel文件
- Java--玩四叶玫瑰
- JS实现单选、全选、反选
- python 爬取12306验证码
- Java网络编程——第八章 客户端Socket
- 《高频交易》边读边学
- cf 808 A
- 链表六双向循环链表
- 2016背包专题1010
