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:org.olat.core.gui.components.table.DefaultXlsTableExporter.java

License:Apache License

private CellStyle getHeaderCellStyle(final Workbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(boldFont);//  ww  w  .j a  va 2s  .  c o  m
    return cellStyle;
}

From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/** 
 * @see ReportRenderer#render(ReportData, String, OutputStream)
 */// ww w.j  ava 2  s.c  o m
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    try {
        log.debug("Attempting to render report with ExcelTemplateRenderer");
        ReportDesign design = getDesign(argument);
        Workbook wb = getExcelTemplate(design);

        if (wb == null) {
            XlsReportRenderer xlsRenderer = new XlsReportRenderer();
            xlsRenderer.render(reportData, argument, out);
        } else {
            //This should be changed to get the dataset name form a parameter 
            DataSet ds = reportData.getDataSets().get("patient");
            ArrayList<String> names = new ArrayList<String>();

            for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) {
                names.add(dataSetRow.getName());
            }
            Sheet s = wb.getSheetAt(0);

            //Trying to creat a row that has the replacement values pre-populated
            Row h = s.createRow(8);
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setBold(true);
            style.setFont(font);

            for (String name : names) {
                Cell c = h.createCell(names.indexOf(name));
                String value = name.toUpperCase().replace("_", " ");
                c.setCellValue(value);
                c.setCellStyle(style);
            }

            Row r = s.getRow(9);

            for (String name : names) {
                Cell c = r.createCell(names.indexOf(name));
                String value = "#patient." + name + "#";
                c.setCellValue(value);
            }

            Map<String, String> repeatSections = getRepeatingSections(design);

            // Put together base set of replacements.  Any dataSet with only one row is included.
            Map<String, Object> replacements = getBaseReplacementData(reportData, design);

            // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned
            List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

            Set<String> usedSheetNames = new HashSet<String>();
            int numberOfSheets = wb.getNumberOfSheets();

            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

                Sheet currentSheet = wb.getSheetAt(sheetNum);
                String originalSheetName = wb.getSheetName(sheetNum);

                String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
                if (dataSetName != null) {

                    DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
                    int dataSetRowNum = 0;
                    for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator
                            .hasNext();) {
                        DataSetRow dataSetRow = rowIterator.next();
                        dataSetRowNum++;
                        Map<String, Object> newReplacements = getReplacementData(replacements, reportData,
                                design, dataSetName, dataSetRow, dataSetRowNum);
                        Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
                        sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
                    }
                } else {
                    sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
                }
            }

            // Then iterate across all of these and add them in
            for (int i = 0; i < sheetsToAdd.size(); i++) {
                addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
            }

            wb.write(out);
        }
    } catch (Exception e) {
        throw new RenderingException("Unable to render results due to: " + e, e);
    }
}

From source file:org.openmrs.module.rwandareports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/** 
 * @see ReportRenderer#render(ReportData, String, OutputStream)
 *///  ww w .ja  v a 2s . c o  m
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    try {
        log.debug("Attempting to render report with ExcelTemplateRenderer");
        ReportDesign design = getDesign(argument);
        Workbook wb = getExcelTemplate(design);

        if (wb == null) {
            XlsReportRenderer xlsRenderer = new XlsReportRenderer();
            xlsRenderer.render(reportData, argument, out);
        } else {
            //This should be changed to get the dataset name form a parameter 
            DataSet ds = reportData.getDataSets().get("patient");
            ArrayList<String> names = new ArrayList<String>();

            for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) {
                names.add(dataSetRow.getName());
            }
            Sheet s = wb.getSheetAt(0);

            Row h = s.createRow(8);
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setBold(true);
            style.setFont(font);

            for (String name : names) {
                Cell c = h.createCell(names.indexOf(name));
                String value = name.toUpperCase().replace("_", " ");
                c.setCellValue(value);
                c.setCellStyle(style);
            }

            //Trying to creat a row that has the replacement values pre-populated
            Row r = s.getRow(9);

            for (String name : names) {
                Cell c = r.createCell(names.indexOf(name));
                String value = "#patient." + name + "#";
                c.setCellValue(value);

            }

            ExcelUtil.formatRow(r);
            Map<String, String> repeatSections = getRepeatingSections(design);

            // Put together base set of replacements.  Any dataSet with only one row is included.
            Map<String, Object> replacements = getBaseReplacementData(reportData, design);

            // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned
            List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

            Set<String> usedSheetNames = new HashSet<String>();
            int numberOfSheets = wb.getNumberOfSheets();

            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

                Sheet currentSheet = wb.getSheetAt(sheetNum);
                String originalSheetName = wb.getSheetName(sheetNum);

                String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
                if (dataSetName != null) {

                    DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
                    int dataSetRowNum = 0;
                    for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator
                            .hasNext();) {
                        DataSetRow dataSetRow = rowIterator.next();
                        dataSetRowNum++;
                        Map<String, Object> newReplacements = getReplacementData(replacements, reportData,
                                design, dataSetName, dataSetRow, dataSetRowNum);
                        Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
                        sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
                    }
                } else {
                    sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
                }
            }

            // Then iterate across all of these and add them in
            for (int i = 0; i < sheetsToAdd.size(); i++) {
                addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
            }

            wb.write(out);
        }
    } catch (Exception e) {
        throw new RenderingException("Unable to render results due to: " + e, e);
    }
}

From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java

License:Apache License

private CellStyle createHyperlinkStyle(Workbook workbook) {
    // cell style for hyperlinks
    // by default hyperlinks are blue and underlined
    CellStyle hyperlinkStyle = workbook.createCellStyle();
    hyperlinkStyle.cloneStyleFrom(statementsSheet.getRow(INDEX_ROW_TEMPLATE_DELTA_SQL_STATEMENT)
            .getCell(INDEX_COLUMN_SQL_TEXT).getCellStyle());

    Font hyperlinkFont = workbook.createFont();
    hyperlinkFont.setUnderline(Font.U_SINGLE);
    hyperlinkFont.setColor(IndexedColors.BLUE.getIndex());

    hyperlinkStyle.setFont(hyperlinkFont);

    return hyperlinkStyle;
}

From source file:org.opentestsystem.delivery.testreg.rest.ExcelBasedTemplateCreator.java

License:Open Source License

private CellStyle getTextCellSytle(Workbook workbook) {
    DataFormat dataFormat = workbook.createDataFormat();
    CellStyle textStyle = workbook.createCellStyle();
    textStyle.setDataFormat(dataFormat.getFormat("@")); //@ is equivalent to Text
    textStyle.setHidden(false);/*w ww . java 2s . com*/
    return textStyle;
}

From source file:org.patientview.radar.service.impl.ExcelDocumentDataBuilder.java

License:Open Source License

public byte[] build(DocumentData documentData) {

    Workbook workbook = new HSSFWorkbook();
    CreationHelper createHelper = workbook.getCreationHelper();
    Sheet sheet = workbook.createSheet("data");

    // add the headers/columns
    Row headerRow = sheet.createRow((short) 0);
    sheet.autoSizeColumn(0);/*from w w w .  java2s .  c  o m*/

    CellStyle headerStyle = workbook.createCellStyle();

    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

    headerStyle.setLeftBorderColor(CellStyle.BORDER_THIN);
    headerStyle.setRightBorderColor(CellStyle.BORDER_THIN);
    headerStyle.setTopBorderColor(CellStyle.BORDER_THIN);
    headerStyle.setBottomBorderColor(CellStyle.BORDER_THIN);

    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    List<String> headers = documentData.getHeaders();
    int headerColumnIndex = 0;
    for (String header : headers) {
        sheet.autoSizeColumn(headerColumnIndex);
        Cell cell = headerRow.createCell(headerColumnIndex);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(header);
        headerColumnIndex++;
    }

    // add the row data
    int columnIndex = 0;
    int rowIndex = 1;
    for (List<String> row : documentData.getRows()) {
        Row spreadSheetRow = sheet.createRow((short) rowIndex++);
        for (String data : row) {
            spreadSheetRow.createCell(columnIndex++).setCellValue(data);
        }
        columnIndex = 0;
    }

    // set the column width to fit the contents - this must be done after the data is added
    headerColumnIndex = 0;
    for (String header : headers) {
        sheet.autoSizeColumn(headerColumnIndex);
        headerColumnIndex++;
    }
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
        workbook.write(outputStream);
        outputStream.close();
        outputStream.flush();
    } catch (IOException e) {
        LOGGER.error("Unable to write workbook to output stream " + e.getMessage(), e);
    }

    return outputStream.toByteArray();
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelCellStyleBuilder.java

License:Open Source License

public ExcelCellStyleBuilder(Workbook workbook) {
    this.workbook = workbook;
    this.hssfCellStyle = workbook.createCellStyle();
    this.isXLSX = hssfCellStyle instanceof XSSFCellStyle;
}

From source file:org.phenotips.export.internal.Styler.java

License:Open Source License

public void style(DataCell dataCell, Cell cell, Workbook wBook) {
    Set<StyleOption> styles = dataCell.getStyles();
    CellStyle cellStyle = wBook.createCellStyle();
    /* For \n to work properly set to true */
    cellStyle.setWrapText(true);//from   w  ww . ja v a2s .c o  m
    if (this.defaultFont == null) {
        this.defaultFont = createDefaultFont(wBook);
    }
    cellStyle.setFont(this.defaultFont);
    if (styles == null) {
        if (this.styleCache.containsKey(Collections.<StyleOption>emptySet())) {
            cell.setCellStyle(this.styleCache.get(Collections.<StyleOption>emptySet()));
            return;
        }
        cell.setCellStyle(cellStyle);
        this.styleCache.put(Collections.<StyleOption>emptySet(), cellStyle);
        return;
    }

    if (this.styleCache.containsKey(styles)) {
        cell.setCellStyle(this.styleCache.get(styles));
        return;
    }

    /* Priority can be coded in by placing the if statement lower, for higher priority */
    /** Font styles */
    Font headerFont = null;
    if (styles.contains(StyleOption.HEADER)) {
        headerFont = wBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(headerFont);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.LARGE_HEADER)) {
        if (headerFont == null) {
            headerFont = wBook.createFont();
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }
        headerFont.setFontHeightInPoints((short) 12);
        cellStyle.setFont(headerFont);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.YES)) {
        Font font = createDefaultFont(wBook);
        font.setColor(HSSFColor.GREEN.index);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.NO)) {
        Font font = createDefaultFont(wBook);
        font.setColor(HSSFColor.DARK_RED.index);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    /** Border styles */
    if (styles.contains(StyleOption.HEADER_BOTTOM)) {
        cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.SECTION_BORDER_LEFT)) {
        cellStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.SECTION_BORDER_RIGHT)) {
        cellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.PATIENT_BORDER)) {
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.FEATURE_SEPARATOR)) {
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.YES_NO_SEPARATOR)) {
        cellStyle.setBorderTop(CellStyle.BORDER_DASHED);
        cellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        cell.setCellStyle(cellStyle);
    }

    /* Keep this as the last statement. */
    this.styleCache.put(styles, cellStyle);
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void applyOptions(Workbook wb, DataTable table, Sheet sheet, ExporterOptions options) {
    facetStyle = wb.createCellStyle();
    facetStyle.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyle.setVerticalAlignment((short) CellStyle.VERTICAL_CENTER);
    facetStyle.setWrapText(true);/*from www  .  j av  a  2s. co  m*/
    applyFacetOptions(wb, options, facetStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setAlignment((short) CellStyle.ALIGN_LEFT);
    applyCellOptions(wb, options, cellStyle);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);
}

From source file:org.rhq.helpers.perftest.support.reporting.ExcelExporter.java

License:Open Source License

@Override
public void export(Map<String, Long> timings, ITestResult result) {

    Workbook wb;
    InputStream inp = null;//from w  w  w.jav  a2s .co  m

    String fileName = getFileName();

    // Check if Workbook is present - otherwise create it
    try {
        inp = new FileInputStream(fileName);
        wb = new HSSFWorkbook(inp);
    } catch (Exception e) {
        wb = new HSSFWorkbook();
    } finally {
        if (inp != null)
            try {
                inp.close();
            } catch (IOException e) {
                e.printStackTrace(); // TODO: Customise this generated block
            }
    }
    // Now write to it
    FileOutputStream fileOut = null;
    try {
        // Check if we have our sheet, otherwise create
        if (wb.getNumberOfSheets() == 0) {
            wb.createSheet("Overview");

        }
        Sheet sheet = wb.getSheetAt(0);

        DataFormat df = wb.createDataFormat();
        integerStyle = wb.createCellStyle();
        integerStyle.setDataFormat(df.getFormat("#######0"));
        Font boldFont = wb.createFont();
        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        boldText = wb.createCellStyle();
        boldText.setFont(boldFont);

        createOverviewHeaderIfNeeded(sheet);
        long time = getTotalTime(timings);
        createOverviewEntry(sheet, time, result);
        createDetailsSheet(wb, timings, result);

        // Write the output to a file
        File outFile = new File(fileName);
        System.out.println("ExcelExporter, writing to " + outFile.getAbsolutePath());
        fileOut = new FileOutputStream(outFile);
        wb.write(fileOut);
        fileOut.flush();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            System.err.println("Failed to close the workbook: " + e.getMessage());
        }
    }
}