Java列表数据导出Excel 详解

Java列表数据导出Excel 详解

1.导包

  <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--xls-->
        <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>

2.配置类

来源:https://www.cnblogs.com/JWMA/p/12768166.html

/**
 * @author
 * @version 1.0
 * @date 2021/6/18 15:39
 * ** 来源： https://www.cnblogs.com/JWMA/p/12768166.html
 */
@Slf4j
public class EasyExcelUtil {
    private static final String XLS_VAL = ".xls";
    private static final String XLS_X_VAL = ".xlsx";


    /**
     * 无模板写文件
     *
     * @param filePath
     * @param head     表头数据
     * @param data     表内容数据
     */
    public static void write(String filePath, List<List<String>> head, List<List<Object>> data) {
        EasyExcel.write(filePath).head(head).sheet().doWrite(data);
    }

    /**
     * 无模板写文件
     *
     * @param filePath
     * @param head      表头数据
     * @param data      表内容数据
     * @param sheetNo   sheet页号，从0开始
     * @param sheetName sheet名称
     */
    public static void write(String filePath, List<List<String>> head, List<List<Object>> data, Integer sheetNo, String sheetName) {
        EasyExcel.write(filePath).head(head).sheet(sheetNo, sheetName).doWrite(data);
    }

    /**
     * 根据excel模板文件写入文件
     *
     * @param filePath
     * @param templateFileName
     * @param headClazz
     * @param data
     */
    public static void writeTemplate(String filePath, String templateFileName, Class headClazz, List data) {
        EasyExcel.write(filePath, headClazz).withTemplate(templateFileName).sheet().doWrite(data);
    }

    /**
     * 根据excel模板文件写入文件
     *
     * @param filePath
     * @param templateFileName
     * @param data
     */
    public static void writeTemplate(String filePath, String templateFileName, List data) {
        EasyExcel.write(filePath).withTemplate(templateFileName).sheet().doWrite(data);
    }

    /**
     * 按模板写文件
     *
     * @param filePath
     * @param headClazz 表头模板
     * @param data      数据
     */
    public static void write(String filePath, Class headClazz, List data) {
        EasyExcel.write(filePath, headClazz).sheet().doWrite(data);
    }

    /**
     * 按模板写文件
     */
    public static void writesByData(String name, Class headClazz, List data, HttpServletResponse response) {
        try {
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode(name + System.currentTimeMillis() + ".xlsx", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), headClazz).autoCloseStream(Boolean.FALSE).sheet(name).doWrite(data);
        } catch (IOException 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());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }

    /**
     * 按模板写文件
     *
     * @param filePath
     * @param headClazz 表头模板
     * @param data      数据
     * @param sheetNo   sheet页号，从0开始
     * @param sheetName sheet名称
     */
    public static void write(String filePath, Class headClazz, List data, Integer sheetNo, String sheetName) {
        EasyExcel.write(filePath, headClazz).sheet(sheetNo, sheetName).doWrite(data);
    }

    /**
     * 按模板写文件
     *
     * @param filePath
     * @param headClazz    表头模板
     * @param data         数据
     * @param writeHandler 自定义的处理器，比如设置table样式，设置超链接、单元格下拉框等等功能都可以通过这个实现（需要注册多个则自己通过链式去调用）
     * @param sheetNo      sheet页号，从0开始
     * @param sheetName    sheet名称
     */
    public static void write(String filePath, Class headClazz, List data, WriteHandler writeHandler, Integer sheetNo, String sheetName) {
        EasyExcel.write(filePath, headClazz).registerWriteHandler(writeHandler).sheet(sheetNo, sheetName).doWrite(data);
    }
    
}

3.前端页面

4.Controller控制层

• ps:我这里是通过前端传递列表的数据id 然后去数据库查询数据
  

5.导出列表的对象类

• PS：
• @ColumnWidth(value = 25) 代表excel每一列列表的宽度
• @ExcelProperty(value = “编码”, index = 0) value是列表的名字 index是表示第一列

@Data
@ApiModel(value = "导出发票列表", description = "返回数据")
@ColumnWidth(value = 25)
public class BillStyleInvoiceListVo {

    @ApiModelProperty(value = "编码")
    @ExcelProperty(value = "编码", index = 0)
    private String fldCode;

    @ApiModelProperty(value = "名称")
    @ExcelProperty(value = "名称", index = 1)
    private String name;

    @ApiModelProperty(value = "状态")
    @ExcelProperty(value = "状态", index = 2)
    private Integer fldType;

    @ApiModelProperty(value = "最近修改人")
    @ExcelProperty(value = "最近修改人", index = 3)
    private String fldModifyUser;

    @ApiModelProperty(value = "最近修改时间")
    @ExcelProperty(value = "最近修改时间", index = 4, converter = DateConverter.class)
    private Date fldModifyDate;
}

6.业务层

    @Override
    public void exportBillList(BillStyleListModel billStyleListModel, HttpServletResponse response) {
        List<String> billId = billStyleListModel.getBillId();
        List<BillStyleInvoiceListVo> invoiceList = new ArrayList<>();
        billId.forEach(billObj->{
            //根据id查询对象数据
            EschargeBilltypeRc eschargeBilltypeRc=billStyleManageMapper.findBillType(billObj);
            //excel列表对象
            BillStyleInvoiceListVo invoiceListVo = new BillStyleInvoiceListVo();
            //把查询到的每个数据设置到列表对象中
            invoiceListVo.setFldCode(eschargeBilltypeRc.getFld_code());
            invoiceListVo.setName(eschargeBilltypeRc.getFld_name());
            invoiceListVo.setFldType(eschargeBilltypeRc.getFld_active());
            invoiceListVo.setFldModifyUser(eschargeBilltypeRc.getFld_modify_user());
            invoiceListVo.setFldModifyDate(eschargeBilltypeRc.getFld_modify_date());
            invoiceList.add(invoiceListVo);
          });
      EasyExcelUtil.writesByData("票据类型发票列表", BillStyleInvoiceListVo.class, invoiceList, response);   
    }

7.测试一下(大功告成)