EasyExcel的简单使用

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.7</version> </dependency>若是使用jar包方式请下载:https://hik.lanzous.com/i1qEtk5mvzi使用官方文档:https://www.yuque.com/easyexcel/doc/easyexcel一般操作官网都有,下列介绍不创建对象方式创建Excel,并且自定义列宽、单元格对齐方式。示例为导出表格其中代码设置样式参考:https://www.yuque.com/easyexcel/doc/write#W4u1e示例 response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")); ServletOutputStream out = response.getOutputStream(); // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); EasyExcel.write(out).registerConverter(new TimestampCovert()) .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(new FixedColumnWidthStyleStrategy()) .head(Utils.toTead(titles)) .sheet("sheet1").doWrite(list); out.flush();Converter由于表格数据list来自数据库,包含Timestamp类型数据,因此这里需要自己写了个转换器:import com.alibaba.excel.converters.Converter;import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.GlobalConfiguration;import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.sql.Timestamp;import java.text.SimpleDateFormat;public class TimestampCovert implements Converter<Timestamp> { @Override public Class supportJavaTypeKey() { return Timestamp.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public Timestamp convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { return Timestamp.valueOf(cellData.getStringValue()); } @Override public CellData convertToExcelData(Timestamp timestamp, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new CellData<>(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(timestamp)); }}详细可参考:https://blog.csdn.net/fsadkjl/article/details/105823830WriteHandler示例代码中使用了样式的与列宽的Handler,FixedColumnWidthStyleStrategy继承了AbstractColumnWidthStyleStrategy,代码如下:import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;import org.apache.poi.ss.usermodel.Cell;import java.util.List;public class FixedColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { private int columnWidth = 23; @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } public FixedColumnWidthStyleStrategy() { } public FixedColumnWidthStyleStrategy(int columnWidth) { this.columnWidth = columnWidth; }}headhead设置头信息,由于head是一个List<List<String>>集合,这里封装成方法,传表头集合进去生成合适的表头类型。 public static List<List<String>> toTead(List<String> titles) { List<List<String>> list = new ArrayList<List<String>>(); List<String> head = new ArrayList<String>(); for (String title : titles) { head.add(title); list.add(head); head = new ArrayList<String>(); } return list; }筛选列导出可以根据传入的需要显示的字段titles动态改变sql获取数据,若查出所有字段的话筛选如下: /** * 设置表格信息 * @param dataList 查询出的数据 * @param titles 需要显示的字段 * @return */ private static List<List<Object>> dataList(List<Object> dataList, List<String> titles) { List<List<Object>> list = new ArrayList<>(); for (Object person : dataList) { List<Object> data = new ArrayList<>(); for (String fieldName : titles) { /**通过反射根据需要显示的字段,获取对应的属性值*/ data.add(getFieldValue(fieldName, person)); } list.add(data); } return list; } /** * 根据传入的字段获取对应的get方法,如name,对应的getName方法 * @param fieldName 字段名 * @param person 对象 * @return */ private static Object getFieldValue(String fieldName, Object person) { try { String firstLetter = fieldName.substring(0, 1).toUpperCase(); String getter = "get" + firstLetter + fieldName.substring(1); Method method = person.getClass().getMethod(getter); return method.invoke(person); } catch (Exception e) { logger.error("使用反射获取对象属性值失败", e); return null; } }参考:https://my.oschina.net/zjiamin/blog/4710076