[开发测试]XSSFWorkbook和HSSFWorkbook导出（亲测）

项目上这种功能很多，写了一个工具类，代码有点垃圾，大神勿喷

导入导出poi组件Excel

     
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

package com.tc.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导出工具类  <p>07导出不限制行数，03有66535局限性</p>
 *
 * @author Fyg_gm
 * @since 2021-05-12 21:01:20
 */
@Component
public class ExcelUtil {
    Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 07 无限制导出
     *
     * @param response
     * @param list     导出的数据
     * @param filename 导出文件名
     * @param title    导出表头
     */
    public void exportMultiToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) {
        Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
        filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis());

        //创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建表单
        XSSFSheet sheet = genSheet(workbook, filename);
        //创建表单样式
        Map<String, XSSFCellStyle> tableStyle = createStyle(workbook);//创建表头样式
        Map<String, XSSFCellStyle> titleStyle = createStyle(workbook);//创建标题样式
        Map<String, XSSFCellStyle> contextStyle = createStyle(workbook);//创建正文样式

        //创建Excel
        genExcel(filename, list, title, sheet, tableStyle.get("cellStyle2"), titleStyle.get("cellStyle"), contextStyle.get("cellStyle3"));
        String suffix = ".xls";
        filename += suffix;
//            最终已流的形式返回
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            logger.info(e.getMessage());
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
                logger.info(e.getMessage());
            }
        }

    }

    /**
     * 03 限制导出行数66535 有局限性
     *
     * @param response
     * @param list     导出的数据
     * @param filename 导出的文件名
     * @param title    导出的表头
     */
    public void exportToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) {
        filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis());
        String suffix = ".xls";
        filename += suffix;
        //创建excel表
        HSSFWorkbook workbook = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet = workbook.createSheet(filename);

        //设置默认行宽
        sheet.setDefaultColumnWidth(20);
        //创建样式
        Map<String, HSSFCellStyle> style = createStyle(workbook);

        //创建表头
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);//行高

        HSSFCell cell = row.createCell(0);
        cell.setCellValue(filename);
        cell.setCellStyle(style.get("cellStyle"));

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.length - 1)));

        //创建标题
        HSSFRow rowTitle = sheet.createRow(1);
        rowTitle.setHeightInPoints(20);

        HSSFCell hc;
        for (int i = 0; i < title.length; i++) {
            hc = rowTitle.createCell(i);
            hc.setCellValue(title[i]);
            //设置标题样式
            hc.setCellStyle(style.get("cellStyle2"));
        }
        //创建表格数据
        Field[] fields;
        int i = 2;
        int index = 0;//记录额外创建的sheet数量

        //数据源
        for (Object obj : list) {
            //反射获取到实体
            fields = obj.getClass().getDeclaredFields();
            HSSFRow rowBody = sheet.createRow(i);
            rowBody.setHeightInPoints(20);
            int j = 0;
            //遍历渲染表格
            for (Field f : fields) {
                f.setAccessible(true);
                Object va = null;
                try {
                    va = f.get(obj);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (null == va) {
                    va = "---";
                }
                //创建行数
                hc = rowBody.createCell(j);
                //赋值数据
                hc.setCellValue(va.toString());
                //设置表格样式
                hc.setCellStyle(style.get("cellStyle3"));

                j++;
            }

            i++;
        }
        //最终已流的形式返回
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            logger.info(e.getMessage());
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
                logger.info(e.getMessage());
            }
        }
    }

    //封装动态渲染excel
    public static void genExcel(String filename, List<?> list, String[] title, XSSFSheet sheet, XSSFCellStyle tableStyle, XSSFCellStyle titleStyle, XSSFCellStyle contextStyle) {
        //设置默认行宽
        sheet.setDefaultColumnWidth(20);
        //创建第一行，为标题，index从0开始
        XSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);
        //创建一列
        XSSFCell cell = row.createCell(0);
        //标题
        cell.setCellValue(filename);
        //设置标题样式
        cell.setCellStyle(titleStyle);
        //设置标题位置
        sheet.addMergedRegion(new CellRangeAddress(
                0, //first row
                0, //last row
                0, //first column
                (title.length - 1) //last column
        ));

        //创建第二行  表头
        XSSFRow rowTitle = sheet.createRow(1);
        rowTitle.setHeightInPoints(20);
        XSSFCell xc;
        for (int i = 0; i < title.length; i++) {
            xc = rowTitle.createCell(i);
            xc.setCellValue(title[i]);
            //设置表头样式
            xc.setCellStyle(tableStyle);
        }
        //从数据库取数据填充到Excel，
        Field[] fields;
        //i从2开始计数，因为上面已经创建了 0 1行
        int i = 2;
        for (Object obj : list) {
            //反射获取到实体
            fields = obj.getClass().getDeclaredFields();
            //从第三行动态去创建
            XSSFRow rowBody = sheet.createRow(i);
            rowBody.setHeightInPoints(20);
            int j = 0;
            //遍历渲染表格
            for (Field f : fields) {
                f.setAccessible(true);
                Object va = null;
                try {
                    va = f.get(obj);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (null == va) {
                    va = "---";
                }
                //创建行数
                xc = rowBody.createCell(j);
                //赋值数据
                xc.setCellValue(va.toString());
                //设置表格样式
                xc.setCellStyle(contextStyle);

                j++;
            }
            i++;
        }
    }

    //设置表单，并生成表单
    public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) {
        //生成表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表单文本居中
        sheet.setHorizontallyCenter(true);
        sheet.setFitToPage(false);
        //打印时在底部右边显示文本页信息
        Footer footer = sheet.getFooter();
        footer.setRight("Page " + HeaderFooter.numPages() + " Of " + HeaderFooter.page());
        //打印时在头部右边显示Excel创建日期信息
        Header header = sheet.getHeader();
        header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time());
        //设置打印方式
        XSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小
        return sheet;
    }

    //设置exportToDownFile样式
    public Map<String, HSSFCellStyle> createStyle(HSSFWorkbook workbook) {
        Map<String, HSSFCellStyle> map = new HashMap<>();
        //表头样式（加粗，水平居中，垂直居中）
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框样式
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

        HSSFFont fontStyle = workbook.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        cellStyle.setFont(fontStyle);

        //标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
//        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);

        //设置边框样式
        cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

        //字段样式（垂直居中）
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        //设置边框样式
        cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        map.put("cellStyle", cellStyle);
        map.put("cellStyle2", cellStyle2);
        map.put("cellStyle3", cellStyle3);
        return map;
    }

    //设置exportMultiToDownFile样式
    public Map<String, XSSFCellStyle> createStyle(XSSFWorkbook workbook) {
        Map<String, XSSFCellStyle> map = new HashMap<>();
        //表头样式（加粗，水平居中，垂直居中）
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框样式
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

        XSSFFont fontStyle = workbook.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        cellStyle.setFont(fontStyle);

        //标题样式（加粗，垂直居中）
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
//        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);

        //设置边框样式
        cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

        //字段样式（垂直居中）
        XSSFCellStyle cellStyle3 = workbook.createCellStyle();
//        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //设置边框样式
        cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        map.put("cellStyle", cellStyle);
        map.put("cellStyle2", cellStyle2);
        map.put("cellStyle3", cellStyle3);
        return map;
    }


}