List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName
public static String createSafeSheetName(final String nameProposal)
From source file:fi.thl.pivot.export.XlsxExporter.java
private void doExport(Model model, OutputStream out) throws IOException { Map<String, ?> params = model.asMap(); Workbook wb = new XSSFWorkbook(); createExportStyles(wb);/*from www. j a v a 2s .c om*/ Sheet sheet = wb.createSheet( WorkbookUtil.createSafeSheetName(((Label) params.get("cubeLabel")).getValue(language))); Pivot pivot = (Pivot) params.get("pivot"); int rowNumber = 0; boolean showCodes = params.containsKey("sc"); rowNumber = createColumnHeaders(pivot, sheet, showCodes); rowNumber = printData(sheet, pivot, rowNumber, showCodes); mergeRowHeaders(sheet, pivot); rowNumber = printFilters(params, sheet, rowNumber, pivot.getColumnCount() + pivot.getColumns().size()); printCopyrightNotice(sheet, rowNumber, params, pivot.getColumnCount() + pivot.getColumns().size()); printCurrentMeasureIfOnlyOneMeasureShown(params, sheet, pivot); mergeTopLeftCorner(sheet, pivot); autosizeColumns(sheet, pivot); sheet.createFreezePane(pivot.getRows().size(), pivot.getColumns().size()); wb.write(out); wb.close(); }
From source file:fr.amapj.service.engine.generator.excel.ExcelGeneratorTool.java
License:Open Source License
/** * Permet de crer une feuille, en indiquant le nombre de colonnes * et la taille de chaque colonne en caractres * /*from w w w . ja v a 2s . c om*/ * * @param sheetName * @param nbCol * @param colWidth * @return */ public void addSheet(String sheetName, int nbCol, int colWidth) { sheetName = WorkbookUtil.createSafeSheetName(sheetName); sheet = wb.createSheet(sheetName); currentRow = null; firstLine = true; this.nbColMax = nbCol; for (int i = 0; i < nbCol; i++) { // setColumnWidth(i, colWidth); // Style par defaut pour toutes les colonnes sheet.setDefaultColumnStyle(i, grasGaucheNonWrappe); } // Les marges par dfaut sont 1 cm de chaque ct setMargin(10, 10, 10, 10); // On est systmatiquement en mode portrait et en A4 sheet.getPrintSetup().setLandscape(false); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); }
From source file:htmlparser.xls.XLSFile.java
public void createScoreTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color, int highlight) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getCompetitionName()); this.scoresheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.scoresheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getCompetitionName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);/* w w w.j a va 2 s.co m*/ customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getTeams().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length); this.scoresheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); rows++; Row colNms = this.scoresheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.shColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } double ordNum = 1; customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Team t : this.parser.getTeams()) { Row r = this.scoresheet.createRow(rows++); int cell = 0; Cell order = r.createCell(cell++); order.setCellValue(ordNum++); if (rows % 2 == 0) order.setCellStyle(customstyle); else order.setCellStyle(cellStyle); for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i <= length; i++) { this.scoresheet.autoSizeColumn(i); } if (highlight >= 0) { highlight += 2; Row r = this.scoresheet.getRow(highlight); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(r.getCell(0).getCellStyle()); Font bold = this.excelfile.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(bold); for (Cell c : r) { c.setCellStyle(customstyle); } } }
From source file:htmlparser.xls.XLSFile.java
public void createMatchTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getTeamName()); this.matchsheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.matchsheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getTeamName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);/*from w ww.j a va 2 s . c o m*/ customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getMatches().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length - 1); this.matchsheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); rows++; Row colNms = this.matchsheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.mhColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Match t : this.parser.getMatches()) { Row r = this.matchsheet.createRow(rows++); int cell = 0; for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i < length; i++) { this.matchsheet.autoSizeColumn(i); } }
From source file:net.rrm.ehour.ui.common.report.AbstractExcelReport.java
License:Open Source License
/** * Create the workbook/*from w w w . ja va 2 s. c o m*/ */ protected ExcelWorkbook createWorkbook(Report treeReport) { ExcelWorkbook wb = new ExcelWorkbook(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(getExcelReportName().getObject())); int rowNumber = 0; short column; for (column = 0; column < 4; column++) { sheet.setColumnWidth(column, 5000); } for (; column < 7; column++) { sheet.setColumnWidth(column, 3000); } rowNumber = createHeaders(rowNumber, sheet, treeReport, wb); rowNumber = addColumnHeaders(rowNumber, sheet, wb); fillReportSheet(treeReport, sheet, rowNumber, wb); return wb; }
From source file:net.rrm.ehour.ui.timesheet.export.TimesheetExcelExport.java
License:Open Source License
private ExcelWorkbook createWorkbook(Report report) { ExcelWorkbook workbook = new ExcelWorkbook(); String sheetName = WebUtils.formatDate("MMMM yyyy", report.getReportRange().getDateStart()); Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName)); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.DATE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.CUSTOMER_CODE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT_CODE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.HOURS.getColumn())); sheet.setColumnWidth(0, 1024);/*from w w w . j a v a2 s. c o m*/ int rowNumber = 9; rowNumber = new ExportReportHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportBodyHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportBody(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportTotal(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); if (isInclSignOff(report)) { new ExportReportSignOff(CELL_BORDER, sheet, report, workbook).createPart(rowNumber + 1); } return workbook; }
From source file:org.activityinfo.server.endpoint.export.SiteExporter.java
License:Open Source License
private String composeUniqueSheetName(ActivityFormDTO activity) { String sheetName = activity.getDatabaseName() + " - " + activity.getName(); // to avoid conflict with our own disambiguation scheme, remove any trailing "(n)" // from sheet names sheetName = sheetName.replaceFirst("\\((\\d+)\\)$", "$1"); // shorten and translate the name to meet excel requirements String safeName = WorkbookUtil.createSafeSheetName(sheetName); // assure that the sheet name is unique if (!sheetNames.containsKey(safeName)) { sheetNames.put(safeName, 1);//from w w w. ja v a 2 s. c o m return safeName; } else { int index = sheetNames.get(safeName) + 1; sheetNames.put(safeName, index); String disambiguatedNamed = safeName + " (" + index + ")"; if (disambiguatedNamed.length() > MAX_WORKSHEET_LENGTH) { int toTrim = disambiguatedNamed.length() - MAX_WORKSHEET_LENGTH; disambiguatedNamed = safeName.substring(0, safeName.length() - toTrim) + " (" + index + ")"; } return disambiguatedNamed; } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();//from w ww .j a v a 2s. co m report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.categorization.EMFFormsCategoryRenderer.java
License:Open Source License
private String getSheetName(Workbook workbook, VCategory vElement) { for (final VAttachment vAttachment : vElement.getAttachments()) { if (VAnnotation.class.isInstance(vAttachment) && SHEET_NAME_KEY.equals(VAnnotation.class.cast(vAttachment).getKey())) { return VAnnotation.class.cast(vAttachment).getValue(); }/*from w w w . j av a 2s . co m*/ } final VAnnotation annotation = VAnnotationFactory.eINSTANCE.createAnnotation(); annotation.setKey(SHEET_NAME_KEY); final String sheetName = WorkbookUtil .createSafeSheetName(workbook.getNumberOfSheets() + 1 + " " + vElement.getLabel()); //$NON-NLS-1$ annotation.setValue(sheetName); vElement.getAttachments().add(annotation); return annotation.getValue(); }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.categorization.SpreadsheetCategorization_Test.java
License:Open Source License
@Test public void testEMFFormsCategoryRenderer() throws EMFFormsNoRendererException { final Workbook workbook = new HSSFWorkbook(); final EMFFormsSpreadsheetRendererFactory rendererFactory = Mockito .mock(EMFFormsSpreadsheetRendererFactory.class); final ReportService reportService = Mockito.mock(ReportService.class); final EMFFormsCategoryRenderer renderer = new EMFFormsCategoryRenderer(rendererFactory, reportService); final ViewModelContext viewModelContext = new EMFFormsSpreadsheetViewModelContext( VViewFactory.eINSTANCE.createView(), null); final EMFFormsSpreadsheetRenderTarget renderTarget = new EMFFormsSpreadsheetRenderTarget("root", 0, 0); //$NON-NLS-1$ final VCategory category = VCategorizationFactory.eINSTANCE.createCategory(); final VControl vElement = VViewFactory.eINSTANCE.createControl(); category.setComposite(vElement);/*from w ww .j a va 2 s . c o m*/ final EMFFormsAbstractSpreadsheetRenderer<VElement> categoryRenderer = Mockito .mock(EMFFormsAbstractSpreadsheetRenderer.class); Mockito.when(rendererFactory.getRendererInstance(vElement, viewModelContext)).thenReturn(categoryRenderer); renderer.render(workbook, category, viewModelContext, renderTarget); Mockito.verify(rendererFactory).getRendererInstance(vElement, viewModelContext); Mockito.verify(categoryRenderer).render(Matchers.same(workbook), Matchers.same(vElement), Matchers.same(viewModelContext), Matchers.argThat(new BaseMatcher<EMFFormsSpreadsheetRenderTarget>() { @Override public boolean matches(Object item) { if (!EMFFormsSpreadsheetRenderTarget.class.isInstance(item)) { return false; } final EMFFormsSpreadsheetRenderTarget target = EMFFormsSpreadsheetRenderTarget.class .cast(item); if (0 != target.getColumn()) { return false; } if (0 != target.getRow()) { return false; } final String expected = WorkbookUtil .createSafeSheetName(workbook.getNumberOfSheets() + 1 + " " + vElement.getLabel()); //$NON-NLS-1$ if (!expected.equals(target.getSheetName())) { return false; } return true; } @Override public void describeTo(Description description) { } })); }