Exemplo n.º 1
0
  public static void main(String g[]) {
    DataDto ddto = new DataDto();
    try {
      ArrayList<String> executeTC = new ArrayList();
      String execute = null;
      String workingDir = System.getProperty("user.dir");
      System.out.println("Current working directory : " + workingDir);
      String exPath = workingDir + "\\TestScripts\\Data\\Execute_Script.xls";
      File file = new File(exPath);
      System.out.println(" " + file);
      Workbook wb = Workbook.getWorkbook(file);
      Sheet sheet = wb.getSheet(0);
      int rows = sheet.getRows();
      int column = sheet.getColumns();
      System.out.println("Rows " + rows);
      for (int i = 1; i < rows; i++) {
        ddto.setTCID(sheet.getCell(0, i).getContents());
        ddto.setEXECUTE(sheet.getCell(1, i).getContents());
        if (ddto.getEXECUTE().equalsIgnoreCase("Y")) {
          getExecute.add(ddto.getEXECUTE());
          executeTCID.add(ddto.getTCID());
        }
      }
      System.out.println("execute condition :" + executeTCID);
      System.out.println(getExecute);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Exemplo n.º 2
0
 /**
  * 取得列数
  *
  * @return 列数
  */
 public int getCols() {
   if (readOnlyWBook == true) {
     return currentSheet.getColumns();
   } else {
     return wrCurrentSheet.getColumns();
   }
 }
Exemplo n.º 3
0
 /**
  * @see read excel by row,read sheet
  * @param sheet
  * @param numrow
  * @return
  */
 public static ArrayList readrow(Sheet sheet, int numrow) {
   ArrayList contentlist = new ArrayList();
   for (int numcol = 0; numcol < sheet.getColumns(); numcol++) {
     Cell cell = sheet.getCell(numcol, numrow);
     String content = cell.getContents();
     contentlist.add(content);
   }
   return contentlist;
 }
Exemplo n.º 4
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();
    }
  }
Exemplo n.º 5
0
 public int getColumnCount() {
   if (reductionSelection == null) {
     return excelSheet.getColumns() + 1;
   } else {
     return reductionSelection.getColumnIndexEnd()
         - reductionSelection.getColumnIndexStart()
         + 1
         + 1;
   }
 }
Exemplo n.º 6
0
 /**
  * 获取第一个第一行单元格内容非空的列号,因为第一行为参数key,不能为空
  *
  * @param mSheet
  * @return
  */
 private static int getColCount(Sheet mSheet) {
   int count = 0;
   int columns = mSheet.getColumns();
   for (int i = 0; i < columns; i++) {
     String content = mSheet.getCell(i, 0).getContents();
     if (StringTools.isEmpty(content)) {
       break;
     }
     ++count;
   }
   return count;
 }
Exemplo n.º 7
0
  public static List<Map<String, String>> getXlsUserInfoData(String path) {
    try {
      File xlsFile = new File(path);
      Workbook book = Workbook.getWorkbook(xlsFile);
      Sheet[] sheet = book.getSheets();
      if (sheet.length > 0) {
        Sheet page = sheet[0];
        int columns = page.getColumns();
        System.out.println(columns);
        int rows = page.getRows();
        System.out.println(page.getName());
        if (rows < 2) return null;

        int columnsCount = 0;

        List<String> titleList = new ArrayList<String>();
        for (int i = 0; i < columns; i++) {
          // 列    行
          Cell cell = page.getCell(i, 0);
          String key = cell.getContents();
          if (key == null || key.equals("")) {
            continue;
          }
          columnsCount++;
          logger.debug(key);
          titleList.add(key);
        }
        columns = columnsCount;
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = 1; i < rows; i++) {
          logger.info("ii:" + i);
          Map<String, String> dataMap = new HashMap<String, String>();
          for (int j = 0; j < columns; j++) {
            String key = titleList.get(j);
            String value = page.getCell(j, i).getContents();
            dataMap.put(key, value);
            logger.debug(key + "  " + value);
          }
          dataList.add(dataMap);
        }
        logger.debug("xls length:" + dataList.size());
        return dataList;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return null;
  }
Exemplo n.º 8
0
 public List<FreshCard> BeangetFromExcel() {
   File operFile = new File(Constant.EXCBUILS, needFileName);
   List<FreshCard> list = new ArrayList<FreshCard>();
   try {
     Workbook wb = Workbook.getWorkbook(operFile);
     int sheets = wb.getSheets().length;
     for (int i = 0; i < sheets; i++) {
       Sheet sheet = wb.getSheet(i);
       int k;
       if (sheet != null) {
         int col = sheet.getColumns();
         int row = sheet.getRows();
         for (int j = 0; j < row; j++) {
           if (j > 0) {
             FreshCard fd = new FreshCard();
             for (k = 0; k < col; k++) {
               String data = sheet.getCell(k, j).getContents();
               if (k == 0) {
                 fd.setFreshTime(data);
               } else if (k == 1) {
                 fd.setCardId(Integer.parseInt(data));
               } else if (k == 2) {
                 fd.setName(data);
               } else if (k == 3) {
                 fd.setDuty(data);
               } else if (k == 4) {
                 fd.setNum(data);
               } else if (k == 5) {
                 fd.setFreshCount(Integer.parseInt(data));
               } else if (k == 6) {
                 fd.setState(data);
               } else {
                 break;
               }
             }
             list.add(fd);
           }
         }
       }
     }
   } catch (BiffException | IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return list;
 }
Exemplo n.º 9
0
  /** @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();
    }
  }
  /**
   * Gets the named cell from this workbook. If the name refers to a range of cells, then the cell
   * on the top left is returned. If the name cannot be found, null is returned
   *
   * @param name the name of the cell/range to search for
   * @return the cell in the top left of the range if found, NULL otherwise
   */
  public Cell findCellByName(String name) {
    NameRecord nr = (NameRecord) namedRecords.get(name);

    if (nr == null) {
      return null;
    }

    NameRecord.NameRange[] ranges = nr.getRanges();

    // Go and retrieve the first cell in the first range
    Sheet s = getSheet(getExternalSheetIndex(ranges[0].getExternalSheet()));
    int col = ranges[0].getFirstColumn();
    int row = ranges[0].getFirstRow();

    // If the sheet boundaries fall short of the named cell, then return
    // an empty cell to stop an exception being thrown
    if (col > s.getColumns() || row > s.getRows()) {
      return new EmptyCell(col, row);
    }

    Cell cell = s.getCell(col, row);

    return cell;
  }
Exemplo n.º 11
0
  /**
   * @param is 要导入Excel的输入流
   * @param sheetName 导入的工作表名称
   * @param entityClass List中对象的类型(Excel中的每一行都要转化为该类型的对象)
   * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例:{id=编号}
   * @param uniqueFields 指定业务主键组合(即复合主键),这些列的组合不能重复
   * @return List
   * @throws ExcelException @Description 将Excel转化成实体对象List
   */
  public static <T> List<T> excelToList(
      InputStream is,
      String sheetName,
      Class<T> entityClass,
      LinkedHashMap<String, String> fieldMap,
      String[] uniqueFields)
      throws ExcelException {
    // 定义要返回的list
    List<T> resultList = new ArrayList<T>();

    try {
      // 根据excel数据源创建WorkBook
      Workbook wb = Workbook.getWorkbook(is);

      // 获取工作表
      Sheet sheet = wb.getSheet(sheetName);

      // 获取工作表的有效行数
      int realRows = 0;
      for (int i = 0; i < sheet.getRows(); i++) {

        int nullCols = 0;
        for (int j = 0; j < sheet.getColumns(); j++) {
          Cell CurrentCell = sheet.getCell(j, i);
          if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
            nullCols++;
          }
        }

        if (nullCols == sheet.getColumns()) {
          break;
        } else {
          realRows++;
        }
      }

      // 如果Excel中没有任何数据则提示错误信息
      if (realRows <= 1) {
        throw new ExcelException("Excel文件中没有任何数据");
      }

      Cell[] firstRow = sheet.getRow(0);
      String[] excelFieldNames = new String[firstRow.length];
      // 获取Excel的列名
      for (int i = 0; i < firstRow.length; i++) {
        excelFieldNames[i] = firstRow[i].getContents().toString().trim();
      }
      // 判断需要的字段在Excel中是否都存在
      boolean isExist = true;
      List<String> excelFieldList = Arrays.asList(excelFieldNames);
      for (String cnName : fieldMap.values()) {
        if (!excelFieldList.contains(cnName)) {
          isExist = false;
          break;
        }
      }

      // 如果有列名不存在或不匹配,则抛出异常并提示错误
      if (!isExist) {
        throw new ExcelException("Excel中缺少必要的字段,或字段名称有误");
      }

      // 将列名和列号放入Map中,这样通过列名就可以拿到列号
      LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
      for (int i = 0; i < excelFieldNames.length; i++) {
        colMap.put(excelFieldNames[i], firstRow[i].getColumn());
      }

      // 判断是否有重复行
      // 1.获取uniqueFields指定的列
      Cell[][] uniqueCells = new Cell[uniqueFields.length][];
      for (int i = 0; i < uniqueFields.length; i++) {
        int col = colMap.get(uniqueFields[i]);
        uniqueCells[i] = sheet.getColumn(col);
      }
      // 2.从指定列中寻找重复行
      for (int i = 1; i < realRows; i++) {
        int nullCols = 0;
        int length = uniqueFields.length;
        for (int j = 0; j < length; j++) {
          Cell currentCell = uniqueCells[j][i];
          String currentContent = currentCell.getContents().toString().trim();
          Cell sameCell =
              sheet.findCell(
                  currentContent,
                  currentCell.getColumn(),
                  currentCell.getRow() + 1,
                  currentCell.getColumn(),
                  uniqueCells[j][realRows - 1].getRow(),
                  true);
          if (sameCell != null) {
            nullCols++;
          }
        }
        // 复合主键,意味着这些列的组合不能重复,
        // 只有当所有的列都有重复的时候,才被认为是有重复行
        if (nullCols == length) {
          throw new Exception("Excel中有重复行,请检查");
        }
      }

      // 将sheet转换为list
      for (int i = 1; i < realRows; i++) {
        // 新建要转换的对象
        T entity = entityClass.newInstance();

        // 给对象中的字段赋值
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
          // 获取英文字段名
          String enNormalName = entry.getKey();
          // 获取中文字段名
          String cnNormalName = entry.getValue();
          // 根据中文字段名获取列号
          int col = colMap.get(cnNormalName);

          // 获取当前单元格中的内容
          String content = sheet.getCell(col, i).getContents().toString().trim();

          // 给对象赋值
          setFieldValueByName(enNormalName, content, entity);
        }

        resultList.add(entity);
      }
    } catch (Exception e) {
      e.printStackTrace();
      // 如果是ExcelException,则直接抛出
      if (e instanceof ExcelException) {
        throw (ExcelException) e;
      } else {
        // 否则将其包装成ExcelException,再将其抛出
        throw new ExcelException("导入ExceL失败");
      }
    }

    return resultList;
  }
Exemplo n.º 12
0
  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) {
      }
    }
  }
  public int ImportDefalutXls(Context mContext) {
    try {
      String xlsType = "";
      InputStream is = null;

      String downloadXlsPath = getUpdateXlsPath(mContext) + File.separator + "address.xls";
      File downloadXlsFile = new File(downloadXlsPath);
      if (downloadXlsFile.exists()) {
        LogUtils.i(XlsUtils.class.getName(), "downloadXlsPath:" + downloadXlsPath);
        is = new FileInputStream(downloadXlsFile);
        xlsType = "xls";
      } else {
        AssetManager assets = mContext.getAssets();
        String[] files = assets.list("");

        for (String fileName : files) {
          if (fileName.equals(ConfigUtils.DEFAULT_XLS_NAME)) {
            xlsType = "xls";
            is = assets.open(fileName);
            LogUtils.i(XlsUtils.class.getName(), "assets file");
            break;
          } else if (fileName.equals(ConfigUtils.DEFAULT_XLSX_NAME)) {
            xlsType = "xlsx";
            is = assets.open(fileName);
            break;
          } else {
            return -1;
          }
        }
      }

      if (xlsType.equals("xls")) {
        Workbook wb = null;
        wb = Workbook.getWorkbook(is);
        Sheet sht =
            wb.getSheet(XResources.getString(mContext, ConfigUtils.SHEET_NAME)); // name or number

        int coloums = sht.getColumns();
        int rows = sht.getRows();

        Cell currentCell = null;
        String addressname = XResources.getString(mContext, ConfigUtils.ADDRESS_USERNAME);
        String jobname = XResources.getString(mContext, ConfigUtils.ADDRESS_JOB);
        String mobilephonename = XResources.getString(mContext, ConfigUtils.ADDRESS_MOBILEPHONE);
        String workphonename = XResources.getString(mContext, ConfigUtils.ADDRESS_WORKPHONE);

        LogUtils.i(XlsUtils.class.getName(), "************************************");
        LogUtils.i(XlsUtils.class.getName(), String.format("row[%d],columns[%d]", rows, coloums));

        // delete address db
        DBUtils.getInstances().deleteAll(mContext, AddressModel.TableName);

        for (int i = 1; i < rows; i++) {
          LogUtils.i(XlsUtils.class.getName(), "####################");
          ContactsBean tmp = new ContactsBean();
          for (int j = 0; j < coloums; j++) {
            Cell topCell = sht.getCell(j, 0);
            String topName = topCell.getContents();
            currentCell = sht.getCell(j, i);
            //                        LogUtils.i(XlsUtils.class.getName(),topName);
            if (topName.equals(addressname)) {
              tmp.setName(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(), String.format("%s:%s", addressname, tmp.getName()));
            } else if (topName.equals(jobname)) {
              tmp.setJob(currentCell.getContents());
              LogUtils.i(XlsUtils.class.getName(), String.format("%s:%s", jobname, tmp.getJob()));
            } else if (topName.equals(mobilephonename)) {
              tmp.setMobilephone(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(),
                  String.format("%s:%s", mobilephonename, tmp.getMobilephone()));
            } else if (topName.equals(workphonename)) {
              tmp.setWorkphone(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(),
                  String.format("%s:%s", workphonename, tmp.getWorkphone()));
            } else {
            }
          }

          // insert to db

          DBUtils.getInstances().InsertAddress(mContext, tmp);

          LogUtils.i(XlsUtils.class.getName(), "####################");
        }
        LogUtils.i(XlsUtils.class.getName(), "************************************");

      } else {
        return -2;
      }

      return 1;
    } catch (Exception e) {
      LogUtils.error(e);
    }

    return 0;
  }
Exemplo n.º 14
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 "";
  }
  @Test
  public void setUp() throws Exception {
    try {

      // ---------------------Maximize Window----------------
      driver.manage().window().maximize();

      // ---------------------Open URL----------------

      System.setProperty(
          "webdriver.ie.driver", "C:\\Users\\nitish_ravindran\\Desktop\\IEDriverServer.exe");
      /*WebDriver driver;
      driver=new InternetExplorerDriver();*/
      driver.get("http://*****:*****@href='/SBCS-WEB/AdvancedSearch.do?moduleSelected=Scenarios']")).getAttribute("title").contains("Advanced Search ");
      			}
      	});

      //-------------------------Logout-----------------------------
      WebElement logoutLinkImg=driver.findElement(By.cssSelector("a[href='/SBCS-WEB/logout.do']"));
      logoutLinkImg.click();*/

    } // -------------------------End of Try block-----------------------------

    // -------------------------Catch Blocks-----------------------------

    catch (NoSuchElementException e) {
      System.out.println("-----------Element not found " + e);
    }
    /*catch(AssertionFailedError e)
    {
    	System.out.println("------------Assertion failed " + e);
    }*/

    /*catch(StaleElementReferenceException e)
    {
    	System.out.println("-----------Stale Element" + e);
    }*/
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      // driver.quit();
    }
  }
Exemplo n.º 16
0
  /**
   * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
   *
   * @param request servlet request
   * @param response servlet response
   * @throws ServletException if a servlet-specific error occurs
   * @throws IOException if an I/O error occurs
   */
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException, BiffException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    HttpSession sesion = ((HttpServletRequest) request).getSession();
    RequestDispatcher dispatcher = null;

    // variables
    String action = "",
        user = "",
        password = "",
        file = "",
        nombres = "",
        apellidos = "",
        estado = "";
    String idperfil_str = "", idusuario_str = "", email = "", telefono = "", direccion = "";
    int idperfil = 0, idusuario = 0, modificaciones = 0;

    // interfaces y objetos
    // usuarioInterface U = new usuarioService();
    User Us = new User();
    userHelper userhelp = new userHelper();

    try {
      action = request.getParameter("action");
    } catch (Exception e) {
      action = "";
    }

    if (action.equals("loginAutenticar")) {
      user = request.getParameter("user");
      password = request.getParameter("passw");

      try {
        Us = userhelp.existeUser(user, password);
      } catch (Exception e) {
        Us = null;
      }
      try {
        if (Us != null) {
          // pe= userhelp.getPerfil(Us.getIdPerfil());
          sesion.setAttribute("IdUsuarioSesion", Us.getId());
          sesion.setAttribute("nickUsuarioSesion", Us.getUsername());
          sesion.setAttribute("nombreUsuarioSesion", Us.getForenames());
          sesion.setAttribute("apellidosUsuarioSesion", Us.getSurnames());
          // #################################################################
          // Actualiza ultimo acceso que tiene el usuario al sistema

          // modificaciones= userhelp.editUser(Us, Us.getId());
          // #################################################################
          dispatcher = request.getRequestDispatcher("client.jsp");
          dispatcher.forward(request, response);

        } else {
          // Guargar log en Bitácora
          dispatcher =
              request.getRequestDispatcher(
                  "index.jsp?resp=Usuario y/o " + "contraseña incorrectos");
          dispatcher.forward(request, response);
        }

        // dispatcher.forward(request, response);
      } catch (Exception e) {

      }
    } else if (action.equals("saliraplicacion")) {
      try {
        sesion.setAttribute("IdUsuarioSesion", null);
        sesion.setAttribute("nickUsuarioSesion", null);
        sesion.setAttribute("nombreUsuarioSesion", null);
        sesion.setAttribute("apellidosUsuarioSesion", null);
        sesion.setAttribute("IdPerfilSesion", null);
        sesion.invalidate();
        response.sendRedirect(response.encodeRedirectURL("index.jsp"));

      } catch (Exception e) {
        out.print("error");
      }

    } else if (action.equalsIgnoreCase("cargarCliente")) {
      try {
        file = request.getParameter("fileField");

        System.out.println("este es:" + file);
        Workbook workbook =
            Workbook.getWorkbook(new File(file)); // Pasamos el excel que vamos a leer
        Sheet sheet = workbook.getSheet(0); // Seleccionamos la hoja que vamos a leer
        String nombre;

        for (int fila = 1; fila < sheet.getRows(); fila++) { // recorremos las filas
          for (int columna = 0;
              columna < sheet.getColumns();
              columna++) { // recorremos las columnas
            nombre = sheet.getCell(columna, fila).getContents(); // setear la celda leida a nombre
            System.out.print(nombre + ""); // imprimir nombre
          }
        }

        dispatcher = request.getRequestDispatcher("client.jsp");
        dispatcher.forward(request, response);

        // dispatcher.forward(request, response);
      } catch (Exception e) {

      }
    } else if (action.equalsIgnoreCase("getusuario")) {

      Us = new User();
      idusuario_str = request.getParameter("iduser");
      idusuario = idusuario_str.equals("") ? 0 : Integer.parseInt(idusuario_str);
      Us = userhelp.getUserByID(idusuario);
      String resp = "", separador = "%";

      //         resp =
      // Us.getUsuario()+separador+Us.getNombres()+separador+Us.getApellidos()+separador+Us.getEmail()+separador+
      //
      // Us.getIdPerfil()+separador+idusuario+separador+Us.getContrasena()+separador+Us.getTelefono()+separador+Us.getDireccion();

      // Us.setFechaCreacion(diaHoraActual);
      // Us.setFechaModificacion(diaHoraActual);
      out.write(resp);
    } else if (action.equalsIgnoreCase("getDataUser")) {

      Us = new User();
      idusuario_str = request.getParameter("iduser");
      idusuario = idusuario_str.equals("") ? 0 : Integer.parseInt(idusuario_str);
      Us = userhelp.getUserByID(idusuario);
      String resp = "", separador = "%";

      //         resp =
      // Us.getUsuario()+separador+Us.getNombres()+separador+Us.getApellidos()+separador+Us.getEmail()+separador+
      //
      // Us.getIdPerfil()+separador+idusuario+separador+Us.getContrasena()+separador+Us.getTelefono()+separador+Us.getDireccion();

      // Us.setFechaCreacion(diaHoraActual);
      // Us.setFechaModificacion(diaHoraActual);
      out.write(resp);
    }
  }
Exemplo n.º 17
0
  public void write2Excel(List<JewelryEntity> list, String excelPath) throws Exception {
    if (list == null && list.isEmpty()) {
      this.log.warn("Product list is empty !");
      return;
    }
    // 1. copy excel head from template
    File templateFile = new ClassPathResource("template/JewelryTemplate.xls").getFile();
    String templateFilePath = templateFile.getAbsolutePath();

    Workbook sourceBook = Workbook.getWorkbook(new File(templateFilePath));
    WritableWorkbook targetBoook = Workbook.createWorkbook(new File(excelPath), sourceBook);
    WritableSheet sheet1 = targetBoook.getSheet(0);
    sheet1.getSettings().setHorizontalFreeze(1); // 设置列冻结
    sheet1.getSettings().setVerticalFreeze(3); // 设置行冻结前2行

    // 2. get columns name
    Sheet st = sourceBook.getSheet(0);
    int totalColumns = st.getColumns(); // for version it's 220
    String[] columnNames = new String[totalColumns];
    for (int i = 0; i < totalColumns; i++) {
      columnNames[i] = st.getCell(i, 2).getContents();
    }

    // 3. write data to excel
    for (int row = 0; row < list.size(); row++) {
      JewelryEntity ent = list.get(row);
      this.log.info(row + "------------------------");
      for (int col = 0; col < totalColumns; col++) {
        Object columnValue = this.getValueByJaveReflect(ent, columnNames[col]);
        if (columnValue == null) {
          if ("generic_keywords".equals(columnNames[col])) {
            String keywords =
                ent.getGenericKeywords1()
                    + " "
                    + ent.getGenericKeywords2()
                    + " "
                    + ent.getGenericKeywords3()
                    + " "
                    + ent.getGenericKeywords4()
                    + " "
                    + ent.getGenericKeywords5()
                    + " "
                    + ent.getNewKeywords();
            int lenght = keywords.length() > 1000 ? 999 : keywords.length() - 1;
            sheet1.addCell(new Label(col, row + 3, keywords.substring(0, lenght)));
          } else {
            sheet1.addCell(new Label(col, row + 3, null));
          }
        } else {
          if (ParentChild.parent.toString().equals(ent.getParentChild())) {
            // some column for parent is no needed
            if (this.omitColumnNames4Parent.contains(columnNames[col])) {
              sheet1.addCell(new Label(col, row + 3, null));
            } else {
              sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
            }
          } else {
            sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
          }
        }
      }
    }

    targetBoook.write();
    targetBoook.close();
    sourceBook.close();
  }
  public static void main(String[] args) throws Exception {
    System.out.println(
        "====================================学术论文Excel整理begin================================================");

    System.out.println("========================获取文件夹中的文件begin============");
    String dirPath = "E:/wuyu/学术论文";
    ReadDirFiles readDirFiles = new ReadDirFiles();
    ArrayList filePathList = readDirFiles.getFileList(dirPath);
    System.out.println(
        "========================获取文件夹中的文件end============文件个数:" + filePathList.size());

    // 写入的Excel
    WritableWorkbook book = null;
    WritableSheet sheet = null;
    String path = "E:\\wuyu\\学术论文2011.xls";
    book = Workbook.createWorkbook(new File(path));
    sheet = book.createSheet("KYLW", 0);
    int rowIndex = 1;

    String filePath = "";
    String filePath2 = "";
    for (int filePathIndex = 0; filePathIndex < filePathList.size(); filePathIndex++) {
      filePath = (String) filePathList.get(filePathIndex);
      filePath2 = filePath.replaceAll("\\\\", "\\/");

      InputStream is = new FileInputStream(filePath2); // File is = new File("D:/test2011.xls");
      Workbook rwb = Workbook.getWorkbook(is);
      Sheet rs = (Sheet) rwb.getSheet(0);
      int columnNum = rs.getColumns();
      int rowNum = rs.getRows();
      System.out.println(
          "第" + filePathIndex + "个 Excel:" + filePath2 + ".中行列为:" + rowNum + ", " + columnNum);
      // columnNum = 45;  //学术论文中的特定值

      Cell cell = null;
      String contentTemp = "";
      for (int i = 6; i <= rowNum - 1; i++) {
        if (rs.getCell(19, i).getContents().trim().equals("2011")
            || rs.getCell(19, i).getContents().trim().equals("2011年")
            || (rs.getCell(19, i).getContents().length() >= 4
                && rs.getCell(19, i).getContents().substring(0, 3).equals("2011"))) {
          for (int j = 0; j <= columnNum - 1; j++) {
            cell = rs.getCell(j, i);
            contentTemp = cell.getContents();
            if (j == 0 && contentTemp.equals("") && contentTemp.trim().equals("")) {
              break; // 如果第一列数据为空,说明数据不存在。“论文”中名称是必须的
            }
            //					System.out.print(j+","+i+":"+contentTemp+" ");

            // 写入Excel
            Label label = new Label(j, rowIndex, contentTemp);
            sheet.addCell(label);
          }
          rowIndex++;
        }
      }
      rwb.close();
    }

    book.write();
    book.close();
    System.out.println("学术论文写入结束");
  }