Example usage for org.apache.poi.xssf.usermodel XSSFRow getRowNum

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getRowNum

Introduction

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

Prototype

@Override
public int getRowNum()

Source Link

Document

Get row number this row represents

Usage

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);
    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false);
    XSSFRow newRow = worksheet.createRow(destinationRowNum);

    // 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
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;/*from  w ww  .j  a  v a2 s.com*/
            continue;
        }

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

        newCell.setCellStyle(newCellStyle);

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

    }

    // If there are 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);
        }
    }

    newRow.setHeight(sourceRow.getHeight());

    return newRow;
}

From source file:in.expertsoftware.colorcheck.Financial_Standard_WorkingSection_count.java

public ArrayList<String> working_Section(int SOFWDLocation, int EOFWDLocation, XSSFWorkbook workbook) {
    ArrayList<String> workingSection = new ArrayList<String>();
    XSSFRow row;
    XSSFCell cell;//from   w  w w .ja v a  2s  .  c om
    XSSFSheet Sheet = workbook.getSheet("Financial_Standard");
    String genrateFormula;
    for (int start = (SOFWDLocation - 1); start < EOFWDLocation; start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(2);
            if (!(cell.getStringCellValue().equals("Common Financial Ratios Reviewed by Lenders"))) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    genrateFormula = "Financial_Standard!C" + (row.getRowNum() + 1);
                    workingSection.add(genrateFormula);
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    System.out.println("Error");
                    break;
                }
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
    return workingSection;
}

From source file:in.expertsoftware.colorcheck.Operation_Standard_WorkingSection_count.java

public ArrayList<String> working_Section(int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) {
    ArrayList<String> workingSection = new ArrayList<String>();
    XSSFRow row;
    XSSFCell cell;/*  w ww.  j  a  va  2 s .  c om*/
    XSSFSheet Sheet = workbook.getSheet("Operation_Standard");
    String genrateFormula;
    for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(2);
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1);
                workingSection.add(genrateFormula);
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                System.out.println("Error");
                break;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
    return workingSection;
}

From source file:nc.noumea.mairie.appock.services.impl.ImportExcelServiceImpl.java

License:Open Source License

private PhotoArticleCatalogue recuperePhotoArticleCatalogue(XSSFSheet firstSheet, String nomFichier, int numRow,
        String reference) throws IOException, ImportExcelException {

    Stream<XSSFShape> shapeStream = firstSheet.getDrawingPatriarch().getShapes().stream() //
            .filter(shape -> {/*from www. jav a2s  .co  m*/
                if (!(shape instanceof XSSFPicture)) {
                    return false;
                }
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFClientAnchor anchor = (XSSFClientAnchor) picture.getAnchor();
                XSSFRow pictureRow = firstSheet.getRow(anchor.getRow1());
                return (anchor.getCol1() == IMPORT_EXCEL_COLONNE_PHOTO && pictureRow != null
                        && pictureRow.getRowNum() == numRow);
            });

    List<XSSFShape> listeShape = shapeStream.collect(Collectors.toList());
    if (listeShape.isEmpty()) {
        throw new ImportExcelException(numRow + 1, reference, "Aucune image n'a t trouve");
    } else if (listeShape.size() > 1) {
        throw new ImportExcelException(numRow + 1, reference, "Plusieurs images ont t trouves");
    }

    XSSFPicture picture = (XSSFPicture) listeShape.get(0);
    byte[] content = picture.getPictureData().getData();
    content = AppockUtil.scale(content, 80, 80);

    if (content == null) {
        throw new ImportExcelException(numRow + 1, reference, "Aucune image n'a t trouve");
    }

    return catalogueService.savePhotoArticleCatalogue(content, nomFichier);
}

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) {
    report.println();/* www  . j  av a 2 s .c  o m*/
    report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale),
            InterfaceReport.FORMAT_NOTE);
    FileOutputStream fos = null;
    XSSFCreationHelper factory = workbook.getCreationHelper();
    XSSFFont boldFont = workbook.createFont();
    boldFont.setFontName("Arial");
    boldFont.setBold(true);
    boldFont.setCharSet(134);
    boldFont.setFontHeightInPoints((short) 9);
    XSSFFont plainFont = workbook.createFont();
    plainFont.setFontName("Arial");
    plainFont.setCharSet(134);
    plainFont.setFontHeightInPoints((short) 9);

    XSSFSheet errorSheet = null;
    if (errorMessages.keySet().size() > 0) {
        String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors";
        errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName);
        errorSheet = workbook.createSheet(errorSheetName);
        workbook.setSheetOrder(errorSheetName, 0);
        workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName));
        XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch();
        if (drawingPatriarch == null) {
            drawingPatriarch = errorSheet.createDrawingPatriarch();
        }
        for (int i = 0; i <= getHeaderRowNo(); i++) {
            XSSFRow newRow = errorSheet.createRow(i);
            XSSFRow row = sheet.getRow(i);
            newRow.setHeight(row.getHeight());
            copyRow(row, newRow, factory, drawingPatriarch);
        }

        // copy merged regions
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstRow() < getHeaderRowNo()) {
                errorSheet.addMergedRegion(mergedRegion);
            }
        }

        // copy images
        List<XSSFPictureData> pics = workbook.getAllPictures();
        List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes();
        for (int i = 0; i < shapes.size(); i++) {
            XSSFShape shape = shapes.get(i);
            XSSFAnchor anchor = shape.getAnchor();
            if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor;
                if (clientAnchor.getRow1() < getHeaderRowNo()) {
                    for (int j = 0; j < pics.size(); j++) {
                        XSSFPictureData picture = pics.get(j);
                        if (picture.getPackagePart().getPartName()
                                .equals(pic.getPictureData().getPackagePart().getPartName())) {
                            drawingPatriarch.createPicture(clientAnchor, j);
                        }
                    }
                }
            }
        }
    }

    try {
        // set comments in the original sheet
        XSSFDrawing patriarch = sheet.getDrawingPatriarch();
        for (CellReference cell : errorMessages.keySet()) {
            if (cell != null && errorMessages.get(cell) != null) {
                XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol()));
                boolean isNewComment = false;
                if (comment == null) {
                    XSSFClientAnchor anchor = factory.createClientAnchor();
                    anchor.setDx1(100);
                    anchor.setDx2(100);
                    anchor.setDy1(100);
                    anchor.setDy2(100);
                    anchor.setCol1(cell.getCol());
                    anchor.setCol2(cell.getCol() + 4);
                    anchor.setRow1(cell.getRow());
                    anchor.setRow2(cell.getRow() + 4);
                    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

                    comment = patriarch.createCellComment(anchor);
                    isNewComment = true;
                }
                XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n");
                rts.applyFont(boldFont);
                rts.append(errorMessages.get(cell), plainFont);
                comment.setString(rts);
                comment.setAuthor("Apache OFBiz PriCat");
                if (isNewComment) {
                    sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment);
                    OFBizPricatUtil.formatCommentShape(sheet, cell);
                }
            }
        }

        // set comments in the new error sheet
        XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch();
        int newRowNum = getHeaderRowNo() + 1;
        Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>();
        for (CellReference cell : errorMessages.keySet()) {
            if (cell != null && errorMessages.get(cell) != null) {
                XSSFRow row = sheet.getRow(cell.getRow());
                Integer rowNum = Integer.valueOf(row.getRowNum());
                int errorRow = newRowNum;
                if (rowMapping.containsKey(rowNum)) {
                    errorRow = rowMapping.get(rowNum).intValue();
                } else {
                    XSSFRow newRow = errorSheet.getRow(errorRow);
                    if (newRow == null) {
                        newRow = errorSheet.createRow(errorRow);
                    }
                    rowMapping.put(rowNum, Integer.valueOf(errorRow));
                    newRow.setHeight(row.getHeight());
                    copyRow(row, newRow, factory, errorPatriarch);
                    newRowNum++;
                }
            }
        }

        // write to file
        if (sequenceNum > 0L) {
            File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx");
            fos = new FileOutputStream(commentedExcel);
            workbook.write(fos);
        } else {
            fos = new FileOutputStream(pricatFile);
            workbook.write(fos);
        }
        fos.flush();
        fos.close();
        workbook.close();
    } catch (FileNotFoundException e) {
        report.println(e);
        Debug.logError(e, module);
    } catch (IOException e) {
        report.println(e);
        Debug.logError(e, module);
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (IOException e) {
                Debug.logError(e, module);
            }
        }
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                Debug.logError(e, module);
            }
        }
    }
    report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
    report.println();
}

From source file:ReadExcel.HSSFReadWrite.java

License:Apache License

private static void startReadXlsxFile(String fileName) {
    try {/*w ww .  j  a  va2s.c o  m*/
        XSSFWorkbook wb = HSSFReadWrite.readxlsxFile(fileName);
        System.out.println("Data dump:\n");
        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            XSSFSheet sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();
            System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
            for (int r = 0; r < rows; r++) {
                XSSFRow row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                int cells = row.getPhysicalNumberOfCells();
                System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
                for (int c = 0; c < cells; c++) {
                    XSSFCell cell = row.getCell(c);
                    String value = null;
                    switch (cell.getCellTypeEnum()) {
                    case FORMULA:
                        value = "FORMULA value=" + cell.getCellFormula();
                        break;
                    case NUMERIC:
                        value = "NUMERIC value=" + cell.getNumericCellValue();
                        break;
                    case STRING:
                        value = "STRING value=" + cell.getStringCellValue();
                        break;
                    default:
                    }
                    System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                }
            }
        }
        wb.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java

License:Open Source License

/**
 * Creates a merged span to make all filter sections the same size and style.
 *
 * @param fromColumn/*from w  ww. jav a  2  s. c  o m*/
 * @param style
 * @param sheet
 * @param row
 */
private void createMergedCellFromColumn(int fromColumn, XSSFCellStyle style, XSSFSheet sheet, XSSFRow row) {
    // Create and style cells for the span
    for (int i = fromColumn + 1; i <= FILTER_END_COLUMN_SPAN; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);

    }
    sheet.addMergedRegion(
            new CellRangeAddress(row.getRowNum(), row.getRowNum(), fromColumn, FILTER_END_COLUMN_SPAN));
}

From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java

License:Open Source License

private void createDataCell(XSSFRow row, int colIndex, String value) {
    XSSFCell cell = row.createCell(colIndex);
    cell.setCellValue(value);//from  w w w  . j av a2s .  c  om
    cell.setCellStyle(row.getRowNum() % 2 == 0 ? stripedDarker : stripedLighter);
}

From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java

License:Open Source License

private void createRichTextDataCell(XSSFRow row, int colIndex, XSSFRichTextString value) {
    XSSFCell cell = row.createCell(colIndex);
    cell.setCellValue(value);/*from   www  .  j  a v  a 2s .c o m*/
    cell.setCellStyle(row.getRowNum() % 2 == 0 ? stripedDarker : stripedLighter);
}

From source file:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {//from   w  w  w .j  a va 2s  . c  o  m
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    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);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}