Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getCreationHelper

Introduction

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

Prototype

@Override
public XSSFCreationHelper getCreationHelper()

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances for XSSF.

Usage

From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java

License:Apache License

@Override
public void generaReporte(ParametrosReporteGlobal param) {
    FileOutputStream fileOut = null;
    try {//from  w  w w  .  ja  v  a 2s.c  om
        //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00"));
        List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal());
        GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination());
        GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial());
        GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal());
        XSSFWorkbook wb = new XSSFWorkbook();
        //estilo para las fechas
        XSSFCellStyle dateCellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        //estilos para las cantidades
        XSSFCellStyle moneyCellStyle = wb.createCellStyle();
        XSSFDataFormat mf = wb.createDataFormat();
        moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00"));
        Sheet sheet = wb.createSheet("Hoja1");
        int rowCount = 1;
        Cell actual;
        Row r;
        for (Servicio x : datos) {
            RenglonRG datRen = renglonFactory.build(x);
            r = sheet.createRow(rowCount);
            rowCount = rowCount + 1;
            for (int i = 0; i < valueMap.length; i++) {
                actual = r.createCell(i);
                if (valueMap[i].getType() == CellValueType.TEXT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    actual.setCellValue(obj.toString());
                }
                if (valueMap[i].getType() == CellValueType.DATE) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    if (obj != null) {
                        Date val = (Date) obj;
                        actual.setCellValue(val);
                        actual.setCellStyle(dateCellStyle);
                    }
                }
                if (valueMap[i].getType() == CellValueType.AMOUNT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    double val = (double) obj;
                    actual.setCellValue(val);
                    actual.setCellStyle(moneyCellStyle);
                }
                if (valueMap[i].getType() == CellValueType.FORMULA) {
                    String formulaRaw = valueMap[i].getValue();
                    ST formula = new ST(formulaRaw);
                    formula.add("row", rowCount + "");
                    actual.setCellFormula(formula.render());
                    actual.setCellStyle(moneyCellStyle);
                }
            }
        }
        llenarEncabezado(sheet, 0, 0);
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException
            | IOException ex) {
        GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}

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

License:Open Source License

/**
 * Returns the CellStyle for a cell showing date and/or time in the format
 * specified by "pattern"; returns the style from "dtStyles" if it contains
 * that style; otherwise, first creates the style using "wb", puts it in
 * "dtStyles" and then returns it//from ww  w  .  j  a  v  a 2s  .  c  om
 * 
 * @param wb
 *        the workbook that the cell showing date and/or time belongs to
 * @param dtStyles
 *        a map containing CellStyles for various date-time patterns
 * @param pattern
 *        the date-time pattern for a cell
 * @return the CellStyle pertaining to "pattern"
 */
private CellStyle getDatetimeStyle(XSSFWorkbook wb, HashMap<String, CellStyle> dtStyles, String pattern) {
    CellStyle style;

    style = dtStyles.get(pattern);
    if (style == null) {
        style = wb.createCellStyle();
        style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(pattern));
        dtStyles.put(pattern, style);
    }

    return style;
}

From source file:org.pentaho.reporting.engine.classic.core.layout.complextext.RichTextRenderingIT.java

License:Open Source License

@Test
public void testExcelRendering() throws Exception {
    URL resource = getClass().getResource("rich-text-sample1.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    report.getReportConfiguration().setConfigProperty(ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY,
            "true");
    report.getReportHeader().getElement(0).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.LTR);/*from ww  w.  j  av a  2  s .  c o m*/
    report.getReportHeader().getElement(1).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.RTL);
    report.getReportHeader().removeElement(0);
    report.getReportHeader().getStyle().setStyleProperty(ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW);
    report.getReportFooter().clear();

    LogicalPageBox logicalPageBox = DebugReportRunner.layoutPage(report, 0);

    RenderNode second = MatchFactory.findElementByName(logicalPageBox, "second");
    assertTrue(second instanceof RenderBox);

    ExcelOutputProcessorMetaData metaData = new ExcelOutputProcessorMetaData(
            ExcelOutputProcessorMetaData.PAGINATION_FULL);
    metaData.initialize(report.getConfiguration());

    XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
    ExcelColorProducer colorProducer = new StaticExcelColorSupport();
    ExcelFontFactory ff = new ExcelFontFactory(hssfWorkbook, colorProducer);
    CreationHelper ch = hssfWorkbook.getCreationHelper();
    ExcelTextExtractor te = new ExcelTextExtractor(metaData, colorProducer, ch, ff);

    Object compute = te.compute((RenderBox) second);
    assertTrue(compute instanceof RichTextString);
    XSSFRichTextString rt = (XSSFRichTextString) compute;
    assertEquals(4, rt.numFormattingRuns());
}

From source file:org.pentaho.reporting.engine.classic.core.layout.complextext.RichTextRenderingIT.java

License:Open Source License

@Test
public void testFastExcelRendering() throws Exception {
    URL resource = getClass().getResource("rich-text-sample1.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    report.getReportConfiguration().setConfigProperty(ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY,
            "true");
    report.getReportHeader().getElement(0).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.LTR);// ww w.  j  a v  a 2 s  .  c  o  m
    report.getReportHeader().getElement(1).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.RTL);
    report.getReportHeader().removeElement(0);
    report.getReportHeader().getStyle().setStyleProperty(ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW);
    report.getReportFooter().clear();

    ExpressionRuntime runtime = new GenericExpressionRuntime(new DefaultTableModel(), 0,
            new DefaultProcessingContext(report));

    RichTextStyleResolver resolver = new RichTextStyleResolver(runtime.getProcessingContext(), report);
    resolver.resolveRichTextStyle(report);

    XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
    ExcelColorProducer colorProducer = new StaticExcelColorSupport();
    ExcelFontFactory ff = new ExcelFontFactory(hssfWorkbook, colorProducer);
    CreationHelper ch = hssfWorkbook.getCreationHelper();
    FastExcelTextExtractor te = new FastExcelTextExtractor(colorProducer, ff, ch);

    Element element = report.getReportHeader().getElement(0);
    Object compute = te.compute(element, runtime);
    assertTrue(compute instanceof RichTextString);
    XSSFRichTextString rt = (XSSFRichTextString) compute;
    assertEquals(4, rt.numFormattingRuns());
}

From source file:org.pentaho.reporting.engine.classic.core.layout.complextext.RichTextRenderingTest.java

License:Open Source License

@Test
public void testFastExcelRendering() throws Exception {
    URL resource = getClass().getResource("rich-text-sample1.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    report.getReportConfiguration().setConfigProperty(ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY,
            "true");
    report.getReportHeader().getElement(0).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.LTR);/*  w ww  .j a v  a2  s.  c  o m*/
    report.getReportHeader().getElement(1).getStyle().setStyleProperty(TextStyleKeys.DIRECTION,
            TextDirection.RTL);
    report.getReportHeader().removeElement(0);
    report.getReportHeader().getStyle().setStyleProperty(ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW);
    report.getReportFooter().clear();

    ExpressionRuntime runtime = new GenericExpressionRuntime(new DefaultTableModel(), 0,
            new DefaultProcessingContext(report));

    RichTextStyleResolver.resolveStyle(report);

    XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
    ExcelColorProducer colorProducer = new StaticExcelColorSupport();
    ExcelFontFactory ff = new ExcelFontFactory(hssfWorkbook, colorProducer);
    CreationHelper ch = hssfWorkbook.getCreationHelper();
    FastExcelTextExtractor te = new FastExcelTextExtractor(colorProducer, ff, ch);

    Element element = report.getReportHeader().getElement(0);
    Object compute = te.compute(element, runtime);
    assertTrue(compute instanceof RichTextString);
    XSSFRichTextString rt = (XSSFRichTextString) compute;
    assertEquals(4, rt.numFormattingRuns());
}

From source file:org.rapidpm.data.table.export.Table2XLSX.java

License:Apache License

@Override
public ByteArrayOutputStream export(final Table table) {
    final XSSFWorkbook workbook = new XSSFWorkbook();

    final XSSFSheet xssfSheet = workbook.createSheet(table.getTableName());
    final Collection<ColumnInformation> infoList = table.getColumnInfoList();
    final XSSFRow xssfHeaderRow = xssfSheet.createRow(0);
    for (final ColumnInformation information : infoList) {
        if (information.isExportable()) {
            final XSSFCell xssfCell = xssfHeaderRow.createCell(information.getSpaltenNr());
            xssfCell.setCellValue(information.getSpaltenName());
            xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else {/*from  ww w  . j av  a 2  s  .co m*/
            if (logger.isDebugEnabled()) {
                logger.debug("ColInfo not exportable " + information.getSpaltenName());
            }
        }
    }

    final List<Row> tableRowList = table.getRowList();
    for (final Row row : tableRowList) {
        final XSSFRow xssfRow = xssfSheet.createRow(row.getRowNr() + 1);
        final List<Cell> cellList = row.getCells();
        for (final Cell cell : cellList) {
            if (cell.getColInfo().isExportable()) {
                final XSSFCell xssfCell = xssfRow.createCell(cell.getColInfo().getSpaltenNr());
                final CellTypeEnum cellType = cell.getCellType();
                if (cellType.equals(CellTypeEnum.RawData)) {
                    xssfCell.setCellValue(cell.getFormattedValue());
                    xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); //JIRA MOD-32 CellType in Abhngigkeit der ValueClass z.B. Number
                } else if (cellType.equals(CellTypeEnum.RawLink)) {
                    final XSSFCreationHelper helper = workbook.getCreationHelper();
                    final XSSFHyperlink xssfHyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
                    xssfHyperlink.setAddress(cell.getFormattedValue());
                    xssfHyperlink.setLabel(cell.getLabel());
                    xssfCell.setCellValue(cell.getLabel());
                    xssfCell.setHyperlink(xssfHyperlink);

                    final CellStyle hlink_style = createHyperLinkStyle(workbook);
                    xssfCell.setCellStyle(hlink_style);
                } else {

                }
            } else {
                if (logger.isDebugEnabled()) {
                    logger.debug("Cell not exportable ");
                }
            }

        }
    }

    final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
        workbook.write(outputStream);
    } catch (IOException e) {
        logger.error(e);
    }
    return outputStream;
}

From source file:se.minstrel.tools.xssfbuilder.impl.Support.java

License:Open Source License

public Support(XSSFWorkbook workbook, MarkerManager markerManager) {
    this.markerManager = markerManager;
    this.workbook = workbook;
    this.styleMap = new HashMap<Style, XSSFCellStyle>();
    this.dataFormat = workbook.createDataFormat();
    this.formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
}

From source file:uk.gov.ofwat.fountain.api.table.POITableRenderer.java

License:Open Source License

public Workbook renderTable(XSSFWorkbook workBook, TableStructure tableStructure, DataTable table) {
    this.workBook = workBook;
    creationHelper = workBook.getCreationHelper();

    sheet = workBook.createSheet(table.getCompany().getCode() + " Table " + tableStructure.getTableName());
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);

    inputDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    copyCellDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    calcDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();

    yellow = new XSSFColor(new java.awt.Color(255, 255, 0));
    lightYellow = new XSSFColor(new java.awt.Color(255, 255, 224));
    lightBlue = new XSSFColor(new java.awt.Color(224, 255, 255));
    pink = new XSSFColor(new java.awt.Color(255, 204, 204));

    // Styles//w w  w . j ava2 s.  co  m
    // Row header style
    rowHeaderStyle = workBook.createCellStyle();
    // Col header style
    colHeaderStyle = workBook.createCellStyle();
    colHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    colHeaderStyle.setFillForegroundColor(yellow);
    Font colHeaderFont = workBook.createFont();
    colHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    colHeaderStyle.setFont(colHeaderFont);
    // Copycell text data cell style
    copyCellTextStyle = workBook.createCellStyle();
    copyCellTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellTextStyle.setFillForegroundColor(pink);
    // Input text data cell style
    inputDataTextStyle = workBook.createCellStyle();
    inputDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputDataTextStyle.setFillForegroundColor(lightYellow);
    // Calc text data cell style
    calcDataTextStyle = workBook.createCellStyle();
    calcDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcDataTextStyle.setFillForegroundColor(lightBlue);
    // Input CG style
    inputCGStyle = workBook.createCellStyle();
    inputCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputCGStyle.setFillForegroundColor(lightYellow);
    // Input CG style
    copyCellCGStyle = workBook.createCellStyle();
    copyCellCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellCGStyle.setFillForegroundColor(pink);
    // Calc CG style
    calcCGStyle = workBook.createCellStyle();
    calcCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcCGStyle.setFillForegroundColor(lightBlue);

    // data format
    DataFormat format = workBook.createDataFormat();

    int rownum = 1; // starting point
    Row infoRow1 = sheet.createRow(rownum);
    CellStyle style = workBook.createCellStyle();
    Font font = workBook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);

    Cell titleCell1 = infoRow1.createCell(0);
    titleCell1.setCellType(Cell.CELL_TYPE_STRING);
    String DATE_FORMAT = "dd MMM yyyy h:mm";
    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
    Calendar c1 = Calendar.getInstance(); // today
    String today = sdf.format(c1.getTime());
    RichTextString dateRts = creationHelper.createRichTextString(today + ": "
            + tableStructure.getModelPage().getModel().getCode() + " for " + table.getCompany().getName());
    titleCell1.setCellValue(dateRts);
    titleCell1.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 9));

    rownum++;
    Row infoRow2 = sheet.createRow(rownum);
    Cell titleCell = infoRow2.createCell(0);
    titleCell.setCellType(Cell.CELL_TYPE_STRING);
    RichTextString rts = creationHelper.createRichTextString(tableStructure.getModelPage().getTable().getName()
            + " - " + tableStructure.getModelPage().getTableDescription());
    titleCell.setCellValue(rts);
    titleCell.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 4));

    rownum++;
    rownum++;

    if (tableStructure.getModelPage().isGroupSelect()) {
        // group dropdown
        groupSelectTable(tableStructure, table, workBook, sheet, format, rownum);
    } else {
        tableWithoutGroupSelect(tableStructure, table, workBook, sheet, format, rownum);
    }
    return workBook;
}

From source file:uk.gov.ofwat.RefTest.java

License:Open Source License

public void writeXLS() throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    // create a new sheet
    Sheet s = wb.createSheet();/*from   ww  w .  j ava  2 s.  c  o m*/
    // declare a row object reference
    Row r = null;
    // declare a cell object reference
    Cell c = null;
    // create 2 cell styles
    XSSFCellStyle cs = wb.createCellStyle();

    XSSFCellStyle cs2 = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();

    // create 2 fonts objects
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // Set font 1 to 12 point type, blue and bold
    f.setFontHeightInPoints((short) 12);
    f.setColor(IndexedColors.RED.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Set font 2 to 10 point type, red and bold
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.RED.getIndex());
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Set cell style and formatting
    cs.setFont(f);
    cs.setDataFormat(df.getFormat("#,##0.0"));

    // Set the other cell style and formatting
    cs2.setBorderBottom(cs2.BORDER_THIN);
    cs2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    cs2.setFont(f2);

    // Define a few rows
    for (int rownum = 0; rownum < 30; rownum++) {
        r = s.createRow(rownum);
        for (int cellnum = 0; cellnum < 10; cellnum += 2) {
            c = r.createCell(cellnum);
            Cell c2 = r.createCell(cellnum + 1);

            c.setCellValue((double) rownum + (cellnum / 10));
            c2.setCellValue(creationHelper.createRichTextString("Hello! " + cellnum));
        }
    }

    File file = new File("d:\\out.xls");
    FileOutputStream fos = new FileOutputStream(file);
    wb.write(fos);
    //      fos.write(wb.getBytes());
    //      fos.flush();
    //      fos.close();

}

From source file:xqt.adapters.csv.test.ExcelTest.java

public void read() {
    try {//w w  w  .ja  va  2s  .  c  om
        FileInputStream file = new FileInputStream(new File("C:\\Users\\standard\\Downloads\\javaTest.xlsx"));
        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheet("Sheet1"); //workbook.getSheet("test");
        //            Stream stream = StreamSupport.stream(sheet.spliterator(), false); // maybe it is better to have a limited size stream by passing the sheet.getPhysicalNumberOfRows()

        Stream<Row> stream = StreamSupport.stream(Spliterators.spliterator(sheet.iterator(),
                sheet.getPhysicalNumberOfRows(), Spliterator.ORDERED), false);

        //long cnt = stream.count();
        stream = stream.skip(1);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        //Iterable<Row> iterable = () -> sheet.iterator();
        List<Entity> result = stream.filter(row -> (!row.getZeroHeight()))
                .map(row -> RowBuilder.createRowArray(row, evaluator)).map(rowArray -> new Entity(rowArray))
                .peek(p -> {
                    System.out.println("");
                }).collect(Collectors.toList());
        long count = result.stream().count();
        System.out.println("Total records: " + count);
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}