easyexcel 简单导出

1引入pom文件

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

2. 创建实体类，和excel数据对应

mport com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;


@Data
@HeadRowHeight(30)
@ColumnWidth(40)
@ContentRowHeight(20)
@HeadFontStyle(bold = BooleanEnum.TRUE)
public class RecordVo implements Serializable {
	private static final long serialVersionUID = 1L;
	
	
	/**
	 * id主键
	 */
	@ExcelIgnore
	private String id;
 	
	
	@ExcelIgnore
	private String code;
 	

	@ExcelProperty(value = "名称", index = 0)
	private String name;
 
	@ExcelProperty(value = "阈值", index = 3)
	private String range;
 	
	
	@ExcelProperty(value = "指标额", index = 1)
	private String targetValue;
 	
 	
	/**
	 * 创建时间
	 */
	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
	@ExcelProperty(value = "统计时间", index = 2)
	private Date creationDate;
 	
	/**
	 * 时间戳
	 */
	@ExcelIgnore
	private String ts;
 		
}

常用注解有：
@ExcelProperty 指定当前字段对应excel中的哪一列。可以根据名字或者Index去匹配。当然也可以不写，默认第一个字段就是index=0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的。
@ExcelIgnore EasyExcel默认所有字段都会和excel去匹配，加了这个注解会忽略该字段
@DateTimeFormat 日期转换，用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat
@NumberFormat 数字转换，用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat

3、 普通导出

 @PostMapping(value = "/exportRecord")
    public void exportRecord(HttpServletResponse response, @RequestBody RecordCondition condition){
       try {
            //从数据库查询导出结果
           List<RecordVo> recordList = glbPrewarnTargetRecordService.queryPrewarnRecordList(condition);
           //设置文件名
           String fileName="test"
           ExcelUtil.export(response, fileName, recordList, RecordVo.class);
       }catch (Exception e){
           log.info("导出接口异常");
       }
    }

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.cpit.ycyt.common.core.util.JsonUtil;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * Excel工具类
 *
 */
@Slf4j
@Component
public class ExcelUtil {
    /**
     * 最大sheet页条数
     */
    private static final int MAX_COUNT = 100000;
    static ExcelUtil excelUtil;

    /**
     * 报表导出
     *
     * @param response httpResponse
     * @param fileName 文件名
     * @param data     导出数据
     * @param clazz    数据实体类
     * @throws IOException
     */
    public static <T> void export(HttpServletResponse response, String fileName, List<T> data, Class<T> clazz) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        try {
            // 前端判断Header为File-type时，进行单独处理
            response.setHeader("File-type", "xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename=" + name );
            int count = data.size();
            if (count <= MAX_COUNT) {
                EasyExcel.write(outputStream, clazz)
                        // 设置自动列宽
                        .registerWriteHandler(new CustomRowWriteHandler())
                        .sheet("sheet1").doWrite(data);
            } else {
                ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
                List<List<T>> lists = Lists.partition(data, MAX_COUNT);
                for (int i = 0; i < lists.size(); i++) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(i + 1).head(clazz)
                            .registerWriteHandler(new CustomRowWriteHandler()).build();
                    excelWriter.write(lists.get(i), writeSheet);
                }
                excelWriter.finish();
            }
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>(16);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JsonUtil.toJSONString(map));
        } finally {
            outputStream.flush();
            response.getOutputStream().close();
        }
    }
}

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;


/**
 * 自定义EasyExcel Row，用于在每一行前添加序号列
 *
 * @author lijie
 */
public class CustomRowWriteHandler implements RowWriteHandler {


    /**
     * 一定将样式设置成全局变量
     * 首行只需要创建一次样式就可以 不然每行都创建一次 数据量大的话会保错
     * 异常信息：The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
     */
    private CellStyle firstCellStyle;


    /**
     * 列号
     */
    private int count = 0;


    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
                                Integer relativeRowIndex, Boolean isHead) {
    }


    /**
     * 行创建后执行此方法
     */
    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                               Integer relativeRowIndex, Boolean isHead) {
        Cell cell = row.createCell(0);
        if (firstCellStyle == null) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            firstCellStyle = CellStyleUtil.firstCellStyle(workbook);
        }
        cell.setCellStyle(firstCellStyle);
        //设置列宽  0列   10个字符宽度
        writeSheetHolder.getSheet().setColumnWidth(0, 20 * 256);
        if (row.getRowNum() == 0) {
            cell.setCellValue("序号");
            return;
        }
        cell.setCellValue(++count);
    }


    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {
    }


}

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;


/**
 * 列样式工具
 * 1.该工具实现设置了表格头的样式和表格内容的样式
 * 2.该工具类设置的样式建议和Test实体类中的样式相同
 * 3.该工具类设置的样式就是为了给自定义序号列使用
 * 
 */
public class CellStyleUtil {
    
    /**
     * excel首列序号列样式
     * @param workbook
     * @return
     */
    public static CellStyle firstCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        //文字
        Font font = workbook.createFont();
        font.setBold(Boolean.TRUE);
        cellStyle.setFont(font);
        return cellStyle;
    }
}