Ejemplo n.º 1
0
  @Override
  public String insertFinanceReport(HttpServletRequest request, HttpServletResponse response)
      throws Exception {
    // 获得上传的附件
    InputStream is = null;
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    MultipartFile multipartFile = multipartRequest.getFile("tableImportTemplate");
    if (null == multipartFile) {
      throw new BusinessException("没有上传财务报表");
    }
    // 上传的参数据
    Map<String, String> model = QueryUtil.getRequestParameterMapByAjax(request);
    // 列名对象解析
    String financeparams = model.get("parames");
    if (null == financeparams
        || "".equals(financeparams)
        || "null".equalsIgnoreCase(financeparams.trim())) {
      throw new BusinessException("没有对Excel的配置");
    }
    try {
      BaseFile bf = templateService.saveUpFiletoService(multipartFile, model);
      String custid = "";
      CustInfo custinfo = null;
      if (model.containsKey("custid")) {
        custid = model.get("custid");
        custinfo = this.tableService.findEntityByID(CustInfo.class, custid);
        if (null == custinfo) {
          throw new BusinessException("给定的客户的ID没有找到客户");
        }
      } else {
        throw new BusinessException("没有给定客户的ID");
      }

      Workbook wb = null;
      is = multipartFile.getInputStream();
      String importFileName = multipartFile.getOriginalFilename().toLowerCase();
      if (importFileName.endsWith("xlsx")) {
        wb = PoiExcelUtil.readWorkbook(is, ExcelVersionEnum.VERSION2007);
      } else {
        wb = PoiExcelUtil.readWorkbook(is, ExcelVersionEnum.VERSION2003);
      }
      List<FinanceReportParamBean> inintParam = this.getImportParams(financeparams);
      if (inintParam.size() > 0) {
        for (FinanceReportParamBean param : inintParam) {
          Map<String, FinanceReportBean> Mapfield =
              this.getClassFieldExcel(param.getSheeName(), param.getTargetClass());
          if (Mapfield.keySet().size() > 0) {
            try {
              Sheet sheet = wb.getSheet(param.getSheeName());
              if (null != sheet) {
                List finance =
                    (List)
                        this.getFinanceReprotEntitysByConfig(sheet, Mapfield, param, bf, custinfo);
                // 保证实体
                if (null != finance) {
                  this.tableService.saveOrUpdateAllEntities(finance);
                }
              } else {
                throw new BusinessException("上传的EXCEL中没有" + param.getSheeName());
              }
            } catch (Exception e) {
              // TODO Auto-generated catch block
              throw new BusinessException(
                  "上传的EXCEL中没有" + param.getSheeName() + ":" + e.getMessage());
            }

          } else {
            throw new BusinessException("excel中" + param.getSheeName() + "没有配置相应的类");
          }
        }
      } else {
        throw new BusinessException("没有对Excel的配置");
      }
    } catch (BusinessException b) {
      throw new BusinessException(b.getMessage());
    } catch (Exception e) {
      e.printStackTrace();
      // TODO Auto-generated catch block
      throw new BusinessException(e.getMessage());
    }
    return "成功";
  }
Ejemplo n.º 2
0
  /** 将 excel中的sheet解析成对应的实体 */
  @Override
  public <T> Collection<T> getFinanceReprotEntitysByConfig(
      Sheet sheet,
      Map<String, FinanceReportBean> financebean,
      FinanceReportParamBean financeParam,
      BaseFile bf,
      CustInfo ci)
      throws Exception {
    // TODO Auto-generated method stub
    String targetClass = financeParam.getTargetClass();
    Class<?> targetObject = Class.forName(targetClass);
    String sheaderCell = financeParam.getHeaderCell();
    String sdataCell = financeParam.getDataCell();
    String vheaderCell[] = sheaderCell.split(",");
    String vdataCell[] = sdataCell.split(",");
    String headerCell = "";
    String dataCell = "";
    int headerCellRowIndex = 0;
    int headerCellCellIndex = 0;
    int dataCellRowIndex = 0;
    int dataCellCellIndex = 0;
    int sheetRowNum = sheet.getLastRowNum();
    int datalength = 0;
    String cellvalue = "";
    String headerValue = "";
    String field = "";
    Boolean flag = true;
    Boolean empty = false;
    Map<String, JSONObject> FinanceEntiies = new HashMap<String, JSONObject>();
    List finance = new ArrayList();
    String year = "";
    try {
      for (int i = 0; i < vheaderCell.length; i++) {
        datalength = 0;
        headerCellRowIndex = 0;
        headerCellCellIndex = 0;
        dataCellRowIndex = 0;
        dataCellCellIndex = 0;
        headerCell = vheaderCell[i];
        dataCell = vdataCell[i];
        ExeclBean headerInfo = PoiExcelUtil.getExcelBean(headerCell);
        ExeclBean dataInfo = PoiExcelUtil.getExcelBean(dataCell);
        headerCellRowIndex = headerInfo.getRow_num() + 1;
        headerCellCellIndex = headerInfo.getCol_num();
        dataCellRowIndex = dataInfo.getRow_num();
        dataCellCellIndex = dataInfo.getCol_num();
        flag = true;

        if (headerCellRowIndex > sheetRowNum || dataCellRowIndex > sheetRowNum) {
          throw new BusinessException(
              "解析excel出错:配置的标题cell("
                  + headerCellRowIndex
                  + ")或数据cell("
                  + dataCellRowIndex
                  + ")大于实际 sheet("
                  + sheet.getSheetName()
                  + ")的row长度");
        } else {
          Row datarow = sheet.getRow(dataCellRowIndex);
          datalength = 0;
          // 获得数据列的长度
          for (int j = dataCellCellIndex; j < datarow.getLastCellNum(); j++) {
            Cell datacell = datarow.getCell(j);
            cellvalue = PoiExcelUtil.getCellValue(datacell);
            if (flag == true) {
              if (null != cellvalue && (!"".equals(cellvalue))) {
                datalength = datalength + 1;
              } else {
                flag = false;
              }
            }
          }
          List<JSONObject> entity = new ArrayList<JSONObject>();
          for (int j = 0; j < datalength; j++) {
            cellvalue = "";
            empty = true;
            JSONObject financeEntityJson = new JSONObject();
            datarow = sheet.getRow(dataCellRowIndex);
            Cell cell = datarow.getCell(dataCellCellIndex + j);
            year = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
            // 隐藏列则不写入数据库
            if (!sheet.isColumnHidden(dataCellCellIndex + j)) {
              if (!"".equals(year.trim())) {
                for (int h = dataCellRowIndex; h < sheetRowNum; h++) {
                  datarow = sheet.getRow(h);
                  if (null != datarow) {
                    if (headerCellCellIndex < datarow.getLastCellNum()) {
                      if (null != datarow.getCell(headerCellCellIndex)) {
                        headerValue =
                            PoiExcelUtil.getCellValue(datarow.getCell(headerCellCellIndex));
                      }
                      if (dataCellCellIndex + j < datarow.getLastCellNum()) {
                        if (null != datarow.getCell(dataCellCellIndex + j)) {
                          cell = datarow.getCell(dataCellCellIndex + j);
                          cellvalue =
                              PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
                          if (null != cellvalue && (!"".equals(cellvalue))) {
                            field = "";
                            flag = true;
                            if (h == dataCellRowIndex) {
                              flag = false;
                            }
                            field =
                                this.getExcelTableField(
                                    headerValue,
                                    (cell.getRowIndex() + 1) + "_" + i,
                                    financebean,
                                    flag);
                            System.out.println(
                                headerValue
                                    + ":"
                                    + ((cell.getRowIndex() + 1) + "_" + i)
                                    + ":"
                                    + cellvalue);
                            if (!"".equals(field)) {
                              financeEntityJson.put(field, cellvalue);
                              empty = false;
                            }
                          }
                        }
                      }
                    }
                  }
                }
                if (empty == false) {
                  // 根据年进行合并
                  if (FinanceEntiies.containsKey(year)) {
                    JSONObject temp = FinanceEntiies.get(year);
                    Iterator it = financeEntityJson.keys();
                    while (it.hasNext()) {
                      String key = (String) it.next();
                      temp.put(key, financeEntityJson.get(key).toString());
                    }
                    FinanceEntiies.put(year, temp);
                  } else {
                    FinanceEntiies.put(year, financeEntityJson);
                  }
                }
              }
            }
          }
        }
      }
      System.out.println(FinanceEntiies);
      if (!FinanceEntiies.isEmpty()) {
        Set<String> key = FinanceEntiies.keySet();
        for (Iterator it = key.iterator(); it.hasNext(); ) {
          year = (String) it.next();
          JSONObject jsonObj = FinanceEntiies.get(year);
          Object sourectObject = targetObject.newInstance();
          this.tableService.copyAndOverrideExistedValueFromJSONObject(jsonObj, sourectObject, null);
          try {
            BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "upLoadId")
                .getWriteMethod()
                .invoke(sourectObject, bf);
            BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "custId")
                .getWriteMethod()
                .invoke(sourectObject, ci);
          } catch (Exception e) {
            // TODO Auto-generated catch block
            throw new BusinessException(
                "在记录上传文件的ID和客户时出请检查实体类中有没有upLoadId和custId字段" + e.getMessage());
          }
          finance.add(sourectObject);
        }

        return finance;
      }

    } catch (BusinessException b) {
      b.printStackTrace();
      throw new BusinessException(b.getMessage());
    } catch (Exception e) {
      e.printStackTrace();
      // TODO Auto-generated catch block
      throw new BusinessException(e.getMessage());
    }
    return null;
  }