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.github.crab2died.ExcelUtils.java

License:Open Source License

private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine,
        int sheetIndex) {

    List<List<String>> list = new ArrayList<>();
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();/*w  ww .j a  v a 2s .c  o  m*/
    for (int i = offsetLine; i <= maxLine; i++) {
        List<String> rows = new ArrayList<>();
        Row row = sheet.getRow(i);
        if (null == row)
            continue;
        for (Cell cell : row) {
            String val = Utils.getCellValue(cell);
            rows.add(val);
        }
        list.add(rows);
    }
    return list;
}

---

From source file:com.github.cutstock.utils.ProfileUtils.java

License:Apache License

public static Profiles parseFile(String filePath) {
    Workbook workbook = null;//w ww  .  ja  v  a2s .com
    Sheet sheet = null;
    // CutStockPlugin.getLogger().log(new Status(0,
    // CutStockPlugin.PLUGIN_ID, filePath));
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        if (filePath.toLowerCase().endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }

    } catch (IOException e) {
        MessageDialog.openError(null, "ERROR",
                "?EXCEL,?????Excel");
    } finally {
        try {
            if (is != null) {
                is.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    sheet = workbook.getSheetAt(0);
    int sheetRowNum = sheet.getLastRowNum();
    Profiles proflies = new Profiles();
    for (int i = 1; i <= sheetRowNum; i++) {
        Row currentRow = sheet.getRow(i);
        if (currentRow != null) {
            Cell cell = currentRow.getCell(ColumnType.PROFILE_NAME);
            String name = (String) getCellValue(cell);
            if (StringUtil.Empty(name)) {
                break;
            }
            cell = currentRow.getCell(ColumnType.PROFILE_CODE_DATA);
            String codeData = (String) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_COLOR);
            String color = (String) getCellValue(cell);

            cell = currentRow.getCell(ColumnType.PROFILE_WIDTH);
            double width = (Double) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_AMOUNT);
            int amount = ((Double) getCellValue(cell)).intValue();

            proflies.add(name, codeData, color, new BigDecimal(width), amount);
        }
    }
    return proflies;
}

---

From source file:com.github.drbookings.ical.XlsxBookingFactory.java

License:Open Source License

@Override
public Collection<BookingBeanSer> build() {
    final List<BookingBeanSer> bookings = new ArrayList<>();
    FileInputStream stream = null;
    Workbook workbook = null;//from  w  w w .  j a v a 2  s.c  o m
    try {
        stream = new FileInputStream(file);
        workbook = new HSSFWorkbook(stream);
        final Sheet sheet = workbook.getSheetAt(0);
        if (logger.isInfoEnabled()) {
            logger.info("Processing sheet " + sheet.getSheetName());
        }
        final int indexBookingNumber = FileFormatBookingXLS.getColumnIndexBookingNumber(sheet.getRow(0));
        final int indexClientName = FileFormatBookingXLS.getColumnIndexClientName(sheet.getRow(0));
        final int indexBookingCheckIn = FileFormatBookingXLS.getColumnIndexCheckIn(sheet.getRow(0));
        final int indexBookingCheckOut = FileFormatBookingXLS.getColumnIndexCheckOut(sheet.getRow(0));
        final int indexStatus = FileFormatBookingXLS.getColumnIndexStatus(sheet.getRow(0));
        final List<Integer> bookingNumbers = new ArrayList<>();
        final List<String> guestNames = new ArrayList<>();
        final List<String> stati = new ArrayList<>();
        final List<LocalDate> bookingCheckIn = new ArrayList<>();
        final List<LocalDate> bookingCheckOut = new ArrayList<>();
        for (final Row r : sheet) {
            // skip first row
            if (r.getRowNum() == 0) {
                continue;
            }
            bookingNumbers.add(FileFormatBookingXLS.getBookingNumber(r.getCell(indexBookingNumber)));
            guestNames.add(FileFormatBookingXLS.getString(r.getCell(indexClientName)));
            bookingCheckIn.add(FileFormatBookingXLS.getDate(r.getCell(indexBookingCheckIn)));
            bookingCheckOut.add(FileFormatBookingXLS.getDate(r.getCell(indexBookingCheckOut)));
            stati.add(FileFormatBookingXLS.getString(r.getCell(indexStatus)));
        }
        if (logger.isDebugEnabled()) {
            logger.debug("BookingBean numbers: " + bookingNumbers);
            logger.debug("Guest names: " + guestNames);
            logger.debug("Check-in dates: " + bookingCheckIn);
            logger.debug("Check-out dates: " + bookingCheckOut);
        }
        if (logger.isInfoEnabled()) {
            logger.info("Building bookings.. ");
        }

        for (int i = 0; i < bookingNumbers.size(); i++) {
            final int number = bookingNumbers.get(i);
            final LocalDate checkIn = bookingCheckIn.get(i);
            final LocalDate checkOut = bookingCheckOut.get(i);
            final String names = guestNames.get(i);
            final String status = stati.get(i);
            if (status.equals("ok")) {
                final BookingBeanSer bb = new BookingBeanSer();
                bb.checkInDate = checkIn;
                bb.checkOutDate = checkOut;
                bb.guestName = names;
                bb.externalId = Integer.toString(number);
                bookings.add(bb);
            } else {
                if (logger.isDebugEnabled()) {
                    logger.debug("Skipping status " + status);
                }
            }
        }
    } catch (final Exception e) {
        if (logger.isErrorEnabled()) {
            logger.error(e.getLocalizedMessage(), e);
        }
    } finally {
        if (workbook != null) {
            IOUtils.closeQuietly(workbook);
        }
        if (stream != null) {
            IOUtils.closeQuietly(stream);
        }
    }
    return bookings;
}

---

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * /*ww w .  ja  v a  2  s . c  om*/
 * Adds a hyperlink into a cell. The contents of the cell remains
 * peronachalnoe. Do not forget to fill in the contents of the cell before
 * add a hyperlinks. If a row already has been flushed, this method not
 * work!
 * 
 * @param sheet
 *            Sheet
 * @param rownum
 *            number of row
 * @param colnum
 *            number of column
 * @param url
 *            hyperlink
 */
public void createHyperlink(Sheet sheet, int rownum, int colnum, String url) {
    Row row = sheet.getRow(rownum);
    if (url != null && !"".equals(url)) {
        Cell cell = row.getCell(colnum);
        CreationHelper createHelper = workbook.getCreationHelper();
        XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
        hyperlink.setAddress(url);
        cell.setHyperlink(hyperlink);
        cell.setCellStyle(getHyperlinkCellStyle(rownum, url));
    }
}

---

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * Format a table of worksheet/*from www. jav  a  2s . c o  m*/
 *
 * @param sheet
 *            Name of sheet
 * @param withHeader
 *            <code>true</code> for create auto filter and freeze pane in
 *            first row, otherwise <code>false</code>
 */
public void setAutoSizeColumns(Sheet sheet, boolean withHeader) {
    if (sheet.getLastRowNum() > 0) {
        if (withHeader) {
            int x = sheet.getRow(sheet.getLastRowNum()).getLastCellNum();
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, x - 1);
            sheet.setAutoFilter(range);
            sheet.createFreezePane(0, 1);
        }
        // auto-sizing columns
        if (columnWidth.containsKey(sheet.getSheetName())) {
            Map<Integer, Integer> width = columnWidth.get(sheet.getSheetName());
            for (Map.Entry<Integer, Integer> entry : width.entrySet()) {
                sheet.setColumnWidth(entry.getKey(), entry.getValue());
            }
        }
    }
}

---

From source file:com.github.jaydsolanki.excelio.ExcelIO.java

private List<List<String>> readSheet(Sheet sheet) {
    List<List<String>> data = new ArrayList<>();
    for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i);
        List<String> rowList = new ArrayList<String>();
        for (int j = 0; j < row.getLastCellNum(); j++) {
            rowList.add(row.getCell(j) + "");
        }/*from w w  w .j a v  a2  s. c om*/
        data.add(rowList);
    }
    return data;
}

---

From source file:com.github.jaydsolanki.excelio.ExcelIO.java

private boolean insertCell(Object obj, Sheet sheet, int rowNo, int cellNo) {

    if (sheet == null) {
        return false;
    }/*  w w w .  java 2  s. c om*/

    Row row = sheet.getRow(rowNo);
    if (row == null) {
        row = sheet.createRow(rowNo);
    }
    Cell cell = row.getCell(cellNo);
    if (cell == null) {
        cell = row.createCell(cellNo);
    }
    cell.setCellValue(obj.toString());
    return true;
}

---

From source file:com.github.pascalgn.jiracli.testutil.ExcelUtils.java

License:Apache License

/**
 * @param row 0-based index/*w ww . j a va 2s . c om*/
 * @param column 0-based index
 */
public static void writeCell(Sheet sheet, int row, int column, String value) {
    Row r = sheet.getRow(row);
    if (r == null) {
        r = sheet.createRow(row);
    }
    Cell cell = r.getCell(column);
    if (cell == null) {
        cell = r.createCell(column, Cell.CELL_TYPE_STRING);
    }
    cell.setCellValue(value);
}

---

From source file:com.github.xiilei.ecdiff.Processor.java

License:Apache License

public void diff() {
    try {//from  ww  w  . jav a  2  s .c o  m
        logger.info("start ,src:" + job.getSrc() + ",dist:" + job.getDist());
        Store store = this.getStoreFromSrc();
        Workbook wb = readExcelFileByext(job.getDist());
        this.font = wb.createFont();
        this.font.setColor((short) 0xa);
        int rows_len = 0, i = 0, max_cells_len = 0;
        Sheet sheet = wb.getSheetAt(job.getDistSheet());
        rows_len = sheet.getPhysicalNumberOfRows();
        logger.info("Dist,open " + sheet.getSheetName() + " with " + rows_len + " rows");
        for (i = 0; i < rows_len; i++) {
            Row row = sheet.getRow(i);
            max_cells_len = row.getPhysicalNumberOfCells();
            if (!job.checkDistIndex(max_cells_len)) {
                logger.warn("Dist,The length of columns is too small at row " + i + ",length:" + max_cells_len);
                continue;
            }
            if (job.isByrow()) {
                cellComparer(store.get(i), row.getCell(job.getDistColumnIndex()));
            } else {
                cellComparer(store.get(getStringCellValue(row.getCell(job.getDistColumnIdIndex()))),
                        row.getCell(job.getDistColumnIndex()));
            }
        }
        try (FileOutputStream out = new FileOutputStream(job.getOutFileName())) {
            wb.write(out);
        }
        logger.info("output file:" + job.getOutFileName());
    } catch (Exception e) {
        logger.fatal(e.getMessage(), e);
        //                e.printStackTrace();
    }
}

---

From source file:com.github.xiilei.ecdiff.Processor.java

License:Apache License

public Store getStoreFromSrc() throws IOException {
    Workbook wb = readExcelFileByext(job.getSrc());
    Row row = null;/*from  w  w  w  .  j a v a  2s  .c om*/
    int max_cells_len = 0;
    int rows_len = 0;
    Sheet sheet = wb.getSheetAt(job.getSrcSheet());
    rows_len = sheet.getPhysicalNumberOfRows();
    Store store = new Store(rows_len);
    logger.info("Src,open " + sheet.getSheetName() + " with " + rows_len + " rows");
    for (int i = 0; i < rows_len; i++) {
        row = sheet.getRow(i);
        max_cells_len = row.getPhysicalNumberOfCells();
        if (!job.checkSrcIndex(max_cells_len)) {
            logger.warn("Src,The length of columns is too small at row " + i + ",length:" + max_cells_len);
            continue;
        }
        if (job.isByrow()) {
            store.put(i, row.getCell(job.getSrcColumnIndex()));
        } else {
            store.put(getStringCellValue(row.getCell(job.getSrcColumnIdIndex())),
                    row.getCell(job.getSrcColumnIndex()));
        }
    }
    return store;
}

---

• «
• 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
• »