POI导出excel

package com.mvc.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * excel export
 *
 * @author smallk
 * @link poi.jar
 * @link poi-ooxml.jar
 * @link poi-ooxml-schemas.jar
 * @link dom4j.jar
 * @link xmlbeans.jar
 * @date 2018/12/4 23:30
 */
public class ExcelExport {

    public static final String W2003 = "2003";

    public static final String W2007 = "2007";

    public static final String XLS = "xls";

    public static final String XLSX = "xlsx";

    /**
     * 创建 Workbook
     *
     * @param version 2003 2007 xls xlsx
     * @return Workbook
     */
    public static Workbook createWorkbook(String version) {
        if (W2003.equalsIgnoreCase(version) || XLS.equalsIgnoreCase(version)) {
            return new HSSFWorkbook();
        } else if (W2007.equalsIgnoreCase(version) || XLSX.equalsIgnoreCase(version)) {
            return new XSSFWorkbook();
        } else {
            return new XSSFWorkbook();
        }
    }

    /**
     * 导出简单Excel
     *
     * @param titleList title list
     * @param dataList  data list
     * @param version   2003 2007 xls xlsx
     * @return Workbook
     */
    public static Workbook exportSimpleExcelByMap(List<String> titleList, List<Map<String, Object>> dataList, String version) {
        Row row;
        int rowIndex = 0;
        Workbook workbook = ExcelExport.createWorkbook(version);
        Sheet sheet = workbook.createSheet();

        if (titleList != null && !titleList.isEmpty()) {
            int cellIndex = 0;
            row = sheet.createRow(rowIndex++);
            for (String title : titleList) {
                row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
            }
        }

        if (dataList != null && !dataList.isEmpty()) {
            for (Map<String, Object> map : dataList) {
                if (map == null || map.isEmpty()) {
                    continue;
                }
                int cellIndex = 0;
                row = sheet.createRow(rowIndex++);
                Iterator iterator = map.values().iterator();
                while (iterator.hasNext()) {
                    Object value = iterator.next();
                    row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
                }
            }
        }

        return workbook;
    }

    /**
     * 导出简单Excel
     *
     * @param titleList title list
     * @param dataList  data list
     * @param version   2003 2007 xls xlsx
     * @return Workbook
     */
    public static Workbook exportSimpleExcelByObject(List<String> titleList, List<List<Object>> dataList, String version) {
        Row row;
        int rowIndex = 0;
        Workbook workbook = ExcelExport.createWorkbook(version);
        Sheet sheet = workbook.createSheet();

        if (titleList != null && !titleList.isEmpty()) {
            int cellIndex = 0;
            row = sheet.createRow(rowIndex++);
            for (String title : titleList) {
                row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
            }
        }

        if (dataList != null && !dataList.isEmpty()) {
            for (List<Object> objList : dataList) {
                if (objList == null || objList.isEmpty()) {
                    continue;
                }
                int cellIndex = 0;
                row = sheet.createRow(rowIndex++);
                for (Object value : objList) {
                    row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
                }
            }
        }

        return workbook;
    }

    /**
     * 填充模板sheet数据
     *
     * @param sheet    Sheet
     * @param dataList data list
     * @param rowIndex 从第几行开始 最小值0
     */
    public static void fillTemplateSheetByMap(Sheet sheet, List<Map<String, Object>> dataList, int rowIndex) {
        if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
            return;
        }
        for (Map<String, Object> map : dataList) {
            if (map == null || map.isEmpty()) {
                continue;
            }
            int cellIndex = 0;
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            Iterator iterator = map.values().iterator();
            while (iterator.hasNext()) {
                Object value = iterator.next();
                Cell cell = row.getCell(cellIndex);
                if (cell == null) {
                    cell = row.createCell(cellIndex);
                }
                cell.setCellValue(value == null ? "" : value.toString());
                cellIndex++;
            }
            rowIndex++;
        }
    }

    /**
     * 填充模板sheet数据
     *
     * @param sheet    Sheet
     * @param dataList data list
     * @param rowIndex 从第几行开始 最小值0
     */
    public static void fillTemplateSheetByObject(Sheet sheet, List<List<Object>> dataList, int rowIndex) {
        if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
            return;
        }
        for (List<Object> objList : dataList) {
            if (objList == null || objList.isEmpty()) {
                continue;
            }
            int cellIndex = 0;
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            for (Object value : objList) {
                Cell cell = row.getCell(cellIndex);
                if (cell == null) {
                    cell = row.createCell(cellIndex);
                }
                cell.setCellValue(value == null ? "" : value.toString());
                cellIndex++;
            }
            rowIndex++;
        }
    }
}

　　设置响应的方法

/**
     * 设置下载响应
     */
    public static void setDownLoadResponse(HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
        String msie = "msie";
        String chrome = "chrome";
        String windows = "windows";
        String firefox = "firefox";
        String browserType = request.getHeader("User-Agent").toLowerCase();
        if (browserType.contains(firefox) || browserType.contains(chrome)) {
            fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
        } else if (browserType.contains(msie) || browserType.contains(windows)) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {
            fileName = new String(fileName.getBytes());
        }
        // 重置
        response.reset();
        // 告知浏览器不缓存
        response.setHeader("pragma", "no-cache");
        response.setHeader("cache-control", "no-cache");
        response.setHeader("expires", "0");
        // 响应编码
        response.setCharacterEncoding("UTF-8");
        // 用给定的名称和值添加一个响应头
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
    }

/**
     * 导出学生excel
     *
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     * @param model    DemoStudentModel
     */
    @GetMapping("/export-stu-excel")
    public void exportStuExcel(HttpServletRequest request, HttpServletResponse response, DemoStudentModel model) throws Exception {
        Workbook workbook = demoService.exportStuExcel(model);
        HttpUtil.setDownLoadResponse(request, response, "学生.xlsx");
        workbook.write(response.getOutputStream());
    }

/**
     * 导出学生excel
     *
     * @param model DemoStudentModel
     * @return Workbook
     */
    public Workbook exportStuExcel(DemoStudentModel model) {
        List<String> titleList = new ArrayList<>();
        titleList.add("ID");
        titleList.add("学生姓名");
        titleList.add("学生年龄");
        List<Map<String, Object>> dataList = demoDao.exportStuExcel(model);
        return ExcelExport.exportSimpleExcelByMap(titleList, dataList, ExcelExport.W2007);
    }

//导出学生excel
$('#exportStuExcel').click(function () {
    var index = layer.confirm('确认导出？', {closeBtn: 0}, function () {
        layer.close(index);
        var stuName = $('#stuNameValue').val();
        location.href="https://zhishitu.com" + '/demo/export-stu-excel?stuName=' + encodeURIComponent(stuName);
    });
});