Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getRow.

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public List<User> getUsers() throws Exception {
    List<User> result = new ArrayList<>();
    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        int amountRows = sheet.getPhysicalNumberOfRows();

        for (int i = HEADER_HEIGHT; i < amountRows; i++) {
            User user = new User();

            Row row = sheet.getRow(i);
            Cell cell = row.getCell(COLUMN_FIO);
            String fio = cell.getStringCellValue().trim();
            if (fio != null && !fio.isEmpty()) {
                user.setFIO(fio);//from   ww  w  .  ja va2 s.  co  m
                user.setRow(i);
                result.add(user);
            }

        }
    } catch (Throwable ex) {
        String message = "  ??   ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public void write(List<User> listUser) throws Exception {

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        for (User user : listUser) {

            int row = user.getRow();
            UserInfo info = user.getInfo();

            if (info != null) {
                Long amountLetters = info.getAMOUNT_LETTERS();
                Long hirsh = info.getHIRSH();
                Double impactPublish = info.getIMPACT_PUBLISH();

                if (amountLetters != null) {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS).setCellValue(amountLetters);
                } else {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }/*  w  w w  .  ja va2s  .co m*/

                if (hirsh != null) {
                    sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(hirsh);
                } else {
                    sheet.getRow(row).getCell(COLUMN_HIRSH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }

                if (impactPublish != null) {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH).setCellValue(impactPublish);
                } else {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }
            } else {
                sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
            }
        }

        try (FileOutputStream os = new FileOutputStream(filePath)) {
            wb.write(os);
        }
    } catch (Throwable ex) {
        String message = "  ?     ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
}

From source file:com.arg.arsoft.siantluis.web.controllers.ClaimController.java

@RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
@Transactional// ww  w .j a  v a2s . com
public @ResponseBody String upload(MultipartFile file) throws Exception {

    if (file != null) {
        System.out.println(file.getOriginalFilename());

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        for (int index = 1; index < sheet.getPhysicalNumberOfRows(); index++) {
            Row row = sheet.getRow(index);
            String code = row.getCell(0).getStringCellValue();
            ClaimUpload entity = cuRepository.findByCode(code);

            if (entity == null) {
                entity = new ClaimUpload();
                entity.setId(0);
                entity.setCode(code);
            }
            System.out.print(entity.getId());
            entity.setDesc(row.getCell(1).getStringCellValue());
            entity.setClaimDate(row.getCell(2).getStringCellValue());
            entity.setClaimTime(row.getCell(3).getStringCellValue());
            entity.setClaimLocation(row.getCell(4).getStringCellValue());
            entity.setClaimReason(row.getCell(5).getStringCellValue());
            cuRepository.save(entity);

        }

    }
    return "Success";
}

From source file:com.asakusafw.testdata.generator.excel.ExcelTesterRoot.java

License:Apache License

/**
 * Obtains the cell./*from www.  j a  v a  2  s . c  om*/
 * @param sheet the sheet
 * @param rowIndex row index
 * @param columnIndex column index
 * @return cell string
 */
protected String cell(Sheet sheet, int rowIndex, int columnIndex) {
    Row row = sheet.getRow(rowIndex);
    assertThat(row, not(nullValue()));
    Cell cell = row.getCell(columnIndex);
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    }
    assertThat(cell.getCellType(), is(Cell.CELL_TYPE_STRING));
    return cell.getStringCellValue();
}

From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

private void adjustDataWidth(Sheet sheet) {
    assert sheet != null;
    int lastColumn = sheet.getRow(0).getLastCellNum();
    adjustColumnWidth(sheet, lastColumn);
}

From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

private Cell getCell(Sheet sheet, int rowIndex, int columnIndex) {
    assert sheet != null;
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        row = sheet.createRow(rowIndex);
    }/*from   w  w w.  ja v a  2 s  . co m*/
    Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
    return cell;
}

From source file:com.asakusafw.testdata.generator.excel.WorkbookGeneratorTest.java

License:Apache License

/**
 * many columns./* w  w w  .  ja  v  a  2  s .  c om*/
 * @throws Exception if occur
 */
@Test
public void many_columns() throws Exception {
    ModelDeclaration model = load("many_columns.dmdl", "many_columns");
    WorkbookGenerator generator = new WorkbookGenerator(folder.getRoot(), WorkbookFormat.DATA);

    generator.generate(model);
    Workbook workbook = open(folder.getRoot(), model, WorkbookFormat.DATA);

    Sheet sheet = workbook.getSheet(WorkbookFormat.DATA.getSheets().get(0).getName());
    assertThat(sheet.getRow(0).getLastCellNum(), is((short) SpreadsheetVersion.EXCEL97.getMaxColumns()));
}

From source file:com.asakusafw.testdata.generator.excel.WorkbookGeneratorTest.java

License:Apache License

/**
 * many columns using xlsx.//from w w  w.j a v  a  2 s  .  co m
 * @throws Exception if occur
 */
@Test
public void many_columnsx() throws Exception {
    ModelDeclaration model = load("many_columns.dmdl", "many_columns");
    WorkbookGenerator generator = new WorkbookGenerator(folder.getRoot(), WorkbookFormat.DATAX);

    generator.generate(model);
    Workbook workbook = open(folder.getRoot(), model, WorkbookFormat.DATAX);

    Sheet sheet = workbook.getSheet(WorkbookFormat.DATA.getSheets().get(0).getName());
    assertThat(sheet.getRow(0).getLastCellNum(), is((short) 300));
}

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

private String getStringCell(Sheet sheet, int rowIndex, int colIndex) {
    assert sheet != null;
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        return "?"; //$NON-NLS-1$
    }/*from w  w  w .  ja va 2  s  .  c o m*/
    Cell cell = row.getCell(colIndex);
    if (cell == null || cell.getCellType() != Cell.CELL_TYPE_STRING) {
        return "?"; //$NON-NLS-1$
    }
    return cell.getStringCellValue();
}

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractorTest.java

License:Apache License

/**
 * name.//www .  j  a v  a2 s .c  om
 * @throws Exception if occur
 */
@Test
public void extractName() throws Exception {
    ExcelRuleExtractor extractor = new DefaultExcelRuleExtractor();
    Sheet sheet = sheet("name.xls");
    assertThat(extractor.extractName(sheet.getRow(3)), is("value"));
    assertThat(extractor.extractName(sheet.getRow(4)), is("a"));
    assertThat(extractor.extractName(sheet.getRow(5)), is("very_long_name"));
}