List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName
public static String createSafeSheetName(final String nameProposal)
From source file:de.ryanthara.ja.rycon.converter.excel.Cadwork2Excel.java
License:GNU General Public License
/** * Converts a coordinate file from Cadwork (node.dat) into a Microsoft Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * * @return success of the conversion/*from w w w. jav a 2 s . c o m*/ */ public boolean convertCadwork2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); String[] lineSplit; Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; short rowNumber = 0; short cellNumber = 0; // remove not needed headlines for (int i = 0; i < 3; i++) { readStringLines.remove(0); } if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; lineSplit = readStringLines.get(0).trim().split("\\s+", -1); for (String description : lineSplit) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue(description); } } // remove furthermore the still not needed comment line readStringLines.remove(0); for (String line : readStringLines) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; lineSplit = line.trim().split("\\t", -1); cell = row.createCell(cellNumber); // No cell.setCellValue(lineSplit[0]); cellNumber++; cell = row.createCell(cellNumber); // X cell.setCellValue(lineSplit[1]); cellNumber++; cell = row.createCell(cellNumber); // Y cell.setCellValue(lineSplit[2]); cellNumber++; cell = row.createCell(cellNumber); // Z cell.setCellValue(lineSplit[3]); cellNumber++; cell = row.createCell(cellNumber); // Code cell.setCellValue(lineSplit[4]); cellNumber++; cell = row.createCell(cellNumber); // Name cell.setCellValue(lineSplit[5]); } // adjust column width to fit the content for (int i = 0; i < 5; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.Caplan2Excel.java
License:GNU General Public License
/** * Converts a Caplan K file element by element into a Microsoft Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * @param writeCommentRow write comment row * * @return success conversion success//www.jav a 2s . co m */ public boolean convertCaplan2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; CellStyle cellStyle; DataFormat format = workbook.createDataFormat(); short rowNumber = 0; short cellNumber = 0; short countColumns = 0; if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.pointNumber)); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.easting)); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.northing)); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.height)); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.object)); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.attribute)); } for (String line : readStringLines) { // skip empty lines directly after reading if (!line.trim().isEmpty()) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; CaplanBlock caplanBlock = new CaplanBlock(line); if (caplanBlock.getNumber() != null) { cell = row.createCell(cellNumber); cell.setCellValue(caplanBlock.getNumber()); cellNumber++; } if (caplanBlock.getEasting() != null) { cell = row.createCell(cellNumber); if (!caplanBlock.getEasting().equals("")) { cell.setCellValue(Double.parseDouble(caplanBlock.getEasting())); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.0000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } else { cell.setCellValue(""); } cellNumber++; } if (caplanBlock.getNorthing() != null) { cell = row.createCell(cellNumber); if (!caplanBlock.getNorthing().equals("")) { cell.setCellValue(Double.parseDouble(caplanBlock.getNorthing())); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.0000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } else { cell.setCellValue(""); } cellNumber++; } if (caplanBlock.getHeight() != null) { cell = row.createCell(cellNumber); if (!caplanBlock.getHeight().equals("")) { cell.setCellValue(Double.parseDouble(caplanBlock.getHeight())); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.0000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } else { cell.setCellValue(""); } cellNumber++; } if (caplanBlock.getCode() != null) { cell = row.createCell(cellNumber); cell.setCellValue(caplanBlock.getCode()); cellNumber++; if (caplanBlock.getAttributes().size() > 0) { for (String attribute : caplanBlock.getAttributes()) { cell = row.createCell(cellNumber); cell.setCellValue(attribute); cellNumber++; } } } if (cellNumber > countColumns) { countColumns = cellNumber; } } } // adjust column width to fit the content for (int i = 0; i < countColumns; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.CSV2Excel.java
License:GNU General Public License
/** * Convert a CSV file element by element into an Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * * @return success conversion success//from ww w .ja va 2 s . co m */ public boolean convertCSV2Excel(boolean isXLS, String sheetName) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; short rowNumber = 0; short cellNumber; short countColumns = 0; for (String[] csvLine : readCSVLines) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; for (String element : csvLine) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue(element); } if (cellNumber > countColumns) { countColumns = cellNumber; } } // adjust column width to fit the content for (int i = 0; i < countColumns; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.CSVBaselStadt2Excel.java
License:GNU General Public License
/** * Converts a comma separated coordinate file from the geodata server Basel Stadt (Switzerland) * into a Zeiss REC formatted file./*from ww w . j av a 2s . co m*/ * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * @param writeCommentRow write comment row * * @return success conversion success */ public boolean convertCSVBaselStadt2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; CellStyle cellStyle; DataFormat format = workbook.createDataFormat(); short rowNumber = 0; short cellNumber = 0; if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; String[] commentLine = readCSVLines.get(0); for (String description : commentLine) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue(description); } } // remove furthermore the still not needed comment line readCSVLines.remove(0); for (String[] csvLine : readCSVLines) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; for (int i = 0; i < csvLine.length; i++) { cell = row.createCell(cellNumber); cellNumber++; switch (i) { case 0: case 1: cell.setCellValue(csvLine[i]); break; case 2: case 3: case 4: case 5: if (csvLine[i].equalsIgnoreCase("")) { cell.setCellValue(csvLine[i]); } else { cell.setCellValue(Double.parseDouble(csvLine[i])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } break; case 6: case 7: case 8: case 9: case 10: cell.setCellValue(csvLine[i]); break; default: System.err.println( "Error in convertCSVBaselStadt2Excel: unknown element found or to much columns"); } } } // adjust column width to fit the content for (int i = 0; i < readCSVLines.get(0).length; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.GSI2Excel.java
License:GNU General Public License
/** * Converts a GSI file element by element into an Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * * @return success conversion success/*from www .ja va 2 s . com*/ */ public boolean convertGSI2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; CellStyle cellStyle; DataFormat format = workbook.createDataFormat(); short rowNumber = 0; short cellNumber = 0; if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; for (int wordIndex : baseToolsGSI.getFoundAllWordIndices()) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue( ResourceBundleUtils.getLangString(WORDINDICES, WordIndices.valueOf("WI" + wordIndex))); } } // fill gsi content into rows and cells for (ArrayList<GSIBlock> blocksInLine : baseToolsGSI.getEncodedLinesOfGSIBlocks()) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; for (GSIBlock block : blocksInLine) { cell = row.createCell(cellNumber); cellNumber++; switch (block.getWordIndex()) { // GENERAL case 11: // Point number (includes block number) case 12: // Instrument serial no case 13: // Instrument type case 18: // Time format 1: pos. 8-9 year, 10-11 sec, 12-14 msec case 19: // Time format 2 : pos, 8-9 month 10-11 day, 12-13 hour, 14-15 min cell.setCellValue(block.toPrintFormatCSV()); break; // ANGLES case 21: // Horizontal Circle (Hz) case 22: // Vertical Angle (V) case 25: // Horizontal circle difference (Hz0-Hz) cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV())); break; // DISTANCE case 31: // Slope Distance case 32: // Horizontal Distance case 33: // Height Difference cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV())); break; // CODE BLOCK case 41: // Code number ( include block number) case 42: // Information 1 case 43: // Information 2 case 44: // Information 3 case 45: // Information 4 case 46: // Information 5 case 47: // Information 6 case 48: // Information 7 case 49: // Information 8 cell.setCellValue(block.toPrintFormatCSV()); break; // DISTANCE (additional information) case 51: // Constants(ppm, mm) case 52: // Number of measurements, standard deviation case 53: // Deviation case 58: // Signal strength case 59: // Reflector constant (1/10 mm)ppm cell.setCellValue(block.toPrintFormatCSV()); break; // POINT CODING case 71: // Point Code case 72: // Attribute 1 case 73: // Attribute 2 case 74: // Attribute 3 case 75: // Attribute 4 case 76: // Attribute 5 case 77: // Attribute 6 case 78: // Attribute 7 case 79: // Attribute 8 cell.setCellValue(block.toPrintFormatCSV()); break; // COORDINATES case 81: // Easting (Target) case 82: // Northing (Target) case 83: // Elevation (Target) case 84: // Station Easting (E0) case 85: // Station Northing (N0) case 86: // Station Elevation (H0) cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV())); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.0000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); break; case 87: // Reflector height (above ground) case 88: // Instrument height (above ground) cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV())); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); break; default: System.err.println("GSI2Excel.convertGSI2Excel() : line contains unknown word index " + block.toPrintFormatCSV()); } } } // adjust column width to fit the content for (int i = 0; i < baseToolsGSI.getEncodedLinesOfGSIBlocks().size(); i++) { sheet.autoSizeColumn((short) i); } // check number of written lines return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.TXT2Excel.java
License:GNU General Public License
/** * Converts a TXT file element by element into an Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * * @return success conversion success/*from ww w. j a v a 2 s .c o m*/ */ public boolean convertTXT2Excel(boolean isXLS, String sheetName) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; short rowNumber = 0; short cellNumber; short countColumns = 0; for (String line : readStringLines) { String[] lineSplit = line.trim().split("\\s+"); row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; for (String element : lineSplit) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue(element); if (cellNumber > countColumns) { countColumns = cellNumber; } } } // adjust column width to fit the content for (int i = 0; i < countColumns; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.TXTBaselLandschaft2Excel.java
License:GNU General Public License
/** * Converts a txt file from the geodata server Basel Landschaft (Switzerland) element by element into an Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * @param writeCommentRow write comment row * * @return success conversion success//from w w w . ja va 2 s . c om */ public boolean convertTXTBaselLand2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; CellStyle cellStyle; DataFormat format = workbook.createDataFormat(); short rowNumber = 0; short cellNumber = 0; short countColumns = 0; if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; String[] lineSplit = readStringLines.get(0).trim().split("\\t", -1); for (String description : lineSplit) { cell = row.createCell(cellNumber); cellNumber++; cell.setCellValue(description); } } // remove furthermore the still not needed comment line readStringLines.remove(0); for (String line : readStringLines) { row = sheet.createRow(rowNumber); rowNumber++; String[] lineSplit = line.trim().split("\\t", -1); cellNumber = 0; switch (lineSplit.length) { case 5: // HFP file cell = row.createCell(cellNumber); // Art cell.setCellValue(lineSplit[0]); cellNumber++; cell = row.createCell(cellNumber); // Number cell.setCellValue(lineSplit[1]); cellNumber++; cell = row.createCell(cellNumber); // X cell.setCellValue(Double.parseDouble(lineSplit[2])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); cellNumber++; cell = row.createCell(cellNumber); // Y cell.setCellValue(Double.parseDouble(lineSplit[3])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); cellNumber++; cell = row.createCell(cellNumber); // Z if (lineSplit[4].equalsIgnoreCase("NULL")) { cell.setCellValue("NULL"); } else { cell.setCellValue(Double.parseDouble(lineSplit[4])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } countColumns = 5; break; case 6: // LFP file cell = row.createCell(cellNumber); // Art cell.setCellValue(lineSplit[0]); cellNumber++; cell = row.createCell(cellNumber); // Number cell.setCellValue(lineSplit[1]); cellNumber++; cell = row.createCell(cellNumber); // VArt cell.setCellValue(lineSplit[2]); cellNumber++; cell = row.createCell(cellNumber); // X cell.setCellValue(Double.parseDouble(lineSplit[3])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); cellNumber++; cell = row.createCell(cellNumber); // Y cell.setCellValue(Double.parseDouble(lineSplit[4])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); cellNumber++; cell = row.createCell(cellNumber); // Z if (lineSplit[5].equalsIgnoreCase("NULL")) { cell.setCellValue("NULL"); } else { cell.setCellValue(Double.parseDouble(lineSplit[5])); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(format.getFormat("#,##0.000")); cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); } countColumns = 6; break; default: System.err.println( "TXTBaselLandschaft2Excel.convertTXTBaselLand2Excel() : line contains less or more tokens " + line); } } // adjust column width to fit the content for (int i = 0; i < countColumns; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
From source file:de.ryanthara.ja.rycon.converter.excel.Zeiss2Excel.java
License:GNU General Public License
/** * Convert a Zeiss REC file element by element into an Excel file. * * @param isXLS selector to distinguish between XLS and XLSX file extension * @param sheetName name of the sheet (file name from input file) * @param writeCommentRow write comment row * * @return success conversion success//from w ww.j a v a2s.c o m */ public boolean convertZeiss2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) { // general preparation of the workbook if (isXLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } String safeName = WorkbookUtil.createSafeSheetName(sheetName); Sheet sheet = workbook.createSheet(safeName); Row row; Cell cell; // CellStyle cellStyle; // DataFormat format = workbook.createDataFormat(); short rowNumber = 0; short cellNumber = 0; short countColumns = 0; // TODO implement comment row and multi line stored values /* if (writeCommentRow) { row = sheet.createRow(rowNumber); rowNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("pointNumber")); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("easting")); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("northing")); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("height")); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("object")); cellNumber++; cell = row.createCell(cellNumber); cell.setCellValue(I18N.getCaplanColumnTyp("attribute")); } */ for (String line : readStringLines) { // skip empty lines directly after reading if (!line.trim().isEmpty()) { row = sheet.createRow(rowNumber); rowNumber++; cellNumber = 0; ZeissDecoder decoder = new ZeissDecoder(); for (ZeissBlock zeissBlock : decoder.getZeissBlocks()) { cell = row.createCell(cellNumber); cell.setCellValue(zeissBlock.getValue()); cellNumber++; countColumns++; } if (cellNumber > countColumns) { countColumns = cellNumber; } } } // adjust column width to fit the content for (int i = 0; i < countColumns; i++) { sheet.autoSizeColumn((short) i); } return rowNumber > 1; }
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 ww w . j a va 2 s . 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; }
From source file:de.symeda.sormas.api.doc.UserRightsGenerator.java
License:Open Source License
@Test public void generateUserRights() throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); // Create User Rights sheet String safeName = WorkbookUtil.createSafeSheetName("User Rights"); XSSFSheet sheet = workbook.createSheet(safeName); // Initialize cell styles // Authorized style XSSFCellStyle authorizedStyle = workbook.createCellStyle(); authorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); authorizedStyle.setFillForegroundColor(new XSSFColor(new Color(0, 153, 0))); authorizedStyle.setBorderBottom(BorderStyle.THIN); authorizedStyle.setBorderLeft(BorderStyle.THIN); authorizedStyle.setBorderTop(BorderStyle.THIN); authorizedStyle.setBorderRight(BorderStyle.THIN); authorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Unauthorized style XSSFCellStyle unauthorizedStyle = workbook.createCellStyle(); unauthorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); unauthorizedStyle.setFillForegroundColor(new XSSFColor(Color.RED)); unauthorizedStyle.setBorderBottom(BorderStyle.THIN); unauthorizedStyle.setBorderLeft(BorderStyle.THIN); unauthorizedStyle.setBorderTop(BorderStyle.THIN); unauthorizedStyle.setBorderRight(BorderStyle.THIN); unauthorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Bold style XSSFFont boldFont = workbook.createFont(); boldFont.setBold(true);//from ww w. ja va 2 s. co m XSSFCellStyle boldStyle = workbook.createCellStyle(); boldStyle.setFont(boldFont); int rowCounter = 0; // Header Row headerRow = sheet.createRow(rowCounter++); Cell userRightHeadlineCell = headerRow.createCell(0); userRightHeadlineCell.setCellValue("User Right"); userRightHeadlineCell.setCellStyle(boldStyle); Cell descHeadlineCell = headerRow.createCell(1); descHeadlineCell.setCellValue("Description"); descHeadlineCell.setCellStyle(boldStyle); sheet.setColumnWidth(0, 256 * 35); sheet.setColumnWidth(1, 256 * 50); for (UserRole userRole : UserRole.values()) { String columnCaption = userRole.toString(); Cell headerCell = headerRow.createCell(userRole.ordinal() + 2); headerCell.setCellValue(columnCaption); headerCell.setCellStyle(boldStyle); sheet.setColumnWidth(userRole.ordinal() + 2, 256 * 14); } // User right rows for (UserRight userRight : UserRight.values()) { Row row = sheet.createRow(rowCounter++); // User right name Cell nameCell = row.createCell(0); nameCell.setCellValue(userRight.name()); nameCell.setCellStyle(boldStyle); // User right description Cell descCell = row.createCell(1); descCell.setCellValue(userRight.toString()); // Add styled cells for all user roles for (UserRole userRole : UserRole.values()) { Cell roleRightCell = row.createCell(userRole.ordinal() + 2); if (userRole.hasDefaultRight(userRight)) { roleRightCell.setCellStyle(authorizedStyle); roleRightCell.setCellValue("Yes"); } else { roleRightCell.setCellStyle(unauthorizedStyle); roleRightCell.setCellValue("No"); } } } XssfHelper.addAboutSheet(workbook); String filePath = "src/main/resources/doc/SORMAS_User_Rights.xlsx"; try (OutputStream fileOut = new FileOutputStream(filePath)) { workbook.write(fileOut); } workbook.close(); // Desktop.getDesktop().open(new File(filePath)); }