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);
    }
  }
Example #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 InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("sheet1");
    CellStyle headerStyle = getHeaderStyle(wb);
    CellStyle firstCellStyle = getFirsetCellStyle(wb);
    CellStyle commonCellStyle = getCommonCellStyle(wb);
    CellStyle amtCellStyle = getAmtCellStyle(wb);

    for (int i = 0; i < LENGTH_9; i++) {
      sheet1.setColumnWidth(i, STR_15 * STR_256);
    }

    // 表头
    Row row = sheet1.createRow(0);
    row.setHeightInPoints(STR_20);

    Cell cell = headInfo(headerStyle, row);

    if (detailList.size() == 0) {
      row = sheet1.createRow(1);
      cell = row.createCell(0);
      cell.setCellValue(NO_RECORD);
    } else {
      fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
    }
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    wb.write(outputStream);
    InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
    outputStream.close();
    return inputStream;
  }
  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));
    }
  }
Example #5
0
  @Override
  public List<Map<String, Object>> parse() throws ReadCensusException {
    XSSFWorkbook listaVotantes = null;
    String fileName = Paths.get(filePath).getFileName().toString();

    try {

      FileInputStream file = new FileInputStream(new File(filePath));
      listaVotantes = new XSSFWorkbook(file);

      XSSFSheet hoja = listaVotantes.getSheetAt(0);
      Iterator<Row> rowIterator = hoja.iterator();
      Row row;

      while (rowIterator.hasNext()) {
        row = rowIterator.next();
        Iterator<Cell> columns = row.cellIterator();

        if (!columns.hasNext()) continue;

        try {
          voter = new HashMap<String, Object>();
          voter.put("name", columns.next().getStringCellValue());
          voter.put("email", columns.next().getStringCellValue());
          voter.put("nif", columns.next().getStringCellValue());
          voter.put("code", String.valueOf((int) columns.next().getNumericCellValue()));
          voter.put("password", PasswordGenerator.generate(8));
          voter.put("file", fileName);
          voter.put("line", row.getRowNum());
        } catch (Exception e) {
          throw new ReadCensusException(
              "[ERROR] ["
                  + fileName
                  + ":"
                  + row.getRowNum()
                  + "] El usuario no tiene el formato correcto");
        }

        voters.add(voter);
      }

    } catch (FileNotFoundException e) {
      throw new ReadCensusException("[ERROR] [" + fileName + "] El fichero no existe");
    } catch (Exception e) {
      throw new ReadCensusException(
          "[ERROR] [" + fileName + "] Fallo inesperado al leer el fichero: " + e.getMessage());
    } finally {
      try {
        if (listaVotantes != null) listaVotantes.close();
      } catch (Exception e) {
        throw new ReadCensusException("[ERROR] [" + fileName + "] I/O Error: " + e.getMessage());
      }
    }

    if (voters.isEmpty()) {
      throw new ReadCensusException("[AVISO] [" + fileName + "] El censo está vacío");
    }

    return voters;
  }
  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);
    }
  }
  @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();
  }
  /**
   * 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;
  }
 /** 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;
 }
 /**
  * Forms report in excel file. Sum weight. Sum volume etc.
  *
  * @param sheet Sheet where write the items
  * @param c Container from where take the info
  * @param lastRow last row after all items were written method uses lastRow + 2 to write report in
  *     next 2 rows after all items of the container were written
  */
 private void setReport(Container c, int lastRow) {
   String[] reportHeadings = {
     "Суммарный вес", // 6
     "Суммарный объем", // 7
     "Остаток вес", // 8
     "Остаток объем" // 9
   };
   double[] values = {
     c.getWeight(),
     c.getVolume(),
     c.getWeightLimit() - c.getWeight(),
     c.getVolumeLimit() - c.getVolume()
   };
   int dataCell = 5;
   int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
   lastRow++;
   Row headings = outputSheet.createRow(lastRow);
   lastRow += 2;
   Row data = outputSheet.createRow(lastRow);
   for (String report : reportHeadings) {
     Cell heading = headings.createCell(dataCell);
     heading.setCellValue(report);
     Cell dataCellValue = data.createCell(dataCell);
     dataCellValue.setCellValue(values[valuesIndex]);
     dataCell++;
     valuesIndex++;
   }
 }
Example #11
0
  public void format() {

    wb = new XSSFWorkbook();

    Map styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 768);
    sheet.setColumnWidth(1, 768);
    sheet.setColumnWidth(2, 2816);
    sheet.setColumnWidth(3, 3584);
    sheet.setColumnWidth(4, 3584);
    sheet.setColumnWidth(5, 3584);
    sheet.setColumnWidth(6, 3584);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35F);
    for (int i = 1; i <= 7; i++)
      titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));

    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple");
  }
Example #12
0
 private static void createRow(
     Sheet sheet,
     Map<String, Object> rowData,
     List<String> mapping,
     List<CellStyle> styles,
     int startRowNum) {
   Row row = sheet.createRow(startRowNum);
   for (int i = 0; i < mapping.size(); i++) {
     String name = mapping.get(i);
     Object obj = rowData.get(name);
     Cell newCell = row.createCell(i);
     CellStyle style = styles.get(i);
     newCell.setCellStyle(style);
     if (obj != null) {
       if (obj instanceof Date) {
         newCell.setCellValue((Date) obj);
       } else if (obj instanceof BigDecimal) {
         double dd = ((BigDecimal) obj).doubleValue();
         newCell.setCellValue(dd);
       } else {
         newCell.setCellValue(obj.toString());
       }
     }
   }
 }
Example #13
0
  public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;
    try {
      in = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(in);
      Sheet sheet = workbook.getSheetAt(0);
      Name name = workbook.getName("雷線基準日");
      CellReference cellRef = new CellReference(name.getRefersToFormula());
      Row row = sheet.getRow(cellRef.getRow());
      Cell baseDateCell = row.getCell(cellRef.getCol());
      // System.out.println("cellが日付か:"
      // + PoiUtil.isCellDateFormatted(baseDateCell));
      Date baseDate = baseDateCell.getDateCellValue();
      return baseDate;

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (in != null)
        try {
          in.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
    }
    return null;
  }
  public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
    List<Specialite> listSpecialite = new ArrayList<>();
    FileInputStream file = new FileInputStream(new File(excelFilePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);

    // Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
    Sheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
      Row nextRow = iterator.next();
      Iterator<Cell> cellIterator = nextRow.cellIterator();
      Specialite sp = new Specialite();
      Admin admin = new Admin();

      while (cellIterator.hasNext()) {
        Cell nextCell = cellIterator.next();
        int columnIndex = nextCell.getColumnIndex();

        switch (columnIndex) {
          case 0:
            sp.setIntitule((String) getCellValue(nextCell));
            break;
        }
      }
      listSpecialite.add(sp);
    }

    file.close();

    return listSpecialite;
  }
Example #15
0
  @Override
  public List<String> getHeaderColumns() {
    if (headerColumns == null) {
      headerColumns = new ArrayList<>();
      Row row = dataSheet.getRow(0);

      int colCount = row != null ? row.getLastCellNum() : 0;

      if (row == null || colCount == 0) {
        LogMgr.logError(
            "ExcelReader.getHeaderColumns()",
            "Cannot retrieve column names because no data is available in the first row of the sheet: "
                + dataSheet.getSheetName(),
            null);
        String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
        messages.append(msg);
        messages.appendNewLine();
        return headerColumns;
      }

      for (int i = 0; i < colCount; i++) {
        Cell cell = row.getCell(i);
        Object value = getCellValue(cell);

        if (value != null) {
          headerColumns.add(value.toString());
        } else {
          headerColumns.add("Col" + Integer.toString(i));
        }
      }
    }
    return headerColumns;
  }
Example #16
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();
   }
 }
Example #17
0
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
Example #18
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
Example #19
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;
  }
	@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();
			}
		}
	}
Example #21
0
 @Override
 public void writeRow(List<?> row) {
   Row record = sheet.createRow(sheet.getLastRowNum() + 1);
   for (int i = 0; i < row.size(); i++) {
     Cell cell = record.createCell(i);
     Object value = row.get(i);
     if (value == null) {
       cell.setCellValue("");
     } else if (value instanceof String) {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue((String) value);
     } else if (value instanceof Number) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellValue(converters.convert(value, Double.class));
     } else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellStyle(dateCellStyle);
       cell.setCellValue(converters.convert(value, Date.class));
     } else if (value instanceof Boolean) {
       cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
       cell.setCellValue((Boolean) value);
     } else {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue(converters.convert(value, String.class));
     }
   }
 }
Example #22
0
  private static void retrieveSheetInformation(Sheet sheet) {
    // every sheet has rows, iterate over them
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {

      // Get the row object
      Row row = rowIterator.next();

      // Every row has columns, get the column iterator and
      // iterate over them
      Iterator<Cell> cellIterator = row.cellIterator();
      StringBuffer sb = new StringBuffer();

      while (cellIterator.hasNext()) {
        // Get the Cell object
        Cell cell = cellIterator.next();

        // check the cell type and process accordingly
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            sb.append(cell.getStringCellValue()).append(" $ ");
            break;
          case Cell.CELL_TYPE_NUMERIC:
            sb.append(cell.getNumericCellValue()).append("  ");
        }
      } // end of cell iterator

      System.out.println(sb.toString());
    } // end of rows iterator
  }
  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.
          }
        }
      }
    }
  }
  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();
  }
Example #25
0
 private Cell getCell(int rowNums, Integer index) {
   Row row = this.sheet.getRow(rowNums);
   if (row == null) {
     return null;
   }
   return row.getCell(index);
 }
  @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 static void main(String args[]) throws Exception {
    File file = new File("d:/delete/createworkbook.xlsx");
    FileInputStream fis = new FileInputStream(file);
    if (file.isFile() && file.exists()) {
      // Get the workbook instance for XLSX file
      XSSFWorkbook workbook = new XSSFWorkbook(fis);
      System.out.println("openworkbook.xlsx file open successfully.");
      // get worksheet, 0 based
      XSSFSheet sheet = workbook.getSheetAt(0);
      // get rows
      Iterator<Row> it = sheet.iterator();
      while (it.hasNext()) {
        Row row = (XSSFRow) it.next();
        // get cells
        Iterator<Cell> cellIt = row.cellIterator();
        while (cellIt.hasNext()) {
          Cell cell = cellIt.next();
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              System.out.print(cell.getNumericCellValue() + " \t\t ");
              break;
            case Cell.CELL_TYPE_STRING:
              System.out.print(cell.getStringCellValue() + " \t\t ");
              break;
          }
        }
      }

    } else {
      System.out.println("Error to open openworkbook.xlsx file.");
    }
  }
Example #28
0
  public String readCGCell(Row row, int cellIdx, DataDto dto, CellType cellType) {
    Cell cell = row.getCell(cellIdx);
    if (null == cell) {
      log.warn(
          "Table import. Table "
              + metaData.getTableName()
              + " Cell at row: "
              + (row.getRowNum() + 1)
              + " col: "
              + (cellIdx + 1)
              + " current value: "
              + dto.getConfidenceGrade()
              + " cell is null.");
      return null;
    }
    if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
      log.warn(
          "Table import. Table "
              + metaData.getTableName()
              + " Cell at row: "
              + (row.getRowNum() + 1)
              + " col: "
              + (cellIdx + 1)
              + " current value: "
              + dto.getConfidenceGrade()
              + " CG cell is not of type String.");
      return null;
    }

    RichTextString cg = cell.getRichStringCellValue();
    return cg.getString();
  }
Example #29
0
  public void loadData(Data data)
      throws EncryptedDocumentException, InvalidFormatException, IOException {

    FileInputStream file = new FileInputStream(new File(path));
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
    org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
    try {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        ArrayList<String> arrayRow = new ArrayList<String>();
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          testDataType(cell, arrayRow);
        }
        data.getData().add(arrayRow);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
    workbook.close();
    file.close();
    sheet = null;
    workbook = null;
    file = null;
  }
  @Override
  public String getPriceDate(String fileName) {
    try {
      HSSFSheet sheet = getSheet(fileName, 0);

      String marker = "Дата";

      int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows();

      String dirtyDate = null;

      for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) {
        Row row = sheet.getRow(i);
        if (row.getPhysicalNumberOfCells() > 1) {
          Cell cell = row.getCell(0);
          if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String stringCellValue = cell.getStringCellValue();
            if (stringCellValue.contains(marker)) {
              dirtyDate = stringCellValue;
              break;
            }
          }
        }
      }
      if (dirtyDate != null) {
        return dirtyDate.trim();
      }

    } catch (Exception ex) {
      LOGGER.error("FAIL to get price date - return today");
      return new Date().toString();
    }
    return new Date().toString();
  }