Apache POI或者是easyExcel都可以。
我用的easyexcel，用起来比较简单。
pom.xml添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.0</version>
</dependency>
读取Excel表格
**注意： **
1.Bean类一定要继承BaseRowMode！
-
index一定要按照Excel表格顺序来！
3.一定要写get set方法！
image.png
Bean类 : LoanInfo
public class LoanInfo extends BaseRowModel {
@ExcelProperty(value = "贷款编号", index = 0)
private long loanId;
@ExcelProperty(value = "用户编号", index = 1)
private String userId;
@ExcelProperty(value = "申请金额", index = 2)
private double applicationAmount;
@ExcelProperty(value = "申请天数", index = 3)
private int applicationdays;
@ExcelProperty(value = "申请时间", index = 4, format = "yyyy/MM/dd hh：mm：ss")
private Date applicationTime;
public long getLoanId() {
return loanId;
}
public void setLoanId(long loanId) {
this.loanId = loanId;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public double getApplicationAmount() {
return applicationAmount;
}
public void setApplicationAmount(double applicationAmount) {
this.applicationAmount = applicationAmount;
}
public int getApplicationdays() {
return applicationdays;
}
public void setApplicationdays(int applicationdays) {
this.applicationdays = applicationdays;
}
public Date getApplicationTime() {
return applicationTime;
}
public void setApplicationTime(Date applicationTime) {
this.applicationTime = applicationTime;
}
}
读Excel方法
注意：
字符流必须支持标记，FileInputStream 不支持标记，可以使用BufferedInputStream 代替。
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(...));
public static <T extends BaseRowModel> List<T> readExcel(final InputStream inputStream, final Class<? extends BaseRowModel> clazz) {
if (null == inputStream) {
throw new NullPointerException("the inputStream is null!");
}
AnalysisEventListener listener = new ExcelListener();
//读取xls 和 xlxs格式
//如果POI版本为3.17，可以如下声明
// ExcelReader reader = new ExcelReader(inputStream, null, listener);
//判断格式，针对POI版本低于3.17
ExcelTypeEnum excelTypeEnum = valueOf(inputStream);
ExcelReader reader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
reader.read(new com.alibaba.excel.metadata.Sheet(1, 1, clazz));
return (List<T>) ((ExcelListener) listener).getData();
}
ExcelListener类
public class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {
/**
* 自定义用于暂时存储data。
* 可以通过实例获取该值
*/
private final List<T> data = new ArrayList<>();
@Override
public void invoke(T object, AnalysisContext context) {
//数据存储
data.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<T> getData() {
return data;
}
}
valueOf方法
/**
* 根据输入流，判断为xls还是xlsx，该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
* 如果POI版本为3.17以下，则FileMagic会报错，找不到该类，此时去到POI 3.17中将FileMagic抽取出来
*/
public static ExcelTypeEnum valueOf(InputStream inputStream) {
try {
FileMagic fileMagic = FileMagic.valueOf(inputStream);
if (FileMagic.OLE2.equals(fileMagic)) {
return ExcelTypeEnum.XLS;
}
if (FileMagic.OOXML.equals(fileMagic)) {
return ExcelTypeEnum.XLSX;
}
throw new IllegalArgumentException("excelTypeEnum can not null");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
存Excel
注意：
1.Bean类一定要继承BaseRowMode！
- index一定要按照Excel表格顺序来！
3.一定要写get set方法！
Bean类 : ResultInfo
public class ResultInfo extends BaseRowModel {
@ExcelProperty(value = "单位（万卢比）", index = 1)
private String unit;
@ExcelProperty(value = "贷款编号", index = 2)
private String loanId;
@ExcelProperty(value = "当前状态", index = 3)
private String stateNow;
public String getUnit() {
return unit;
}
public void setUnit(String unit) {
this.unit = unit;
}
public String getLoanId() {
return loanId;
}
public void setLoanId(String loanId) {
this.loanId = loanId;
}
public String getStateNow() {
return stateNow;
}
public void setStateNow(String stateNow) {
this.stateNow = stateNow;
}
}
写Excel方法
/**
* 需要写入的Excel，有模型映射关系
*
* @param file 需要写入的Excel，格式为xlsx
* @param list 写入Excel中的所有数据，继承于BaseRowModel
*/
public static void writeExcel(final File file, List<? extends BaseRowModel> list) {
OutputStream out = null;
try {
out = new FileOutputStream(file);
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
//写第一个sheet, 有模型映射关系
Class t = list.get(0).getClass();
com.alibaba.excel.metadata.Sheet sheet = new com.alibaba.excel.metadata.Sheet(1, 0, t);
writer.write(list, sheet);
writer.finish();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
测试
//读Excel测试
public static void excelReadOperation() throws IOException {
// InputStream inputStream = getInputStream();
List<LoanInfo> listLoanInfo = new ArrayList<>();
File fileloaninfo = new File("F:\\Excel\\loan-info.xlsm");
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileloaninfo));
try {
listLoanInfo =readExcel(bis, LoanInfo.class);
} catch (Exception e) {
e.printStackTrace();
} finally {
bis.close();
}
}
//写Excel测试
public static void excelWriteOperation() throws IOException {
List<ResultInfo> listResultInfo = new ArrayList<>();
for (int j = 0; j < 5; j++) {
ResultInfo resultInfo = new ResultInfo();
listResultInfo.get(i).setUnit("单位"+j);
listResultInfo.get(i).setLoanId("贷款编号"+j);
listResultInfo.get(i).setStateNow(“当前状态”+j);
listResultInfo.add(resultInfo);
}
File file = new File("F:\\Excel\\从星合loan-info表生成的数据分析表.xlsx");
if (file.exists()) {
file.delete();
}
file.createNewFile();
writeExcel(file, listResultInfo);
}
