SpringBoot实现excel文件生成和下载

使用SpringBoot实现excel生成和下载，生成模板如下

controller

@RequestMapping(value = { "/downloadExcelTemplate" }, method = RequestMethod.GET)
    public String downloadExcelTemplate(HttpSession httpSession, HttpServletResponse response) {
        try {
            dealExcelService.downloadExcelTemplate(response);
            return "success";
        } catch (Exception e) {
            logger.error("downloadExcelTemplate_error", e);
            return "failure";
        }
    }

service

public void downloadExcelTemplate(HttpServletResponse response) throws Exception {
        //文件名
        SimpleDateFormat format3 = new SimpleDateFormat("yyyyMMddHHmm");
        String fileName = new String(("文件名" + format3.format(new Date()) + "导入模板").getBytes(), "ISO8859_1");
        //配置请求头
        ServletOutputStream outputStream = response.getOutputStream();
        // 组装附件名称和格式
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
        // 创建一个workbook 对应一个excel应用文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workBook.createSheet("模板");
        ExportUtil exportUtil = new ExportUtil(workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle2();
        // 构建表头
        XSSFRow headRow = ExportUtil.createRow(sheet, 0);
        XSSFCell cell;

        String[] titles = {"表头一", "表头二", "表头三"};
        int index = 0;
        for (String title : titles) {
            cell = ExportUtil.createCell(headRow, index);
            cell.setCellStyle(headStyle);
            cell.setCellValue(title);
            index++;
        }

        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

ExportUtil导出工具类

package com.shengsheng.utils;

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

/**
 * excel 表格导出工具类
 *
 * @author shengshenglalala
 */
public class ExportUtil {
    private XSSFWorkbook wb;

    private XSSFSheet sheet;

    /**
     * @param wb
     * @param sheet
     */
    public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet) {
        this.wb = wb;
        this.sheet = sheet;
    }

    /**
     * 合并单元格后给合并后的单元格加边框
     *
     * @param region
     * @param cs
     */
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

        int toprowNum = region.getFirstRow();
        for (int i = toprowNum; i <= region.getLastRow(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                XSSFCell cell = row.getCell(j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 设置表头的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getHeadStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // // 设置单元格的背景颜色为淡蓝色
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        // cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        // font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
//		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getBodyStyle2() {
        // 创建单元格样式
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 创建单元格内容显示不下时自动换行
        // cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        // font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        font.setColor(HSSFColor.BLACK.index);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        return cellStyle;
    }

    /**
     * 没有行，就创建行
     *
     * @param sheet
     * @param index
     * @return
     */
    public static XSSFRow createRow(XSSFSheet sheet, Integer index) {
        XSSFRow row = sheet.getRow(index);
        if (row == null) {
            return sheet.createRow(index);
        }
        return row;
    }

    /**
     * 如果没有列，就创建列
     *
     * @param row
     * @param index
     * @return
     */
    public static XSSFCell createCell(XSSFRow row, Integer index) {
        XSSFCell cell = row.getCell(index);
        if (cell == null) {
            return row.createCell(index);
        }
        return cell;
    }
}