Excel导出功能（poi实现）

导包：

<properties>
    <poi-ooxml.version>4.0.0</poi-ooxml.version>
</properties>

<!-- poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>${poi-ooxml.version}</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>${poi-ooxml.version}</version>
</dependency>

代码：

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * 基于POI的excel导出工具类
 *
 * @author Vick C on 2018/11/8.
 */
@Slf4j
public class ExportExcel {

    /**
     * 显示的导出表的标题
     */
    private String fileName;
    /**
     * 导出表的列名
     */
    private String[] rowName;
    /**
     * 单表数据集合
     */
    private List<String[]> dataList = new ArrayList<>();
    /**
     * sheet名称
     */
    private String[] sheetTitle;
    /**
     * sheet个数
     */
    private int sheetNum;
    /**
     * 多表数据集合
     */
    private List<List<String[]>> dataLists;

    /**
     * 单表构造方法，传入要导出的数据
     *
     * @param fileName The file name
     * @param rowName  the row name array
     * @param dataList the data list
     */
    public ExportExcel(String fileName, String[] rowName, List<String[]> dataList) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.fileName = fileName;
    }

    /**
     * 多表构造方法
     *
     * @param fileName   the file name
     * @param sheetTitle the sheet title array
     * @param sheetNum   the sheet num
     * @param rowName    the row name array
     * @param dataLists  the data list
     */
    public ExportExcel(String fileName, String[] sheetTitle, int sheetNum, String[] rowName, List<List<String[]>> dataLists) {
        this.dataLists = dataLists;
        this.rowName = rowName;
        this.fileName = fileName;
        this.sheetTitle = sheetTitle;
        this.sheetNum = sheetNum;
    }

    /**
     * 导出数据-单表
     *
     * @param request  the http servlet request
     * @param response the http servlet response
     */
    public void export(HttpServletRequest request, HttpServletResponse response) {
        try {
            // 创建工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 创建工作表
            XSSFSheet sheet = workbook.createSheet();
            sheet.setDefaultColumnWidth(20);
            // 获取列头样式对象
            XSSFCellStyle columnTopStyle = getCellStyle(workbook, ExcelEnum.TOP);
            // 单元格样式对象
            XSSFCellStyle style = getCellStyle(workbook, ExcelEnum.COLUMN);
            // 定义所需列数
            int columnNum = rowName.length;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            XSSFRow rowRowName = sheet.createRow(0);
            rowRowName.setHeightInPoints(30);
            // 将列头设置到sheet的单元格中\
            setHearderToSheet(columnTopStyle, columnNum, rowRowName);
            // 将查询出的数据设置到sheet对应的单元格中
            fillDataToSheet(sheet, style, this.dataList);
            // 让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                sheet.autoSizeColumn((short) colNum);
            }
            exportFileStream(fileName, workbook, request, response);
        } catch (Exception e) {
            log.error("Excel单表导出失败，失败原因：", e);
        }
    }

    /**
     * 导出数据-多表
     *
     * @param request  the http servlet request
     * @param response the http servlet response
     */
    public void exportSheets(HttpServletRequest request, HttpServletResponse response) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            for (int z = 0; z <= sheetNum; z++) {
                XSSFSheet sheet = workbook.createSheet();
                workbook.setSheetName(z, sheetTitle[z]);
                sheet.setDefaultColumnWidth(20);
                // sheet样式定义
                XSSFCellStyle columnTopStyle = getCellStyle(workbook, ExcelEnum.TOP);
                // 单元格样式对象
                XSSFCellStyle style = getCellStyle(workbook, ExcelEnum.COLUMN);
                // 定义所需列数
                int columnNum = rowName.length;
                // 在索引2的位置创建行(最顶端的行开始的第二行)
                XSSFRow rowRowName = sheet.createRow(0);
                rowRowName.setHeightInPoints(30);
                // 将列头设置到sheet的单元格中
                setHearderToSheet(columnTopStyle, columnNum, rowRowName);
                List<String[]> dataList = dataLists.get(z);
                // 将查询出的数据设置到sheet对应的单元格中
                fillDataToSheet(sheet, style, dataList);
            }
            exportFileStream(fileName, workbook, request, response);
        } catch (Exception e) {
            log.error("Excel多表导出失败，失败原因：", e);
        }
    }


    /**
     * 列头单元格样式
     *
     * @param workbook the work book
     * @param type     the resource type enum
     * @return the cell style
     */
    private XSSFCellStyle getCellStyle(XSSFWorkbook workbook, ExcelEnum type) {
        // 设置字体
        XSSFFont font = workbook.createFont();
        if (ExcelEnum.TOP.equals(type)) {
            // 设置字体大小
            font.setFontHeightInPoints((short) 12);
            // 字体加粗
            font.setBold(true);
        }
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        // 设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 返回
        return style;
    }

    /**
     * 填充数据到sheet
     *
     * @param sheet    the target sheet
     * @param style    the cell style
     * @param dataList the data list
     */
    private void fillDataToSheet(XSSFSheet sheet, XSSFCellStyle style, List<String[]> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            String[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            row.setHeightInPoints(22);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;
                cell = row.createCell(j, CellType.STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j]);
                } else {
                    cell.setCellValue("");
                }
                // 设置单元格样式
                cell.setCellStyle(style);
            }
        }
    }

    /**
     * 设置表头
     *
     * @param columnTopStyle top style
     * @param columnNum      the column num
     * @param rowRowName     the row name
     */
    private void setHearderToSheet(XSSFCellStyle columnTopStyle, int columnNum, XSSFRow rowRowName) {
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            // 设置列头单元格的数据类型
            cellRowName.setCellType(CellType.STRING);
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            // 设置列头单元格的值
            cellRowName.setCellValue(text);
            // 设置列头单元格样式
            cellRowName.setCellStyle(columnTopStyle);
        }
    }

    /**
     * 导出文件
     *
     * @param fileName the file name
     * @param workbook the work book
     * @param request  the http servlet request
     * @param response the http servlet response
     */
    public static void exportFileStream(String fileName, Workbook workbook, HttpServletRequest request, HttpServletResponse response) {
        ServletOutputStream out = null;
        final String userAgent = request.getHeader("USER-AGENT");
        try {
            out = response.getOutputStream();
            //清空输出流
            response.reset();
            String finalFileName = null;
            if (StringUtils.contains(userAgent, "MSIE")) {
                // IE浏览器
                finalFileName = URLEncoder.encode(fileName, "UTF8");
            } else if (StringUtils.contains(userAgent, "Mozilla")) {
                // google,火狐浏览器
                finalFileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                //其他浏览器
                finalFileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.setHeader("content-disposition", "attachment;filename=" + finalFileName);
            workbook.write(out);
        } catch (IOException var12) {
            throw new Exception("excel导出失败");
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException var11) {
                var11.printStackTrace();
            }
        }
    }
}

定义了一个枚举类

import java.util.EnumSet;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * <p>excel枚举类</p>
 *
 * @author Vick C on 2018/11/8
 */
public enum ExcelEnum {
    /**
     * 顶部
     */
    TOP("top"),
    /**
     * 行
     */
    ROW("row"),
    /**
     * 列
     */
    COLUMN("column");

    private String value;

    private static Map<String, ExcelEnum> map;

    ExcelEnum(String value) {
        this.value = value;
    }

    static {
        map = EnumSet.allOf(ExcelEnum.class).stream().collect(Collectors.toMap(ExcelEnum::getValue, value -> value));
    }

    public String getValue() {
        return this.value;
    }

    public static ExcelEnum fromValue(String value) {
        return map.get(value);
    }

}