Java 将多个List＜Map＜String, Object＞＞导出为多个sheet的excel

单个List＜Map＜String, Object＞＞导出为单个sheet的excel可以参考: Java 将List＜Map＜String, Object＞＞导出为excel (poi导出Excel使用)

这里补充下多个的代码工具类 ExcelUtils

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author 
 * @date 2021/1/5
 */
public class ExcelUtils {

    private static ExcelUtils instance = new ExcelUtils();

    private ExcelUtils() {
    }

    public static ExcelUtils getInstance() {
        return instance;
    }

    /**
     * 创建含有多个sheet的Excel文件, 注意titles要与mapLists长度一致, 否则会抛出异常
     * @param filename  文件名
     * @param titles    每个sheet的名称
     * @param mapLists  表格数据来源
     * @return  文件输出路径
     * @throws Exception
     */
    public String createExcelMultiSheet(String filename, String[] titles,
                                        List<Map<String, Object>>... mapLists) throws Exception {
        if (titles.length != mapLists.length) {
            throw new Exception();
        }
        //定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();

        for (int k = 0; k < titles.length; k++) {
            String title=titles[k];
            List<Map<String, Object>> mapList=mapLists[k];

            //获取数据源的 key, 用于获取列数及设置标题
            Map<String, Object> map = mapList.get(0);
            Set<String> stringSet = map.keySet();
            ArrayList<String> headList = new ArrayList<>(stringSet);


            //创建一个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) 13);

            //在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 = mapList.get(i).get(headList.get(j)).toString();
                    cells = rows.createCell(j);
                    cells.setCellValue(value);
                }
            }
        }

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        // 使用项目根目录, 文件名加上时间戳
        String path = System.getProperty("user.dir") + "\\" + filename + dateFormat.format(date) + ".xlsx";
        System.out.println("Excel文件输出路径: " + path);
        try {
            File file = new File(path);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            wb.write(fileOutputStream);
            wb.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;

    }


    /**
     * 将 List<Map<String,Object>> 类型的数据导出为 Excel
     * 默认 Excel 文件的输出路径为 项目根目录下
     * 文件名为 title + 时间戳 + .xlsx
     *
     * @param mapList  数据源(通常为数据库查询数据)
     * @param filename 文件名前缀, 实际文件名为 title + 时间戳 + .xlsx
     * @param title    表格首行标题
     * @return 文件输出路径
     */
    public String createExcel(List<Map<String, Object>> mapList, String filename, String title) {
        //获取数据源的 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 = mapList.get(i).get(headList.get(j)).toString();
                cells = rows.createCell(j);
                cells.setCellValue(value);
            }
        }

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        // 使用项目根目录, 文件名加上时间戳
        String path = System.getProperty("user.dir") + "\\" + filename + dateFormat.format(date) + ".xlsx";
        System.out.println("Excel文件输出路径: " + path);
        try {
            File file = new File(path);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            wb.write(fileOutputStream);
            wb.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }



    public static void main(String[] args) throws Exception {
        System.out.println("数据加载...");
        List<Map<String, Object>> mapArrayList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Map<String, Object> map = new HashMap<>();
            map.put("姓名", i);
            map.put("年龄", i);
            map.put("性别", i);
            mapArrayList.add(map);
        }
        List<Map<String, Object>> mapArrayList2 = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Map<String, Object> map = new HashMap<>();
            map.put("姓名", i+3);
            map.put("年龄", i+3);
            map.put("性别", i+3);
            mapArrayList2.add(map);
        }
        String[] title={"sheet1","sheet2"};
        List<Map<String, Object>>[] array=new List[2];
        array[0]=mapArrayList;
        array[1]=mapArrayList2;
        System.out.println("数据加载完成...");


        ExcelUtils.getInstance().createExcelMultiSheet("test",title,array);

    }

}