经网络整理,最终符合项目需求,记录一下。
废话不多说,直接上代码
1.maven依赖
<!--hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.5</version>
</dependency>
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2. 工具类
public class ExcelExportUtil {
public static void downLoad(Class clazz, List<?> list, String fileName, String sheetName, HttpServletResponse response) throws Exception {
if (!list.get(0).getClass().equals(clazz)) {
throw new RuntimeException("数据类型与传入的集合数据类型不一致!");
} else {
ExcelWriter writer = ExcelUtil.getWriter();
// 获取当前类字段
Field[] fields = clazz.getDeclaredFields();
// 字段名称集合
List<String> fieldNames = new ArrayList<>();
// 字段中文名称集合(获取实体中@ExcelColumnTitle注解name的值)
List<String> cnNames = new ArrayList<>();
for (Field field : fields) {
if (!field.isAccessible()) {
// 关闭反射访问安全检查,为了提高速度
field.setAccessible(true);
}
// 判断是否有@ExcelColumnTitle注解
boolean annotationPresent = field.isAnnotationPresent(ExcelColumnTitle.class);
if (annotationPresent) {
fieldNames.add(field.getName());
ExcelColumnTitle annotation = field.getAnnotation(ExcelColumnTitle.class);
String name = annotation.name();
cnNames.add(name);
}
}
String[] fs = fieldNames.toArray(new String[0]);
String[] ns = cnNames.toArray(new String[0]);
for (int i = 0; i < ns.length; i++) {
// 设置表头及字段名
writer.addHeaderAlias(fs[i], ns[i]);
}
Workbook workbook = writer.getWorkbook();
StyleSet styleSet = new StyleSet(workbook);
// 自动换行
//styleSet.setWrapText();
styleSet.setAlign(HorizontalAlignment.LEFT, VerticalAlignment.CENTER);
CellStyle cellStyle = styleSet.getHeadCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
writer.setStyleSet(styleSet);
//sheet命名
writer.renameSheet(sheetName);
//单独设置第一行的高度
writer.setRowHeight(0, 40);
//设置列宽
for (int i = 0; i < fieldNames.size(); i++) {
writer.setColumnWidth(i, 23);
}
//开始写数据
writer.write(list, true);
ServletOutputStream out = null;
try {
assert response != null;
out = response.getOutputStream();
response.setContentType("application/x-msdownload;charset=utf-8");
String ecodeFileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + ecodeFileName + ".xls");
writer.flush(out, true);
writer.close();
} catch (IOException e) {
log.error(e.getMessage());
e.printStackTrace();
} finally {
IoUtil.close(out);
}
}
}
}
3.自定义注解类
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumnTitle {
/**
* 名称
*/
String name() default "";
}
4.业务数据实体类
@Data
public class EmpBo implements Serializable {
private static final long serialVersionUID = 1241274476889052983L;
@ExcelColumnTitle(name = "设备名称")
private String name;
@ExcelColumnTitle(name = "所属组织")
private String orgName;
@ExcelColumnTitle(name = "设备状态")
private Integer status;
public String getStatus() {
return ObjectUtil.equals(this.status, 1) ? "启用" : "关闭";
}
}
5.调用
List<EmpBo> allBos = new ArrayList<>();
//填充业务数
//
ExcelExportUtil.downLoad(EmpBo.class, allBos, "测试","测试", response);