private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
      throws Exception {
    Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
    Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
    nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
    nCell.setCellStyle(tCell.getCellStyle());

    tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
    nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
    nCell.setCellValue(
        tCell
            .getStringCellValue()
            .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
            .replaceAll(
                PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
            .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
    nCell.setCellStyle(tCell.getCellStyle());

    Row tempRow = tempSheet.getRow(4);
    Row newRow = newSheet.createRow(4);

    for (int c = 0; c < tempRow.getLastCellNum(); c++) {
      tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
      nCell = newRow.createCell(c, tCell.getCellType());
      nCell.setCellValue(tCell.getStringCellValue());
      nCell.setCellStyle(tCell.getCellStyle());
    }

    for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
      CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
      newSheet.addMergedRegion(mergedRegion);
    }
  }
Esempio n. 2
0
 private void loadData() {
   int rowNum = sheet.getLastRowNum();
   for (int i = 0; i <= rowNum; i++) {
     if (i % 2 == 0) {
       Row rowID = sheet.getRow(i);
       Row rowName = sheet.getRow(i + 1);
       for (int j = 0; j < 15; j++) {
         Cell cellID = rowID.getCell(j);
         Cell cellName = rowName.getCell(j);
         String name = WorkBookUtil.getCellValue(cellName);
         String cellValue = WorkBookUtil.getCellValue(cellID);
         if (name == null || name.equalsIgnoreCase("")) {
           continue;
         }
         int parseInt = Integer.parseInt(cellValue);
         if (catalogUsers.containsValue(name)) {
           repeatUsers.put(parseInt, name);
         }
         if (cellName.getCellStyle().getFillPattern() == CellStyle.SOLID_FOREGROUND) {
           colorUsers.put(parseInt, name);
         }
         catalogUsers.put(parseInt, name);
       }
     }
   }
 }
  private void generateEnforcementsRegion(Workbook wb, StringBuilder sb) {
    // Get the Enforcements Sheet
    Sheet sheet = wb.getSheet("Enforcements");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        int id = (int) cellId.getNumericCellValue();
        Cell cellName = row.getCell(1);
        String name = cellName.getStringCellValue().replaceAll("\"", "'");
        Cell cellDescription = row.getCell(2);
        String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
        Cell cellType = row.getCell(3);
        String type = cellType.getStringCellValue();
        sb.append("Enforcement En" + id + " {");
        sb.append("\n");
        sb.append("\tName \"" + name + "\"");
        sb.append("\n");
        sb.append("\tDescription \"" + description + "\"");
        sb.append("\n");
        sb.append("\tType " + type);
        sb.append("\n}");
        sb.append("\n\n");
      } else break;
    }
  }
	@Override
	public void importTower(Sheet sheet) {
		
		boolean isFirst = Boolean.TRUE;
		for(Iterator<Row> it = sheet.rowIterator(); it.hasNext(); ) {
			try {
				
				Row row = it.next();
				if(isFirst) {
					isFirst = Boolean.FALSE;
					continue;
				}

				Manufacturer manufacturer = manufacturerService.findOrCreateByNameAndCategory(
						row.getCell(MANUFACTURER).getStringCellValue().trim(),
						CategoryEnum.TOWER
				);
				
				Tower tower = new Tower(manufacturer);
				
				tower.setName(row.getCell(NAME).getStringCellValue().trim());
				tower.setPrice(BigDecimal.valueOf(row.getCell(PRICE).getNumericCellValue()));
				tower.setWatts(String.valueOf(row.getCell(WATTS).getNumericCellValue()));
				tower.setCode("0000000000");
				
				tower = this.save(tower);
				
				System.out.println(tower.toString());
			} catch (ServiceException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
  /**
   * 获取销售记录
   *
   * @param productSheet
   * @return
   */
  private int getSaleRecordsFormXSheet(Sheet salesSheet) {
    int count = 0;
    if (salesSheet == null) return 0;
    try {
      int rowStart = salesSheet.getFirstRowNum();
      int rowEnd = salesSheet.getLastRowNum();
      for (int i = rowStart + 1; i <= rowEnd; i++) {
        Row row = salesSheet.getRow(i);

        SaleRecord sr = new SaleRecord();

        // 日期
        String datestr = String.valueOf(getXssCellData(row.getCell(0)));
        if ("".equals(datestr)) continue;
        Date date = null;
        try {
          date = new SimpleDateFormat("yyyy-MM-dd").parse(datestr);
        } catch (Exception e) {
          continue;
        }

        String struser = String.valueOf(getXssCellData(row.getCell(1)));
        // if have not user info discard this record
        if ("".equals(struser)) continue;
        int user = Integer.parseInt(struser);

        String strproduct = String.valueOf(getXssCellData(row.getCell(2)));
        // if have not user info discard this record
        if ("".equals(strproduct)) continue;
        int product = Integer.parseInt(strproduct);

        // 购买价格
        String pricestr = (String) getXssCellData(row.getCell(3));
        double price = 0d;
        if (!"".equals(pricestr)) {
          price = Double.parseDouble(pricestr);
        }

        // 购买量
        String numstr = (String) getXssCellData(row.getCell(3));
        double num = 0d;
        if (!"".equals(numstr)) {
          num = Double.parseDouble(numstr);
        }

        sr.setNum(num);
        sr.setPrice(price);
        sr.setProduct(product);
        sr.setUser(user);
        sr.setSaledate(date);

        simpleDao.createEntity(sr);
        count++;
      }
    } catch (Exception e) {
      e.printStackTrace();
      log.error("getSaleRecordsFormXSheet exception !");
    }
    return count;
  }
Esempio n. 6
0
 public void writeData(Data data, boolean onlyNumbers) {
   try {
     File file = new File(path);
     if (!file.exists()) {
       file.createNewFile();
     }
     FileOutputStream fOutputStream = new FileOutputStream(file);
     org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
     org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
     @SuppressWarnings("rawtypes")
     ArrayList<ArrayList> dataList = data.getData();
     for (int i = 0; i < dataList.size(); i++) {
       Row row = sheet.createRow(i);
       for (int j = 0; j < dataList.get(i).size(); j++) {
         row.createCell(j);
         if (onlyNumbers) {
           row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
         } else {
           row.getCell(j).setCellValue((String) dataList.get(i).get(j));
         }
       }
     }
     workbook.write(fOutputStream);
     workbook.close();
     fOutputStream.close();
     workbook = null;
     file = null;
     sheet = null;
   } catch (EncryptedDocumentException | IOException e) {
     e.printStackTrace();
   }
 }
  @Override
  protected Workbook indexExcel(Map<String, String> params, Workbook excel) {
    List<Map<String, Object>> list = service.financialIndexData_02(params);
    Sheet sheet = excel.getSheetAt(0);

    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    cell.setCellValue(params.get("statMonth") + reportName);
    row = sheet.getRow(2);
    if (list != null && list.size() > 0) {
      for (Map<String, Object> map : list) {
        sheet = PoiUtil.copyLastRow(sheet, false);
        row = sheet.getRow(sheet.getLastRowNum() - 1);
        row.getCell(0).setCellValue(map.get("ENT_NAME").toString());
        row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1")));
        row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1")));
        row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2")));
        row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2")));
        row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3")));
        row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3")));
      }
    }
    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
    return excel;
  }
  public void parseExcelData(String filepath)
      throws InvalidFormatException, IOException, ParseException {

    Sheet sheet = loadWorksheet(filepath);

    Iterator<Row> row_iter = sheet.iterator();

    // Check if first row is a header row
    if (row_iter.hasNext()) {
      Row header_row = row_iter.next();

      // if the first cell is numeric,
      // there is no header row, reset the iterator
      if (header_row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC)
        row_iter = sheet.iterator();
    }

    while (row_iter.hasNext()) {
      InterviewInstance current_instance = getInstance(row_iter.next());

      // if no interview is present, start one
      if (interviews.isEmpty()) interviews.add(new Interview(current_instance));

      // add to the current interview instance in the list
      // make a new interview if the interview ID doesn't match up
      if (!interviews.get(interviews.size() - 1).add(current_instance))
        interviews.add(new Interview(current_instance));
    }
  }
Esempio n. 9
0
 public static void copySheet(Sheet sheet, Sheet newSheet) {
   int maxCol = 0;
   for (int row = 0; row <= sheet.getLastRowNum(); row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = newSheet.getRow(row);
     if (newRow == null) newRow = newSheet.createRow(row);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
     for (int col = 0; col < oldRow.getLastCellNum(); col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col);
       if (newCell == null) newCell = newRow.createCell(col);
       copyCell(oldCell, newCell, true);
     }
   }
   for (int col = 0; col <= maxCol; col++) {
     if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
   }
   for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
     CellRangeAddress cra = sheet.getMergedRegion(i);
     newSheet.addMergedRegion(cra);
   }
 }
  @Override
  public List<SeekerAimsTO> buildParticipants() throws Exception {
    boolean mark = false;
    List<Row> participantRows = new ArrayList<Row>();
    for (int i = 0; i < sheetParticipants.getLastRowNum(); i++) {
      Row row = sheetParticipants.getRow(i);
      if (mark) {
        if (row != null) {
          participantRows.add(row);
        }
      }
      if (row != null && row.getCell(ParticipantCols.NAME.getColumn()) != null) {
        String string = row.getCell(ParticipantCols.NAME.getColumn()).toString();
        if (string.contains(ParticipantCols.NAME.getHeader())) {
          mark = true;
        }

        if (mark) {
          if (row == null
              || (row.getCell(ParticipantCols.NAME.getColumn()) == null)
              || row.getCell(ParticipantCols.NAME.getColumn()).toString().isEmpty()) {
            mark = false;
          }
        }
      }
    }
    ProgramHeaderTO buildProgramDetails = buildProgramDetails();
    List<SeekerAimsTO> processRows = processRows(participantRows);
    for (SeekerAimsTO seekerAims : processRows) {
      seekerAims.setCountry(buildProgramDetails.getCountry());
    }
    sLogger.info("Participant list:" + participantRows.size());
    return processRows;
  }
  public void parse() throws FileNotFoundException, IOException, InvalidFormatException {

    InputStream inp;
    inp = new FileInputStream(fileName);
    Workbook wb = WorkbookFactory.create(inp);

    Row row;
    Sheet sheet = wb.getSheet(sheetName);
    int startingRow = 0;
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
      if (breakNow) {
        break;
      }
      row = sheet.getRow(i);
      if (row == null) {
        break;
      }

      for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getStringCellValue().isEmpty()
            || row.getCell(j).getStringCellValue() == null) {
          breakNow = true;
          break;
        }
        //                category.setCategoryName(row.getCell(j).getStringCellValue());
      }
    }

    inp.close();
  }
  public void loadBeachLocations() {

    try {
      FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls"));
      workbook = new HSSFWorkbook(file);
      Sheet sheet = workbook.getSheet("BEACH  SUMMARY");
      Iterator<Row> rowIterator = sheet.iterator();
      rowIterator.next(); // Skip the header row.
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String code = row.getCell(1).getStringCellValue().trim();
        System.out.println(code);
        if (!code.isEmpty()) {

          String beachname = row.getCell(1).getStringCellValue().trim();
          String county = row.getCell(2).getStringCellValue().trim();
          String description = row.getCell(1).getStringCellValue().trim() + "Beach";
          System.out.println(beachname + ":" + county + ":" + description);

          Beach beach = new Beach(beachname, description, county);
          beach.saveBeach();
        }
      }
      JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations....");
    } catch (IOException ex) {
      ex.printStackTrace();
      Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
 /** Reads excel file and matches cells to values */
 public Stock readFile(int firstItem, int lastItem)
     throws IllegalStateException, InvalidFormatException, IOException {
   List<Item> stockList = new ArrayList();
   Workbook book = WorkbookFactory.create(this.sourceFile);
   Sheet workSheet = book.getSheetAt(sheet);
   Item emptyItem = new Item("Системе не удалось разпознать элемент", 0, 0, 0, 0, 0, 0, 0);
   for (int n = firstItem - 1; n < lastItem; n++) {
     try {
       Row row = workSheet.getRow(n);
       Item item =
           new Item(
               row.getCell(cellCodes[0]).toString(),
               row.getCell(cellCodes[1]).getNumericCellValue(),
               row.getCell(cellCodes[2]).getNumericCellValue(),
               row.getCell(cellCodes[3]).getNumericCellValue(),
               row.getCell(cellCodes[4]).getNumericCellValue(),
               row.getCell(cellCodes[5]).getNumericCellValue(),
               row.getCell(cellCodes[6]).getNumericCellValue(),
               row.getCell(cellCodes[7]).getNumericCellValue());
       stockList.add(item);
     } catch (NullPointerException ex) {
       stockList.add(emptyItem);
     }
   }
   // pkg.close();
   Stock stock = new Stock(stockList);
   return stock;
 }
Esempio n. 14
0
 @Test
 public void linksTest() throws IOException {
   FileInputStream f =
       new FileInputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
   XSSFWorkbook wb = new XSSFWorkbook(f);
   XSSFSheet ws = wb.getSheet("sheet1");
   Iterator<Row> row = ws.iterator();
   row.next();
   while (row.hasNext()) {
     Row r = row.next();
     String linkname = r.getCell(0).getStringCellValue();
     try {
       driver.findElement(By.linkText(linkname)).click();
       String acturl = driver.getCurrentUrl();
       r.createCell(2).setCellValue(acturl);
       String expurl = r.getCell(1).getStringCellValue();
       if (acturl.equals(expurl)) {
         r.createCell(3).setCellValue("passsed");
       } else {
         r.createCell(3).setCellValue("failed");
       }
       driver.navigate().back();
     } catch (Exception e) {
       r.createCell(3).setCellValue("links not found");
     }
   }
   FileOutputStream f1 =
       new FileOutputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
   wb.write(f1);
   f1.close();
   driver.quit();
 }
Esempio n. 15
0
 public String getCellContent(Sheet sheet, int rowNum, int colNum) {
   Row row = sheet.getRow(rowNum);
   String contents = "";
   if (row != null && row.getCell(colNum) != null) {
     contents = getCellContent(row.getCell(colNum)).trim();
   }
   return contents;
 }
  private void generateServicesRegion(Workbook wb, StringBuilder sb) {
    // Get the Services Sheet
    Sheet sheet = wb.getSheet("Services");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        int id = (int) cellId.getNumericCellValue();
        Cell cellName = row.getCell(1);
        String name = cellName.getStringCellValue().replaceAll("\"", "'");
        Cell cellDescription = row.getCell(2);
        String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
        Cell cellPrivateData = row.getCell(3);
        Cell cellPartOf = row.getCell(4);
        sb.append("Service S" + id + " {");
        sb.append("\n");
        sb.append("\tName \"" + name + "\"");
        sb.append("\n");
        sb.append("\tDescription \"" + description + "\"");
        sb.append("\n");

        if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int privateData = (int) cellPrivateData.getNumericCellValue();
          sb.append("\tRefersTo PrivateData PD" + privateData);
          sb.append("\n");
        } else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) {
          String privateData = cellPrivateData.getStringCellValue();

          if (privateData.equals("All")) {
            sb.append("\tRefersTo PrivateData All");
          } else {
            sb.append("\tRefersTo PrivateData ");

            for (String s : privateData.split(", ")) {
              sb.append("PD" + s + ",");
            }
            // Delete last ','
            sb.deleteCharAt(sb.length() - 1);
          }
          sb.append("\n");
        }

        if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int partOf = (int) cellPartOf.getNumericCellValue();
          sb.append("\tService_Part S" + partOf);
          sb.append("\n");
        }

        sb.append("}");
        sb.append("\n\n");
      } else break;
    }
  }
  private void generateRecipientsRegion(Workbook wb, StringBuilder sb) {
    // Get the Recipients Sheet
    Sheet sheet = wb.getSheet("Recipients");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int id = (int) cellId.getNumericCellValue();
          Cell cellDescription = row.getCell(1);
          String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
          Cell cellScope = row.getCell(2);
          String scope = cellScope.getStringCellValue();

          if (scope.contains("/")) {
            scope = "Internal/External";
          } else {
            scope = scope.substring(0, 1).toUpperCase() + scope.substring(1);
          }

          Cell cellType = row.getCell(3);
          String type = cellType.getStringCellValue();

          if (type.contains("/")) {
            type = "Individual/Organization";
          } else {
            type = type.substring(0, 1).toUpperCase() + type.substring(1);
          }

          Cell cellPartOf = row.getCell(4);
          sb.append("Recipient R" + id + " {");
          sb.append("\n");
          sb.append("\tName \"" + description + "\"");
          sb.append("\n");
          sb.append("\tDescription \"" + description + "\"");
          sb.append("\n");

          if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            int partOf = (int) cellPartOf.getNumericCellValue();
            sb.append("\tRecipient_Part R" + partOf);
            sb.append("\n");
          }

          sb.append("\tScope " + scope);
          sb.append("\n");
          sb.append("\tType " + type);
          sb.append("\n}");
          sb.append("\n\n");
        }
      } else break;
    }
  }
Esempio n. 18
0
 private Cliente createCliente(Row posicao) {
   return new Cliente(
       posicao.getCell(6).getStringCellValue(),
       posicao.getCell(2).getStringCellValue(),
       posicao.getCell(0).getStringCellValue(),
       posicao.getCell(1).getStringCellValue(),
       posicao.getCell(3).getStringCellValue(),
       new Date(posicao.getCell(4).getStringCellValue()),
       posicao.getCell(5).getStringCellValue());
 }
 /**
  * @param header
  * @param codateRow
  */
 private void populateStartDate(ProgramHeaderTO header, Row codateRow) {
   if (codateRow.getCell(ProgramCols.EVENT_DATE.getCell() + 1) != null) {
     String string = codateRow.getCell(ProgramCols.EVENT_DATE.getCell() + 1).toString();
     SimpleDateFormat format = new SimpleDateFormat(DD_MMM_YYYY);
     try {
       Date date = format.parse(string);
       header.setProgramStartDate(date);
     } catch (ParseException e) {
       header.setProgramRawStartDate(string);
     }
   }
 }
 public static String setOd() throws Exception {
   String od = Dater.getUnique();
   POIFSFileSystem fs =
       new POIFSFileSystem(new FileInputStream(ShippingRequestValueContainerCola.file_path));
   HSSFWorkbook wb = new HSSFWorkbook(fs);
   Sheet sheet1 = wb.getSheetAt(0);
   Row row1 = sheet1.getRow(start);
   row1.getCell(clmnod).setCellValue(od);
   row1.getCell(clmncomment).setCellValue(od);
   wb.write(new FileOutputStream(ShippingRequestValueContainerCola.file_path));
   return od;
 }
Esempio n. 21
0
 public int getTotalLength(String sheetName) {
   ArrayList<Row> rows = getRows(sheetName);
   int totalLength = 0;
   for (Row row : rows) {
     if (!row.getCell(0).getStringCellValue().equals("")) {
       totalLength += row.getCell(1).getNumericCellValue();
     } else {
       break;
     }
   }
   return totalLength;
 }
 private String validateRow(Row row) {
   for (int i = 0; i < NUM_COLS; i++) {
     Cell cell = row.getCell(i);
     if (cell == null) {
       if (REQUIRED[i]) {
         return "Required cell "
             + HEADER_TITLES[i]
             + " missing for row "
             + String.valueOf(row.getRowNum());
       }
     } else {
       if (i == DECLARED_LICENSE_COL || i == CONCLUDED_LICENSE_COL) {
         try {
           SPDXLicenseInfoFactory.parseSPDXLicenseString(cell.getStringCellValue());
         } catch (SpreadsheetException ex) {
           if (i == DECLARED_LICENSE_COL) {
             return "Invalid declared license in row "
                 + String.valueOf(row.getRowNum())
                 + " detail: "
                 + ex.getMessage();
           } else {
             return "Invalid seen license in row "
                 + String.valueOf(row.getRowNum())
                 + " detail: "
                 + ex.getMessage();
           }
         }
       } else if (i == LICENSE_INFO_IN_FILES_COL) {
         String[] licenses =
             row.getCell(LICENSE_INFO_IN_FILES_COL).getStringCellValue().split(",");
         if (licenses.length < 1) {
           return "Missing licenss information in files";
         }
         for (int j = 0; j < licenses.length; j++) {
           try {
             SPDXLicenseInfoFactory.parseSPDXLicenseString(cell.getStringCellValue().trim());
           } catch (SpreadsheetException ex) {
             return "Invalid license information in row "
                 + String.valueOf(row.getRowNum())
                 + " detail: "
                 + ex.getMessage();
           }
         }
       }
       //				if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
       //					return "Invalid cell format for "+HEADER_TITLES[i]+" for forw
       // "+String.valueOf(row.getRowNum());
       //				}
     }
   }
   return null;
 }
 public static void setDateLoadUnload(String dateLoad, String dateUnload) throws Exception {
   try {
     POIFSFileSystem fs =
         new POIFSFileSystem(new FileInputStream(ShippingRequestValueContainerCola.file_path));
     HSSFWorkbook wb = new HSSFWorkbook(fs);
     Sheet sheet1 = wb.getSheetAt(0);
     Row row1 = sheet1.getRow(start);
     row1.getCell(clmndateLoad).setCellValue(dateLoad);
     row1.getCell(clmndateUnload).setCellValue(dateUnload);
     wb.write(new FileOutputStream(ShippingRequestValueContainerCola.file_path));
   } catch (IOException e) {
     System.out.println("setDateLoadUnload");
   }
 }
Esempio n. 24
0
  /**
   * read excel Xls and add the result into arraylist.
   *
   * @param sheetName excel sheet name
   * @throws RuntimeException
   */
  public List<Map<String, String>> excelToList(String sheetName) {
    Row firstxlRow = null;
    FileInputStream fso = null;
    List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        return null;
      }
      firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
      int firstCell = firstxlRow.getFirstCellNum();
      int lastCell = firstxlRow.getLastCellNum();
      List<String> keyList = new ArrayList<String>();

      for (int cNum = firstCell; cNum < lastCell; cNum++) {
        if (firstxlRow.getCell(cNum).toString() == null) {
          break;
        }
        keyList.add(firstxlRow.getCell(cNum).toString());
      }

      for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
        xlRow = xlSheet.getRow(i);
        List<String> valueList = new ArrayList<String>();
        if (xlRow == null) {
          break;
        }
        for (int j = firstCell; j < lastCell; j++) {
          xlCell = xlRow.getCell(j);
          if (xlCell == null) {
            valueList.add(null);
            continue;
          } else {
            valueList.add(xlCell.toString());
          }
        }
        paraList.add(creatMap(keyList, valueList));
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
  private int getUserFormXSheet(Sheet userSheet) {
    int count = 0;
    if (userSheet == null) return 0;
    try {
      int rowStart = userSheet.getFirstRowNum();
      int rowEnd = userSheet.getLastRowNum();
      for (int i = rowStart + 1; i <= rowEnd; i++) {
        Row row = userSheet.getRow(i);

        User user = new User();

        String strid = String.valueOf(getXssCellData(row.getCell(0)));
        if ("".equals(strid)) continue;
        int id = Integer.parseInt(strid);

        String name = (String) getXssCellData(row.getCell(1));
        String address = (String) getXssCellData(row.getCell(2));

        String str_borthday = getXssCellData(row.getCell(3)).toString();
        Date borthday = null;
        if (!"".equals(str_borthday)) {
          try {
            borthday = new SimpleDateFormat("yyyy-MM-dd").parse(str_borthday);
          } catch (Exception e) {

          }
        }

        String gender = (String) getXssCellData(row.getCell(4));

        user.setId(id);
        user.setName(name);
        user.setAddress(address);
        user.setBirthday(borthday);
        if ("男".equals(gender)) {
          user.setGender("男");
        }

        simpleDao.createEntity(user);
        count++;
      }

    } catch (Exception e) {

      e.printStackTrace();
      log.error("getUserFormXSheet exception !");
    }
    return count;
  }
Esempio n. 26
0
  /**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
Esempio n. 27
0
 private Cell getCell(int rowNums, Integer index) {
   Row row = this.sheet.getRow(rowNums);
   if (row == null) {
     return null;
   }
   return row.getCell(index);
 }
  public void parseData(
      final Sheet sheet,
      final int startRow,
      final int endColumn,
      final List<String> propertyNames,
      final Class clazzOfTestCase) {

    int rowCounter = startRow;

    while (!isBlank(sheet, rowCounter, endColumn)) {
      Row row = sheet.getRow(rowCounter - 1);
      for (int i = 0; i < endColumn; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
          try {
            Method method = clazzOfTestCase.getMethod("set" + propertyNames.get(i), Object.class);
          } catch (NoSuchMethodException e) {
            e
                .printStackTrace(); // To change body of catch statement use File | Settings | File
                                    // Templates.
          }
        }
      }
    }
  }
  @Test
  public void dataDrivernTest() throws IOException {
    FileInputStream f =
        new FileInputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");

    XSSFWorkbook wb = new XSSFWorkbook(f);
    XSSFSheet ws = wb.getSheet("Sheet1");
    Iterator<Row> row = ws.iterator();
    row.next();

    WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
    RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
    RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
    wm.menu.register();
    while (row.hasNext()) {
      Row r = row.next();
      rm1.contactInformation(r);
      boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
      if (result == true) {
        r.createCell(12).setCellValue("Passed");
      } else {
        r.createCell(12).setCellValue("Failed");
      }
      driver.navigate().back();
    }
    FileOutputStream f1 =
        new FileOutputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
    wb.write(f1);
    f1.close();
    driver.quit();
  }
Esempio n. 30
0
  /**
   * Compute width of a column based on a subset of the rows and return the result
   *
   * @param sheet the sheet to calculate
   * @param column 0-based index of the column
   * @param useMergedCells whether to use merged cells
   * @param firstRow 0-based index of the first row to consider (inclusive)
   * @param lastRow 0-based index of the last row to consider (inclusive)
   * @return the width in pixels
   */
  public static double getColumnWidth(
      Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
    AttributedString str;
    TextLayout layout;

    Workbook wb = sheet.getWorkbook();
    DataFormatter formatter = new DataFormatter();
    Font defaultFont = wb.getFontAt((short) 0);

    str = new AttributedString(String.valueOf(defaultChar));
    copyAttributes(defaultFont, str, 0, 1);
    layout = new TextLayout(str.getIterator(), fontRenderContext);
    int defaultCharWidth = (int) layout.getAdvance();

    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
      Row row = sheet.getRow(rowIdx);
      if (row != null) {

        Cell cell = row.getCell(column);

        if (cell == null) {
          continue;
        }

        double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
        width = Math.max(width, cellWidth);
      }
    }
    return width;
  }