读excel文件上传到数据表_new excelexception-优快云博客

本文链接：https://blog.youkuaiyun.com/fuyou0104/article/details/6452768

本文详细介绍了如何通过批量上传Excel文件实现活动计划的导入，包括文件上传、数据验证、错误处理及上传流程。

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

    public String batchUploadCampaign() throws Exception
    {
        HttpServletRequest request = ServletActionContext.getRequest();
        User user = (User)request.getSession().getAttribute("user_object");
        Role userRole =
            (Role) ServletActionContext.getRequest().getSession().getAttribute(
                WebConstants.USER_ROLE);

        String fileName = "";
        for (int i = 0; i < file.length; i++)
        {
            File _file = file[i];
            fileName = _file.getName();
            try
            {
                FileInputStream fileIn = new FileInputStream(_file);
                FileUtil.saveFile(_file, fileName, uploadPath);
                break;
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }

        Workbook wwb = null;
        try
        {
            wwb = Workbook.getWorkbook(new File(uploadPath + fileName));
            Sheet sheet = wwb.getSheet(0);
            try
            {
                ClaimUploadResult result =
                    campaignAndPlancodeBatchUploadFacade.batchUploadCampaign(sheet, user.getSsoId(),userRole.getName());
                request.setAttribute("claimUploadResult", result);
            }
            catch (Exception ex)
            {
                request.setAttribute("errorMessage", ex.getMessage());
            }
        }
        catch (Exception e)
        {
            request.setAttribute("errorMessage", "incorrect excel file");
        }
        return SUCCESS;
    }

    public ClaimUploadResult batchUploadCampaign(Sheet sheet, String userName, String roleName) throws Exception
    {
        ResourceBundle rb = ResourceBundle.getBundle("resources/claim-config");
        int expectedColumns = Integer.parseInt(rb.getString("nb_cicap_campaign_info.column.count"));
        int rows = sheet.getRows();
        int columns = sheet.getColumns();
        if (rows == 0)
        {
            throw new Exception("Excel文件为空，请检查！");
        }
        else if (rows == 1)
        {
            throw new Exception("Excel文件不包含有效数据，请检查！");
        }
        else if (columns != expectedColumns)
        {
            throw new Exception("Excel文件列数错误，请检查！");
        }
        for (int i = 0; i < sheet.getColumns(); i++)
        {
            String expectedColumn = rb.getString("nb_cicap_campaign_info.column." + (i + 1));
            String column = sheet.getCell(i, 0).getContents();
            if (!column.equals(expectedColumn))
            {
                throw new Exception("第{" + (i+1) + "}列期望的标题为{" + expectedColumn + "}，而实际为{" + column + "}" );
            }
        }
        List list = new ArrayList();
        for (int j = 1; j < sheet.getRows(); j++)
        {
            NBCicapCampaignInfo info = new NBCicapCampaignInfo();
            info.setProgramCode(sheet.getCell(0, j).getContents());
            info.setCampaignCode(sheet.getCell(1, j).getContents());
            info.setSponsorCampaignCode(sheet.getCell(2, j).getContents());
            info.setPackageCode(sheet.getCell(3, j).getContents());
            info.setPoPrefix(sheet.getCell(4, j).getContents());
            info.setSponsorCode(sheet.getCell(5, j).getContents());
            info.setSplitDesc(sheet.getCell(6, j).getContents());
            info.setCity(sheet.getCell(7, j).getContents());
            info.setCampaignStatus(sheet.getCell(8, j).getContents());
            info.setCampaignStartDate(sheet.getCell(9, j).getContents());
            info.setCampaignEndDate(sheet.getCell(10, j).getContents());
            info.setCampaignMeasureDay(sheet.getCell(11, j).getContents());
            info.setVersion(0);
            info.setCreateUsr(userName);
            info.setCreateUsrRole(roleName);

            list.add(info);
        }

        ClaimUploadResult result = null;
        try{
            result = campaignAndPlancodeBatchUploadDAO.batchUploadCampaign(list);
        }
        catch(Exception e){
            throw e;
        }

        //result.setBatchNumber(batchNumber);
        return result;
    }