java 读取 excel 表格内容

一、添加依赖

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

二、工具类
根据文件后缀判断 2003 || 2007 || 2010 格式。

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

import java.io.InputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Map;


public class ExcelUtils {

	private static Workbook wb;
    private static Sheet sheet;
    private static Row row;

private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";


/**
 * 读取表头
 *
 * @param inputStream inputStream
 * @param suffix      file suffix
 * @return map <index,value>
 */
public static Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) {
    getWorkbook(inputStream, suffix);
    sheet = wb.getSheetAt(0);
    row = sheet.getRow(0);
    // 标题总列数
    int colNum = row.getPhysicalNumberOfCells();
    Map<Integer, String> map = new HashMap<>();
    for (int i = 0; i < colNum; i++) {
        map.put(i, row.getCell(i).getStringCellValue());
    }
    return map;
}

/**
 * 读取excel内容
 *
 * @param inputStream 文件
 * @return Map<行, Map < 下标, Object>>
 */
public static Map<Integer, Map<Integer, String>> readExcelContent(InputStream inputStream, String suffix) {
    getWorkbook(inputStream, suffix);
    Map<Integer, Map<Integer, String>> content = new HashMap<>();
    sheet = wb.getSheetAt(0);
    // 得到总行数
    int rowNum = sheet.getLastRowNum();
    row = sheet.getRow(0);
    int colNum = row.getPhysicalNumberOfCells();
    // 正文内容应该从第二行开始,第一行为表头的标题
    for (int i = 1; i <= rowNum; i++) {
        row = sheet.getRow(i);
        int j = 0;
        Map<Integer, String> cellValue = new HashMap<>();
        while (j < colNum) {
            String obj = getCellFormatValue(row.getCell(j));
            cellValue.put(j, obj);
            j++;
        }
        content.put(i, cellValue);
    }
    return content;
}

private static String getCellFormatValue(Cell cell) {
    String cellValue = "";
    if (cell != null) {
        // 判断当前Cell的Type
        switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case Cell.CELL_TYPE_NUMERIC:
            case Cell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (DateUtil.isCellDateFormatted(cell)) {
                    DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss");
                    Instant instant = cell.getDateCellValue().toInstant();
                    ZoneId zoneId = ZoneId.systemDefault();
                    LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId);
                    cellValue = dateTimeFormatter.format(localDateTime);
                } else {
                    // 如果是纯数字
                    // 取得当前Cell的数值
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRING
            case Cell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellValue = cell.getRichStringCellValue().getString();
                break;
            default:
                // 默认的Cell值
                cellValue = "";
        }
    }
    return cellValue;
}


private static void getWorkbook(InputStream inputStream, String suffix) {

    try {
        //2003
        if (EXCEL_XLS.equals(suffix)) {
            wb = new HSSFWorkbook(inputStream);
            //2007/2010
        } else if (EXCEL_XLSX.equals(suffix)) {
            wb = new XSSFWorkbook(inputStream);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    }

}

转自：https://blog.csdn.net/weixin_40467684/article/details/91883896