List of usage examples for org.apache.poi.ss.usermodel CellStyle setWrapText
void setWrapText(boolean wrapped);
From source file:com.quanticate.opensource.datalistdownload.DataListDownloadWebScript.java
License:Open Source License
@Override protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource;// w w w . ja va 2s . c om List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Pair<Object, String> valAndLink = identifyValueAndLink(item, prop); if (valAndLink == null) { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } else { Object val = valAndLink.getFirst(); // Multi-line property? if (val instanceof String[]) { String[] lines = (String[]) val; StringBuffer text = new StringBuffer(); for (String line : lines) { if (text.length() > 0) { text.append('\n'); } text.append(line); } String v = text.toString(); c.setCellValue(v); if (lines.length > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines.length * sheet.getDefaultRowHeightInPoints()); } } // Regular properties else if (val instanceof String) { c.setCellValue((String) val); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err .println("TODO: Handle Excel output of " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { sheet.autoSizeColumn(colNum); colNum++; } }
From source file:com.qwazr.externalizor.BenchmarkTest.java
License:Apache License
@BeforeClass public static void beforeClass() { workbook = new HSSFWorkbook(); sheet = workbook.createSheet("Default"); Row row = sheet.createRow(ypos++);//from w ww . j ava 2 s . com CellStyle style = workbook.createCellStyle(); style.setWrapText(true); setCell(style, row, 1, "Bytes\nSize"); setCell(style, row, 2, "Serialization\nRaw"); setCell(style, row, 3, "Externalizor\nRaw"); setCell(style, row, 4, "Serialization\nCompressed"); setCell(style, row, 5, "Externalizor\nCompressed"); setCell(style, row, 7, "Rate\nrw / sec"); setCell(style, row, 8, "Serialization\nRaw"); setCell(style, row, 9, "Externalizor\nRaw"); setCell(style, row, 10, "Serialization\nCompressed"); setCell(style, row, 11, "Externalizor\nCompressed"); }
From source file:com.sccl.attech.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?/*w w w .ja v a 2s . c om*/ * @param row * @param column ? * @param val * @param align ??1?23?? * @return ? */ public Cell addCellStyle(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : "")); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setWrapText(true); try { if (val == null) { cell.setCellValue(""); } else if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); } else if (val instanceof Long) { cell.setCellValue((Long) val); } else if (val instanceof Double) { cell.setCellValue((Double) val); } else if (val instanceof Float) { cell.setCellValue((Float) val); } else if (val instanceof Date) { DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellValue((Date) val); } else { if (fieldType != Class.class) { cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } if (column == 8) { CellRangeAddressList regions = new CellRangeAddressList(25, 25, 8, 8); // ? DVConstraint constraint = DVConstraint .createExplicitListConstraint(new String[] { "2", "3", "" }); // HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint); // sheet data_validation.createErrorBox("Error", "Error"); data_validation.createPromptBox("", null); sheet.addValidationData(data_validation); } cell.setCellStyle(style); return cell; }
From source file:com.vincestyling.apkinfoextractor.core.export.ExportToExcel.java
License:Apache License
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); String fontName = getAvaliableTitleFont(); CellStyle style; Font font = wb.createFont();//from ww w. j ava2 s .com font.setFontName(fontName); font.setFontHeightInPoints((short) 14); font.setColor(IndexedColors.BROWN.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(font); styles.put("title", style); font = wb.createFont(); font.setFontName(fontName); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFont(font); style.setWrapText(true); setBorder(style); styles.put("header", style); font = wb.createFont(); font.setFontName(fontName); font.setFontHeightInPoints((short) 12); font.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); setBorder(style); styles.put("cell", style); return styles; }
From source file:com.wabacus.system.assistant.StandardExcelAssistant.java
License:Open Source License
public CellStyle getTitleCellStyleForStandardExcel(Workbook workbook) { CellStyle cs = workbook.createCellStyle(); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setWrapText(true); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setFillPattern(CellStyle.SOLID_FOREGROUND); cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); font.setFontHeightInPoints((short) 10); cs.setFont(font);//w w w .j a v a 2s . c o m //cs.setUserStyleName("wabacus_title_rowstyle");// return cs; }
From source file:com.wabacus.system.assistant.StandardExcelAssistant.java
License:Open Source License
public CellStyle getDataCellStyleForStandardExcel(Workbook workbook) { CellStyle cs = workbook.createCellStyle(); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setWrapText(true); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); font.setFontHeightInPoints((short) 10); cs.setFont(font);//from ww w .jav a2s .c o m return cs; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private void transformFormat(HSSFWorkbook wb, HSSFWorkbook wbNew) { HSSFSheet transactionSheet = wb.getSheet("Transactions"); HSSFSheet businessSheet = wb.getSheet("Business_List"); int numRows = transactionSheet.getLastRowNum() + 1; HSSFSheet transactionSheetNew = wbNew.getSheet("NewTransactions"); HSSFRow newRow;//from ww w . j av a 2 s. c om HSSFCell newCell; HSSFSheet lookupNew = wbNew.getSheet("NewLookUp"); HashMap<Long, HSSFRow> storedRows = new HashMap<Long, HSSFRow>(); HashMap<Long, String> storedSerials = new HashMap<Long, String>(); CRC32 crc32 = new CRC32(); CellStyle cs = wbNew.createCellStyle(); cs.setWrapText(true); int newRowLfd = 0; int i = 1; for (; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String serial = getStrVal(row.getCell(0)); // Serial_number String contactPerson = getStrVal(row.getCell(2)); // person String adressRec = getStrVal(row.getCell(4)); // Address if ((serial == null || serial.trim().isEmpty())) { if (contactPerson != null && !contactPerson.isEmpty() || adressRec != null && !adressRec.isEmpty()) { System.err.println("serial is seriously null... " + (i + 1)); } } else { /* int index = serial.lastIndexOf("_"); if (index <= 0) { System.err.println("index error ... no '_' there... " + (i + 1)); } serial = serial.substring(0, index) + "_" + (i + 1); */ HSSFRow busRow = getRow(businessSheet, adressRec, 9); if (busRow == null) { System.err.println("Id issue on recs...Row: " + (i + 1) + "\t" + adressRec); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressRec = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String prodNameOut = getStrVal(row.getCell(6)); // ProductName String prodNumOut = getStrVal(row.getCell(7)); // ProductNo String dayOut = getStrVal(row.getCell(8)); // Day String monthOut = getStrVal(row.getCell(9)); // Month String yearOut = getStrVal(row.getCell(10)); // Year String amountKG_Out = getStrVal(row.getCell(11)); // amountKG String typePUOut = getStrVal(row.getCell(12)); // typePU String numPUOut = getStrVal(row.getCell(13)); // numPU String lotNo_Out = getStrVal(row.getCell(14)); // String dayMHDOut = getStrVal(row.getCell(15)); String monthMHDOut = getStrVal(row.getCell(16)); String yearMHDOut = getStrVal(row.getCell(17)); // String dayPDOut = getStrVal(row.getCell(18)); String monthPDOut = getStrVal(row.getCell(19)); String yearPDOut = getStrVal(row.getCell(20)); String adressInsp = getStrVal(row.getCell(22)); // Address String activityInsp = getStrVal(row.getCell(23)); // Activity busRow = getRow(businessSheet, adressInsp, 9); if (busRow == null) { System.err.println("Id issue on insps...Row: " + (i + 1) + "\t" + adressInsp); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressInsp = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String prodNameIn = getStrVal(row.getCell(24)); // ProductName String prodNumIn = getStrVal(row.getCell(25)); // ProductNo String dayIn = getStrVal(row.getCell(26)); // Day String monthIn = getStrVal(row.getCell(27)); // Month String yearIn = getStrVal(row.getCell(28)); // Year String amountKG_In = getStrVal(row.getCell(29)); // amountKG String typePUIn = getStrVal(row.getCell(30)); // typePU String numPUIn = getStrVal(row.getCell(31)); // numPU String lotNo_In = getStrVal(row.getCell(32)); // String dayMHDIn = getStrVal(row.getCell(33)); String monthMHDIn = getStrVal(row.getCell(34)); String yearMHDIn = getStrVal(row.getCell(35)); // String dayPDIn = getStrVal(row.getCell(36)); String monthPDIn = getStrVal(row.getCell(37)); String yearPDIn = getStrVal(row.getCell(38)); String adressSup = getStrVal(row.getCell(40)); // Address String activitySup = getStrVal(row.getCell(41)); // Activity busRow = getRow(businessSheet, adressSup, 9); if (busRow == null) { System.err.println("Id issue on susps...Row: " + (i + 1) + "\t" + adressSup); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressSup = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String ec = getStrVal(row.getCell(42)); // EndChain String ece = getStrVal(row.getCell(43)); // Explanation_EndChain String oc = getStrVal(row.getCell(44)); // OriginCountry String cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks String ft = getStrVal(row.getCell(46)); // Further_Traceback String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample busRow = getRow(lookupNew, activityInsp, 9); String treatmentIn = null, treatmentOut = null; if (busRow != null) treatmentOut = busRow.getCell(13).getStringCellValue(); busRow = getRow(lookupNew, activitySup, 9); if (busRow != null) treatmentIn = busRow.getCell(13).getStringCellValue(); String sOut = adressInsp + "_" + prodNameOut + "_" + prodNumOut + "_" + lotNo_Out + "_" + dayPDOut + "_" + monthPDOut + "_" + yearPDOut + "_" + dayMHDOut + "_" + monthMHDOut + "_" + yearMHDOut + "_" + dayOut + "_" + monthOut + "_" + yearOut + "_" + amountKG_Out + "_" + numPUOut + "_" + typePUOut + "_" + adressRec; crc32.reset(); crc32.update(sOut.getBytes()); long crc32Out = crc32.getValue(); //System.err.println(crc32Out + " -> " + sOut); String sIn = adressSup + "_" + prodNameIn + "_" + prodNumIn + "_" + lotNo_In + "_" + dayPDIn + "_" + monthPDIn + "_" + yearPDIn + "_" + dayMHDIn + "_" + monthMHDIn + "_" + yearMHDIn + "_" + dayIn + "_" + monthIn + "_" + yearIn + "_" + amountKG_In + "_" + numPUIn + "_" + typePUIn + "_" + adressInsp; crc32.reset(); crc32.update(sIn.getBytes()); long crc32In = crc32.getValue(); //System.err.println(crc32In + " -> " + sIn); String backSerial = serial + ".1"; if (storedRows.containsKey(crc32In)) { //HSSFRow r = storedRows.get(crc32In); backSerial = r.getCell(1).getStringCellValue(); backSerial = storedSerials.get(crc32In); } if (storedRows.containsKey(crc32Out)) { HSSFRow r = storedRows.get(crc32Out); HSSFCell c = r.getCell(0); if (c == null) { c = r.createCell(0); c.setCellStyle(cs); c.setCellValue(backSerial); } else add2Cell(c, backSerial); add2Cell(r.getCell(20), contactPerson); add2Cell(r.getCell(23), oc); add2Cell(r.getCell(24), cqr); add2Cell(r.getCell(26), ms); } else { newRowLfd++; newRow = transactionSheetNew.createRow(newRowLfd); newCell = newRow.createCell(0, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(backSerial); newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(serial + ".0"); newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressInsp); newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNameOut); newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNumOut); if (treatmentOut != null) { newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(treatmentOut); } newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(lotNo_Out); newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayPDOut); newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthPDOut); newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearPDOut); newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayMHDOut); newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthMHDOut); newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearMHDOut); newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayOut); newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthOut); newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearOut); newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(amountKG_Out); newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(numPUOut); newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(typePUOut); newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressRec); newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(contactPerson); newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(oc); newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(cqr); newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ms); storedRows.put(crc32Out, newRow); storedSerials.put(crc32Out, serial + ".0"); } if (storedRows.containsKey(crc32In)) { HSSFRow r = storedRows.get(crc32In); add2Cell(r.getCell(20), contactPerson); add2Cell(r.getCell(21), ec); add2Cell(r.getCell(22), ece); add2Cell(r.getCell(23), oc); add2Cell(r.getCell(24), cqr); add2Cell(r.getCell(25), ft); add2Cell(r.getCell(26), ms); } else { newRowLfd++; newRow = transactionSheetNew.createRow(newRowLfd); newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(serial + ".1"); newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressSup); newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNameIn); newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNumIn); if (treatmentIn != null) { newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(treatmentIn); } newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(lotNo_In); newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayPDIn); newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthPDIn); newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearPDIn); newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayMHDIn); newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthMHDIn); newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearMHDIn); newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayIn); newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthIn); newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearIn); newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(amountKG_In); newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(numPUIn); newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(typePUIn); newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressInsp); newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(contactPerson); newCell = newRow.createCell(21, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ec); newCell = newRow.createCell(22, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ece); newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(oc); newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(cqr); newCell = newRow.createCell(25, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ft); newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ms); storedRows.put(crc32In, newRow); storedSerials.put(crc32In, serial + ".1"); } } } } System.err.println("last row: " + i); }
From source file:de.fme.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@SuppressWarnings("deprecation") @Override/* w w w. ja va 2s .c o m*/ protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource; List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CreationHelper createHelper = workbook.getCreationHelper(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); CellStyle hlink_style = workbook.createCellStyle(); Font hlink_font = workbook.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Serializable val = nodeService.getProperty(item, prop); if (val == null) { // Is it an association, or just missing? List<AssociationRef> assocs = nodeService.getTargetAssocs(item, prop); Set<QName> qnames = new HashSet<QName>(1, 1.0f); qnames.add(prop); List<ChildAssociationRef> childAssocs = nodeService.getChildAssocs(item, qnames); if (assocs.size() > 0) { StringBuffer text = new StringBuffer(); int lines = 1; for (AssociationRef ref : assocs) { NodeRef child = ref.getTargetRef(); QName type = nodeService.getType(child); if (ContentModel.TYPE_PERSON.equals(type)) { if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_FIRSTNAME)); text.append(" "); text.append(nodeService.getProperty(child, ContentModel.PROP_LASTNAME)); } else if (ContentModel.TYPE_CONTENT.equals(type)) { // TODO Link to the content if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); /*MessageFormat.format(CONTENT_DOWNLOAD_PROP_URL, new Object[] { child.getStoreRef().getProtocol(), child.getStoreRef().getIdentifier(), child.getId(), URLEncoder.encode((String)nodeService.getProperty(child, ContentModel.PROP_TITLE)), URLEncoder.encode(ContentModel.PROP_CONTENT.toString()) }); */ /*currently only one link per cell possible * Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); *link.setAddress("http://poi.apache.org/"); *c.setHyperlink(link); *c.setCellStyle(hlink_style);*/ } else if (ApplicationModel.TYPE_FILELINK.equals(type)) { NodeRef linkRef = (NodeRef) nodeService.getProperty(child, ContentModel.PROP_LINK_DESTINATION); if (linkRef != null) { if (text.length() > 0) { text.append('\n'); lines++; } text.append("link to: "); try { text.append(nodeService.getProperty(linkRef, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(linkRef, ContentModel.PROP_TITLE)); text.append(") "); } catch (Exception e) { text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); } } } else { System.err.println("TODO: handle " + type + " for " + child); } } String v = text.toString(); c.setCellValue(v); if (lines > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines * sheet.getDefaultRowHeightInPoints()); } } else if (childAssocs.size() > 0) { StringBuffer text = new StringBuffer(); for (ChildAssociationRef childAssociationRef : childAssocs) { NodeRef child = childAssociationRef.getChildRef(); QName type = nodeService.getType(child); if (type.equals(ForumModel.TYPE_FORUM)) { List<ChildAssociationRef> topics = nodeService.getChildAssocs(child); if (topics.size() > 0) { ChildAssociationRef topicRef = topics.get(0); List<ChildAssociationRef> comments = nodeService .getChildAssocs(topicRef.getChildRef()); for (ChildAssociationRef commentChildRef : comments) { NodeRef commentRef = commentChildRef.getChildRef(); ContentData data = (ContentData) nodeService.getProperty(commentRef, ContentModel.PROP_CONTENT); TemplateContentData contentData = new TemplateContentData(data, ContentModel.PROP_CONTENT); String commentString = ""; try { commentString = contentData.getContentAsText(commentRef, -1); } catch (Exception e) { logger.warn("failed to extract content for nodeRef " + commentRef, e); } String creator = (String) nodeService.getProperty(commentRef, ContentModel.PROP_CREATOR); NodeRef person = personService.getPerson(creator, false); if (person != null) { creator = nodeService.getProperty(person, ContentModel.PROP_FIRSTNAME) + " " + nodeService.getProperty(person, ContentModel.PROP_LASTNAME); } Date created = (Date) nodeService.getProperty(commentRef, ContentModel.PROP_CREATED); text.append(creator).append(" (") .append(DateFormatUtils.format(created, "yyyy-MM-dd")) .append("):\n "); text.append(commentString).append("\n"); } } } } String v = text.toString(); c.setCellValue(v); c.setCellStyle(styleNewLines); } else { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } } else { // Regular property, set if (val instanceof String) { c.setCellValue((String) val); c.setCellStyle(styleNewLines); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err.println("TODO: handle " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { try { sheet.autoSizeColumn(colNum); } catch (IllegalArgumentException e) { sheet.setColumnWidth(colNum, 40 * 256); } colNum++; } }
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java
License:Open Source License
/** * create a library of cell styles/* w ww .jav a2 s . co m*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }
From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java
License:Open Source License
@SuppressWarnings("unchecked") private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass, String i18nPrefix) {//from w w w . j ava 2s.c om String name = I18nProperties.getCaption(i18nPrefix); String safeName = WorkbookUtil.createSafeSheetName(name); XSSFSheet sheet = workbook.createSheet(safeName); // Create XSSFTable table = sheet.createTable(); String safeTableName = safeName.replaceAll("\\s", "_"); table.setName(safeTableName); table.setDisplayName(safeTableName); XssfHelper.styleTable(table, 1); int columnCount = EntityColumn.values().length; int rowNumber = 0; // header XSSFRow headerRow = sheet.createRow(rowNumber++); for (EntityColumn column : EntityColumn.values()) { table.addColumn(); String columnCaption = column.toString(); columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase(); headerRow.createCell(column.ordinal()).setCellValue(columnCaption); } // column width sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60); sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10); sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45); sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10); CellStyle defaultCellStyle = workbook.createCellStyle(); defaultCellStyle.setWrapText(true); List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>(); for (Field field : entityClass.getDeclaredFields()) { if (java.lang.reflect.Modifier.isStatic(field.getModifiers())) continue; XSSFRow row = sheet.createRow(rowNumber++); // field name XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal()); fieldNameCell.setCellValue(field.getName()); // value range XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal()); fieldValueCell.setCellStyle(defaultCellStyle); Class<?> fieldType = field.getType(); if (fieldType.isEnum()) { // use enum type name - values are added below // Object[] enumValues = fieldType.getEnumConstants(); // StringBuilder valuesString = new StringBuilder(); // for (Object enumValue : enumValues) { // if (valuesString.length() > 0) // valuesString.append(", "); // valuesString.append(((Enum) enumValue).name()); // } // fieldValueCell.setCellValue(valuesString.toString()); fieldValueCell.setCellValue(fieldType.getSimpleName()); if (!usedEnums.contains(fieldType)) { usedEnums.add((Class<Enum<?>>) fieldType); } } else if (EntityDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (ReferenceDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (String.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("text")); } else if (Date.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("date")); } else if (Number.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("number")); } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString()); } // caption XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal()); captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), "")); // description XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal()); descriptionCell.setCellStyle(defaultCellStyle); descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), "")); // required XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal()); if (field.getAnnotation(Required.class) != null) requiredCell.setCellValue(true); // diseases XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal()); diseasesCell.setCellStyle(defaultCellStyle); Diseases diseases = field.getAnnotation(Diseases.class); if (diseases != null) { StringBuilder diseasesString = new StringBuilder(); for (Disease disease : diseases.value()) { if (diseasesString.length() > 0) diseasesString.append(", "); diseasesString.append(disease.toShortString()); } diseasesCell.setCellValue(diseasesString.toString()); } else { diseasesCell.setCellValue("All"); } // outbreak XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal()); if (field.getAnnotation(Outbreaks.class) != null) outbreakCell.setCellValue(true); } AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0), new CellReference(rowNumber - 1, columnCount - 1)); table.setCellReferences(reference); table.getCTTable().addNewAutoFilter(); for (Class<Enum<?>> usedEnum : usedEnums) { rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum); } return sheet; }