一、概述
在日常java开发中，我们常用的excel处理工具为Apache POI或EasyExcel，EasyExcel相对于Apache POI就是简单易学，以下是工作总结的一些使用方法
二、EasyExcel导出
2.1 有模板导出
public class EasyExcelUtil {
@Data
@ColumnWidth(20) // 定义列宽
public static class TestVO{
@ExcelProperty( value = "姓名",index = 0)
private String name;
@ExcelProperty( value = "年龄",index = 1)
private int age;
@ExcelProperty( value = "学校",index = 2)
private String school;
}
/**
* 使用 模型 来写入Excel
*
* @param outputStream Excel的输出流
* @param dataList 要写入的以 模型 为单位的数据
* @param clazz 模型的类
*/
public static void writeExcelWithModel(OutputStream outputStream, String sheetName, List<? extends Object> dataList, Class<? extends Object> clazz) {
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).autoTrim(true).autoCloseStream(true).excelType(ExcelTypeEnum.XLSX).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build();
excelWriter.write(dataList,writeSheet);
}
public static void main(String[] args) throws Exception {
OutputStream outputStream = null;
outputStream = new FileOutputStream(new File("E:\\6.xls"));
List<TestVO> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
TestVO testVO = new TestVO();
testVO.setAge(i + 20);
testVO.setName("vo" + i);
testVO.setSchool("school" + i);
dataList.add(testVO);
}
String sheetName = "导出文件";
writeExcelWithModel(outputStream, sheetName, dataList, TestVO.class);
}
}
2.2 无模板导出
2.2.1 泛型参数类
@Data
public class ExportExcelParam<T> extends BaseDTO {
/**
* 查询参数
*/
private T queryParam;
/**
* 导出标题
*/
private ArrayList<String> titleList;
/**
* 导出文件名字
*/
private String fileName;
}
2.2.2 导出接口
//
public interface ExportExcel<T> {
/**
* 查询
*
* @param param
* @return
*/
List<List<Object>> queryExportData(ExportExcelParam<T> param);
/**
* 获取多语言列名
*
* @param param
* @return
*/
List<String> getMultilingualHeader(ExportExcelParam<T> param);
/**
* 开始导出
* @param param
* @param response
*/
void exportStart(ExportExcelParam<T> param, HttpServletResponse response)throws IOException;
// @Override
// public void exportStart(ExportExcelParam<VendorAssesForm> vendorAssesFormDto, HttpServletResponse response) throws IOException {
// // 获取导出的数据
// List<List<Object>> dataList = queryExportData(vendorAssesFormDto);
// // 标题
// List<String> head = getMultilingualHeader(vendorAssesFormDto);
// // 文件名
// String fileName = vendorAssesFormDto.getFileName();
// // 开始导出
// EasyExcelUtil.exportStart(response, dataList, head, fileName);
// }
}
2.2.3 工具类
public class EasyExcelUtil {
/**
* @param outputStream 输出流
* @param sheetName 定义sheet名称
* @param headList sheet表头
* @param lineList sheet行数据
*/
public static void writeExcel(OutputStream outputStream,String sheetName,List<String> headList,List<List<Object>> lineList){
List<List<String>> list = new ArrayList<>();
if(headList != null){
headList.forEach(h -> list.add(Collections.singletonList(h)));
}
EasyExcel.write(outputStream).head(list).sheet(sheetName).doWrite(lineList);
}
/**
* 获取输出流
* @param response
* @param fileName
* @return
* @throws IOException
*/
public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) throws IOException {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
}
/**
* 开始导出
* @param response
* @param dataList
* @param head
* @param fileName
* @throws IOException
*/
public static void exportStart(HttpServletResponse response, List<List<Object>> dataList, List<String> head, String fileName) throws IOException {
// 获取输出流
ServletOutputStream outputStream = EasyExcelUtil.getServletOutputStream(response, fileName);
// 导出文件
EasyExcelUtil. writeExcel(outputStream, fileName ,head,dataList);
}
}
2.2.4 使用示例
pubile class EasyExcelTest implements ExportExcel< TestVO >{
@Override
public List<List<Object>> queryExportData(ExportExcelParam<TestVO> testVO) {
// 编写业务
//注意返回数据类型List<List<Object>>里面的Object最好为String类型，例如Date类型会出错
}
@Override
public List<String> getMultilingualHeader(ExportExcelParam<TestVO> testVO) {
// 编写业务
}
@Override
public void exportStart(ExportExcelParam<TestVO> testVO, HttpServletResponse response) throws IOException {
// 获取导出的数据
List<List<Object>> dataList = queryExportData(testVO);
// 标题
List<String> head = getMultilingualHeader(testVO);
// 文件名
String fileName = vendorImproveFormDto.getFileName();
// 开始导出
EasyExcelUtil.exportStart(response, dataList, head, fileName);
}
}
三、EasyExcel导入
3.1有模型导入
public class EasyExcelUtil {
@Data
public static class TestVO{
@ExcelProperty( value = "姓名",index = 0)
private String name;
@ExcelProperty( value = "年龄",index = 1)
private int age;
@ExcelProperty( value = "学校",index = 2)
private String school;
}
/**
* 使用 模型 来读取Excel
* @param fileInputStream Excel的输入流
* @param clazz 模型的类
* @return 返回 模型 的列表(为object列表,需强转)
*/
public static List<Object> readExcelWithModel(InputStream fileInputStream, Class<? extends Object> clazz) throws IOException{
ExcelListener excelListener = new ExcelListener<>();
ExcelReader excelReader = EasyExcel.read(fileInputStream, clazz, excelListener).autoTrim(true).autoCloseStream(true).build();
ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(1).build();
excelReader.read(readSheet);
return excelListener.getDatas();
}
public static void main(String[] args) throws Exception {
String filePath = "D:\\123.xlsx";
InputStream inputStream = null;
inputStream = new FileInputStream(new File(filePath));
List<Object> list = readExcelWithModel(inputStream, TestVO.class);
list.forEach((user)->{
TestVO user1 = (TestVO)user;
System.out.println(user1.getName()+", "+user1.getAge()+", "+user1.getSchool());
});
}
}
3.2 坑
模板导入的字段类型如果是
int，但导入表格的却不是数字，导入就会报数字转换异常，建议字段类型都用String类型，导入校验格式后再转
四、自定义导出样式
请转至文章EasyExcel自定义导出样式
