excel和csv文件导出

       这里做一个excel和csv以流的形式导出到客户端的案例，即为可以在postman端直接调用可下载的。这里我用的是第三方依赖是hutool，里面有可直接使用的工具方法。其maven依赖如下：

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.7</version>
</dependency>

1、Excel

首先是excel，我们一般一行excel是一个对象，那多条数据就是一个集合，先定义一个对象：

package cn.coralglobal.model.vo;

import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * @Description:
 * @Author chenjianwen
 * @Date 2020/8/4
 **/
@Data
public class IncomeExportVo implements Serializable {

    private static final long serialVersionUID = 63572446880062722L;
    /**
     * 创建日期
     */
    private String gmtCreate;
    /**
     * 平台
     */
    private String platform;
    /**
     * 收款账号
     */
    private String foreignBankAccount;
    /**
     * 入账流水号
     */
    private String incomeId;
    /**
     * 入账金额
     */
    private BigDecimal money;
    /**
     * 币种
     */
    private String currency;
    /**
     * 可用余额
     */
    private BigDecimal leftMoney;
}

下面是导出excel接口代码：
 

@PostMapping("/records/income/export")
public HttpServletResponse exportIncomeRecord(HttpServletResponse response) throws IOException {
        List<IncomeExportVo> voList = recordService.incomeExport(ie);
        //定义一个输出对象
        ExcelWriter writer = ExcelUtil.getWriter();
        //设置excel单元格大小
        writer.setColumnWidth(0, 40);
        writer.setColumnWidth(1, 35);
        writer.setColumnWidth(2, 30);
        writer.setColumnWidth(3, 20);
        writer.setColumnWidth(4, 10);
        writer.setColumnWidth(5, 20);
        //writer.setColumnWidth(6, 20);
        writer.setColumnWidth(6, 30);
        //设置excel单元格标题
        writer.merge(6, "入账记录表");
        //填充excel单元格数据
        if(voList != null && voList.size() > 0){
            writer.addHeaderAlias("incomeId", "入账流水号");
            writer.addHeaderAlias("foreignBankAccount", "收款银行账号");
            writer.addHeaderAlias("platform", "平台");
            writer.addHeaderAlias("money", "入账金额");
            writer.addHeaderAlias("currency", "币种");
            writer.addHeaderAlias("leftMoney", "可用余额");
            //writer.addHeaderAlias("status", "状态");
            writer.addHeaderAlias("gmtCreate", "创建日期");
            writer.write(voList, true);
        }else{
            writer.writeCellValue(0, 1, "入账流水号");
            writer.writeCellValue(1, 1, "收款银行账号");
            writer.writeCellValue(2, 1, "平台");
            writer.writeCellValue(3, 1, "入账金额");
            writer.writeCellValue(4, 1, "币种");
            writer.writeCellValue(5, 1, "可用余额");
            writer.writeCellValue(6, 1, "创建日期");
        }
        //定义字体和标题行宽度
        Font font = writer.createFont();
        font.setFontHeight((short) 250);
        writer.getStyleSet().setFont(font, true);
        writer.setRowHeight(0, 20);
        writer.setRowHeight(1, 20);
        //定义response对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=incomeRecord.xls");
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
        return response;
    }

2、Csv

@RequestMapping("/index")
public HttpServletResponse export(HttpServletResponse response) throws Exception {
        //设置分隔符
        CsvWriteConfig csvWriteConfig = new CsvWriteConfig();
        csvWriteConfig.setFieldSeparator('|');
        //创建csv"写对象"
        CsvWriter writer = new CsvWriter(new OutputStreamWriter(response.getOutputStream(), CharsetUtil.CHARSET_GBK), csvWriteConfig);
        //设置response
        response.setContentType("application/csv;charset=gbk");
        response.setHeader("Content-Disposition", "attachment; filename=exported.csv");
        //将数据写入到csv"写对象"中
        writer.write(
                new String[] {"a1", "b1", "c1"},
                new String[] {"a2", "b2", "c2"},
                new String[] {"a3", "b3", "c3"}
        );
        return response;
    }