Пример #1
0
  public ExcelDataProvider(String classname, String methodname) {

    try {

      int dotNum = classname.indexOf(".");

      if (dotNum > 0) {
        classname = classname.substring(classname.lastIndexOf(".") + 1, classname.length());
      }

      String path = "data/" + classname + ".xls";
      InputStream inputStream = new FileInputStream(path);

      book = Workbook.getWorkbook(inputStream);
      //            方法名即为sheet名
      sheet = book.getSheet(methodname);
      //            sheet = book.getSheet(0);
      rowNum = sheet.getRows();
      Cell[] cell = sheet.getRow(0);
      columnNum = cell.length;
      columnnName = new String[cell.length];

      for (int i = 0; i < cell.length; i++) {
        columnnName[i] = cell[i].getContents().toString();
      }
      this.currentRowNo++;

    } catch (Exception e) {
      e.printStackTrace();
      Assert.fail("unable to read Excel data");
    }
  }
Пример #2
0
 public static List<ConsoleVo> readExcel(String fileName) {
   List<ConsoleVo> list = new ArrayList<ConsoleVo>();
   try {
     Workbook book = Workbook.getWorkbook(new File(fileName));
     Sheet[] sheets = book.getSheets();
     for (Sheet s : sheets) {
       for (int i = 1; i < s.getRows(); i++) {
         ConsoleVo m = new ConsoleVo();
         Cell[] cells = s.getRow(i);
         if (cells != null) {
           System.out.println(cells.length);
           m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents()));
           m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents()));
           m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents()));
           m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents()));
           list.add(m);
         } else {
           System.out.print("excel格式错误导入数据失败!");
           return null;
         }
       }
     }
     book.close();
     return list;
   } catch (Exception e) {
     e.printStackTrace();
   }
   return null;
 }
Пример #3
0
  public static void main(String args[]) {
    try {
      // 构建Workbook对象, 只读Workbook对象
      // 直接从本地文件创建Workbook
      // 从输入流创建Workbook

      System.out.println("start load file-------------------------");
      InputStream is = new FileInputStream("E:/account.xls"); // 创建输入

      jxl.Workbook rwb = Workbook.getWorkbook(is);
      Sheet rs = rwb.getSheet(0); // 读取第一个sheet
      int colNum = rs.getColumns(); // 列数
      int rowNum = rs.getRows(); // 行数
      // System.out.println(colNum+" "+rowNum);
      // 创建account  bean
      ApplicationContext ctx =
          new ClassPathXmlApplicationContext(
              new String[] {
                "spring/applicationContext-model-account.xml", "spring/applicationContext.xml"
              });
      AccountService g = (AccountService) ctx.getBean("accountService");
      for (int i = 1; i < rowNum; i++) {
        String no = rs.getCell(0, i).getContents();
        String pass = rs.getCell(1, i).getContents();
        String name = rs.getCell(2, i).getContents();
        String maj = rs.getCell(3, i).getContents();
        String grade = rs.getCell(4, i).getContents();
        String cla = rs.getCell(5, i).getContents();
        String idn = rs.getCell(6, i).getContents();
        String email = rs.getCell(7, i).getContents();
        String phone = rs.getCell(8, i).getContents();
        String status = rs.getCell(9, i).getContents();
        String role = rs.getCell(10, i).getContents();

        ModelAccount ma = new ModelAccount();
        ma.setAccountNo(no);
        ma.setAccountPassword(pass);
        ma.setAccountRealName(name);
        ma.setAccountMajority(maj);
        ma.setAccountGrade(grade);
        ma.setAccountClass(cla);
        ma.setAccountIdentification(idn);
        ma.setAccountEmail(email);
        ma.setAccountMobilePhone(phone);
        ma.setAccountStatus(0);
        ModelRoles ac = new ModelRoles();
        ac.setRole_id(role);
        ma.setAccountRole(ac);
        g.saveOrUpdate(ma);
        // ma.setAccount_role(account_role)
        // System.out.println(no);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Пример #4
0
  public void createExcel(String file) {

    String filename = createPath(file);
    System.out.println(filename);

    /*
       try {
       WorkbookSettings ws = new WorkbookSettings();
       ws.setLocale(new Locale("sv", "SE"));
       WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
       WritableSheet s = workbook.createSheet("Sheet1", 0);
    //writeDataSheet(s);
    workbook.write();
    workbook.close();
    } catch (IOException e) {
    e.printStackTrace();
    } catch (WriteException e) {
    e.printStackTrace();
    }**/

    try {

      File newFile = new File(filename);
      Writer out =
          new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newFile), "UTF8"));
      //	BufferedWrite output = new BufferedWriter(new OutputStreamReader(new
      // FileInputS§tream(filename), "iso-8859-1"));
      // FileWriter fw = new FileWriter(newFile.getAbsoluteFile());
      // BufferedWriter bw = new BufferedWriter(fw);
      // bw.write("Artikel;Antal/st;Pris/st;Total\n");
      out.append("Artikel;Antal/st;Pris/st;Total\n");
      for (Data d : dataList) {
        out.append(d.toString());
        out.append("\n");
        // bw.write(d.toString());
        // bw.write("\n");
      }
      // bw.close();
      out.flush();
      out.close();
    } catch (UnsupportedEncodingException unsuppEn) {
      unsuppEn.printStackTrace();
    } catch (IOException ioE) {
      ioE.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Пример #5
0
  // DESDE AQUI AGREGADO POR GHUCHET
  private void completarMovimiento(MMovement move) {
    // Preparar el movimiento
    move.setDocAction(X_M_Movement.DOCACTION_Prepare);
    DocumentEngine.processIt(move, X_M_Movement.DOCACTION_Prepare);
    move.save();
    commit();
    move.load(get_TrxName());
    if (move.getDocStatus().equals("IN")) {
      ADialog.error(EnvConstants.WINDOW_INFO, new Container(), "NoLines");
    } else {

      // Aca debo actualizar las cantidades aprobadas
      String sql =
          "SELECT QtyRequired, XX_ApprovedQty FROM M_MOVEMENTLINE WHERE M_MOVEMENT_ID = "
              + move.getM_Movement_ID();
      PreparedStatement ps_1 =
          DB.prepareStatement(
              sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, null);
      ResultSet rs_1 = null;
      // System.out.println(sql);
      int qty_required = 0;
      try {
        rs_1 = ps_1.executeQuery();
        while (rs_1.next()) {
          qty_required = rs_1.getInt("QtyRequired");
          rs_1.updateInt("XX_ApprovedQty", qty_required);
          rs_1.updateRow();
        }
      } catch (Exception e) {
        e.printStackTrace();

      } finally {
        try {
          rs_1.close();
          ps_1.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }

      move.setXX_Status("PE");
      move.setXX_RequestDate(move.getUpdated());
      move.save();

      // Se envian correos dependiendo del tipo de documento
      enviarCorreoATienda(move, Env.getCtx().getContextAsInt("#XX_L_MT_PTRANSFERAPPROVAL_ID"));
    }
  }
Пример #6
0
  public boolean hasNext() {

    if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) {

      try {
        book.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
      return false;
    } else {
      // sheet下一行内容为空判定结束
      if ((sheet.getRow(currentRowNo))[0].getContents().equals("")) return false;
      return true;
    }
  }
Пример #7
0
  public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) {

    String main_tsn = "";
    String main_rank = "";
    String main_kingdom = "";
    row = 0;
    try {
      statement = new_statement;
      func = new AdditionalFunctions(statement);
      copy = workbook;
      kingdom = arguments[0];
      hrank = arguments[1];
      lrank = arguments[2];
      scientificName = arguments[3];
      date_from = arguments[4];
      date_to = arguments[5];
      user = arguments[6];

      String temp = "";
      temp =
          "SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'";
      System.out.println(temp);
      result = statement.executeQuery(temp);
      metadata = result.getMetaData();
      int numberOfRows = 0;
      int hrankVal = Integer.parseInt(hrank);
      int main_rankVal = 0;
      if (result.last()) numberOfRows = result.getRow();
      result.first();
      if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
        main_tsn = result.getString(1);
        main_rank = result.getString(2);
        main_rankVal = Integer.parseInt(main_rank);
        main_kingdom = result.getString(3);
      }
      if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func);

      FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments);
      copy.write();
      copy.close();
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of constructor
Пример #8
0
  // public method to extract and write the Publication information
  public static void WritePublication(String publicationId, int rowNum) {

    WritableSheet sheet2 = copy.getSheet(1);

    try {
      String query =
          "SELECT address,author,publicationTitle,chapter,edition,editor,month,day,note,number,publisher,"
              + "series,title,volume,year,isbn,issn,pages FROM Publication WHERE id="
              + publicationId;
      System.out.println(query);

      ResultSet result_query = statement.executeQuery(query);
      ResultSetMetaData metadata_query = result_query.getMetaData();
      int numberOfRows = 0;
      if (result_query.last()) numberOfRows = result_query.getRow();
      result_query.first();
      if (numberOfRows > 0 && metadata_query.getColumnCount() > 0) {
        String aut_edi = result_query.getString(2);
        System.out.println("autor is " + aut_edi);
        if (result_query.getString(6).compareTo("") != 0)
          aut_edi = aut_edi + " / " + result_query.getString(6) + ",eds.";
        Label label = new Label(7, rowNum, aut_edi);
        sheet2.addCell(label);
        label = new Label(8, rowNum, result_query.getString(13));
        sheet2.addCell(label);
        String publication_name = result_query.getString(3);
        if (result_query.getString(12).compareTo("") != 0)
          publication_name = publication_name + ", ser. " + result_query.getString(12);
        if (result_query.getString(14).compareTo("0") != 0)
          publication_name = publication_name + ", vol. " + result_query.getString(14);
        if (result_query.getString(10).compareTo("0") != 0)
          publication_name = publication_name + ",no. " + result_query.getString(10);
        label = new Label(9, rowNum, publication_name);
        sheet2.addCell(label);
        label = new Label(12, rowNum, result_query.getString(11));
        sheet2.addCell(label);
        label = new Label(13, rowNum, result_query.getString(1));
        sheet2.addCell(label);
        label = new Label(15, rowNum, result_query.getString(9));
        sheet2.addCell(label);
        label = new Label(16, rowNum, result_query.getString(16));
        sheet2.addCell(label);
        label = new Label(17, rowNum, result_query.getString(17));
        sheet2.addCell(label);
        label = new Label(14, rowNum, result_query.getString(18));
        sheet2.addCell(label);

        String date = result_query.getString(15);
        String month = "Jan";
        System.out.println("month is " + result_query.getString(7));
        if (result_query.getString(7).compareTo("") != 0) month = result_query.getString(7);
        month = func.ConvertMonth(month);
        date = month + "/" + date;
        String day = "01";
        if (result_query.getString(8).compareTo("0") != 0) day = result_query.getString(8);
        if (day.length() == 1) day = "0" + day;
        date = day + "/" + date;
        label = new Label(10, rowNum, date);
        sheet2.addCell(label);
        label = new Label(11, rowNum, date);
        sheet2.addCell(label);
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of WritePublication
Пример #9
0
  // public method to find child information
  public void FindChildInformation(String tsn, String[] arguments, AdditionalFunctions func) {

    WritableSheet sheet2 = copy.getSheet(1);
    Label label = null;
    String children[] = null;
    String temp = "", current_tsn = "", dateCreated = "";
    int dateCheck = 1;
    temp = "SELECT distinct tsn from Tree where parent_tsn='" + tsn + "'";
    try {
      result = statement.executeQuery(temp);
      metadata = result.getMetaData();
      int numberOfRows = 0;
      if (result.last()) numberOfRows = result.getRow();
      if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
        result.first();
        children = new String[numberOfRows];
        for (int j = 0; j < numberOfRows; j++) {
          children[j] = result.getString(1);
          result.next();
        }
      }
      for (int j = 0; j < numberOfRows; j++) {
        int current_tsnId = Integer.parseInt(children[j]);
        if (current_tsnId >= 999000000) {
          temp =
              "SELECT distinct Tree.tsn,unit_name1,unit_name2,unit_name3,unit_name4,scientificName,`usage`,"
                  + "taxon_author,parent_tsn,rank_name, pages, publicationId, userId, dateCreated, nameType FROM Tree,"
                  + " BaseObject, TaxonConcepts, TaxonAuthors,TaxonUnitTypes"
                  + " WHERE Tree.tsn=TaxonConcepts.tsn AND Tree.tsn='"
                  + current_tsnId
                  + "' AND "
                  + "BaseObject.id=TaxonConcepts.id AND TaxonAuthors.taxon_author_id=Tree.taxon_author_id AND "
                  + "Tree.rank_id=TaxonUnitTypes.rank_id";
          System.out.println(temp);

          result = statement.executeQuery(temp);
          metadata = result.getMetaData();
          int RowNumber = 0;
          if (result.last()) RowNumber = result.getRow();
          if (RowNumber > 0 && metadata.getColumnCount() > 0) {
            current_tsn = result.getString(1);
            dateCreated = result.getString(14);
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date d1 = df.parse(dateCreated);
            Date d2 = null;
            if (date_from.compareTo("all") != 0) {
              d2 = df.parse(date_from);
              if (d2.before(d1) || d2.equals(d1)) dateCheck = 1;
              else dateCheck = 0;
            }
            if (date_to.compareTo("today") != 0) {
              d2 = df.parse(date_to);
              if (d1.before(d2) || d1.equals(d2)) dateCheck = 1;
              else dateCheck = 0;
            }
            if (result.getString(7).compareTo("public") == 0
                && result.getString(15).compareTo("Regular scientific name") == 0
                && result.getString(12).compareTo("") != 0
                && dateCheck == 1) {

              String scientificName = result.getString(6);
              String taxon_author = result.getString(8);
              String publicationId = result.getString(12);
              for (int k = 0; k < 4; k++) {
                if (result.getString(k + 2).compareTo("null") != 0) {
                  label = new Label(k, row + 1, result.getString(k + 2));
                  sheet2.addCell(label);
                }
              }
              label = new Label(4, row + 1, taxon_author);
              sheet2.addCell(label);
              label = new Label(5, row + 1, result.getString(10));
              sheet2.addCell(label);

              String parent_tsn = result.getString(9);
              String userId = result.getString(13);
              WritePublication(publicationId, row + 1);
              label = new Label(18, row + 1, func.GetSubmitter(userId));
              sheet2.addCell(label);
              label = new Label(6, row + 1, func.GetParent(parent_tsn));
              sheet2.addCell(label);

              row = row + 1;
            }
          }
        }
        continue;
      }
      for (int j = 0; j < numberOfRows; j++) {
        if (HasChildren(children[j]) == true) FindChildInformation(children[j], arguments, func);
        else continue;
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of FindChildInformation
Пример #10
0
  /** Envia correos al personal de tienda en caso de la aprobación de un traspaso */
  private void enviarCorreoATienda(MMovement movimiento, int plantillaDeCorreo) {

    MWarehouse almaSalida = new MWarehouse(getCtx(), movimiento.getM_WarehouseFrom_ID(), null);
    MWarehouse almaLleada = new MWarehouse(getCtx(), movimiento.getM_WarehouseTo_ID(), null);
    X_XX_VMR_Department departamento =
        new X_XX_VMR_Department(getCtx(), movimiento.getXX_VMR_Department_ID(), null);

    // Mensaje debe indicar departamento, origen, destino y traspaso
    String mensaje =
        Msg.getMsg(
            getCtx(),
            "XX_PTransferApproval",
            new String[] {
              movimiento.getDocumentNo(),
              departamento.getValue() + "-" + departamento.getName(),
              almaSalida.getValue() + "-" + almaSalida.getName(),
              almaLleada.getValue() + "-" + almaLleada.getName(),
              movimiento.getXX_StatusName()
            });

    // Al Gerente de Tienda
    // Selecciono el o los gerentes de Tienda
    String SQL =
        "SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' "
            + " AND C_BPARTNER_ID IN "
            + "("
            + "SELECT C_BPARTNER_ID "
            + "FROM C_BPARTNER WHERE isActive='Y' "
            + "AND C_JOB_ID ="
            + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_STOREMAN_ID")
            + " "
            + "AND (M_WAREHOUSE_ID = "
            + almaLleada.get_ID()
            + " "
            + "OR M_WAREHOUSE_ID = "
            + almaSalida.get_ID()
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")"
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")";

    Vector<Integer> storeManagers = new Vector<Integer>();
    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        storeManagers.add(rs.getInt("AD_USER_ID"));
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    // Envio correos a los gerentes
    Utilities f = null;
    for (int i = 0; i < storeManagers.size(); i++) {

      f =
          new Utilities(
              Env.getCtx(),
              null,
              plantillaDeCorreo,
              mensaje,
              -1,
              Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
              -1,
              storeManagers.get(i),
              null);
      try {
        f.ejecutarMail();
      } catch (Exception e) {
        e.printStackTrace();
      }
      f = null;
    }

    // *********************************
    // Selecciono los asesores de almacen
    SQL =
        "SELECT AD_USER_ID FROM AD_USER WHERE  ISACTIVE='Y' "
            + "AND C_BPARTNER_ID IN "
            + "("
            + "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' "
            + "AND C_JOB_ID ="
            + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_DEPASE_ID")
            + " "
            + "AND (M_WAREHOUSE_ID = "
            + almaLleada.get_ID()
            + " "
            + "OR M_WAREHOUSE_ID = "
            + almaSalida.get_ID()
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")"
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")";

    Vector<Integer> warehouseAsessors = new Vector<Integer>();
    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        warehouseAsessors.add(rs.getInt("AD_USER_ID"));
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    // Envio correos a los asesores
    Utilities m = null;
    for (int i = 0; i < warehouseAsessors.size(); i++) {

      m =
          new Utilities(
              Env.getCtx(),
              null,
              plantillaDeCorreo,
              mensaje,
              -1,
              Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
              -1,
              warehouseAsessors.get(i),
              null);
      try {
        m.ejecutarMail();
      } catch (Exception e) {
        e.printStackTrace();
      }
      m = null;
    }

    // *********************************
    // Selecciono los Gerentes de Area ADMIN y MERCA, Asesor de Inventario
    SQL =
        "SELECT AD_USER_ID FROM AD_USER WHERE  ISACTIVE='Y' "
            + "AND C_BPARTNER_ID IN "
            + "("
            + "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' "
            + "AND C_JOB_ID IN ("
            + " "
            + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_ADMINMANAG_ID")
            + ","
            + " "
            + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_GAMERC_ID")
            + ","
            + " "
            + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_INVASSES_ID")
            + ") "
            + "AND (M_WAREHOUSE_ID = "
            + almaLleada.get_ID()
            + " "
            + "OR M_WAREHOUSE_ID = "
            + almaSalida.get_ID()
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")"
            + ") "
            + "AND AD_Client_ID IN (0,"
            + Env.getCtx().getAD_Client_ID()
            + ")";

    Vector<Integer> managerArea = new Vector<Integer>();
    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        managerArea.add(rs.getInt("AD_USER_ID"));
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    // Envio correos a gerentes de area
    Utilities u = null;
    for (int i = 0; i < managerArea.size(); i++) {

      u =
          new Utilities(
              Env.getCtx(),
              null,
              plantillaDeCorreo,
              mensaje,
              -1,
              Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
              -1,
              managerArea.get(i),
              null);
      try {
        u.ejecutarMail();
      } catch (Exception e) {
        e.printStackTrace();
      }
      u = null;
    }
  }