Example usage for org.apache.poi.ss.usermodel Row getLastCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getLastCellNum

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:com.rapidminer.operator.nio.Excel2007SheetTableModel.java

License:Open Source License

@Override
public int getColumnCount() {
    if (config != null) {
        return config.getColumnLast() - config.getColumnOffset() + 1;
    } else {//from ww w .  j  a v  a 2 s . c  o  m
        Row row = sheet.getRow(sheet.getFirstRowNum());
        if (row == null) {
            return 0;
        } else {
            return row.getLastCellNum();
        }
    }
}

From source file:com.read.main.LeerPDF.java

/**
 * @param args the command line arguments
 *///from w  w w  . ja va 2  s. com
public static void main(String[] args) throws IOException {
    try {

        FileInputStream file = new FileInputStream(new File("/home/aaron/Escritorio/Example.xlsx"));
        XSSFWorkbook workbook2 = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook2.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

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

            System.out.println("Numero de Columnas: " + row.getLastCellNum());

            System.out.println(row.getRowNum());

            if (row.getRowNum() == 0) {
                System.out.println("Fila Cero");
            } else {

                int numColumna = 0;

                while (numColumna < row.getLastCellNum()) {

                    Cell cell = row.getCell(numColumna);

                    try {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(numColumna + ".- BOOLEAN: ");
                            System.out.print(cell.getBooleanCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(numColumna + ".- NUMERIC: ");
                            System.out.print(cell.getNumericCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(numColumna + ".- STRING: ");
                            System.out.print(cell.getStringCellValue() + "\t\t");
                            break;
                        }
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                    ;

                    numColumna++;
                }
            }

            System.out.println("");
        }
        file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.rodrigodev.xgen4j_table_generator.test.common.assertion.excel.conditions.ExcelFile.java

License:Open Source License

@Override
public boolean matches(InputStream actualInputStream) {

    boolean result = true;

    try {//from  w ww.j  av a 2s.co m
        try (Workbook expectedWb = WorkbookFactory.create(expectedInputStream)) {
            Sheet expectedSheet = expectedWb.getSheetAt(0);

            try (Workbook actualWb = WorkbookFactory.create(actualInputStream)) {
                Sheet actualSheet = actualWb.getSheetAt(0);

                int expectedRowCount = expectedSheet.getLastRowNum();
                for (int r = 0; r <= expectedRowCount; r++) {
                    Row expectedRow = expectedSheet.getRow(r);
                    Row actualRow = actualSheet.getRow(r);
                    if (actualRow == null) {
                        actualRow = actualSheet.createRow(r);
                    }

                    int expectedCellCount = expectedRow.getLastCellNum();
                    for (int c = 0; c < expectedCellCount; c++) {
                        Cell expectedCell = expectedRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
                        Cell actualCell = actualRow.getCell(c, Row.CREATE_NULL_AS_BLANK);

                        if (expectedCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            assertThat(actualCell.getNumericCellValue())
                                    .isEqualTo(expectedCell.getNumericCellValue(), offset(0.00001));
                        } else {
                            expectedCell.setCellType(Cell.CELL_TYPE_STRING);
                            actualCell.setCellType(Cell.CELL_TYPE_STRING);
                            assertThat(actualCell.getStringCellValue())
                                    .isEqualTo(expectedCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (AssertionError error) {
        describedAs(error.getMessage());
        result = false;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    return result;
}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void autoSize() {
    Row row = this.errorSheet.getRow(HEADER_COLUMN);
    if (row != null) {
        for (short s = row.getFirstCellNum(); s < row.getLastCellNum(); s++) {
            this.errorSheet.autoSizeColumn(s);
        }//from w ww .j av a2s .c o m
    }
}

From source file:com.siberhus.tdfl.excel.DefaultExcelRowReader.java

License:Apache License

private String[] getStringValues(Row row) {
    String values[] = null;//from   w ww .j  a v a2s. c o  m
    values = new String[row.getLastCellNum()];
    for (int i = 0; i < row.getLastCellNum(); i++) {
        values[i] = getCellValueAsString(row.getCell(i));
    }
    return values;
}

From source file:com.smanempat.view.ExcelReading.java

public static void echoAsCSV(Sheet sheet) {
    Row row = null;
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);/*from w  ww  .j a v a 2 s  .co  m*/
        for (int j = 0; j < row.getLastCellNum(); j++) {
            System.out.print("\"" + row.getCell(j) + "\";");
        }
        System.out.println();
    }
}

From source file:com.smegi.bonusi.model.Excel.java

public void getExcelBonuses(int month) {
    Calendar calendar = Calendar.getInstance();
    calendar.set(2016, month - 1, Calendar.DAY_OF_MONTH);
    try {//from   w ww .  j  a v a 2 s.  c o  m
        FileInputStream inputStream = new FileInputStream(new File(path));
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(month - 1);

        for (int i = 2; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            String username = "";
            int bonuses = 0;
            for (int j = 1; j < row.getLastCellNum(); j++) {
                //getting username

                Cell cell = row.getCell(j);
                if (j == 1) {
                    username = cell.toString();
                    continue;
                }

                if (cell.toString() != "") {
                    bonuses++;
                }
            }

            // updating user in userslist
            for (User user : users) {
                if (user.getName().equalsIgnoreCase(username)) {
                    //                        System.out.println("Found username in base: " + username);
                    //                        System.out.println("    - adding bonuses: " + bonuses);
                    StringBuilder sb = new StringBuilder();
                    sb.append(calendar.get(Calendar.YEAR)).append("-").append((calendar.get(Calendar.MONTH)));
                    user.addExcelBonusi(sb.toString(), bonuses);
                    break;
                }
            }

        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.ssic.education.provider.controller.ProSupplierController.java

/**
 * ??//from w ww  . j av a 2 s.co m
 * 
 * @param file
 * @param request
 * @param response
 * @return
 * @author chenminghai
 * @throws IOException
 */
@RequestMapping("/supplierImport")
@ResponseBody
public Json supplierImport(MultipartFile file, HttpServletRequest request, HttpServletResponse response)
        throws IOException {
    Json j = new Json();
    SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME);
    // ???id
    String supplierId = info.getSupplierId();
    String errorMsg = null;
    Map<String, Map<ProSupplierReceiver, ProSupplier>> map = new HashMap();
    Set<String> set = new HashSet();
    try (Workbook wb = WorkbookFactory.create(file.getInputStream());) {
        Sheet sheet = wb.getSheetAt(0);
        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d");
        if (sheet == null) {
            return null;
        }
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            if (errorMsg != null) {
                break;
            }
            Map<ProSupplierReceiver, ProSupplier> suppliers = new HashMap();
            ProSupplier supplier = null;
            ProSupplierReceiver psr = null;
            Row row = sheet.getRow(rowNum);
            int n = 0;
            for (int i = 0; i < row.getLastCellNum(); i++) {
                if (errorMsg != null) {
                    break;
                }
                Cell cell = row.getCell(i);
                String value = ParseExcelUtil.getStringCellValue(cell);
                if (value != null) {
                    value = value.trim();
                }
                if (i == 0) {
                    // ??
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "???????";
                        break;
                    }
                    ProSupplier s = supplierService.findProSupplierByName(value, supplierId);
                    if (s != null) {
                        errorMsg = "" + (rowNum + 1) + "??????";
                        break;
                    }
                    if (map.get(value) != null) {
                        errorMsg = "" + (rowNum + 1) + "????????";
                        break;
                    }
                    supplier = new ProSupplier();
                    supplier.setSupplierName(value);
                    supplier.setCreateTime(now);
                    supplier.setUpdater(info.getId());
                    supplier.setLastUpdateTime(now);
                    supplier.setStat(1);
                } else if (i == 1) {
                    // ???
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "??????";
                        break;
                    }
                    supplier.setAddress(value);
                    //               } else if (i == 2) {
                    //                  // ?
                    //                  if (StringUtils.isBlank(value)) {
                    //                     n += 1;
                    //                     break;
                    //                  }
                    //                  supplier.setFoodServiceCode(value);
                    //               } else if (i == 2) {
                    //                  if (StringUtils.isBlank(value)) {
                    //                     n += 1;
                    //                     break;
                    //                  }
                    //                  supplier.setFoodBusinessCode(value);
                } else if (i == 2) {
                    if (StringUtils.isBlank(value)) {
                        n += 1;
                        continue;
                    }
                    supplier.setFoodCirculationCode(value);
                } else if (i == 3) {
                    if (StringUtils.isBlank(value)) {
                        if (n == 1) {
                            errorMsg = "" + (rowNum + 1) + "???";
                        }
                        break;
                    }
                    supplier.setFoodProduceCode(value);
                    //               } else if (i == 6) {
                    //                  if (StringUtils.isBlank(value)) {
                    //                     if (n == 4) {
                    //                        errorMsg = "" + (rowNum + 1)
                    //                              + "?????";
                    //                     }
                    //                     break;
                    //                  }
                    //                  supplier.setBusinessLicense(value);
                    //               } else if (i == 7 && !StringUtils.isBlank(value)) {
                    //                  // ???
                    //                  int x = srService.findBySupplierCode(value, supplierId);
                    //                  if (x != 0) {
                    //                     errorMsg = "" + (rowNum + 1) + "?????";
                    //                     break;
                    //                  }
                    //                  if(value!=null&& !StringUtils.isBlank(value)){
                    //                     int s=set.size();
                    //                     set.add(value);
                    //                     if(s==set.size()){
                    //                        errorMsg = "" + (rowNum + 1) + "???????";
                    //                        break;
                    //                     }
                    //                  }
                    //                  psr = new ProSupplierReceiver();
                    //                  psr.setSupplierCode(value);
                } else if (i == 4) {
                    // ?
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "????";
                        break;
                    }
                    supplier.setCorporation(value);
                } else if (i == 5) {
                    // ??
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "????";
                        break;
                    }
                    supplier.setContactWay(value);
                }
            }
            if (supplier != null && errorMsg == null) {
                supplier.setId(UUID.randomUUID().toString());
                supplier.setSupplierType(0);
                supplier.setReviewed((byte) 1);
                if (psr == null) {
                    psr = new ProSupplierReceiver();
                }
                psr.setId(UUID.randomUUID().toString());
                psr.setSupplierId(supplier.getId());
                psr.setReceiverId(supplierId);
                psr.setCreateTime(new Date());
                psr.setLastUpdateTime(psr.getCreateTime());
                suppliers.put(psr, supplier);
                map.put(supplier.getSupplierName(), suppliers);
            }
        }
    } catch (EncryptedDocumentException | InvalidFormatException e) {
        errorMsg = "Excel??";
    }
    if (errorMsg != null) {
        j.setMsg(errorMsg);
        j.setSuccess(false);
    } else {
        int r = supplierService.importSupplier(map);
        j.setMsg("?" + r + "??");
        j.setSuccess(true);
    }
    return j;
}

From source file:com.ssic.education.provider.controller.WaresController.java

@RequestMapping(value = "/import")
@ResponseBody// ww  w  .  j a v  a 2s  .c o  m
/**
 * ?excel
 * 
 * @param file
 * @param request
 * @param response
 * @return
 * @author zhangjiwei
 * @since 2016.5.21
 */
public Json importExcel(@RequestParam("filename") MultipartFile file, HttpServletRequest request,
        HttpServletResponse response) throws IOException {
    Json j = new Json();
    SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME);
    // ?id
    String supplierId = info.getSupplierId();
    String errorMsg = null;
    // ?excellist
    List<ProWares> list = new ArrayList();
    Set<String> set = new HashSet();
    // ?excel
    try (Workbook wb = WorkbookFactory.create(file.getInputStream());) {
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return null;
        }

        Date now = new Date();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            if (errorMsg != null) {
                break;
            }
            ProWares dto = new ProWares();
            Row row = sheet.getRow(rowNum);
            for (int i = 0; i < row.getLastCellNum(); i++) {
                if (errorMsg != null) {
                    break;
                }
                Cell cell = row.getCell(i);
                String value = ParseExcelUtil.getStringCellValue(cell);
                if (value != null) {
                    value = value.trim();
                }
                if (i == 0) {
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "?????";
                        break;
                    }
                    // ???
                    dto.setWaresName(value);
                } else if (i == 1) {
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "??????";
                        break;
                    }
                    // ?
                    dto.setAmountUnit(value);
                } else if (i == 2) {
                    // ?
                    dto.setSpec(value);
                } else if (i == 3) {
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "???";
                        break;
                    }
                    // ?
                    try {
                        dto.setWaresType(ProductClass.fromName(value));
                        if (dto.getWaresType() == null) {
                            errorMsg = "" + (rowNum + 1) + "???";
                            break;
                        }
                    } catch (Exception e) {
                        errorMsg = "" + (rowNum + 1) + "???";
                        break;
                    }
                } else if (i == 4) {
                    // ?
                    if (StringUtils.isBlank(value)) {
                        errorMsg = "" + (rowNum + 1) + "????";
                        break;
                    }
                    dto.setManufacturer(value);
                    // ???
                    ProWares pw = waresService.findProWarsByNameSpecManu(dto.getWaresName(),
                            dto.getAmountUnit(), dto.getManufacturer(), supplierId);
                    if (pw != null) {
                        errorMsg = "" + (rowNum + 1) + "???";
                        break;
                    }
                    String mark = dto.getWaresName() + "," + dto.getAmountUnit() + "," + dto.getManufacturer();
                    int m = set.size();
                    set.add(mark);
                    if (m == set.size()) {
                        errorMsg = "" + (rowNum + 1) + "?????";
                        break;
                    }
                    //               } else if (i == 4 && StringUtils.isNotBlank(value)) {
                    //                  // ??
                    //                  dto.setEnName(value);
                    //               } else if (i == 5 && StringUtils.isNotBlank(value)) {
                    //                  // ???
                    //                  dto.setBarCode(value);
                    //               } else if (i == 6 && StringUtils.isNotBlank(value)) {
                    //                  // ??
                    //                  dto.setCustomCode(value);
                } else if (i == 5 && StringUtils.isNotBlank(value)) {
                    // ?
                    try {
                        dto.setShelfLife(Integer.parseInt(value));
                    } catch (Exception e) {
                        errorMsg = "" + (rowNum + 1) + "?????";
                        break;
                    }
                } else if (i == 6) {
                    // ???
                    if (dto.getShelfLife() != null) {
                        if (StringUtils.isBlank(value)) {
                            errorMsg = "" + (rowNum + 1) + "????";
                            break;
                        }
                        dto.setUnit(value);
                    } else {
                        if (StringUtils.isNotBlank(value)) {
                            dto.setShelfLife(0);
                            dto.setUnit(value);
                        }
                    }
                } else if (i == 7 && StringUtils.isNotBlank(value)) {
                    // 
                    dto.setPlace(value);
                }
            }
            if (errorMsg != null) {
                break;
            }
            dto.setSupplierId(supplierId);
            dto.setWay(0);
            dto.setDishes(false);
            dto.setCreator(info.getId());
            dto.setCreateTime(now);
            dto.setUpdater(info.getId());
            dto.setLastUpdateTime(now);
            dto.setStat(1);
            list.add(dto);
        }
    } catch (EncryptedDocumentException | InvalidFormatException e) {
        errorMsg = "Excel??";
    }

    if (errorMsg != null) {
        // TODO ???
        j.setMsg(errorMsg);
        j.setSuccess(false);
        return j;
    } else {
        // ?
        waresService.addProWares(list);
        j.setMsg("??");
        j.setSuccess(true);
        return j;
    }

}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }/*  w w  w.j  a  va2s  . c o  m*/

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}