JavaExcel导入导出读取

引入maven依赖

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

利用FileMagic对文件格式进行判断

public ResResult importExcel(MultipartFile file) {
        try {
            InputStream in = new BufferedInputStream(file.getInputStream());
            /**
             * 优化excel类型判断
             * 注意可能会获取报错 getFileMagic() only operates on streams which support mark(int)
             * 原因，BufferedInputStream中markSupported方法返回值为false造成的InputStream中返回值是true，所以改为InputStream is = new BufferedInputStream(multipartFile.getInputStream())
             */
            FileMagic fileMagic = FileMagic.valueOf(in);
            if (Objects.equals(fileMagic, FileMagic.OLE2)) {
                return importExcelXLS(file); // 这是xls
            } else if (Objects.equals(fileMagic, FileMagic.OOXML)) {
                return importExcelXLSX(file); // 这是xlsx
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ResResult.fail("导入异常！");
    }

HSSFWorkbook

读取xls文件，97-2003版本的excel

package com.excel;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * 一个EXCEL包含如下几个基础组成
 * HSSFSheet：表单，通过HSSFWorkbook获取
 * Row：行，通过HSSFSheet获取
 * Cell：单元格，通过Row获取
 * 下标均从0开始
 * 导入97-2003版本的excel，xls格式
 * */
    public ResResult importExcelXLS(MultipartFile file) throws FileNotFoundException {
        if (file.isEmpty()) {
            return ResResult.fail("文件为空！");
        }
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            HSSFWorkbook wb = new HSSFWorkbook(inputStream);
            // 获取sheet页数
            int sheetNum = wb.getNumberOfSheets();
            for (int i=0;i<sheetNum;i++) {
                Sheet sheet = wb.getSheetAt(i);
                for (int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++) {
                    // 获取行数
                    Row row = sheet.getRow(rowNum);
                    log.warn("开始读取第"+(i+1)+"个Sheet!第"+rowNum+"行!该行一共"+row.getLastCellNum()+"列!");

                    Cell orderNumber = row.getCell(1);
                    if (null != orderNumber) {
                        orderNumber.setCellType(CellType.STRING);
                        orderDelivery.setOrderNumber(orderNumber.getStringCellValue());
                    }
                    Cell deliveryName = row.getCell(2);
                    if (null != deliveryName) {
                        deliveryName.setCellType(CellType.STRING);
                        orderDelivery.setDeliveryName(deliveryName.getStringCellValue());
                    }
            }
        } catch (IOException e) {
            log.error("excel文件读取异常："+ e);
            e.printStackTrace();
            return ResResult.fail("导入异常，请咨询管理员！");
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                log.error("excel文件流关闭异常："+ e);
                e.printStackTrace();
                return ResResult.fail("导入异常，请咨询管理员！");
            }
        }
}

XSSFWorkbook

读取xlsx文件，高于2003版本的excel，读写均与HSSFWorkbook保持一致

或者直接使用开源组件Excel-Boot

GitHub - programmeres/excel-boot: Easy-POI是一款Excel导入导出解决方案组成的轻量级开源组件。

兼容

Workbook workbook=null;
if (is2003Excel){
    workbook= new HSSFWorkbook(inputStream);
}else {
    workbook = new XSSFWorkbook(inputStream);
}