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:temp.ExcelReader.java

public static void readFromFile(File file) {
    try {//from w  ww .j ava  2  s.c  o m

        //        XSSFWorkbook workbookXls;
        //        can load up every sheet as previews
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
        System.out.println("Number of sheets:" + workbook.getNumberOfSheets());

        //say I want sheet 0
        HSSFSheet sheet = workbook.getSheetAt(5);

        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //                Iterator<Cell> cellI = row.cellIterator();
            //                
            //                while(cellI.hasNext()){
            //                    Cell cell = cellI.next();
            //                    //This is wrong -> the blank cell should not return 0!
            //                    //use toString would work; but it could cause the formulas to go wrong
            //                    
            //                    
            //                    
            //                    System.out.print(cell + "|");
            //                }
            for (int i = 0; i < row.getLastCellNum(); i++) {
                System.out.print(row.getCell(i) + "|"); //this you won't miss any cells! right way to do
            }

            System.out.println("");
        }

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

}

From source file:tools.xor.service.AggregateManager.java

License:Apache License

private Map<String, Integer> getHeaderMap(Sheet sheet) {
    Map<String, Integer> colMap = new HashMap<String, Integer>();
    Row headerRow = sheet.getRow(0);
    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
        Cell headerCell = headerRow.getCell(i);
        colMap.put(headerCell.getStringCellValue(), i);
    }/* www .j a v  a2s  . com*/

    return colMap;
}

From source file:uk.ac.leeds.ccg.andyt.projects.pfi.XSLX2CSV.java

/**
 * 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.  j  a  v  a  2s  .  co  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, int sheetid) {
    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.getCellType() != Cell.CELL_TYPE_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 (this.maxRowWidth.size() < (sheetid + 1)) {
            this.maxRowWidth.add(0);
        }
        if (lastCellNum > this.maxRowWidth.get(sheetid)) {
            this.maxRowWidth.set(sheetid, lastCellNum);
        }
    }
    this.csvData.get(sheetid).add(csvLine);
}

From source file:uk.ac.leeds.ccg.andyt.rdl.conversion.RDL_XSLX2CSV.java

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file./*  w w w . j a  v  a2s .co 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.getCellType() != Cell.CELL_TYPE_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:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java

License:Apache License

private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) {
    // generic part
    boolean costumFormatting = false;
    boolean formulae = false;
    boolean UDF = false;
    boolean hasComments = false;

    Set<String> udfs = new HashSet<String>();
    FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator();

    s.setAttribute("name", ss.getSheetName());
    s.setAttribute("firstRow", "" + ss.getFirstRowNum());
    s.setAttribute("lastRow", "" + ss.getLastRowNum());
    try {/*from   w w  w.  j a  v a  2s.c o  m*/
        s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation());
    } catch (Throwable x) {
        //x.printStackTrace();
    }

    // shapes in detail? 
    Footer footer = ss.getFooter();
    if (footer != null) {
        s.setAttribute("footer", "true");
    }
    Header header = ss.getHeader();
    if (header != null) {
        s.setAttribute("header", "true");
    }
    PaneInformation paneInformation = ss.getPaneInformation();
    if (paneInformation != null) {
        s.setAttribute("panels", "true");
    }

    HSSFSheet hs = null;
    XSSFSheet xs = null;
    if (ss instanceof HSSFSheet) {
        hs = (HSSFSheet) ss;
        try {
            if (hs.getDrawingPatriarch() != null) {
                if (hs.getDrawingPatriarch().containsChart())
                    s.addContent(new Element("charts", sn));
                if (hs.getDrawingPatriarch().countOfAllChildren() > 0)
                    s.addContent(new Element("shapes", sn));
            }
        } catch (Exception x) {
            x.printStackTrace();
        }

        if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) {
            s.setAttribute("conditionalFormatting", "true");
        }
    }
    if (ss instanceof XSSFSheet) {
        xs = (XSSFSheet) ss;

    }
    Iterator<Row> rows = ss.rowIterator();

    int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0);
    int endColumn = 0;
    while (rows.hasNext()) {
        Row row = rows.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    s.setAttribute("firstColumn", "" + firstColumn);
    s.setAttribute("lastColumn", "" + endColumn);
    rows = ss.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row)
            if (cell != null) {
                try {
                    if (!cell.getCellStyle().getDataFormatString().equals("GENERAL"))
                        costumFormatting = true;
                } catch (Throwable t) {
                }

                if (cell.getCellComment() != null)
                    hasComments = true;
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    // System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //                        if (DateUtil.isCellDateFormatted(cell)) {
                    //                            // System.out.println(cell.getDateCellValue());
                    //                        } else {
                    //                            // System.out.println(cell.getNumericCellValue());
                    //                        }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    // System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // System.out.println(cell.getCellFormula());
                    formulae = true;
                    if (!UDF)
                        try {
                            evaluator.evaluate(cell);
                        } catch (Exception x) {
                            if (x instanceof NotImplementedException) {
                                Throwable e = x;

                                //e.printStackTrace();
                                while (e != null) {
                                    for (StackTraceElement c : e.getStackTrace()) {
                                        if (c.getClassName().contains("UserDefinedFunction")) {
                                            UDF = true;
                                            System.out.println("UDF " + e.getMessage());
                                            udfs.add(e.getMessage());
                                        }
                                    }
                                    e = e.getCause();
                                }

                            }
                        }
                    break;
                default:
                }

            }
    }
    if (costumFormatting) {
        Element cf = new Element("customisedFormatting", sn);
        s.addContent(cf);
    }
    if (formulae) {
        Element cf = new Element("formulae", sn);
        s.addContent(cf);
    }
    if (UDF) {
        Element cf = new Element("userDefinedFunctions", sn);
        for (String sss : udfs)
            cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss));
        s.addContent(cf);
    }
    if (hasComments) {
        Element cf = new Element("cellComments", sn);
        s.addContent(cf);
    }
}

From source file:uk.ac.liverpool.spreadsheet.ToXML.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    ensureColumnBounds(sheet);//from   w w w.j av a 2  s .  c  o  m
    printColumnHeads();

    cellsToFormula = new HashMap<String, List<String>>();
    cellToFormulaConverted = new HashMap<String, String>();
    crToParent = new HashMap<String, List<String>>();
    FormulaParsingWorkbook fpwb;
    FormulaRenderingWorkbook frwb;
    if (xswb != null) {
        XSSFEvaluationWorkbook w = XSSFEvaluationWorkbook.create(xswb);
        frwb = w;
        fpwb = w;
    } else if (hswb != null) {
        HSSFEvaluationWorkbook w = HSSFEvaluationWorkbook.create(hswb);
        frwb = w;
        fpwb = w;
    }

    else
        return;
    // first we need to determine all the dependencies ofr each formula
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (int i = firstColumn; i < endColumn; i++) {
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
                        try {
                            parseFormula(cell, fpwb, frwb);

                        } catch (Exception x) {

                        }
                }
            }
        }
    }
    rows = sheet.rowIterator();

    while (rows.hasNext()) {
        Row row = rows.next();
        int rowNumber = row.getRowNum() + 1;
        out.format("  <TableRow>%n");
        out.format("    <RowHeader>%d</RowHeader>%n", rowNumber);
        out.format("  <TableCells>%n");
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "0";
            String attrs = "";
            CellStyle style = null;
            String valueType = "float";
            Cell cell = row.getCell(i);
            CellReference c = new CellReference(rowNumber - 1, i);
            attrs += " cellID=\"." + c.formatAsString() + "\"";

            String cr = c.formatAsString();
            // if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {

            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                attrs += " readOnly=\"readOnly\"";
                try {
                    attrs += " cellFormula=\"" + StringEscapeUtils.escapeXml(cell.getCellFormula()) + "\"";
                } catch (Exception x) {
                    attrs += " cellFormula=\"FORMULA ERROR\"";
                }
            } else {
                List<String> cfrl = cellsToFormula.get(cr);
                StringBuffer formula = new StringBuffer("");

                if (cfrl != null) {
                    List<String> refs = new LinkedList<String>();
                    visit(cfrl, refs);
                    System.out.println(refs);
                    cleanup(refs);
                    for (String s : refs) {
                        formula.append(StringEscapeUtils.escapeXml(cellToFormulaConverted.get(s)));
                        formula.append(" || ");
                    }
                }
                if (formula.length() > 0)
                    attrs += " formula=\"" + formula.substring(0, formula.length() - 4) + "\"";
            }
            if (cell != null) {
                style = cell.getCellStyle();
                // Set the value that is rendered for the cell
                // also applies the format

                try {
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                } catch (Exception x) {
                    content = "DATA FORMULA ERROR ";
                }

            }
            // }
            attrs += " value_type=\"" + valueType + "\"";
            attrs += " value=\"" + StringEscapeUtils.escapeXml(content) + "\"";
            out.format("    <TableCell  %s>%s</TableCell>%n", // class=%s
                    // styleName(style),
                    attrs, StringEscapeUtils.escapeXml(content));
        }
        out.format(" </TableCells> </TableRow>%n%n");
    }
}

From source file:uk.co.certait.test.ExcelToHtmlConverter.java

License:Apache License

private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds) {
        return;//  w  w  w .jav a2 s  .  c o  m
    }

    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
        Row row = iter.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    gotBounds = true;
}

From source file:uk.co.certait.test.ExcelToHtmlConverter.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from   w  w  w.j a  v  a2s.co  m

    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;
            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:uk.co.spudsoft.birt.emitters.excel.tests.ReportRunner.java

License:Open Source License

protected int greatestNumColumns(Sheet sheet) {
    int result = 0;
    for (Row row : sheet) {
        if (row.getLastCellNum() > result) {
            result = row.getLastCellNum();
        }//www.  j av a 2  s.co  m
    }
    return result;
}

From source file:update2viva.ConvertXLSX.java

static void readXlsx(File inputFile, String outputfile)
        throws FileNotFoundException, UnsupportedEncodingException, IOException {
    String[] args;//from www  .  j av a  2s  .  c  om
    //File to store data in form of CSV
    File f = new File(outputfile + "\\out_.csv");

    OutputStream os = (OutputStream) new FileOutputStream(f);
    String encoding = "ISO-8859-1";
    OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
    BufferedWriter bw = new BufferedWriter(osw);

    // Get the workbook instance for XLSX file
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

    // Get first sheet from the workbook
    XSSFSheet sheet = wb.getSheetAt(0);

    Row row;
    Cell cell;

    String acrow = "";
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);

        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (!(row.getCell(j) == null)) {
                switch (row.getCell(j).getCellType()) {

                case Cell.CELL_TYPE_BOOLEAN:
                    acrow = "" + row.getCell(j).getBooleanCellValue();
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    acrow = "" + row.getCell(j).getNumericCellValue();
                    break;

                case Cell.CELL_TYPE_STRING:
                    acrow = row.getCell(j).getStringCellValue();
                    break;

                case Cell.CELL_TYPE_BLANK:
                    System.out.println(" ");
                    break;
                }
                // acrow=row.getCell(j).getStringCellValue();
                if (acrow.contains("\n"))
                    acrow = acrow.replaceAll("\n", "");

                if (!(acrow.contains("\n")))

                {
                    bw.write(acrow + ";");
                }
            }
            if (row.getCell(j) == null) {
                bw.write(';');
            }
        }

        bw.newLine();
    }

    bw.flush();
    bw.close();
    inputFile.delete();
}