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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.github.cutstock.excel.model.SheetBuilder.java

License:Apache License

public SheetBuilder createColumns(ICellInfo columns) {
    IExcelRectangle rect = columns.getRect();
    int rowLine = rect.getStartRow();
    Row row = createRow(rowLine);/*  w w w  .  j  a v a  2 s. c  o  m*/
    // String colName = columns.getText();
    // String[] colNames = colName.split(",");
    Object[] colNames = columns.getColumns();
    for (int i = rect.getStartCol(), j = rect.getEndCol(), index = 0; i <= j; i++, index++) {
        Cell colCell = row.createCell(i);
        // cut num should cast to number 5,13
        if (colNames[index] instanceof BigDecimal || colNames[index] instanceof Integer) {
            colCell.setCellValue(Double.parseDouble(colNames[index].toString()));
            colCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        } else {
            colCell.setCellValue(colNames[index].toString());
        }
        CellStyle style = styles.get(columns.getCellType().typeValue());
        colCell.setCellStyle(style);
    }

    Row preRow = createRow(rowLine - 1);
    if (preRow != null) {
        Cell nameCel = preRow.getCell(rect.getStartCol());
        if (nameCel != null) {
            if (nameCel.getStringCellValue().equals(row.getCell(rect.getStartCol()).getStringCellValue())) {
                mergeRegion(ExcelModelFactory.createCellRect(rect.getStartCol(), rect.getStartCol(),
                        rowLine - 1, rowLine));
            }
        }
    }
    return this;
}

---

From source file:com.github.cutstock.excel.model.SheetBuilder.java

License:Apache License

public void createTitle(ICellInfo title) {
    IExcelRectangle titleRect = title.getRect();
    int row = titleRect.getStartRow();
    Row titleRow = createRow(row);
    titleRow.setHeightInPoints(titleRect.getHeight());
    int startCol = titleRect.getStartCol();
    Cell titleCell = titleRow.getCell(startCol);
    if (titleCell == null) {
        titleCell = titleRow.createCell(startCol);
    }//from  ww  w. j  av  a  2  s  . c  om
    titleCell.setCellValue(title.getColumns()[0].toString());
    mergeRegion(titleRect);
    CellStyle style = styles.get(title.getCellType().typeValue());
    titleCell.setCellStyle(style);
}

---

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

License:Apache License

public static Profiles parseFile(String filePath) {
    Workbook workbook = null;/*from ww  w .  j a  va  2 s .c  om*/
    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;/*  ww  w.j  ava2s .com*/
    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.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;// ww  w  .  ja v a 2  s .  c o  m
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

---

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

License:Apache License

/**
 * /*  www .ja  va  2s.  c  o m*/
 * 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.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) + "");
        }// w w w.  java  2s  . c  o m
        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;
    }/*  ww  w  .j av  a  2  s  .  c  o  m*/

    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.ko2ic.plugin.eclipse.taggen.core.domain.model.spreadsheet.SheetImpl.java

License:Open Source License

private Cell getCell(int columnIndex, int rowIndex) throws InvalidCellIndexException {
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        throw new InvalidCellIndexException(columnIndex, rowIndex);
    }//from  www .  j  ava2s  . c  o  m
    Cell cell = row.getCell(columnIndex);
    return cell;
}

---

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

License:Apache License

/**
 * @param row 0-based index/*from w w w.  j  a v  a2s . c  o  m*/
 * @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);
}

---

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