Example usage for org.apache.poi.ss.usermodel CreationHelper createDataFormat

List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CreationHelper
  6. createDataFormat

Introduction

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

Prototype

DataFormat createDataFormat();

Source Link

Document

Creates a new DataFormat instance

Usage

From source file:org.nuclos.server.report.export.ExcelExport.java

License:Open Source License

private NuclosFile export(Workbook wb, String sheetname, ResultVO result, List<ReportFieldDefinition> fields,
        String name) throws NuclosReportException {
    sheetname = sheetname != null ? sheetname
            : SpringLocaleDelegate.getInstance().getMessage("XLSExport.2", "Daten aus Nucleus");
    Sheet s = wb.getSheet(sheetname);/*from w  w w.j  a v a2s  . c  o  m*/
    if (s == null) {
        s = wb.createSheet(sheetname);
    }

    int iRowNum = 0;
    int iColumnNum = 0;
    CreationHelper createHelper = wb.getCreationHelper();

    Row row = getRow(s, 0);

    Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();

    for (Iterator<ResultColumnVO> i = result.getColumns().iterator(); i.hasNext(); iColumnNum++) {
        i.next();
        Cell cell = getCell(row, iColumnNum);
        cell.setCellValue(fields.get(iColumnNum).getLabel());

        CellStyle style = wb.createCellStyle();
        String f = getFormat(fields.get(iColumnNum));
        if (f != null) {
            style.setDataFormat(createHelper.createDataFormat().getFormat(f));
        }
        styles.put(iColumnNum, style);
    }
    iRowNum++;

    // export data
    for (int i = 0; i < result.getRows().size(); i++, iRowNum++) {
        iColumnNum = 0;
        Object[] dataRow = result.getRows().get(i);
        row = getRow(s, iRowNum);
        for (int j = 0; j < result.getColumns().size(); j++, iColumnNum++) {
            Object value = dataRow[j];
            Cell c = getCell(row, iColumnNum);
            ReportFieldDefinition def = fields.get(j);

            if (value != null) {
                if (value instanceof List) {
                    final StringBuilder sb = new StringBuilder();
                    for (Iterator<?> it = ((List<?>) value).iterator(); it.hasNext();) {
                        final Object v = it.next();
                        sb.append(CollectableFieldFormat.getInstance(def.getJavaClass())
                                .format(def.getOutputformat(), v));
                        if (it.hasNext()) {
                            sb.append(", ");
                        }
                    }
                    c.setCellValue(sb.toString());
                } else {
                    if (Date.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Date) value);
                    } else if (Integer.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Integer) value);
                    } else if (Double.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Double) value);
                    } else {
                        c.setCellValue(String.valueOf(value));
                    }
                }
            } else {
                c.setCellValue("");
            }
        }
    }

    try {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    } catch (Exception e) {
    } // ignore any Exception

    ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
    try {
        wb.write(baos);
        return new NuclosFile(name + format.getExtension(), baos.toByteArray());
    } catch (IOException e) {
        throw new NuclosReportException(e);
    } finally {
        try {
            baos.close();
        } catch (IOException e) {
        }
    }
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

private void createStyles(HSSFWorkbook wb) {
    CellStyle dateTimeEditStyle, dateTimeNoEditStyle, headerStyle, rowEditStyle, rowNoEditStyle;
    CreationHelper helper;
    Font font;//from ww  w  .  j a  va  2s. c om

    helper = wb.getCreationHelper();
    styles = new HashMap<String, CellStyle>();

    font = wb.createFont();
    font.setColor(IndexedColors.WHITE.getIndex());
    headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
    headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
    headerStyle.setFont(font);
    headerStyle.setLocked(true);
    styles.put("header", headerStyle);

    rowEditStyle = wb.createCellStyle();
    rowEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowEditStyle.setLocked(false);
    styles.put("row_edit", rowEditStyle);

    rowNoEditStyle = wb.createCellStyle();
    rowNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    rowNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    rowNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowNoEditStyle.setLocked(true);
    styles.put("row_no_edit", rowNoEditStyle);

    dateTimeEditStyle = wb.createCellStyle();
    dateTimeEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeEditStyle.setLocked(false);
    styles.put("datetime_edit", dateTimeEditStyle);

    dateTimeNoEditStyle = wb.createCellStyle();
    dateTimeNoEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    dateTimeNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    dateTimeNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeNoEditStyle.setLocked(true);
    styles.put("datetime_no_edit", dateTimeNoEditStyle);
}

From source file:org.openepics.discs.ccdb.gui.export.ExcelExportTable.java

License:Open Source License

private void initTimestampStyle() {
    timestampStyle = wb.createCellStyle();
    final CreationHelper creationHelper = wb.getCreationHelper();
    timestampStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
}

From source file:org.riflemansd.businessprofit.excel.MyExcelDocument.java

License:Open Source License

public void setDate(int nsheet, int nrow, int ncolumn, Date value) {
    org.apache.poi.ss.usermodel.Cell cell = getCell(nsheet, nrow, ncolumn);
    CellStyle style = workbook.createCellStyle();
    CreationHelper helper = workbook.getCreationHelper();
    style.setDataFormat(helper.createDataFormat().getFormat("dd/mm/yyy"));
    cell.setCellValue(value);//from www  .  j  av  a 2 s  .c o  m
    cell.setCellStyle(style);
}

From source file:org.seedstack.io.jasper.fixtures.CustomXlsRenderer.java

License:Mozilla Public License

@Override
public void render(OutputStream outputStream, Object model, String mimeType, Map<String, Object> parameters) {
    Validate.isTrue(StringUtils.equals(mimeType, "application/xls"));
    try {/*from w ww . java  2 s.  com*/
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        CreationHelper createHelper = wb.getCreationHelper();
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);

        // Or do it on one line.
        CustomerBean bean = (CustomerBean) model;
        row.createCell(1).setCellValue(bean.getCustomerNo());
        row.createCell(2).setCellValue(createHelper.createRichTextString(bean.getFirstName()));
        row.createCell(3).setCellValue(createHelper.createRichTextString(bean.getLastName()));
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        Cell cell = row.createCell(4);
        cell.setCellValue(bean.getBirthDate());
        cell.setCellStyle(cellStyle);
        row.createCell(5).setCellValue(bean.getMailingAddress());
        row.createCell(6).setCellValue(bean.getMarried());
        row.createCell(7).setCellValue(bean.getNumberOfKids());
        row.createCell(8).setCellValue(bean.getFavouriteQuote());
        row.createCell(9).setCellValue(bean.getEmail());
        row.createCell(10).setCellValue(bean.getLoyaltyPoints());

        wb.write(outputStream);
        outputStream.close();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

From source file:org.sevenorcas.style.app.mod.ss.SpreadsheetCell.java

/**
 * Get cell format/*from   www.j a  v a 2  s .c o m*/
 * @param wb
 * @return
 */
public HSSFCellStyle getCellStyle(HSSFWorkbook wb) {

    //EX1
    if (sheet.containsStyleId(styleId)) {
        return sheet.getStyle(styleId);
    }

    HSSFCellStyle style = wb.createCellStyle();
    Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null);

    switch (clazzX != null ? clazzX : CLASS_STRING) {
    case CLASS_DATE:
        if (!isHeader()) {
            CreationHelper createHelper = wb.getCreationHelper();
            style = wb.createCellStyle();
            style.setDataFormat(createHelper.createDataFormat()
                    .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy"));
        }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_PERCENTAGE:
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER:
    case CLASS_LONG:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_DOUBLE:
    case CLASS_UKURS:
        //Needs work
        //              if (numberFormat != null){
        //                  createHelper = wb.getCreationHelper();
        //                  style = wb.createCellStyle();
        //                  style.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat));
        //              }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER_LEFT:
    case CLASS_STRING:
    case CLASS_BOOLEAN:
    case CLASS_CHARACTER:
    default:
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    }

    sheet.setCellStyle(wb, style, this, styleId);
    return style;
}

From source file:org.wurtele.ifttt.watchers.WorkTimesWatcher.java

License:Open Source License

private void processFile(Path input) {
    logger.info("Updating " + output);

    try (Workbook wb = new XSSFWorkbook();
            OutputStream out = Files.newOutputStream(output, StandardOpenOption.CREATE,
                    StandardOpenOption.TRUNCATE_EXISTING)) {
        Sheet sheet = wb.createSheet("Time Sheet");
        List<WorkDay> days = new ArrayList<>();
        DateFormat df = new SimpleDateFormat("MMMM dd, yyyy 'at' hh:mma");
        for (String line : Files.readAllLines(input)) {
            String[] data = line.split(";");
            LocationType type = LocationType.valueOf(data[0].toUpperCase());
            Date time = df.parse(data[1]);
            Date day = DateUtils.truncate(time, Calendar.DATE);
            WorkDay wd = new WorkDay(day);
            if (days.contains(wd))
                wd = days.get(days.indexOf(wd));
            else/*  w w  w .  j a v  a 2 s. c  o  m*/
                days.add(wd);
            wd.getTimes().add(new WorkTime(time, type));
        }

        CreationHelper helper = wb.getCreationHelper();
        Font bold = wb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);

        CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(helper.createDataFormat().getFormat("MMMM d, yyyy"));
        CellStyle timeStyle = wb.createCellStyle();
        timeStyle.setDataFormat(helper.createDataFormat().getFormat("h:mm AM/PM"));
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setFont(bold);
        CellStyle totalStyle = wb.createCellStyle();
        totalStyle.setAlignment(CellStyle.ALIGN_RIGHT);

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("DATE");
        header.getCell(0).setCellStyle(headerStyle);

        Collections.sort(days);
        for (int r = 0; r < days.size(); r++) {
            WorkDay day = days.get(r);
            Row row = sheet.createRow(r + 1);
            row.createCell(0).setCellValue(day.getDate());
            row.getCell(0).setCellStyle(dateStyle);
            Collections.sort(day.getTimes());
            for (int c = 0; c < day.getTimes().size(); c++) {
                WorkTime time = day.getTimes().get(c);
                if (sheet.getRow(0).getCell(c + 1) != null
                        && !sheet.getRow(0).getCell(c + 1).getStringCellValue().equals(time.getType().name())) {
                    throw new Exception("Invalid data");
                } else if (sheet.getRow(0).getCell(c + 1) == null) {
                    sheet.getRow(0).createCell(c + 1).setCellValue(time.getType().name());
                    sheet.getRow(0).getCell(c + 1).setCellStyle(headerStyle);
                }
                row.createCell(c + 1).setCellValue(time.getTime());
                row.getCell(c + 1).setCellStyle(timeStyle);
            }
        }

        int totalCol = header.getLastCellNum();
        header.createCell(totalCol).setCellValue("TOTAL");
        header.getCell(totalCol).setCellStyle(headerStyle);

        for (int r = 0; r < days.size(); r++) {
            sheet.getRow(r + 1).createCell(totalCol).setCellValue(days.get(r).getTotal());
            sheet.getRow(r + 1).getCell(totalCol).setCellStyle(totalStyle);
        }

        for (int c = 0; c <= totalCol; c++) {
            sheet.autoSizeColumn(c);
        }

        wb.write(out);
    } catch (Exception e) {
        logger.error("Failed to update " + output, e);
    }
}

From source file:org.zafritech.zidingorms.io.excel.ExcelFunctions.java

private static Map<String, CellStyle> createStyles(Workbook wb) {

    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CreationHelper creationHelper = wb.getCreationHelper();

    CellStyle style;/*  w w w .j  a va 2 s.  c o  m*/

    // Header Font
    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 12);
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());

    // Header Left Aligned Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(headerFont);
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("HeaderLeftAlign", style);

    // Header Center Aligned Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(headerFont);
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("HeaderCenterAlign", style);

    // Body Left Aligned Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    styles.put("BodyLeftAlign", style);

    // Body Center Aligned Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    styles.put("BodyCenterAlign", style);

    // Body Left Aligned WrapText Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setWrapText(true);
    styles.put("BodyLeftAlignWrapText", style);

    // Body Left Aligned Date Format Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
    styles.put("BodyLeftAlignDate", style);

    // Body Center Aligned Date Format Style
    style = createBorderedStyle(wb);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
    styles.put("BodyCenterAlignDate", style);

    return styles;
}

From source file:packtest.CreateCell.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 0);
    cell.setCellValue(1);/*from   ww w. j  a  v a2s .c  o m*/

    //numeric value
    row.createCell(1).setCellValue(1.2);

    //plain string value
    row.createCell(2).setCellValue("This is a string cell");

    //rich text string
    RichTextString str = creationHelper.createRichTextString("Apache");
    Font font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(Font.U_SINGLE);
    str.applyFont(font);
    row.createCell(3).setCellValue(str);

    //boolean value
    row.createCell(4).setCellValue(true);

    //formula
    row.createCell(5).setCellFormula("SUM(A1:B1)");

    //date
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(6);
    cell.setCellValue(new Date());
    cell.setCellStyle(style);

    //hyperlink
    row.createCell(7).setCellFormula("SUM(A1:B1)");
    cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java

License:Apache License

private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) {

    final Workbook wb = sheet.getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy"));

    Row row = sheet.createRow(rowIdx);/*  w ww  . j a  v  a2s.  co  m*/

    Cell cell = row.createCell(colIdx);
    cell.setCellValue("Open Pension Fund");

    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(createHeaderFont(wb, (short) 12));
    cell.setCellStyle(cellStyle);

    cell = row.createCell(colIdx + 1);
    cell.setCellValue("Number of members");
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(rowIdx + 1);
    sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund
            rowIdx, // first row (0-based)
            rowIdx + 1, // last row (0-based)
            colIdx, // first column (0-based)
            colIdx // last column (0-based)
    ));

    sheet.addMergedRegion(new CellRangeAddress(// merge Number of members
            rowIdx, // first row (0-based)
            rowIdx, // last row (0-based)
            colIdx + 1, // first column (0-based)
            colIdx + dates.size() // last column (0-based)
    ));

    int colIt = colIdx + 1;
    for (final Date date : dates) {
        cell = row.createCell(colIt++);
        cell.setCellValue(date);
        cell.setCellStyle(dateCellStyle);
    }

}