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

我不是女神ヾ 2024-04-08 11:36 103阅读 0赞

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

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

![a129223cfbab4f5da85541b8ebbdae99.png][]

**目录**

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");
        }
    }

### 6、测试导出 ###

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

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

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

[Java_Excel_SUMMERENT_-CSDN]: https://blog.csdn.net/SUMMERENT/article/details/126478625?spm=1001.2014.3001.5501
[a129223cfbab4f5da85541b8ebbdae99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4dc39d4f300544bca96786d3f325c7f1.png
[http_localhost_exportA]: http://localhost:8081/exportA