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

Introduction

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

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetExporterImpl.java

License:Open Source License

private void addDefaultCellStyles(Workbook workbook, Map<String, CellStyle> keyStyleMap) {
    final CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setLocked(true);/* w  w  w.  j  av  a  2s  . c  o  m*/
    keyStyleMap.put(EMFFormsCellStyleConstants.LOCKED, cellStyle);
    final CellStyle cellStyle2 = workbook.createCellStyle();
    cellStyle2.setLocked(true);
    cellStyle2.setWrapText(true);
    keyStyleMap.put(EMFFormsCellStyleConstants.LOCKED_AND_WRAPPED, cellStyle2);
    final CellStyle cellStyle3 = workbook.createCellStyle();
    cellStyle3.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$
    keyStyleMap.put(EMFFormsCellStyleConstants.TEXT, cellStyle3);
    final CellStyle cellStyle4 = workbook.createCellStyle();
    cellStyle4.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy")); //$NON-NLS-1$
    keyStyleMap.put(EMFFormsCellStyleConstants.DATE, cellStyle4);
}

---

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetExporterImpl.java

License:Open Source License

private void prepareNumberCellStyles(Workbook workbook, Set<String> retrievedFormats,
        Map<String, CellStyle> keyStyleMap) {
    final DataFormat dataFormat = workbook.createDataFormat();
    for (final String format : retrievedFormats) {
        final CellStyle cellStyleNumberFormat = workbook.createCellStyle();
        cellStyleNumberFormat.setDataFormat(dataFormat.getFormat(format));
        keyStyleMap.put(format, cellStyleNumberFormat);
    }//from  www . j a v  a  2  s  . com
}

---

From source file:org.eclipse.nebula.widgets.nattable.extension.poi.PoiExcelExporter.java

License:Open Source License

private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData, String dataFormat, int hAlign,
        int vAlign, boolean vertical) {

    CellStyle xlCellStyle = xlCellStyles
            .get(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical));

    if (xlCellStyle == null) {
        xlCellStyle = xlWorkbook.createCellStyle();

        if (applyBackgroundColor) {
            // Note: xl fill foreground = background
            setFillForegroundColor(xlCellStyle, bg);
            xlCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }//  w w w.  jav a 2s.  c  om

        Font xlFont = xlWorkbook.createFont();
        setFontColor(xlFont, fg);
        xlFont.setFontName(fontData.getName());
        xlFont.setFontHeightInPoints((short) fontData.getHeight());
        xlCellStyle.setFont(xlFont);

        if (vertical)
            xlCellStyle.setRotation((short) 90);

        switch (hAlign) {
        case SWT.CENTER:
            xlCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            break;
        case SWT.LEFT:
            xlCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
            break;
        case SWT.RIGHT:
            xlCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
            break;
        }
        switch (vAlign) {
        case SWT.TOP:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
            break;
        case SWT.CENTER:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            break;
        case SWT.BOTTOM:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
            break;
        }

        if (dataFormat != null) {
            CreationHelper createHelper = xlWorkbook.getCreationHelper();
            xlCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dataFormat));
        }

        xlCellStyles.put(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical),
                xlCellStyle);
    }
    return xlCellStyle;
}

---

From source file:org.eclipse.titanium.markers.export.ExportedProblemMerger.java

License:Open Source License

/**
 * Write the dates and smell data./* w  ww. j  ava 2s. c  om*/
 * @throws FileNotFoundException 
 * 
 * @throws RowsExceededException
 * @throws WriteException
 * @throws BiffException
 * @throws IOException
 */
private void writeData() throws FileNotFoundException, IOException {

    int col = 1;
    for (final Date date : dates) {
        if (col > 250) {
            System.out.println("could not process date " + date + "\t column limit exceeded.");
        } else {
            final File file = datefile.get(date);
            System.out.println("Processing file: " + file.getName() + " | date: "
                    + new SimpleDateFormat("yyyy.MM.dd").format(date));
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            final HSSFSheet sheet = workbook.getSheetAt(0);

            // add date
            final CellStyle cellStyle = outbook.createCellStyle();
            cellStyle.setDataFormat(outbook.getCreationHelper().createDataFormat().getFormat("yyyy.mm.dd"));
            final Cell cell = summarysheet.getRow(1).createCell(col);
            cell.setCellValue(date);
            cell.setCellStyle(cellStyle);

            writeSmellData(sheet, date, col);

            ++col;

            workbook = null;
        }
    }
}

---

From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java

License:Open Source License

/**
 * Export the code smells of a project to an excel workbook.
 * <p>/*  w  w  w.j  a v  a  2s.com*/
 * The first sheet of the workbook is a summary page, showing the number of
 * hits for each code smell, and an expressive bar chart of these data. The
 * further sheets enumerate the specific code smells of each kind, including
 * the message of the code smell, and the file name and line where it
 * occurred.
 * <p>
 * Note: All code smell types are used in the analysis and are written in
 * the output. Some code smells use external settings, which can be fine
 * tuned on the preference page.
 * 
 * @param filename
 *            the file to save the xls
 * @param date
 *            the time stamp to write on the summary page
 * 
 * @throws IOException
 *             when writing the file fails
 */
@Override
// Flow analysis thinks 'sheet' may be referenced as null, but it is
// guaranteed to be initialized first.
public void exportMarkers(final IProgressMonitor monitor, final String filename, final Date date)
        throws IOException {
    final SubMonitor progress = SubMonitor.convert(monitor, 100);
    final File file = new File(filename);
    POIFSFileSystem fs = null;
    HSSFWorkbook workbook = null;

    try {
        fs = new POIFSFileSystem(XlsProblemExporter.class.getResourceAsStream("ProblemMarkers.xlt"));
        workbook = new HSSFWorkbook(fs, true);
    } catch (IOException e) {
        ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e);
        // Error on opening the template xls. Create an empty
        // one (without the chart).
        if (reportDebugInformation) {
            TITANDebugConsole.println("Error on opening ProblemMarkers.xlt. Chartless xls will be generated");
        }
        workbook = new HSSFWorkbook(new FileInputStream(file));
        workbook.createSheet("Summary");
        workbook.setSheetOrder("Summary", 0);
    } catch (Exception e) {
        ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e);
        return;
    }
    progress.worked(10);

    try {
        final HSSFSheet summarySheet = workbook.getSheetAt(0);
        createTimeSheet(workbook);

        final Map<String, Integer> smellCount = new HashMap<String, Integer>();
        int summaryRow = 4;

        Cell label = null;
        Cell numberCell = null;

        final Map<TaskType, List<IMarker>> markers = collectMarkers();
        // export the task markers:
        for (final TaskType t : TaskType.values()) {
            createTaskSheet(workbook, t, markers.get(t));

            final Row row1 = summarySheet.createRow(summaryRow++);
            label = row1.createCell(0);
            label.setCellValue(t.getHumanReadableName());

            final int nofMarkers = markers.get(t).size();
            numberCell = row1.createCell(1);
            numberCell.setCellValue(nofMarkers);

            // row-1 is the number of found markers
            smellCount.put(t.name(), nofMarkers);
        }

        progress.worked(20);

        final MarkerHandler mh = AnalyzerCache.withAll().analyzeProject(progress.newChild(30), project);
        progress.setWorkRemaining(CodeSmellType.values().length + 1);
        // export the semantic problem markers:
        for (final CodeSmellType t : CodeSmellType.values()) {
            createCodeSmellSheet(workbook, mh, t);

            final Row row1 = summarySheet.createRow(summaryRow++);
            label = row1.createCell(0);
            label.setCellValue(t.getHumanReadableName());

            smellCount.put(t.name(), mh.numberOfOccurrences(t));

            numberCell = row1.createCell(1);
            numberCell.setCellValue(mh.numberOfOccurrences(t));

            progress.worked(1);
        }

        final Row row0 = summarySheet.createRow(0);
        row0.createCell(0).setCellValue("Project: " + project.getName());

        final Row row1 = summarySheet.createRow(1);
        row1.createCell(0).setCellValue("Code smell \\ date");

        final CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy.mm.dd"));
        label = row1.createCell(1);
        label.setCellValue(date);
        label.setCellStyle(cellStyle);

        final Row row2 = summarySheet.createRow(2);
        row2.createCell(0).setCellValue("Commulative Project Risk Factor");
        final int riskFactor = new RiskFactorCalculator().measure(project, smellCount);
        row2.createCell(1).setCellValue(riskFactor);

        summarySheet.autoSizeColumn(0);
        summarySheet.autoSizeColumn(1);

        progress.worked(1);
    } catch (Exception e) {
        ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e);
    } finally {
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            ErrorReporter.logExceptionStackTrace("Error while closing the generated excel", e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }
    }
}

---

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 w  w .  j a  v 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());
    }
}

---

From source file:org.isisaddons.module.excel.dom.ExcelConverter.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    final CreationHelper createHelper = wb.getCreationHelper();
    final short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

---

From source file:org.isisaddons.wicket.excel.cpt.ui.ExcelFileModel.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    CreationHelper createHelper = wb.getCreationHelper();
    short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

---

From source file:org.jboss.dashboard.displayer.table.ExportTool.java

License:Apache License

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

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();/*from w ww.j a v a  2s  . co m*/
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 10);
    cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3)));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    styles.put("text_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat()
            .getFormat(DateFormatConverter.convert(LocaleManager.currentLocale(), dateFormatPattern)));
    styles.put("date_cell", style);
    return styles;
}

---

From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Date value, String format) {
    HSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }/*from   www . ja  va 2 s  .  c o  m*/

    HSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

---

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