JAVA POI 一步直接导出EXCEL

 最近老是遇到需要导出excel的业务，觉得一直cv太麻烦了，而且poi的业务重复度很高，影响阅读，自己封装了一个excel处理类，目前暂时只支持普通的数据导出，复杂的单元格合并的情况暂时没有处理。

废话不多说，直接贴代码，主要作为自己的笔记记录，大佬勿喷！有需要者自取

maven依赖

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

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

ExcelExportParam.java

用于设定导出的参数

package com.pub.bean;

import lombok.Data;
import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;

/**
 * @Description: [ExcelExportParam]
 * @Author: Luo Ly
 * @Date: 2022/5/7 16:21
 */
@Data
public class ExcelExportParam {
    private String title; //表格标题
    private String fileName; //文件名称
    private JSONArray data; //数据主体
    private String[] head; //表头
    private String[] cellKey; //单元格中对应表头的字段名（注意：必须与表头对应，否则数据填充错乱）
    private int[] cellWidth; //单元格中对应表头的字段宽度（注意：必须与表头对应，否则数据填充错乱，单位为：字符）

    private Boolean hasNumber; //是否添加序号
    private HSSFCellStyle titleStyle; //标题样式（不设置则使用默认样式）
    private HSSFCellStyle headStyle; //表头样式（不设置则使用默认样式）
    private HSSFCellStyle cellStyle; //单元格样式（不设置则使用默认样式）

}

ExcelExportService.java

poi的处理类，准备后期新增其他处理方式的，目前只支持普通数据

package com.pub.service;

import com.pub.bean.ExcelExportParam;
import com.utils.ExcelUtils;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;

/**
 * @Description: [ExcelExportService]
 * @Author: Luo Ly
 * @Date: 2022/5/7 16:21
 */
public class ExcelExportService {


    /**
     *常规文件导出（适用于无单元格合并，无特殊数据处理的普通数据列表 ）
     * @param response
     * @param param 参数配置
     * @throws Exception
     */
    public static void exportExcelNormal(HttpServletResponse response,ExcelExportParam param) throws Exception{
        //参数校验
        if(param.getHead()==null || param.getHead().length<=0){
            System.err.println("表头参数【head】不完整！");
            return;
        }
        if(param.getCellKey()==null || param.getCellKey().length<=0){
            System.err.println("字段参数【cellKey】不完整！");
            return;
        }
        if(param.getCellWidth()==null || param.getCellWidth().length<=0){
            System.err.println("单元格宽度参数【cellWidth】不完整！");
            return;
        }

        if(param.getHasNumber()==null){
            param.setHasNumber(false);
        }

        HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象
        HSSFSheet sheet = workbook.createSheet("sheet");// 产生工作表对象

        HSSFCellStyle titleStyle = workbook.createCellStyle(); // 标题
        HSSFCellStyle headStyle = workbook.createCellStyle(); // 表头
        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 表格内容
        if(param.getTitleStyle()!=null){
            titleStyle=param.getTitleStyle();
        }else{
            titleStyle= ExcelUtils.getTitleStyle(workbook);
        }
        if(param.getHeadStyle()!=null){
            headStyle=param.getHeadStyle();
        }else{
            headStyle= ExcelUtils.getHeadStyle(workbook);
        }
        if(param.getCellStyle()!=null){
            cellStyle=param.getCellStyle();
        }else{
            cellStyle= ExcelUtils.getCellStyle(workbook);
        }



        // 设置标题栏
        HSSFRow titleRow = sheet.createRow(0);
        if(param.getHasNumber()){
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, param.getHead().length);
            sheet.addMergedRegion(cra);
        }else{
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, param.getHead().length - 1);
            sheet.addMergedRegion(cra);
        }


        titleRow.setHeight((short) (40 * 20));
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue(param.getTitle());

        // 表头
        HSSFRow row = sheet.createRow(1);
        if(param.getHasNumber()){
            String []newHead=new String[param.getHead().length+1];
            int [] newWidth=new int[param.getCellWidth().length+1];
            for (int i = 0; i < newHead.length; i++) {
                if(i==0){
                    newHead[0]="序号";
                    newWidth[0]=5;
                }else{
                    newHead[i]=param.getHead()[i-1];
                    newWidth[i]=param.getCellWidth()[i-1];
                }
            }
            param.setHead(newHead);
            param.setCellWidth(newWidth);
        }

        for (int i = 0; i < param.getHead().length; i++) {
            row.setHeight((short) (25 * 20));
            HSSFCell headCell = row.createCell(i);
            headCell.setCellStyle(headStyle);
            headCell.setCellValue(param.getHead()[i]);
            sheet.setColumnWidth(i, ExcelUtils.formatWidth2Char(param.getCellWidth()[i]));

        }


        //数据主体
        JSONArray data=param.getData();
        for (int i = 0; i < data.size(); i++) {
            JSONObject item=data.getJSONObject(i);

            HSSFRow itemRow = sheet.createRow(i+2); //除去标题和表头的序列
            itemRow.setHeightInPoints(25);

            if(param.getHasNumber()){
                HSSFCell cell = itemRow.createCell(0);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(i + 1);
            }

            for (int i1 = 0; i1 < param.getCellKey().length; i1++) {
                String key=param.getCellKey()[i1];
                if(item.has(key)){
                    int cellIndex=param.getHasNumber()?i1+1:i1; //当前列的序列
                    HSSFCell cell = itemRow.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getString(key));
                }else{
                    System.err.println("数据【"+i+"】不存在"+key);
                }


            }



        }

        //为作用单元格加上边框
        ExcelUtils.setBorder(sheet,true,0,data.size(),0,param.getHead().length);



        // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
        response.reset();
        response.setContentType("APPLICATION/vnd.ms-excel");
        // 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
        response.setHeader("Content-Disposition", "attachment;filename=\""
                + new String(param.getFileName().getBytes("gb2312"), "ISO8859-1")
                + ".xls\"");
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    }

}

ExcelUtils.java

业务过程中一些的工具，单元格高宽换算的部分是在网上大佬的代码中cv过来魔改的，查了很多资料很难做到统一。然后我就根据实际导出效果来计算的大概数据，如果在业务中出现高宽异常的情况，可能需要调整一下这部分的逻辑

package com.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import sofun.util.StringUtil;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * POI excel 工具集
 *
 * @Description: [ExcelUtils]
 * @Author: Luo Ly
 * @Date: 2022/5/7 15:21
 */
public class ExcelUtils {


    /**
     * 合并指定单元格并设置边框
     *
     * @param sheet
     * @param startRow
     * @param endRow
     * @param startCell
     * @param endCell
     * @date 2022年5月6日16:43:58
     * @author lly
     */
    public static void mergedRegion(HSSFSheet sheet, int startRow, int endRow, int startCell, int endCell) {
        CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCell, endCell);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);

    }

    /**
     * 给指定单元格加上边框
     *
     * @param sheet
     * @param autoRowHeight 是否自定调整行高
     * @param startRow      开始行
     * @param endRow        结束行
     * @param startCell     开始列
     * @param endCell       结束列
     */
    public static void setBorder(HSSFSheet sheet, boolean autoRowHeight, int startRow, int endRow, int startCell, int endCell) {

        for (int i = startRow; i < endRow; i++) {
            for (int j = startCell; j < endCell; j++) {
                CellRangeAddress cra = new CellRangeAddress(i, i, j, j);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                HSSFCell cell = sheet.getRow(i).getCell(j);
                if (cell != null && autoRowHeight) {
                    autoRowHeight(cell, (short) 0);
                }

            }
        }

    }


    /**
     * 获取默认标题样式
     *
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20); // 字号
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return titleStyle;
    }

    /**
     * 获取默认表头样式
     *
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headStyle = workbook.createCellStyle();
        HSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 15); // 字号
        headFont.setBold(true);
        headStyle.setFont(headFont);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置25灰度背景颜色
//        headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return headStyle;
    }

    /**
     * 获取默认单元格样式
     *
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 10); // 字号
        cellFont.setBold(true);
        cellStyle.setFont(cellFont);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        cellStyle.setWrapText(true); //自动换行
        return cellStyle;
    }


    /**
     * 自适应excel行高
     *
     * @param cell
     * @param margin
     */
    public static void autoRowHeight(HSSFCell cell, short margin) {
        if (cell == null) {
            return;
        }
        String cellVal = getStringCellValue(cell);
        if (StringUtil.isEmpty(cellVal)) {
            return;
        }

        HSSFRow row = cell.getRow();
        HSSFWorkbook workbook = row.getSheet().getWorkbook();

        // pt = px*72/96 = px*3/4

        //换算思路如下（根据使用情况测出，与实际单位换算有出入）：

        // 宽度计算
        // 字体12px时 50宽 可容纳22个字
        // 每个字占宽约 2.28宽
        // 字体每px占约0.19宽度
        // 宽度20的单元格 可容纳字数应为：20/(12*0.19) = 8.78个字

        // 高度计算
        // 字体为12px时 高30pt的单元格可容纳2行
        // 单行占高 30/2=15pt
        // 字体每px约占15/12=1.25pt高度
        // 2行应占高度为 1.25*12*2=30pt;

        float fontSize = cell.getCellStyle().getFont(workbook).getFontHeightInPoints(); // 字体大小
        float cellWidth = getCellWidth(cell);// 单元格宽度

        int fontForCellWidth = (int) (cellWidth / (fontSize * 0.19)); // 每行可容纳字数
        if (fontForCellWidth <= 0) {
            return;
        }


        int fontLength = cellVal.length();
        // 计算内容中的换行
        String v = cellVal.replaceAll("\\n|\\r", "");

        // 单元格内换行符出现的次数
        int line = fontLength - v.length();

        int rowNum = fontLength / fontForCellWidth + (fontLength % fontForCellWidth > 0 ? 1 : 0);// 分行
        rowNum += line;
        short cellHeight = (short) ((1.25 * fontSize + margin) * rowNum);

        float oldH = row.getHeightInPoints();
        if (cellHeight > oldH) {
            row.setHeightInPoints(cellHeight);
        }
    }

    private static String getStringCellValue(Cell cell) {

        if (cell.getCellType() == CellType.FORMULA) {
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd")) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            }
            return cell.getCellFormula();
        } else if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                String dateStr = "";
                int style = cell.getCellStyle().getDataFormat();
                Date date = cell.getDateCellValue();
                // 对不一样格式的日期类型作不一样的输出，与单元格格式保持一致
                switch (style) {
                    case 178:
                        dateStr = new SimpleDateFormat("yyyy'年'M'月'd'日'").format(date);
                        break;
                    case 14:
                        dateStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        break;
                    case 179:
                        dateStr = new SimpleDateFormat("yyyy/MM/dd HH:mm").format(date);
                        break;
                    case 181:
                        dateStr = new SimpleDateFormat("yyyy/MM/dd HH:mm a ").format(date);
                        break;
                    case 22:
                        dateStr = new SimpleDateFormat(" yyyy/MM/dd HH:mm:ss ").format(date);
                        break;
                    default:
                        break;
                }
                return dateStr;
            } else {
                cell.setCellType(CellType.STRING);
            }
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
        } else {
            return "";
        }


    }


    /**
     * 字符长度转poi长度
     *
     * @param columnWidth
     * @return
     */
    public static float formatWidth(float columnWidth) {
        columnWidth = (float) ((columnWidth / 256) - 0.72);
        return columnWidth;
    }

    /**
     * 转字符长度，输出数值与excel中数值一直（有微弱偏差，可忽略）
     *
     * @param num
     * @return
     */
    public static int formatWidth2Char(float num) {
        return (int) ((num + 0.72) * 256);
    }


    private static float getCellWidth(HSSFCell cell) {
        if (cell == null) {
            return 0;
        }

        HSSFSheet sheet = cell.getSheet();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();
        // float width = sheet.getColumnWidth(columnIndex);
        float width = formatWidth(sheet.getColumnWidth(columnIndex));
        boolean isPartOfRegion = false;
        int firstColumn = 0;
        int lastColumn = 0;
        int firstRow = 0;
        int lastRow = 0;
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            firstColumn = ca.getFirstColumn();
            lastColumn = ca.getLastColumn();
            firstRow = ca.getFirstRow();
            lastRow = ca.getLastRow();
            if (rowIndex == firstRow && rowIndex <= lastRow) {
                if (columnIndex == firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }

            }

        }

        if (isPartOfRegion) {
            width = 0;

            for (int i = firstColumn; i <= lastColumn; i++) {
                // width += sheet.getColumnWidth(i);
                width += formatWidth(sheet.getColumnWidth(i));
            }

        }

        return width;

    }
}

调用范例

懒得写假数据了，前面几行获取数据的代码自行替换

组织装ExcelExportParam 然后直接调用就OK了

方便处理 data使用的JSONArray 如果业务不允许需要自行修改service

    @RequestMapping("export")
    public void export(HttpServletRequest request, HttpServletResponse response) {
        try {
            //必须转码，不然会导致乱码查询参数错误
            request.setCharacterEncoding("UTF-8");
            String exportTitle=request.getParameter("exportTitle");

            JSONObject queryParam = super.getQueryParam(request);
            PageResult pageResult=super.pageQuery(1,9999,queryParam);
            JSONArray data=JSONArray.fromObject(pageResult.getData());


            //表头
            String []head=new String[]{"标题","文件类型","流水号","状态","接收人姓名","接收人部门"};
            
            //表头对应字段名
            String []cellKey=new String[]{"title","type","documentNumber","state","receiveName","receiveDeptName"};
            
            //表头对应单元格宽度
            int []cellWidth=new int[]{60,20,15,30,20,10};


            ExcelExportParam eep=new ExcelExportParam();
            eep.setFileName(exportTitle);
            eep.setTitle(exportTitle);
            eep.setHasNumber(true);
            eep.setHead(head);
            eep.setCellKey(cellKey);
            eep.setCellWidth(cellWidth);


            eep.setData(data);



            ExcelExportService.exportExcelNormal(response,eep);
        }catch (Exception e){
            e.printStackTrace();
            System.err.println("导出失败!");
        }
    }