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.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

private static boolean match(Row row1, Row row2) {
    if (row1.getLastCellNum() != row2.getLastCellNum() || row1.getFirstCellNum() != row2.getFirstCellNum()) {
        return false;
    }//from w  w  w  .  ja  va2  s  . c  o m
    //p("rows " + row1.getRowNum() + "/" + row2.getRowNum() + " MIGHT be a match");
    Iterator cs1 = row1.cellIterator();
    Iterator cs2 = row2.cellIterator();
    while (cs1.hasNext()) {
        HSSFCell c1 = (HSSFCell) cs1.next();
        if (!cs2.hasNext()) {
            return false;
        }
        HSSFCell c2 = (HSSFCell) cs2.next();
        if (c1.getCellNum() != c2.getCellNum())
            return false;
        if (c1.getCellType() != c2.getCellType())
            return false;
    }
    return true;
}

From source file:com.ben12.reta.util.RETAAnalysis.java

License:Open Source License

public void writeExcel(Window parent) throws IOException, InvalidFormatException {
    logger.info("Start write excel output");

    Path outputFile = Paths.get(output);
    if (!outputFile.isAbsolute()) {
        Path root = config.getAbsoluteFile().getParentFile().toPath();
        outputFile = root.resolve(outputFile);
    }//www  .  j  a v a  2s. c o  m

    // test using template
    InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx");
    ExcelTransformer transformer = new ExcelTransformer();
    List<String> sheetNames = new ArrayList<>();
    List<String> sheetTemplateNames = new ArrayList<>();
    for (InputRequirementSource requirementSource : requirementSources.values()) {
        sheetTemplateNames.add("DOCUMENT");
        sheetTemplateNames.add("COVERAGE");
        sheetNames.add(requirementSource.getName());
        sheetNames.add(requirementSource.getName() + " coverage");
    }

    List<Map<String, Object>> sheetValues = new ArrayList<>();
    for (InputRequirementSource source : requirementSources.values()) {
        Map<String, Object> values = new HashMap<>();
        values.put("source", source);
        values.put("null", null);
        values.put("line", "\n");

        Set<String> attributes = new LinkedHashSet<>();
        attributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            attributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        attributes.addAll(source.getAttributesGroup().keySet());
        attributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("attributes", attributes);

        Set<String> refAttributes = new LinkedHashSet<>();
        refAttributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            refAttributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        refAttributes.addAll(source.getRefAttributesGroup().keySet());
        refAttributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("refAttributes", refAttributes);

        sheetValues.add(values);
        sheetValues.add(values);
    }

    Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues);
    int sheetCount = wb.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int columns = 0;
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row != null) {
                row.setHeight((short) -1);
                columns = Math.max(columns, row.getLastCellNum() + 1);
            }
        }
        for (int j = 0; j < columns; j++) {
            sheet.autoSizeColumn(j);
        }
    }

    try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
        wb.write(fos);
    } catch (FileNotFoundException e) {
        int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed.");

        if (confirm == MessageDialog.OK_OPTION) {
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                wb.write(fos);
            } catch (IOException e2) {
                throw e2;
            }
        } else {
            throw e;
        }
    }

    logger.info("End write excel output");
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean deleteColumn(int[] columnIndex)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Sheet sheet = null;//from  w w  w  . j a v a2  s.  co m
    try {
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            //?
            Row tmpRow = sheet.getRow(i);

            for (int j = columnIndex.length - 1; j > -1; j--) {
                //????
                for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) {
                    Cell tmpCell = tmpRow.getCell(k);
                    if (null != tmpCell) {
                        tmpRow.removeCell(tmpCell);
                    }
                    Cell rightCell = tmpRow.getCell(k + 1);
                    if (null != rightCell) {
                        HSSFRow hr = (HSSFRow) tmpRow;
                        hr.moveCell((HSSFCell) rightCell, (short) k);
                    }
                }

            }
        }
        m_InputStream.close();
        try ( // Write the output to a file
                final FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }
    return flag;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean addColumn(Object[] columnName, Object[][] columnData)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Row rowCaption;
    Sheet sheet = null;/*from  w  ww .  j  a  v  a  2  s.c om*/

    try {
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }
        //
        rowCaption = sheet.getRow(0);
        if (rowCaption != null) {
            int columnsCount = rowCaption.getLastCellNum();
            for (int i = 0; i < columnName.length; i++) {
                Cell cell = rowCaption.createCell(columnsCount + i);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(columnName[i]));
            }

            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                //?
                Row tmpRow = sheet.getRow(i + 1);

                for (int cIndex = 0; cIndex < columnName.length; cIndex++) {
                    Cell cell = tmpRow.getCell(columnsCount + cIndex);
                    if (cell == null) {
                        cell = tmpRow.createCell(columnsCount + cIndex);
                    }
                    //?
                    Object obj = columnData[i][cIndex];
                    if (obj.getClass().getName().equals(Double.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    } else if (obj.getClass().getName().equals(String.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    } else {
                        //?
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    setCellValue(cell, obj);
                }

            }
        }

        m_InputStream.close();
        try ( // Write the output to a file
                FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;

    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }

    return flag;
}

From source file:com.chlq.fileprocessor.ToCSV.java

License:Apache License

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file./*from  w w  w.  ja v a 2  s  . c  o m*/
 *
 * @param row An instance of either the HSSFRow or XSSFRow classes that
 *            encapsulates information about a row of cells recovered from
 *            an Excel workbook.
 */
private void rowToCSV(Row row) {
    Cell cell = null;
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the csvLine ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                if (cell.getCellTypeEnum() != CellType.FORMULA) {
                    csvLine.add(this.formatter.formatCellValue(cell));
                } else {
                    csvLine.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }
        // Make a note of the index number of the right most cell. This value
        // will later be used to ensure that the matrix of data in the CSV file
        // is square.
        if (lastCellNum > this.maxRowWidth) {
            this.maxRowWidth = lastCellNum;
        }
    }
    this.csvData.add(csvLine);
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream();
    try {/*from w w  w . ja  v a  2s. c  om*/
        Workbook book = WorkbookFactory.create(is);
        Sheet sheet = book.getSheet(execBean.getSheetName());
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<String> names = new ArrayList<String>();
        Map<String, String> typeMap = execBean.getTypeMap();
        int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum();
        for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) {
            Row excelRow = sheet.getRow(rowIdx);

            short minColIx = excelRow.getFirstCellNum();
            short maxColIx = excelRow.getLastCellNum();

            Map<String, Object> row = new HashMap<String, Object>();

            for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) {
                Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK);

                if (rowIdx == 0) {
                    names.add(cell.getStringCellValue());
                } else {
                    String type = null;
                    if (names.size() > colIdx) {
                        type = typeMap.get(names.get(colIdx));
                    }
                    if (StringUtils.isNotEmpty(type)) {
                        if (type.equals("string")) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            row.put(names.get(colIdx), cell.getStringCellValue().trim());
                        } else if (type.equals("double")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), cell.getNumericCellValue());
                        } else if (type.equals("int")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), (int) cell.getNumericCellValue());
                        } else if (type.equals("date")) {
                            row.put(names.get(colIdx), cell.getDateCellValue());
                        } else {
                            throw new DataProcessException("??Excel?");
                        }
                    }
                }
            }
            if (rowIdx != 0) {
                result.add(row);
            }
        }
        context.setAttribute(execBean.getResultName(), result);
    } finally {
        if (is != null) {
            is.close();
        }
    }

}

From source file:com.cn.util.Units.java

public static boolean isEmptyRowForExcel(Row row) {
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);// w w  w .j  a  v  a  2 s .  c o m
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            return false;
        }
    }
    return true;
}

From source file:com.codellect.util.ExcelReader.java

private void read() {
    try (FileInputStream inputStream = new FileInputStream(excel);
            Workbook workbook = new XSSFWorkbook(inputStream)) {
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();
        Row nextRow = iterator.next();
        while (iterator.hasNext()) {
            nextRow = iterator.next();/*from  ww  w  .  java  2 s .  c o m*/
            short count = nextRow.getLastCellNum();
            if (count == 4 & notEmpty(nextRow.getCell(ColumnType.STATEMENT.value()))) {
                QbankBean qbankBean = new QbankBean();
                qbankBean.setId((int) nextRow.getCell(ColumnType.ID.value()).getNumericCellValue());
                qbankBean.setLevel((int) nextRow.getCell(ColumnType.LEVEL.value()).getNumericCellValue());
                qbankBean.setTag(nextRow.getCell(ColumnType.TAG.value()).getStringCellValue());
                qbankBean.setQuestion(nextRow.getCell(ColumnType.STATEMENT.value()).getStringCellValue());
                push(qbankBean);
            }
        }
    } catch (IOException ex) {
        System.out.println("\033[0;1m" + ex.getMessage());
        System.out.println(
                "Please place the data.xlsx file here or provide path of data file as in command line argument"
                        + "\033[0;0m");
    }
}

From source file:com.common.report.util.html.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    //        printColumnHeads();

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;/* w w w .java  2 s .  c o m*/
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Validates the reader-config.xml with the Excel file
 *
 * @param vcConfig The validator configuration object.
 * @param filename Name of the Excel file.
 * @param dDoc Document conatins the request.
 * @param iResultNode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param lErrorList LinkedList contains all the errors.
 *//* w  ww . ja va2s.c  o  m*/
public static void validate(ValidatorConfig vcConfig, String filename, Document dDoc, int iResultNode,
        int sheetno, int startrow, int endrow, List<FileException> lErrorList) {
    try {

        setRecordsread(0);
        setEndoffile(false);

        Workbook book = null;
        Sheet sheet = null;
        Row row;
        FileInputStream fileinp = null;
        //String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
        int sheetindex;
        int noofsheets;
        if (filename == null) {
            throw new FileException(LogMessages.PLEASE_PROVIDE_FILE_NAME);
        }
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                try {
                    book = (Workbook) new HSSFWorkbook(fileinp);
                } catch (IOException ex) {
                    Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
                }
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                try {
                    book = new XSSFWorkbook(fileinp);
                } catch (IOException ex) {
                    Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
                }
            } else {
                //ERROR
                fileinp.close();
                throw new FileException(LogMessages.INPUT_FILE_NOT_SUPPORTED);
            }
        } else {
            //ERROR
            fileinp.close();
            throw new FileException(LogMessages.FILE_NOT_FOUND);
        }
        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        //check whether the sheetindex exists or not
        for (; sheetindex < noofsheets; sheetindex++) {
            if (sheetindex >= book.getNumberOfSheets()) {
                //no sheet
                throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex);
            }
            sheet = book.getSheetAt(sheetindex);
            if (sheet == null) {
                throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex);
            }
        }

        //validate columns

        //get last column index
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (maxcol < row.getLastCellNum()) {
                maxcol = row.getLastCellNum();
            }
        }
        //check column index in reader-config
        ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList.listIterator();
        while (fieldslist.hasNext()) {
            FieldType excelfields = (FieldType) fieldslist.next();
            try {
                if (Short.parseShort(excelfields.sColumnIndex) < 0
                        || Short.parseShort(excelfields.sColumnIndex) >= maxcol) {
                    throw new FileException(LogMessages.COLUMN_INDEX_NOT_FOUND, excelfields.sColumnIndex,
                            (maxcol - 1));
                }
            } catch (NumberFormatException ex) {
                throw new FileException(ex, LogMessages.COLUMN_INDEX_NOT_VALID, excelfields.sColumnIndex);
            }
        }

        if (endrow == -1) {
            endrow = sheet.getLastRowNum();
            if (startrow == -1) {
                startrow = 0;
            }
        } else {
            endrow = startrow + endrow - 1;
            if (endrow > sheet.getLastRowNum()) {
                endrow = sheet.getLastRowNum();
            }
        }

        setRecordsread(endrow - startrow + 1);

    } catch (IOException ex) {
        lErrorList.add(new FileException(ex, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename));
    } catch (FileException ex) {
        lErrorList.add(ex);
    }

}