List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat
DataFormat createDataFormat();
From source file:onl.netfishers.netshot.RestService.java
License:Open Source License
@GET @Path("reports/export") @RolesAllowed("readonly") @Produces({ "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }) public Response getDataXLSX(@Context HttpServletRequest request, @DefaultValue("-1") @QueryParam("group") long group, @DefaultValue("false") @QueryParam("interfaces") boolean exportInterfaces, @DefaultValue("false") @QueryParam("inventory") boolean exportInventory, @DefaultValue("xlsx") @QueryParam("format") String fileFormat) throws WebApplicationException { logger.debug("REST request, export data."); User user = (User) request.getSession().getAttribute("user"); if (fileFormat.compareToIgnoreCase("xlsx") == 0) { String fileName = String.format("netshot-export_%s.xlsx", (new SimpleDateFormat("yyyyMMdd-HHmmss")).format(new Date())); Session session = Database.getSession(); try {/*from w w w. j a va 2s.c o m*/ Workbook workBook = new XSSFWorkbook(); Row row; Cell cell; CreationHelper createHelper = workBook.getCreationHelper(); CellStyle datetimeCellStyle = workBook.createCellStyle(); datetimeCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); CellStyle dateCellStyle = workBook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd")); Sheet summarySheet = workBook.createSheet("Summary"); row = summarySheet.createRow(0); row.createCell(0).setCellValue("Netshot version"); row.createCell(1).setCellValue(Netshot.VERSION); row = summarySheet.createRow(1); row.createCell(0).setCellValue("Exported by"); row.createCell(1).setCellValue(user.getName()); row = summarySheet.createRow(2); row.createCell(0).setCellValue("Date and time"); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(datetimeCellStyle); row = summarySheet.createRow(4); row.createCell(0).setCellValue("Selected Group"); Query query; if (group == -1) { query = session.createQuery("select d from Device d"); row.createCell(1).setCellValue("None"); } else { query = session.createQuery("select d from Device d join d.ownerGroups g where g.id = :id") .setLong("id", group); DeviceGroup deviceGroup = (DeviceGroup) session.get(DeviceGroup.class, group); row.createCell(1).setCellValue(deviceGroup.getName()); } Sheet deviceSheet = workBook.createSheet("Devices"); row = deviceSheet.createRow(0); row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("Name"); row.createCell(2).setCellValue("Management IP"); row.createCell(3).setCellValue("Domain"); row.createCell(4).setCellValue("Network Class"); row.createCell(5).setCellValue("Family"); row.createCell(6).setCellValue("Creation"); row.createCell(7).setCellValue("Last Change"); row.createCell(8).setCellValue("Software"); row.createCell(9).setCellValue("End of Sale Date"); row.createCell(10).setCellValue("End Of Life Date"); int yDevice = 1; @SuppressWarnings("unchecked") List<Device> devices = query.list(); for (Device device : devices) { row = deviceSheet.createRow(yDevice++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(device.getName()); row.createCell(2).setCellValue(device.getMgmtAddress().getIp()); row.createCell(3).setCellValue(device.getMgmtDomain().getName()); row.createCell(4).setCellValue(device.getNetworkClass().toString()); row.createCell(5).setCellValue(device.getFamily()); cell = row.createCell(6); cell.setCellValue(device.getCreatedDate()); cell.setCellStyle(datetimeCellStyle); cell = row.createCell(7); cell.setCellValue(device.getChangeDate()); cell.setCellStyle(datetimeCellStyle); row.createCell(8).setCellValue(device.getSoftwareVersion()); if (device.getEosDate() != null) { cell = row.createCell(9); cell.setCellValue(device.getEosDate()); cell.setCellStyle(dateCellStyle); } if (device.getEolDate() != null) { cell = row.createCell(10); cell.setCellValue(device.getEolDate()); cell.setCellStyle(dateCellStyle); } } if (exportInterfaces) { Sheet interfaceSheet = workBook.createSheet("Interfaces"); row = interfaceSheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Virtual Device"); row.createCell(2).setCellValue("Name"); row.createCell(3).setCellValue("Description"); row.createCell(4).setCellValue("VRF"); row.createCell(5).setCellValue("MAC Address"); row.createCell(6).setCellValue("Enabled"); row.createCell(7).setCellValue("Level 3"); row.createCell(8).setCellValue("IP Address"); row.createCell(9).setCellValue("Mask Length"); row.createCell(10).setCellValue("Usage"); int yInterface = 1; for (Device device : devices) { for (NetworkInterface networkInterface : device.getNetworkInterfaces()) { if (networkInterface.getIpAddresses().size() == 0) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(""); row.createCell(9).setCellValue(""); row.createCell(10).setCellValue(""); } for (NetworkAddress address : networkInterface.getIpAddresses()) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(address.getIp()); row.createCell(9).setCellValue(address.getPrefixLength()); row.createCell(10).setCellValue(address.getAddressUsage() == null ? "" : address.getAddressUsage().toString()); } } } } if (exportInventory) { Sheet inventorySheet = workBook.createSheet("Inventory"); row = inventorySheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Slot"); row.createCell(2).setCellValue("Part Number"); row.createCell(3).setCellValue("Serial Number"); int yInventory = 1; for (Device device : devices) { for (Module module : device.getModules()) { row = inventorySheet.createRow(yInventory++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(module.getSlot()); row.createCell(2).setCellValue(module.getPartNumber()); row.createCell(3).setCellValue(module.getSerialNumber()); } } } ByteArrayOutputStream output = new ByteArrayOutputStream(); workBook.write(output); workBook.close(); return Response.ok(output.toByteArray()) .header("Content-Disposition", "attachment; filename=" + fileName).build(); } catch (IOException e) { logger.error("Unable to write the resulting file.", e); throw new WebApplicationException("Unable to write the resulting file.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } catch (Exception e) { logger.error("Unable to generate the report.", e); throw new WebApplicationException("Unable to generate the report.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } finally { session.close(); } } logger.warn("Invalid requested file format."); throw new WebApplicationException("The requested file format is invalid or not supported.", javax.ws.rs.core.Response.Status.BAD_REQUEST); }
From source file:opn.greenwebs.FXMLDocumentController.java
private void inject(XSSFWorkbook wb, Object obj, int row, int col) { if (wb == null) { System.out.println("wb is null"); }/*from w w w. j a v a 2 s . c om*/ XSSFSheet sheet = wb.getSheet("Digital Version"); Row rowed = sheet.getRow(row); Cell cell = rowed.getCell(col); CellStyle cellStyle = cell.getCellStyle(); XSSFFont font = sheet.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } cell.setCellStyle(cellStyle); }
From source file:opn.greenwebs.FXMLDocumentController.java
private void injectStock(XSSFWorkbook wbs, Object obj, int row, int col) { Row rowed = wbs.getSheet("Digital Version").getRow(row); Cell cell = rowed.getCell(col);//w w w. j a v a2s. c o m if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CellStyle cellStyle = wbs.getCellStyleAt(col); CreationHelper createHelper = wbs.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); cell.setCellStyle(cellStyle); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);/* w ww. ja v a2s .c o m*/ final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * @see AbstractPOIExcelView#buildExcelDocument(Map, Workbook, HttpServletRequest, HttpServletResponse) *//*from w w w . j a v a 2s. c om*/ @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { log.debug("Generating Excel report from run samples"); // Set the headers response.setHeader("Content-Type", "application/octet-stream"); response.setHeader("Content-Disposition", "attachment; filename=central_perf_result.xlsx"); // get data model which is passed by the Spring container Run run = (Run) model.get("run"); // Set run summary informations setCellValueByName(PROJECT_NAME_CELL_NAME, run.getProject().getName(), workbook); setCellValueByName(RUN_LABEL_CELL_NAME, run.getLabel(), workbook); setCellValueByName(RUN_DESCRIPTION_CELL_NAME, run.getComment(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(GENERATED_ON_CELL_NAME, "" + unixTimestamp2ExcelTimestampconvert(new Date().getTime()), workbook); // Populate data sheet XSSFSheet dataSheet = (XSSFSheet) workbook.getSheet(DATA_SHEET_NAME); // Set date style for first column CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd")); dataSheet.setDefaultColumnStyle(0, dateStyle); // Add samples for (int i = 0; i < run.getSamples().size(); i++) { Sample sample = run.getSamples().get(i); XSSFRow dataRow = dataSheet.createRow(i + 1); if (sample.getTimestamp() != null) { dataRow.createCell(0) .setCellValue(unixTimestamp2ExcelTimestampconvert(sample.getTimestamp().getTime())); dataRow.createCell(1).setCellValue(sample.getElapsed()); dataRow.createCell(2).setCellValue(sample.getSampleName()); dataRow.createCell(3).setCellValue(sample.getStatus()); dataRow.createCell(4).setCellValue(sample.getReturnCode()); dataRow.createCell(5).setCellValue(sample.getSizeInOctet()); dataRow.createCell(6).setCellValue(sample.getGrpThreads()); dataRow.createCell(7).setCellValue(sample.getAllThreads()); dataRow.createCell(8).setCellValue(sample.getLatency()); } } // Return generated sheet OutputStream outStream = null; try { outStream = response.getOutputStream(); workbook.write(outStream); outStream.flush(); } finally { outStream.close(); } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * Method used to initialize the different styles according to the type of value *//*from w ww. j a v a 2 s.c o m*/ private void initializeStyles(int[] columnTypes) { // Style header styleHeader = (XSSFCellStyle) workbook.createCellStyle(); styleHeader.setAlignment(CellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleHeader.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); styleHeader.setFillForegroundColor(new XSSFColor(Color.decode(HEADER_BG_COLOR_HEX))); styleHeader.setWrapText(true); // Font XSSFFont font = (XSSFFont) workbook.createFont(); font.setBold(true); font.setFontName(HEADER_FONT_NAME); font.setColor(new XSSFColor(Color.decode(HEADER_FONT_COLOR_HEX))); font.setFontHeightInPoints(HEADER_FONT_SIZE); styleHeader.setFont(font); richTextFont = workbook.createFont(); richTextFont.setFontName("Tahoma"); richTextFont.setBold(true); richTextFont.setColor(HSSFColor.RED.index); // border this.setBottomBorderCell(styleHeader, Color.decode(HEADER_BORDER_COLOR_HEX)); CreationHelper createHelper = workbook.getCreationHelper(); columnStyles = new XSSFCellStyle[columnTypes.length]; for (int c = 0; c < columnTypes.length; c++) { columnStyles[c] = (XSSFCellStyle) workbook.createCellStyle(); switch (columnTypes[c]) { // Style numeric case COLUMN_TYPE_NUMERIC: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style date case COLUMN_TYPE_DATE: columnStyles[c].setDataFormat(createHelper.createDataFormat().getFormat(CELL_DATE_FORMAT)); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // styleBoleean case COLUMN_TYPE_BOOLEAN: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("#.##")); break; // styleBudget case COLUMN_TYPE_BUDGET: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00")); // "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)" break; // Style decimal case COLUMN_TYPE_DECIMAL: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("#.##")); break; // Style long string case COLUMN_TYPE_TEXT_LONG: columnStyles[c].setAlignment(HorizontalAlignment.LEFT); columnStyles[c].setWrapText(true); break; // Style short string case COLUMN_TYPE_TEXT_SHORT: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style hyperlink case COLUMN_TYPE_HYPERLINK: XSSFFont hlinkfont = (XSSFFont) workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); columnStyles[c].setFont(hlinkfont); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style hyperlink case COLUMN_TYPE_DATE_TIME: columnStyles[c].setDataFormat(createHelper.createDataFormat().getFormat(CELL_DATE_TIME_FORMAT)); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; } this.setBottomBorderCell(columnStyles[c], Color.decode(CELL_BORDER_COLOR_HEX)); if (c == 0) { columnStyles[c].setBorderLeft(CELL_BORDER_TYPE_LEFT); columnStyles[c].setBorderColor(BorderSide.LEFT, new XSSFColor(Color.decode(CELL_BORDER_COLOR_HEX))); } else if (c == columnTypes.length - 1) { columnStyles[c].setBorderRight(CELL_BORDER_TYPE_RIGHT); columnStyles[c].setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.decode(CELL_BORDER_COLOR_HEX))); } } }
From source file:org.codelabor.example.poi.xssf.usermodel.XSSFWorkbookTest.java
License:Apache License
@Test public void testSetCellStyle() { String path = "C:/temp/workbook4.xlsx"; OutputStream outputStream = null; try {//from w w w . j av a 2 s . c o m XSSFWorkbook workbook = new XSSFWorkbook(); Sheet sheet1 = workbook.createSheet("Sheet1"); CreationHelper creationHelper = workbook.getCreationHelper(); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); Row row = sheet1.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(new Date()); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); outputStream = new FileOutputStream(path); workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); fail(); } finally { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:org.eclipse.kapua.app.console.servlet.DeviceExporterExcel.java
License:Open Source License
@Override public void init(String account) throws ServletException, IOException { m_account = account;// ww w .java2s . co m // workbook m_workbook = new HSSFWorkbook(); m_sheet = m_workbook.createSheet(); CreationHelper createHelper = m_workbook.getCreationHelper(); m_dateStyle = m_workbook.createCellStyle(); m_dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy hh:mm:ss.0")); // headers m_rowCount = 0; Row row = m_sheet.createRow(m_rowCount++); int iColCount = 0; for (String property : s_deviceProperties) { m_sheet.setColumnWidth(iColCount, 18 * 256); row.createCell(iColCount++).setCellValue(truncate(property)); } }
From source file:org.eclipse.nebula.widgets.nattable.extension.poi.PoiExcelExporter.java
License:Open Source License
private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData, String dataFormat, int hAlign, int vAlign, boolean vertical) { CellStyle xlCellStyle = xlCellStyles .get(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical)); if (xlCellStyle == null) { xlCellStyle = xlWorkbook.createCellStyle(); if (applyBackgroundColor) { // Note: xl fill foreground = background setFillForegroundColor(xlCellStyle, bg); xlCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); }// w ww .j av a2s . co m Font xlFont = xlWorkbook.createFont(); setFontColor(xlFont, fg); xlFont.setFontName(fontData.getName()); xlFont.setFontHeightInPoints((short) fontData.getHeight()); xlCellStyle.setFont(xlFont); if (vertical) xlCellStyle.setRotation((short) 90); switch (hAlign) { case SWT.CENTER: xlCellStyle.setAlignment(CellStyle.ALIGN_CENTER); break; case SWT.LEFT: xlCellStyle.setAlignment(CellStyle.ALIGN_LEFT); break; case SWT.RIGHT: xlCellStyle.setAlignment(CellStyle.ALIGN_RIGHT); break; } switch (vAlign) { case SWT.TOP: xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP); break; case SWT.CENTER: xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); break; case SWT.BOTTOM: xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); break; } if (dataFormat != null) { CreationHelper createHelper = xlWorkbook.getCreationHelper(); xlCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dataFormat)); } xlCellStyles.put(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical), xlCellStyle); } return xlCellStyle; }
From source file:org.fhaes.gui.AnalysisResultsPanel.java
License:Open Source License
/** * Save the current analyses results to a multi-tabbed Excel file * /* w ww .j a v a 2s . co m*/ * @param outputfile */ public void saveXLSXOfResults(File outputfile) { Workbook workbook = new XSSFWorkbook(); CreationHelper createHelper = workbook.getCreationHelper(); doubleStyle = workbook.createCellStyle(); doubleStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.000")); writeParametersToXLSXSheet(workbook.createSheet("Parameters")); writeModelToXLSXSheet(workbook.createSheet("General Summary"), generalSummaryModel); writeModelToXLSXSheet(workbook.createSheet("Interval Summary"), intervalsSummaryModel); writeModelToXLSXSheet(workbook.createSheet("Interval Exceedence"), intervalsExceedenceModel); writeModelToXLSXSheet(workbook.createSheet("Seasonality Summary"), seasonalitySummaryModel); writeModelToXLSXSheet(workbook.createSheet("Binary Site Summary"), siteSummaryModel); writeModelToXLSXSheet(workbook.createSheet("Binary Tree Summary"), treeSummaryModel); writeModelToXLSXSheet(workbook.createSheet("NTP Matrix"), NTPModel); writeModelToXLSXSheet(workbook.createSheet("COHEN Dissimilarity"), DSCOHModel); writeModelToXLSXSheet(workbook.createSheet("JACCARD Dissimilarity"), DSJACModel); writeModelToXLSXSheet(workbook.createSheet("COHEN Similarity"), SCOHModel); writeModelToXLSXSheet(workbook.createSheet("JACCARD Similarity"), SJACModel); writeModelToXLSXSheet(workbook.createSheet("Matrix A (1-1)"), bin11Model); writeModelToXLSXSheet(workbook.createSheet("Matrix B (0-1)"), bin01Model); writeModelToXLSXSheet(workbook.createSheet("Matrix C (1-0)"), bin10Model); writeModelToXLSXSheet(workbook.createSheet("Matrix D (0-0)"), bin00Model); writeModelToXLSXSheet(workbook.createSheet("Matrix L (Sum)"), binSumModel); writeModelToXLSXSheet(workbook.createSheet("Single File Summary"), singleFileSummaryModel); writeModelToXLSXSheet(workbook.createSheet("Single File Event Summary"), singleEventSummaryModel); OutputStream os = IOUtils.createOutput(outputfile); try { workbook.write(os); } catch (IOException e) { e.printStackTrace(); } finally { try { os.close(); } catch (IOException e) { e.printStackTrace(); } } }