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

SUMMERENT

已于 2023-08-01 13:21:42 修改

阅读量3.4k

点赞数

分类专栏： java 文章标签： java servlet mybatis

于 2022-08-24 10:33:02 首次发布

本文链接：https://blog.csdn.net/SUMMERENT/article/details/126499054

版权

java 专栏收录该内容

25 篇文章 3 订阅

订阅专栏

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

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

1、引入maven依赖

2、编写ExportExcelVo

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

4、编写ExcelService层，准备数据

5、编写controller层，调用接口

6、测试导出

1、引入maven依赖

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.8</version>
        </dependency>
        <dependency>
            <groupId>cn.jimmyshi</groupId>
            <artifactId>bean-query</artifactId>
            <version>1.1.5</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

注意：下面如果有报错，请查看导入的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 <T> void doExport(Collection<T> dataList, Map<String,String> fieldMap,String fileName) throws IOException{
        if (CollectionUtils.isEmpty(dataList)){
            return;
        }
        StringBuilder sb = new StringBuilder(128);
        for (Map.Entry<String,String> 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<Map<String,Object>> 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<String> 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<String, Object> o : resultList) {
                    for (Map.Entry<String, Object> 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("导出文件类型不支持！");
        }
    }

}

ContextUtil 类

import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;

public class ContextUtil {
    public static HttpServletResponse getHttpResponse() {
        return ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
    }
}

4、编写ExcelService层，准备数据

import java.util.ArrayList;
import java.util.List;

public class ExcelService {
    public static List<ExportExcelVo> getExportData() {
        ExportExcelVo excelVo = new ExportExcelVo();
        List<ExportExcelVo> 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<ExportExcelVo> data = ExcelService.getExportData();
        // 构建导出的map
        Map<String,String> 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");
    }
}