<script src="../js/axios.min.js"></script>

    $("#carExport").click(function() {
        axios.post(basePath + ‘/excel/car‘, JSON.stringify(carSearchObject), {
                headers: {
                    ‘Content-Type‘: ‘application/json; charset=utf-8‘
                },
                responseType: ‘blob‘
            })
            .then(function(response) {
                var blob = new Blob([response.data])
                var downloadElement = document.createElement(‘a‘);　　
                var href = window.URL.createObjectURL(blob); //创建下载的链接
                　　
                downloadElement.href = href;　　
                downloadElement.download = ‘车辆查询.xlsx‘; //下载后文件名
                　　
                document.body.appendChild(downloadElement);　　
                downloadElement.click(); //点击下载                　　
                document.body.removeChild(downloadElement); //下载完成移除元素　　
                window.URL.revokeObjectURL(href); //释放掉blob对象
                console.log(response);
            })
            .catch(function(error) {
                console.log(error);
            });
    })

package com.resafety.port.controller;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.resafety.util.ExcelUtilX;
import com.resafety.vo.CarSearchData;
import com.resafety.vo.CarSearchVo;
import com.resafety.vo.FwSearchData;
import com.resafety.vo.FwSearchVo;
import com.resafety.vo.JsySearchData;
import com.resafety.vo.JsySearchVo;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    @RequestMapping(value = "/jsy", method = RequestMethod.POST)
    @ResponseBody
    public void jsyExcel(@RequestBody JsySearchVo jsySearchVo, HttpServletRequest request,
            HttpServletResponse response) {
        System.out.println("驾驶员信息：" + jsySearchVo.toString());
        // excel文件名
        String fileName = jsySearchVo.getJsySearchName() + ".xls";
        // sheet名
        String sheetName = jsySearchVo.getJsySearchName();
        // 实际数据
        List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
        // 页面返回数据
        List<JsySearchData> jsySearchData = jsySearchVo.getJsySearchData();
        for (JsySearchData temp : jsySearchData) {
            realData.add(beanToMap(temp));
        }

        Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getJsyTitle(), realData, null);
        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/fw", method = RequestMethod.POST)
    @ResponseBody
    public void jsyExcel(@RequestBody FwSearchVo fwSearchVo, HttpServletRequest request, HttpServletResponse response) {
        System.out.println("服务信息：" + fwSearchVo.toString());
        // excel文件名
        String fileName = fwSearchVo.getFwSearchName() + ".xls";
        // sheet名
        String sheetName = fwSearchVo.getFwSearchName();
        // 实际数据
        List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
        // 页面返回数据

        List<FwSearchData> fwSearchData = fwSearchVo.getFwSearchData();
        for (FwSearchData temp : fwSearchData) {
            realData.add(beanToMap(temp));
        }

        Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getFwTitle(), realData, null);
        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/car", method = RequestMethod.POST)
    @ResponseBody
    public void jsyExcel(@RequestBody CarSearchVo carSearchVo, HttpServletRequest request,
            HttpServletResponse response) {
        System.out.println("车辆信息：" + carSearchVo.toString());
        // excel文件名
        String fileName = carSearchVo.getCarSearchName() + ".xls";
        // sheet名
        String sheetName = carSearchVo.getCarSearchName();
        // 实际数据
        List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
        // 页面返回数据

        List<CarSearchData> carSearchData = carSearchVo.getCarSearchData();
        for (CarSearchData temp : carSearchData) {
            realData.add(beanToMap(temp));
        }

        Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getCarTitle(), realData, null);
        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/export")
    @ResponseBody
    public void export2(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 模拟获取数据
        List<Map<String, String>> list = tempData();
        // excel标题
        String[] title = { "序号", "线路", "年份", "年日均满载率(%)" };
        // 内容
        String[][] content = new String[list.size()][title.length];
        // excel文件名
        String fileName = "线路客流满载率.xls";
        // sheet名
        String sheetName = "线路客流满载率";
        for (int i = 0; i < list.size(); i++) {
            Map<String, String> obj = list.get(i);
            content[i][0] = obj.get("index");
            content[i][1] = obj.get("road");
            content[i][2] = obj.get("year");
            content[i][3] = obj.get("bfb");
        }

        // 创建HSSFWorkbook
        Workbook wb = ExcelUtilX.getHSSFWorkbook(sheetName, title, content, null);

        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 发送响应流方法
    private void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private List<Map<String, String>> tempData() {
        List<Map<String, String>> temp = new ArrayList<Map<String, String>>();
        int year = 2015;
        int bfb = 98;
        for (int i = 1; i <= 6; i++) {
            Map<String, String> tempMap = new HashMap<String, String>();
            year++;
            bfb = bfb - ((int) (Math.random() * (5 - 1) + 1));
            tempMap.put("index", i + "");
            tempMap.put("road", "线路" + i);
            tempMap.put("year", year + "年");
            tempMap.put("bfb", bfb + "%");
            temp.add(tempMap);
        }
        return temp;
    }

    private static Map<String, Object> beanToMap(Object bean) {
        if (null == bean)
            return null;
        try {
            Map<String, Object> map = BeanUtils.describe(bean);
            // 移除key=class
            map.remove("class");
            return map;
        } catch (Exception e) {
            System.out.println("JavaBean-->Map转换失败：" + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /***
     * 保证Excel的列正确
     * 
     * @return
     */
    private List<ExcelUtilX.TitileBean> getJsyTitle() {
        String[] titleid = { "id", "name", "sex", "company", "cyzgzh", "cyzgzlb", "cyzgzyxrq", "badRecordCount",
                "negativeCount", "complainCount", "breakLowCount", "status" };
        String[] titleName = { "序号", "姓名", "性别", "所属企业", "从业资格证号", "从业资格类别", "资格证有效日期", "不良记录次数", "差评次数", "投诉次数",
                "违章次数", "从业状态" };
        List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
        for (int i = 0; i < titleName.length; i++) {
            ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
            bean.setId(titleid[i]);
            bean.setName(titleName[i]);
            titileBeans.add(bean);
        }
        return titileBeans;
    }

    private List<ExcelUtilX.TitileBean> getFwTitle() {
        String[] titleid = { "id", "plateNumber", "company", "complainTime", "complainMsg" };
        String[] titleName = { "序号", "车牌号", "所属企业", "投诉时间", "投诉内容" };
        List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
        for (int i = 0; i < titleName.length; i++) {
            ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
            bean.setId(titleid[i]);
            bean.setName(titleName[i]);
            titileBeans.add(bean);
        }
        return titileBeans;
    }

    private List<ExcelUtilX.TitileBean> getCarTitle() {
        String[] titleid = { "id", "plateNumber", "company", "yszh", "operateTime", "carType", "haveInsurance" };
        String[] titleName = { "序号", "车牌号", "所属企业", "运输证号", "投入运营时间", "车辆类型", "有无保险" };
        List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
        for (int i = 0; i < titleName.length; i++) {
            ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
            bean.setId(titleid[i]);
            bean.setName(titleName[i]);
            titileBeans.add(bean);
        }
        return titileBeans;
    }

}

package com.resafety.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author 谢辉
 * @Classname ExcelUtilX
 * @Description TODO
 * @Date 2020/12/9 18:34
 */
public class ExcelUtilX {

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param data      内容
     * @param wb        Workbook对象： 如果是.xls文件的请传入HSSFWorkbook对象类型
     *                  如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
     * @return
     */
    public static Workbook getHSSFWorkbook(String sheetName, String[] title, String[][] data, Workbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            // 默认使用兼容.xls文件的
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        // style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 声明列对象
        Cell cell = null;

        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        // 创建内容
        for (int i = 0; i < data.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(data[i][j]);
            }
        }
        return wb;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param data      内容
     * @param wb        Workbook对象： 如果是.xls文件的请传入HSSFWorkbook对象类型
     *                  如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
     * @return
     */
    public static Workbook getHSSFWorkbook(String sheetName, List<ExcelUtilX.TitileBean> title,
            List<Map<String, Object>> data, Workbook wb) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (title == null || title.size() <= 0) {
            throw new IllegalArgumentException("传入的title参数有问题");
        }
        if (data == null) {
            data = new ArrayList<Map<String, Object>>();
        }
        if (data.size() > 0) {
            Map<String, Object> map = data.get(0);
            if (map.size() != title.size()) {
                throw new IllegalArgumentException("传入的title列数和实际数据列数不一致！");
            }
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        // style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 声明列对象
        Cell cell = null;

        int titleLength = title.size();

        // 创建标题
        for (int i = 0; i < titleLength; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title.get(i).getName());
            cell.setCellStyle(style);
        }

        // 创建内容
        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < titleLength; j++) {
                // 将内容按顺序赋给对应的列对象
                Cell tempCell = row.createCell(j);
                // 这里为了代码清晰，每一步都写出来
                Map<String, Object> map = data.get(i);
                Object objectValue = map.get(title.get(j).getId());
                tempCell.setCellValue(objectValue.toString());
                // tempCell.setCellStyle(style);
            }
        }
        return wb;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param data      内容
     * @param wb        Workbook对象： 如果是.xls文件的请传入HSSFWorkbook对象类型
     *                  如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
     * @return
     */
    public static Workbook getHSSFWorkbook(String sheetName, List<LinkedHashMap<String, Object>> data, Workbook wb) {
        // 创建title
        List<String> title = new ArrayList<String>();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (data == null) {
            data = new ArrayList<LinkedHashMap<String, Object>>();
        }
        if (data.size() > 0) {
            // 因为LinkedHashMap保存了插入时的顺序
            LinkedHashMap<String, Object> linkedHashMap = data.get(0);
            Set<Entry<String, Object>> entrySet = linkedHashMap.entrySet();
            for (Entry<String, Object> entry : entrySet) {
                // 保存title，使用key值作为title
                title.add(entry.getKey());
            }
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        // style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 声明列对象
        Cell cell = null;

        int titleLength = title.size();

        // 创建标题
        for (int i = 0; i < titleLength; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title.get(i));
            cell.setCellStyle(style);
        }

        // 创建内容
        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < titleLength; j++) {
                // 将内容按顺序赋给对应的列对象
                Cell tempCell = row.createCell(j);
                // 这里为了代码清晰，每一步都写出来
                Map<String, Object> map = data.get(i);
                Object objectValue = map.get(title.get(j));
                tempCell.setCellValue(objectValue.toString());
                // tempCell.setCellStyle(style);
            }
        }
        return wb;
    }

    /**
     * 读取Excel数据，简单的行列数据（数据表格式），不适用复杂的数据
     *
     * @param excelPath 文件路径
     * @return 表格数据
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcel(String excelPath) throws IOException {
        Workbook wb = null;
        InputStream is = new FileInputStream(excelPath);
        int indexPoint = excelPath.lastIndexOf(".");
        String suffixName = "";
        if (indexPoint != -1) {
            suffixName = excelPath.substring(indexPoint + 1);
            System.out.println("后缀名是：" + suffixName);
        }
        // 根据文件后缀（xls/xlsx）进行判断
        if ("xls".equalsIgnoreCase(suffixName)) {
            wb = new HSSFWorkbook(is);
        } else if ("xlsx".equalsIgnoreCase(suffixName)) {
            wb = new XSSFWorkbook(is);
        } else {
            throw new IllegalArgumentException("文件类型错误!");
        }
        List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
        List<String> keys = new ArrayList<String>();
        // 遍历sheet页
        for (int numSheet = 0, sheetLength = wb.getNumberOfSheets(); numSheet < sheetLength; numSheet++) {
            Sheet hssfSheet = wb.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // 开始遍历行
            if (hssfSheet.getLastRowNum() > 0) {

                // 使用第一行作为key
                Row row = hssfSheet.getRow(0);
                for (int i = row.getFirstCellNum(), keyNum = row.getLastCellNum(); i < keyNum; i++) {
                    keys.add(row.getCell(i).getStringCellValue());
                }

                // 遍历数据行(除去第一行，下标从1开始)
                for (int j = hssfSheet.getFirstRowNum() + 1, k = hssfSheet.getLastRowNum(); j <= k; j++) {
                    Map<String, Object> map = new LinkedHashMap<String, Object>();
                    // 遍历每个列
                    for (int index = hssfSheet.getRow(j).getFirstCellNum(), cellLength = hssfSheet.getRow(j)
                            .getLastCellNum(); index < cellLength; index++) {

                        map.put(keys.get(index), getValue(hssfSheet.getRow(j).getCell(index)));

                    }
                    // 存放数据
                    data.add(map);
                }

            } // 行结束

        } // sheet页for结束
        return data;
    }

    private static String getValue(Cell hssfCell) {
        if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {

            // 返回数值类型的值
            // return String.valueOf(hssfCell.getNumericCellValue());
            return numOfImport(hssfCell);
        } else if (hssfCell.getCellTypeEnum() == CellType.BLANK) {
            // 返回空值
            return "";
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 数字转换字符串
     *
     * @param cell
     * @return
     */
    private static String numOfImport(Cell cell) {
        String value = cell.toString();
        if (cell.getCellTypeEnum() == CellType.STRING) {// 字符串类型
            return value;
        } else {
            String[] str = value.split("\\.");
            if (str.length > 1) {
                String str1 = str[1];
                int m = Integer.parseInt(str1);
                if (m == 0) {
                    return str[0];
                } else {
                    return value;
                }
            } else {
                return value;
            }
        }

    }

    public static class TitileBean {
        private String id;
        private String name;

        public String getId() {
            return id;
        }

        public void setId(String id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Override
        public String toString() {
            return "TitileBean [id=" + id + ", name=" + name + "]";
        }
    }

}