springboot+hutool工具+自定义注解方式实现数据导出excel

经网络整理,最终符合项目需求,记录一下。

废话不多说,直接上代码

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);