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:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(//from w  w  w  .j  av a2  s  .  c o  m
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static void deleteRow(Sheet sheet, Row rowToDelete) {

    // if the row contains merged regions, delete them
    List<Integer> mergedRegionsToDelete = new ArrayList<>();
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == rowToDelete.getRowNum()
                && mergedRegion.getLastRow() == rowToDelete.getRowNum()) {
            // this region is within the row - so mark it for deletion
            mergedRegionsToDelete.add(i);
        }/*from   w  w  w. ja v  a2 s. co m*/
    }

    // now that we know all regions to delete just do it
    for (Integer indexToDelete : mergedRegionsToDelete) {
        sheet.removeMergedRegion(indexToDelete);
    }

    int rowIndex = rowToDelete.getRowNum();

    // this only removes the content of the row
    sheet.removeRow(rowToDelete);

    int lastRowNum = sheet.getLastRowNum();

    // shift the rest of the sheet one index down
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
}

From source file:org.opentestsystem.delivery.testreg.rest.FilePreviewHelper.java

License:Open Source License

/**
 * Extracts preview data from EXCEL format files.
 *
 * @param instream//w  w w  .j  ava2  s.co m
 *        input stream
 * @param fileName
 *        name of the uploaded file
 * @param noOfRowsToExtract
 *        number of rows to extract including header row
 * @return List<FilePreview> list of file preview. Each sheet is translated into a file preview.
 * @throws IOException
 */
private static List<FilePreview> getPreviewDataFromExcel(final InputStream instream, final String formatType,
        final String fileName, final int noOfRowsToExtract) throws Exception {

    final ExcelUtils excelUtils = new ExcelUtils();
    final List<FilePreview> previewData = new ArrayList<FilePreview>();
    excelUtils.processExcelFile(instream, new ExcelWorksheetProcessor() {

        // Sheet Processing
        @Override
        public void process(final Sheet sheet) {
            final FilePreview preview = new FilePreview();
            final List<List<String>> rowData = new ArrayList<List<String>>();
            final boolean skipHeader = false;

            // Row Processing
            excelUtils.iterateRows(sheet, new ExcelRowMapper() {
                int numberOfHeaders;

                @Override
                public boolean mapRow(final Row row) {
                    if (row == null) {
                        rowData.add(null);
                        return true;
                    }
                    final String[] columnsText = excelUtils.getCellTextValuesWithNullAsBlank(row);

                    if (StringUtils.isBlank(preview.getSheetName())) { // Sheet Name - FormatType
                        preview.setSheetName(formatType);
                    }

                    if (row.getRowNum() < noOfRowsToExtract) {

                        if (row.getRowNum() == HEADER_ROW) {
                            this.numberOfHeaders = columnsText.length;
                        }
                        rowData.add(asList(copyOf(padEmptyIfNoColumnAtEnd(this.numberOfHeaders, columnsText),
                                this.numberOfHeaders, String[].class)));
                        return true;

                    } else {
                        return false; // Break Mapping the row

                    }
                }
            }, skipHeader, true);

            // Populate Preview
            preview.setRowData(rowData);
            previewData.add(preview);
        }
    });

    return previewData;
}

From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java

License:Open Source License

public static int getRowNum(final Row row) {
    if (row == null) {
        return -1; // Exception Scenario
    }/*w w  w  .  jav  a2 s. c o m*/
    return row.getRowNum() + 1; // Excel Rows are 0 based
}

From source file:org.opentestsystem.delivery.testreg.upload.RowMetadataImpl.java

License:Open Source License

public RowMetadataImpl(final Row row) {
    // Excel Rows are 0 based so add 1
    this(row.getRowNum() + 1, row.getRowStyle() != null ? row.getRowStyle().toString() : null);
}

From source file:org.patientview.ibd.util.MedicationImporter.java

License:Open Source License

public void run(String excelFileLocation, String outputFileLocation) {
    this.outputFileLocation = outputFileLocation;

    // first check to see if the file already exists and if it does delete it so we dont append more sql
    // to what exists in it from a previous export
    if (fileExists(outputFileLocation)) {
        deleteFile(outputFileLocation);//from w ww.j a  v a  2 s  .c o m
    }

    try {
        // try and read the file
        InputStream inp = new FileInputStream(excelFileLocation);

        // create a spreadsheet so we can move through it
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            String medicationTypeName = null;
            String medicationName = null;
            String medicationDosages = null;

            // first row is the title of the columns
            if (row.getRowNum() > 0) {
                for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                    // we only expect 3 cols of data
                    if (cn > MAX_COLS) {
                        break;
                    }

                    Cell cell = row.getCell(cn);

                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);

                        String value = cell.getStringCellValue();

                        // check what cell it is and set data we need
                        if (cn == MEDICATION_TYPE_NAME_COL) {
                            medicationTypeName = value.trim();
                        } else if (cn == MEDICATION_NAME_COL) {
                            medicationName = value.trim();
                        } else if (cn == MEDICATION_DOSAGES_COL) {
                            medicationDosages = value.trim();
                        }

                        if (medicationTypeName != null && medicationTypeName.length() > 0
                                && medicationName != null && medicationName.length() > 0
                                && medicationDosages != null && medicationDosages.length() > 0) {
                            // first check if a medication type already exists with this name else create
                            MedicationType medicationType = medicationTypes.get(medicationTypeName);

                            if (medicationType == null) {
                                medicationType = new MedicationType();
                                medicationType.setName(medicationTypeName);
                                medicationType.setMedications(new ArrayList<Medication>());
                                medicationTypes.put(medicationTypeName, medicationType);

                                // set the id and increment for the next one
                                medicationType.setId(medicationTypeCurrentId);
                                medicationTypeCurrentId++;
                            }

                            // then create a medication object that we can assign the dosages
                            Medication medication = new Medication();
                            medication.setName(medicationName);

                            // now parse any dosages and assign to the medication
                            medication.setAllowedDosages(parseMedicationDosages(medicationDosages));

                            // set the id and increment for the next one
                            medication.setId(medicationCurrentId);
                            medicationCurrentId++;

                            // add this medication to the medication type
                            medicationType.getMedications().add(medication);
                        }
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        System.out.println("Could not find file " + e);
    } catch (IOException e) {
        System.out.println("Could not read file " + e);
    } catch (Exception e) {
        System.out.println("Unknown error " + e);
    }

    // if the file was processed and we have any objects then build some sql statements
    if (!medicationTypes.isEmpty()) {
        for (MedicationType medicationType : medicationTypes.values()) {
            // first enter the medications and dosages
            buildMedicationSqlInsert(medicationType.getMedications());

            // then buld the sql for the medication type and map the medications to it
            buildMedicationTypeSqlInsert(medicationType);
        }
    }

    System.out.println("Import file created " + outputFileLocation);
}

From source file:org.patientview.radar.util.RadarPhase2ExcelDataToSqlMapper.java

License:Open Source License

public static void main(String params[]) {
    InputStream inp = null;/*w w  w .java 2 s  .  co  m*/
    try {
        /************** 1. first create the prd codes sql  ***************/
        // this is the file from radar originally called ERA_EDTA_new_PRD_codes_27042012_def for NDT_RADAR
        inp = new FileInputStream(BASE_PATH + "input/prd_codes_and_working_group.xls");
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        // each list item represents a row to insert
        List<List<String>> dataList = new ArrayList<List<String>>();

        //iterate through the rows in excel file
        for (Row row : sheet) {
            // ignore non data rows
            if (row.getRowNum() < FIRST_DATA_ROW) {
                continue;
            } else if (row.getRowNum() > LAST_DATA_ROW) {
                break;
            }

            List<String> values = new ArrayList<String>();
            // iterate through cells
            for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                Cell cell = row.getCell(cn);
                // ignore non data cells
                if (cn > 23) {
                    break;
                }
                String value = "";
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                // convert x values to 1 which means true, or blank to 0 which means false
                if ((cn >= FIRST_BOOLEAN_FIELD && cn <= LAST_BOOLEAN_FIELD)) {
                    value = value.equals("x") ? "1" : "0";
                }

                values.add(value);
            }

            dataList.add(values);
        }

        StringBuilder outputText = new StringBuilder();
        StringBuilder prdSql = new StringBuilder();
        String sqlBaseInsert = "INSERT INTO rdr_prd_code(ERA_EDTA_PRD_code, ERA_EDTA_primaryRenalDiagnosisTerm, "
                + "histology, clinicalHistory, familyHistory, clinicalExam, biochemistry, immunology, "
                + "urineAnalysis, " + "imaging, geneTest, otherCriteriaAndNotes, "
                + "SNOMED_CT_conceptIdentifierForFocusConcept, " + "SNOMED_CT_fullySpecifiedName, "
                + "SNOMED_CT_expressionConstraint, majorHeading, mappingToOldPRDCode, "
                + "mappingToOldPRDTerm, ERA_EDTA_defaultSortOrder, geneticsHomeReferenceLink, "
                + "nationalCenterForBiotechnologyLink, ICD_10_code, ICD10_rubricTerm, alternativesearchTerms) "
                + "VALUES (";
        int index = 0;

        // for each row in the data list create an sql insert statement
        for (List<String> row : dataList) {
            String sqlInsert = sqlBaseInsert;
            int valueIndex = 0;
            for (String value : row) {
                value = value.replace("'", "").replace("\"", "");
                sqlInsert += "'" + value + "'" + (valueIndex != row.size() - 1 ? "," : "");
                valueIndex++;
            }
            sqlInsert += ");" + System.getProperty("line.separator");
            prdSql.append(sqlInsert);
            index++;
        }

        // append to output text - output text will eventually be written to a file
        outputText.append(prdSql + System.getProperty("line.separator"));

        /************** 2. create the working groups sql  ***************/
        Row row = sheet.getRow(1);
        List<String> workingGroups = new ArrayList<String>();
        // iterate through all working groups
        for (Cell cell : row) {
            if (cell.getColumnIndex() < FIRST_WORKING_GROUP_INDEX) {
                continue;
            } else if (cell.getColumnIndex() > LAST_WORKING_GROUP_INDEX) {
                break;
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            value = value.replace("'", "\\'");
            workingGroups.add(value);
        }

        // create sql for working groups sql insert
        String workingGroupSql = "" + System.getProperty("line.separator");
        int workingGroupIndex = 0;
        for (String workingGroup : workingGroups) {
            String unitCode = workingGroup.split(" ")[0] + workingGroupIndex; // this is id, has to be unique
            workingGroupSql += "INSERT INTO unit(unitcode, name, shortName, sourceType) VALUES('" + unitCode
                    + "', '" + workingGroup + "', '','radargroup');" + System.getProperty("line.separator");
            workingGroupIndex++;
        }

        // append to output text - output text will eventually be written to a file
        outputText.append(workingGroupSql + System.getProperty("line.separator"));

        /************** 3. create the mapping table sql - this is the tricky bit!  ***************/
        List<List<String>> mappingData = new ArrayList<List<String>>();

        // for each working group collect mapping values to working group
        for (int columnIndex = FIRST_WORKING_GROUP_INDEX; columnIndex < LAST_WORKING_GROUP_INDEX; columnIndex++) {
            List<String> list = new ArrayList<String>();
            for (int rowIndex = FIRST_DATA_ROW; rowIndex <= LAST_DATA_ROW; rowIndex++) {
                Row mappingRow = sheet.getRow(rowIndex);
                Cell cell = mappingRow.getCell(columnIndex);
                String value = "0";
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                list.add(value);
            }
            mappingData.add(list);
        }

        // create list of prd ids
        List<String> prdIds = new ArrayList<String>();
        for (int i = FIRST_DATA_ROW; i <= LAST_DATA_ROW; i++) {
            Row aRow = sheet.getRow(i);
            Cell cell = aRow.getCell(0);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            prdIds.add(value);
        }

        // create sql insert statements based on where working group and disease intersect
        String mappingSql = "";
        String baseSql = "INSERT INTO rdr_diagnosis_mapping(workingGroup, PRDCode, ordering) VALUES(";
        for (int i = 0; i < mappingData.size(); i++) {
            String sql = "";
            List<String> list = mappingData.get(i);
            for (int j = 0; j < list.size(); j++) {
                sql = baseSql;
                String value = list.get(j);
                if (!value.equals("0")) {
                    sql += "'" + (workingGroups.get(i).split(" ")[0] + i) + "', '" + prdIds.get(j) + "','"
                            + value + "');";
                    if (!sql.equals(baseSql)) {
                        mappingSql += sql + System.getProperty("line.separator");
                    }
                }
            }

        }

        outputText.append(mappingSql);
        // output all sql stuff to file
        FileWriter fileWriter = new FileWriter(BASE_PATH + "output/phase2Data.sql");
        BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
        bufferedWriter.write(outputText.toString());
        //Close the output stream
        bufferedWriter.close();
    } catch (Exception e) {
        //To change body of catch statement use File | Settings | File Templates.
        LOGGER.error(e.getMessage());
        LOGGER.debug(e.getMessage(), e);

    }

}

From source file:org.pentaho.di.trans.steps.excelinput.poi.PoiCell.java

License:Apache License

public int getRow() {
    Row row = cell.getRow();
    return row.getRowNum();
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

public ExportRow copyRow(ExportRow targetRow) {
    final Row poiRow = copyRow(targetRow.getSheet().getPoiSheet(), targetRow.getRowNum());
    initRowList();//  w  w  w. java  2 s  .co  m
    return rows.get(poiRow.getRowNum());
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }/*  w w  w.  j  a v  a2  s . c  o  m*/

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}