java中使用cn.hutool.poi.excel导出Excel

 如果需要导出复杂的单元格，合并单元格，请查看 

Java中导出Excel，合并单元格，简单上手_SUMMERENT的博客-CSDN博客

目录

 1、引入maven依赖

2、 编写ExportExcelVo

3、编写ExportUtil导出数据工具，支持xlsx和csv两种

4、编写ExcelService层，准备数据

5、编写controller层，调用接口

6、测试导出

---

 1、引入maven依赖

        
            cn.hutool
            hutool-all
            5.3.8
        
        
            cn.jimmyshi
            bean-query
            1.1.5
        
        
            commons-io
            commons-io
            2.6
        

注意：下面如果有报错，请查看导入的maven依赖 

2、 编写ExportExcelVo

@Data
public class ExportExcelVo {
 
    private String headOne;
 
    private String headTwoCome;
 
    private String headTwoOn;
 
    private String headThreeCome;
 
    private String headThreeOn;
 
    private String headFourCome;
 
    private String headFourOn;
 
    private String headFiveCome;
 
    private String headFiveOn;
 
}

3、编写ExportUtil导出数据工具，支持xlsx和csv两种

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.io.FilenameUtils;
import org.springframework.util.CollectionUtils;
import cn.jimmyshi.beanquery.BeanQuery;
import springboot.redis.demo.exception.MyRuntimeException;
 
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.Writer;
import java.nio.charset.StandardCharsets;
import java.util.Collection;
import java.util.List;
import java.util.Map;
 
/**
 * @Description: 导出数据工具，支持xlsx和csv两种
 * @Title: ExportUtil
 */
@Slf4j
public class ExportUtil {
    /**
     *
     * @param dataList 导出数据列表
     * @param fieldMap 导出的数据字段，key为对象字段名称，value为标题名称
     * @param fileName 导出文件名
     * @return void
     * @throws IOException 文件操作失败
     */
    public static  void doExport(Collection dataList, Map fieldMap,String fileName) throws IOException{
        if (CollectionUtils.isEmpty(dataList)){
            return;
        }
        StringBuilder sb = new StringBuilder(128);
        for (Map.Entry e: fieldMap.entrySet()) {
            sb.append(e.getKey()).append(" as ").append(e.getValue()).append(", ");
        }
        //去掉末尾空格
        String fieldString = sb.substring(0,sb.length() - 2);
        //写出数据到excel格式的输出流,BeanQuery 是一个把对象转换为Map的Java工具库
        List> resultList = BeanQuery.select(fieldString).executeFrom(dataList);
         构建HTTP输出流参数
        HttpServletResponse response = ContextUtil.getHttpResponse();
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        if ("xlsx".equals(FilenameUtils.getExtension(fileName))) {
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.setRowHeight(-1, 30);
            writer.setColumnWidth(-1, 30);
            writer.setColumnWidth(1, 20);
            writer.write(resultList);
            writer.flush(out);
            writer.close();
            IoUtil.close(out);
 
        } else if ("csv".equals(FilenameUtils.getExtension(fileName))) {
            Collection headerList = fieldMap.values();
            String[] headerArray = new String[headerList.size()];
            headerList.toArray(headerArray);
            CSVFormat format = CSVFormat.DEFAULT.withHeader(headerArray);
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            try (Writer out = response.getWriter(); CSVPrinter printer = new CSVPrinter(out, format)) {
                for (Map o : resultList) {
                    for (Map.Entry entry : o.entrySet()) {
                        printer.print(entry.getValue());
                    }
                    printer.println();
                }
                printer.flush();
            } catch (Exception e) {
                log.error("Failed to call ExportUtil.doExport", e);
            }
        } else {
            throw new MyRuntimeException("不支持的导出文件类型！");
        }
    }
 
}

4、编写ExcelService层，准备数据

import java.util.ArrayList;
import java.util.List;
 
public class ExcelService {
    public static List getExportData() {
        ExportExcelVo excelVo = new ExportExcelVo();
        List list = new ArrayList<>();
 
        for (int i = 0; i < 3; i++) {
            excelVo.setHeadOne("AAA");
            excelVo.setHeadTwoCome("BBB");
            excelVo.setHeadTwoOn("CCC");
            excelVo.setHeadThreeCome("DDD");
            excelVo.setHeadThreeOn("EEE");
            excelVo.setHeadFourCome("FFF");
            excelVo.setHeadFourOn("GGG");
            excelVo.setHeadFiveCome("HHH");
            excelVo.setHeadFiveOn("III");
            list.add(excelVo);
        }
        return  list;
    }
}

5、编写controller层，调用接口

@RestController
public class alibaba {
  
    @GetMapping("/exportA")
    public void exportA(HttpServletResponse response) throws Exception {
        List data = ExcelService.getExportData();
        // 构建导出的map
        Map headerMap = new LinkedHashMap<>(21);
        headerMap.put("headOne","单元格一");
        headerMap.put("headTwoCome", "单元格二");
        headerMap.put("headTwoOn", "单元格三");
        headerMap.put("headThreeCome", "单元格四");
        headerMap.put("headThreeOn", "单元格五");
        headerMap.put("headFourCome", "单元格六");
        headerMap.put("headFourOn", "单元格七");
        headerMap.put("headFiveCome", "单元格八");
        headerMap.put("headFiveOn", "单元格九");
 
        ExportUtil.doExport(data,headerMap,"测试.xlsx");
    }
}

6、测试导出

在浏览器地址栏中访问：http://localhost:项目端口号/exportA

如果需要导出复杂的单元格，合并单元格，请查看 

Java中导出Excel，合并单元格，简单上手_SUMMERENT的博客-CSDN博客