Excel导出功能（poi实现）

男娘i 2022-04-18 06:10 384阅读 0赞

导包：

<properties>
        <poi-ooxml.version>4.0.0</poi-ooxml.version>
    </properties>
    
    
    <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);
        }
    
    }