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.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;//  w w w. j av a2 s. com
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static boolean rowHasData(Row row) {
    short cellNumber;
    boolean nonBlankRowFound = false;
    for (cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
        Cell cell = row.getCell(cellNumber);
        if (cell != null && row.getCell(cellNumber).getCellType() != cell.CELL_TYPE_BLANK) {
            nonBlankRowFound = true;// w  w  w  .  ja v a2 s  .  co  m
        }
    }
    return nonBlankRowFound;
}

From source file:com.jaspersoft.ireport.designer.connection.gui.XlsxDataSourceConnectionEditor.java

License:Open Source License

private void jButtonGetColumnsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGetColumnsActionPerformed
    try {//from  www . j a  v a2s  . c o  m

        if (jTextFieldFilename.getText().length() > 0) {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(jTextFieldFilename.getText())));
            Sheet sheet = workbook.getSheetAt(0);

            DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel();
            dtm.setRowCount(0);

            Row row = sheet.getRow(0);
            Map<String, Integer> columnNames = new HashMap<String, Integer>();
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                Cell cell = row.getCell(columnIndex);
                String columnName = "";
                if (cell != null) {
                    columnName = cell.toString();
                } else {
                    columnName = "COLUMN_" + columnIndex;
                }

                if (columnName != null && columnName.trim().length() > 0) {
                    dtm.addRow(new Object[] { columnName, new Integer(columnIndex) });
                }
            }

            jTable1.updateUI();

            jCheckBoxFirstRowAsHeader.setSelected(true);
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(this, ex.getMessage(),
                I18n.getString("XlsxDataSourceConnectionEditor.Message.Exception"), JOptionPane.ERROR_MESSAGE); //"message.title.exception"
    }
}

From source file:com.javabean.excel.ExcelDocumentReader.java

public List<String> GetColumnName(int sheetIndex) {
    List<String> columnNameList;
    Sheet sheet;//ww w .ja v  a  2s .c  om
    Cell cell;
    int columnCount;
    String stringTemp;
    sheet = GetSheet(sheetIndex);
    if (sheet == null) {
        return null;
    }
    Row row = sheet.getRow(0);
    columnCount = row.getLastCellNum();
    columnNameList = new ArrayList();
    for (int i = 0; i < columnCount; i++) {
        cell = row.getCell(i);
        stringTemp = Convert.NullToString(cell.getStringCellValue(), ReturnStringValue.NULL);
        columnNameList.add(stringTemp);
    }
    return columnNameList;
}

From source file:com.javabean.excel.ExcelDocumentReader.java

public List<List<String>> GetDataRowList(int sheetIndex) {
    List<List<String>> dataRowList;
    Sheet sheet;//w ww  . ja  va2  s .  c  o  m
    Row row;
    int rowCount;
    int columnCount;
    sheet = GetSheet(sheetIndex);
    if (sheet == null) {
        return null;
    }
    rowCount = sheet.getLastRowNum();
    row = sheet.getRow(0);
    columnCount = row.getLastCellNum();
    dataRowList = new ArrayList<>();
    for (int i = 0; i < rowCount + 1; i++) {
        List<String> dataRow = GetOneDataRow(sheet, i, columnCount);
        if (dataRow != null) {
            dataRowList.add(dataRow);
        }
    }
    return dataRowList;
}

From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java

@Override
public ResponseValidator validateDocument(InputStream inputStream) {
    ResponseValidator response;//w  ww. ja va2  s .co  m
    StringBuilder stringBuilder = new StringBuilder();
    if (isReadyToValidate() && inputStream != null) {
        HashMap rowsType = new HashMap();
        for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) {
            rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i);
        }
        try {
            int rownum = 0;
            int wrong = 0;
            int right = 0;
            int skip = 0;
            OPCPackage pkg = OPCPackage.open(inputStream);
            XSSFWorkbook workBook = new XSSFWorkbook(pkg);
            FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet xssfSheet = workBook.getSheetAt(0);
            for (Row row : xssfSheet) {
                String rowType = row.getCell(0).getStringCellValue();
                if (rowsType.containsKey(rowType)) {
                    RowValidator rowValidator = documentValidator.getListRowValidator()
                            .get((int) rowsType.get(rowType));
                    int columnNum = rowValidator.getListItemValidator().size();
                    if (row.getLastCellNum() == columnNum) {
                        String[] values = new String[columnNum];
                        int i = 0;
                        for (Cell cell : row) {
                            switch (evaluator.evaluateInCell(cell).getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                values[i] = cell.getNumericCellValue() + "";
                                break;
                            case Cell.CELL_TYPE_STRING:
                                values[i] = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                values[i] = "";
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                values[i] = "";
                                break;
                            }
                            i++;
                        }
                        ResponseValidator responseValidator = rowValidator.validate(values);
                        if (!responseValidator.isSuccess()) {
                            wrong++;
                            stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":")
                                    .append(responseValidator.getMessage()).append(",");
                        } else
                            right++;
                    } else {
                        wrong++;
                        stringBuilder.append("{\"row\":").append(rownum)
                                .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString();
                    }
                } else {
                    skip++;
                    stringBuilder.append("{\"row\":").append(rownum)
                            .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString();
                }
                rownum++;

            }

            response = new ResponseValidator(wrong == 0,
                    "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum
                            + ",\"errorMessages\":["
                            + (stringBuilder.toString().length() > 0
                                    ? stringBuilder.substring(0, stringBuilder.toString().length() - 1)
                                    : "")
                            + "]}");
        } catch (Exception ex) {
            Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex);
            response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                    .append(ex.getMessage()).append("\"}").toString());
        } finally {
            try {
                inputStream.close();
            } catch (IOException ex) {
                response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                        .append(ex.getMessage()).append("\"}").toString());
            }
        }
    } else {
        response = new ResponseValidator(false,
                stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString());
    }
    return response;
}

From source file:com.jogo.dao.RepositorioDao.java

@Override
public List importarPerguntas(int index) {

    //CRIO UM ARRAY DE PERGUNTAS PARA ARMAZENAR AS PERGUNTAS BUSCAS DO EXCEL.
    List<Perguntas> perguntas = new ArrayList<>();

    try {//from  w  w w.j a  v  a2  s .c  o  m

        //CAPTURANDO O EXCEL PARA MEU WB.    
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURO A FOLHA DO EXCEL PASSANDO O INDEX
        Sheet folha = wb.getSheetAt(index);

        //CRIO UM ITERATOR PARA INTERAGIR COM AS LINHAS.
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER INTERAO PEGA UMA LINHA.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();
            //CRIO UM ITERATOR PARA PEGAR AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //AQUI DIGO QUE MINHAS COLUNAS NO PODE PASSAR DE 6, COMO TA A MINHA ESTRUTURA PARA O EXCEL
            //1 - PERGUNTA, 2- ALTERNATIVA, 3- ALTERNATIVA, 4- ATLTERNATIVA, 5- ALTERNATIVA, 6- RESPOSTA
            //CHEGOU MAIOR QUE 6 SAIU DO LOOP DE COLUNAS.
            if (linha.getLastCellNum() > 6) {
                break;
            }

            //CRIOU A CLASSE DE PERGUNTAS E ADD DENTRO DO MEU ARRAY
            Perguntas per = new Perguntas();
            perguntas.add(per);

            //INTERAGIR COM AS COLUNAS, PEGAR AS COLUNAS DO EXCEL
            while (colunaIterator.hasNext()) {

                //TENDO A LINHA E COLUNA JA POSSO TER UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE PERGUNTAS QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURAR O TIPO DA CELULA, NO CASO TODAS AS PERGUNTAS E ALTERNATIVAS SO STRINGS.
                    //OBS: OLHE QUE NESSE CASO S POSSO TRAZER STRING'S CASO CONTRARIO NO IR? FUNCIONAR.:/
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        switch (celula.getColumnIndex()) {
                        case 0:
                            per.setPergunta(celula.getStringCellValue());
                            break;
                        case 1:
                            per.setAlt1(celula.getStringCellValue());
                            break;
                        case 2:
                            per.setAlt2(celula.getStringCellValue());
                            break;
                        case 3:
                            per.setAlt3(celula.getStringCellValue());
                            break;
                        case 4:
                            per.setAlt4(celula.getStringCellValue());
                            break;
                        case 5:
                            per.setResposta(celula.getStringCellValue());
                            break;
                        default:
                            break;
                        }
                    }

                }

            }
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return perguntas;
}

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

public byte[] build(List<Map<String, String>> row) throws IOException {
    List<String> colLabels = new ArrayList<String>();
    Sheet reportSheet = workbook.getSheet("Report");
    if (reportSheet == null) {
        reportSheet = workbook.createSheet("Report");
    }//from  w w w .j  a  v  a2s.  c  o m
    Row firstRow = reportSheet.getRow(0);
    if (firstRow == null) {
        firstRow = reportSheet.createRow(0);
        int cellNum = 0;
        for (Map<String, String> col : row) {
            for (String key : col.keySet()) {
                Cell cell = firstRow.createCell(cellNum++);
                setCellValue(cell, key);
            }
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        Cell cell = firstRow.getCell(i);
        if (cell != null) {
            colLabels.add(cell.getStringCellValue());
        }
    }
    Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1);
    int lastIndex = -1;
    for (Map<String, String> col : row) {
        for (String key : col.keySet()) {
            int colNum = -1;
            Cell cell = null;
            if (colLabels.contains(key)) {
                colNum = colLabels.indexOf(key);
                lastIndex = colNum;
            }
            if (colNum == -1) {
                lastIndex++;
                colNum = lastIndex;
                shiftColumns(reportSheet, colNum, key);
                colLabels.add(colNum, key);
            }
            cell = newRow.getCell(colNum);
            if (cell == null) {
                cell = newRow.createCell(colNum);
            }
            setCellValue(cell, col.get(key));
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        reportSheet.autoSizeColumn(i);
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    workbook.write(baos);
    return baos.toByteArray();
}

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

private void shiftColumns(Sheet reportSheet, int colNum, String key) {
    boolean firstRow = true;
    for (Row row : reportSheet) {
        for (int i = row.getLastCellNum() + 1; i >= colNum; i--) {
            Cell nextCell = row.getCell(i + 1);
            Cell oldCell = row.getCell(i);
            if (oldCell != null) {
                if (nextCell == null) {
                    nextCell = row.createCell(i + 1);
                }/*from   w ww .ja  v  a  2 s .c  om*/
                setCellValue(nextCell, oldCell.getStringCellValue());
                if (firstRow && i == colNum) {
                    setCellValue(oldCell, key);
                    firstRow = false;
                } else {
                    setCellValue(oldCell, "");
                }
            } else if (firstRow && i == colNum) {
                oldCell = row.createCell(i);
                setCellValue(oldCell, key);
                firstRow = false;
            }
        }
    }
}

From source file:com.kongwu.insweb.utils.ReadExcel.java

License:Apache License

/**
 * @param filepath excel/*  w w  w. ja v a 2  s  . com*/
 *        
 *        
 *        
 * @return ??,:list<usrid,query,biz,semantic>
 *                        :
 *                           :
 * @throws IOException
 */
public static List<List<String>> readTestset(String filepath) throws IOException {
    FileInputStream fileIn = null;
    List<List<String>> testsetList = new ArrayList<List<String>>();
    /**?excel**/
    try {
        fileIn = new FileInputStream(filepath);
        /**excel 2007**/
        if (filepath.endsWith(".xlsx")) {
            Workbook wb = new XSSFWorkbook(fileIn);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                /**
                 * ?cell
                 */
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(i);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }

                testsetList.add(list);
            }
        } else {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;
            HSSFCell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)// ???
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }
                testsetList.add(list);
            }
        }
    } finally {
        if (fileIn != null)
            fileIn.close();
    }
    return testsetList;
}