数据导出工具类

工具类：

package com.siemens.sitracs.realtime.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * @author ran mengyu
 * @create 2021-09-02   11:06
 */
public class ExcelUtil {

    /**
     * 导入模版，给模版赋值
     *
     * @param workbook
     * @param list     数据集
     * @param keys     数据集里的key值，要跟rows对应
     * @param rows     表头内容
     * @throws Exception
     */
    public static void createExcel(HSSFWorkbook workbook, List list, String[] keys, String[] rows, String str) throws Exception {
        //创建sheet
        HSSFSheet sheet = workbook.createSheet("sheet");

        //创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);

        //设置文件提示
        HSSFRow headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellStyle(cellStyle);
        headerRow.createCell(0).setCellValue(str);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(region);

        //设置标题
        HSSFRow totalRow = sheet.createRow(1);
        for (int i = 0; i < rows.length; i++) {
            totalRow.createCell(i).setCellStyle(cellStyle);
            totalRow.createCell(i).setCellValue(rows[i]);
        }

        //填充数据
        HSSFRow row;
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 2);
            for (int j = 0; j < rows.length; j++) {
                row.createCell(j).setCellStyle(cellStyle);
                row.createCell(j).setCellValue(StringUtils.isBlank(JSONObject.parseObject(list.get(i).toString()).getString(keys[j])) ? "-" : JSONObject.parseObject(list.get(i).toString()).getString(keys[j]));
            }
        }

    }
}

调用：

// 设置模版字段
            String tableName = "0".equals(type) ? "实时离线路口列表_" : "实时在线路口列表_";
            tableName += DateUtil.ymdhms(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
            String[] rows = {"路口ID", "路口名称", "所属区域", "所属信号机","所属项目"};
            String[] keys = {"junctionId", "junctionName", "regionName", "controllerId","agreement"};

            // 下载
            HSSFWorkbook workbook = new HSSFWorkbook();
            ExcelUtil.createExcel(workbook, junctions, keys, rows, tableName);
            response.setHeader("Content-type", "application/vnd.ms-excel");

            // 解决导出文件名中文乱码
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(tableName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
            workbook.write(response.getOutputStream());
            json.put("code", "1000");
            json.put("msg", "success");