Example usage for org.apache.poi.ss.usermodel Workbook 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.eclipse.sw360.exporter.ExcelExporter.java

License:Open Source License

/**
 * Create style for data cells//  w ww.ja va 2s  . com
 */
private static CellStyle createCellStyle(Workbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    return cellStyle;
}

---

From source file:org.exist.xquery.corenlp.Tokenize.java

License:Open Source License

private void createXSLXSpreadsheet(List<List<CoreLabel>> sentences, List<CoreLabel> tokens) {
    Workbook workbook = null;
    if (outputFormat == OutDocType.XSLX) {
        workbook = new SXSSFWorkbook();
    } else {//from w  w w.  ja  va2s  . c  o m
        workbook = new HSSFWorkbook();
    }
    CreationHelper creationHelper = workbook.getCreationHelper();
    org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet();

    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Header
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(boldFont);
    int lineIndex = 0;
    for (List<CoreLabel> sentence : sentences) {
        for (CoreLabel token : sentence) {
            String value = token.get(CoreAnnotations.OriginalTextAnnotation.class);
            Row row = sheet.createRow(lineIndex);
            row.createCell(0).setCellValue(creationHelper.createRichTextString(value));
            row.createCell(1).setCellValue(creationHelper.createRichTextString(backgroundSymbol));
            lineIndex++;
        }
        Row row = sheet.createRow(lineIndex);
        row.createCell(0).setCellValue(creationHelper.createRichTextString(""));
        row.createCell(1).setCellValue(creationHelper.createRichTextString(""));
        lineIndex++;
    }

    try (OutputStream os = Files.newOutputStream(tempOutFile)) {
        workbook.write(os);
    } catch (FileNotFoundException fe) {
        LOG.error(fe);
    } catch (IOException ioe) {
        LOG.error(ioe);
    } finally {
        if (workbook != null) {
            if (workbook instanceof SXSSFWorkbook) {
                ((SXSSFWorkbook) workbook).dispose();
            } else {
                workbook = null;
            }
        }
    }
}

---

From source file:org.fhaes.gui.AnalysisResultsPanel.java

License:Open Source License

/**
 * Save the current analyses results to a multi-tabbed Excel file
 * //from   www  . j a va2  s  . c  o  m
 * @param outputfile
 */
public void saveXLSXOfResults(File outputfile) {

    Workbook workbook = new XSSFWorkbook();
    CreationHelper createHelper = workbook.getCreationHelper();

    doubleStyle = workbook.createCellStyle();
    doubleStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.000"));

    writeParametersToXLSXSheet(workbook.createSheet("Parameters"));
    writeModelToXLSXSheet(workbook.createSheet("General Summary"), generalSummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("Interval Summary"), intervalsSummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("Interval Exceedence"), intervalsExceedenceModel);
    writeModelToXLSXSheet(workbook.createSheet("Seasonality Summary"), seasonalitySummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("Binary Site Summary"), siteSummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("Binary Tree Summary"), treeSummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("NTP Matrix"), NTPModel);
    writeModelToXLSXSheet(workbook.createSheet("COHEN Dissimilarity"), DSCOHModel);
    writeModelToXLSXSheet(workbook.createSheet("JACCARD Dissimilarity"), DSJACModel);
    writeModelToXLSXSheet(workbook.createSheet("COHEN Similarity"), SCOHModel);
    writeModelToXLSXSheet(workbook.createSheet("JACCARD Similarity"), SJACModel);
    writeModelToXLSXSheet(workbook.createSheet("Matrix A (1-1)"), bin11Model);
    writeModelToXLSXSheet(workbook.createSheet("Matrix B (0-1)"), bin01Model);
    writeModelToXLSXSheet(workbook.createSheet("Matrix C (1-0)"), bin10Model);
    writeModelToXLSXSheet(workbook.createSheet("Matrix D (0-0)"), bin00Model);
    writeModelToXLSXSheet(workbook.createSheet("Matrix L (Sum)"), binSumModel);
    writeModelToXLSXSheet(workbook.createSheet("Single File Summary"), singleFileSummaryModel);
    writeModelToXLSXSheet(workbook.createSheet("Single File Event Summary"), singleEventSummaryModel);

    OutputStream os = IOUtils.createOutput(outputfile);
    try {
        workbook.write(os);

    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

---

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver.java

License:Open Source License

private void initDateTimeStyles(Workbook wb) {
    this.DATE_TIME_STYLE = wb.createCellStyle();
    this.DATE_TIME_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_TIME_FORMAT));

    this.DATE_STYLE = wb.createCellStyle();
    this.DATE_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));

    this.TIME_STYLE = wb.createCellStyle();
    this.TIME_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(TIME_FORMAT));
}

---

From source file:org.forzaframework.util.ExcelUtils.java

License:Apache License

static public void modelToExcelSheet(Workbook wb, String sheetName, List<Map<String, Object>> headers,
        List<Map<String, Object>> data, List<Map<String, Object>> footers, Integer freezePane,
        Boolean defaultFormat, Boolean createNewSheet, Integer indexSheet, Integer startInRow,
        Boolean printHeader, Boolean autoSizeColumns) {
    Sheet sheet = getSheet(wb, sheetName, createNewSheet, indexSheet);
    CellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, defaultFormat);
    CellStyle titlesCellStyle = null;/*from w w  w  .  j a v a  2s.  c om*/
    if (defaultFormat != null && defaultFormat) {
        titlesCellStyle = wb.createCellStyle();
        //Creamos el tipo de fuente
        Font titleFont = wb.createFont();
        //            headerFont.setFontName(HSSFFont.FONT_ARIAL);
        titleFont.setBold(Boolean.TRUE);
        titleFont.setColor(Font.COLOR_NORMAL);
        titleFont.setFontHeightInPoints((short) 8);
        titlesCellStyle.setFont(titleFont);
    }

    Integer col = 0;
    Integer row = 0;
    if (startInRow != null) {
        row = startInRow;
    }
    Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>();
    //Indice de la fila donde empieza los encabezados de titulo de cada columna
    Integer principalHeaderIndex = headers.size() - 1;
    if (printHeader != null && printHeader) {
        //Armamos el encabezado
        for (Map<String, Object> header : headers) {
            for (Map.Entry<String, Object> entry : header.entrySet()) {
                Cell cell = getCell(sheet, row, col);
                if (defaultFormat != null && defaultFormat) {
                    if (principalHeaderIndex.equals(row)) {
                        //Colocamos el formato de la celda
                        cell.setCellStyle(headerCellStyle);
                    } else {
                        cell.setCellStyle(titlesCellStyle);
                    }
                }
                setValue(cell, entry.getValue());
                //Especificamos el ancho que tendra la columna
                if (autoSizeColumns != null && autoSizeColumns) {
                    columnWidthMap.put(col, entry.getValue().toString().length());
                }
                col++;
            }
            row++;
            col = 0;
        }
        //Ponemos la altura del encabezado
        setRowHeight(sheet, row - 1, (short) 420);
    }

    CellStyle detailCellStyle = getDefaultDetailCellStyle(wb, defaultFormat);

    Map<String, Object> principalHeader = headers.get(principalHeaderIndex);
    // datos
    for (Map<String, Object> map : data) {
        for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
            Object value = map.get(entry.getKey());
            buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap,
                    autoSizeColumns);
            col++;
        }
        col = 0;
        row++;
    }
    CellStyle totalCellStyle = null;
    if (defaultFormat != null && defaultFormat) {
        //Armamos el formato los totales
        totalCellStyle = wb.createCellStyle();
        Font totalFont = wb.createFont();
        totalFont.setBold(Boolean.TRUE);
        totalFont.setColor(Font.COLOR_NORMAL);
        totalFont.setFontHeightInPoints((short) 8);
        totalCellStyle.setFont(totalFont);
    }

    if (footers != null) {
        for (Map<String, Object> footer : footers) {
            for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
                Cell cell = getCell(sheet, row, col++);
                if (totalCellStyle != null) {
                    //Colocamos el formato de la celda
                    cell.setCellStyle(totalCellStyle);
                }

                Object object = footer.get(entry.getKey());
                if (object != null) {
                    setValue(cell, object);
                } else {
                    setText(cell, "");
                }
            }
        }
    }

    if (autoSizeColumns != null && autoSizeColumns) {
        setColumnsWidth(sheet, columnWidthMap, principalHeader.size());
    }

    if (freezePane != null && freezePane > 0) {
        //Colocamos la columna estatica y las filas del encabezado estaticas
        sheet.createFreezePane(freezePane, headers.size());
    }
}

---

From source file:org.forzaframework.util.ExcelUtils.java

License:Apache License

public static CellStyle getDefaultDetailCellStyle(Workbook wb, Boolean defaultFormat) {
    CellStyle detailCellStyle = null;//from w ww  . ja  va  2  s  .c  om
    if (defaultFormat != null && defaultFormat) {
        //Armamos el formato para los datos del detalle
        detailCellStyle = wb.createCellStyle();
        Font detailFont = wb.createFont();
        detailFont.setFontHeightInPoints((short) 8);
        detailCellStyle.setFont(detailFont);
    }
    return detailCellStyle;
}

---

From source file:org.forzaframework.util.ExcelUtils.java

License:Apache License

public static CellStyle getDefaultHeaderCellStyle(Workbook wb, Boolean defaultFormat) {
    CellStyle headerCellStyle = null;/*from  w  ww. j av a2  s . c o  m*/
    if (defaultFormat != null && defaultFormat) {
        //Le damos formato a los encabezados
        headerCellStyle = wb.createCellStyle();
        headerCellStyle.setBorderBottom(BorderStyle.DOTTED);
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //        headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //Creamos el tipo de fuente
        Font headerFont = wb.createFont();
        //            headerFont.setFontName(HSSFFont.FONT_ARIAL);
        headerFont.setBold(Boolean.TRUE);
        headerFont.setColor(Font.COLOR_NORMAL);
        headerFont.setFontHeightInPoints((short) 8);
        headerCellStyle.setFont(headerFont);
    }
    return headerCellStyle;
}

---

From source file:org.geoserver.wfs.response.ExcelCellStyles.java

License:Open Source License

public ExcelCellStyles(Workbook wb) {
    CreationHelper helper = wb.getCreationHelper();
    DataFormat fmt = helper.createDataFormat();

    dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(fmt.getFormat("yyyy-mm-dd hh:mm:ss"));

    headerStyle = wb.createCellStyle();/*  w w w  . j  a  v a  2s  .  c o  m*/
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    warningStyle = wb.createCellStyle();
    Font warningFont = wb.createFont();
    warningFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    warningFont.setColor(Font.COLOR_RED);
    warningStyle.setFont(warningFont);
}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("rawtypes")
private static void writeHead(boolean useTemplate, Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    Integer headRowIndex = sheetProcessor.getHeadRowIndex();
    if (headRowIndex == null) {
        return;/*from  w  w  w  .j  av  a  2 s .  co m*/
    }
    Workbook wookbook = sheet.getWorkbook();
    // use theme
    CellStyle style = null;
    if (!useTemplate && sheetProcessor.getTheme() != null) {
        int theme = sheetProcessor.getTheme();
        if (theme == ExcelWriteTheme.BASE) {
            style = wookbook.createCellStyle();
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor((short) 44);
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setBorderTop(CellStyle.BORDER_THIN);
            // style.setBottomBorderColor((short) 44);
            style.setAlignment(CellStyle.ALIGN_CENTER);
        }
        // freeze Pane
        if (sheetProcessor.getHeadRowIndex() != null && sheetProcessor.getHeadRowIndex() == 0) {
            sheet.createFreezePane(0, 1, 0, 1);
        }
    }

    Row row = sheet.getRow(headRowIndex);
    if (row == null) {
        row = sheet.createRow(headRowIndex);
    }
    for (Map.Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> entry : sheetProcessor
            .getFieldMapping().export().entrySet()) {
        Map<Integer, ExcelWriteFieldMappingAttribute> map = entry.getValue();
        if (map != null) {
            for (Map.Entry<Integer, ExcelWriteFieldMappingAttribute> entry2 : map.entrySet()) {
                String head = entry2.getValue().getHead();
                Integer colIndex = entry2.getKey();
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                // use theme
                if (!useTemplate && sheetProcessor.getTheme() != null) {
                    cell.setCellStyle(style);

                }
                cell.setCellValue(head);
            }
        }
    }

}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("unused")
private static void writeCell(Cell cell, Object val, boolean userTemplate,
        ExcelWriteFieldMappingAttribute attribute, Object bean) {
    if (attribute != null && attribute.getLinkField() != null) {
        String addressFieldName = attribute.getLinkField();
        String address = null;/*from   w  ww. j  av a 2s .c o  m*/
        if (bean != null) {
            address = (String) getFieldValue(bean, addressFieldName, true);
        }
        Workbook wb = cell.getRow().getSheet().getWorkbook();

        Hyperlink link = wb.getCreationHelper().createHyperlink(attribute.getLinkType());
        link.setAddress(address);
        cell.setHyperlink(link);
        // Its style can't inherit from cell.
        CellStyle style = wb.createCellStyle();
        Font hlinkFont = wb.createFont();
        hlinkFont.setUnderline(Font.U_SINGLE);
        hlinkFont.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(hlinkFont);
        if (cell.getCellStyle() != null) {
            style.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor());
        }
        cell.setCellStyle(style);
    }
    if (val == null) {
        cell.setCellValue((String) null);
        return;
    }
    Class<?> clazz = val.getClass();
    if (val instanceof Byte) {// Double
        Byte temp = (Byte) val;
        cell.setCellValue((double) temp.byteValue());
    } else if (val instanceof Short) {
        Short temp = (Short) val;
        cell.setCellValue((double) temp.shortValue());
    } else if (val instanceof Integer) {
        Integer temp = (Integer) val;
        cell.setCellValue((double) temp.intValue());
    } else if (val instanceof Long) {
        Long temp = (Long) val;
        cell.setCellValue((double) temp.longValue());
    } else if (val instanceof Float) {
        Float temp = (Float) val;
        cell.setCellValue((double) temp.floatValue());
    } else if (val instanceof Double) {
        Double temp = (Double) val;
        cell.setCellValue((double) temp.doubleValue());
    } else if (val instanceof Date) {// Date
        Date dateVal = (Date) val;
        long time = dateVal.getTime();
        // read is based on 1899/12/31 but DateUtil.getExcelDate is base on
        // 1900/01/01
        if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_01_00_00_00_000) {
            Date incOneDay = new Date(time + 24 * 60 * 60 * 1000);
            double d = DateUtil.getExcelDate(incOneDay);
            cell.setCellValue(d - 1);
        } else {
            cell.setCellValue(dateVal);
        }

        if (!userTemplate) {
            Workbook wb = cell.getRow().getSheet().getWorkbook();
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = wb.createCellStyle();
            }
            DataFormat dataFormat = wb.getCreationHelper().createDataFormat();
            // @see #BuiltinFormats
            // 0xe, "m/d/yy"
            // 0x14 "h:mm"
            // 0x16 "m/d/yy h:mm"
            // {@linke https://en.wikipedia.org/wiki/Year_10,000_problem}
            /** [1899/12/31 00:00:00:000~1900/01/01 00:00:000) */
            if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_02_00_00_00_000) {
                cellStyle.setDataFormat(dataFormat.getFormat("h:mm"));
                // cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
            } else {
                // if ( time % (24 * 60 * 60 * 1000) == 0) {//for time
                // zone,we can't use this way.
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dateVal);
                int hour = calendar.get(Calendar.HOUR_OF_DAY);
                int minute = calendar.get(Calendar.MINUTE);
                int second = calendar.get(Calendar.SECOND);
                int millisecond = calendar.get(Calendar.MILLISECOND);
                if (millisecond == 0 && second == 0 && minute == 0 && hour == 0) {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy"));
                } else {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
                }
            }
            cell.setCellStyle(cellStyle);
        }
    } else if (val instanceof Boolean) {// Boolean
        cell.setCellValue(((Boolean) val).booleanValue());
    } else {// String
        cell.setCellValue((String) val.toString());
    }
}

---

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