Example usage for org.apache.poi.ss.usermodel Workbook createCellStyle

List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createCellStyle

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:com.toba.bll.admin.ReportsDownloadServlet.java

/**
 * Handles the HTTP <code>GET</code> method.
 *
 * @param request servlet request/*from w  w  w.  j  a  va2s.  c  om*/
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Users Registered in the Past Month");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("User Name");
    row.createCell(1).setCellValue("First Name");
    row.createCell(2).setCellValue("Last Name");
    row.createCell(3).setCellValue("Registration Date");

    Calendar calendar = Calendar.getInstance();
    calendar.add(Calendar.MONTH, -1);
    Date oneMonthAgo = calendar.getTime();

    List<User> usersRegisteredInThePastMonth = UserDB.selectUsersRegisteredAfter(oneMonthAgo);

    HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle();
    short dateDataFormat = workbook.createDataFormat().getFormat("dd/MM/yyyy");
    dateCellStyle.setDataFormat(dateDataFormat);

    for (int i = 0; i < usersRegisteredInThePastMonth.size(); i++) {
        User user = usersRegisteredInThePastMonth.get(i);

        row = sheet.createRow(1 + i);
        row.createCell(0).setCellValue(user.getUserName());
        row.createCell(1).setCellValue(user.getFirstName());
        row.createCell(2).setCellValue(user.getLastName());
        row.createCell(3).setCellValue(user.getRegistrationDate());
        row.getCell(3).setCellStyle(dateCellStyle);
    }

    workbook.write(response.getOutputStream());
    workbook.close();

    response.setHeader("content-disposition", "attachment; filename=users.xls");
    response.setHeader("cache-control", "no-cache");
}

From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;//ww w  .  ja  v a  2s  .co m

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;/*ww w  .j a  v  a2 s  .com*/

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new SXSSFWorkbook(100);

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Updates the cell value and type, causes a recalculation of all the values
 * in the cell./*  www . j av a  2s.c om*/
 *
 * If there is a {@link CellValueHandler} defined, then it is used.
 *
 * Cells starting with "=" or "+" will be created/changed into FORMULA type.
 *
 * Cells that are existing and are NUMERIC type will be parsed according to
 * their existing format, or if that fails, as Double.
 *
 * Cells not containing any letters and containing at least one number will
 * be created/changed into NUMERIC type (formatting is not changed).
 *
 * Existing Boolean cells will be parsed as Boolean.
 *
 * For everything else and if any of the above fail, the cell will get the
 * STRING type and the value will just be a string, except empty values will
 * cause the cell type to be BLANK.
 *
 * @param col
 *            Column index of target cell, 1-based
 * @param row
 *            Row index of target cell, 1-based
 * @param value
 *            The new value to set to the target cell, formulas will start
 *            with an extra "=" or "+"
 */
public void onCellValueChange(int col, int row, String value) {
    Workbook workbook = spreadsheet.getWorkbook();
    // update cell value
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row - 1);
    if (r == null) {
        r = activeSheet.createRow(row - 1);
    }
    Cell cell = r.getCell(col - 1);
    String formattedCellValue = null;
    int oldCellType = -1;
    // capture cell value to history
    CellValueCommand command = new CellValueCommand(spreadsheet);
    command.captureCellValues(new CellReference(row - 1, col - 1));
    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    boolean updateHyperlinks = false;

    if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet,
            col - 1, row - 1, value, getFormulaEvaluator(), formatter)) {
        Exception exception = null;
        try {
            // handle new cell creation
            SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory();
            final Locale spreadsheetLocale = spreadsheet.getLocale();
            if (cell == null) {
                cell = r.createCell(col - 1);
            } else {
                // modify existing cell, possibly switch type
                formattedCellValue = getFormattedCellValue(cell);
                final String key = SpreadsheetUtil.toKey(col, row);
                oldCellType = cell.getCellType();
                if (!sentCells.remove(key)) {
                    sentFormulaCells.remove(key);
                }

                // Old value was hyperlink => needs refresh
                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                        && cell.getCellFormula().startsWith("HYPERLINK")) {
                    updateHyperlinks = true;
                }
            }
            if (formulaFormatter.isFormulaFormat(value)) {
                if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) {
                    spreadsheet.removeInvalidFormulaMark(col, row);
                    getFormulaEvaluator().notifyUpdateCell(cell);
                    cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(
                            formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale));
                    getFormulaEvaluator().notifySetFormula(cell);
                    if (value.startsWith("=HYPERLINK(")
                            && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) {
                        // set the cell style to link cell
                        CellStyle hyperlinkCellStyle;
                        if (hyperlinkStyleIndex == -1) {
                            hyperlinkCellStyle = styler.createHyperlinkCellStyle();
                            hyperlinkStyleIndex = -1;
                        } else {
                            hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex);
                        }
                        cell.setCellStyle(hyperlinkCellStyle);
                        styler.cellStyleUpdated(cell, true);
                        updateHyperlinks = true;
                    }
                } else {
                    // it's formula but invalid
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                    spreadsheet.markInvalidFormula(col, row);
                }
            } else {
                spreadsheet.removeInvalidFormulaMark(col, row);
                Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale);
                Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale);
                if (value.isEmpty()) {
                    cell.setCellType(Cell.CELL_TYPE_BLANK);
                } else if (percentage != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    CellStyle cs = cell.getCellStyle();
                    if (cs == null) {
                        cs = workbook.createCellStyle();
                        cell.setCellStyle(cs);
                    }

                    if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) {
                        cs.setDataFormat(workbook.createDataFormat()
                                .getFormat(spreadsheet.getDefaultPercentageFormat()));
                        styler.cellStyleUpdated(cell, true);
                    }
                    cell.setCellValue(percentage);
                } else if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) {
                    cell.setCellValue(Boolean.parseBoolean(value));
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                }
                getFormulaEvaluator().notifyUpdateCell(cell);
            }

        } catch (FormulaParseException fpe) {
            try {
                exception = fpe;

                // parses formula
                cell.setCellFormula(value.substring(1).replace(" ", ""));
            } catch (FormulaParseException fpe2) {
                exception = fpe2;
                /*
                 * We could force storing the formula even if it is invalid.
                 * Instead, just store it as the value. Clearing the formula
                 * makes sure the value is displayed as-is.
                 */
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(value);
                spreadsheet.markInvalidFormula(col, row);
            }
        } catch (NumberFormatException nfe) {
            exception = nfe;
            cell.setCellValue(value);
        } catch (Exception e) {
            exception = e;
            cell.setCellValue(value);
        }
        if (cell != null) {
            markCellForUpdate(cell);
            if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell))
                    || oldCellType != cell.getCellType()) {
                fireCellValueChangeEvent(cell);
            }
        }
        if (exception != null) {
            LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " ("
                    + exception.getMessage() + ")", exception);
        }
    }

    spreadsheet.updateMarkedCells();

    if (updateHyperlinks) {
        spreadsheet.loadHyperLinks();
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

/**
 * Creates a CellStyle to be used with hyperlinks
 * //w  w w . jav a2 s.c o m
 * @return A new hyperlink CellStyle
 */
public CellStyle createHyperlinkCellStyle() {
    Workbook wb = spreadsheet.getWorkbook();
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setFontName(defaultFont.getFontName());
    hlink_font.setFontHeightInPoints(defaultFontHeightInPoints);
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    return hlink_style;
}

From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java

License:Apache License

public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException {
    File template = new File("Template.xls");
    template.createNewFile();//from   w  ww  .  j  a  v a 2s .  co m
    org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook();
    org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet();
    wb.setSheetName(0, "Requirements");
    int column = 0;
    CellStyle cs = wb.createCellStyle();
    cs.setDataFormat(getBuiltinFormat("text"));
    Font f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    f.setBold(true);
    f.setColor((short) Font.COLOR_NORMAL);
    cs.setFont(f);
    Row newRow = sheet.createRow(0);
    for (String label : COLUMNS) {
        Cell newCell = newRow.createCell(column);
        newCell.setCellStyle(cs);
        newCell.setCellValue(label);
        column++;
    }

    try (FileOutputStream out = new FileOutputStream(template)) {
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        LOG.log(Level.SEVERE, null, e);
    } catch (IOException e) {
        LOG.log(Level.SEVERE, null, e);
    }
    return template;
}

From source file:com.validation.manager.core.tool.step.importer.StepImporter.java

License:Apache License

public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException {
    File template = new File("Template.xls");
    template.createNewFile();/*from   www  . j a v  a 2 s.  co m*/
    org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook();
    org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet();
    wb.setSheetName(0, "Steps");
    int column = 0;
    CellStyle cs = wb.createCellStyle();
    cs.setDataFormat(getBuiltinFormat("text"));
    Font f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    f.setBold(true);
    f.setColor((short) Font.COLOR_NORMAL);
    cs.setFont(f);
    Row newRow = sheet.createRow(0);
    for (String label : COLUMNS) {
        Cell newCell = newRow.createCell(column);
        newCell.setCellStyle(cs);
        newCell.setCellValue(label);
        column++;
    }

    try (FileOutputStream out = new FileOutputStream(template)) {
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        LOG.log(Level.SEVERE, null, e);
    } catch (IOException e) {
        LOG.log(Level.SEVERE, null, e);
    }
    return template;
}

From source file:com.vincestyling.apkinfoextractor.core.export.ExportToExcel.java

License:Apache License

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    String fontName = getAvaliableTitleFont();
    CellStyle style;//from ww  w . j  av  a 2 s . c  o  m

    Font font = wb.createFont();
    font.setFontName(fontName);
    font.setFontHeightInPoints((short) 14);
    font.setColor(IndexedColors.BROWN.getIndex());

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName(fontName);
    font.setFontHeightInPoints((short) 14);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.WHITE.getIndex());

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFont(font);
    style.setWrapText(true);
    setBorder(style);
    styles.put("header", style);

    font = wb.createFont();
    font.setFontName(fontName);
    font.setFontHeightInPoints((short) 12);
    font.setColor(IndexedColors.BLACK.getIndex());

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);
    style.setFont(font);
    setBorder(style);
    styles.put("cell", style);

    return styles;
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public CellStyle getTitleCellStyleForStandardExcel(Workbook workbook) {
    CellStyle cs = workbook.createCellStyle();
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setWrapText(true);/*w  w w  .j  a  v  a  2s.  co m*/
    cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cs.setAlignment(CellStyle.ALIGN_CENTER);
    cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cs.setFont(font);
    //cs.setUserStyleName("wabacus_title_rowstyle");//
    return cs;
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public CellStyle getDataCellStyleForStandardExcel(Workbook workbook) {
    CellStyle cs = workbook.createCellStyle();
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setWrapText(true);/*from  ww  w.  j a v a  2 s  . c om*/
    cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cs.setFont(font);
    return cs;
}