Example usage for org.apache.poi.ss.usermodel Workbook getCreationHelper

List of usage examples for org.apache.poi.ss.usermodel Workbook getCreationHelper

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getCreationHelper

Introduction

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

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

private String getCellValue(Cell cell) {
    if (cell != null) {
        String value = null;//w  w w. ja  v a2 s .c  o  m
        int type = cell.getCellType();
        if (type == Cell.CELL_TYPE_STRING) {
            value = cell.getStringCellValue();
        } else if (type == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                value = FastDateFormat.getInstance(DEFAULT_OUTPUT_DATE_FORMAT).format(date);
            } else {
                double d = cell.getNumericCellValue();
                if (d == Math.floor(d)) { // need to consider when d is negative
                    value = "" + (int) d;
                } else {
                    value = "" + cell.getNumericCellValue();
                }
            }
        } else if (type == Cell.CELL_TYPE_FORMULA) {
            // get calculated value if the cell type is formula 
            Workbook wb = cell.getSheet().getWorkbook();
            CreationHelper crateHelper = wb.getCreationHelper();
            FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
            // get recursively if the value is still formula 
            value = getCellValue(evaluator.evaluateInCell(cell));
        }
        return value;
    }
    return null;
}

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 {//w ww  . ja va2 s .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  w w w.j  a  va 2 s .com
 * @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.generationcp.middleware.operation.parser.WorkbookParser.java

License:Open Source License

public static String getCellStringValue(final Workbook wb, final Cell cell) {
    if (cell == null) {
        return null;
    }/*from w w  w  .ja  v a  2  s  . c  o m*/
    final FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
    final DataFormatter formatter = new DataFormatter();
    return formatter.formatCellValue(cell, formulaEval);
}

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();//from   ww w. ja v a 2  s. c  o  m
    dateStyle.setDataFormat(fmt.getFormat("yyyy-mm-dd hh:mm:ss"));

    headerStyle = wb.createCellStyle();
    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.geoserver.wfs.response.ExcelOutputFormat.java

License:Open Source License

/**
 * @see WFSGetFeatureOutputFormat#write(Object, OutputStream, Operation)
 *//*from   ww w .  j a  va2s  .co m*/
@Override
protected void write(FeatureCollectionResponse featureCollection, OutputStream output, Operation getFeature)
        throws IOException, ServiceException {

    // Create the workbook
    Workbook wb = getNewWorkbook();
    CreationHelper helper = wb.getCreationHelper();
    ExcelCellStyles styles = new ExcelCellStyles(wb);

    for (Iterator it = featureCollection.getFeature().iterator(); it.hasNext();) {
        SimpleFeatureCollection fc = (SimpleFeatureCollection) it.next();

        // create the sheet for this feature collection
        Sheet sheet = wb.createSheet(fc.getSchema().getTypeName());

        // write out the header
        Row header = sheet.createRow(0);

        SimpleFeatureType ft = fc.getSchema();
        Cell cell;

        cell = header.createCell(0);
        cell.setCellValue(helper.createRichTextString("FID"));
        for (int i = 0; i < ft.getAttributeCount() && i < colLimit; i++) {
            AttributeDescriptor ad = ft.getDescriptor(i);
            cell = header.createCell(i + 1);
            cell.setCellValue(helper.createRichTextString(ad.getLocalName()));
            cell.setCellStyle(styles.getHeaderStyle());
        }

        // write out the features
        SimpleFeatureIterator i = fc.features();
        int r = 0; // row index
        try {
            Row row;
            while (i.hasNext()) {
                r++; // start at 1, since header is at 0

                row = sheet.createRow(r);
                cell = row.createCell(0);

                if (r == (rowLimit - 1) && i.hasNext()) {
                    // there are more features than rows available in this
                    // Excel format. write out a warning line and break
                    RichTextString rowWarning = helper.createRichTextString(
                            TRUNCATE_WARNING + ": ROWS " + r + " - " + fc.size() + " NOT SHOWN");
                    cell.setCellValue(rowWarning);
                    cell.setCellStyle(styles.getWarningStyle());
                    break;
                }

                SimpleFeature f = i.next();
                cell.setCellValue(helper.createRichTextString(f.getID()));
                for (int j = 0; j < f.getAttributeCount() && j < colLimit; j++) {
                    Object att = f.getAttribute(j);
                    if (att != null) {
                        cell = row.createCell(j + 1);
                        if (att instanceof Number) {
                            cell.setCellValue(((Number) att).doubleValue());
                        } else if (att instanceof Date) {
                            cell.setCellValue((Date) att);
                            cell.setCellStyle(styles.getDateStyle());
                        } else if (att instanceof Calendar) {
                            cell.setCellValue((Calendar) att);
                            cell.setCellStyle(styles.getDateStyle());
                        } else if (att instanceof Boolean) {
                            cell.setCellValue((Boolean) att);
                        } else {
                            // ok, it seems we have no better way than dump it as a string
                            String stringVal = att.toString();

                            // if string length > excel cell limit, truncate it and warn the
                            // user, otherwise excel workbook will be corrupted
                            if (stringVal.length() > CELL_CHAR_LIMIT) {
                                stringVal = TRUNCATE_WARNING + " " + stringVal.substring(0,
                                        CELL_CHAR_LIMIT - TRUNCATE_WARNING.length() - 1);
                                cell.setCellStyle(styles.getWarningStyle());
                            }
                            cell.setCellValue(helper.createRichTextString(stringVal));

                        }
                    }
                }
            }
        } finally {
            i.close();
        }
    }

    // write to output
    wb.write(output);
}

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;// 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());
    }
}

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

License:Apache License

private static void setCellComment(final Cell cell, final String commentText) {
    Sheet sheet = cell.getSheet();/*from  w  w  w . j av  a2s. c  o m*/
    Row row = cell.getRow();
    Workbook workbook = sheet.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);

    Drawing drawing = sheet.createDrawingPatriarch();
    Comment comment1 = drawing.createCellComment(anchor);

    RichTextString commentRtf = creationHelper.createRichTextString(commentText);

    comment1.setString(commentRtf);
    Comment comment = comment1;
    cell.setCellComment(comment);
}

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;
}