ExcelUtil

<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>

import com.isyscore.ibo.neo.NeoMap;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import static org.apache.poi.ss.usermodel.Cell.*;
/**
 * @author shizi
 * @since 2020/3/27 5:35 PM
 */
@Slf4j
@UtilityClass
public class ExcelUtil {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    /**
     * 读取xls和xlsx 对应的文件的某一个sheet对应的数据
     * 
     * 注意：
     * 这里会默认跳过第一行，因为第一行一般都是列的描述
     * 
     * @param fileName excel对应的文件
     * @param sheetNum sheet的下标
     * @param columnMap 某一sheet中列和对应的别名对应：key为从0开始的索引
     * @return 读取到的key和value对应的数据
     */
    public List<NeoMap> read(String fileName, Integer sheetNum, NeoMap columnMap) {
        Workbook workbook;
        FileInputStream inputStream = null;
        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                log.warn("指定的Excel文件不存在！");
                return null;
            }
            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);
            // 读取excel中的数据
            return parseExcel(sheetNum, workbook, columnMap);
        } catch (Exception e) {
            log.warn("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage(), e);
            return Collections.emptyList();
        } finally {
            try {
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.warn("关闭数据流出错！错误信息：" + e.getMessage(), e);
            }
        }
    }
    /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private List<NeoMap> parseExcel(Integer sheetNum, Workbook workbook, NeoMap columnMap) {
        List<NeoMap> resultDataList = new ArrayList<>();
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 校验sheet是否合法
        if (sheet == null) {
            return Collections.emptyList();
        }
        // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            log.warn("解析Excel失败，在第一行没有读取到任何数据！");
        }
        // 解析每一行的数据，构造数据对象
        int rowStart = firstRowNum + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                continue;
            }
            NeoMap resultData = convertRowToData(row, columnMap);
            if (null == resultData) {
                log.warn("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                continue;
            }
            resultDataList.add(resultData);
        }
        return resultDataList;
    }
    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     *
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private NeoMap convertRowToData(Row row, NeoMap columnMap) {
        NeoMap resultData = new NeoMap();
        Cell cell;
        int cellNum = 0;
        // 获取姓名
        while ((cell = row.getCell(cellNum)) != null){
            resultData.put(columnMap.getString(String.valueOf(cellNum)), convertCellValueToString(cell));
            cellNum++;
        }
        return resultData;
    }
    /**
     * 将单元格内容转换为字符串
     */
    private String convertCellValueToString(Cell cell) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case CELL_TYPE_NUMERIC:
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case CELL_TYPE_STRING:
                returnValue = cell.getStringCellValue();
                break;
            case CELL_TYPE_BOOLEAN:
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case CELL_TYPE_BLANK:
                break;
            case CELL_TYPE_FORMULA:
                returnValue = cell.getCellFormula();
                break;
            case CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }
    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }
}

比如像这种

解析得到的数据可以达到这种程度

[
    {
        "address":"杭州耿迪医药科技有限公司",
        "idCard":"330326********5421",
        "inputType":"引进往届大专生",
        "name":"asdf"
    },
    {
        "address":"浙江泽大律师事务所",
        "idCard":"332501********0021",
        "inputType":"引进往届本科生",
        "name":"asdf"
    }
]

将文件流导出到表格

@Test
public void test21() {
    HashMap<String, String> fieldMap = new HashMap<>();
    fieldMap.put("name","测试");
    List<DataEntity> dataList = new ArrayList<>();
    dataList.add(new DataEntity("数据1"));
    dataList.add(new DataEntity("数据2"));
    dataList.add(new DataEntity("数据3"));
    ExcelUtil.export("分页1", dataList, fieldMap);
}

import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
/**
 * @author shizi
 * @since 2021-04-14 10:27:34
 */
@Slf4j
@UtilityClass
public class ExcelUtil {
    /**
     * 导出Excel
     *
     * @param sheetName 分页名
     * @param list      要导出的数据集合
     * @param fieldMap  中英文字段对应Map，即要导出的excel表头
     */
    public <T> void export(String file, String sheetName, List<T> list, Map<String, String> fieldMap) {
        try {
            //创建一个WorkBook,对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            //在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
            HSSFSheet sheet = wb.createSheet(sheetName);
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            //创建一个居中格式
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 填充工作表
            fillSheet(sheet, list, fieldMap, style);
            //将文件输出
            //            OutputStream ouputStream = response.getOutputStream();
            FileOutputStream ouputStream = new FileOutputStream(new File(file));
            wb.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            log.info("导出Excel失败！");
            log.error(e.getMessage());
        }
        log.info("导出Excel成功，位置：" + file);
    }
    /**
     * 根据字段名获取字段对象
     *
     * @param fieldName 字段名
     * @param clazz     包含该字段的类
     * @return 字段
     */
    public static Field getFieldByName(String fieldName, Class<?> clazz) {
        //        log.info("根据字段名获取字段对象:getFieldByName()");
        // 拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();
        // 如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            //如果本类中存在该字段，则返回
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }
        // 否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            //递归
            return getFieldByName(fieldName, superClazz);
        }
        // 如果本类和父类都没有，则返回空
        return null;
    }
    /**
     * 根据字段名获取字段值
     *
     * @param fieldName 字段名
     * @param object         对象
     * @return 字段值
     * @throws Exception 异常
     */
    public static Object getFieldValueByName(String fieldName, Object object) throws Exception {
        Object value = null;
        //根据字段名得到字段对象
        Field field = getFieldByName(fieldName, object.getClass());
        //如果该字段存在，则取出该字段的值
        if (field != null) {
            field.setAccessible(true);//类中的成员变量为private,在类外边使用属性值，故必须进行此操作
            value = field.get(object);//获取当前对象中当前Field的value
        } else {
            throw new Exception(object.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }
        return value;
    }
    /**
     * 根据带路径或不带路径的属性名获取属性值,即接受简单属性名，
     * 如userName等，又接受带路径的属性名，如student.department.name等
     *
     * @param fieldNameSequence 带路径的属性名或简单属性名
     * @param object                 对象
     * @return 属性值
     * @throws Exception 异常
     */
    public static Object getFieldValueByNameSequence(String fieldNameSequence, Object object) throws Exception {
        Object value = null;
        // 将fieldNameSequence进行拆分
        String[] attributes = fieldNameSequence.split("\\.");
        if (attributes.length == 1) {
            value = getFieldValueByName(fieldNameSequence, object);
        } else {
            // 根据数组中第一个连接属性名获取连接属性对象，如student.department.name
            Object fieldObj = getFieldValueByName(attributes[0], object);
            //截取除第一个属性名之后的路径
            String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
            //递归得到最终的属性对象的值
            value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
        }
        return value;
    }
    /**
     * 向工作表中填充数据
     *
     * @param sheet    excel的工作表名称
     * @param list     数据源
     * @param fieldMap 中英文字段对应关系的Map
     * @param style    表格中的格式
     * @throws Exception 异常
     */
    public static <T> void fillSheet(HSSFSheet sheet, List<T> list, Map<String, String> fieldMap, HSSFCellStyle style) throws Exception {
        // 定义存放英文字段名和中文字段名的数组
        String[] enFields = new String[fieldMap.size()];
        String[] cnFields = new String[fieldMap.size()];
        // 填充数组
        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            enFields[count] = entry.getKey();
            cnFields[count] = entry.getValue();
            count++;
        }
        //在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 填充表头
        for (int i = 0; i < cnFields.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(cnFields[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
        }
        // 填充内容
        for (int index = 0; index < list.size(); index++) {
            row = sheet.createRow(index + 1);
            // 获取单个对象
            T item = list.get(index);
            for (int i = 0; i < enFields.length; i++) {
                Object objValue = getFieldValueByNameSequence(enFields[i], item);
                String fieldValue = objValue == null ? "" : objValue.toString();
                row.createCell(i).setCellValue(fieldValue);
            }
        }
    }
}

excel的导出示例

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
 * @author shizi
 * @since 2022-03-30 12:00:04
 */
@Slf4j
public class ExcepDemo {
    @Test
    @SneakyThrows
    public void test21() {
        HashMap<String, String> fieldMap = new HashMap<>();
        fieldMap.put("name","名字");
        fieldMap.put("memory","内存");
        fieldMap.put("cpu","cpu");
        fieldMap.put("storage","存储");
        fieldMap.put("time","时间");
        String equalContent = FileUtil.readFromResource(ExcepDemo.class, "/json/service.txt");
        List<ServiceEntity> dataList = Arrays.stream(equalContent.split("\n")).flatMap(e->JSON.parseArray(e, ServiceEntity.class).stream()).collect(Collectors.toList());
        Map<String, List<ServiceEntity>> dataMap = dataList.stream().collect(Collectors.groupingBy(ServiceEntity::getName));
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream ouputStream = null;
        ouputStream = new FileOutputStream(new File("/Users/zhouzhenyong/tem/export/service_final1.xls"));
        dataMap.forEach((k, v) -> {
            HSSFSheet sheet = wb.createSheet(k);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            try {
                ExcelUtil.fillSheet(sheet, v, fieldMap, style);
            } catch (Throwable e) {
                e.printStackTrace();
            }
        });
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }
    @Test
    @SneakyThrows
    public void test22() {
        HashMap<String, String> fieldMap = new HashMap<>();
        fieldMap.put("cpuUsage","cpu使用");
        fieldMap.put("diskTotal","磁盘总量（有问题）");
        fieldMap.put("diskUsed","磁盘使用量（有问题）");
        fieldMap.put("diskRatio","磁盘比率");
        fieldMap.put("memoryUsage","内存使用");
        fieldMap.put("createTime","搜集时间");
        String equalContent = FileUtil.readFromResource(ExcepDemo.class, "/json/total.txt");
        List<TotalEntity> dataList = Arrays.stream(equalContent.split("\n")).map(e->{
            TotalEntity data = JSON.parseObject(e, TotalEntity.class);
            data.setCpuUsage(data.getCpuUsage().substring(0, 5) + "%");
            Long totalSize = Long.valueOf(data.getDiskTotal());
            Long useSize = Long.valueOf(data.getDiskUsed());
            String ratio = String.valueOf((useSize * 100 )/totalSize);
            data.setDiskRatio(ratio + "%");
            data.setDiskTotal(changeSize(data.getDiskTotal()));
            data.setDiskUsed(changeSize(data.getDiskUsed()));
            data.setMemoryUsage(data.getMemoryUsage() + "%");
            return data;
        }).collect(Collectors.toList());
        ExcelUtil.export("/Users/zhouzhenyong/tem/export/total_final1.xls", "总况", dataList, fieldMap);
    }
    private Integer ByteSize = 1024;
    private String changeSize(String disk) {
        Long data = Long.valueOf(disk);
        if (data < ByteSize) {
            return data + "B";
        }
        data /= ByteSize;
        if (data < ByteSize) {
            return data + "KB";
        }
        data /= ByteSize;
        if (data < ByteSize) {
            return data + "MB";
        }
        data /= ByteSize;
        if (data < ByteSize) {
            return data + "GB";
        }
        return data + "TB";
    }
}