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

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

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

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:org.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

/**
 * Write the specified dataset to the specified Excel document.
 *///from   w  w w .  j av a  2s .c  o m
public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException {
    logger.debug("write(dataSet={}, out={}) - start", dataSet, out);

    Workbook workbook = createWorkbook();

    this.dateCellStyle = createDateCellStyle(workbook);

    int index = 0;
    ITableIterator iterator = dataSet.iterator();
    while (iterator.next()) {
        // create the table i.e. sheet
        ITable table = iterator.getTable();
        ITableMetaData metaData = table.getTableMetaData();
        Sheet sheet = workbook.createSheet(metaData.getTableName());

        // write table metadata i.e. first row in sheet
        workbook.setSheetName(index, metaData.getTableName());

        Row headerRow = sheet.createRow(0);
        Column[] columns = metaData.getColumns();
        for (int j = 0; j < columns.length; j++) {
            Column column = columns[j];
            Cell cell = headerRow.createCell(j);
            cell.setCellValue(column.getColumnName());
        }

        // write table data
        for (int j = 0; j < table.getRowCount(); j++) {
            Row row = sheet.createRow(j + 1);
            for (int k = 0; k < columns.length; k++) {
                Column column = columns[k];
                Object value = table.getValue(j, column.getColumnName());
                if (value != null) {
                    Cell cell = row.createCell(k);
                    if (value instanceof Date) {
                        setDateCell(cell, (Date) value, workbook);
                    } else if (value instanceof BigDecimal) {
                        setNumericCell(cell, (BigDecimal) value, workbook);
                    } else if (value instanceof Long) {
                        setDateCell(cell, new Date(((Long) value).longValue()), workbook);
                    } else {
                        cell.setCellValue(DataType.asString(value));
                    }
                }
            }
        }

        index++;
    }

    // write xls document
    workbook.write(out);
    out.flush();
}

From source file:org.dhatim.fastexcel.Benchmarks.java

License:Apache License

private int poiPopulate(org.apache.poi.ss.usermodel.Workbook wb) throws Exception {
    Sheet ws = wb.createSheet("Sheet 1");
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
    for (int r = 0; r < NB_ROWS; ++r) {
        Row row = ws.createRow(r);/*from ww  w.  java  2 s .  c om*/
        row.createCell(0).setCellValue(r);
        row.createCell(1).setCellValue(Integer.toString(r % 1000));
        row.createCell(2).setCellValue(r / 87.0);
        Cell c = row.createCell(3);
        c.setCellStyle(dateStyle);
        c.setCellValue(new Date(1549915044));
    }
    CountingOutputStream count = new CountingOutputStream(new NullOutputStream());
    wb.write(count);
    return count.getCount();
}

From source file:org.dkpro.lab.reporting.FlexTable.java

License:Apache License

public StreamWriter getExcelWriter() {
    return new StreamWriter() {
        @Override/*from w ww  . j  av a  2 s.  c  om*/
        public void write(OutputStream aStream) throws Exception {
            String[] colIds = FlexTable.this.compact ? getCompactColumnIds(false) : getColumnIds();

            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Summary");

            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);

            // Header row
            {
                Row row = sheet.createRow(0);
                Cell rowIdCell = row.createCell(0);
                rowIdCell.setCellValue("ID");

                int colNum = 1;
                for (String colId : colIds) {
                    Cell cell = row.createCell(colNum);
                    cell.setCellValue(colId);
                    colNum++;
                }
            }

            // Body rows
            {
                int rowNum = 1;
                for (String rowId : getRowIds()) {
                    Row row = sheet.createRow(rowNum);
                    Cell rowIdCell = row.createCell(0);
                    rowIdCell.setCellValue(rowId);

                    int colNum = 1;
                    for (String colId : colIds) {
                        Cell cell = row.createCell(colNum);
                        String value = getValueAsString(rowId, colId);
                        try {
                            cell.setCellValue(Double.valueOf(value));
                        } catch (NumberFormatException e) {
                            cell.setCellValue(value);
                        }
                        colNum++;
                    }
                    rowNum++;
                }
            }

            wb.write(aStream);
        }
    };
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiAttributeConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetMultiAttributeConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);/*from w ww.  j  a va 2 s . c  o m*/

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiReferenceConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetMultiReferenceConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);/*from w  ww  .j  ava  2s . c om*/

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleAttributeConverter_Test.java

License:Open Source License

@Before
public void setup() {
    final Workbook wb = new HSSFWorkbook();
    cellStyle = wb.createCellStyle();//from www  .  j  av a2s. c o m
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    cellStyle2 = wb.createCellStyle();
    cellStyle2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.DATE)).thenReturn(cellStyle2);

    converter = new EMFFormsSpreadsheetSingleAttributeConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleReferenceConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetSingleReferenceConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);//w  w  w.  j  a v  a  2s.  c o  m

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.EMFFormsSpreadsheetControlRenderer.java

License:Open Source License

/**
 * {@inheritDoc}// ww w  .j  av a2s  .co  m
 *
 * @see org.eclipse.emfforms.spi.spreadsheet.core.EMFFormsAbstractSpreadsheetRenderer#render(org.apache.poi.ss.usermodel.Workbook,
 *      org.eclipse.emf.ecp.view.spi.model.VElement, org.eclipse.emf.ecp.view.spi.context.ViewModelContext,
 *      org.eclipse.emfforms.spi.spreadsheet.core.EMFFormsSpreadsheetRenderTarget)
 */
@Override
public int render(Workbook workbook, VControl vElement, ViewModelContext viewModelContext,
        EMFFormsSpreadsheetRenderTarget renderTarget) {
    Sheet sheet = workbook.getSheet(renderTarget.getSheetName());
    if (sheet == null) {
        sheet = workbook.createSheet(renderTarget.getSheetName());
        setupSheetFormat(sheet);
    }
    Row labelRow = sheet.getRow(0);
    if (labelRow == null) {
        labelRow = sheet.createRow(0);
    }
    Row descriptionRow = sheet.getRow(1);
    if (descriptionRow == null) {
        descriptionRow = sheet.createRow(1);
    }
    Row formatRow = sheet.getRow(2);
    if (formatRow == null) {
        formatRow = sheet.createRow(2);
    }

    final CellStyle readOnly = (CellStyle) viewModelContext.getContextValue(EMFFormsCellStyleConstants.LOCKED);
    final CellStyle readOnlyWrap = (CellStyle) viewModelContext
            .getContextValue(EMFFormsCellStyleConstants.LOCKED_AND_WRAPPED);

    final Cell idCell = labelRow.getCell(0, Row.CREATE_NULL_AS_BLANK);
    idCell.setCellValue(EMFFormsIdProvider.ID_COLUMN);
    idCell.setCellStyle(readOnly);

    final Cell labelCell = labelRow.getCell(renderTarget.getColumn() + 1, Row.CREATE_NULL_AS_BLANK);
    labelCell.setCellStyle(readOnlyWrap);

    final Cell descriptionCell = descriptionRow.getCell(renderTarget.getColumn() + 1, Row.CREATE_NULL_AS_BLANK);
    descriptionCell.setCellStyle(readOnlyWrap);

    final Cell formatCell = formatRow.getCell(renderTarget.getColumn() + 1, Row.CREATE_NULL_AS_BLANK);
    formatCell.setCellStyle(readOnlyWrap);

    try {
        final EMFFormsExportTableParent exportTableParent = (EMFFormsExportTableParent) viewModelContext
                .getContextValue(EMFFormsExportTableParent.EXPORT_TABLE_PARENT);
        VDomainModelReference dmrToResolve = EcoreUtil.copy(vElement.getDomainModelReference());
        if (exportTableParent != null) {
            final VIndexDomainModelReference indexDMR = exportTableParent.getIndexDMRToExtend();
            indexDMR.setTargetDMR(dmrToResolve);

            dmrToResolve = exportTableParent.getIndexDMRToResolve();
        }

        if (labelCell.getCellComment() == null) {
            final EStructuralFeature structuralFeature = emfformsDatabinding
                    .getValueProperty(dmrToResolve, viewModelContext.getDomainModel()).getStructuralFeature();

            writeLabel(vElement, viewModelContext, labelCell, exportTableParent, dmrToResolve,
                    structuralFeature);

            final Comment comment = createComment(workbook, sheet, dmrToResolve, renderTarget.getRow(),
                    renderTarget.getColumn() + 1);
            labelCell.setCellComment(comment);

            writeDescription(viewModelContext, descriptionCell, dmrToResolve);

            writeFormatInformation(formatCell, structuralFeature);
        }
        if (viewModelContext.getDomainModel() != null) {
            writeValue(viewModelContext, renderTarget, sheet, dmrToResolve);
        }

        return 1;
    } catch (final DatabindingFailedException ex) {
        reportService.report(new EMFFormsSpreadsheetReport(ex, EMFFormsSpreadsheetReport.ERROR));
    } catch (final NoLabelFoundException ex) {
        reportService.report(new EMFFormsSpreadsheetReport(ex, EMFFormsSpreadsheetReport.ERROR));
    } catch (final IOException ex) {
        reportService.report(new EMFFormsSpreadsheetReport(ex, EMFFormsSpreadsheetReport.ERROR));
    } catch (final EMFFormsConverterException ex) {
        reportService.report(new EMFFormsSpreadsheetReport(ex, EMFFormsSpreadsheetReport.ERROR));
    }

    return 0;
}

From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java

License:Open Source License

@Test
public void testDuplicateEObjectIDsOnDifferentSheets() throws IOException {
    /* setup *///from w  w  w.  j a v a  2s  .  co  m
    stream = bundle.getEntry("errorSheets/basexls").openStream(); //$NON-NLS-1$
    final Workbook workbook = new HSSFWorkbook(stream);
    final Sheet sheet2 = workbook.createSheet("Sheet2"); //$NON-NLS-1$
    sheet2.createRow(0).createCell(0).setCellValue(EMFFormsIdProvider.ID_COLUMN);
    sheet2.createRow(3).createCell(0).setCellValue("_5XI6cEG2EeW04_MCsEmiSg"); //$NON-NLS-1$

    /* act */
    final SpreadsheetImportResult result = EMFFormsSpreadsheetImporter.INSTANCE.importSpreadsheet(workbook,
            eClass);
    final EList<ErrorReport> errorReports = result.getErrorReports();

    /* assert */
    assertEquals(0, errorReports.size());
}

From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java

License:Open Source License

@Test
public void testAdditionalInformationSheetIgnored() throws IOException {
    /* setup */// ww  w.  ja  va2 s .com
    stream = bundle.getEntry("errorSheets/basexls").openStream(); //$NON-NLS-1$
    final Workbook workbook = new HSSFWorkbook(stream);
    final Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("My Sheet")); //$NON-NLS-1$
    final Row titleRow = sheet.createRow(0);
    final Cell idLabelCell = titleRow.createCell(0);
    idLabelCell.setCellValue(EMFFormsIdProvider.ID_COLUMN);
    idLabelCell.setCellComment(createComment(workbook, sheet, 0, 0));
    titleRow.createCell(1).setCellValue("MyColumn"); //$NON-NLS-1$

    for (int i = 1; i < 5; i++) {
        final Row dataRow = sheet.createRow(i);
        dataRow.createCell(0).setCellValue("_5XI6cEG2EeW04_MCsEmiSg"); //$NON-NLS-1$
        dataRow.createCell(1).setCellValue("My Value " + i); //$NON-NLS-1$
    }

    /* act */
    final SpreadsheetImportResult result = EMFFormsSpreadsheetImporter.INSTANCE.importSpreadsheet(workbook,
            eClass);
    final EList<ErrorReport> errorReports = result.getErrorReports();

    /* assert */
    assertEquals(0, errorReports.size());
}