Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:com.github.autoprimer3.Primer3ResultViewController.java

License:Open Source License

private void writePrimersToExcel(final File f) throws IOException {
    final Service<Void> service = new Service<Void>() {
        @Override//from  ww  w  .  ja  va  2  s. co m
        protected Task<Void> createTask() {
            return new Task<Void>() {
                @Override
                protected Void call() throws IOException {
                    BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f));
                    Workbook wb = new XSSFWorkbook();
                    CellStyle hlink_style = wb.createCellStyle();
                    Font hlink_font = wb.createFont();
                    hlink_font.setUnderline(Font.U_SINGLE);
                    hlink_font.setColor(IndexedColors.BLUE.getIndex());
                    hlink_style.setFont(hlink_font);
                    CreationHelper createHelper = wb.getCreationHelper();
                    Sheet listSheet = wb.createSheet();
                    Sheet detailsSheet = wb.createSheet();
                    Row row = null;
                    int rowNo = 0;
                    int sheetNo = 0;
                    wb.setSheetName(sheetNo++, "List");
                    wb.setSheetName(sheetNo++, "Details");

                    row = listSheet.createRow(rowNo++);
                    String header[] = { "Primer", "Sequence", "Product Size (bp)" };
                    for (int col = 0; col < header.length; col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(header[col]);
                    }

                    updateMessage("Writing primers . . .");
                    updateProgress(0, data.size() * 3);
                    int n = 0;
                    for (Primer3Result r : data) {
                        n++;
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "F");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        updateProgress(n, data.size() * 3);
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        col = 0;
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "R");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        n++;
                        updateProgress(n, data.size() * 3);
                    }
                    rowNo = 0;
                    row = detailsSheet.createRow(rowNo++);
                    ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs",
                            "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR"));
                    if (ispcrResCol.isVisible()) {
                        detailsHeader.add("in-silico PCR Results");
                    }
                    for (int col = 0; col < detailsHeader.size(); col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(detailsHeader.get(col));
                    }
                    int m = 0;
                    for (Primer3Result r : data) {
                        m++;
                        updateMessage("Writing details for pair " + m + " . . .");
                        row = detailsSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getTranscripts());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRegion());
                        cell = row.createCell(col++);
                        if (r.getIsPcrUrl() != null) {
                            cell.setCellValue("isPCR");
                            org.apache.poi.ss.usermodel.Hyperlink hl = createHelper
                                    .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL);
                            hl.setAddress(r.getIsPcrUrl());
                            cell.setHyperlink(hl);
                            cell.setCellStyle(hlink_style);
                        } else {
                            cell.setCellValue("");
                        }
                        if (ispcrResCol.isVisible()) {
                            cell = row.createCell(col++);
                            if (r.getIsPcrResults() != null) {
                                cell.setCellValue(r.getIsPcrResults());
                            } else {
                                cell.setCellValue("");
                            }
                        }
                        updateProgress(n + m, data.size() * 3);
                    }

                    updateMessage("Wrote " + data.size() + " primer pairs to file.");
                    wb.write(bo);
                    bo.close();
                    return null;
                }
            };
        }

    };

    service.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {

            Action response = Dialogs.create().title("Done").masthead("Finished writing")
                    .message("Primers successfully written to " + f.getAbsolutePath()
                            + "\n\nDo you want to open " + "this file now?")
                    .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE)
                    .showConfirm();

            if (response == Dialog.ACTION_YES) {
                try {
                    openFile(f);
                } catch (IOException ex) {
                    Action openFailed = Dialogs.create().title("Open failed")
                            .masthead("Could not open output file")
                            .message("Exception encountered when attempting to open "
                                    + "the saved file. See below:")
                            .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex);
                }
            }
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnCancelled(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled")
                    .masthead("Cancelled writing to file").message("User cancelled writing primers to file.")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE);
            writeCancelled.showInformation();
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnFailed(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Action writeFailed = Dialogs.create().title("Writing failed")
                    .masthead("Could not write primers to file")
                    .message("Exception encountered when attempting to write " + "primers to file. See below:")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException());
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    progressBar.setVisible(true);
    progressBar.progressProperty().bind(service.progressProperty());
    summaryLabel.textProperty().bind(service.messageProperty());
    closeButton.setDisable(true);
    closeMenuItem.setDisable(true);
    checkIsPcrButton.setText("Cancel");
    checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() {
        @Override
        public void handle(ActionEvent actionEvent) {
            service.cancel();

        }
    });
    service.start();
}

From source file:com.github.crab2died.handler.SheetTemplateHandler.java

License:Open Source License

private static void clearCell(Cell cell) {
    cell.setCellStyle(null);
    cell.setCellValue("");
}

From source file:com.github.crab2died.handler.SheetTemplateHandler.java

License:Open Source License

/**
 * ??/* www  .ja  v a 2s.  c o m*/
 *
 * @param cell     cell
 * @param styleKey ?
 */
private static void setCellStyle(SheetTemplate template, Cell cell, String styleKey) {
    if (null != styleKey && null != template.classifyStyle.get(styleKey)) {
        cell.setCellStyle(template.classifyStyle.get(styleKey));
        return;
    }

    if (null != template.appointLineStyle && template.appointLineStyle.containsKey(cell.getRowIndex())) {
        cell.setCellStyle(template.appointLineStyle.get(cell.getRowIndex()));
        return;
    }
    if (null != template.singleLineStyle && (cell.getRowIndex() % 2 != 0)) {
        cell.setCellStyle(template.singleLineStyle);
        return;
    }
    if (null != template.doubleLineStyle && (cell.getRowIndex() % 2 == 0)) {
        cell.setCellStyle(template.doubleLineStyle);
        return;
    }
    if (null != template.defaultStyle)
        cell.setCellStyle(template.defaultStyle);
}

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);//from www . ja v  a  2  s. c om
    // 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);//from w ww .j  ava2s  .  c  om
    titleRow.setHeightInPoints(titleRect.getHeight());
    int startCol = titleRect.getStartCol();
    Cell titleCell = titleRow.getCell(startCol);
    if (titleCell == null) {
        titleCell = titleRow.createCell(startCol);
    }
    titleCell.setCellValue(title.getColumns()[0].toString());
    mergeRegion(titleRect);
    CellStyle style = styles.get(title.getCellType().typeValue());
    titleCell.setCellStyle(style);
}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private void setExcelCell(HSSFWorkbook workbook, Object cellValue, Cell excelCell) {
    if (cellValue instanceof BigDecimal) {
        double doubleValue = ((BigDecimal) cellValue).doubleValue();
        excelCell.setCellValue(doubleValue);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00"));
        excelCell.setCellStyle(cellStyle);
        excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (cellValue instanceof LocalDate) {
        LocalDate localDate = (LocalDate) cellValue;
        Calendar calendar = Calendar.getInstance();
        calendar.set(localDate.getYear(), localDate.getMonthValue() - 1, localDate.getDayOfMonth());
        excelCell.setCellValue(calendar);

        String excelFormatPattern = DateFormatConverter.convert(Locale.US, "yyyy-MM-DD");
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat poiFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        excelCell.setCellStyle(cellStyle);
    } else if (cellValue != null) {
        excelCell.setCellValue(cellValue.toString());
    }/*w w w .j  a  va 2s  . c o m*/
}

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

License:Apache License

/**
 * Creates new row in the worksheet//from  w  ww.  j a  v  a  2  s . c  om
 *
 * @param sheet
 *            Sheet
 * @param values
 *            the value of the new cell line
 * @param header
 *            <code>true</code> if this row is the header, otherwise
 *            <code>false</code>
 * @param withStyle
 *            <code>true</code> if in this row will be applied styles for
 *            the cells, otherwise <code>false</code>
 * @return created row
 */
public Row createRow(Sheet sheet, Object[] values, boolean header, boolean withStyle) {
    Row row;
    String sheetName = sheet.getSheetName();
    int rownum = 0;
    if (rows.containsKey(sheetName)) {
        rownum = rows.get(sheetName);
    }
    // create new row
    row = sheet.createRow(rownum);
    // create a cells of row
    for (int x = 0; x < values.length; x++) {
        Object o = values[x];
        Cell cell = row.createCell(x);
        if (o != null) {
            if (o.getClass().getName().contains("String")) {
                String value = (String) values[x];
                cell.setCellValue(value);
            } else if (o.getClass().getName().contains("Double")) {
                cell.setCellValue((Double) values[x]);
            } else if (o.getClass().getName().contains("Integer")) {
                cell.setCellValue((Integer) values[x]);
            } else if (o.getClass().getName().contains("Date")) {
                cell.setCellValue((Date) values[x]);
            }
            if (withStyle) {
                cell.setCellStyle(getCellStyle(rownum, values[x], header));
            }
        }
        // save max column width
        if (!header) {
            saveColumnWidth(sheet, x, o);
        }
    }
    // save the last number of row for this worksheet
    rows.put(sheetName, ++rownum);
    return row;
}

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

License:Apache License

/**
 * //from   w w  w  .  ja v  a2s. 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.jferard.spreadsheetwrapper.xls.poi.XlsPoiStyleHelper.java

License:Open Source License

public boolean setStyle(Workbook workbook, Cell poiCell, WrapperCellStyle wrapperCellStyle) {
    final CellStyle cellStyle = this.toCellStyle(workbook, wrapperCellStyle);
    poiCell.setCellStyle(cellStyle);
    return true;/*  w  w  w.  j  av a2s. com*/
}

From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiStyleHelper.java

License:Open Source License

public boolean setSyleName(Workbook workbook, Cell cell, String styleName) {
    final CellStyle cellStyle = this.getCellStyle(workbook, styleName);
    if (cellStyle == null)
        return false;
    else {/* w w  w  .  ja v a 2  s.  co  m*/
        cell.setCellStyle(cellStyle);
        return true;
    }
}