List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java
License:Open Source License
/** * @param workbook the workbook of the cell * @param strikeOut true, if cell should be striked out * @return the normal cell style/*from ww w . j av a 2 s.c o m*/ */ public static CellStyle getNormalStyle(Workbook workbook, boolean strikeOut) { // create cell style CellStyle cellStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); // set thin border around the cell cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); // set cell data format to text cellStyle.setDataFormat(df.getFormat("@")); // display multiple lines cellStyle.setWrapText(true); if (strikeOut) { // strike out font Font disabledFont = workbook.createFont(); disabledFont.setStrikeout(true); disabledFont.setColor(IndexedColors.GREY_40_PERCENT.getIndex()); cellStyle.setFont(disabledFont); } return cellStyle; }
From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java
License:Open Source License
/** * @param workbook the workbook of the cell * @param strikeOut true, if cell should be striked out * @return the highlighted cell style//from w ww . j a v a 2 s . co m */ public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) { // create highlight style for type cells CellStyle highlightStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); // set thin border around the cell highlightStyle.setBorderBottom(CellStyle.BORDER_THIN); highlightStyle.setBorderLeft(CellStyle.BORDER_THIN); highlightStyle.setBorderRight(CellStyle.BORDER_THIN); // set cell data format to text highlightStyle.setDataFormat(df.getFormat("@")); // display multiple lines highlightStyle.setWrapText(true); highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); if (strikeOut) { Font disabledTypeFont = workbook.createFont(); disabledTypeFont.setStrikeout(true); disabledTypeFont.setColor(IndexedColors.BLACK.getIndex()); highlightStyle.setFont(disabledTypeFont); } return highlightStyle; }
From source file:export.notes.view.to.excel.ExcelWriter.java
License:Apache License
private void createCellStyle(int position, ViewColumn column, ViewEntry entry) throws NotesException { CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); if (column.isFontBold()) { font.setBoldweight(Font.BOLDWEIGHT_BOLD); }/*from www .j a va 2 s . com*/ font.setItalic(column.isFontItalic()); switch (column.getFontColor()) { case RichTextStyle.COLOR_BLACK: font.setColor(HSSFColor.BLACK.index); break; case RichTextStyle.COLOR_BLUE: font.setColor(HSSFColor.BLUE.index); break; case RichTextStyle.COLOR_CYAN: font.setColor(HSSFColor.CORAL.index); break; case RichTextStyle.COLOR_DARK_BLUE: font.setColor(HSSFColor.DARK_BLUE.index); break; case RichTextStyle.COLOR_DARK_CYAN: font.setColor(HSSFColor.DARK_GREEN.index); break; case RichTextStyle.COLOR_DARK_GREEN: font.setColor(HSSFColor.DARK_GREEN.index); break; case RichTextStyle.COLOR_DARK_MAGENTA: font.setColor(HSSFColor.VIOLET.index); break; case RichTextStyle.COLOR_DARK_RED: font.setColor(HSSFColor.DARK_RED.index); break; case RichTextStyle.COLOR_DARK_YELLOW: font.setColor(HSSFColor.DARK_YELLOW.index); break; case RichTextStyle.COLOR_GRAY: font.setColor(HSSFColor.GREY_80_PERCENT.index); break; case RichTextStyle.COLOR_GREEN: font.setColor(HSSFColor.GREEN.index); break; case RichTextStyle.COLOR_LIGHT_GRAY: font.setColor(HSSFColor.GREY_50_PERCENT.index); break; case RichTextStyle.COLOR_MAGENTA: font.setColor(HSSFColor.VIOLET.index); break; case RichTextStyle.COLOR_RED: font.setColor(HSSFColor.RED.index); break; case RichTextStyle.COLOR_WHITE: font.setColor(HSSFColor.BLACK.index); break; case RichTextStyle.COLOR_YELLOW: font.setColor(HSSFColor.YELLOW.index); break; default: break; } cellStyle.setFont(font); switch (column.getAlignment()) { case ViewColumn.ALIGN_CENTER: cellStyle.setAlignment(CellStyle.ALIGN_CENTER); break; case ViewColumn.ALIGN_LEFT: cellStyle.setAlignment(CellStyle.ALIGN_LEFT); break; case ViewColumn.ALIGN_RIGHT: cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); break; default: break; } @SuppressWarnings("unchecked") Vector<Object> values = entry.getColumnValues(); Object value = values.get(position); String name = value.getClass().getSimpleName(); short format = 0; if (name.contains("Double")) { //$NON-NLS-1$ XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat(); switch (column.getNumberFormat()) { case ViewColumn.FMT_CURRENCY: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(6)); break; case ViewColumn.FMT_FIXED: String zero = "0"; //$NON-NLS-1$ String fixedFormat = "#0"; //$NON-NLS-1$ int digits = column.getNumberDigits(); if (digits > 0) { String n = StringUtils.repeat(zero, digits); fixedFormat = fixedFormat + "." + n; } format = fmt.getFormat(fixedFormat); break; default: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1)); break; } } else if (name.contains("DateTime")) { //$NON-NLS-1$ XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat(); switch (column.getTimeDateFmt()) { case ViewColumn.FMT_DATE: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe)); break; case ViewColumn.FMT_DATETIME: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x16)); break; case ViewColumn.FMT_TIME: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x15)); break; default: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe)); break; } } cellStyle.setDataFormat(format); styles.add(cellStyle); }
From source file:fi.thl.pivot.export.XlsxExporter.java
private CellStyle measureStyle(Workbook wb, int decimals) { if (decimals == 0) { return numberStyle; }/*from w w w . j ava 2 s . c o m*/ if (decimalStyles.containsKey(decimals)) { return decimalStyles.get(decimals); } CellStyle style = wb.createCellStyle(); String format = String.format("#,##0.%0" + decimals + "d", 0); style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(format)); style.setFont(valueFont); decimalStyles.put(decimals, style); return style; }
From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java
License:Apache License
/** * Initialisation des styles de cellule/*from w w w. jav a2s. co m*/ */ protected void initStyles() { CellStyle defaultStyle = workbook.createCellStyle(); defaultStyle.setFont(getFont(FONT_NORMAL_NAME)); setStyleFillForegroundColor(defaultStyle, colorRegistry, HSSFColor.WHITE.index); defaultStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); defaultStyle.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultStyle.setWrapText(true); registerStyle(STYLE_DEFAULT_NAME, defaultStyle); CellStyle styleHeader = workbook.createCellStyle(); styleHeader.setAlignment(CellStyle.ALIGN_CENTER); styleHeader.setFont(getFont(FONT_HEADER_NAME)); setStyleFillForegroundColor(styleHeader, colorRegistry, HEADER_BACKGROUND_COLOR_INDEX); styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND); styleHeader.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setDataFormat((short) 0); styleHeader.setWrapText(true); registerStyle(STYLE_HEADER_NAME, styleHeader); CellStyle styleOdd = cloneStyle(defaultStyle); registerStyle(STYLE_STANDARD_NAME + ROW_ODD_NAME, styleOdd); CellStyle styleEven = cloneStyle(styleOdd); setStyleFillForegroundColor(styleEven, colorRegistry, EVEN_ROW_BACKGROUND_COLOR_INDEX); registerStyle(STYLE_STANDARD_NAME + ROW_EVEN_NAME, styleEven); // styles pour les nombres entiers short integerFormatIndex = dataFormat.getFormat(integerDataFormat); CellStyle styleOddInteger = cloneStyle(styleOdd); styleOddInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleOddInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_ODD_NAME, styleOddInteger); CellStyle styleEvenInteger = cloneStyle(styleEven); styleEvenInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_EVEN_NAME, styleEvenInteger); // styles pour les nombres dcimaux short decimalFormatIndex = dataFormat.getFormat(decimalDataFormat); CellStyle styleOddDecimal = cloneStyle(styleOdd); styleOddDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleOddDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_ODD_NAME, styleOddDecimal); CellStyle styleEvenDecimal = cloneStyle(styleEven); styleEvenDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_EVEN_NAME, styleEvenDecimal); // styles pour les dates short dateFormatIndex = dataFormat.getFormat(dateDataFormat); CellStyle styleOddDate = cloneStyle(styleOdd); styleOddDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_ODD_NAME, styleOddDate); CellStyle styleEvenDate = cloneStyle(styleEven); styleEvenDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_EVEN_NAME, styleEvenDate); // styles pour les dates avec heure short dateTimeFormatIndex = dataFormat.getFormat(dateTimeDataFormat); CellStyle styleOddDateTime = cloneStyle(styleOdd); styleOddDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_ODD_NAME, styleOddDateTime); CellStyle styleEvenDateTime = cloneStyle(styleEven); styleEvenDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_EVEN_NAME, styleEvenDateTime); // styles pour les pourcentages short percentFormatIndex = dataFormat.getFormat(percentDataFormat); CellStyle styleOddPercent = cloneStyle(styleOdd); styleOddPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_ODD_NAME, styleOddPercent); CellStyle styleEvenPercent = cloneStyle(styleEven); styleEvenPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_EVEN_NAME, styleEvenPercent); short percentRelativeFormatIndex = dataFormat.getFormat(percentRelativeDataFormat); CellStyle styleOddPercentRelative = cloneStyle(styleOdd); styleOddPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_ODD_NAME, styleOddPercentRelative); CellStyle styleEvenPercentRelative = cloneStyle(styleEven); styleEvenPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_EVEN_NAME, styleEvenPercentRelative); // styles pour les liens CellStyle styleOddLink = cloneStyle(styleOdd); styleOddLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_ODD_NAME, styleOddLink); CellStyle styleEvenLink = cloneStyle(styleEven); styleEvenLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_EVEN_NAME, styleEvenLink); // styles pour les tailles de fichiers short fileSizeFormatIndex = dataFormat.getFormat(fileSizeDataFormat); CellStyle styleOddFileSize = cloneStyle(styleOdd); styleOddFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_ODD_NAME, styleOddFileSize); CellStyle styleEvenFileSize = cloneStyle(styleEven); styleEvenFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_EVEN_NAME, styleEvenFileSize); }
From source file:functions.excels.exports.CarteSommeBiodiversiteExcel.java
License:Apache License
public CarteSommeBiodiversiteExcel(Map<String, String> info, CarteSommeBiodiversite csb) { super();//w ww.ja v a2 s . c om Sheet sheet = wb.createSheet("Carte somme de la biodiversit"); Espece espece = Espece.find.byId(Integer.parseInt(info.get("espece"))); SousGroupe sous_groupe = SousGroupe.find.byId(Integer.parseInt(info.get("sous_groupe"))); Groupe groupe = Groupe.find.byId(Integer.parseInt(info.get("groupe"))); StadeSexe stade_sexe = StadeSexe.find.byId(Integer.parseInt(info.get("stade"))); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Carte indiquant les premires observations "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; titre += " du " + date1 + " au " + date2; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("UTM"); rowHead.createCell(1).setCellValue("Fiche ID"); rowHead.createCell(2).setCellValue("Espce"); rowHead.createCell(3).setCellValue("Date"); rowHead.createCell(4).setCellValue("Tmoin(s)"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (UTMS utm : UTMS.findAll()) { List<List<InformationsComplementaires>> observationsDansCetteMaille = csb.carte.get(utm); for (List<InformationsComplementaires> observationsPourCetteEspece : observationsDansCetteMaille) { for (InformationsComplementaires complements : observationsPourCetteEspece) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(utm.utm); row.createCell(1).setCellValue( complements.informations_complementaires_observation.observation_fiche.fiche_id); row.createCell(2).setCellValue( complements.informations_complementaires_observation.observation_espece.espece_nom); Cell cellDate = row.createCell(3); cellDate.setCellValue( complements.informations_complementaires_observation.observation_fiche.fiche_date); cellDate.setCellStyle(cellStyleDate); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = complements.informations_complementaires_observation.observation_fiche .getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(4).setCellValue(membres.toString()); i++; } } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); for (int k = 1; k <= 20; k++) { if (sheet.getRow(k) == null) sheet.createRow(k); } CellStyle redBackGround = wb.createCellStyle(); redBackGround.setFillBackgroundColor(IndexedColors.RED.getIndex()); redBackGround.setFillPattern(CellStyle.BIG_SPOTS); for (UTMS utm : csb.carte.keySet()) { int xy[] = UTMtoXY.convert10x10(utm.utm); Row row = sheet.getRow(xy[1] + 1); Cell cell = row.createCell(xy[0] + 5); int nombreDEspeces = csb.getNombreDEspecesDansMaille(utm); cell.setCellValue(nombreDEspeces); if (nombreDEspeces != 0) cell.setCellStyle(redBackGround); } for (int k = 5; k < 25; k++) { sheet.autoSizeColumn(k); } Row rowUniteMailleEspece; if ((rowUniteMailleEspece = sheet.getRow(23)) == null) rowUniteMailleEspece = sheet.createRow(23); rowUniteMailleEspece.createCell(6).setCellValue("Units maille-espce : " + csb.getUnitesMailleEspece()); }
From source file:functions.excels.exports.ChronologieDUnTemoinExcel.java
License:Apache License
public ChronologieDUnTemoinExcel(Map<String, String> info, ChronologieDUnTemoin cdut) { super();//from w w w. ja v a 2 s .c o m Sheet sheet = wb.createSheet("Chronologie d'un tmoin"); Espece espece = Espece.find.byId(Integer.parseInt(info.get("espece"))); SousGroupe sous_groupe = SousGroupe.find.byId(Integer.parseInt(info.get("sous_groupe"))); Groupe groupe = Groupe.find.byId(Integer.parseInt(info.get("groupe"))); StadeSexe stade_sexe = StadeSexe.find.byId(Integer.parseInt(info.get("stade"))); String maille = info.get("maille"); String temoin = info.get("temoin"); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Chronologie des tmoignages "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; if (!maille.equals("")) titre += " dans la maille " + maille; titre += " faits par " + temoin; titre += " du " + date1 + " au " + date2; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("Fiche ID"); rowHead.createCell(1).setCellValue("UTM"); rowHead.createCell(2).setCellValue("Lieu-dit"); rowHead.createCell(3).setCellValue("Commune"); rowHead.createCell(4).setCellValue("Dp."); rowHead.createCell(5).setCellValue("Date min"); rowHead.createCell(6).setCellValue("Date"); rowHead.createCell(7).setCellValue("Espce"); rowHead.createCell(8).setCellValue("Nombre"); rowHead.createCell(9).setCellValue("Stade/Sexe"); rowHead.createCell(10).setCellValue("Tmoins"); rowHead.createCell(11).setCellValue("Mmo"); rowHead.createCell(12).setCellValue("Groupe"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (InformationsComplementaires complements : cdut.chronologie) { Row row = sheet.createRow(i); Observation observation = complements.informations_complementaires_observation; Fiche fiche = observation.observation_fiche; row.createCell(0).setCellValue(fiche.fiche_id); row.createCell(1).setCellValue(fiche.fiche_utm.utm); row.createCell(2).setCellValue(fiche.fiche_lieudit); if (fiche.fiche_commune != null) { row.createCell(3).setCellValue(fiche.fiche_commune.ville_nom_aer); row.createCell(4).setCellValue(fiche.fiche_commune.ville_departement.departement_code); } if (fiche.fiche_date_min != null) { Cell cell = row.createCell(5); cell.setCellValue(fiche.fiche_date_min.getTime()); cell.setCellStyle(cellStyleDate); } Cell cell = row.createCell(6); cell.setCellValue(fiche.fiche_date.getTime()); cell.setCellStyle(cellStyleDate); row.createCell(7).setCellValue(observation.observation_espece.espece_nom); Integer nombre = complements.informations_complementaires_nombre_de_specimens; if (nombre == null) row.createCell(8).setCellValue("?"); else row.createCell(8).setCellValue(nombre); row.createCell(9).setCellValue(complements.informations_complementaires_stade_sexe.stade_sexe_intitule); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = fiche.getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(10).setCellValue(membres.toString()); row.createCell(11).setCellValue(fiche.fiche_memo); row.createCell(12) .setCellValue(observation.observation_espece.espece_sous_groupe.sous_groupe_groupe.groupe_nom); i++; } for (int j = 0; j < 11; j++) sheet.autoSizeColumn(j); }
From source file:functions.excels.exports.MaChronologieExcel.java
License:Apache License
public MaChronologieExcel(Map<String, String> info, MaChronologie maChronologie) { super();//w ww .j a v a 2 s . c o m Sheet sheet = wb.createSheet("Ma chronologie"); String temoin = info.get("temoin"); String titre = "Chronologie des tmoignages "; titre += " dposs par " + temoin; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("Fiche ID"); rowHead.createCell(1).setCellValue("UTM"); rowHead.createCell(2).setCellValue("Lieu-dit"); rowHead.createCell(3).setCellValue("Commune"); rowHead.createCell(4).setCellValue("Dp."); rowHead.createCell(5).setCellValue("Date min"); rowHead.createCell(6).setCellValue("Date"); rowHead.createCell(7).setCellValue("Espce"); rowHead.createCell(8).setCellValue("Nombre"); rowHead.createCell(9).setCellValue("Stade/Sexe"); rowHead.createCell(10).setCellValue("Tmoins"); rowHead.createCell(11).setCellValue("Mmo"); rowHead.createCell(12).setCellValue("Groupe"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (Observation observation : maChronologie.chronologie) { for (InformationsComplementaires complement : observation.getInfos()) { Row row = sheet.createRow(i); Fiche fiche = observation.observation_fiche; row.createCell(0).setCellValue(fiche.fiche_id); row.createCell(1).setCellValue(fiche.fiche_utm.utm); row.createCell(2).setCellValue(fiche.fiche_lieudit); if (fiche.fiche_commune != null) { row.createCell(3).setCellValue(fiche.fiche_commune.ville_nom_aer); row.createCell(4).setCellValue(fiche.fiche_commune.ville_departement.departement_code); } if (fiche.fiche_date_min != null) { Cell cell = row.createCell(5); cell.setCellValue(fiche.fiche_date_min.getTime()); cell.setCellStyle(cellStyleDate); } Cell cell = row.createCell(6); cell.setCellValue(fiche.fiche_date.getTime()); cell.setCellStyle(cellStyleDate); row.createCell(7).setCellValue(observation.observation_espece.espece_nom); Integer nombre = complement.informations_complementaires_nombre_de_specimens; if (nombre == null) row.createCell(8).setCellValue("?"); else row.createCell(8).setCellValue(nombre); row.createCell(9) .setCellValue(complement.informations_complementaires_stade_sexe.stade_sexe_intitule); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = fiche.getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(10).setCellValue(membres.toString()); row.createCell(11).setCellValue(fiche.fiche_memo); row.createCell(12).setCellValue( observation.observation_espece.espece_sous_groupe.sous_groupe_groupe.groupe_nom); i++; } } for (int j = 0; j < 11; j++) sheet.autoSizeColumn(j); }
From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java
License:Open Source License
/** * @param args/*from w ww . j ava 2 s . c o m*/ * @throws InvalidFormatException * @throws IOException */ public static void run(String inputfile, String outputfile) throws IOException { InputStream in = new BufferedInputStream(new FileInputStream(inputfile)); try { Workbook wbIn = new HSSFWorkbook(in); File outFn = new File(outputfile); if (outFn.exists()) { outFn.delete(); } Workbook wbOut = new XSSFWorkbook(); int sheetCnt = wbIn.getNumberOfSheets(); for (int i = 0; i < sheetCnt; i++) { Sheet sIn = wbIn.getSheetAt(0); Sheet sOut = wbOut.createSheet(sIn.getSheetName()); Iterator<Row> rowIt = sIn.rowIterator(); while (rowIt.hasNext()) { Row rowIn = rowIt.next(); Row rowOut = sOut.createRow(rowIn.getRowNum()); Iterator<Cell> cellIt = rowIn.cellIterator(); while (cellIt.hasNext()) { Cell cellIn = cellIt.next(); Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType()); switch (cellIn.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cellOut.setCellValue(cellIn.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cellOut.setCellValue(cellIn.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellOut.setCellFormula(cellIn.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cellOut.setCellValue(cellIn.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellOut.setCellValue(cellIn.getStringCellValue()); break; } { CellStyle styleIn = cellIn.getCellStyle(); CellStyle styleOut = cellOut.getCellStyle(); styleOut.setDataFormat(styleIn.getDataFormat()); } cellOut.setCellComment(cellIn.getCellComment()); // HSSFCellStyle cannot be cast to XSSFCellStyle // cellOut.setCellStyle(cellIn.getCellStyle()); } } } OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn)); try { wbOut.write(out); } finally { out.close(); } } finally { in.close(); } }
From source file:gregchen.Simulation.java
License:Open Source License
private void initializeExcelFile() { //short rownum; //Create a Data folder if it does not already exist File theDir = new File("Data"); //if the directory does not exist, create it if(!theDir.exists()) {// ww w .j a v a 2 s. c o m theDir.mkdir(); } // create a new file if(fileCount == 0) { File file = null; do { fileCount++; file = new File("Data/Data " + fileCount + ".xls"); }while(file.exists()); } try { excelOut = new FileOutputStream("Data/Data " + fileCount + ".xls"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } fileCount++; // create a new workbook wb = new HSSFWorkbook(); // create a new sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = 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 f.setFontHeightInPoints((short) 12); //make it blue f.setColor( (short)0xc ); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor( (short)Font.COLOR_RED ); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout( true ); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "Greg Chen"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // Make header cells r = s.createRow(0); c = r.createCell(7); c.setCellStyle(cs3); c.setCellValue("Initial number: " + initialNumber + " Number per release: " + numPerRelease + " Number Releases: " + numReleases + " Release Interval: " + releaseInterval + " Female lethal gene: " + fsRIDL); currentRow = 2; for (short cellnum = (short) 0; cellnum < 7; cellnum ++) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals String heading = null; switch(cellnum) { case 0: heading = "Day"; break; case 1: heading = "Total Population"; break; case 2: heading = "Male Population"; break; case 3: heading = "Female Population"; break; case 4: heading = "FF"; break; case 5: heading = "Ff"; break; case 6: heading = "ff"; break; } c.setCellValue(heading); // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" // make this column a bit wider if(cellnum > 0) { s.setColumnWidth((short) (cellnum), (short) ((300) / ((double) 1 / 20))); } r.setHeight((short) 800); } /* int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); r.setHeight((short) 0x249); // } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); */ // write the workbook to the output stream // close our file (don't blow out our file handles) }