@Override
  public void showImportObject(String filePath) throws Exception {
    // TODO Auto-generated method stub
    String path = filePath.replace("\\", "\\\\").replace("C:\\\\fakepath", "D:\\kjcoutput");

    Workbook workbook = Workbook.getWorkbook(new File(path));
    Sheet sheet = workbook.getSheet(0);
    int rows = sheet.getRows();
    for (int i = 1; i < rows; i++) {
      Wqwqxkzxq1 wqwqxkzxq1 = new Wqwqxkzxq1();
      wqwqxkzxq1.setXh(sheet.getCell(0, i).getContents());
      wqwqxkzxq1.setZymc(sheet.getCell(1, i).getContents());
      wqwqxkzxq1.setBz(sheet.getCell(2, i).getContents());
      wqwqxkzxq1.setJlnf(sheet.getCell(3, i).getContents());

      // xzxzgzb.setJlnf(sheet.getCell(13, i).getContents());
      wqwqxkzxq1.setUsername(formListTemp.get(0).getUsername());
      wqwqxkzxq1.setGxsj(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
      wqwqxkzxq1.setSubmit(0);
      // xzxzgzb.setUsername();
      wqwqxkzxq1Dao.save(wqwqxkzxq1);
    }

    workbook.close();
  }
Esempio n. 2
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();
    }
  }
Esempio n. 3
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;
 }
  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;
  }
 @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);
 }
  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;
  }
  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
Esempio n. 8
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;
    }
  }
  @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());
  }
  @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());
  }
 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;
 }
Esempio n. 12
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);
   }
 }
Esempio n. 13
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();
    }
  }
Esempio n. 14
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;
 }
 /**
  * 获取第一个第一行单元格内容非空的列号,因为第一行为参数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;
 }
Esempio n. 17
0
  public Object getValueAt(int rowIndex, int columnIndex) {
    if (columnIndex == 0) {
      String value = getAnnotationMap().get(rowIndex);
      if (value == null) {
        return AnnotationCellEditor.NONE;
      } else {
        return value;
      }
    }
    columnIndex--;

    if (reductionSelection == null) {
      return excelSheet.getCell(columnIndex, rowIndex).getContents();
    } else {
      return excelSheet
          .getCell(
              columnIndex + reductionSelection.getColumnIndexStart(),
              rowIndex + reductionSelection.getRowIndexStart())
          .getContents();
    }
  }
  public void loadExcelData2DB(String excelPath, String sheetName) throws Exception {
    Workbook book = Workbook.getWorkbook(new File(excelPath));
    Sheet st = book.getSheet(sheetName);
    int totalColumns = 220;
    int totalRows = st.getRows();

    // 1. get columns name
    String[] columnNames = new String[totalColumns];
    for (int i = 0; i < totalColumns; i++) {
      columnNames[i] = st.getCell(i, 2).getContents();
    }

    String value = null;
    for (int row = 3; row < totalRows; row++) {
      JewelryEntity entity = new JewelryEntity();
      int col;
      for (col = 0; col < totalColumns; col++) {
        Cell cell = st.getCell(col, row);
        if (CellType.EMPTY == cell.getType()) {
          if (0 == col) {
            break;
          } else {
            value = null;
          }
        } else {
          value = cell.getContents();
          if (0 == col && value != null && value.trim().length() < 1) {
            break;
          }
        }
        this.setValueByJaveReflect(entity, columnNames[col], value);
      }

      if (0 != col) {
        System.out.println(entity);
        this.amazonJewelryDao.saveOrUpdate(entity);
      }
    }
    book.close();
  }
Esempio n. 19
0
  /**
   * Récupère la liste des étudiants dans un xls
   *
   * @param uri : l'uri du fichier xls
   * @return la liste remplie
   * @author Foulon Alain
   */
  public static GestionEtudiants getValsFromXls(String uri) throws BiffException, IOException {
    GestionEtudiants liste = new GestionEtudiants();

    try {
      // Récupère le fichier
      File fich = new File(uri);
      WorkbookSettings workSet = new WorkbookSettings(); // config de fichier .xls
      workSet.setLocale(new Locale("fr", "FR")); // vars locales
      Workbook workbook =
          Workbook.getWorkbook(fich, workSet); // ouvre un fichier existant en fct des settings
      Sheet feuille = workbook.getSheet(0); // ouvre la première feuille du fichier

      Cell cellNom = feuille.findCell("NOM"); // trouve la cellule "NOM" dans la feuille
      // soit : REGEX sur "nom, Nom, noms, NOM, NOMS..."
      // soit : fct qui teste les possibilités; (note: la CELL est null si non trouvée)

      int colNom = cellNom.getColumn(); // colonne noms
      int colPrenom = colNom + 1; // colonne prénoms
      int firstRow = cellNom.getRow() + 1; // premiere ligne
      int lastRow = feuille.getRows(); // derniere ligne

      // boucle de remplissage
      for (int i = firstRow, j = 0; i < lastRow; i++) {
        // je récupère les infors
        String nom = feuille.getCell(colNom, i).getContents();
        String prenom = feuille.getCell(colPrenom, i).getContents();

        // j'ajoute l'étudiant dans la liste
        j++;
        liste.add(new Etudiant(j, nom, prenom));
      }
    } catch (IOException ioe) {
      System.out.println("Erreur : " + ioe); // Erreur lors de l'ouverture du fichier Excel
    }

    // je retourne la liste finie
    return liste;
  }
Esempio n. 20
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;
 }
Esempio n. 21
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;
 }
Esempio n. 22
0
  public ExcelInstance() {
    // Initialize the template file
    try {
      template = Workbook.getWorkbook(new File(""));
      templateSheet = template.getSheet(0);
    } catch (IOException | BiffException e) {
      JOptionPane.showMessageDialog(
          null,
          "Excel template file is either malformed or missing, please verify its integrity",
          "Error",
          JOptionPane.ERROR_MESSAGE);
    }

    // Init
    String currentCell;
    for (int i = 0; i < 100; i++) {
      for (int j = 0; j < 100; j++) {
        currentCell = templateSheet.getCell(i, j).getContents();
        if (currentCell.matches("\\${\\d{1,}}")) { // TODO: Proper regex
        }
      }
    }
  }
Esempio n. 23
0
  public String[][] getTableArray() throws Exception {
    String[][] tabArray = null;

    Workbook workbook = Workbook.getWorkbook(new File(inputFile));
    Sheet sheet = workbook.getSheet(sheetName);
    int startRow, startCol, endRow, endCol, ci, cj;
    Cell tableStart = sheet.findCell(tableName);
    startRow = tableStart.getRow();
    startCol = tableStart.getColumn();
    Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 400, 64000, false);
    // Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow+1, 100, 64000,  false);

    endRow = tableEnd.getRow();
    endCol = tableEnd.getColumn();
    System.out.println(
        "startRow="
            + startRow
            + ", endRow="
            + endRow
            + ", "
            + "startCol="
            + startCol
            + ", endCol="
            + endCol);
    tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
    ci = 0;

    for (int i = startRow + 1; i < endRow; i++, ci++) {
      cj = 0;
      for (int j = startCol + 1; j < endCol; j++, cj++) {
        tabArray[ci][cj] = sheet.getCell(j, i).getContents();
      }
    }

    return (tabArray);
  }
  /**
   * 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;
  }
 /**
  * 获取数据表中某个属性的信息增益
  *
  * @param pt 需要计算信息增益的属性
  * @param t 判断属性,也即属性pt分类时的依据属性。
  * @return 属性pt的信息增益
  */
 public String[] getInformationGain(String pt, String t) {
   String[] gain_and_support = new String[3];
   String gain = null;
   int cla = 0; // 代表分类数目
   BufferedWriter writer = null;
   int part = 0;
   int target = 0;
   String[] attr = PublicData.getAttr();
   for (int p = 0; p < attr.length; p++) {
     if (attr[p].equals(pt)) {
       part = p;
       System.out.println("分类属性是:" + attr[p]);
     }
     if (attr[p].equals(t)) {
       target = p;
       System.out.println("所选属性是:" + attr[p]);
     }
   }
   /** 所有参与计算的变量必须都放到方法内部来,避免下次计算时重复 */
   float yes_to_attr = 0; // 属于当前类的数目
   float no_to_attr = 0;
   BigDecimal info = new BigDecimal(0); // 计算元组分类所需的期望信息
   BigDecimal infoD = new BigDecimal(0); // 存储当前列属性的信息增益
   float sum = 0; // 存储总行数
   java.text.DecimalFormat DataFormat =
       (java.text.DecimalFormat) java.text.DecimalFormat.getInstance();
   // 为了得到整数相处保留三位小数的结果。注意,经过这样的处理后,计算得到的结果是String类型
   DataFormat.applyPattern("##.###"); // 为了得到整数相处保留三位小数的结果
   value = new int[50][2];
   // 以下是用来读取Excel文件中的单元值
   try {
     Workbook book = Workbook.getWorkbook(new File(PublicData.getFile()));
     Sheet sheet = book.getSheet(0);
     for (int i = 1; i < sheet.getRows(); i++) // 默认第一行为属性辨别行,不参与计算
     {
       Cell cell = sheet.getCell(target, i); // 获取当前属性所在列
       Cell cell2 = sheet.getCell(part, i); // 获取结果列,即训练元组最终分类类别那一列
       String c2 = cell2.getContents().replaceAll("\"", "").trim();
       String result = cell.getContents().replaceAll("\"", " ").trim();
       if (list.contains(result) == false && result != null) // 将所选属性加入到list中
       {
         list.add(result);
         cla = list.indexOf(result);
       } else if (list.contains(result) == true)
         cla = list.indexOf(result); // 以当前属性的类别(String类型)所在list中的索引为value的第一维
       if (c2.equals("y") || c2.equals("yes")) // 以二维数组value的第二维的第一列来存储属于最终类的总数
       {
         value[cla][0]++;
         yes_to_attr++; // 全部数据中符合条件占比
       } else // if(c2.equals("n")||c2.equals("no"))
       {
         value[cla][1]++; // 第二列来存储不属于最终列的总数
         no_to_attr++; // 全部数据中不符合数量
       }
     }
     // writer.close();
     book.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   // 以下是用来计算信息增益的程序段
   System.out.println("属性长度" + list.size());
   sum = yes_to_attr + no_to_attr;
   BigDecimal ysum = mlog.getDivide(yes_to_attr, sum);
   BigDecimal nsum = mlog.getDivide(no_to_attr, sum);
   info = (nsum.multiply(mlog.getLog(nsum))).abs().add((ysum.multiply(mlog.getLog(ysum))).abs());
   String[] at = new String[list.size()];
   System.out.println("属性长度-------------" + list.size());
   for (int p = 0; p < list.size(); p++) {
     if (!(list.get(p).equals("") || list.get(p).equals(null))) {
       at[p] = list.get(p);
     }
     int a = value[p][0];
     int b = value[p][1];
     System.out.println("|" + list.get(p) + "| " + a + " | " + b + " |");
     int c = a + b;
     BigDecimal asum = mlog.getDivide(a, c);
     BigDecimal bsum = mlog.getDivide(b, c); // asum是肯定总量,bsum是否定总量
     BigDecimal csum = mlog.getDivide(c, sum);
     if (a == 0 && b != 0)
       infoD = infoD.add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs());
     else if (b == 0 && a != 0)
       infoD = infoD.add((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs());
     else if (a != 0 && b != 0)
       infoD =
           infoD.add(
               ((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs())
                   .add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs()));
     else if (a == 0 && b == 0) infoD = infoD.add(new BigDecimal(0));
   }
   for (int i = 0; i < at.length; i++) {
     if (i == 0 && at[i] != null) {
       attr_part = at[i];
     } else {
       if (at[i] != null) {
         attr_part = attr_part + "," + at[i];
       }
     }
   }
   System.out.println("attr_part长度是:" + attr_part.length());
   try {
     String f = HEAD + t + LAST;
     System.out.println("正在尝试写入文件-------" + f);
     BufferedWriter w = new BufferedWriter(new FileWriter(new File(f)));
     w.append(attr_part.trim()); // 去掉双引号
     w.flush();
     w.close();
   } catch (IOException e) {
     e.printStackTrace();
   }
   attr_part = "";
   gain = info.subtract(infoD).toString();
   System.out.println("当前属性信息增益:" + infoD);
   list.clear();
   // 清空list表,以便下次存储时不会由于前面的插入元素受干扰
   gain_and_support[0] = gain;
   gain_and_support[1] = yes_to_attr + "";
   gain_and_support[2] = no_to_attr - 1 + "";
   return gain_and_support;
 }
Esempio n. 26
0
  public void Read() {
    try {

      Sheet sh = Functions.readExcel("capstone.xlt");
      Sheet sh2 = Functions.readExcel("liste.xlt");

      String word;

      for (int k = 0; k < 81; k++) {

        hash.put(sh2.getCell(1, k).getContents().toString(), 0);
      }

      for (int i = 3; i < 1457; i++) {

        word =
            sh.getCell(3, i).getContents().toString()
                + sh.getCell(3, i + 1).getContents().toString()
                + sh.getCell(3, i + 2).getContents().toString()
                + sh.getCell(3, i + 3).getContents().toString();
        hash.put(word, hash.get(word) + 1);
      }

      String q_1 = null;
      String q_2 = null;
      String q_3 = null;
      String q_4 = null;

      for (int b = 0; b < 3; b++) {

        q_1 = null;

        if (b == 0) q_1 = "G";
        else if (b == 1) q_1 = "Y";
        else if (b == 2) q_1 = "K";

        for (int c = 0; c < 3; c++) {

          q_2 = null;
          q_2 = q_1;

          if (c == 0) q_2 = q_1 + "G";
          else if (c == 1) q_2 = q_1 + "Y";
          else if (c == 2) q_2 = q_1 + "K";

          for (int d = 0; d < 3; d++) {

            q_3 = null;
            q_3 = q_2;

            if (d == 0) q_3 = q_2 + "G";
            else if (d == 1) q_3 = q_2 + "Y";
            else if (d == 2) q_3 = q_2 + "K";

            for (int e = 0; e < 3; e++) {

              q_4 = null;
              q_4 = q_3;

              if (e == 0) q_4 = q_3 + "G";
              else if (e == 1) q_4 = q_3 + "Y";
              else if (e == 2) q_4 = q_3 + "K";

              alist.put(
                  q_4,
                  (float) hash.get(q_4)
                      / (hash.get(q_3 + "G") + hash.get(q_3 + "Y") + hash.get(q_3 + "K")));
            }
          }
        }
      }

      for (int k = 0; k < 81; k++) {
        System.out.println(
            (sh2.getCell(1, k).getContents().toString()
                + " is :"
                + alist.get(sh2.getCell(1, k).getContents())));
        Weather_GUI.txt.append(
            (sh2.getCell(1, k).getContents().toString()
                    + " is :"
                    + alist.get(sh2.getCell(1, k).getContents()))
                + "\n");
      }

    } catch (BiffException | IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Esempio n. 27
0
  private ActionForward impScore(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response) {
    try {
      request.setCharacterEncoding("utf-8");
      response.setCharacterEncoding("utf-8");
      String path = request.getContextPath();
      String rootPath = request.getSession().getServletContext().getRealPath("/");
      // 先建文件夹
      String folderPath = rootPath + "export/";
      String str_Path = folderPath.toString().replace("\\", "/");
      // 上传文件
      SmartUpload mySmartUpload = new SmartUpload();
      // 设置允许上传的文件类型
      // mySmartUpload.setAllowedFilesList("xls");
      ServletConfig config = this.getServlet().getServletConfig();
      mySmartUpload.initialize(config, request, response);
      mySmartUpload.upload();

      String CURDATE = GetRecentDate.getRecentDate("yyyyMMddHHmmssS");
      String FILE_PATH = "";
      for (int i = 0; i < mySmartUpload.getFiles().getCount(); i++) {
        com.jspsmart.upload.File file = mySmartUpload.getFiles().getFile(i);
        if (!file.getFileName().equals("")) {
          FILE_PATH = str_Path + CURDATE + "_" + i + "." + file.getFileExt();
          file.saveAs(FILE_PATH);
        }
      }

      ScoreManagementDAO dao = new ScoreManagementDAO();
      InputStream stream = new FileInputStream(FILE_PATH);
      Workbook rwb = Workbook.getWorkbook(stream);
      Sheet sheet = rwb.getSheet(0);
      for (int i = 1; i < sheet.getRows(); i++) {
        String SCORE_YEAR = sheet.getCell(0, i).getContents(); // 年份
        if (SCORE_YEAR.equals("")) break;
        String SEMESTER = sheet.getCell(1, i).getContents(); // 学期
        String SCORE_GRADE = sheet.getCell(2, i).getContents(); // 班级
        String SCORE_CLASS = sheet.getCell(3, i).getContents(); // 班级
        String SCORE_NO = sheet.getCell(4, i).getContents(); // 学号
        String STU_NAME = sheet.getCell(5, i).getContents(); // 姓名
        String STU_SEX = sheet.getCell(6, i).getContents(); // 性别
        String AAS = sheet.getCell(7, i).getContents(); // 文理科
        String KEMU = sheet.getCell(8, i).getContents(); // 考试名称
        String CHINESE = sheet.getCell(9, i).getContents(); // 语文
        if (CHINESE.equals("")) CHINESE = "0";
        String MATCH = sheet.getCell(10, i).getContents(); // 数学
        if (MATCH.equals("")) MATCH = "0";
        String ENGLISH = sheet.getCell(12, i).getContents(); // 英语
        if (ENGLISH.equals("")) ENGLISH = "0";
        String PHYSICAL = sheet.getCell(13, i).getContents(); // 物理
        if (PHYSICAL.equals("")) PHYSICAL = "0";
        String CHEMISTRY = sheet.getCell(14, i).getContents(); // 化学
        if (CHEMISTRY.equals("")) CHEMISTRY = "0";
        String BIOLOGICAL = sheet.getCell(15, i).getContents(); // 生物
        if (BIOLOGICAL.equals("")) BIOLOGICAL = "0";
        String HISTORY = sheet.getCell(16, i).getContents(); // 历史
        if (HISTORY.equals("")) HISTORY = "0";
        String POLITY = sheet.getCell(17, i).getContents(); // 政治
        if (POLITY.equals("")) POLITY = "0";
        String GEOGRAPHY = sheet.getCell(18, i).getContents(); // 地理
        if (GEOGRAPHY.equals("")) GEOGRAPHY = "0";
        float total =
            Float.valueOf(CHINESE)
                + Float.valueOf(MATCH)
                + Float.valueOf(ENGLISH)
                + Float.valueOf(PHYSICAL)
                + Float.valueOf(CHEMISTRY)
                + Float.valueOf(BIOLOGICAL)
                + Float.valueOf(HISTORY)
                + Float.valueOf(POLITY)
                + Float.valueOf(GEOGRAPHY);

        HashMap map = new HashMap();
        map.put("SCORE_YEAR", SCORE_YEAR);
        map.put("SEMESTER", SEMESTER);
        map.put("SCORE_GRADE", SCORE_GRADE);
        map.put("SCORE_CLASS", SCORE_CLASS);
        map.put("SCORE_NO", SCORE_NO);
        map.put("STU_NAME", STU_NAME);
        map.put("STU_SEX", STU_SEX);
        map.put("AAS", AAS);
        map.put("KEMU", KEMU);
        map.put("CHINESE", CHINESE);
        map.put("MATCH", MATCH);
        map.put("ENGLISH", ENGLISH);
        map.put("PHYSICAL", PHYSICAL);
        map.put("CHEMISTRY", CHEMISTRY);
        map.put("BIOLOGICAL", BIOLOGICAL);
        map.put("HISTORY", HISTORY);
        map.put("POLITY", POLITY);
        map.put("GEOGRAPHY", GEOGRAPHY);
        map.put("TOTAL_SCORE", total);
        dao.insertScore(map);
      }
      rwb.close();
      stream.close();
      response.getWriter().write("1");
      response.getWriter().close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }
Esempio n. 28
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) {
      }
    }
  }
Esempio n. 29
0
 /**
  * 导入Excel格式评奖信息到数据库
  *
  * @param mapping
  * @param form
  * @param request
  * @param response
  * @return
  */
 public ActionForward importToDatabase(
     ActionMapping mapping,
     ActionForm form,
     HttpServletRequest request,
     HttpServletResponse response) {
   DynaActionForm fileForm = (DynaActionForm) form;
   FormFile formFile = (FormFile) fileForm.get("objExcel");
   try {
     String jxjlx = request.getParameter("jxjlx");
     InputStream excelInput = formFile.getInputStream();
     Workbook workbook = Workbook.getWorkbook(excelInput);
     Sheet sheet = workbook.getSheet(0);
     int rows = sheet.getRows();
     Xs0708 xs0708 = null;
     List xs0708List = new ArrayList();
     for (int i = 2; i < rows; i++) {
       String xh = sheet.getCell(1, i).getContents().trim();
       String nd = sheet.getCell(0, i).getContents().trim();
       String jx = sheet.getCell(2, i).getContents().trim();
       String yhzh = sheet.getCell(3, i).getContents().trim();
       String jlje = sheet.getCell(4, i).getContents().trim();
       String bz = sheet.getCell(5, i).getContents().trim();
       Xs0101 xs0101 = this.getStzzServiceImpl().findXs0101ByXh(xh);
       if (xs0101 == null) {
         this.writeJsMessage(
             response,
             "alert('导入失败,第"
                 + (i + 1)
                 + "行学号:"
                 + xh
                 + ",在系统中不存在');window.parent.returnValue='ok';window.parent.close();");
         return null;
       }
       xs0708 = new Xs0708();
       xs0708.setXs0101(xs0101);
       xs0708.setJlnd(nd);
       xs0708.setJx(jx);
       xs0708.setYhzh(yhzh);
       xs0708.setJlje(new Long(jlje));
       xs0708.setBz(bz);
       xs0708.setJxjlx(jxjlx);
       xs0708List.add(xs0708);
     }
     workbook.close();
     this.getStzzServiceImpl().batchSaveXs0708(xs0708List, request);
     this.writeJsMessage(
         response,
         "alert('数据导入成功!共导入"
             + xs0708List.size()
             + "条数据');window.parent.returnValue='ok';window.parent.close();");
   } catch (NumberFormatException e) {
     this.writeJsMessage(
         response,
         "alert('导入失败,\"奖励金额\"列出现非法数据');window.parent.returnValue='ok';window.parent.close();");
   } catch (FileNotFoundException e) {
     this.writeJsMessage(
         response, "alert('找不到文件!');window.parent.returnValue='ok';window.parent.close();");
   } catch (IOException e) {
     this.writeJsMessage(
         response, "alert('输入输出错误!');window.parent.returnValue='ok';window.parent.close();");
   } catch (BiffException e) {
     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;
 }
Esempio n. 30
0
  /**
   * 读取奖惩管理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;
  }