コード例 #1
0
 public static List<String[]> readExcel(Context ctx, String fileName, Object targetSheet) {
   List<String[]> rowList = new ArrayList<String[]>();
   try {
     InputStream mInputStream = ctx.getResources().getAssets().open(fileName);
     Workbook wb = Workbook.getWorkbook(mInputStream);
     Sheet mSheet = null;
     if (targetSheet instanceof Integer) {
       mSheet = wb.getSheet((Integer) targetSheet);
     } else {
       mSheet = wb.getSheet((String) targetSheet);
     }
     int row = getRowCount(mSheet);
     int columns = getColCount(mSheet);
     Log.d("W", "Total Row: " + row + ", Total Columns: " + columns);
     String[] colArray = new String[columns];
     for (int i = 1; i < row; i++) {
       for (int j = 0; j < columns; j++) {
         Cell temp = mSheet.getCell(j, i);
         String content = temp.getContents();
         Log.d("W", j + " ," + i + " ," + content);
         colArray[j] = content;
       }
       rowList.add(colArray);
     }
     wb.close();
     mInputStream.close();
   } catch (BiffException e) {
     e.printStackTrace();
   } catch (IndexOutOfBoundsException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }
   return rowList;
 }
コード例 #2
0
  public ArrayList<String> getCitiesFromExcel() {

    ArrayList<String> cities = new ArrayList<String>();
    try {
      Workbook workbook =
          Workbook.getWorkbook(this.getClass().getResourceAsStream("/resource/cities.xls"));
      int totalsheet = workbook.getNumberOfSheets();
      for (int i = 0; i < totalsheet; i++) {
        Sheet sheet = workbook.getSheet(i);
        int rows = sheet.getRows();

        for (int j = 1; j < rows; j++) {
          Cell cell1 = sheet.getCell(0, j);
          cities.add(cell1.getContents());
        }
      }
      return cities;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    } catch (BiffException e) {
      e.printStackTrace();
      return null;
    }
  }
コード例 #3
0
  public Map<String, Semantic> loadAxiom() {
    Map<String, Semantic> axiom = new HashMap<String, Semantic>();
    Workbook readwb = null;

    try {
      readwb = Workbook.getWorkbook(new File("src/cn/edu/buaa/resources/Axiom.xls"));
      Sheet readsheet = readwb.getSheet(0);
      int rsRows = readsheet.getRows();
      int i = 1;
      while (i < rsRows) {
        String name = readsheet.getCell(0, i).getContents().trim();
        if (null == name || name.equals("")) continue;
        List<Item> semanSet = new ArrayList<Item>();
        Semantic seman = new Semantic(semanSet);
        while (i < rsRows) {
          String tmp = readsheet.getCell(0, i).getContents().trim();
          if (null == tmp || tmp.equals("") || tmp.equals(name)) {
            String premise = readsheet.getCell(1, i).getContents().trim();
            String left = readsheet.getCell(2, i).getContents().trim();
            String right = readsheet.getCell(3, i).getContents().trim();

            if (null == premise || premise.equals("")) premise = null;
            if (null == left || left.equals("")) left = null;
            if (null == right || right.equals("")) right = null;

            if (null != premise || null != left || null != right) {
              Item item = new Item(premise, left, right);
              semanSet.add(item);
            }
            i++;
          } else {
            break;
          }
        }
        axiom.put(name, seman);
        name = null;
      }
    } catch (BiffException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (null != readwb) {
        readwb.close();
      }
    }

    // test axiom
    for (String name : axiom.keySet()) {
      System.out.println(name + ":");
      List<Item> items = axiom.get(name).getSemanSet();
      for (Item e : items) {
        System.out.println(e.getPremise() + " -> " + e.getLeft() + " = " + e.getRight());
      }
    }
    System.out.println("======================================");

    return axiom;
  }
コード例 #4
0
 public static WritableWorkbook getWorkBook(File file) {
   if (file == null) return null;
   Workbook workbookIn;
   WritableWorkbook workbook = null;
   try {
     workbookIn = Workbook.getWorkbook(file);
     workbook = Workbook.createWorkbook(file, workbookIn);
   } catch (IOException e) {
     e.printStackTrace();
   } catch (BiffException e) {
     e.printStackTrace();
   }
   return workbook;
 }
コード例 #5
0
 @Test
 public void testGetCollection() {
   ExcelUtil excelUtil = new ExcelUtil();
   try {
     excelUtil.openUploadExcel("collectionTemplate.xls", 0);
   } catch (BiffException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   System.out.println(excelUtil.getCollection());
   excelUtil.closeExcel();
 }
コード例 #6
0
 @Override
 public List<? extends Persistable> getAll() {
   try {
     List<ArrayList<String>> result;
     for (List<String> list : (result = xclAdaptee.read(file))) {
       // MappingStrategy<? extends Persistable> ms = TODO
       processLines();
     }
   } catch (BiffException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return null;
 }
コード例 #7
0
ファイル: ReadXLSTest.java プロジェクト: jaakan/waihu
  /** @param args */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
      Workbook book = Workbook.getWorkbook(new File("测试.xls"));
      Sheet sheet = book.getSheet(0);
      int x = sheet.getRows();
      int y = sheet.getColumns();
      System.out.println("表格的列数为:" + x);
      System.out.println("表格的行数为:" + y);

    } catch (BiffException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
コード例 #8
0
 @Override
 public void removeAll(List<? extends Persistable> objList) {
   try {
     xclAdaptee.write(file, new ArrayList<ArrayList<String>>());
   } catch (RowsExceededException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } catch (BiffException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } catch (WriteException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
 }
コード例 #9
0
ファイル: ExcelOper.java プロジェクト: glaftr/GLAF
 /**
  * 用于根据模板生成新的xls
  *
  * @param tplFile 模板文件路径
  * @param sFile 新的文件路径
  */
 public ExcelOper(File tplFile, File sFile) {
   try {
     // 取得模板文件
     workbook = Workbook.getWorkbook(tplFile);
     // 生成新文件
     wrWorkbook = Workbook.createWorkbook(sFile, workbook);
     readOnlyWBook = false;
   } catch (BiffException e) {
     // 写日志
     logger.error(e.getMessage());
     // 抛出异常
     throw new BaseException(e);
   } catch (IOException e) {
     // 写日志
     logger.error(e.getMessage());
     // 抛出异常
     throw new BaseException(e);
   }
 }
コード例 #10
0
  /**
   * 读取Excel文件的内容
   *
   * @param file 待读取的文件
   * @return
   */
  public static String readExcel(File file) {
    StringBuffer sb = new StringBuffer();

    Workbook wb = null;
    try {
      // 构造Workbook(工作薄)对象
      wb = Workbook.getWorkbook(file);
    } catch (BiffException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }

    if (wb == null) return null;

    // 获得了Workbook对象之后,就可以通过它得到Sheet(工作表)对象了
    Sheet[] sheet = wb.getSheets();

    if (sheet != null && sheet.length > 0) {
      // 对每个工作表进行循环
      for (int i = 0; i < sheet.length; i++) {
        // 得到当前工作表的行数
        int rowNum = sheet[i].getRows();
        for (int j = 0; j < rowNum; j++) {
          // 得到当前行的所有单元格
          Cell[] cells = sheet[i].getRow(j);
          if (cells != null && cells.length > 0) {
            // 对每个单元格进行循环
            for (int k = 0; k < cells.length; k++) {
              // 读取当前单元格的值
              String cellValue = cells[k].getContents();
              sb.append(cellValue + "\t");
            }
          }
          sb.append("\r\n");
        }
        sb.append("\r\n");
      }
    }
    // 最后关闭资源,释放内存
    wb.close();
    return sb.toString();
  }
コード例 #11
0
ファイル: ExcelOper.java プロジェクト: glaftr/GLAF
  /** 只用于读取xls数据 xls文件名做成参数 */
  public ExcelOper(File file) {
    try {
      // Workbook workbook = null;
      readOnlyWBook = true;
      // 设置workbook
      workbook = Workbook.getWorkbook(file);
    } catch (BiffException e) {
      logger.error(e.getMessage());
      throw new BaseException(e);
    } catch (IOException e) {
      logger.error(e.getMessage());
      throw new BaseException(e);
    }
    // 设置当前工作workbook
    if (workbook != null) {

      setWorkbook(workbook);
    }
  }
コード例 #12
0
  @Override
  public void addAll(List<? extends Persistable> objList) {
    ArrayList<ArrayList<String>> serializedList = new ArrayList<>();

    for (Persistable p : objList) {
      ArrayList<String> lineLs = p.serializeToStringArray();
      serializedList.add(lineLs);
    }
    try {
      xclAdaptee.write(file, serializedList);
    } catch (RowsExceededException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (BiffException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (WriteException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
コード例 #13
0
  private String test() {

    // 读取excel文件
    String realPath = "C:/Users/wchao/Desktop/上品214快捷日获奖名单.xls";
    Workbook book = null;

    WritableWorkbook book2 = null;
    WritableSheet sheet2 = null;
    Connection conn = null;
    conn =
        JDBCUtil.getConnection(
            JDBCUtil.getUrl("192.168.1.21", "1521", "danpin1", 0), "dev_user", "dev_user");
    try {
      try {
        book2 = Workbook.createWorkbook(new File("C:/Users/wchao/Desktop/上品214快捷日获奖名单111.xls"));
        sheet2 = book2.createSheet("第1页", 0);

      } catch (Exception e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }

      book = Workbook.getWorkbook(new File(realPath));

      Sheet sheet = book.getSheet(0);
      int count = sheet.getRows();
      System.out.println("--------------------------------------");
      for (int i = 1; i < count; i++) { // 最后一行是合计				
        Cell[] cells = sheet.getRow(i);
        String tradeNo = cells[2].getContents().trim();

        StringBuffer result = new StringBuffer();
        URL U = null;
        BufferedReader in = null;
        String sign = null;
        Map<String, String> sParaTemp = new HashMap();
        sParaTemp.put("out_trade_no", null);
        sParaTemp.put("trade_no", tradeNo);
        sParaTemp.put("service", "single_trade_query");
        sParaTemp.put("partner", "2088002692486430");
        sParaTemp.put("_input_charset", "utf-8");
        Map<String, String> sPara = AlipayCore.paraFilter(sParaTemp);
        // 生成签名结果
        sign = AlipayCore.buildMysign(sPara); // 752cc62e72503d01c4cc93b654906918

        try {
          String url =
              "https://www.alipay.com/cooperate/gateway.do?_input_charset=utf-8"
                  + "&sign="
                  + sign
                  + "&_input_charset=utf-8"
                  + "&sign_type=MD5"
                  + "&service=single_trade_query"
                  + "&partner=2088002692486430"
                  + "&trade_no="
                  + tradeNo;
          U = new URL(url);
          URLConnection connection = U.openConnection();
          connection.connect();
          in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
          String line;
          while ((line = in.readLine()) != null) {
            result.append(line);
            // System.out.println("-------------------\n" + new String(line.getBytes("gbk"),
            // "utf-8"));
          }

          StringReader sr =
              new StringReader(new String(result.toString().getBytes("gbk"), "utf-8"));
          BufferedReader br = new BufferedReader(sr);
          try {
            JAXBContext jaxbContext = JAXBContext.newInstance("net.shopin.alipay.entity");
            Unmarshaller unMarshaller = jaxbContext.createUnmarshaller();
            SchemaFactory schemaFactory =
                SchemaFactory.newInstance("http://www.w3.org/2001/XMLSchema");
            Schema schema =
                schemaFactory.newSchema(
                    new File(
                        "E:/上品/支付宝批量退货/java/refund_fastpay_by_platform_nopwd_jsp_utf8/src/net/shopin/alipay/entity/singleTradeQuery.xsd"));
            unMarshaller.setSchema(schema);
            Alipay alipay = (Alipay) unMarshaller.unmarshal(U);
            String outTradeNo = alipay.getResponse().getTrade().getOutTradeNo();
            // 根据单品订单号查询收货人姓名	详细邮寄地址	联系电话	省份	城市	邮编
            System.out.println(
                "SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
                    + "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
                    + outTradeNo
                    + "')");

            // 循环结果集
            Statement stmt = conn.createStatement();
            ResultSet rs =
                stmt.executeQuery(
                    "SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
                        + "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
                        + outTradeNo
                        + "')");
            while (rs.next()) {
              Label cell0 = new Label(0, i, tradeNo);
              Label cell1 = new Label(1, i, outTradeNo);
              Label cell2 = new Label(2, i, rs.getString("RECEPT_NAME"));
              Label cell3 = new Label(3, i, rs.getString("RECEPT_ADDRESS"));
              Label cell4 = new Label(4, i, rs.getString("RECEPT_PHONE"));
              Label cell5 = new Label(5, i, rs.getString("INCEPT_PROVINCE"));
              Label cell6 = new Label(6, i, rs.getString("INCEPT_CITY"));
              Label cell7 = new Label(7, i, rs.getString("INCEPT_POSTCODE"));

              sheet2.addCell(cell0);
              sheet2.addCell(cell1);
              sheet2.addCell(cell2);
              sheet2.addCell(cell3);
              sheet2.addCell(cell4);
              sheet2.addCell(cell5);
              sheet2.addCell(cell6);
              sheet2.addCell(cell7);
            }
            JDBCUtil.close(rs, stmt, null);

          } catch (Exception e) {
            e.printStackTrace();
          }

        } catch (Exception e) {
          e.printStackTrace();
        }
      }
      try {
        book2.write();
        book2.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    } catch (BiffException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IndexOutOfBoundsException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      try {
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    return null;
  }
コード例 #14
0
  private Result processRefundFastpayExcel(String realPath) {
    // TODO Auto-generated method stub
    Set<String> repetFilter = new HashSet(); // 过滤重复
    Workbook book = null;
    StringBuffer msg = new StringBuffer();
    Result result = new Result();
    // 2011112421847473^0.01^NOT_THIS_PARTNERS_TRAD
    int batchNum = 0;
    StringBuffer batchData = new StringBuffer();
    StringBuffer relation = new StringBuffer();
    BigDecimal totalRefund = new BigDecimal(0); // 退款总金额
    boolean hasError = false;
    try {
      try {
        book = Workbook.getWorkbook(new File(realPath));
      } catch (BiffException e) {
        e.printStackTrace();
        result.setMsg("导入文件读取失败,请重新导入或者联系开发人员!<br>");
        return result;
      } catch (IOException e) {
        e.printStackTrace();
        result.setMsg("导入文件读取失败,请重新导入或者联系开发人员!<br>");
        return result;
      }
      Sheet sheet = book.getSheet(0);
      int count = sheet.getRows();
      // 生成批量退款笔数及退款数据集
      if (count <= 2) { // 没有数据
        result.setMsg("没有数据!");
        return result;
      } else if (count
          >= (Integer.valueOf(PropertiesUtil.getProperties("alipay.batchNumLimit", "1000"))
                  .intValue()
              + 2)) { // 超出最大笔数
        result.setMsg(
            "支付宝即时到账批量退款,最大支持"
                + PropertiesUtil.getProperties("alipay.batchNumLimit", "1000")
                + "笔!");
        return result;
      } else { // 校验合法性
        for (int i = 1; i < count - 1; i++) { // 最后一行是合计				
          Cell[] cells = sheet.getRow(i);
          String outTradeNo =
              cells[Integer.valueOf(PropertiesUtil.getProperties("outTradeNOIndex", "0"))]
                  .getContents()
                  .trim();
          if (!repetFilter.contains(outTradeNo)) {
            repetFilter.add(outTradeNo);
          } else {
            msg.append(
                "EXCEL第"
                    + i
                    + "行(不包含表头部分)数据-单品订单号["
                    + outTradeNo
                    + "]在导入文件的前面部分已经出现请合并为一条退款记录!<br/>");
            hasError = true;
          }

          // 校验money
          String totalFee =
              cells[Integer.valueOf(PropertiesUtil.getProperties("totalFeeIndex", "0"))]
                  .getContents()
                  .trim();
          totalRefund = totalRefund.add(new BigDecimal(totalFee));
          String tradeNo = getTradeNoByOutTradeNo(outTradeNo, new BigDecimal(totalFee));

          // 校验tradeNo逻辑
          if (tradeNo == null) {
            msg.append("EXCEL第" + i + "行(不包含表头部分)数据-单品订单号[" + outTradeNo + "]获取支付宝交易号失败!<br/>");
            hasError = true;
          } else if (tradeNo.indexOf(
                  PropertiesUtil.getProperties(
                      "alipay.interface.returncode.outoftotalfee", "OUTOFTOTALFEE"))
              == 0) {
            msg.append("EXCEL第" + i + "行(不包含表头部分)数据-单品订单号[" + outTradeNo + "]退款总金额大于当前可退款金额!<br/>");
            hasError = true;
          } else {
            // 拼接relation (商家订单号1^支付宝交易号1#商家订单号2^支付宝交易号2)
            relation.append(outTradeNo).append("^").append(tradeNo);
            if (i < count - 2) {
              relation.append("#");
            }
          }

          String remark = PropertiesUtil.getProperties("refundRemark", "上品折扣支付宝退款");
          // 处理逻辑
          batchNum++;
          batchData.append(tradeNo + "^" + totalFee + "^" + remark);
          if (i < count - 2) {
            batchData.append("#");
          }
        }
      }

      if (hasError) {
        result.setMsg(msg.toString());
        return result;
      }

      result =
          refundFastpayService.processRefundFastpayExcel(
              batchNum, batchData.toString(), realPath, relation.toString(), totalRefund);

    } catch (RuntimeException e) {
      e.printStackTrace();
      result.setMsg("服务端异常,导致导入文件读取失败,请联系开发人员!<br>" + e.getMessage());
    } finally {
      book.close();
    }
    return result;
  }
コード例 #15
0
ファイル: UploadItemPart.java プロジェクト: sorinoi/laconic
  public void readData(ItemMgr itemMgr) throws SQLException {
    Connection conn = OracleGate.getConnection();
    itemMgr.getPnList().clear();
    Workbook workbook = null;
    try {
      workbook = Workbook.getWorkbook(this.getCurrentFile());
      Sheet sheet = workbook.getSheet(0); // the first sheet in excel file
      int rows = sheet.getRows();
      int columns = sheet.getColumns();

      for (int i = 1; i < rows; i++) {
        if (sheet.getCell(1, i).getContents().trim() == null
            || (sheet.getCell(1, i).getContents().trim()).equals("")) break;

        Part part = new Part("");
        if (columns > 1) {
          part.setPnCode(sheet.getCell(1, i).getContents().trim());
        }

        if (columns > 2) {
          part.setPnName(sheet.getCell(2, i).getContents().trim());
        }

        if (columns > 3) {
          part.setPartFullName(sheet.getCell(3, i).getContents().trim());
        }

        if (columns > 4) {
          part.setPartTemplateCode(sheet.getCell(4, i).getContents().trim());
        }

        if (columns > 5) {
          part.setRecurType(sheet.getCell(5, i).getContents().trim());
        }

        if (columns > 6) {
          part.setAccrued(sheet.getCell(6, i).getContents().trim().equals("Y"));
        }

        if (columns > 7) {
          part.setCashAccount(this.findAccounting(conn, sheet.getCell(7, i).getContents().trim()));
        }

        if (columns > 8) {
          part.setClaimAccount(this.findAccounting(conn, sheet.getCell(8, i).getContents().trim()));
        }

        if (columns > 9) {
          part.setEntityAccount(
              this.findAccounting(conn, sheet.getCell(9, i).getContents().trim()));
        }

        part.setPnRowNo(i);
        itemMgr.getPnList().add(part);
      }
    } catch (BiffException e) {
      e.printStackTrace();

    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      try {
        OracleGate.freeConnection(conn);
        workbook.close();
      } catch (Exception e) {
      }
    }
  }
コード例 #16
0
ファイル: JcglAction.java プロジェクト: crabeye/jjjw2008
  /**
   * 读取奖惩管理EXCEL内容
   *
   * @param mapping
   * @param form
   * @param request
   * @param response
   * @return
   */
  public ActionForward readJcglExcl(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response) {
    DynaActionForm fileForm = (DynaActionForm) form;
    FormFile formFile = (FormFile) fileForm.get("objExcel");
    List list = new ArrayList();
    try {
      InputStream excelInput = formFile.getInputStream();
      Workbook workbook = Workbook.getWorkbook(excelInput);
      Sheet sheet = workbook.getSheet(0);
      int rows = sheet.getRows();
      Xs0709 xs0709 = null;
      for (int i = 9; i < rows; i++) {
        try {
          String xh = sheet.getCell(0, i).getContents().trim();
          String wjrq = sheet.getCell(2, i).getContents().trim();
          String wjlb = sheet.getCell(3, i).getContents().trim();
          String cfmc = sheet.getCell(4, i).getContents().trim();
          String cfdw = sheet.getCell(5, i).getContents().trim();
          String wjjj = sheet.getCell(6, i).getContents().trim();
          String cfwh = sheet.getCell(7, i).getContents().trim();
          String cfrq = sheet.getCell(8, i).getContents().trim();
          xs0709 = new Xs0709();
          Xs0101 xs0101 = this.getJeffglServiceImpl().findXs0101Byxh(xh);
          if (xs0101 == null) {
            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘学号’列的值:"
                    + xh
                    + ",在系统中不存在,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;
          }
          xs0709.setXs0101(xs0101);
          if (wjrq != null && !wjrq.equals("")) {
            xs0709.setWjrq(wjrq);
          } else {
            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘违纪日期’列的值为空,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;
          }
          ZzdWjlb zdwjlb = null;
          if (wjlb != null && !wjlb.equals("")) {
            zdwjlb =
                (ZzdWjlb)
                    ZzdSingleton.getInstance(request.getSession().getServletContext())
                        .getZdByNameKey("ZzdWjlb", wjlb);
          } else {
            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘违纪类别’列的值为空,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;
          }
          if (zdwjlb == null) {

            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘违纪类别’列的值在系统中不存在,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;

          } else {
            xs0709.setZzdWjlb(zdwjlb);
          }

          ZzdCfmc zdwjmc = null;
          if (cfmc != null && !cfmc.equals("")) {
            zdwjmc =
                (ZzdCfmc)
                    ZzdSingleton.getInstance(request.getSession().getServletContext())
                        .getZdByNameKey("ZzdCfmc", cfmc);
          } else {

            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘处分名称’列的值为空,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;
          }
          if (zdwjmc == null) {

            this.writeJsMessage(
                response,
                "alert('导入失败,第"
                    + i
                    + "行,‘处分名称’列的值在系统中不存在,请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
            return null;

          } else {
            xs0709.setZzdCfmc(zdwjmc);
          }
          if (cfdw != null && !cfdw.equals("")) {
            xs0709.setGycfdw(cfdw);
          }
          if (wjjj != null && !wjjj.equals("")) {
            xs0709.setWjjk(wjjj);
          }
          if (cfwh != null && !cfwh.equals("")) {
            xs0709.setCfwh(cfwh);
          }
          if (cfrq != null && !cfrq.equals("")) {
            xs0709.setCfrq(cfrq);
          }
          list.add(xs0709);
        } catch (Exception e) {
          e.printStackTrace();
          return null;
        }
      }
      workbook.close();
    } catch (FileNotFoundException e) {
      this.writeJsMessage(
          response,
          "alert('数据导入失败,请检查EXCEL文件是否存在!');window.parent.returnValue='ok';window.parent.close();");
      e.printStackTrace();
      return null;
    } catch (IOException e) {
      this.writeJsMessage(
          response,
          "alert('数据导入失败,请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
      e.printStackTrace();
      return null;
    } catch (BiffException e) {
      e.printStackTrace();
      this.writeJsMessage(
          response,
          "alert('数据导入失败,请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
      return null;
    } catch (Exception e) {
      e.printStackTrace();
      this.writeJsMessage(
          response,
          "alert('数据导入失败,系统没有检测到的错误!');window.parent.returnValue='ok';window.parent.close();");
      return null;
    }
    try {
      if (list.size() != 0) {
        Iterator it = list.iterator();
        Xs0709 xs0709 = null;
        while (it.hasNext()) {
          xs0709 = (Xs0709) it.next();
          this.getIWjglService().saveXs0709(xs0709, request);
        }
      }
      this.writeJsMessage(
          response, "alert('数据导入成功!');window.parent.returnValue='ok';window.parent.close();");
    } catch (Exception e) {
      this.writeJsMessage(
          response,
          "alert('数据导入失败,一般是由于数据不符合格式引起,请检查数据!');window.parent.returnValue='ok';window.parent.close();");
    }
    return null;
  }
コード例 #17
0
  public String readFile() throws IOException {

    File inputWorkbook = new File(archivo);
    Workbook w;
    try {
      String msg = "";
      w = Workbook.getWorkbook(inputWorkbook);
      // Get the first sheet
      Sheet sheet = w.getSheet(0);

      // int defaultRows = sheet.getRows();

      // Si la cantidad de columnas es 4
      if (sheet.getColumns() >= 4 && sheet.getRows() > 1) {

        // Valido que las cabeceras tengan el formato correcto
        if (!sheet.getCell(0, 0).getContents().equals("ORIGEN")
            || !sheet.getCell(1, 0).getContents().equals("DESTINO")
            || !sheet.getCell(2, 0).getContents().equals("PRODUCTO")
            || !sheet.getCell(3, 0).getContents().equals("PIEZAS")) {
          msg = Msg.translate(getCtx(), "Column Names");
          return msg;
        }

        // El vector que almacena la estructura de traspasos
        Vector<Movimiento> movimientos = new Vector<Movimiento>();
        Iterator<Movimiento> iterator_movimientos = null;
        Movimiento movimiento_temporal = null, movimiento_nuevo = null;
        for (int i = 1;
            i < sheet.getRows() && getStore(sheet.getCell(0, i).getContents()) != null;
            i++) {

          // Capturo el origen
          MWarehouse origen = getStore(sheet.getCell(0, i).getContents());
          MWarehouse destino = getStore(sheet.getCell(1, i).getContents());

          String isStore = "N";
          // Aca debo bucar los values de los m_warehouse que son cd
          String sql =
              "SELECT XX_IsStore FROM M_Warehouse WHERE M_Warehouse_ID in ( "
                  + origen.getM_Warehouse_ID()
                  + ","
                  + destino.getM_Warehouse_ID()
                  + ")";
          PreparedStatement pstmt = null;
          ResultSet rs = null;
          try {
            pstmt = DB.prepareStatement(sql, null);
            rs = pstmt.executeQuery();

            while (rs.next()) {
              if (rs.getString(1).equals("Y")) isStore = rs.getString(1);
            }

          } catch (Exception a) {
            log.log(Level.SEVERE, sql, a);
          } finally {
            DB.closeResultSet(rs);
            DB.closeStatement(pstmt);
          }

          if (origen == null || isStore.equals("Y"))
            return Msg.translate(getCtx(), "Cell A Error")
                + (i + 1)
                + " "
                + sheet.getCell(0, i).getContents();

          // Capturo el destino

          if (destino == null || isStore.equals("Y"))
            return Msg.translate(getCtx(), "Cell B Error")
                + (i + 1)
                + " "
                + sheet.getCell(1, i).getContents();

          // Capturo el producto
          String p_id = sheet.getCell(2, i).getContents();
          MProduct producto = getProduct(p_id);
          if (producto == null) {
            return Msg.translate(getCtx(), "Cell C Error")
                + (i + 1)
                + " "
                + sheet.getCell(2, i).getContents();
          }

          // Capturo las piezas
          Double piezas = null;
          try {
            if (sheet.getCell(3, i) != null)
              piezas = Double.parseDouble(sheet.getCell(3, i).getContents());
          } catch (NumberFormatException e) {
            return Msg.translate(getCtx(), "Cell D Error")
                + (i + 1)
                + " "
                + sheet.getCell(3, i).getContents();
          }
          if (piezas == null) {
            return Msg.translate(getCtx(), "Cell D Error")
                + (i + 1)
                + " "
                + sheet.getCell(3, i).getContents();
          }

          // Agregar cada fila al vector de traspasos
          iterator_movimientos = movimientos.iterator();
          boolean encontrado = false;
          while (iterator_movimientos.hasNext()) {
            movimiento_temporal = iterator_movimientos.next();

            // Si un traspaso anterior va a la misma tienda, departamento, etc
            if (movimiento_temporal.origen == origen.get_ID()) {
              if (movimiento_temporal.destino == destino.get_ID()) {
                if (movimiento_temporal.departamento == producto.getXX_VMR_Department_ID()) {
                  // Entonces agregar algo al objeto traspaso

                  movimiento_temporal.agregarCantidades(producto.get_ID(), piezas);
                  encontrado = true;
                  break;
                }
              }
            }
          }

          // Si no se encontró en el vector de traspasos agregarlo
          if (!encontrado) {
            movimiento_nuevo =
                new Movimiento(
                    origen.get_ID(), destino.get_ID(), producto.getXX_VMR_Department_ID());
            movimiento_nuevo.agregarCantidades(producto.get_ID(), piezas);
            movimientos.add(movimiento_nuevo);
          }
        }

        // Una vez leido todo el archivo se procede a crear los traspasos
        // System.out.println(traspasos);
        return procesarMovimientos(movimientos).toString();

      } else {
        return Msg.translate(getCtx(), "4 Columns");
      }
    } catch (BiffException e) {
      log.log(Level.SEVERE, e.getMessage());
    }
    return "";
  }