Example usage for org.apache.poi.ss.usermodel Sheet getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.stam.excellatin.ExcelLatin.java

public static void main(String[] args) {
    List<String> options = new ArrayList<>();
    int startIndex = 0;
    for (String arg : args) {
        if (validOptions.contains(arg)) {
            options.add(arg);//  w w  w.  j a  v  a 2s.c  om
            startIndex++;
        }
    }

    if (args[0].equals("-h") || args.length < 3) {
        System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames...");
        System.out.println("options:");
        System.out.println("\t-L\tto Latin (default)");
        System.out.println("\t-G\tto Greek");
        System.out.println("\t-d\tdon't deaccent");
        System.out.println("\t-h\thelp");
    } else {
        boolean greekToLatin = false;
        boolean latinToGreek = false;
        Transliterator transliterator = null;
        if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) {
            transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN");
            System.out.println("\nTransliterating Greek to Latin");
            greekToLatin = true;
        } else if (options.contains("-G")) {
            transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN");
            System.out.println("\nTransliterating Latin to Greek");
            latinToGreek = true;
        }

        if (transliterator == null) {
            System.out.println("Not a valid option for the transliteration language");
            return;
        }

        boolean deAccent = true;
        if (options.contains("-d")) {
            deAccent = false;
            System.out.println("Will not deaccent");
        }

        String fileNameIn = args[startIndex];
        String fileNameOut = args[startIndex + 1];
        List<String> columnNames = new ArrayList<>();
        System.out.println("\nColumns to transliterate\n---------------------------");
        for (int i = startIndex + 2; i < args.length; i++) {
            columnNames.add(args[i]);
            System.out.println(args[i]);
        }
        System.out.println("\n");

        try {
            File file = new File(fileNameIn);
            if (!file.exists()) {
                System.out.println("The file " + fileNameIn + " was not found");
                return;
            }

            Map<String, String> mapTransformations = new HashMap<>();
            Scanner sc = new Scanner(new FileReader("map.txt"));
            while (sc.hasNextLine()) {
                String greekEntry = sc.next();
                String latinEntry = sc.next();

                if (greekToLatin) {
                    mapTransformations.put(greekEntry, latinEntry);
                } else if (latinToGreek) {
                    mapTransformations.put(latinEntry, greekEntry);
                }
            }

            DataFormatter formatter = new DataFormatter();
            Workbook wb = WorkbookFactory.create(file);

            Workbook newWb = null;
            if (wb instanceof HSSFWorkbook) {
                newWb = new HSSFWorkbook();
            } else if (wb instanceof XSSFWorkbook) {
                newWb = new XSSFWorkbook();
            }
            FileOutputStream fileOut = new FileOutputStream(fileNameOut);
            if (newWb != null) {
                Sheet sheetOut = newWb.createSheet();

                Sheet sheet = wb.getSheetAt(0);

                List<Integer> idxs = new ArrayList<>();

                Row row = sheet.getRow(0);
                for (Cell cell : row) {
                    String cellVal = formatter.formatCellValue(cell);
                    if (cellVal == null || cellVal.trim().equals("")) {
                        break;
                    }

                    if (columnNames.contains(cell.getStringCellValue())) {
                        idxs.add(cell.getColumnIndex());
                    }
                }

                for (Row rowIn : sheet) {
                    Row rowOut = sheetOut.createRow(rowIn.getRowNum());
                    if (rowIn.getRowNum() == 0) {
                        for (Cell cell : rowIn) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            Cell cellOut = rowOut.createCell(cell.getColumnIndex());
                            cellOut.setCellValue(cell.getStringCellValue());
                        }
                    } else {
                        for (Cell cell : rowIn) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String cellVal = formatter.formatCellValue(cell);
                            String cellNewVal = cellVal;
                            if (idxs.contains(cell.getColumnIndex()) && cellVal != null) {
                                if (mapTransformations.containsKey(cellVal)) {
                                    cellNewVal = mapTransformations.get(cellVal);
                                } else {
                                    if (deAccent) {
                                        cellNewVal = deAccent(transliterator.transform(cellVal));
                                    } else {
                                        cellNewVal = transliterator.transform(cellVal);
                                    }
                                }
                            }
                            Cell cellOut = rowOut.createCell(cell.getColumnIndex());
                            cellOut.setCellValue(cellNewVal);
                        }
                    }
                }

                System.out.println("Finished!");

                newWb.write(fileOut);
                fileOut.close();
            }
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex);
        }
    }

}

---

From source file:com.surenpi.autotest.suite.parser.ExcelSuiteParser.java

License:Apache License

/**
 * @param sheet//from  w w w .ja  v  a 2s. co  m
 * @param suitePage
 * @return
 */
private boolean sheetParse(Sheet sheet, SuitePage suitePage) {
    if (sheet.getSheetName().equals("SuiteConfig")) {
        for (int i = 0; i < maxRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }

            Cell keyCell = row.getCell(0);
            Cell valCell = row.getCell(1);
            if (keyCell == null || valCell == null) {
                continue;
            }

            String keyCellVal = keyCell.getStringCellValue();
            String valCellVal = valCell.getStringCellValue();
            if (keyCellVal.equals("PageConfig")) {
                suite.setXmlConfPath(valCellVal);
            } else if (keyCellVal.equals("PagePackage")) {
                suite.setPagePackage(valCellVal);
            } else if (keyCellVal.equals("AfterSleep")) {
                try {
                    suite.setAfterSleep(Long.parseLong(valCellVal));
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                }
            }
        }

        return false;
    }

    suitePage.setPage(sheet.getSheetName());
    suitePage.setActionList(new ArrayList<SuiteAction>());

    for (int i = 0; i < maxRows; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            break;
        }

        SuiteAction suiteAction = new SuiteAction();
        suiteAction.setRepeat(1);
        rowParse(row, suiteAction);
        suitePage.getActionList().add(suiteAction);
    }

    return true;
}

---

From source file:com.svi.main.logic.ExtractAndPrint.java

private void writeProjectDetails(Sheet mainSheet) {
    Row mainSheetRow;//from  w w w  .  ja v a2 s  .c  om
    mainSheetRow = mainSheet.getRow(1);
    Cell cell = mainSheetRow.createCell(2);
    cell.setCellValue(cluster);

    DateFormat dateFormat = new SimpleDateFormat("MMM dd, yyyy HH:mm");
    Date date = new Date();
    mainSheetRow = mainSheet.getRow(2);
    cell = mainSheetRow.createCell(2);
    cell.setCellValue(dateFormat.format(date));
}

---

From source file:com.svi.uzabase.logic.ExtractData.java

private List<String> extractCompanyNames() {
    List<String> companyList = new ArrayList<>();
    try {//from  www .  j  av a 2  s. c  o m
        FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH));
        Workbook workbook = new HSSFWorkbook(inputStream);
        Row row;
        Cell cell;
        Sheet sheet;
        sheet = workbook.getSheetAt(0);
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);
            for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                cell = row.getCell(colIndex);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if (colIndex == 1) {
                    companyList.add(cell.getStringCellValue().toUpperCase());
                }
            }
        }
        //Closes opened documents
        inputStream.close();
        workbook.close();
    } catch (IOException ex) {
        Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex);
    }
    return companyList;
}

---

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

protected void readSheet(Sheet sheet, FlatFileReaderCallback<T> callback) {
    for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        if (rowIndex < skipLines + sheet.getFirstRowNum()) {
            continue;
        }//www  .java 2  s . c  o  m
        String[] tokens = readRow(row);
        T bean = rowMapper.getRow(tokens, rowIndex + 1);
        callback.processRow(rowIndex, tokens, bean);
    }
}

---

From source file:com.teeznar.poi.test.TestPoi.java

License:Open Source License

public void test() {
    InputStream inp;//from   w  w w  . j  a v a  2 s .c  o  m
    try {
        inp = new FileInputStream("sample-file.xlsx");

        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row arow : sheet) {

            for (Cell acell : arow) {
                System.out.print(" cell: " + acell);
            }
            System.out.println("\n");
        }

        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);

        System.out.println(cell.toString());

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

---

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public Row getRow(Sheet sheet, int i) {
    Row r = sheet.getRow(i);
    if (r == null) {
        r = sheet.createRow(i);/*  w w w.java 2s.  c  om*/
    }
    return r;
}

---

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void paintBorder(XSSFWorkbook wb, Sheet sheet, short borderType, BordeSeccion borde) {
    for (int i = borde.getUpperRow(); i <= borde.getLowerRow(); i++) {
        Row row = sheet.getRow(i);
        for (int j = borde.getLeftColumn(); j <= borde.getRightColumn(); j++) {
            if (i == borde.getUpperRow() || i == borde.getLowerRow() || j == borde.getLeftColumn()
                    || j == borde.getRightColumn()) {
                Cell cell = row.getCell(j);
                XSSFCellStyle actual = (XSSFCellStyle) cell.getCellStyle();
                XSSFCellStyle nuevo = wb.createCellStyle();
                nuevo.cloneStyleFrom(actual);
                if (i == borde.getUpperRow()) {
                    nuevo.setBorderTop(borderType);
                    nuevo.setTopBorderColor(IndexedColors.BLACK.getIndex());
                }//  www  . j a  va  2  s . co  m
                if (i == borde.getLowerRow()) {
                    nuevo.setBorderBottom(borderType);
                    nuevo.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getLeftColumn()) {
                    nuevo.setBorderLeft(borderType);
                    nuevo.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getRightColumn()) {
                    nuevo.setBorderRight(borderType);
                    nuevo.setRightBorderColor(IndexedColors.BLACK.getIndex());
                }
                cell.setCellStyle(nuevo);
            }
        }
    }
}

---

From source file:com.upbest.utils.AddDimensionedImage.java

License:Apache License

/**
 * Determines whether the sheets row should be re-sized to accomodate
 * the image, adjusts the rows height if necessary and creates then
 * returns a ClientAnchorDetail object that facilitates construction of
 * a ClientAnchor that will fix the image on the sheet and establish
 * it's size.//  w  w w.j a va  2 s.c om
 *
 * @param sheet A reference to the sheet that will 'contain' the image.
 * @param rowNumber A primitive int that contains the index number of a
 *                  row on the sheet.
 * @param reqImageHeightMM A primitive double that contains the required
 *                         height of the image in millimetres
 * @param resizeBehaviour A primitive int whose value will indicate how the
 *                        height of the row should be adjusted if the
 *                        required height of the image is greater than the
 *                        height of the row.
 * @return An instance of the ClientAnchorDetail class that will contain
 *         the index number of the row containing the cell whose top
 *         left hand corner also defines the top left hand corner of the
 *         image, the index number of the row containing the cell whose
 *         top left hand corner also defines the bottom right hand
 *         corner of the image and an inset that determines how far the
 *         bottom edge of the image can protrude into the next (lower)
 *         row - expressed as a specific number of coordinate positions.
 */
private ClientAnchorDetail fitImageToRows(Sheet sheet, int rowNumber, double reqImageHeightMM,
        int resizeBehaviour) {
    Row row = null;
    double rowHeightMM = 0.0D;
    double rowCoordinatesPerMM = 0.0D;
    int pictureHeightCoordinates = 0;
    ClientAnchorDetail rowClientAnchorDetail = null;

    // Get the row and it's height
    row = sheet.getRow(rowNumber);
    if (row == null) {
        // Create row if it does not exist.
        row = sheet.createRow(rowNumber);
    }

    // Get the row's height in millimetres
    rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE;

    // Check that the row's height will accomodate the image at the required
    // dimensions. If the height of the row is LESS than the required height
    // of the image, decide how the application should respond - resize the
    // row or overlay the image across a series of rows.
    if (rowHeightMM < reqImageHeightMM) {
        if ((resizeBehaviour == AddDimensionedImage.EXPAND_ROW)
                || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) {
            row.setHeightInPoints((float) (reqImageHeightMM * ConvertImageUnits.POINTS_PER_MILLIMETRE));
            if (sheet instanceof HSSFSheet) {
                rowHeightMM = reqImageHeightMM;
                rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
                pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
            } else {
                pictureHeightCoordinates = (int) (reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
            }
            rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
        }
        // If the user has chosen to overlay both rows and columns or just
        // to expand ONLY the size of the columns, then calculate how to lay
        // the image out ver one or more rows.
        else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)
                || (resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)) {
            rowClientAnchorDetail = this.calculateRowLocation(sheet, rowNumber, reqImageHeightMM);
        }
    }
    // Else, if the image is smaller than the space available
    else {
        if (sheet instanceof HSSFSheet) {
            rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
            pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
        } else {
            pictureHeightCoordinates = (int) (reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
        }
        rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
    }
    return (rowClientAnchorDetail);
}

---

From source file:com.upbest.utils.AddDimensionedImage.java

License:Apache License

/**
 * If the image is to overlie more than one rows, calculations need to be
 * performed to determine how many rows and whether the image will
 * overlie just a part of one row in order to be presented at the
 * required size.//from  w w  w . j  a v  a 2  s  .  c  o  m
 *
 * @param sheet The sheet that will 'contain' the image.
 * @param startingRow A primitive int whose value is the index of the row
 *                    that contains the cell whose top left hand corner
 *                    should be aligned with the top left hand corner of
 *                    the image.
 * @param reqImageHeightMM A primitive double whose value will indicate the
 *                         required height of the image in millimetres.
 * @return An instance of the ClientAnchorDetail class that will contain
 *         the index number of the row containing the cell whose top
 *         left hand corner also defines the top left hand corner of the
 *         image, the index number of the row containing the cell whose top
 *         left hand corner also defines the bottom right hand corner of
 *         the image and an inset that determines how far the bottom edge
 *         can protrude into the next (lower) row - expressed as a specific
 *         number of co-ordinate positions.
 */
private ClientAnchorDetail calculateRowLocation(Sheet sheet, int startingRow, double reqImageHeightMM) {
    ClientAnchorDetail clientAnchorDetail = null;
    Row row = null;
    double rowHeightMM = 0.0D;
    double totalRowHeightMM = 0.0D;
    double overlapMM = 0.0D;
    double rowCoordinatesPerMM = 0.0D;
    int toRow = startingRow;
    int inset = 0;

    // Step through the rows in the sheet and accumulate a total of their
    // heights.
    while (totalRowHeightMM < reqImageHeightMM) {
        row = sheet.getRow(toRow);
        // Note, if the row does not already exist on the sheet then create
        // it here.
        if (row == null) {
            row = sheet.createRow(toRow);
        }
        // Get the row's height in millimetres and add to the running total.
        rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE;
        totalRowHeightMM += rowHeightMM;
        toRow++;
    }
    // Owing to the way the loop above works, the rowNumber will have been
    // incremented one row too far. Undo that here.
    toRow--;
    // Check to see whether the image should occupy an exact number of
    // rows. If so, build the ClientAnchorDetail record to point
    // to those rows and with an inset of the total number of co-ordinate
    // position in the row.
    //
    // To overcome problems that can occur with comparing double values for
    // equality, cast both to int(s) to truncate the value; VERY crude and
    // I do not really like it!!
    if ((int) totalRowHeightMM == (int) reqImageHeightMM) {
        if (sheet instanceof HSSFSheet) {
            clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS);
        } else {
            clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    (int) reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
        }
    } else {
        // Calculate how far the image will project into the next row. Note
        // that the height of the last row assessed is subtracted from the
        // total height of all rows assessed so far.
        overlapMM = reqImageHeightMM - (totalRowHeightMM - rowHeightMM);

        // To prevent an exception being thrown when the required width of
        // the image is very close indeed to the column size.
        if (overlapMM < 0) {
            overlapMM = 0.0D;
        }

        if (sheet instanceof HSSFSheet) {
            rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
            inset = (int) (overlapMM * rowCoordinatesPerMM);
        } else {
            inset = (int) overlapMM * AddDimensionedImage.EMU_PER_MM;
        }
        clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow, inset);
    }
    return (clientAnchorDetail);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »