java 上传 Excel

最新推荐文章于 2025-05-07 15:19:12 发布

c香蕉你个不呐呐

最新推荐文章于 2025-05-07 15:19:12 发布

阅读量397

点赞数

CC 4.0 BY-SA版权

文章标签： excel java 上传文件

本文链接：https://blog.csdn.net/Caozuqing/article/details/78961594

本文介绍了一个使用Java处理Excel文件并解析数据到后台系统的具体案例。该案例通过读取特定格式的Excel表格，将员工相关信息转换为后台可以处理的数据格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

后台Controller:

 @SuppressWarnings("unchecked")
    @ApiOperation(value = "上传excel模板", notes = "上传模板", response = ResultData.class)
    @RequestMapping(value = "/uploadEmpExcel", method = RequestMethod.POST)
    public ResultData uploadEmpExcel(HttpServletRequest request, HttpServletResponse response, @RequestParam MultipartFile file)  throws Exception {
        //每条数据
        List<Map<String, Object>> insertMapList = new ArrayList<>();

        ResultData data = null;

        InputStream in = file.getInputStream();

        //读取文件输入流
        Workbook wk = null;

        wk = PoiUtil.getWorkbook(in);

        Sheet sheet = wk.getSheetAt(0);

        //获得excel列头信息
        Row rowTitle = sheet.getRow(3);

        //获得列的数量
        int columnNum = rowTitle.getLastCellNum();

        String[] columnName = {"empName", "certificateNo", "company", "customer",
                "pHouston",
                "pBenefits", "pOldIndividual", "pMedicalIndividual",
                "pUnemployedIndividual","pIndividualHousing",
                "pPensionEn","pMedicalEn","pInjuryJob","pBirth","pUnemployedEn","pHousingEn",
                "pAccident","pManagement","pDifference","pTax","pTaxIndividual","pOthers",
                "xHouston","xWages","xWarrantyGold","xManagement","xDifference","xTax","xTaxIndividual",

            "wHouston","wBenefits","wOldIndividual","wMedicalIndividual","wUnemployedIndividual","wIndividualHousing","wAccident",
                    "wPensionEn","wMedicalEn","wInjuryJob","wBirth","wUnemployedEn","wHousingEn","wManagement",
            "wDifference","wTax","wTaxIndividual","wLateFee",

                    "sHouston","sOldIndividual","sMedicalIndividual","sUnemployedIndividual",
                    "sIndividualHousing","sAccident","sPensionEn","sMedicalEn","sInjuryJob","sBirth","sUnemployedEn","sHousingEn","sOthers",
                    "sDifference","sManagement","sTax",
            "zDispatchIncome","zProjectIncome","zOutsourceIncome","zSocialIncome","yIndividualTax"
        };

        //循环获得excel内容信息
        for (int j = 4; j < sheet.getLastRowNum()+1; j++) {
            Row rowContent = sheet.getRow(j);
            Map<String,Object> insertMap = new HashMap<>();
            for (int k = 1; k < columnNum; k++) {
                Cell cell = rowContent.getCell(k);
                String value = PoiUtil.getStringCellValue(cell);
                if("".equals(value) && k!=3 && k!=2 && k!=1 && k!=4){
                    value = "0";
                }
                insertMap.put(columnName[k-1], value);
            }
            insertMapList.add(insertMap);
        }


        data = new ResultData(insertMapList);

        return data;
    }

PoiUtil-getStringCellValue方法:

    @SuppressWarnings("unused")
    public static String getStringCellValue(Cell cell) {

        if (null == cell) {
            return "";
        }

        String strCell = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                try{
                    strCell=String.valueOf(cell.getNumericCellValue());
                }catch (IllegalStateException e){
                    strCell=String.valueOf(cell.getRichStringCellValue());
                }

                break;
            case Cell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }