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.lanzoux.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/105823830
WriteHandler
示例代码中使用了样式的与列宽的 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;}
}
head
head 设置头信息,由于 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
- 示例
- Converter
- WriteHandler
- head
- 筛选列导出
- 示例
- Converter
- WriteHandler
- head
- 筛选列导出