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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:testpoi.Department.java

License:Open Source License

private static void makeEntry(Department deptt) {
    //create new row in xlsx to be generated
    Row newRow = sheetNew.createRow(rowCnt++);
    //Create a new cell in current row
    Cell newCell = newRow.createCell(0);
    //Set value to the department's name
    newCell.setCellValue(deptt.name);/*from w  w  w .  ja v  a 2  s .  c  om*/
    newCell = newRow.createCell(1);
    newCell.setCellValue(deptt.isNew ? "New" : "Old");
    newCell = newRow.createCell(2);
    newCell.setCellValue(crNo++);

    Row row = null;
    if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) {
        //            //Pick a row from female sheet randomly (Female sheet should have all reproducible ages)
        //            int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows());

        row = sheetFemale.getRow(femaleRowNum++);
        System.out.println("Sheet:Female, row: " + row.getRowNum());
    } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) {
        row = sheetChildren.getRow(childRowNum++);
        System.out.println("Sheet:Children, row: " + row.getRowNum());
    } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows())
    {
        row = sheetAll.getRow(allRowNum++);
        System.out.println("Sheet:All, row: " + row.getRowNum());
    }
    assert row != null;

    //read and write fetched row
    Iterator<Cell> cellIterator = row.cellIterator();
    int newCellCnt = 3;
    while (cellIterator.hasNext()) {
        //May we write all cells as strings?
        Cell cell = cellIterator.next();
        String cellValue = null;
        try {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = (int) (cell.getNumericCellValue()) + "";
            else
                cellValue = cell.getStringCellValue();

            newCell = newRow.createCell(newCellCnt++);
            newCell.setCellValue(cellValue);
        } catch (Exception e) {
            System.out.println("Could not write from cell (value:" + cellValue +
            //                        ", column:"+cell.getSheet().getWorkbook().+
                    ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:"
                    + cell.getColumnIndex() + ")");
            e.printStackTrace();
        }
    }
    System.out.println();

    //        //delete row read
    //        if (row.getSheet()==sheetFemale)
    //            sheetFemale.removeRow(row);
    //        else if (row.getSheet()==sheetChildren)
    //            sheetChildren.removeRow(row);
    //        else
    //            sheetAll.removeRow(row);
}

From source file:testpoi.OldDepttSheet.java

License:Open Source License

private static void makeEntry(Department deptt) {
    //create new row in xlsx to be generated
    Row newRow = sheetNew.createRow(rowCnt++);
    //Create a new cell in current row
    Cell newCell = newRow.createCell(0);
    //Set value to the department's name
    newCell.setCellValue(deptt.name);//  w w w  .  j  a  v  a2s.co  m
    newCell = newRow.createCell(1);
    newCell.setCellValue(deptt.isNew ? "New" : "Old");

    if (deptt.isNew) {
        newCell = newRow.createCell(2);
        newCell.setCellValue(crNo++);

        Row row = null;
        if (deptt.name.equals("Obs & Gynae")) {
            //            //Pick a row from female sheet randomly (Female sheet should have all reproducible ages)
            //            int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows());

            if (femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) {
                row = sheetFemale.getRow(femaleRowNum++);
                System.out.println("Sheet:Female, row: " + row.getRowNum());
            } else {
                System.err.println("Female entries exhausted!");
            }
        } else if (deptt.name.equals("Paediatrics")) {
            if (childRowNum < sheetChildren.getPhysicalNumberOfRows()) {
                row = sheetChildren.getRow(childRowNum++);
                System.out.println("Sheet:Children, row: " + row.getRowNum());
            } else {
                System.err.println("Child entries exhausted!");
            }
        } else {
            if (allRowNum < sheetAll.getPhysicalNumberOfRows()) {
                row = sheetAll.getRow(allRowNum++);
                System.out.println("Sheet:All, row: " + row.getRowNum());
            } else {
                System.err.println("All(General New) entries exhausted!");
            }
        }
        if (row == null) {
            throw new IllegalArgumentException("New input Rows Exhausted");
        }
        assert row != null;

        //read and write fetched row
        Iterator<Cell> cellIterator = row.cellIterator();
        int newCellCnt = 3;
        while (cellIterator.hasNext()) {
            //May we write all cells as strings?
            Cell cell = cellIterator.next();
            String cellValue = null;
            try {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                    cellValue = (int) (cell.getNumericCellValue()) + "";
                else
                    cellValue = cell.getStringCellValue();

                newCell = newRow.createCell(newCellCnt++);
                newCell.setCellValue(cellValue);
            } catch (Exception e) {
                System.out.println("Could not write from cell (value:" + cellValue +
                //                        ", column:"+cell.getSheet().getWorkbook().+
                        ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex()
                        + ", column:" + cell.getColumnIndex() + ")");
                e.printStackTrace();
            }
        }
    } else //deptt is old
    {
        OldDepttSheet oldDepttSheetToUse = depttToOldSheetsMap.get(deptt.name);

        Row row = oldDepttSheetToUse.sheet.getRow(oldDepttSheetToUse.rowCnt++);

        if (row == null) {
            throw new IllegalArgumentException("Old Input Rows Exhausted in department " + deptt.name);
        }

        System.out.println("Sheet:" + deptt.name + ", row: " + row.getRowNum());

        //Copy row from old sheet to newRow
        int newCellCnt = 2;
        Iterator<Cell> cellIterator = row.cellIterator();
        //Skip columns Department and Patient Type
        cellIterator.next();
        cellIterator.next();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            String cellValue = null;
            try {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                    cellValue = (int) (cell.getNumericCellValue()) + "";
                else
                    cellValue = cell.getStringCellValue();

                newCell = newRow.createCell(newCellCnt++);
                newCell.setCellValue(cellValue);
            } catch (Exception e) {
                System.out.println("Could not write from old sheet cell (value:" + cellValue + ", sheet:"
                        + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:"
                        + cell.getColumnIndex() + ")");
                e.printStackTrace();
            }

        }
    }
}

From source file:ui.MainWindow.java

/**
 * Creates new form MainWindow/*from w w  w.jav  a 2s  .  co  m*/
 */
public MainWindow() {
    initComponents();
    jFileChooser1.setFileFilter(FILTER);
    int returnVal = jFileChooser1.showOpenDialog(this);
    jLabel2.setVisible(false);
    jLabel3.setVisible(false);
    jLabel4.setVisible(false);
    jLabel5.setVisible(false);
    jLabel6.setVisible(false);
    jLabel7.setVisible(false);
    if (returnVal == JFileChooser.APPROVE_OPTION) {
        workbook = WorksheetFactory.importExcel(jFileChooser1.getSelectedFile().getAbsolutePath());
    } else {
        System.out.println("Cancelado");
        System.exit(0);
    }
    Sheet sheet = workbook.getSheetAt(0);
    Iterator iter = sheet.rowIterator();
    Row row;
    Cell c;
    while (iter.hasNext()) {
        row = (Row) iter.next();
        if (row.getRowNum() >= 4 && !row.getCell(1).getStringCellValue().isEmpty()) {
            c = row.getCell(1);
            jComboBox1.addItem(c.getStringCellValue());
        }
    }
    Calendar mon = Calendar.getInstance(), sun = Calendar.getInstance();

    mon.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
    sun.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);

    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    while (!(sun.get(Calendar.MONTH) == Calendar.DECEMBER && sun.get(Calendar.WEEK_OF_MONTH) > 2)) {
        jComboBox2.addItem(df.format(mon.getTime()) + "-" + df.format(sun.getTime()));
        mon.add(Calendar.DAY_OF_YEAR, 7);
        sun.add(Calendar.DAY_OF_YEAR, 7);
    }
}

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

License:Apache License

private void printSheetContent(Sheet sheet) {
    ensureColumnBounds(sheet);/*from   w w w.  java 2  s.co 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 printSheetContent(Sheet sheet) {
    printColumnHeads();//from w w  w.  jav a2  s.c o 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 lastRow(Sheet sheet) {
    int max = 0;/*from   w  w w. j a va 2  s  .c  o m*/
    for (Row row : sheet) {
        max = row.getRowNum();
    }
    return max;
}

From source file:uk.gov.ofwat.fountain.api.table.POITableRenderer.java

License:Open Source License

private void mergeCells(Row row, int colIdx, FormDisplayCell cell) {
    if (cell.getColumnSpan() > 1 || cell.getRowSpan() > 1) {
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum() + cell.getRowSpan() - 1,
                colIdx, colIdx + cell.getColumnSpan() - 1));
    }/*from  w  w  w.  ja v a 2 s . c  om*/
}

From source file:util.DocumentFunction.java

public static Map<Boolean, List<ImportedStudent>> readStudentXlsxFile(String filename, int course_id) {
    //        StringBuilder text = new StringBuilder();
    Map<Boolean, List<ImportedStudent>> stMap = new TreeMap<>();
    List<ImportedStudent> exStudentList = new ArrayList<>();
    ImportedStudent st = null;/*  w ww  .  ja  v  a  2  s  .co  m*/
    try {
        FileInputStream file = new FileInputStream(new File(filename));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        boolean breakPoint = true;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(i);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            String email = null, firstname = null, lastname = null;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                int rownum = row.getRowNum();

                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    if (cell.getColumnIndex() == 0) {
                        email = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 1) {
                        firstname = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 2) {
                        lastname = cell.getStringCellValue();
                    }
                }

                if (row.getRowNum() == 0) {
                    if (email.equalsIgnoreCase("Email") && firstname.equalsIgnoreCase("Firstname")
                            && lastname.equalsIgnoreCase("Lastname")) {
                        continue;
                    } else {
                        stMap.put(false, exStudentList);
                        breakPoint = false;
                        break;
                    }
                } else {
                    st = new ImportedStudent();
                    st.setCourse_id(course_id);
                    st.setEmail(email);
                    st.setFirstname(firstname);
                    st.setLastname(lastname);
                    exStudentList.add(st);
                }
                //                    System.out.println(email + "/" + firstname + "/" + lastname);
            }
        }
        if (breakPoint) {
            stMap.put(true, exStudentList);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return stMap;
}

From source file:util.excel.SheetOfStudentInfo.java

License:Open Source License

/**
 * Reads student information which is present in the given spreadsheet
 *///from  w  ww .j ava2  s .  c om
public static List<StudentInClassOrError> read(File file) {
    try {
        Workbook workBook = WorkbookFactory.create(file);
        Sheet sheet = workBook.getSheetAt(0);
        // TODO: check that there is exactly one sheet

        List<StudentInClassOrError> result = new ArrayList<>();
        for (Row row : sheet) {

            // ignore empty rows
            if (countNonBlankCells(row) > 0) {
                Cell firstCell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
                // ignore 'comment' lines
                if (!cellStartsComment(firstCell)) {
                    StudentInClassOrError data = new StudentInClassOrError();

                    data.setRowNumber(row.getRowNum() + 1);

                    String classCode = getStringValueOrNull(row, 0);
                    if (classCode == null || classCode.trim().isEmpty()) {
                        data.setErrorCode("spreadsheet.classcode.empty");
                        continue;
                    }
                    data.setClassCode(classCode.trim());

                    String name = getStringValueOrNull(row, 1);
                    if (name == null || name.trim().isEmpty()) {
                        data.setErrorCode("spreadsheet.name.empty");
                    } else {
                        data.setName(name.trim());
                    }

                    String firstName = getStringValueOrNull(row, 2);
                    if (firstName == null || firstName.trim().isEmpty()) {
                        data.setErrorCode("spreadsheet.firstname.empty");
                    } else {
                        data.setFirstName(firstName.trim());
                    }

                    data.setEmail(getStringValueOrNull(row, 3));

                    String gender = getStringValueOrNull(row, 4);
                    if (gender != null && !gender.isEmpty()) {
                        // TODO: do not hard code language information
                        char firstChar = Character.toLowerCase(gender.charAt(0));
                        if (firstChar == 'm') {
                            data.setMale(true);
                        } else if (firstChar == 'f' || firstChar == 'v') {
                            data.setMale(false);
                        } else {
                            data.setErrorCode("spreadsheet.invalid.gender");
                        }
                    } else {
                        data.setErrorCode("spreadsheet.gender.empty");
                    }

                    data.setBebrasId(getStringValueOrNull(row, 5));
                    result.add(data);
                }
            }
        }
        return result;

    } catch (IOException | OpenXML4JException ex) {
        throw new RuntimeException("Error reading spreadsheet file", ex);
    }
}

From source file:WeeklyOPD.SplitOldDepartmentwise.java

License:Open Source License

private static void createDepartmentwiseSheets() {
    HashMap<String, Integer> depttMap = new HashMap<>();
    depttMap.put("Medicine", 1);
    depttMap.put("Surgery", 2);
    depttMap.put("Obs & Gynae", 3);
    depttMap.put("Paediatrics", 4);
    depttMap.put("Orthopaedics", 5);
    depttMap.put("Ophthalmology", 6);
    depttMap.put("ENT", 7);
    depttMap.put("Dental", 8);
    depttMap.put("Casualty", 9);

    int depttSheetCreateFlag = 0;
    System.out.println("In createDepartmentwiseSheets");
    Iterator<Row> rowIterator = sheetAllOld.rowIterator();
    //Store the first row to be printed as it is.
    ArrayList<String> heading = new ArrayList<>();
    Row row = rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext())
        heading.add(cellIterator.next().getStringCellValue());

    int rowNums[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1 };
    while (rowIterator.hasNext()) {
        row = rowIterator.next();/*from   w w  w .  jav a2  s  .  c o m*/
        XSSFSheet sheetToWrite = null;

        System.out.println("Row: " + row.getRowNum());

        Cell cell = row.getCell(0);
        if ((depttSheetCreateFlag & 1 << (depttMap.get(cell.getStringCellValue()))) == 0) {
            //that means this deptt came in this sheet for the first time in this row.
            XSSFSheet sheet = sheets[depttMap.get(cell.getStringCellValue()) - 1] = workbookOld
                    .createSheet(cell.getStringCellValue());
            //create heading row in this sheet
            Row headingRow = sheet.createRow(0);
            for (int i = 0; i < heading.size(); i++) {
                String cellString = heading.get(i);
                Cell headingCell = headingRow.createCell(i);
                headingCell.setCellValue(cellString);//sets cell type to string too
            }
            //mark this deptt. as seen
            depttSheetCreateFlag |= (1 << (depttMap.get(cell.getStringCellValue())));
        }

        int sheetNum = depttMap.get(cell.getStringCellValue()) - 1;
        sheetToWrite = sheets[sheetNum];
        assert (sheetToWrite != null);

        //write row to sheetToWrite
        Row rowNew = sheetToWrite.createRow(rowNums[sheetNum]++);

        cellIterator = row.cellIterator();
        int cellNum = 0;
        while (cellIterator.hasNext()) {
            cell = cellIterator.next();

            //write cell
            Cell cellNew = rowNew.createCell(cellNum++);
            String cellValue;
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = (int) (cell.getNumericCellValue()) + "";
            else
                cellValue = cell.getStringCellValue();
            cellNew.setCellValue(cellValue);
        }
    }
}