Example #1
0
 /**
  * @param rulename
  * @param support 0: no 1: yes 2: all
  * @return
  */
 public LinkedList<String> getPCREbyRuleset(Workbook book, String rulename, int support) {
   LinkedList<String> lpcre = new LinkedList<String>();
   Sheet sheet = book.getSheet(rulename);
   if (sheet == null) {
     return null;
   }
   // begin to read
   int index = 1;
   while (index < sheet.getRows()) {
     String pStatus = sheet.getCell(3, index).getContents();
     String pcre = sheet.getCell(4, index).getContents();
     if (support == 1) {
       if (pStatus.compareToIgnoreCase("1") == 0) {
         lpcre.add(pcre);
       }
     } else if (support == 0) {
       if (pStatus.compareToIgnoreCase("0") == 0) {
         lpcre.add(pcre);
       }
     } else {
       lpcre.add(pcre);
     }
     index++;
   }
   return lpcre;
 }
  private void jButton7ActionPerformed(
      java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton7ActionPerformed

    try {
      if (lbl_ruta.getText().equals("")) {
        JOptionPane.showMessageDialog(null, "Seleccione Archivo a importar");
        return;
      }

      Workbook wb = Workbook.getWorkbook(arch);
      Sheet sheet = wb.getSheet(0);
      int rows = sheet.getRows();

      String nrodoc = "";
      DefaultListModel l = new DefaultListModel();
      for (int i = 0; i < rows; i++) {
        nrodoc = sheet.getCell(0, i).getContents().trim();
        l.addElement(nrodoc);
      }
      jList2.setModel(l);
      lbln_regcar.setText(l.size() + "");
    } catch (Exception e) {
      JOptionPane.showMessageDialog(null, e);
    }
  } // GEN-LAST:event_jButton7ActionPerformed
  @Test
  public void testJExcel() throws Exception {
    AbstractJExcelView excelView =
        new UnixSafeAbstractJExcelView() {
          @Override
          protected void buildExcelDocument(
              Map<String, Object> model,
              WritableWorkbook wb,
              HttpServletRequest request,
              HttpServletResponse response)
              throws Exception {
            WritableSheet sheet = wb.createSheet("Test Sheet", 0);
            // test all possible permutation of row or column not existing
            sheet.addCell(new Label(2, 4, "Test Value"));
            sheet.addCell(new Label(2, 3, "Test Value"));
            sheet.addCell(new Label(3, 4, "Test Value"));
            sheet.addCell(new Label(2, 4, "Test Value"));
          }
        };

    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    assertEquals("Test Sheet", wb.getSheet(0).getName());
    Sheet sheet = wb.getSheet("Test Sheet");
    Cell cell = sheet.getCell(2, 4);
    assertEquals("Test Value", cell.getContents());
  }
  @Test
  public void testJExcelWithTemplateAndLanguage() throws Exception {
    request.setAttribute(
        DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("de", ""));

    AbstractJExcelView excelView =
        new UnixSafeAbstractJExcelView() {
          @Override
          protected void buildExcelDocument(
              Map<String, Object> model,
              WritableWorkbook wb,
              HttpServletRequest request,
              HttpServletResponse response)
              throws Exception {
            WritableSheet sheet = wb.getSheet("Sheet1");
            // test all possible permutation of row or column not existing
            sheet.addCell(new Label(2, 4, "Test Value"));
            sheet.addCell(new Label(2, 3, "Test Value"));
            sheet.addCell(new Label(3, 4, "Test Value"));
            sheet.addCell(new Label(2, 4, "Test Value"));
          }
        };

    excelView.setApplicationContext(webAppCtx);
    excelView.setUrl("template");
    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    Sheet sheet = wb.getSheet("Sheet1");
    Cell cell = sheet.getCell(0, 0);
    assertEquals("Test Template auf Deutsch", cell.getContents());
  }
Example #5
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();
    }
  }
 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;
 }
  /**
   * 读Excel
   *
   * @param pathname
   */
  public static LinkedList<HashMap<String, String>> readExcel(String pathname) {
    LinkedList<HashMap<String, String>> list = new LinkedList<HashMap<String, String>>();
    File file = new File(pathname);
    String uri = "f:\\xml\\map\\edge.xml";
    Workbook wb = null;
    try {
      wb = Workbook.getWorkbook(file);
      Sheet sheet = wb.getSheet(0);
      int rows = sheet.getRows();
      // ThreadPoolExecutorTest threadPool = new
      // ThreadPoolExecutorTest(queue);
      for (int i = 0; i < rows; i++) {
        HashMap<String, String> map = new HashMap<String, String>();
        // System.out.println("第"+(i+1)+"条数据正在执行");
        Cell[] cols = sheet.getRow(i);
        String sender = cols[0].getContents();
        String nodeID = cols[1].getContents();
        String wayID = cols[2].getContents();
        String depart = cols[3].getContents();

        String edgeID = SaxService.getWayIdFromNodeXML(uri, nodeID, wayID);
        map.put("sender", sender);
        map.put("nodeID", nodeID);
        map.put("wayID", wayID);
        map.put("edgeID", edgeID);
        map.put("depart", depart);
        list.add(map);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }
  private List<String> getInvestigationNameFromExcel(File excelFile) throws Exception {

    File tmpInvestigation =
        new File(System.getProperty("java.io.tmpdir") + File.separator + "tmpInvestigation.txt");
    if (tmpInvestigation.exists()) {
      boolean deleteSuccess = tmpInvestigation.delete();
      if (!deleteSuccess) {
        throw new Exception("Deletion of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
      }
    }
    boolean createSuccess = tmpInvestigation.createNewFile();
    if (!createSuccess) {
      throw new Exception("Creation of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
    }

    Workbook workbook = Workbook.getWorkbook(excelFile);
    for (Sheet sheet : workbook.getSheets()) {
      if (sheet.getName().toLowerCase().equals("investigation")) {
        writeSheetToFile(sheet, tmpInvestigation);
      }
    }

    List<String> names = XgapCommonImport.getInvestigationNameFromFile(tmpInvestigation);

    return names;
  }
Example #9
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;
 }
Example #10
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;
    }
  }
Example #11
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");
    }
  }
Example #12
0
  /**
   * @throws IOException
   * @throws FileNotFoundException
   * @throws BiffException
   * @see
   */
  public int getmaxrownumber(String filename, String sheetname)
      throws BiffException, FileNotFoundException, IOException {
    int rownumber = 0;
    Workbook wb = Workbook.getWorkbook(new FileInputStream(filename));
    Sheet sheet = wb.getSheet(sheetname);
    rownumber = sheet.getRows();

    return rownumber;
  }
Example #13
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;
 }
 @Test
 public void verifyLogin() throws Exception {
   fi = new FileInputStream("D:\\Workspace10AM\\Selenium_Autotest\\OR\\LoginOR.xls");
   w = Workbook.getWorkbook(fi);
   s = w.getSheet("Login");
   driver.get("http://mail.in.com");
   driver.findElement(By.id(s.getCell(0, 1).getContents())).sendKeys("jan30selenium");
   driver.findElement(By.id(s.getCell(1, 1).getContents())).sendKeys("selenium");
   driver.findElement(By.cssSelector(s.getCell(2, 1).getContents())).click();
   Thread.sleep(3000);
 }
 /**
  * 获取第一个第一行单元格内容非空的列号,因为第一行为参数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;
 }
 /**
  * 获取第一个第一列单元格内容非空的行号,因为第一列为caseId,不能为空
  *
  * @param mSheet
  * @return
  */
 private static int getRowCount(Sheet mSheet) {
   int count = 0;
   int row = mSheet.getRows();
   for (int i = 0; i < row; i++) {
     String content = mSheet.getCell(0, i).getContents();
     if (StringTools.isEmpty(content)) {
       break;
     }
     ++count;
   }
   return count;
 }
Example #17
0
  /** Writes out the workbook data as XML, without formatting information */
  private void writeXML() throws IOException {
    try {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      bw.write("<?xml version=\"1.0\" ?>");
      bw.newLine();
      bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
      bw.newLine();
      bw.newLine();
      bw.write("<workbook>");
      bw.newLine();
      for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
        Sheet s = workbook.getSheet(sheet);

        bw.write("  <sheet>");
        bw.newLine();
        bw.write("    <name><![CDATA[" + s.getName() + "]]></name>");
        bw.newLine();

        Cell[] row = null;

        for (int i = 0; i < s.getRows(); i++) {
          bw.write("    <row number=\"" + i + "\">");
          bw.newLine();
          row = s.getRow(i);

          for (int j = 0; j < row.length; j++) {
            if (row[j].getType() != CellType.EMPTY) {
              bw.write("      <col number=\"" + j + "\">");
              bw.write("<![CDATA[" + row[j].getContents() + "]]>");
              bw.write("</col>");
              bw.newLine();
            }
          }
          bw.write("    </row>");
          bw.newLine();
        }
        bw.write("  </sheet>");
        bw.newLine();
      }

      bw.write("</workbook>");
      bw.newLine();

      bw.flush();
      bw.close();
    } catch (UnsupportedEncodingException e) {
      System.err.println(e.toString());
    }
  }
Example #18
0
  /**
   * Constructor
   *
   * @param w The workbook to interrogate
   * @param out The output stream to which the CSV values are written
   * @param encoding The encoding used by the output stream. Null or unrecognized values cause the
   *     encoding to default to UTF8
   * @param hide Suppresses hidden cells
   * @exception java.io.IOException
   */
  public CSV(Workbook w, OutputStream out, String encoding, boolean hide) throws IOException {
    if (encoding == null || !encoding.equals("UnicodeBig")) {
      encoding = "UTF8";
    }

    try {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
        Sheet s = w.getSheet(sheet);

        if (!(hide && s.getSettings().isHidden())) {
          bw.write("*** " + s.getName() + " ****");
          bw.newLine();

          Cell[] row = null;

          for (int i = 0; i < s.getRows(); i++) {
            row = s.getRow(i);

            if (row.length > 0) {
              if (!(hide && row[0].isHidden())) {
                bw.write(row[0].getContents());
                // Java 1.4 code to handle embedded commas
                // bw.write("\"" +
                // row[0].getContents().replaceAll("\"","\"\"")
                // + "\"");
              }

              for (int j = 1; j < row.length; j++) {
                bw.write(',');
                if (!(hide && row[j].isHidden())) {
                  bw.write(row[j].getContents());
                  // Java 1.4 code to handle embedded quotes
                  // bw.write("\"" +
                  // row[j].getContents().replaceAll("\"","\"\"")
                  // + "\"");
                }
              }
            }
            bw.newLine();
          }
        }
      }
      bw.flush();
      bw.close();
    } catch (UnsupportedEncodingException e) {
      System.err.println(e.toString());
    }
  }
Example #19
0
 /**
  * 取得当前sheet的sheet名
  *
  * @return
  */
 public String getSheetName() {
   if (readOnlyWBook == true) {
     return currentSheet.getName();
   } else {
     return wrCurrentSheet.getName();
   }
 }
Example #20
0
 /**
  * 取得cell
  *
  * @param colNum 列号
  * @param rowNum 行号
  * @return
  */
 public Cell getCell(int colNum, int rowNum) {
   if (readOnlyWBook == true) {
     return currentSheet.getCell(colNum, rowNum);
   } else {
     return wrCurrentSheet.getCell(colNum, rowNum);
   }
 }
Example #21
0
 /**
  * 取得一行的cell
  *
  * @param rowNum 行号
  * @return
  */
 public Cell[] getRowCells(int rowNum) {
   if (readOnlyWBook == true) {
     return currentSheet.getRow(rowNum);
   } else {
     return wrCurrentSheet.getRow(rowNum);
   }
 }
Example #22
0
 /**
  * 取得一列的cell
  *
  * @param rowNum 行号
  * @return 一列的cell
  */
 public Cell[] getColCells(int colNum) {
   if (readOnlyWBook == true) {
     return currentSheet.getColumn(colNum);
   } else {
     return wrCurrentSheet.getColumn(colNum);
   }
 }
Example #23
0
 /**
  * 读取Excel数据
  *
  * @param pBegin 从第几行开始读数据<br>
  *     <b>注意下标索引从0开始的哦!
  * @return 以List<BaseDTO>形式返回数据
  * @throws BiffException
  * @throws IOException
  */
 public List read(int pBegin) throws BiffException, IOException {
   List list = new ArrayList();
   Workbook workbook = Workbook.getWorkbook(getIs());
   Sheet sheet = workbook.getSheet(0);
   int rows = sheet.getRows();
   for (int i = pBegin; i < rows; i++) {
     Dto rowDto = new BaseDto();
     Cell[] cells = sheet.getRow(i);
     for (int j = 0; j < cells.length; j++) {
       String key = getMetaData().trim().split(",")[j];
       if (G4Utils.isNotEmpty(key)) rowDto.put(key, cells[j].getContents());
     }
     list.add(rowDto);
   }
   return list;
 }
Example #24
0
 /**
  * 取得列数
  *
  * @return 列数
  */
 public int getCols() {
   if (readOnlyWBook == true) {
     return currentSheet.getColumns();
   } else {
     return wrCurrentSheet.getColumns();
   }
 }
Example #25
0
 public int getRowCount() {
   if (reductionSelection == null) {
     return excelSheet.getRows();
   } else {
     return reductionSelection.getRowIndexEnd() - reductionSelection.getRowIndexStart() + 1;
   }
 }
Example #26
0
  public Object[] next() {

    Cell[] c = sheet.getRow(this.currentRowNo);
    Map<String, String> data = new HashMap<String, String>();
    // List<String> list = new ArrayList<String>();

    for (int i = 0; i < this.columnNum; i++) {

      String temp = "";

      try {
        temp = c[i].getContents().toString();
      } catch (ArrayIndexOutOfBoundsException ex) {
        temp = "";
      }

      // if(temp != null&& !temp.equals(""))
      // list.add(temp);
      data.put(this.columnnName[i], temp);
    }
    Object object[] = new Object[1];
    object[0] = data;
    this.currentRowNo++;
    return object;
  }
Example #27
0
 /**
  * 得到需要爬取的百度百科词条
  *
  * @throws Exception
  */
 public static ArrayList<String> getWord() throws Exception {
   String excelPath = "imagedata/baike/HTML/C_termlist.xls";
   Workbook wb = Workbook.getWorkbook(new File(excelPath));
   Sheet sheet = wb.getSheet(0);
   int row = sheet.getRows();
   System.out.println("百科词条数:" + row);
   ArrayList<String> failword = new ArrayList<String>();
   for (int i = 0; i < row; i++) {
     String word = sheet.getCell(1, i).getContents();
     System.out.println("\n正在爬取: " + word);
     int flag = crawlerImage(word);
     if (flag == 0) {
       failword.add(word);
     }
   }
   return failword;
 }
Example #28
0
  private void initialise() {

    Workbook workbook = WorkbookSingleton.getWorkbook(workbookFileName);
    Sheet currentSheet = workbook.getSheet(ColumnIndexes.FAILURECLASS__SHEETNO);

    Cell[] row;
    for (int i = 1; i < currentSheet.getRows(); i++) {
      row = currentSheet.getRow(i);

      if (row.length > 0) {

        createFailure(
            Integer.parseInt(row[ColumnIndexes.FAILURECLASS_FAILURECLASS_COLNO].getContents()),
            row[ColumnIndexes.FAILURECLASS_DESCRIPTION_COLNO].getContents());
      }
    }
  }
Example #29
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;
 }
Example #30
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();
    }
  }