基于hutool封装的excel导出功能

需求中经常会用到excel导出，看了下hutool对poi的封装，感觉还不错。在此基础之上，封装一个excel导出注解。
先上用法，如图在controller层方法加上@CustomRespExcel注解即可。

	@GetMapping("/export/{id}")
	@CustomRespExcel(name = "示例信息",templatePath = "template/template01.xlsx",startRow = 2)
	public List<DemoExcelVO> export(@PathVariable("id") Long id) {
		return demoService.export(id);
	}

name表示导出的excel名称，templatePath表示依赖的excel模板（不填此参数会切换为非模板导出模式，表头由VO类注解信息解析而来）,startRow表示在模板模式下，从第几行开始写入数据。
@CustomRespExcel注解会把export方法返回的List当作数据源写入excel然后导出。

上具体的代码，一共6个类

注解类

import java.lang.annotation.*;

@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface CustomRespExcel {
	String name() default "";

	String templatePath() default "";

	int startRow() default 2;
}

注解值切面

import cn.hutool.core.lang.UUID;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

@Aspect
public class ExportParamAspect {

	@Before("@annotation(customRespExcel)")
	public void around(CustomRespExcel customRespExcel){
		String excelName = StringUtils.hasLength(customRespExcel.name()) ? customRespExcel.name() : UUID.fastUUID().toString();
		RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
		requestAttributes.setAttribute("__EXCEL_NAME_KEY__",excelName,0);
		requestAttributes.setAttribute("__EXCEL_TEMPPATH_KEY__", customRespExcel.templatePath(),0);
		requestAttributes.setAttribute("__EXCEL_STARTROW_KEY__", customRespExcel.startRow(),0);
	}
}

方法返回值处理器

import cn.hutool.core.collection.CollectionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.MethodParameter;
import org.springframework.web.context.request.NativeWebRequest;
import org.springframework.web.method.support.HandlerMethodReturnValueHandler;
import org.springframework.web.method.support.ModelAndViewContainer;

import javax.servlet.http.HttpServletResponse;
import java.util.List;


public class RespExcelReturnValueHandler implements HandlerMethodReturnValueHandler {

	@Autowired
	private ExcelWriteHandler excelWriteHandler;

	@Override
	public boolean supportsReturnType(MethodParameter parameter) {
		return parameter.getMethodAnnotation(CustomRespExcel.class) != null;
	}

	@Override
	public void handleReturnValue(Object returnValue, MethodParameter returnType, ModelAndViewContainer mavContainer, NativeWebRequest webRequest) throws Exception {
		if(returnValue == null || !(returnValue instanceof List)
				|| CollectionUtil.isEmpty(List.class.cast(returnValue)))
			throw new RuntimeException("@CustomRespExcel 返回值需要为List");
		HttpServletResponse response = webRequest.getNativeResponse(HttpServletResponse.class);
		mavContainer.setRequestHandled(true);
		excelWriteHandler.export(returnValue,response);
	}
}

excel处理类

import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;

public class ExcelWriteHandler {

	public void export(Object o, HttpServletResponse response) {
		RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
		String name = String.class.cast(requestAttributes.getAttribute("__EXCEL_NAME_KEY__", 0));
		String templatePath = String.class.cast(requestAttributes.getAttribute("__EXCEL_TEMPPATH_KEY__", 0));
		int startRow = Integer.class.cast(requestAttributes.getAttribute("__EXCEL_STARTROW_KEY__", 0));
		List Datas = List.class.cast(o);
		this.export(Datas,Datas.get(0).getClass(),response,name,templatePath,startRow);
	}

	private <T> void export(List<T> tList, Class<T> tClass,HttpServletResponse response,
								  String excelName,String templatePath,Integer startRow){
		ExcelWriter excelWriter;
		try {
			excelWriter = StringUtils.hasLength(templatePath) ? ExcelUtil.getReader(new ClassPathResource(templatePath).getInputStream()).getWriter():ExcelUtil.getWriter(true);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		if(StringUtils.hasLength(templatePath)){
			excelWriter.passRows(startRow-1).write(tList);
		}else{
			for(Field field : tClass.getDeclaredFields()){
				excelWriter.addHeaderAlias(field.getName(), field.getDeclaredAnnotation(ApiModelProperty.class).value());
			}
			excelWriter.setOnlyAlias(true).write(tList, true);
		}
		excelWriter.autoSizeColumnAll();
		try{
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(excelName+"-"+ DateUtil.today()+".xlsx", "utf-8"));
			excelWriter.flush(response.getOutputStream(), true);
		}catch (Exception e){
			throw new RuntimeException(e);
		}finally {
			excelWriter.close();
		}
	}
}

2个springboot配置类

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class RespExcelConfig {

	@Bean
	public ExportParamAspect excelParamAspect(){
		return new ExportParamAspect();
	}

	@Bean
	public RespExcelReturnValueHandler respExcelReturnValueHandler(){
		return new RespExcelReturnValueHandler();
	}


	@Bean
	public ExcelWriteHandler excelWriteHandler(){
		return new ExcelWriteHandler();
	}
}

import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.web.method.support.HandlerMethodReturnValueHandler;
import org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter;

import javax.annotation.PostConstruct;
import java.util.ArrayList;
import java.util.List;

@Import({RespExcelConfig.class})
@Configuration(
		proxyBeanMethods = false
)
@RequiredArgsConstructor
public class RespExcelAutoConfig {
	private final RequestMappingHandlerAdapter requestMappingHandlerAdapter;

	private final RespExcelReturnValueHandler respExcelReturnValueHandler;


	@PostConstruct
	public void setReturnValueHandlers() {
		List<HandlerMethodReturnValueHandler> returnValueHandlers = this.requestMappingHandlerAdapter.getReturnValueHandlers();
		List<HandlerMethodReturnValueHandler> newHandlers = new ArrayList();
		newHandlers.add(this.respExcelReturnValueHandler);
		newHandlers.addAll(returnValueHandlers);
		this.requestMappingHandlerAdapter.setReturnValueHandlers(newHandlers);
	}
}

模板存放位置

返回值VO类

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;


@Data
public class DemoExcelVO {

	@ApiModelProperty(value="id")
    private String id;

	@ApiModelProperty(value="parentId")
    private String parentId;

    /**
     * 用户名
     */
    @ApiModelProperty(value="用户名")
    private String username;

    /**
     * 密码
     */
    @ApiModelProperty(value="密码")
    private String password;

}

模板示例

运行示例图



非模板导出（表头字段由解析VO类注解值而来）

	@GetMapping("/export/{id}")
	@CustomRespExcel(name = "示例信息")
	public List<DemoExcelVO> export(@PathVariable("id") Long id) {
		return demoService.export(id);
	}

需要注意的点
1.模板导出的时候，读取模板文件要采用输入流的形式，如果以文件或者路径的形式，写入数据的时候会污染模板文件。
2.比较长的Long字段数据，输出到excel会被折叠为科学计数法表示，可以在VO类中把数据类型改为String。