Ejemplo n.º 1
0
  /**
   * This method is used to display the Excel content to command line.
   *
   * @param xlsPath
   */
  @SuppressWarnings("unchecked")
  public void displayFromExcel(InputStream inputStream) {

    POIFSFileSystem fileSystem = null;

    try {
      fileSystem = new POIFSFileSystem(inputStream);

      Map headerTable = null;
      HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
      HSSFSheet sheet = workBook.getSheetAt(0);
      System.out.println("LastRowNum:" + sheet.getLastRowNum());

      Iterator<HSSFRow> rows = sheet.rowIterator();

      while (rows.hasNext()) {
        HSSFRow row = rows.next();

        // display row number in the console.
        System.out.println("Row No.: " + row.getRowNum());

        Contact contact = null;
        if (row.getRowNum() == 0) {
          headerTable = headerTable(row);
        } else {
          contact = processRow(row, headerTable);
          if (contact != null) contactManager.saveContact(contact);
        }
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
Ejemplo n.º 2
0
  @Override
  public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
      throws UploadActionException {
    StringBuffer response = new StringBuffer();

    for (FileItem item : sessionFiles) {
      if (false == item.isFormField()) {

        try {

          if (item.getName().endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            HSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              HSSFRow row = (HSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }

          } else if (item.getName().endsWith(".xlsx")) {
            // POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            XSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              XSSFRow row = (XSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }
          }

        } catch (Exception e) {
          throw new UploadActionException(e);
        }
      }
    }
    // / Remove files from session because we have a copy of them
    removeSessionFileItems(request);
    // / Send your customized message to the client.
    return response.toString();
  }
    @DataProvider(name = "placeOrderCredentials")
    public Iterator<Object[]> readFromExcelIterator() throws FileNotFoundException, IOException {
      //	prop.load(getClass().getResourceAsStream("AutomationEnv.properties"));
      // String sourceXlsFileName=(String)prop.get("signUpAndOrderCard");
      String fileName =
          "C:\\Users\\WINQA\\workspace\\NewHkAutomationSuite\\Excel\\signUpandPlace.xls";
      int sheetNo = 0;
      ArrayList<Object[]> excelDataArray = new ArrayList<Object[]>();
      int cnt = 0;
      try {

        InputStream input = new BufferedInputStream(new FileInputStream(fileName));

        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext()) {
          int cellCount = 0;
          int flagStop = 0;
          HSSFRow row = (HSSFRow) rows.next();
          System.out.println("\n");
          Iterator cells = row.cellIterator();

          List<String> readExcelData = new LinkedList<String>();

          while (cells.hasNext()) {
            if (cellCount <= 10) {
              int cellValueInt;
              String CellValue;
              HSSFCell cell = (HSSFCell) cells.next();

              if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                // System.out.print( cell.getNumericCellValue()+"     " );
                cellValueInt = (int) cell.getNumericCellValue();
                CellValue = Integer.toString(cellValueInt);
              } else {
                CellValue = cell.getStringCellValue();
              }
              readExcelData.add(CellValue);
              cnt++;

              cellCount++;
            }

            if (cellCount == 11) break;
          }
          excelDataArray.add(new Object[] {readExcelData});
        }

      } catch (IOException ex) {
        ex.printStackTrace();
      }
      return excelDataArray.iterator();
    }
  /** @param args */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    // Directory path here
    String path = "./plantillas";

    String files;
    File folder = new File(path);
    File[] listOfFiles = folder.listFiles();

    for (int i = 0; i < listOfFiles.length; i++) {

      if (listOfFiles[i].isFile()) {
        files = listOfFiles[i].getName();
        System.out.println(files);
      }
    }

    InputStream input;
    try {
      input = new BufferedInputStream(new FileInputStream("./plantillas/Panama.xls"));
      POIFSFileSystem fs;

      fs = new POIFSFileSystem(input);

      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);

      Iterator rows = sheet.rowIterator();

      while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();
        System.out.println("\n");
        Iterator cells = row.cellIterator();
        while (cells.hasNext()) {

          HSSFCell cell = (HSSFCell) cells.next();
          if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType())
            System.out.print(cell.getNumericCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType())
            System.out.print(cell.getStringCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType())
            System.out.print(cell.getBooleanCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK     ");
          else System.out.print("Unknown cell type");
        }
      }

    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Ejemplo n.º 5
0
 static StringBuilder readSheet(HSSFSheet sheet) {
   StringBuilder json_sheet = new StringBuilder(1024 * 100);
   int row_length = sheet.getPhysicalNumberOfRows();
   Iterator<Row> rows = sheet.rowIterator();
   HSSFRow row;
   if (rows.hasNext()) row = (HSSFRow) rows.next();
   else return null;
   int col_length = row.getPhysicalNumberOfCells();
   Iterator<Cell> cells = row.cellIterator();
   HSSFCell cell;
   if (cells.hasNext()) cell = (HSSFCell) cells.next();
   else return null;
   int first_row = cell.getRowIndex();
   json_sheet.append("{'categories':[");
   a:
   for (int x = first_row; x < row_length; x++) {
     row = sheet.getRow(x);
     if (x > first_row) json_sheet.append("{");
     for (int y = 0; y < col_length; y++) {
       cell = row.getCell(y);
       String value = "空白";
       switch (cell.getCellType()) {
         case HSSFCell.CELL_TYPE_NUMERIC:
           value = "" + cell.getNumericCellValue();
           // Log.v("数字",value);
           break;
         case HSSFCell.CELL_TYPE_BOOLEAN:
           // Log.v("布尔",""+cell.getBooleanCellValue());
           break a;
         case HSSFCell.CELL_TYPE_STRING:
           value = "'" + cell.getStringCellValue() + "'";
           // Log.v("文本",value);
           break;
       }
       if (y == 0) {
         if (x == first_row) continue;
         else json_sheet.append("'name':" + value + ",'type':'line','data':[");
       } else json_sheet.append(value + ",");
     }
     json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
     if (x == first_row) json_sheet.append("}\n[");
     else json_sheet.append("},");
   }
   json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
   // Log.v("JSON",json_sheet.toString());
   return json_sheet;
 }
Ejemplo n.º 6
0
  // read the data from the excel
  public List readFromXML(InputStream input) throws Exception {
    List<TeamRecord> teamRecord = new ArrayList<TeamRecord>();
    List<String> wrong_data = new ArrayList<String>();
    List rt_list = new ArrayList<List>();
    HashSet<String> wrong_users = new HashSet<String>();

    //		String log="Start read Excel";
    //		System.out.println(log);
    //		all_log.add(log);

    try {
      POIFSFileSystem fs = new POIFSFileSystem(input);
      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);

      boolean vali_date = true;
      boolean firstLine = true;
      setRmLoginNames(trackingTicketScriptDao.findrmLoginNames());
      for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
        Row row = rit.next();
        if (firstLine) {
          firstLine = false;
        } else {
          vali_date = validation.validate_exceldata(row, wrong_users, wrong_data);
          if (vali_date == true) {
            teamRecord.add(readexcelline(row));
          }
        }
        rt_list.add(teamRecord);
        rt_list.add(wrong_data);
        rt_list.add(wrong_users);
      }
    } catch (IOException ex) {
      wrong_data.add("Excel is NULL,Please check Excel!");
      rt_list.add(teamRecord);
      rt_list.add(wrong_data);
      rt_list.add(wrong_users);
      return rt_list;
    }
    return rt_list;
  }
Ejemplo n.º 7
0
 private List<GoodsDTO> parseFile(File file) {
   try {
     LOG.info("Starting parsing goods from file {}", file.getName());
     List<GoodsDTO> goodsList = new ArrayList<>();
     FileInputStream fileInputStream = new FileInputStream(file);
     HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
     HSSFSheet hssfSheet = workbook.getSheetAt(0);
     Iterator rowIterator = hssfSheet.rowIterator();
     while (rowIterator.hasNext()) {
       HSSFRow row = (HSSFRow) rowIterator.next();
       GoodsDTO parsedGoods = parseRow(row);
       if (parsedGoods != null) {
         goodsList.add(parsedGoods);
       }
     }
     return goodsList;
   } catch (IOException e) {
     LOG.error("Could not parse file {}", file.getName(), e);
     return null;
   }
 }
Ejemplo n.º 8
0
    private boolean searchInExcel(String file) throws Exception {
      Row row;
      Cell cell;
      String text;
      boolean found = false;
      InputStream in = null;

      try {
        in = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(in);
        int sheets = wb.getNumberOfSheets();
        OUTERMOST:
        for (int i = 0; i < sheets; i++) {
          HSSFSheet sheet = wb.getSheetAt(i);
          Iterator<Row> rowIterator = sheet.rowIterator();
          while (rowIterator.hasNext()) {
            row = (Row) rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
              cell = cellIterator.next();
              int type = cell.getCellType();
              if (type == HSSFCell.CELL_TYPE_STRING) {
                text = cell.getStringCellValue();
                found = searchText(text);
                break OUTERMOST;
              }
            }
          }
        }
        return found;
      } finally {
        if (in != null)
          try {
            in.close();
          } catch (Exception e) {
          }
      }
    }
Ejemplo n.º 9
0
  /**
   * 添加临时表格内容
   *
   * @param mainSheet -- 原表单内容
   * @param subSheet -- 临时表单内容
   * @return
   */
  public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
    if (mainSheet == null || subSheet == null) return null;
    // 判断报表是否允许输出
    if (!isAllowOut(mainSheet)) return mainSheet;
    // 原报表的最后一行
    int endRowNum = mainSheet.getPhysicalNumberOfRows();

    HSSFRow sourow = null, descrow = null;
    HSSFCell sourcell = null, descell = null, orgcell = null;
    int i = 0, offsetcnt = 0;

    // 复制表格中的图片
    copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());

    // 设置以合并的单元格
    CellRangeAddress range = null;
    int mergedNum = subSheet.getNumMergedRegions();
    for (i = 0; i < mergedNum; i++) {
      range = subSheet.getMergedRegion(i);
      range.setFirstRow(range.getFirstRow() + endRowNum);
      range.setLastRow(range.getLastRow() + endRowNum);
      mainSheet.addMergedRegion(range);
    }
    range = null;
    // int k = 0;

    // 设置相关参数
    mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
    mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
    mainSheet.setAutobreaks(subSheet.getAutobreaks());
    mainSheet.setDialog(subSheet.getDialog());
    mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
    mainSheet.setFitToPage(subSheet.getFitToPage());

    for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
      sourow = (HSSFRow) iterow.next();
      offsetcnt = sourow.getRowNum() + endRowNum;
      descrow = mainSheet.createRow(offsetcnt);
      descrow.setHeight(sourow.getHeight());
      descrow.setHeightInPoints(sourow.getHeightInPoints());

      java.util.Iterator<Cell> iter = sourow.cellIterator();
      while (iter.hasNext()) {
        sourcell = (HSSFCell) iter.next();
        int column = sourcell.getColumnIndex();
        descell = descrow.createCell(column);

        // 取模板中的单元格,与来源表单位置相同
        int row = sourcell.getRowIndex();
        orgcell = mainSheet.getRow(row).getCell(column);
        if (orgcell != null) {
          // 取模板中的类型赋值
          descell.setCellType(orgcell.getCellType());
          // 取模板中的样式赋值
          descell.setCellStyle(orgcell.getCellStyle());
        } else {
          _log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
        }

        if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
          descell.setCellValue(sourcell.getStringCellValue());
        else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
          descell.setCellValue(sourcell.getNumericCellValue());
        else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
      }
      sourow = null;
      sourcell = null;
      descrow = null;
      orgcell = null;
    }

    return mainSheet;
  }
  /** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    // TODO Auto-generated method stub
    String filepath = null;
    String id = null,
        name = null,
        email = null,
        mobile = null,
        doj = null,
        password = null,
        man1 = null,
        man2 = null,
        check = "success";
    int level = 0, layer = 0;

    /* filepath=request.getParameter("file");
     */
    String message = "";
    // out.print("<html><body><h3>"+filepath+"</h3></body></html>");
    boolean isMultiPart = ServletFileUpload.isMultipartContent(request);
    HttpSession session = request.getSession();
    if (isMultiPart) {
      ServletFileUpload upload = new ServletFileUpload();

      try {
        FileItemIterator itr = upload.getItemIterator(request);
        while (itr.hasNext()) {
          FileItemStream item = itr.next();
          if (!item.isFormField()) {

            String path = getServletContext().getInitParameter("file-upload");
            request.setAttribute("path", path);

            if (FileUpload.processFile(path, item)) {
              filepath =
                  "C:\\Users\\shagayaraj\\Documents\\Tina\\webtechlab\\Ideation\\WebContent"
                      + "\\"
                      + item.getName();

              String[] ext = filepath.split("\\.");
              String ext1 = ext[0];
              String ext2 = ext[1];
              if (ext2.equals("xlsx")) {
                int rc = 0;
                FileInputStream fis = new FileInputStream(new File(filepath));

                XSSFWorkbook wb = new XSSFWorkbook(fis);
                XSSFSheet spreadsheet = wb.getSheetAt(0);

                // System.out.println(list);
                Iterator<Row> rowIterator = spreadsheet.iterator();
                while (rowIterator.hasNext()) {
                  check = "success";
                  row = (XSSFRow) rowIterator.next();
                  rc++;
                  Iterator<Cell> cellIterator = row.cellIterator();
                  while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    // out.print("<h3>"+cell.getCellType()+"</h3><br>");
                    switch (cell.getCellType()) {
                      case Cell.CELL_TYPE_NUMERIC:
                        if (cell.getColumnIndex() == 4) {
                          DataFormatter df = new DataFormatter();
                          if ((df.formatCellValue(cell))
                              .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
                            doj = df.formatCellValue(cell);
                          else {
                            message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
                            check = "error";
                          }
                          // out.print( "<h3>"+cvd+"</h3><br>");
                        } else if (cell.getColumnIndex() == 7) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]{10}"))
                            mobile = cell.getStringCellValue();
                          else {
                            message +=
                                "<p>Invalid Mobile Number format at row:"
                                    + rc
                                    + " and Column:8</p>";
                            check = "error";
                          }
                        } else if (cell.getColumnIndex() == 5) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]+"))
                            level = Integer.parseInt(cell.getStringCellValue());
                          else {
                            message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
                            check = "error";
                          }
                        } else if (cell.getColumnIndex() == 6) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]+"))
                            layer = Integer.parseInt(cell.getStringCellValue());
                          else {
                            message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
                            check = "error";
                          }
                        }
                        break;
                      case Cell.CELL_TYPE_STRING:
                        if (cell.getColumnIndex() == 0) {
                          id = cell.getStringCellValue();
                          String test;
                          if (id.isEmpty()) test = "invalid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(id);
                            if (user == null) {
                              test = "valid";
                            } else test = "invalid";
                          }
                          if (test.equals("invalid")) {
                            message +=
                                "<p>User Id is either null or already exists at row:"
                                    + rc
                                    + " and Column:1</p>";
                            check = "error";
                          }
                        }
                        if (cell.getColumnIndex() == 8) {
                          man1 = cell.getStringCellValue();
                          String test;
                          if (man1.isEmpty()) test = "valid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(man1);
                            if (user == null) {
                              test = "invalid";
                            } else test = "valid";
                          }
                          if (test.equals("invalid")) {
                            message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
                            check = "error";
                          }
                        }
                        if (cell.getColumnIndex() == 9) {
                          man2 = cell.getStringCellValue();
                          String test;
                          if (man2.isEmpty()) test = "valid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(man2);
                            if (user == null) {
                              test = "invalid";
                            } else test = "valid";
                          }
                          if (test.equals("invalid")) {
                            message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 1) {
                          if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
                            name = cell.getStringCellValue();
                          else {
                            message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 2) {
                          if ((cell.getStringCellValue())
                              .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
                            email = cell.getStringCellValue();
                          else {
                            message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();

                        break;
                    }
                  }
                  if (check.equals("success")) {
                    java.sql.Date date_joined = java.sql.Date.valueOf(doj);
                    userdao userd = new userdao();
                    userd.setUser(
                        id,
                        name,
                        email,
                        password,
                        date_joined,
                        level,
                        layer,
                        0,
                        mobile,
                        man1,
                        man2,
                        false,
                        true);
                    user_roledao user_roled = new user_roledao();
                    user_roled.setUserRolebyUId(id, "3");
                  }
                }
                fis.close();
              } // if closed
              else if (ext2.equals("xls")) {
                int rc = 0;
                File file = new File(filepath);
                InputStream input = new BufferedInputStream(new FileInputStream(file));

                POIFSFileSystem fs = new POIFSFileSystem(input);
                HSSFWorkbook wb = new HSSFWorkbook(fs);
                HSSFSheet sheet = wb.getSheetAt(0);
                Iterator rows = sheet.rowIterator();
                while (rows.hasNext()) {
                  HSSFRow row = (HSSFRow) rows.next();
                  rc++;
                  check = "success";
                  Iterator cells = row.cellIterator();
                  while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                      if (cell.getColumnIndex() == 4) {
                        DataFormatter df = new DataFormatter();
                        if ((df.formatCellValue(cell))
                            .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
                          doj = df.formatCellValue(cell);
                        else {
                          message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
                          check = "error";
                        }
                        // out.print( "<h3>"+cvd+"</h3><br>");
                      } else if (cell.getColumnIndex() == 7) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]{10}"))
                          mobile = cell.getStringCellValue();
                        else {
                          message +=
                              "<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>";
                          check = "error";
                        }
                      } else if (cell.getColumnIndex() == 5) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]+"))
                          level = Integer.parseInt(cell.getStringCellValue());
                        else {
                          message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
                          check = "error";
                        }
                      } else if (cell.getColumnIndex() == 6) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]+"))
                          layer = Integer.parseInt(cell.getStringCellValue());
                        else {
                          message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
                          check = "error";
                        }
                      }

                    } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
                      if (cell.getColumnIndex() == 0) {
                        id = cell.getStringCellValue();
                        String test;
                        if (id.isEmpty()) test = "invalid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(id);
                          if (user == null) {
                            test = "valid";
                          } else test = "invalid";
                        }
                        if (test.equals("invalid")) {
                          message +=
                              "<p>User Id is either null or already exists at row:"
                                  + rc
                                  + " and Column:1</p>";
                          check = "error";
                        }
                      }
                      if (cell.getColumnIndex() == 8) {
                        man1 = cell.getStringCellValue();
                        String test;
                        if (man1.isEmpty()) test = "valid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(man1);
                          if (user == null) {
                            test = "invalid";
                          } else test = "valid";
                        }
                        if (test.equals("invalid")) {
                          message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
                          check = "error";
                        }
                      }
                      if (cell.getColumnIndex() == 9) {
                        man2 = cell.getStringCellValue();
                        String test;
                        if (man2.isEmpty()) test = "valid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(man2);
                          if (user == null) {
                            test = "invalid";
                          } else test = "valid";
                        }
                        if (test.equals("invalid")) {
                          message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 1) {
                        if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
                          name = cell.getStringCellValue();
                        else {
                          message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 2) {
                        if ((cell.getStringCellValue())
                            .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
                          email = cell.getStringCellValue();
                        else {
                          message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
                    }
                  }
                  if (check.equals("success")) {
                    java.sql.Date date_joined = java.sql.Date.valueOf(doj);
                    userdao userd = new userdao();
                    userd.setUser(
                        id,
                        name,
                        email,
                        password,
                        date_joined,
                        level,
                        layer,
                        0,
                        mobile,
                        man1,
                        man2,
                        false,
                        true);
                    user_roledao user_roled = new user_roledao();
                    user_roled.setUserRolebyUId(id, "3");
                  }
                }
              } else {
                message += "Unsupported File format!Choose files of format .xls or .xlsx only!";
              }
              if (message == null) {
                message = "<p>Users added successfully</p>";
              }
            } else {
              message += "File upload unsuccessful";
            }
          }
        }

        request.setAttribute("errormsg", message);
        RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
        rd.forward(request, response);
      } catch (FileNotFoundException e) {
        message += "The given file cannot be found!";
        request.setAttribute("errormsg", message);
        RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
        rd.forward(request, response);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
  private ArrayList<WorkInfoDto> readExcelFromInputstream(InputStream inputstream) {

    //		// HSSFWorkbook wb = new HSSFWorkbook(new
    //		// FileInputStream("e:\\workbook.xls"));
    //		// HSSFSheet sheet = wb.getSheetAt(0);
    //		//
    //		// for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator();
    //		// iter.hasNext();) {
    //		// Row row = iter.next();
    //		// for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator();
    //		// iter2.hasNext();) {
    //		// Cell cell = iter2.next();
    //		// String content = cell.getStringCellValue();// 除非是sring类型,否则这样迭代读取会有错误
    //		// System.out.println(content);
    //		HSSFWorkbook rwb = null;
    //		try {
    //
    //			rwb = new HSSFWorkbook(inputstream);
    //			HSSFSheet st = rwb.getSheetAt(0);
    //			if (st != null) {
    //				ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
    //				ArrayList<String> lables = new ArrayList<String>();
    //				boolean first = true;
    //				for (Iterator<Row> iter = st.rowIterator(); iter.hasNext();) {
    //					Row row = iter.next();
    //					if (first) {
    //						for (Iterator<Cell> iter2 = row.cellIterator(); iter2
    //								.hasNext();) {
    //							Cell cell = iter2.next();
    //							lables.add(cell.getStringCellValue());
    //						}
    //						first = false;
    //					}
    //
    //					else {
    //						int j = 0;
    //						WorkInfoDto e = new WorkInfoDto();
    //						for (Iterator<Cell> iter2 = row.cellIterator(); iter2
    //								.hasNext();) {
    //							Cell cell = iter2.next();
    //							String val = "";
    //							if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
    //								val = String
    //										.valueOf(cell.getBooleanCellValue());
    //							else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
    //								val = String.valueOf((int) cell
    //										.getNumericCellValue());
    //							else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
    //								val = cell.getStringCellValue();
    //							setEmployeeValue(lables.get(j++), val, e);
    //						}
    //						list.add(e);
    //					}
    //				}
    //				return list;
    //			}
    //		} catch (Exception ex) {
    //			ex.printStackTrace();
    //		} finally {
    //			try {
    //				inputstream.close();
    //			} catch (IOException e) {
    //				e.printStackTrace();
    //			}
    //		}
    //		return null;

    boolean success = true;
    HSSFWorkbook rwb = null;
    try {

      rwb = new HSSFWorkbook(inputstream);
      HSSFSheet st = rwb.getSheetAt(0);
      if (st != null) {
        ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
        ArrayList<String> lables = new ArrayList<String>();
        boolean first = true;
        for (Iterator<Row> iter = st.rowIterator(); iter.hasNext(); ) {
          Row row = iter.next();
          if (first) {
            for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
              Cell cell = iter2.next();
              lables.add(cell.getStringCellValue());
            }
            first = false;
          } else {
            int j = 0;
            WorkInfoDto e = new WorkInfoDto();
            boolean complete = true;
            for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
              Cell cell = iter2.next();
              String val = "";
              if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
                val = String.valueOf(cell.getBooleanCellValue());
              else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                val = String.valueOf((int) cell.getNumericCellValue());
              else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
                val = cell.getStringCellValue();
              if (lables.get(j).toLowerCase().equals("id") && exist(val)) {
                System.out.println(lables.get(j) + " : " + val);
                complete = false;
                success = false;
              }
              setEmployeeValue(lables.get(j++), val, e);
            }
            if (complete) list.add(e);
            else {
              sessionManager.addGlobalMessageWarn(
                  "ID: " + e.getId() + " has existed. This employee is not added into system.",
                  null);
              // FacesMessage message = new FacesMessage("ID: " +
              // e.getId() +
              // " has existed. This employee is not added into system.",null);
            }
          }
        }
        if (success)
          sessionManager.addGlobalMessageInfo("The data file is successfully uploaded.", null);
        else
          sessionManager.addGlobalMessageWarn(
              "There are employees that not be added to the system.", null);
        return list;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      try {
        inputstream.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    return null;
  }
  /**
   * Process the specified HTTP request, and create the corresponding HTTP response (or forward to
   * another web component that will create it). Return an <code>ActionForward</code> instance
   * describing where and how control should be forwarded, or <code>null</code> if the response has
   * already been completed.
   *
   * @param mapping The ActionMapping used to select this instance
   * @param form The optional ActionForm bean for this request (if any)
   * @param request The HTTP request we are processing
   * @param response The HTTP response we are creating
   * @exception Exception if business logic throws an exception
   */
  public ActionForward execute(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {

    // Extract attributes we will need
    MessageResources messages = getResources(request);

    // save errors
    ActionMessages errors = new ActionMessages();

    // START check for login (security)
    if (!SecurityService.getInstance().checkForLogin(request.getSession(false))) {
      return (mapping.findForward("welcome"));
    }
    // END check for login (security)

    // START get id of current quote from either request, attribute, or cookie
    // id of quote from request
    String projectId = null;
    projectId = request.getParameter("projectViewId");

    // check attribute in request
    if (projectId == null) {
      projectId = (String) request.getAttribute("projectViewId");
    }

    // id of quote from cookie
    if (projectId == null) {
      projectId = StandardCode.getInstance().getCookie("projectViewId", request.getCookies());
    }

    // default client to first if not in request or cookie
    if (projectId == null) {
      List results = QuoteService.getInstance().getQuoteList();
      Quote1 first = (Quote1) results.get(0);
      projectId = String.valueOf(first.getQuote1Id());
    }
    // projectId="6553";
    Integer id = Integer.valueOf(projectId);
    String[] dataValue = new String[11];
    Project p = ProjectService.getInstance().getSingleProject(Integer.parseInt(projectId));
    // END get id of current quote from either request, attribute, or cookie

    // get quote to add files to
    // Quote1 q = QuoteService.getInstance().getSingleQuote(p.getQuotes());
    Set sources = p.getSourceDocs();
    // get the lin task to update
    // String linTaskId = StandardCode.getInstance().getCookie("quoteViewGeneralTradosUploadId",
    // request.getCookies());
    System.out.println(sources.size());

    File folder = new File("C:/log");
    File[] listOfFiles = folder.listFiles();
    for (int ij = 0; ij < listOfFiles.length; ij++) {
      if (listOfFiles[ij].isFile() && (listOfFiles[ij].getName().endsWith(".log"))
          || listOfFiles[ij].getName().endsWith(".xls")
          || listOfFiles[ij].getName().endsWith(".xlsx")
          || listOfFiles[ij].getName().endsWith(".xml")) {
        String lang = "";
        System.out.println("File " + listOfFiles[ij].getName());
        String myFile = listOfFiles[ij].getName();
        Integer leng = myFile.length();
        if (listOfFiles[ij].getName().endsWith(".log")) {
          lang =
              (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4));
        } else if (listOfFiles[ij].getName().endsWith(".xlsx")) {
          lang =
              (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 7, leng - 5));
        } else {
          lang =
              (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4));
        }
        // List sourceLang = QuoteService.getInstance().getSourceLang1(q);
        for (Iterator sourceIter = sources.iterator(); sourceIter.hasNext(); ) {
          SourceDoc sd = (SourceDoc) sourceIter.next();

          List targetLang = QuoteService.getInstance().getTargetLang(sd.getSourceDocId());
          for (Iterator linTargetIter = sd.getTargetDocs().iterator(); linTargetIter.hasNext(); ) {

            TargetDoc td = (TargetDoc) linTargetIter.next();
            if (td.getLanguage().equalsIgnoreCase(lang)) {

              List linTasklist = QuoteService.getInstance().getLinTask(td.getTargetDocId());
              for (int k = 0; k < linTasklist.size(); k++) {

                LinTask lt = (LinTask) linTasklist.get(k);

                // get input stream
                // InputStream in = listOfFiles[ij].getInputStream();

                if (listOfFiles[ij].getName().endsWith(".log")) {

                  FileInputStream in = new FileInputStream(listOfFiles[ij]);
                  // byte[] fileData = listOfFiles[ij].getFileData(); //byte array of entire file
                  long length = listOfFiles[ij].length();

                  byte[] fileData = new byte[(int) length];

                  in.read(fileData); // read data into fileData
                  String entireRead = new String(fileData); // the entire file as a string
                  String[] lines = entireRead.split("\n"); // lines within the file

                  String line = new String(""); // each line

                  // scroll to totals
                  int j = 0; // line numbers
                  try {
                    while (true) {
                      line = lines[j++];
                      if (line != null
                          && line.length() > 12
                          && line.substring(0, 13).equals("Analyse Total")) {
                        break;
                      }
                    }

                    // move to repetitions line
                    j++;
                    j++;
                    j++;

                    String[] parts; // each number per line
                    String wordRep = null;
                    String word100 = null;
                    String word95 = null;
                    String word85 = null;
                    String word75 = null;
                    String word50 = null;
                    String wordNo = null;
                    String wordPerfect = null;
                    String wordContext = null;
                    String wordTotal = null;

                    // wordRep
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 3) { // if at the words column
                        wordRep = parts[i];
                        break;
                      }
                    }

                    // word100
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 3) { // if at the words column
                        word100 = parts[i];
                        break;
                      }
                    }

                    // word95
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 5) { // if at the words column
                        word95 = parts[i];
                        break;
                      }
                    }

                    // word85
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 5) { // if at the words column
                        word85 = parts[i];
                        break;
                      }
                    }

                    // word75
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 5) { // if at the words column
                        word75 = parts[i];
                        break;
                      }
                    }

                    // word50
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 5) { // if at the words column
                        word50 = parts[i];
                        break;
                      }
                    }

                    // wordNo
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 4) { // if at the words column
                        wordNo = parts[i];
                        break;
                      }
                    }

                    // wordTotal
                    line = lines[j++];
                    parts = line.split(" ");
                    for (int i = 0, counter = 0; i < parts.length; i++) {
                      if (parts[i].length() > 0) { // look for non-blank items and count them
                        counter++;
                      }
                      if (counter == 3) { // if at the words column
                        wordTotal = parts[i];
                        break;
                      }
                    }
                    // END process the trados .log file

                    // remove commas from trados values
                    wordRep = wordRep.replaceAll(",", "");
                    word100 = word100.replaceAll(",", "");
                    word95 = word95.replaceAll(",", "");
                    word85 = word85.replaceAll(",", "");
                    word75 = word75.replaceAll(",", "");
                    word50 = word50.replaceAll(",", "");
                    wordNo = wordNo.replaceAll(",", "");
                    // wordPerfect=null;
                    // wordContext=null;
                    wordTotal = wordTotal.replaceAll(",", "");

                    // convert trados values from strings to numbers
                    Integer numRep = Integer.valueOf(wordRep);
                    Integer num100 = Integer.valueOf(word100);
                    Integer num95 = Integer.valueOf(word95);
                    Integer num85 = Integer.valueOf(word85);
                    Integer num75 = Integer.valueOf(word75);
                    Integer num50 = Integer.valueOf(word50);
                    Integer numNo = Integer.valueOf(wordNo);
                    Double numTotal = Double.valueOf(wordTotal);

                    // find totals to save to lin task
                    int numNew = num50.intValue() + numNo.intValue();
                    int num8599 = num95.intValue() + num85.intValue();
                    int numNew4 = num75.intValue() + numNew;
                    if (lt.getTaskName().equalsIgnoreCase("Translation")) {
                      // set new trados values for the lin task
                      lt.setWordRep(numRep);
                      lt.setWord100(num100);
                      lt.setWord95(num95);
                      lt.setWord85(num85);
                      lt.setWord75(num75);
                      lt.setWordNew(new Integer(numNew));
                      lt.setWord8599(new Integer(num8599));
                      lt.setWordNew4(new Double(numNew4));
                      lt.setWordTotal(numTotal);
                    } else if (lt.getTaskName().equalsIgnoreCase("editing")) {

                      lt.setWordNew4(numTotal);
                      lt.setWordTotal(numTotal);
                    }
                    // upload the new trados values to db
                    ProjectService.getInstance().updateLinTask(lt);

                    // START get file list
                    // get input stream

                    in.close();
                    in = new FileInputStream(listOfFiles[ij]);

                    length = listOfFiles[ij].length();

                    fileData = new byte[(int) length];

                    in.read(fileData); // read data into fileData
                    entireRead = new String(fileData); // the entire file as a string
                    lines = entireRead.split("\n"); // lines within the file

                    line = new String(""); // each line

                  } catch (Exception e) {
                  }
                  in.close();

                } else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xls")) {

                  POIFSFileSystem fs =
                      new POIFSFileSystem(
                          new FileInputStream("C:/log/" + listOfFiles[ij].getName()));

                  HSSFWorkbook wb = new HSSFWorkbook(fs);
                  HSSFSheet sheet = wb.getSheetAt(0);
                  HSSFRow row;
                  HSSFCell cell;
                  int count = 0, i = 0;
                  String flag = "true";

                  Iterator rows = sheet.rowIterator();

                  while (rows.hasNext()) {
                    row = (HSSFRow) rows.next();
                    count = 0;
                    Iterator cells = row.cellIterator();
                    while (cells.hasNext()) {

                      cell = (HSSFCell) cells.next();

                      count++;
                      try {
                        if (count == 4 && flag.equalsIgnoreCase("true")) {
                          dataValue[i++] = cell.toString();

                          System.out.println("cel value---------->  " + cell.toString());

                          if (i > 10) {
                            flag = "false";
                          }
                        }
                      } catch (Exception e) {
                        System.out.println("Integer Value" + count++);
                      }
                    }
                  }

                  Integer numRep = Math.round(Float.parseFloat(dataValue[2]));
                  Integer num100 = Math.round(Float.parseFloat(dataValue[4]));
                  Integer num95 = Math.round(Float.parseFloat(dataValue[5]));
                  Integer num85 = Math.round(Float.parseFloat(dataValue[6]));
                  Integer num75 = Math.round(Float.parseFloat(dataValue[7]));
                  Integer num50 = Math.round(Float.parseFloat(dataValue[8]));
                  Integer numNo = Math.round(Float.parseFloat(dataValue[9]));
                  Integer numPerfect = Math.round(Float.parseFloat(dataValue[1]));
                  Integer numContext = Math.round(Float.parseFloat(dataValue[3]));
                  Double numTotal = Double.valueOf(dataValue[10]);
                  // numRep = Integer.parseInt(dataValue[1]);

                  int numNew = num50.intValue() + numNo.intValue();
                  int num8599 = num95.intValue() + num85.intValue();
                  int numNew4 = num75.intValue() + numNew;
                  if (lt.getTaskName().equalsIgnoreCase("Translation")) {
                    // set new trados values for the lin task
                    lt.setWordRep(numRep);
                    lt.setWord100(num100);
                    lt.setWord95(num95);
                    lt.setWord85(num85);
                    lt.setWord75(num75);
                    lt.setWordNew(new Integer(numNew));
                    lt.setWord8599(new Integer(num8599));
                    lt.setWordNew4(new Double(numNew4));
                    lt.setWordContext(numContext);
                    lt.setWordPerfect(numPerfect);
                    lt.setWordTotal(numTotal);
                  } else if (lt.getTaskName().equalsIgnoreCase("editing")) {

                    lt.setWordNew4(numTotal);
                    lt.setWordTotal(numTotal);
                  }
                  // upload the new trados values to db
                  ProjectService.getInstance().updateLinTask(lt);

                } else if (listOfFiles[ij].isFile()
                    && listOfFiles[ij].getName().endsWith(".xlsx")) {
                  //                                      POIFSFileSystem fs = new
                  // POIFSFileSystem(new FileInputStream("C:/log/" + listOfFiles[ij].getName()));
                  //                                    File file = new File("C:/log/" +
                  // listOfFiles[ij].getName());
                  //                                   OPCPackage pkg = OPCPackage.open(new
                  // FileInputStream(file.getAbsolutePath()));
                  //                                    XSSFWorkbook wb = new XSSFWorkbook(pkg);
                  InputStream fs = new FileInputStream("C:/log/" + listOfFiles[ij].getName());
                  XSSFWorkbook wb = new XSSFWorkbook(fs);

                  //                                    XSSFWorkbook wb = new XSSFWorkbook(fs);
                  XSSFSheet sheet = wb.getSheetAt(0);
                  XSSFRow row;
                  XSSFCell cell;
                  int count = 0, i = 0;
                  String flag = "true";

                  Iterator rows = sheet.rowIterator();

                  while (rows.hasNext()) {
                    row = (XSSFRow) rows.next();
                    count = 0;
                    Iterator cells = row.cellIterator();
                    while (cells.hasNext()) {

                      cell = (XSSFCell) cells.next();

                      count++;
                      try {
                        if (count == 4 && flag.equalsIgnoreCase("true")) {
                          dataValue[i++] = cell.toString();

                          System.out.println("cel value---------->  " + cell.toString());

                          if (i > 10) {
                            flag = "false";
                          }
                        }
                      } catch (Exception e) {
                        System.out.println("Integer Value" + count++);
                      }
                    }
                  }

                  Integer numRep = Math.round(Float.parseFloat(dataValue[2]));
                  Integer num100 = Math.round(Float.parseFloat(dataValue[4]));
                  Integer num95 = Math.round(Float.parseFloat(dataValue[5]));
                  Integer num85 = Math.round(Float.parseFloat(dataValue[6]));
                  Integer num75 = Math.round(Float.parseFloat(dataValue[7]));
                  Integer num50 = Math.round(Float.parseFloat(dataValue[8]));
                  Integer numNo = Math.round(Float.parseFloat(dataValue[9]));
                  Double numTotal = Double.valueOf(dataValue[10]);
                  // numRep = Integer.parseInt(dataValue[1]);

                  int numNew = num50.intValue() + numNo.intValue();
                  int num8599 = num95.intValue() + num85.intValue();
                  int numNew4 = num75.intValue() + numNew;
                  if (lt.getTaskName().equalsIgnoreCase("Translation")) {
                    // set new trados values for the lin task
                    lt.setWordRep(numRep);
                    lt.setWord100(num100);
                    lt.setWord95(num95);
                    lt.setWord85(num85);
                    lt.setWord75(num75);
                    lt.setWordNew(new Integer(numNew));
                    lt.setWord8599(new Integer(num8599));
                    lt.setWordNew4(new Double(numNew4));
                    lt.setWordTotal(numTotal);
                  } else if (lt.getTaskName().equalsIgnoreCase("editing")) {

                    lt.setWordNew4(numTotal);
                    lt.setWordTotal(numTotal);
                  }
                  // upload the new trados values to db
                  ProjectService.getInstance().updateLinTask(lt);

                } else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xml")) {

                  InputStream in = new FileInputStream("C:/log/" + listOfFiles[ij].getName());
                  System.setProperty(
                      "javax.xml.parsers.DocumentBuilderFactory",
                      "com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl");
                  DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
                  DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
                  Document doc = dBuilder.parse(in);
                  doc.getDocumentElement().normalize();
                  System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
                  Integer numRep = 0;
                  Integer num100 = 0;
                  Integer num95 = 0;
                  Integer num85 = 0;
                  Integer num75 = 0;
                  Integer num50 = 0;
                  Integer numNo = 0;
                  Integer numTotal = 0;
                  Integer numContext = 0;
                  Integer numPerfect = 0;
                  NodeList batchTotal = doc.getElementsByTagName("batchTotal");
                  //                                     Element eElement = (Element) batchTotal;
                  //                                      NodeList analyse =
                  // eElement.getElementsByTagName("analyse");
                  if (batchTotal != null && batchTotal.getLength() > 0) {
                    Node node = batchTotal.item(0);
                    if (node.getNodeType() == Node.ELEMENT_NODE) {
                      Element eElement = (Element) node;
                      NodeList analyse = eElement.getElementsByTagName("analyse");

                      if (analyse != null && analyse.getLength() > 0) {
                        Node node1 = analyse.item(0);
                        if (node1.getNodeType() == Node.ELEMENT_NODE) {
                          Element eElement1 = (Element) node1;
                          //                                                    NodeList analyse1 =
                          // eElement.getElementsByTagName("analyse");

                          eElement1.getElementsByTagName("fuzzy").item(0).getTextContent();
                          NodeList fuzzy = doc.getElementsByTagName("fuzzy");

                          for (int temp = 0; temp < fuzzy.getLength(); temp++) {

                            Node nNode = fuzzy.item(temp);

                            System.out.println("\nCurrent Element :" + nNode.getNodeName());

                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {

                              Element eElement2 = (Element) nNode;
                              if (eElement2.getAttribute("min").equalsIgnoreCase("50")
                                  && eElement2.getAttribute("max").equalsIgnoreCase("74")) {
                                num50 = Integer.parseInt(eElement2.getAttribute("words"));
                              }
                              if (eElement2.getAttribute("min").equalsIgnoreCase("75")
                                  && eElement2.getAttribute("max").equalsIgnoreCase("84")) {
                                num75 = Integer.parseInt(eElement2.getAttribute("words"));
                              }
                              if (eElement2.getAttribute("min").equalsIgnoreCase("85")
                                  && eElement2.getAttribute("max").equalsIgnoreCase("94")) {
                                num85 = Integer.parseInt(eElement2.getAttribute("words"));
                              }
                              if (eElement2.getAttribute("min").equalsIgnoreCase("95")
                                  && eElement2.getAttribute("max").equalsIgnoreCase("99")) {
                                num95 = Integer.parseInt(eElement2.getAttribute("words"));
                              }
                            }
                          }

                          //

                          eElement1.getElementsByTagName("new").item(0).getTextContent();
                          NodeList new1 = doc.getElementsByTagName("new");
                          for (int temp = 0; temp < new1.getLength(); temp++) {
                            Node nNode = new1.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              numNo = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }
                          eElement1.getElementsByTagName("total").item(0).getTextContent();
                          NodeList total = doc.getElementsByTagName("total");
                          for (int temp = 0; temp < total.getLength(); temp++) {
                            Node nNode = total.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              numTotal = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }
                          eElement1.getElementsByTagName("exact").item(0).getTextContent();
                          NodeList exact = doc.getElementsByTagName("exact");
                          for (int temp = 0; temp < exact.getLength(); temp++) {
                            Node nNode = exact.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              num100 = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }

                          eElement1.getElementsByTagName("perfect").item(0).getTextContent();
                          NodeList perfect = doc.getElementsByTagName("perfect");
                          for (int temp = 0; temp < perfect.getLength(); temp++) {
                            Node nNode = perfect.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              numPerfect = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }

                          eElement1.getElementsByTagName("repeated").item(0).getTextContent();
                          NodeList repeated = doc.getElementsByTagName("repeated");
                          for (int temp = 0; temp < repeated.getLength(); temp++) {
                            Node nNode = repeated.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              numRep = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }
                          try {
                            eElement1
                                .getElementsByTagName("crossFileRepeated")
                                .item(0)
                                .getTextContent();
                            NodeList crossFileRepeated =
                                doc.getElementsByTagName("crossFileRepeated");
                            for (int temp = 0; temp < crossFileRepeated.getLength(); temp++) {
                              Node nNode = crossFileRepeated.item(temp);
                              if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                                Element eElement2 = (Element) nNode;
                                numRep += Integer.parseInt(eElement2.getAttribute("words"));
                              }
                            }
                          } catch (Exception e) {
                          }

                          eElement1.getElementsByTagName("inContextExact").item(0).getTextContent();
                          NodeList inContextExact = doc.getElementsByTagName("inContextExact");
                          for (int temp = 0; temp < inContextExact.getLength(); temp++) {
                            Node nNode = inContextExact.item(temp);
                            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                              Element eElement2 = (Element) nNode;
                              numContext = Integer.parseInt(eElement2.getAttribute("words"));
                            }
                          }
                        }
                      }
                    }
                  }

                  int numNew = num50.intValue() + numNo.intValue();
                  int num8599 = num95.intValue() + num85.intValue();
                  int numNew4 = num75.intValue() + numNew;
                  if (lt.getTaskName().equalsIgnoreCase("Translation")) {
                    // set new trados values for the lin task
                    lt.setWordRep(numRep);
                    lt.setWord100(num100);
                    lt.setWord95(num95);
                    lt.setWord85(num85);
                    lt.setWord75(num75);
                    lt.setWordNew(new Integer(numNew));
                    lt.setWord8599(new Integer(num8599));
                    lt.setWordNew4(new Double(numNew4));
                    lt.setWordTotal(new Double(numTotal));
                    lt.setWordContext(numContext);
                    lt.setWordPerfect(numPerfect);
                  } else if (lt.getTaskName().equalsIgnoreCase("editing")) {
                    lt.setWordNew(new Integer(numTotal));
                    lt.setWordNew4(new Double(numTotal));
                    lt.setWordTotal(new Double(numTotal));
                  }
                  // upload the new trados values to db
                  ProjectService.getInstance().updateLinTask(lt);

                } else {
                  System.out.println("no Match");
                  request.setAttribute("isError", "error");
                  return (mapping.findForward("Error"));
                }
              }
            }
          }
        }

      } else if (listOfFiles[ij].isDirectory()) {
        System.out.println("Directory " + listOfFiles[ij].getName());
      }
    }

    deleteFile("C:/log");
    // END get file list

    // Forward control to the specified success URI
    return (mapping.findForward("Success"));
  }
Ejemplo n.º 13
0
  @Override
  public ExcelData getExcelData(String relativeExcelPath) {

    FileInputStream fis = null;
    ExcelData dataMap = new ExcelData();

    try {

      fis = new FileInputStream(relativeExcelPath);

      //
      // Create an excel workbook from the file system.
      //
      HSSFWorkbook workbook = new HSSFWorkbook(fis);
      //
      // Get the first sheet on the workbook.
      //
      HSSFSheet sheet = workbook.getSheetAt(0);

      Iterator<Row> rows = sheet.rowIterator();
      boolean headerFlag = true;
      List<String> hdrList = new ArrayList<String>();

      while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();
        Iterator<Cell> cells = row.cellIterator();
        if (headerFlag) {

          while (cells.hasNext()) {

            HSSFCell cell = (HSSFCell) cells.next();
            String hdrValue = cell.getStringCellValue();
            dataMap.put(hdrValue, new ArrayList<String>());
            hdrList.add(hdrValue);
            headerFlag = false;
          }
        } else {
          Iterator<String> hdrIterator = hdrList.iterator();
          while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            String cellvalue = "";
            if (cell.getCellType() == 0) cell.setCellType(Cell.CELL_TYPE_STRING);
            cellvalue = cell.getStringCellValue();
            String headerName = hdrIterator.next();
            List<String> data = dataMap.get(headerName);
            data.add(cellvalue);
            dataMap.put(headerName, data);
          }
        }
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      if (fis != null) {
        try {
          fis.close();
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
    return dataMap;
  }
Ejemplo n.º 14
0
 /**
  * 导入 excel
  *
  * @param inputstream : 文件输入流
  * @param pojoClass : 对应的导入对象 (每行记录)
  * @return
  */
 public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) {
   Collection dist = new ArrayList<Object>();
   try {
     // 得到目标目标类的所有的字段列表
     Field filed[] = pojoClass.getDeclaredFields();
     // 将所有标有Annotation的字段,也就是允许导入数据的字段,放入到一个map中
     Map<String, Method> fieldSetMap = new HashMap<String, Method>();
     Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
     // 循环读取所有字段
     for (int i = 0; i < filed.length; i++) {
       Field f = filed[i];
       // 得到单个字段上的Annotation
       Excel excel = f.getAnnotation(Excel.class);
       // 如果标识了Annotationd的话
       if (excel != null) {
         // 构造设置了Annotation的字段的Setter方法
         String fieldname = f.getName();
         String setMethodName =
             "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
         // 构造调用的method,
         Method setMethod = pojoClass.getMethod(setMethodName, new Class[] {f.getType()});
         // 将这个method以Annotaion的名字为key来存入。
         // 对于重名将导致 覆盖 失败,对于此处的限制需要
         fieldSetMap.put(excel.exportName(), setMethod);
         if (excel.importConvertSign() == 1) {
           // ----------------------------------------------------------------
           // update-begin--Author:Quainty  Date:20130524 for:[8]excel导出时间问题
           // 用get/setXxxxConvert方法名的话, 由于直接使用了数据库绑定的Entity对象,注入会有冲突
           StringBuffer setConvertMethodName = new StringBuffer("convertSet");
           setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
           setConvertMethodName.append(fieldname.substring(1));
           // update-end--Author:Quainty  Date:20130524 for:[8]excel导出时间问题
           // ----------------------------------------------------------------
           Method getConvertMethod =
               pojoClass.getMethod(setConvertMethodName.toString(), new Class[] {String.class});
           fieldSetConvertMap.put(excel.exportName(), getConvertMethod);
         }
       }
     }
     // 将传入的File构造为FileInputStream;
     // // 得到工作表
     HSSFWorkbook book = new HSSFWorkbook(inputstream);
     // // 得到第一页
     HSSFSheet sheet = book.getSheetAt(0);
     // // 得到第一面的所有行
     Iterator<Row> row = sheet.rowIterator();
     // 得到第一行,也就是标题行
     Row title = row.next();
     // 得到第一行的所有列
     Iterator<Cell> cellTitle = title.cellIterator();
     // 将标题的文字内容放入到一个map中。
     Map titlemap = new HashMap();
     // 从标题第一列开始
     int i = 0;
     // 循环标题所有的列
     while (cellTitle.hasNext()) {
       Cell cell = cellTitle.next();
       String value = cell.getStringCellValue();
       titlemap.put(i, value);
       i = i + 1;
     }
     // 用来格式化日期的DateFormat
     // SimpleDateFormat sf;
     while (row.hasNext()) {
       // 标题下的第一行
       Row rown = row.next();
       // 行的所有列
       Iterator<Cell> cellbody = rown.cellIterator();
       // 得到传入类的实例
       Object tObject = pojoClass.newInstance();
       int k = 0;
       // 遍历一行的列
       while (cellbody.hasNext()) {
         Cell cell = cellbody.next();
         // 这里得到此列的对应的标题
         String titleString = (String) titlemap.get(k);
         // 如果这一列的标题和类中的某一列的Annotation相同,那么则调用此类的的set方法,进行设值
         if (fieldSetMap.containsKey(titleString)) {
           Method setMethod = (Method) fieldSetMap.get(titleString);
           // 得到setter方法的参数
           Type[] ts = setMethod.getGenericParameterTypes();
           // 只要一个参数
           String xclass = ts[0].toString();
           // 判断参数类型
           if (Cell.CELL_TYPE_STRING == cell.getCellType()
               && fieldSetConvertMap.containsKey(titleString)) {
             // 目前只支持从String转换
             fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue());
           } else {
             if (xclass.equals("class java.lang.String")) {
               // 先设置Cell的类型,然后就可以把纯数字作为String类型读进来了:
               cell.setCellType(Cell.CELL_TYPE_STRING);
               setMethod.invoke(tObject, cell.getStringCellValue());
             } else if (xclass.equals("class java.util.Date")) {
               // update-start--Author:Quainty Date:20130523 for:日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
               Date cellDate = null;
               if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                 // 日期格式
                 cellDate = cell.getDateCellValue();
               } else { // 全认为是  Cell.CELL_TYPE_STRING: 如果不是 yyyy-mm-dd hh:mm:ss 的格式就不对(wait to
                        // do:有局限性)
                 cellDate = stringToDate(cell.getStringCellValue());
               }
               setMethod.invoke(tObject, cellDate);
               //// update-start--Author:lihuan Date:20130423 for:导入bug修复直接将导出的Excel导入出现的bug的修复
               ////
               // --------------------------------------------------------------------------------------------
               // String cellValue = cell.getStringCellValue();
               // Date theDate = stringToDate(cellValue);
               // setMethod.invoke(tObject, theDate);
               //// update-end--Author:lihuan Date:20130423 for:导入bug修复直接将导出的Excel导入出现的bug的修复
               ////
               // --------------------------------------------------------------------------------------------
             } else if (xclass.equals("class java.lang.Boolean")) {
               boolean valBool;
               if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                 valBool = cell.getBooleanCellValue();
               } else { // 全认为是  Cell.CELL_TYPE_STRING
                 valBool =
                     cell.getStringCellValue().equalsIgnoreCase("true")
                         || (!cell.getStringCellValue().equals("0"));
               }
               setMethod.invoke(tObject, valBool);
             } else if (xclass.equals("class java.lang.Integer")) {
               Integer valInt;
               if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                 valInt = (new Double(cell.getNumericCellValue())).intValue();
               } else { // 全认为是  Cell.CELL_TYPE_STRING
                 valInt = new Integer(cell.getStringCellValue());
               }
               setMethod.invoke(tObject, valInt);
             } else if (xclass.equals("class java.lang.Long")) {
               Long valLong;
               if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                 valLong = (new Double(cell.getNumericCellValue())).longValue();
               } else { // 全认为是  Cell.CELL_TYPE_STRING
                 valLong = new Long(cell.getStringCellValue());
               }
               setMethod.invoke(tObject, valLong);
             } else if (xclass.equals("class java.math.BigDecimal")) {
               BigDecimal valDecimal;
               if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                 valDecimal = new BigDecimal(cell.getNumericCellValue());
               } else { // 全认为是  Cell.CELL_TYPE_STRING
                 valDecimal = new BigDecimal(cell.getStringCellValue());
               }
               setMethod.invoke(tObject, valDecimal);
               //// ----------------------------------------------------------------
               //// update-begin--Author:sky Date:20130422
               // for:取值类型调整cell.getNumberCellValue-->>getStringCellValue
               // setMethod.invoke(tObject, new BigDecimal(cell.getStringCellValue()));
               //// update-end--Author:sky Date:20130422 for:取值类型调整
               //// ----------------------------------------------------------------
               // update-end--Author:Quainty Date:20130523 for:日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
             }
           }
         }
         // 下一列
         k = k + 1;
       }
       dist.add(tObject);
     }
   } catch (Exception e) {
     e.printStackTrace();
     return null;
   }
   return dist;
 }
Ejemplo n.º 15
0
  /**
   * Enregistrer des notes a partir de fichier excel (.xls) importés
   *
   * @return la chaine de navigation pour la suite des opérations
   */
  public String enregistrerImport() {
    if (fichierImport != null && fichierImport.getSize() != 0) {

      ByteArrayInputStream stream = new ByteArrayInputStream(fichierImport.getContents());

      try {
        POIFSFileSystem fs = new POIFSFileSystem(stream);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = null;
        HSSFCell cellmat = null;
        HSSFCell cellnote = null;
        int numcell = 0;

        // recupere tous les eleves figurant dans le fichier importé
        for (Iterator<Row> rowIt = sheet.rowIterator(); rowIt.hasNext(); ) {

          if (numcell >= 4) {
            row = (HSSFRow) rowIt.next();
            cellnote = row.getCell(5);

            cellmat = row.getCell(2);
            if (cellmat != null && cellnote != null) {

              // nous verifions ici si la note est comprise dans la fourchette voulue
              if ((float) cellnote.getNumericCellValue() > 20
                  || (float) cellnote.getNumericCellValue() < 0) {
                Repertoire.addMessageerreur(
                    "vous avez entrez une ou plusieurs note(s) qui n'est (sont) pas comprise en zero et 20");
                return "";
              }
              // fonction qui take le matricule et la note dun eleve pour mettre dans le bean
              insererNoteEleve(
                  cellmat.getStringCellValue(), (float) cellnote.getNumericCellValue());
            } else {
              if (cellmat != null && cellnote == null) {

                // fonction qui take le matricule et la note dun eleve pour mette dans le bean
                insererNoteEleve(cellmat.getStringCellValue(), (float) 0);
              }
            }
          } else {
            numcell++;
            rowIt.next();
          }
        } // End for

      } catch (FileNotFoundException e) {
        e.printStackTrace();
      } catch (IOException e) {
        e.printStackTrace();
      }
      try {
        this.service.enregistrerNotes(compositions, codeevaluation);
      } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    Repertoire.addMessageinfo("Notes enregistrées");
    return OperationResults.navWithParam(
        "visualiserNotes", "codeevaluation", String.valueOf(codeevaluation));
  }