java 导出多个Sheet的Excel文件

package com.forestar.patrol.service;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelService {
    
    public void exportExchange(HttpServletResponse response) {

        //全部数据
        List<List<Map<String,Object>>> list = new ArrayList<>();

        //第一个sheet的数据
        List<Map<String,Object>> maps1 = new ArrayList<>();
        Map<String,Object> map1 = new HashMap<>();
        map1.put("A", 11001);
        map1.put("B", 22001);
        map1.put("C", 33001);
        maps1.add(map1);
        Map<String,Object> map2 = new HashMap<>();
        map2.put("A", 11002);
        map2.put("B", 22002);
        map2.put("C", 33002);
        maps1.add(map2);

        //第二个sheet的数据
        List<Map<String,Object>> maps2 = new ArrayList<>();
        Map<String,Object> map11 = new HashMap<>();
        map11.put("A", 111001);
        map11.put("B", 222001);
        map11.put("C", 333001);
        maps2.add(map11);
        Map<String,Object> map22 = new HashMap<>();
        map22.put("A", 111002);
        map22.put("B", 222002);
        map22.put("C", 333002);
        maps2.add(map22);

        list.add(maps1);
        list.add(maps2);

        //列信息
        String[] tableHeader = {"col-1","col-2","col-3"};
        HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个Excel
        HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型
        style.setAlignment(HorizontalAlignment.CENTER);
        HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型
        style1.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont font = workbook.createFont(); //设置字体

        for (int j = 0; j < list.size(); j++) {
            List<Map<String, Object>> maps = list.get(j);

            HSSFSheet sheet = workbook.createSheet("sheet"+(j+1)); //创建一个sheet
            HSSFHeader header = sheet.getHeader();//设置sheet的头
            try {
                //根据是否取出数据，设置header信息
                if (maps.size() == 0) {
                    header.setCenter("无数据");
                } else {
                    header.setCenter("导出信息");
                    HSSFRow row = sheet.createRow(0);
                    row.setHeight((short) 400);
                    HSSFCell cell = null;
                    //表头
                    for (int k = 0; k < tableHeader.length; k++) {
                        cell = row.createCell((short) k);//创建第0行第k列
                        cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
                        sheet.setColumnWidth((short) k, (short) 8000);//设置列的宽度
                        font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
                        font.setFontHeight((short) 350); //设置单元字体高度
                        style1.setFont(font);//设置字体风格
                        cell.setCellStyle(style1);
                    }
                    // 给Excel填充数据
                    for (int i = 0; i < maps.size(); i++) {
                        //获取InternationalStudent对象
                        Map<String, Object> map = maps.get(i);
                        row = sheet.createRow((short) (i + 1));//创建第i+1行
                        row.setHeight((short) 400);//设置行高

                        if (map.get("A") != null) {
                            cell = row.createCell((short) 0);//创建第i+1行第0列
                            cell.setCellValue(map.get("A").toString());//设置第i+1行第0列的值
                            cell.setCellStyle(style);//设置风格
                        }
                        if (map.get("B") != null) {
                            cell = row.createCell((short) 1); //创建第i+1行第1列
                            cell.setCellValue(map.get("B").toString());//设置第i+1行第1列的值
                            cell.setCellStyle(style); //设置风格
                        }
                        if (map.get("C") != null) {
                            cell = row.createCell((short) 2); //创建第i+1行第1列
                            cell.setCellValue(map.get("C").toString());//设置第i+1行第1列的值
                            cell.setCellStyle(style); //设置风格
                        }

                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        outputSetting(workbook,"导出信息("+ LocalTime.now() +").xls",response);
    }

    //固定配置
    public void outputSetting(HSSFWorkbook workbook ,String fileName ,HttpServletResponse response) {
        OutputStream out = null;//创建一个输出流对象
        try {
            out = response.getOutputStream();// 得到输出流
            response.setHeader("Content-disposition","attachment; filename="+new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));//filename是下载的xls的名
            response.setContentType("application/msexcel;charset=UTF-8");//设置类型
            response.setHeader("Pragma","No-cache");//设置头
            response.setHeader("Cache-Control","no-cache");//设置头
            response.setDateHeader("Expires", 0);//设置日期头
            workbook.write(out);
            out.flush();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try{
                if(out!=null){
                    out.close();
                }
            }catch(IOException e){
                e.printStackTrace();
            }
        }
    }


}

 

需要引用的包：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

 

导出文件预览：