java读取Excel工具包

引入依赖

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

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

import com.eastmoney.base.BusinessException;
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 org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

/**
 * @author ：Yangxx
 * @date ：2021/11/9 19:22
 * @description：
 */
@Slf4j
public class OfficeUtils {

    public static Object[][] readExcelContent(MultipartFile file) {
        // 上传文件名
        Workbook wb = getWb(file);
        if (wb == null) {
            throw new BusinessException("文件不能为空");
        }
        Sheet sheet = wb.getSheetAt(0);
        //总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        //总列数
        int colNum = row.getPhysicalNumberOfCells();
        Object[][] table = new Object[rowNum][colNum];
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 0; i < rowNum; i++) {
            row = sheet.getRow(i);
            for (int j = 0; j < colNum; j++) {
                Object value = getCellFormatValue(row.getCell(j));
                table[i][j] = value;
            }

        }
        return table;
    }

    //根据Cell类型设置数据
    private static Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case NUMERIC:
                    cellvalue = cell.getNumericCellValue();
                    break;
                case FORMULA:
                    cellvalue = cell.getDateCellValue();
                    break;
                case STRING:
                    cellvalue = cell.getRichStringCellValue();
                    break;
                default:
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    private static Workbook getWb(MultipartFile mf) {
        String filepath = mf.getOriginalFilename();
        String ext = filepath.substring(filepath.lastIndexOf("."));
        Workbook wb = null;
        try {
            InputStream is = mf.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            log.warn("FileNotFoundException:{}", e);
        } catch (IOException e) {
            log.warn("IOException:{}", e);
        }
        return wb;
    }
}