Example usage for org.apache.poi.ss.util WorkbookUtil createSafeSheetName

List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. WorkbookUtil
  6. createSafeSheetName

Introduction

In this page you can find the example usage for org.apache.poi.ss.util WorkbookUtil createSafeSheetName.

Prototype

public static String createSafeSheetName(final String nameProposal)

Source Link

Document

Creates a valid sheet name, which is conform to the rules.

Usage

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

License:Open Source License

@Test
public void testAdditionalInformationSheetIgnored() throws IOException {
    /* setup *///  w ww.  ja  v a2 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());
}

From source file:org.gedantic.web.servlet.WorkbookCreator.java

License:Open Source License

/**
 * Creates the workbook/*w  ww  . j av  a 2 s  .  c o  m*/
 *
 * @return the workbook
 */
public Workbook create() {
    wb = new XSSFWorkbook();
    setupStyles();
    sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(analyzer.getName()));

    setupStyles();

    addTitleAndSubTitle();

    inspectResults();

    addHeaders();
    addRowsOfData();
    addFooter();

    autofitColumns();

    return wb;
}

From source file:org.jberet.support.io.ExcelUserModelItemWriter.java

License:Open Source License

@Override
public void open(final Serializable checkpoint) throws Exception {
    //if template is used, create workbook based on template resource, and try to get header from template
    if (templateResource != null) {
        InputStream templateInputStream = null;
        try {//from ww w  .jav a2s.  c o  m
            templateInputStream = getInputStream(templateResource, false);

            //for SXSSF (streaming), the original templateWorkbook is wrapped inside this.workbook, and these 2
            // workbook instances are different.  For XSSF and HSSF, the two are the same.
            // SXSSF workbook does not support reading, so we have to use the original templateWorkbook to read
            // header, and then reassign sheet to that of this.workbook, which is SXSSFWorkbook
            final Workbook templateWorkbook = createWorkbook(templateInputStream);
            if (templateSheetName != null) {
                sheet = templateWorkbook.getSheet(templateSheetName);
            }
            if (sheet == null) {
                sheet = templateWorkbook.getSheetAt(templateSheetIndex);
            }
            //if header property is already injected from job.xml, use it and no need to check templateHeaderRow
            if (header == null) {
                if (templateHeaderRow == null) {
                    throw SupportMessages.MESSAGES.invalidReaderWriterProperty(null, null, "templateHeaderRow");
                }
                final Row headerRow = sheet.getRow(templateHeaderRow);
                if (headerRow == null) {
                    throw SupportMessages.MESSAGES.failToReadExcelHeader(templateResource, templateSheetName);
                }
                header = getCellStringValues(headerRow);
            }
            currentRowNum = sheet.getLastRowNum();
            if (workbook != templateWorkbook) {
                sheet = workbook.getSheet(sheet.getSheetName());
            }
            workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        } finally {
            if (templateInputStream != null) {
                try {
                    templateInputStream.close();
                } catch (final Exception e) {
                    SupportLogger.LOGGER.tracef(e,
                            "Failed to close template InputStream %s for template resource %s%n",
                            templateInputStream, templateResource);
                }
            }
        }
    } else { // no template is specified
        createWorkbook(null);
        sheet = sheetName == null ? workbook.createSheet()
                : workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));

        if (header == null) {
            throw SupportMessages.MESSAGES.invalidReaderWriterProperty(null, null, "header");
        }
        //write header row
        final Row headerRow = sheet.createRow(0);
        for (int i = 0, j = header.length; i < j; ++i) {
            headerRow.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(header[i]);
        }
        currentRowNum = 0;
    }
    outputStream = getOutputStream(writeMode);
}

From source file:org.olat.course.db.CustomDBController.java

License:Apache License

private byte[] getDbsContent(String courseTitle, String category) {
    ICourse course = CourseFactory.loadCourse(courseKey);
    List<CourseDBEntry> content = CourseDBManager.getInstance().getValues(course, null, category, null);

    Workbook wb = new HSSFWorkbook();
    CellStyle headerCellStyle = getHeaderCellStyle(wb);
    String saveTitle = WorkbookUtil.createSafeSheetName(courseTitle);
    Sheet exportSheet = wb.createSheet(saveTitle);

    //create the headers
    Row headerRow = exportSheet.createRow(0);
    Cell cell = headerRow.createCell(0);
    cell.setCellValue(translate("customDb.category"));
    cell.setCellStyle(headerCellStyle);//  www .j a  v  a  2 s . c o  m

    cell = headerRow.createCell(1);
    cell.setCellValue(translate("customDb.entry.identity"));
    cell.setCellStyle(headerCellStyle);

    cell = headerRow.createCell(2);
    cell.setCellValue(translate("customDb.entry.name"));
    cell.setCellStyle(headerCellStyle);

    cell = headerRow.createCell(3);
    cell.setCellValue(translate("customDb.entry.value"));
    cell.setCellStyle(headerCellStyle);

    int count = 0;
    for (CourseDBEntry entry : content) {
        User user = entry.getIdentity().getUser();
        String name = user.getProperty(UserConstants.FIRSTNAME, null) + " "
                + user.getProperty(UserConstants.LASTNAME, null);

        Row dataRow = exportSheet.createRow(++count);
        Cell dataCell = dataRow.createCell(0);
        dataCell.setCellValue(entry.getCategory());

        dataCell = dataRow.createCell(1);
        dataCell.setCellValue(name);

        if (StringHelper.containsNonWhitespace(entry.getName())) {
            dataCell = dataRow.createCell(2);
            dataCell.setCellValue(entry.getName());
        }

        if (entry.getValue() != null) {
            dataCell = dataRow.createCell(3);
            dataCell.setCellValue(entry.getValue().toString());
        }
    }

    try {
        ByteArrayOutputStream fos = new ByteArrayOutputStream();
        wb.write(fos);
        fos.flush();
        return fos.toByteArray();
    } catch (IOException e) {
        logError("", e);
    }
    return null;
}

From source file:org.olat.instantMessaging.manager.ChatLogHelper.java

License:Apache License

public Workbook log(OLATResourceable ores, Locale locale) {
    Translator translator = Util.createPackageTranslator(ChatController.class, locale);

    Workbook wb = new HSSFWorkbook();
    String tableExportTitle = translator.translate("logChat.export.title");
    String saveTitle = WorkbookUtil.createSafeSheetName(tableExportTitle);
    Sheet exportSheet = wb.createSheet(saveTitle);
    Formatter formatter = Formatter.getInstance(locale);

    //headers//from w w w  .jav  a  2 s.  c o  m
    Row headerRow = exportSheet.createRow(0);
    CellStyle headerCellStyle = getHeaderCellStyle(wb);
    addHeader(headerRow, headerCellStyle, "User", 0);
    addHeader(headerRow, headerCellStyle, "Date", 1);
    addHeader(headerRow, headerCellStyle, "Content", 2);

    //content
    List<InstantMessage> messages = imDao.getMessages(ores, null, 0, -1);
    int count = 1;
    for (InstantMessage message : messages) {
        Row dataRow = exportSheet.createRow(count++);
        addCell(dataRow, message.getFromNickName(), 0);
        addCell(dataRow, message.getCreationDate(), 1, formatter);
        addCell(dataRow, message.getBody(), 2);
    }
    return wb;
}

From source file:org.pivot4j.ui.poi.ExcelExporter.java

License:Common Public License

/**
 * @param context/*  w  w  w . j a  va  2  s  .c om*/
 * @param workbook
 * @return
 */
protected Sheet createSheet(TableRenderContext context, Workbook workbook) {
    Sheet newSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(getSheetName(context, sheetIndex)));

    sheetIndex++;
    return newSheet;
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

@Override
public void export(FacesContext context, DataTable table, String filename, boolean pageOnly,
        boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, ExporterOptions options) throws IOException {
    Workbook wb = createWorkBook();/*from  w ww  . j  av a2  s .c om*/
    String sheetName = getSheetName(context, table);
    if (sheetName == null) {
        sheetName = table.getId();
    }

    sheetName = WorkbookUtil.createSafeSheetName(sheetName);
    if (sheetName.equals("empty") || sheetName.equals("null")) {
        sheetName = "Sheet";
    }

    Sheet sheet = wb.createSheet(sheetName);

    if (preProcessor != null) {
        preProcessor.invoke(context.getELContext(), new Object[] { wb });
    }

    applyOptions(wb, table, sheet, options);
    exportTable(context, table, sheet, pageOnly, selectionOnly);

    for (int i = 0; i < table.getColumnsCount(); i++) {
        sheet.autoSizeColumn((short) i);
    }

    if (postProcessor != null) {
        postProcessor.invoke(context.getELContext(), new Object[] { wb });
    }

    writeExcelToResponse(context.getExternalContext(), wb, filename);
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

@Override
public void export(FacesContext context, String filename, List<DataTable> tables, boolean pageOnly,
        boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, ExporterOptions options) throws IOException {
    Workbook wb = createWorkBook();//from www . j  ava 2s  .  c  om

    if (preProcessor != null) {
        preProcessor.invoke(context.getELContext(), new Object[] { wb });
    }

    for (int i = 0; i < tables.size(); i++) {
        DataTable table = tables.get(i);
        String sheetName = getSheetName(context, table);
        if (sheetName == null) {
            sheetName = table.getId();
        }

        sheetName = WorkbookUtil.createSafeSheetName(sheetName);
        if (sheetName.equals("empty") || sheetName.equals("null")) {
            sheetName = "Sheet" + String.valueOf(i + 1);
        }

        Sheet sheet = wb.createSheet(sheetName);
        applyOptions(wb, table, sheet, options);
        exportTable(context, table, sheet, pageOnly, selectionOnly);

        for (int j = 0; j < table.getColumnsCount(); j++) {
            sheet.autoSizeColumn((short) j);
        }
    }

    if (postProcessor != null) {
        postProcessor.invoke(context.getELContext(), new Object[] { wb });
    }

    writeExcelToResponse(context.getExternalContext(), wb, filename);
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();/*from  www  .java  2 s .  co m*/
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();

    facetStyleLeftAlign = wb.createCellStyle();
    facetStyleCenterAlign = wb.createCellStyle();
    facetStyleRightAlign = wb.createCellStyle();
    cellStyleLeftAlign = wb.createCellStyle();
    cellStyleCenterAlign = wb.createCellStyle();
    cellStyleRightAlign = wb.createCellStyle();

    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable)
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();//from   ww  w.ja  v a  2s .co  m
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBold(true);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable) {
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }
    }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}