java处理excel文件

文章目录

• maven引入依赖
• 编写ExcelUtil工具类
• 使用ExcelUtil工具类

maven引入依赖

<!--处理excel依赖-->
<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>

编写ExcelUtil工具类

package com.rc.rc_exam.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    public static List<Map<String, String>> readExcel(String path){
        try {
            FileInputStream inputStream = new FileInputStream(path);
            Workbook workbook = null;
            if (path.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            return getExcelResultList(workbook);
        }catch (Exception e){
            log.error(e.getMessage());
        }
        return null;
    }

    public static List<Map<String, String>> readExcel(MultipartFile file){
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            return getExcelResultList(workbook);
        }catch (Exception e){
            log.error(e.getMessage());
        }
        return null;
    }

    private static List<Map<String, String>> getExcelResultList(Workbook workbook) {
        List<Map<String,String>> resultList = new ArrayList<>();
        //获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行数据
        Row row = sheet.getRow(0);
        //获取有效单元格数量
        int colNum = row.getPhysicalNumberOfCells();
        Map<Integer,String> headLineMap = new HashMap<>();
        //处理第一行数据
        for (int i = 0; i < colNum; i++) {
            Cell cell = row.getCell(i);
            //每一列第一行的标题
            String stringCellValue = cell.getStringCellValue();
            headLineMap.put(i,stringCellValue);
        }
        Map<String,String> resultMap = null;
        //处理第二行之后的数据
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rowNum; i++) {
            resultMap = new HashMap<>();
            Row row1 = sheet.getRow(i);
            int physicalNumberOfCells1 = row1.getPhysicalNumberOfCells();
            for (int i1 = 0; i1 < physicalNumberOfCells1; i1++) {
                Cell cell = row1.getCell(i1);
                String cellValue = null;
                if (cell!=null){
                    cellValue = handleCellValue(cell, workbook);
                }
                resultMap.put(headLineMap.get(i1),cellValue);
            }
            resultList.add(resultMap);
        }
        return resultList;
    }

    private static String handleCellValue(Cell cell, Workbook workbook) {
        String result = "";
        int cellType = cell.getCellType();
        switch (cellType){
            case XSSFCell.CELL_TYPE_STRING:
                result = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                //获得计算公式
                String cellFormula = cell.getCellFormula();
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                result = evaluate.formatAsString();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                    result = String.valueOf(cell.getDateCellValue());
                }else {
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    result = cell.getStringCellValue();
                }
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                result = "";
                break;
        }
        return result;
    }

    public static void main(String[] args) {
        String path = "D:\\01wangcheng\\excelExam\\N1题库.xls";
        List<Map<String, String>> resultList = readExcel(path);
        System.out.println(resultList);
    }
}

使用ExcelUtil工具类

 /**
     * 上传试题excel
     * @param file
     * @return
     */
    @PostMapping("uploadExamExcel")
    @ApiOperation("上传试题excel")
    public Result uploadExamExcel(@RequestParam("file") MultipartFile file){
        String result = rcExamService.createRcExamDataByExcel(file);
        return Result.ok(result);
    }