POI Excel 通用工具类

package com.ruoyi.common.excel.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * POI Excel 通用工具类
 * @Author：
 * @Date：2025-11-05 
 * @Description
 * @Version 1.0
 */
public class ExcelUtils {

    /**
     *  导出（数据库表数据 到 excel表格）
     * @param dataList
     * @param headers
     * @param fieldNames
     * @param sheetName
     * @param outputStream
     * @param <T>
     * @throws Exception
     */
    public static <T> void exportExcel(List<T> dataList, String[] headers, String[] fieldNames,
                                       String sheetName, OutputStream outputStream) throws Exception {
        if(headers.length != fieldNames.length){
            throw new IllegalArgumentException("表头数量与属性名数量不匹配");
        }
        /**
         * 创建工作簿,
         */

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }
        //写入实体数据
        if(dataList != null && !dataList.isEmpty()){
            Class<?> clazz = dataList.get(0).getClass();
            for (int i = 1; i < dataList.size(); i++) {
                T data = dataList.get(i);
                Row dataRow = sheet.createRow(i+1);
                //反射获取实体属性值，写入对应单元格
                for (int j = 0; j < fieldNames.length; j++) {
                    Field field = clazz.getDeclaredField(fieldNames[j]);
                    field.setAccessible(true);
                    Object value = field.get(data);
                    Cell cell = dataRow.createCell(j);
                    setCellValue(cell,value);
                }
            }
        }

        //自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //写入流并关闭资源

        try {
            workbook.write(outputStream);
        } finally {
            workbook.close();
            outputStream.close();
        }
    }

    /**
     * 导入 excel表格到 List 集合
     * @param inputStream
     * @param clazz
     * @param fieldNames
     * @param headerRowIndex
     * @return
     * @param <T>
     * @throws Exception
     */
    public static <T> List<T> importExcel(InputStream inputStream, Class<T> clazz,
                                          String[] fieldNames,int headerRowIndex) throws Exception {
        List<T> dataList = new ArrayList<>();
        //读取工作簿
        try(Workbook workbook = WorkbookFactory.create(inputStream)){
            Sheet sheet = workbook.getSheetAt(0);
            if(sheet == null){
                return dataList;
            }
            //遍历数据行，从表头下一行开始
            int firstDataRowIndex = headerRowIndex + 1;
            int lastDataRowIndex = sheet.getLastRowNum();
            for(int i = firstDataRowIndex; i <= lastDataRowIndex; i++){
                Row row = sheet.getRow(i);
                if(row == null){
                    continue;
                }
                //反射创建实体对象
                T entity = clazz.getDeclaredConstructor().newInstance();
                //读取单元格值，赋值给实体属性
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.getCell(j);
                    String cellValue = cell.getStringCellValue();
                    Field field = clazz.getDeclaredField(fieldNames[j]);
                    field.setAccessible(true);
                    setFieldValue(field,entity,cellValue);
                }
                dataList.add(entity);
            }
        }
        return dataList;

    }

    /**
     * 统一设置单元格值
     */
    private static void setCellValue(Cell cell, Object value) {
        if(value == null){
            cell.setCellValue("");
            return;
        }
        if(value instanceof String){
            cell.setCellValue((String) value);
        } else if(value instanceof Integer){
            cell.setCellValue((Integer) value);
        } else if(value instanceof Double){
            cell.setCellValue((Double) value);
        } else if(value instanceof Long){
            cell.setCellValue((Long) value);
        } else if(value instanceof Boolean){
            cell.setCellValue((Boolean) value);
        } else if(value instanceof Date){
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }

    }

    /**
     * 统一获取单元格值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value ="";
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue().trim();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    value = cell.getDateCellValue().toString();
                } else{
                    //处理字数过长，避免科学计数法
                    value = new DataFormatter().formatCellValue(cell);
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                value = new DataFormatter().formatCellValue(cell);
                break;
            default:
                value = "";
        }
        return value;
    }


    private static <T> void setFieldValue(Field field,T entity,String value) throws IllegalAccessException {
        if(value ==null || value.isEmpty()) return;
        Class<?> fieldType = field.getType();
        if (fieldType == String.class) {
            field.set(entity, value);
        } else if (fieldType == Integer.class || fieldType == int.class) {
            field.set(entity, Integer.parseInt(value));
        } else if (fieldType == Double.class || fieldType == double.class) {
            field.set(entity, Double.parseDouble(value));
        } else if (fieldType == Long.class || fieldType == long.class) {
            field.set(entity, Long.parseLong(value));
        }  else if (fieldType == Date.class) {

            try {
                field.set(entity, new Date(value));
            } catch (IllegalArgumentException e) {
                field.set(entity, null);
            }
        }
    }
}