Example usage for org.apache.poi.ss.usermodel CellStyle setFont

List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setFont

Introduction

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

Prototype

void setFont(Font font);

Source Link

Document

set the font for this style

Usage

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static CellStyle getDateStyle(String style, Sheet sheet, Font font) {
    if (StringUtils.isNotBlank(style) && sheet != null) {
        StyleKey cache = new StyleKey(style, sheet.getSheetName());
        if (styleCache.get(cache) == null) {
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            font.setFontHeight((short) 10);
            cellStyle.setFont(font);
            styleCache.put(cache, cellStyle);
        }//from www .  jav a2 s . c  o m
        return styleCache.get(cache);
    }
    return null;
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadErrorResponse(InputStream is, List<String> errors)
        throws IOException {
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFFont font = wb.createFont();/*from  ww  w  .java  2s. c  om*/
    font.setColor(Font.COLOR_RED);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    HSSFSheet sheet = wb.getSheetAt(0);

    Row row = sheet.getRow(0);
    int lastCell = row.getLastCellNum();
    Cell cell = createExcelCell(sheet, row, lastCell, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("ERRORS");

    for (String anError : errors) {
        String lineNoStr = StringUtils.substringBefore(anError, ":");
        lineNoStr = StringUtils.substringAfter(lineNoStr, "Line ");
        Integer lineNo = new Integer(lineNoStr) - 1;

        row = sheet.getRow(lineNo);
        cell = createExcelCell(sheet, row, lastCell, 256 * 100);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(anError);
    }

    return createOutputStream(wb);
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadSuccessResponse() {
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFFont font = wb.createFont();//  w  ww . ja  va2s  .  com
    font.setColor(IndexedColors.GREEN.getIndex());
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = createExcelCell(sheet, row, 0, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("ALL tolls uploaded successfully");

    return createOutputStream(wb);
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadExceptionResponse(Exception e) {
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFFont font = wb.createFont();//from w  w w  .ja v  a 2s.  com
    font.setColor(Font.COLOR_RED);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = createExcelCell(sheet, row, 0, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("An error occurred while uploading!!!");

    return createOutputStream(wb);
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./* w  ww  .j av a2  s.  c o  m*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.rapidminer.operator.io.ExcelExampleSetWriter.java

License:Open Source License

/**
 * Writes the provided {@link ExampleSet} to a XLSX formatted data sheet.
 *
 * @param wb/*from   w  ww . jav  a2s  .  c o  m*/
 *            the workbook to use
 * @param sheet
 *            the excel sheet to write to.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param exampleSet
 *            the exampleSet to write
 * @param op
 *            needed for checkForStop
 * @throws ProcessStoppedException
 *             if the process was stopped by the user.
 * @throws WriteException
 */
private static void writeXLSXDataSheet(SXSSFWorkbook wb, Sheet sheet, String dateFormat, String numberFormat,
        ExampleSet exampleSet, Operator op) throws WriteException, ProcessStoppedException {

    Font headerFont = wb.createFont();
    headerFont.setBold(true);

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFont(headerFont);

    // create the header
    Iterator<Attribute> a = exampleSet.getAttributes().allAttributes();
    int columnCounter = 0;
    int rowCounter = 0;
    Row headerRow = sheet.createRow(rowCounter);
    while (a.hasNext()) {
        Attribute attribute = a.next();
        Cell headerCell = headerRow.createCell(columnCounter);
        headerCell.setCellValue(attribute.getName());
        headerCell.setCellStyle(headerStyle);
        columnCounter++;
    }
    rowCounter++;

    // body font
    Font bodyFont = wb.createFont();
    bodyFont.setBold(false);

    CreationHelper createHelper = wb.getCreationHelper();

    // number format
    CellStyle numericalStyle = wb.createCellStyle();
    numericalStyle.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat));
    numericalStyle.setFont(bodyFont);

    // date format
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));
    dateStyle.setFont(bodyFont);

    // create nominal cell style
    CellStyle nominalStyle = wb.createCellStyle();
    nominalStyle.setFont(bodyFont);

    // fill body
    for (Example example : exampleSet) {

        // create new row
        Row bodyRow = sheet.createRow(rowCounter);

        // iterate over attributes and save examples
        a = exampleSet.getAttributes().allAttributes();
        columnCounter = 0;
        while (a.hasNext()) {
            Attribute attribute = a.next();
            Cell currentCell = bodyRow.createCell(columnCounter);
            if (!Double.isNaN(example.getValue(attribute))) {
                if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) {
                    Date dateValue = example.getDateValue(attribute);
                    currentCell.setCellValue(dateValue);
                    currentCell.setCellStyle(dateStyle);
                } else if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.NUMERICAL)) {
                    double numericalValue = example.getNumericalValue(attribute);
                    currentCell.setCellValue(numericalValue);
                    currentCell.setCellStyle(numericalStyle);
                } else {
                    currentCell.setCellValue(
                            stripIfNecessary(replaceForbiddenChars(example.getValueAsString(attribute))));
                    currentCell.setCellStyle(nominalStyle);
                }
            }
            columnCounter++;
        }
        rowCounter++;

        // checkForStop every 100 examples
        if (op != null && rowCounter % 100 == 0) {
            op.checkForStop();
        }
    }
}

From source file:com.rarediscovery.services.logic.WorkPad.java

protected CellStyle applySelectedStyle() {
    Font font = workbook.createFont();
    if (bold) {//from  ww  w  .j av  a2 s .co m
        font.setBold(true);
    } else {
        font.setBold(false);
    }

    CellStyle style = workbook.createCellStyle();
    style.setFont(font);

    if (alignRight) {
        style.setAlignment(CellStyle.ALIGN_RIGHT);
    } else {
        style.setAlignment(CellStyle.ALIGN_LEFT);
    }

    if (backgroundColor) {
        style.setFillBackgroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.BIG_SPOTS);
    } else {
        style.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
    }

    return style;
}

From source file:com.saba.CalendarDemo.java

License:Apache License

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Trebuchet MS");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);/*w  w w  . java2  s .c o m*/
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("title", style);

    Font itemFontLeft = wb.createFont();
    itemFontLeft.setFontHeightInPoints((short) 11);
    itemFontLeft.setFontName("Trebuchet MS");
    itemFontLeft.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(itemFontLeft);
    styles.put("item_left", style);

    Font itemFontRight = wb.createFont();
    itemFontRight.setFontHeightInPoints((short) 10);
    itemFontRight.setFontName("Trebuchet MS");
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(itemFontRight);
    styles.put("item_right", style);

    return styles;
}

From source file:com.saba.CalendarDemo.java

License:Apache License

/**
* createHeaderStyle : /*w  ww. j  a v a  2 s .  c om*/
* Header row setting for sheet
* @param wb
* @return
*/
private static CellStyle createHeaderStyleForAward(Workbook wb) {
    CellStyle headerStyle;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle = createBorderedStyle(wb);
    headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setFont(headerFont);
    return headerStyle;
}

From source file:com.sec.ose.osi.report.standard.BillOfMaterialsSheetTemplate.java

License:Open Source License

public void writeBillOfMaterialsRow(ArrayList<BillOfMaterialsRow> billOfMaterialsList) {

    for (BillOfMaterialsRow billOfMaterialsRow : billOfMaterialsList) {
        Row row = sheet.createRow(curRow++);

        Cell cell = row.createCell(ISheetTemplate.COL_A);
        cell.setCellValue(billOfMaterialsRow.getCategory());
        cell.setCellStyle(normalStyle);/*w  ww .j ava  2  s  .  c  om*/

        cell = row.createCell(ISheetTemplate.COL_B);
        cell.setCellValue(billOfMaterialsRow.getMatchedFiles());
        cell.setCellStyle(leftStyle);

        cell = row.createCell(ISheetTemplate.COL_C);
        cell.setCellValue(billOfMaterialsRow.getMatchedFileCounts());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_D);
        cell.setCellValue(billOfMaterialsRow.getComponent());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_E);
        String license = billOfMaterialsRow.getLicense();
        cell.setCellValue(license);
        CellStyle licenseCellStyle = getCellStyle(WHITE, getFont(FONT_BLACK, (short) 10, false));
        for (String criticalLicense : criticalLicenseList) {
            if (license.contains(criticalLicense)) {
                licenseCellStyle.setFont(getFont(RED, (short) 10, false));
            }
        }
        for (String majorLicense : majorLicenseList) {
            if (license.contains(majorLicense)) {
                licenseCellStyle.setFont(getFont(ORANGE, (short) 10, false));
            }
        }
        cell.setCellStyle(licenseCellStyle);

        cell = row.createCell(ISheetTemplate.COL_F);
        cell.setCellValue(billOfMaterialsRow.getComment());
        cell.setCellStyle(leftStyle);

    }

}