Java 将List＜Map＜String, Object＞＞导出为excel

前言:导出excel是开发过程中一个非常常用的技能，使用EasyExcel导出十分简单，但仅能将对象导出为excel，那么这里带来的是使用poi将List<Map<String,Object>>导出为excel,并返回二进制流给前端。

1. pom.xml 添加依赖

        <!--poi start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!--poi end-->

2.新建 ExcelUtils 类, 用于导出excel

package com.matemro.dwmai.modular.util;
 
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
 
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
 
/**
 * @author yjc
 * @date 2022/9/28
 */
public class ExcelUtils {
 
    /**
     * 将 List<Map<String,Object>> 类型的数据导出为 Excel
     */
    public static void  createExcel(List<Map<String, Object>> mapList, String filename, String title, HttpServletResponse response) {
        //获取数据源的 key, 用于获取列数及设置标题
        Map<String, Object> map = mapList.get(0);
        Set<String> stringSet = map.keySet();
        ArrayList<String> headList = new ArrayList<>(stringSet);
 
        //定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建一个Sheet页
        XSSFSheet sheet = wb.createSheet(title);
        //设置行高
        sheet.setDefaultRowHeight((short) (2 * 256));
        //为有数据的每列设置列宽
        for (int i = 0; i < headList.size(); i++) {
            sheet.setColumnWidth(i, 8000);
        }
        //设置单元格字体样式
        XSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
 
        //在sheet里创建第一行，并设置单元格内容为 title (标题)
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(title);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size() - 1));
        // 创建单元格文字居中样式并设置标题单元格居中
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCell.setCellStyle(cellStyle);
 
        //获得表格第二行
        XSSFRow row = sheet.createRow(1);
        //根据数据源信息给第二行每一列设置标题
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(headList.get(i));
        }
 
        XSSFRow rows;
        XSSFCell cells;
        //循环拿到的数据给所有行每一列设置对应的值
        for (int i = 0; i < mapList.size(); i++) {
            //在这个sheet页里创建一行
            rows = sheet.createRow(i + 2);
            //给该行数据赋值
            for (int j = 0; j < headList.size(); j++) {
                String value;
                if (mapList.get(i).get(headList.get(j)) !=null){
                     value = mapList.get(i).get(headList.get(j)).toString();
                }else {
                    value = "";
                }
                cells = rows.createCell(j);
                cells.setCellValue(value);
            }
        }
 
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(filename));
            OutputStream out = response.getOutputStream();
 
            wb.write(out);
            wb.close();
            out.close();;
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
}

3.编写测试Controller

/**
     * 导出excel
     * @param response
     * @return
     */
    @PostMapping(name = "导出", path = "/excel")
    public void resultExport(HttpServletResponse response) {
 
        List<Map<String,Object>> list = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        map.put("名称","iphone13");
        map.put("属性1",1);
        map.put("属性2",2);
        map.put("属性3",3);
 
        Map<String,Object> map1 = new HashMap<>();
        map1.put("名称","iphone12");
        map1.put("属性1",1);
        map1.put("属性2",2);
        map1.put("属性3",3);
 
        Map<String,Object> map2 = new HashMap<>();
        map2.put("名称","iphone11");
        map2.put("属性1",1);
        map2.put("属性3",3);
 
        list.add(map);
        list.add(map1);
        list.add(map2);
 
        ExcelUtils.getInstance().createExcel(list, "testExcel.xls", "测试文档", response);
    }

4.用postman调用测试。结果返回的是二进制流，点击save to a file将文件保存下来

5.最终导出结果