/** * Creates a valid sheet name, which is conform to the rules. * In any case, the result safely can be used for * {@link org.apache.poi.ss.usermodel.Workbook#setSheetName(int, String)}. * <br> * Rules: * <ul> * <li>never null</li> * <li>minimum length is 1</li> * <li>maximum length is 31</li> * <li>doesn't contain special chars: : 0x0000, 0x0003, / \ ? * ] [ </li> * <li>Sheet names must not begin or end with ' (apostrophe)</li> * </ul> * Invalid characters are replaced by one space character ' '. * * @param nameProposal can be any string, will be truncated if necessary, * allowed to be null * @return a valid string, "empty" if to short, "null" if null */ public static String createSafeSheetName(final String nameProposal) { return createSafeSheetName(nameProposal, ' '); }
@Override public void export(FacesContext context, String filename, List<DataTable> tables, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, ExporterOptions options, MethodExpression onTableRender) throws IOException { Workbook wb = createWorkBook(); 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 = createSheet(wb, 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); }
@Override public void export(FacesContext context, DataTable table, String filename, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, ExporterOptions options, MethodExpression onTableRender) throws IOException { Workbook wb = createWorkBook(); 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 = createSheet(wb, 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); }
Device device = Context.getIdentityManager().getById(deviceId); deviceEvents.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceEvents.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId());
Device device = Context.getIdentityManager().getById(deviceId); deviceStops.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceStops.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId());
Device device = Context.getIdentityManager().getById(deviceId); deviceTrips.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceTrips.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId());
Device device = Context.getIdentityManager().getById(deviceId); deviceRoutes.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceRoutes.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId());
/** * Creates a valid sheet name, which is conform to the rules. * In any case, the result safely can be used for * {@link org.apache.poi.ss.usermodel.Workbook#setSheetName(int, String)}. * <br> * Rules: * <ul> * <li>never null</li> * <li>minimum length is 1</li> * <li>maximum length is 31</li> * <li>doesn't contain special chars: : 0x0000, 0x0003, / \ ? * ] [ </li> * <li>Sheet names must not begin or end with ' (apostrophe)</li> * </ul> * Invalid characters are replaced by one space character ' '. * * @param nameProposal can be any string, will be truncated if necessary, * allowed to be null * @return a valid string, "empty" if to short, "null" if null */ public static String createSafeSheetName(final String nameProposal) { return createSafeSheetName(nameProposal, ' '); }
/** * Creates a valid sheet name, which is conform to the rules. * In any case, the result safely can be used for * {@link org.apache.poi.ss.usermodel.Workbook#setSheetName(int, String)}. * <br> * Rules: * <ul> * <li>never null</li> * <li>minimum length is 1</li> * <li>maximum length is 31</li> * <li>doesn't contain special chars: : 0x0000, 0x0003, / \ ? * ] [ </li> * <li>Sheet names must not begin or end with ' (apostrophe)</li> * </ul> * Invalid characters are replaced by one space character ' '. * * @param nameProposal can be any string, will be truncated if necessary, * allowed to be null * @return a valid string, "empty" if to short, "null" if null */ public final static String createSafeSheetName(final String nameProposal) { return createSafeSheetName(nameProposal, ' '); }
/** * Creates a valid sheet name, which is conform to the rules. * In any case, the result safely can be used for * {@link org.apache.poi.ss.usermodel.Workbook#setSheetName(int, String)}. * <br> * Rules: * <ul> * <li>never null</li> * <li>minimum length is 1</li> * <li>maximum length is 31</li> * <li>doesn't contain special chars: : 0x0000, 0x0003, / \ ? * ] [ </li> * <li>Sheet names must not begin or end with ' (apostrophe)</li> * </ul> * Invalid characters are replaced by one space character ' '. * * @param nameProposal can be any string, will be truncated if necessary, * allowed to be null * @return a valid string, "empty" if to short, "null" if null */ public final static String createSafeSheetName(final String nameProposal) { return createSafeSheetName(nameProposal, ' '); }
@Override public Editable insertSheet(String name) { workbook.createSheet(WorkbookUtil.createSafeSheetName(name)); return this; }
Sheet createSheet(String name) { Workbook workbook = new HSSFWorkbook(); return workbook.createSheet(createSafeSheetName(name)); }
Sheet createSheet(String name) { Workbook workbook = new XSSFWorkbook(); return workbook.createSheet(createSafeSheetName(name)); }
/** * Create a new sheet with an optional name * * @param sName * The name to be used. May be <code>null</code>. * @return The created workbook sheet */ @Nonnull public Sheet createNewSheet (@Nullable final String sName) { m_aLastSheet = sName == null ? m_aWB.createSheet () : m_aWB.createSheet (WorkbookUtil.createSafeSheetName (sName)); m_nLastSheetRowIndex = 0; m_aLastRow = null; m_nLastRowCellIndex = 0; m_aLastCell = null; m_nMaxCellIndex = 0; return m_aLastSheet; }
/** * @param context * @param workbook * @return */ protected Sheet createSheet(TableRenderContext context, Workbook workbook) { Sheet newSheet = workbook.createSheet(WorkbookUtil .createSafeSheetName(getSheetName(context, sheetIndex))); sheetIndex++; return newSheet; }
private void createSheetMacros( final Workbook wb, final Node node, final ExportPortsProvider exportPortsProvider, final List<Macro> macros) { LOGGER.info("Create sheet for every macro."); for (Macro macro : macros) { String macroName = null; if (StringUtils.isBlank(macro.getName())) { macroName = String.format("M%1$02d : %2$s", macro.getNumber(), ""); } else { macroName = String.format("M%1$02d : %2$s", macro.getNumber(), macro.getName()); } String sheetName = WorkbookUtil.createSafeSheetName(macroName); createSheetMacro(wb, node, exportPortsProvider, sheetName, macro); } }
public static void main(String[] args) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { wb.createSheet("new sheet"); // create with default name wb.createSheet(); final String name = "second sheet"; // setting sheet name later wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name)); try (FileOutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } } } }
private void createSheetAccessories( final Workbook wb, final Node node, final ExportPortsProvider exportPortsProvider, final List<Accessory> accessories) { LOGGER.info("Create accessories sheet"); for (Accessory accessory : accessories) { String accessoryName = null; if (StringUtils.isBlank(accessory.getName())) { // accessoryName = "Accessory_" + accessory.getNumber(); accessoryName = String.format("A%1$02d : %2$s", accessory.getNumber(), ""); } else { accessoryName = String.format("A%1$02d : %2$s", accessory.getNumber(), accessory.getName()); } String sheetName = WorkbookUtil.createSafeSheetName(accessoryName); createSheetAccessory(wb, node, exportPortsProvider, sheetName, accessory); } }
@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(); 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); }
private void createOrUpdateSummarySheet(final Workbook wb, final String version, final int rowStartPosition) { Sheet sheet = wb.getSheet("Summary"); if (sheet == null) { sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("Summary")); final CreationHelper creationHelper = wb.getCreationHelper(); final Font font = wb.createFont(); XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); // Title final Row rowTitle = sheet.createRow(0); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); font.setBold(true); cellStyle.setFont(font); final Cell cellTitle = rowTitle.createCell(4); cellTitle.setCellValue(creationHelper.createRichTextString(getDefaultReportConfig().getTitle())); cellTitle.setCellStyle(cellStyle); sheet.autoSizeColumn(cellTitle.getColumnIndex()); // Row for total of requirements for all versions final Row rowTotalOfReqsForAllVersions = sheet.createRow(3); final Cell cellTotalOfReqs = rowTotalOfReqsForAllVersions.createCell(0); cellTotalOfReqs.setCellValue(creationHelper.createRichTextString("Total of requirements : " + getRequirements().size())); cellTotalOfReqs.getCellStyle().setFont(font); sheet.autoSizeColumn(cellTotalOfReqs.getColumnIndex()); sheet.setZoom(DEFAULT_ZOOM_SCALE); } if (!StringUtils.isBlank(version)) { this.updateSummarySheet(wb, sheet, version, rowStartPosition); } }