List of usage examples for org.apache.poi.ss.usermodel DataFormat getFormat
String getFormat(short index);
From source file:ro.ldir.report.formatter.GarbageExcelFormatter.java
License:Open Source License
public final Workbook convert(Workbook wb) { Sheet sheet = wb.createSheet("Lista Mormane gunoi"); Row row = sheet.createRow(0);//from ww w . j a v a2 s . c o m int k = 0; row.createCell(k).setCellValue("ID"); k++; row.createCell(k).setCellValue("Jude\u0163"); k++; row.createCell(k).setCellValue("Comun\u04d1"); k++; row.createCell(k).setCellValue("Latitudine"); k++; row.createCell(k).setCellValue("Longitudine"); k++; row.createCell(k).setCellValue("Precizie GPS (metri)"); k++; row.createCell(k).setCellValue("Dispersat"); k++; row.createCell(k).setCellValue("Num\u04d1r saci"); k++; row.createCell(k).setCellValue("Marime TrashOut (1=mic;2=medium;3=mare)"); k++; row.createCell(k).setCellValue("Compozitie TrashOut"); k++; row.createCell(k).setCellValue("Plastic"); k++; row.createCell(k).setCellValue("Metal"); k++; row.createCell(k).setCellValue("Sticl\u04d1"); k++; row.createCell(k).setCellValue("Nereciclabil"); k++; row.createCell(k).setCellValue("Greu de transportat"); k++; row.createCell(k).setCellValue("Descriere"); k++; row.createCell(k).setCellValue("Stare"); k++; row.createCell(k).setCellValue("Zon\u04d1 cartare"); k++; row.createCell(k).setCellValue("Numele mormanului"); k++; row.createCell(k).setCellValue("Raza"); k++; row.createCell(k).setCellValue("Numar de voturi"); k++; row.createCell(k).setCellValue("Data introducerii"); k++; row.createCell(k).setCellValue("Nominalizat pentru Votare"); k++; row.createCell(k).setCellValue("Nominalizat pentru Curatare"); k++; for (int i = 0; i < garbages.size(); i++) { k = 0; row = sheet.createRow(i + 1); Garbage garbage = garbages.get(i); row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getGarbageId()); k++; row.createCell(k).setCellValue(garbage.getCounty().getName()); k++; if (garbage.getTown() != null) row.createCell(k).setCellValue(garbage.getTown().getName()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getY()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getX()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getAccuracy()); k++; row.createCell(k, Cell.CELL_TYPE_BOOLEAN).setCellValue(garbage.isDispersed()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getBagCount()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getTrashOutSize()); k++; if (garbage.getTrashOutTypes() != null) row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.getTrashOutTypes()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentagePlastic()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageMetal()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageGlass()); k++; row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageWaste()); k++; row.createCell(k).setCellValue(garbage.getBigComponentsDescription()); k++; if (garbage.getDescription() != null) row.createCell(k, Cell.CELL_TYPE_STRING) .setCellValue(garbage.getDescription().replaceAll("\\r\\n|\\r|\\n", " ")); k++; if (garbage.getStatus() != null) row.createCell(k).setCellValue(garbage.getStatus().getTranslation()); k++; if (garbage.getChartedArea() != null) row.createCell(k).setCellValue(garbage.getChartedArea().getName()); k++; try { if (garbage.getName() != null) row.createCell(k).setCellValue(garbage.getName()); } catch (Exception e) { // TODO: handle exception } k++; try { row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getRadius()); } catch (Exception e1) { // TODO: handle exception } k++; try { if (garbage.getVotes() != null) row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getVoteCount()); } catch (Exception ee) { } k++; try { Cell cell; DataFormat df = wb.createDataFormat(); CellStyle cs = wb.createCellStyle(); cs.setDataFormat(df.getFormat("dd-mm-yyyy")); if (garbage.getRecordDate() != null) { cell = row.createCell(k, Cell.CELL_TYPE_STRING); cell.setCellValue(garbage.getRecordDate()); cell.setCellStyle(cs); } } catch (Exception ee) { } k++; row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.isToVote()); k++; row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.isToClean()); k++; } return wb; }
From source file:stroom.dashboard.server.download.ExcelTarget.java
License:Apache License
private void dateTime(final SXSSFWorkbook wb, final Cell cell, final Object value, final FormatSettings settings) { if (value instanceof Double) { final long ms = ((Double) value).longValue(); final Date date = new Date(ms); cell.setCellValue(date);//ww w .j a v a2 s .com cell.setCellType(Cell.CELL_TYPE_NUMERIC); String pattern = "dd/mm/yyyy hh:mm:ss"; if (settings != null && settings instanceof DateTimeFormatSettings) { final DateTimeFormatSettings dateTimeFormatSettings = (DateTimeFormatSettings) settings; if (dateTimeFormatSettings.getPattern() != null && dateTimeFormatSettings.getPattern().trim().length() > 0) { pattern = dateTimeFormatSettings.getPattern(); pattern = pattern.replaceAll("'", ""); pattern = pattern.replaceAll("\\.SSS", ""); } } final DataFormat df = wb.createDataFormat(); final CellStyle cs = wb.createCellStyle(); cs.setDataFormat(df.getFormat(pattern)); cell.setCellStyle(cs); } else { cell.setCellValue(getText(value)); } }
From source file:stroom.dashboard.server.download.ExcelTarget.java
License:Apache License
private void number(final SXSSFWorkbook wb, final Cell cell, final Object value, final FormatSettings settings) { if (value instanceof Double) { final double dbl = ((Double) value).doubleValue(); cell.setCellValue(dbl);/*w w w. j a v a 2 s . co m*/ cell.setCellType(Cell.CELL_TYPE_NUMERIC); if (settings != null && settings instanceof NumberFormatSettings) { final NumberFormatSettings numberFormatSettings = (NumberFormatSettings) settings; final StringBuilder sb = new StringBuilder(); if (Boolean.TRUE.equals(numberFormatSettings.getUseSeparator())) { sb.append("#,##0"); } else { sb.append("#"); } if (numberFormatSettings.getDecimalPlaces() != null && numberFormatSettings.getDecimalPlaces() > 0) { sb.append("."); for (int i = 0; i < numberFormatSettings.getDecimalPlaces(); i++) { sb.append("0"); } } final String pattern = sb.toString(); final DataFormat df = wb.createDataFormat(); final CellStyle cs = wb.createCellStyle(); cs.setDataFormat(df.getFormat(pattern)); cell.setCellStyle(cs); } } else { cell.setCellValue(getText(value)); } }
From source file:summary.GenotypeSummary.java
License:LGPL
public void writeToWorkbook(Workbook wb) { Sheet sheet = getSheet(wb);//from w ww . java 2 s . co m Row header = sheet.createRow(0); header.createCell(0).setCellValue("Metabolizer Group based on Genotype Only"); header.createCell(1).setCellValue("Weak"); header.createCell(2).setCellValue("Potent"); header.createCell(3).setCellValue("Count"); int rowNum = 1; for (String key : countMap.keySet()) { String[] fields = key.split("\\|"); Row data = sheet.createRow(rowNum); data.createCell(0).setCellValue(fields[0]); data.createCell(1).setCellValue(fields[1]); data.createCell(2).setCellValue(fields[2]); data.createCell(3).setCellValue(countMap.get(key)); rowNum++; } // Tumor source table Row row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("*4 Status by Sample Source"); row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Source"); row.createCell(1).setCellValue("Count"); row.createCell(2).setCellValue("*4 Homozygous"); row.createCell(3).setCellValue("*4 Heterozygous"); row.createCell(4).setCellValue("Non-*4"); for (Subject.SampleSource source : Subject.SampleSource.values()) { row = sheet.createRow(++rowNum); row.createCell(0).setCellValue(source.toString()); row.createCell(1).setCellValue(sourceMap.get(source)[fourTotal]); row.createCell(2).setCellValue(sourceMap.get(source)[fourHomo]); row.createCell(3).setCellValue(sourceMap.get(source)[fourHeto]); row.createCell(4).setCellValue(sourceMap.get(source)[fourNon]); } rowNum++; row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Sample Source by Site"); row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Site"); int colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { row.createCell(colMarker * 2 + 1).setCellValue(source.name() + " N"); row.createCell(colMarker * 2 + 2).setCellValue(source.name() + " %"); colMarker++; } int[] totals = new int[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; CellStyle pctStyle = sheet.getWorkbook().createCellStyle(); DataFormat format = sheet.getWorkbook().createDataFormat(); pctStyle.setDataFormat(format.getFormat("0.0%")); for (Integer i : tumorFreqMap.keySet()) { row = sheet.createRow(++rowNum); Integer siteTotal = tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FFP.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FROZEN.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.BLOOD.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.BUCCAL.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.UNKNOWN.ordinal()]; Cell cell; row.createCell(0).setCellValue(i + 1); colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { Integer total = tumorFreqMap.get(i)[source.ordinal()]; Float pct = (float) tumorFreqMap.get(i)[source.ordinal()] / (float) siteTotal; row.createCell(colMarker * 2 + 1).setCellValue(total); cell = row.createCell(colMarker * 2 + 2); cell.setCellValue(pct); cell.setCellStyle(pctStyle); totals[source.ordinal()] += total; colMarker++; } } row = sheet.createRow(++rowNum); int projectTotal = totals[Subject.SampleSource.TUMOR_FFP.ordinal()] + totals[Subject.SampleSource.TUMOR_FROZEN.ordinal()] + totals[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()] + totals[Subject.SampleSource.BLOOD.ordinal()] + totals[Subject.SampleSource.BUCCAL.ordinal()] + totals[Subject.SampleSource.UNKNOWN.ordinal()]; colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { row.createCell(colMarker * 2 + 1).setCellValue(totals[source.ordinal()]); Cell cell = row.createCell(colMarker * 2 + 2); cell.setCellValue((float) totals[source.ordinal()] / (float) projectTotal); cell.setCellStyle(pctStyle); colMarker++; } }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerUtils.java
License:Open Source License
/** * Apply a BIRT number/date/time format to a POI CellStyle. * @param workbook/* w w w .j a v a 2s. com*/ * The workbook containing the CellStyle (needed to create a new DataFormat). * @param birtStyle * The BIRT style which may contain a number format. * @param poiStyle * The CellStyle that is to receive the number format. */ public void applyNumberFormat(Workbook workbook, BirtStyle birtStyle, CellStyle poiStyle, Locale locale) { String dataFormat = null; String format = getNumberFormat(birtStyle); if (format != null) { log.debug("BIRT number format == ", format); dataFormat = poiNumberFormatFromBirt(format); } else { format = getDateTimeFormat(birtStyle); if (format != null) { log.debug("BIRT date/time format == ", format); dataFormat = poiDateTimeFormatFromBirt(format, locale); } else { format = getTimeFormat(birtStyle); if (format != null) { log.debug("BIRT time format == ", format); dataFormat = poiDateTimeFormatFromBirt(format, locale); } else { format = getDateFormat(birtStyle); if (format != null) { log.debug("BIRT date format == ", format); dataFormat = poiDateTimeFormatFromBirt(format, locale); } } } } if (dataFormat != null) { DataFormat poiFormat = workbook.createDataFormat(); log.debug("Setting POI data format to ", dataFormat); poiStyle.setDataFormat(poiFormat.getFormat(dataFormat)); } }
From source file:uk.gov.ofwat.fountain.api.report.POIReportWriter.java
License:Open Source License
private short cellFormat(DataFormat format, DataDto dataDto) { String formatString = "#,##0"; // default format short formatCode = format.getFormat(formatString); if (null == dataDto) { return formatCode; // default }/*from w w w . j ava 2s . c o m*/ if (dataDto.getItemPropertiesDto().getDecimalPlaces() > 0) { formatString = formatString + "."; for (int i = 1; i <= dataDto.getItemPropertiesDto().getDecimalPlaces(); i++) { formatString = formatString + "0"; } formatCode = format.getFormat(formatString); } if (dataDto.getItem().getUnit().equals("%")) { formatCode = format.getFormat("0.00%"); } return formatCode; }
From source file:uk.gov.ofwat.RefTest.java
License:Open Source License
public void writeXLS() throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); // create a new sheet Sheet s = wb.createSheet();//www . ja v a 2 s .c om // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 2 cell styles XSSFCellStyle cs = wb.createCellStyle(); XSSFCellStyle cs2 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); // Set font 1 to 12 point type, blue and bold f.setFontHeightInPoints((short) 12); f.setColor(IndexedColors.RED.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set font 2 to 10 point type, red and bold f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.RED.getIndex()); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set cell style and formatting cs.setFont(f); cs.setDataFormat(df.getFormat("#,##0.0")); // Set the other cell style and formatting cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); cs2.setFont(f2); // Define a few rows for (int rownum = 0; rownum < 30; rownum++) { r = s.createRow(rownum); for (int cellnum = 0; cellnum < 10; cellnum += 2) { c = r.createCell(cellnum); Cell c2 = r.createCell(cellnum + 1); c.setCellValue((double) rownum + (cellnum / 10)); c2.setCellValue(creationHelper.createRichTextString("Hello! " + cellnum)); } } File file = new File("d:\\out.xls"); FileOutputStream fos = new FileOutputStream(file); wb.write(fos); // fos.write(wb.getBytes()); // fos.flush(); // fos.close(); }
From source file:Utilities.ExportToXLSX.java
private CellStyle createDateStyle() { DataFormat df = wb.createDataFormat(); CellStyle style = createStandardStyle(); style.setDataFormat(df.getFormat("yyMMdd")); style.setAlignment(CellStyle.ALIGN_LEFT); return style; }
From source file:Utilities.ExportToXLSX.java
private CellStyle createSSNStyle() { Font font3 = wb.createFont(); DataFormat df = wb.createDataFormat(); CellStyle style = wb.createCellStyle(); style = createStandardStyle();//www . j a va 2 s . c o m style.setDataFormat(df.getFormat("0000")); style.setAlignment(CellStyle.ALIGN_LEFT); //style.setFont(font3); style.setWrapText(true); return style; }
From source file:utilities.XLSTaskManager.java
License:Open Source License
private void processTaskListForXLS(TaskListGeoJson tl, Sheet sheet, Sheet settingsSheet, Map<String, CellStyle> styles, ArrayList<Column> cols, String tz) throws IOException { DataFormat format = wb.createDataFormat(); CellStyle styleTimestamp = wb.createCellStyle(); ZoneId timeZoneId = ZoneId.of(tz); ZoneId gmtZoneId = ZoneId.of("GMT"); styleTimestamp.setDataFormat(format.getFormat("yyyy-mm-dd h:mm")); int currentTask = -1; for (TaskFeature feature : tl.features) { TaskProperties props = feature.properties; int thisTask = props.id; Row row = sheet.createRow(rowNumber++); for (int i = 0; i < cols.size(); i++) { Column col = cols.get(i);//from w w w . ja va 2 s . co m Cell cell = row.createCell(i); if (col.isAssignment || thisTask != currentTask) { // Write all the assignments but only task data on new task if (col.name.equals("from") || col.name.equals("to")) { cell.setCellStyle(styleTimestamp); if (col.getDateValue(props) != null) { LocalDateTime gmtDate = col.getDateValue(props).toLocalDateTime(); ZonedDateTime gmtZoned = ZonedDateTime.of(gmtDate, gmtZoneId); ZonedDateTime localZoned = gmtZoned.withZoneSameInstant(timeZoneId); LocalDateTime localDate = localZoned.toLocalDateTime(); Timestamp ts2 = Timestamp.valueOf(localDate); cell.setCellValue(ts2); } } else { if (col.name.equals("url")) { cell.setCellStyle(styles.get("default_grey")); } else { cell.setCellStyle(styles.get("default")); } cell.setCellValue(col.getValue(props)); } } else { cell.setCellStyle(styles.get("default_grey")); } } currentTask = thisTask; } // Populate settings sheet Row settingsRow = settingsSheet.createRow(0); Cell k = null; Cell v = null; k = settingsRow.createCell(0); k.setCellValue("Time Zone:"); v = settingsRow.createCell(1); v.setCellValue(tz); }