Java解析上传Excel文件内容，亦可校验模板正确性

• 引入所需jar包

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

• 创建工具类

package com.vd.canary.b2b.edge.operation.util.inquiry;

import com.vd.canary.b2b.edge.operation.util.inquiry.TlerpExcelReader.ExcelFileType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * Excel工具类
 * @author huangjinghua
 *
 */
public class TlerpExcelUtil {

    /**
     * 导入Excel指定sheet到List
     *
     * @param excelInputStream
     * @param fileType
     * @param sheetName
     * @param startRowno
     *            读取开始行号
     * @return
     * @throws IOException
     */
    public static List<HashMap<String, Object>> getDataFromExcel(InputStream excelInputStream, ExcelFileType fileType,
            String sheetName, int startRowno) throws IOException {
        List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        TlerpExcelReader reader = new TlerpExcelReader(excelInputStream, fileType);
        Workbook workbook = reader.getWorkBook();
        Sheet sheet = workbook.getSheet(sheetName);
        // 读取首行获取列名
        Row firstRow = sheet.getRow(startRowno);
        String[] cellName = new String[firstRow.getLastCellNum()];
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            Cell cell = firstRow.getCell(i);
            if (cell == null || getCellValue(cell) == null || !StringUtils.hasText(getCellValue(cell).toString())) {
                break;
            }
            String value = getCellValue(cell).toString();
            value = value.replaceAll("\t|\r|\n", "");
            cellName[i] = value;
        }
        // 循环读取sheet
        for (int rowno = startRowno + 1; rowno <= sheet.getLastRowNum(); rowno++) {
            Row row = sheet.getRow(rowno);
            if (row == null) {
                continue;
            }
            boolean emptyFlag = true;
            // 循环读取row的每一列,比第一行多出的部分不读取
            HashMap<String, Object> rowMap = new HashMap<String, Object>();
            for (int cellno = 0; cellno < firstRow.getLastCellNum(); cellno++) {
                Cell cell = row.getCell(cellno);
                Object value = getCellValueFormula(cell, reader.getFormulaEvaluator());
                if (value != null && !"".equals(value)) {
                    emptyFlag = false;
                }
                rowMap.put(cellName[cellno], value);
            }
            if (!emptyFlag) {
                list.add(rowMap);
            }
        }
        return list;

    }

    /**
     * 导入Excel指定sheet到List
     *
     * @param excelInputStream
     * @param fileType
     * @param sheetNo
     * @param startRowno
     * @return
     * @throws IOException
     */
    public static List<HashMap<String, Object>> getDataFromExcelBySheetNo(InputStream excelInputStream,
            ExcelFileType fileType, int sheetNo, int startRowno) throws IOException {
        List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        TlerpExcelReader reader = new TlerpExcelReader(excelInputStream, fileType);
        Workbook workbook = reader.getWorkBook();
        Sheet sheet = workbook.getSheetAt(sheetNo);
        // 读取首行获取列名
        Row firstRow = sheet.getRow(startRowno);
        String[] cellName = new String[firstRow.getLastCellNum()];
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            Cell cell = firstRow.getCell(i);
            if (cell == null || getCellValue(cell) == null || !StringUtils.hasText(getCellValue(cell).toString())) {
                break;
            }
            String value = getCellValue(cell).toString();
            value = value.replaceAll("\t|\r|\n", "");
            cellName[i] = value;
        }
        // 循环读取sheet
        for (int rowno = startRowno + 1; rowno <= sheet.getLastRowNum(); rowno++) {
            Row row = sheet.getRow(rowno);
            if (row == null) {
                continue;
            }

            boolean emptyFlag = true;
            // 循环读取row的每一列,比第一行多出的部分不读取
            LinkedHashMap<String, Object> rowMap = new LinkedHashMap<String, Object>();
            for (int cellno = 0; cellno < firstRow.getLastCellNum(); cellno++) {
                Cell cell = row.getCell(cellno);
                Object value = getCellValueFormula(cell, reader.getFormulaEvaluator());
                if (value != null && !"".equals(value)) {
                    emptyFlag = false;
                }
                rowMap.put(cellName[cellno], value);
            }
            if (!emptyFlag) {
                list.add(rowMap);
            }
        }
        return list;

    }

    /**
     * 根据list创建workbook
     *
     * @param workbook
     *            如需创建新文件，设为null
     * @param list
     * @param cellName
     *            列名数组
     * @param sheetName
     * @param fileType
     * @return
     */
    public static Workbook exportToWorkBook(Workbook workbook, List<HashMap<String, Object>> list, String[] cellName,
                                            String sheetName, ExcelFileType fileType) {
        if (workbook == null) {
            if (ExcelFileType.XLS == fileType) {
                workbook = new HSSFWorkbook();
            }
            else if (ExcelFileType.XLSX == fileType) {
                workbook = new XSSFWorkbook();
            }
            else {
                workbook = new HSSFWorkbook();
            }
        }
        Sheet sheet = workbook.createSheet(sheetName);
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        // 创建表头
        Row row = sheet.createRow(0);
        for (int i = 0; i < cellName.length; i++) {
            String name = cellName[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(name);
        }
        // 循环插入行
        int currentRowNum = 1;
        for (HashMap<String, Object> map : list) {
            Row currentRow = sheet.createRow(currentRowNum);
            for (int i = 0; i < cellName.length; i++) {
                String name = cellName[i];
                Cell cell = currentRow.createCell(i);
                setCellValue(cell, map.get(name), workbook, cellStyle, font);
            }
            currentRowNum++;
        }
        return workbook;

    }

    /**
     * 导出带错误信息的模板文件
     *
     * @param list
     *            带错误信息的数据集合
     * @param excelInputStream
     *            原始模板文件输入流
     * @param fileType
     * @param sheetName
     * @param startRowno
     *            读取开始行号
     * @return
     * @throws IOException
     */
    public static Workbook exportExcelWithCheckInfo(List<HashMap<String, Object>> list, InputStream excelInputStream,
                                                    ExcelFileType fileType, String sheetName, int startRowno) throws IOException {
        TlerpExcelReader reader;
        Workbook workbook = null;
        reader = new TlerpExcelReader(excelInputStream, fileType);
        workbook = reader.getWorkBook();
        Sheet sheet = workbook.getSheet(sheetName);
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        // 读取首行获取列名
        Row firstRow = sheet.getRow(startRowno);
        String[] cellName = new String[firstRow.getLastCellNum()];
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            Cell cell = firstRow.getCell(i);
            // 读到空行则认为列头完结
            if (cell == null || getCellValue(cell) == null || !StringUtils.hasText(getCellValue(cell).toString())) {
                break;
            }
            String value = getCellValue(cell).toString();
            value = value.replaceAll("\t|\r|\n", "");
            cellName[i] = value;
        }
        // 循环插入行
        int currentRowNum = startRowno + 1;
        for (HashMap<String, Object> map : list) {
            Row currentRow = sheet.createRow(currentRowNum);
            for (int i = 0; i < cellName.length; i++) {
                String name = cellName[i];
                Cell cell = currentRow.createCell(i);
                setCellValue(cell, map.get(name), workbook, cellStyle, font);
            }
            currentRowNum++;
        }
        return workbook;
    }

    /**
     * 导出带错误信息的模板文件(物流)
     *
     * @param list
     *            带错误信息的数据集合
     * @param excelInputStream
     *            原始模板文件输入流
     * @param fileType
     * @param sheetName
     * @param startRowno
     *            读取开始行号
     * @return
     */
    public static Workbook exportWLExcelWithCheckInfo(Workbook workbook, List<HashMap<String, Object>> list,
                                                      InputStream excelInputStream, ExcelFileType fileType, String sheetName, int startRowno) throws IOException {
        TlerpExcelReader reader;
        if (workbook == null) {
            reader = new TlerpExcelReader(excelInputStream, fileType);
            workbook = reader.getWorkBook();
        }

        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
        }
        // 读取首行获取列名
        Row firstRow = sheet.getRow(startRowno);
        String[] cellName = new String[firstRow.getLastCellNum()];
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            Cell cell = firstRow.getCell(i);
            // 读到空行则认为列头完结
            if (cell == null || getCellValue(cell) == null || !StringUtils.hasText(getCellValue(cell).toString())) {
                break;
            }
            String value = getCellValue(cell).toString();
            value = value.replaceAll("\t|\r|\n", "");
            cellName[i] = value;
        }
        // 循环插入行
        int currentRowNum = startRowno + 1;
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        for (HashMap<String, Object> map : list) {
            Row currentRow = sheet.createRow(currentRowNum);
            for (int i = 0; i < cellName.length; i++) {
                String name = cellName[i];
                Cell cell = currentRow.createCell(i);
                setCellValue(cell, map.get(name), workbook, cellStyle, font);
            }
            currentRowNum++;
        }
        return workbook;
    }

    public static void setCellValue(Cell cell, Object value, Workbook wb, CellStyle cellStyle, Font cellFont) {
        CreationHelper createHelper = wb.getCreationHelper();
        if (value instanceof String) {
            if (((String) value).startsWith("HYPERLINK") || ((String) value).startsWith("=HYPERLINK")) {
                cellFont.setUnderline((byte) 1);
                cellFont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
                cellStyle.setFont(cellFont);
                cell.setCellStyle(cellStyle);
                cell.setCellFormula((String) value);
            }
            cell.setCellValue((String) value);
        }
        else if (value instanceof Date) {
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY/MM/DD hh:mm:ss"));
            cell.setCellValue((Date) value);
            cell.setCellStyle(cellStyle);
        }
        else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        }
        else if (value instanceof Integer) {
            String str = String.valueOf(value);
            cell.setCellValue(Double.parseDouble(str));
        }
        else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        }
    }

    public static Object getCellValue(Cell cell) {
        if (CellType.STRING == cell.getCellType()) {
            return cell.getRichStringCellValue().getString().trim();
        }
        if(CellType.NUMERIC == cell.getCellType()) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                cell.setCellType(CellType.STRING);
                return new BigDecimal(cell.getStringCellValue());
            }
        }
        if(CellType.BOOLEAN == cell.getCellType())
            return cell.getBooleanCellValue();
        if(CellType.BLANK == cell.getCellType())
            return "";
        if(CellType.ERROR == cell.getCellType())
            return cell.getErrorCellValue();
        return null;
    }

    public static Object getCellValueFormula(Cell cell, FormulaEvaluator formulaEvaluator) {
        if (cell == null || formulaEvaluator == null) {
            return null;
        }
        if (cell.getCellType() == CellType.FORMULA) {
            if (cell.getCellType() == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    cell.setCellType(CellType.STRING);
                    return new BigDecimal(cell.getStringCellValue());
                }
            }
            if (cell.getCellType() == CellType.STRING)
                return cell.getRichStringCellValue().getString().trim();
            if (cell.getCellType() == CellType.BOOLEAN)
                return cell.getBooleanCellValue();
            if (cell.getCellType() == CellType.BLANK)
                return "";
            if (cell.getCellType() == CellType.ERROR)
                return cell.getErrorCellValue();
        }
        return getCellValue(cell);
    }

}

package com.vd.canary.b2b.edge.operation.util.inquiry;

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

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

public class TlerpExcelReader {

    private Workbook workBook;

    private FormulaEvaluator formulaEvaluator;

    public TlerpExcelReader(final InputStream excelInputStream, final ExcelFileType fileType) throws IOException {
        if (ExcelFileType.XLS == fileType) {
            workBook = new HSSFWorkbook(excelInputStream);
            formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();
        }
        else if (ExcelFileType.XLSX == fileType) {
            workBook = new XSSFWorkbook(excelInputStream);
            formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();
        }
        else {
            throw new IllegalArgumentException("错误的文件类型");
        }
    }

    public enum ExcelFileType {
        /**
         * 97-2003
         */
        XLS,
        /**
         * 2007-201x
         */
        XLSX
    }

    public Workbook getWorkBook() {
        return this.workBook;
    }

    public FormulaEvaluator getFormulaEvaluator() {
        return this.formulaEvaluator;
    }
}

• postman接口测试,成功读取数据

    /**
     * 测试导入excel解析
     * @param file
     * @return
     */
    @PostMapping("/importExcel")
    @ResponseBody
     public List<HashMap<String, Object>> importExcel(MultipartFile file){
        List<HashMap<String, Object>> xlsx = null;
        try {
           return TlerpExcelUtil.getDataFromExcelBySheetNo(file.getInputStream(), TlerpExcelReader.ExcelFileType.XLSX, 0, 0);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return xlsx;
    }