java实现解析Excel并以List方式返回前端

controller代码

/**
 * 
 *
 * @author Khh_Coder
 */
@RestController
@Slf4j
@RequestMapping("/test")
public class Controller {
    @Autowired
    private ExcelFileUploadService excelFileUploadService;


    @PostMapping("/uploadExcelFile")
    public RestResponse uploadExcelFile(@RequestParam("file") MultipartFile multipartFile) throws BusinessException {
        RestResponse result = new RestResponse(200, "读取成功");
        result.setContent(excelFileUploadService.importPeopleaccountdetail(multipartFile));
        return result;
    }
}

service代码

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


import org.apache.poi.hssf.usermodel.HSSFCell;
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.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;


@Service
public class ExcelFileUploadService {
    public List<Object> importPeopleaccountdetail(MultipartFile file) throws Exception {

        List<Object> list = new ArrayList<>();
        Workbook workbook =null;

        //读取文件内容
        try {
            String fileName=file.getOriginalFilename();
            if (fileName.endsWith("xls")){
                workbook = new HSSFWorkbook(file.getInputStream());
            }else if (fileName.endsWith("xlsx")){
                workbook=new XSSFWorkbook(file.getInputStream());
            }

        } catch (IOException e) {
            throw new Exception("读取文件内容失败");
        }

        //获取第一个工作表
        Sheet sheet =workbook.getSheetAt(0);

        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        try {
            //循环插入数据
            for(int i=firstRowNum+1;i<=lastRowNum;i++){
                //People people= new People ();

                //获取第一行
                Row row = sheet.getRow(i);

                //获取第一列
                Cell name = row.getCell(0);
                String cellValue=null;
                if(name !=null){
                    cellValue=getCellStringValue(name);
                    //people.setName(cellValue);
                }

                //获取第二列
                Cell money = row.getCell(1);
                if(money !=null){
                    cellValue=getCellStringValue(money );
                    //people.setMoney(new BigDecimal(cellValue));
                }
                //list.add(people);
            }
        } catch (Exception e) {
            throw new Exception("读取Excel文件失败");
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new Exception("服务器繁忙，请稍后再试");
            }
        }
        return list;
    }


    private String getCellStringValue(Cell cell){
        String cellValue=null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                cellValue=cell.getNumericCellValue()+"";
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


}

这里解析的Excel一共两列，第一列为名称，第二列为钱数

依赖jar包

       
         <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        

有什么疑问可以留言哦