List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat
DataFormat createDataFormat();
From source file:data.control.dataSheet.java
private void newPatient(Patient newPatient) { XSSFRow newRow;//from www. j a v a2s .c om // creating a new row if (firstSheet.getRow(newPatient.getId()) == null) { newRow = firstSheet.createRow(newPatient.getId()); } else { newRow = firstSheet.getRow(newPatient.getId()); } // creating a new cell XSSFCell cell = newRow.getCell(0); if (cell == null) { cell = newRow.createCell(0); } // adding the patient ID cell.setCellValue(newPatient.getId()); // adding the patient Name // creating a new cell cell = newRow.getCell(1); if (cell == null) { cell = newRow.createCell(1); } cell.setCellValue(newPatient.getName()); // adding BPM if (!newPatient.getBPMArray().isEmpty()) { int cellID = 2; for (Iterator itr = newPatient.getBPMArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding Tempreature if (!newPatient.getTempArray().isEmpty()) { int cellID = 7; for (Iterator itr = newPatient.getTempArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding the patient blood type cell = newRow.getCell(12); if (cell == null) { cell = newRow.createCell(12); } cell.setCellValue(newPatient.getBloodType()); // adding the patient sex cell = newRow.getCell(13); if (cell == null) { cell = newRow.createCell(13); } cell.setCellValue(newPatient.getSex()); // adding the patient age cell = newRow.getCell(14); if (cell == null) { cell = newRow.createCell(14); } cell.setCellValue(newPatient.getAge()); // setting up the date format XSSFCellStyle cellStyle = theWorkbook.createCellStyle(); CreationHelper createHelper = theWorkbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy hh:mm:ss")); // adding the patient dateAdded cell = newRow.getCell(15); if (cell == null) { cell = newRow.createCell(15); } if (newPatient.getDateCreated() != null) { cell.setCellValue(newPatient.getDateCreated()); } else { cell.setCellValue(new Date()); } cell.setCellStyle(cellStyle); // adding the date modified if (newPatient.getLastModified() != null) { cell = newRow.getCell(16); if (cell == null) { cell = newRow.createCell(16); } cell.setCellValue(newPatient.getLastModified()); cell.setCellStyle(cellStyle); } // adding the lastAlarm if (newPatient.getLastAlarmed() != null) { cell = newRow.getCell(17); if (cell == null) { cell = newRow.createCell(17); } cell.setCellValue(newPatient.getLastAlarmed()); cell.setCellStyle(cellStyle); } }
From source file:de.ks.idnadrev.expimp.xls.ReflectionColumn.java
License:Apache License
@Override public CellStyle getCellStyle(SXSSFWorkbook workbook) { CreationHelper creationHelper = workbook.getCreationHelper(); if (LocalDateTime.class.isAssignableFrom(fieldType)) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); return cellStyle; } else if (LocalDate.class.isAssignableFrom(fieldType)) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd")); return cellStyle; }// w w w . jav a2 s.co m return null; }
From source file:facturasdiferidas.frmFacturasDif.java
private void ExportarExcel(ResultSet rs) throws IOException, SQLException { JFileChooser file = new JFileChooser(); FileNameExtensionFilter filtro = new FileNameExtensionFilter("Excel(*.XLSX)", "xlsx"); file.setFileFilter(filtro);/* w w w . j a v a 2s .c om*/ int seleccion = file.showSaveDialog(this); if (seleccion == JFileChooser.CANCEL_OPTION) { return; } File guarda = file.getSelectedFile(); //file.set if (guarda != null) { // file.setFileFilter(filtro); /*guardamos el archivo y le damos el formato directamente, * si queremos que se guarde en formato doc lo definimos como .doc*/ rutaArchivo = guarda.getAbsolutePath() + ".xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } else { // file.setFileFilter(filtro); rutaArchivo = System.getProperty("user.home") + "/Kardex.xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } /*Se crea el objeto de tipo File con la ruta del archivo*/ archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete(); // try { /*Se crea el archivo*/ //archivoXLS. archivoXLS.createNewFile(); libro = new XSSFWorkbook(); archivo = new FileOutputStream(archivoXLS); //archivo.close(); CreationHelper createhelper = libro.getCreationHelper(); cellStyle = libro.createCellStyle(); cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy")); fuente = libro.createFont(); fuente.setFontHeightInPoints((short) 10); fuente.setFontName("Calibri"); fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente.setBold(true); fuente3 = libro.createFont(); fuente3.setFontHeightInPoints((short) 8); fuente3.setFontName("Calibri"); fuente3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente2 = libro.createFont(); fuente2.setFontHeightInPoints((short) 9); fuente2.setFontName("Calibri"); //fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); CellStyle cellStyle2 = libro.createCellStyle(); cellStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); cellStyle2.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle2.setFont(fuente); cellStyle2.setWrapText(true); CellStyle cellStyle3 = libro.createCellStyle(); cellStyle3.setAlignment(XSSFCellStyle.ALIGN_LEFT); cellStyle3.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle3.setFont(fuente); CellStyle cellStyle4 = libro.createCellStyle(); // cellStyle4.setAlignment(XSSFCellStyle. ALIGN_LEFT); // cellStyle4.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle4.setFont(fuente); hoja = libro.createSheet("FacturasDiferidas"); //hoja. hoja.setDefaultRowHeightInPoints(12); Row fila = hoja.createRow(0); Cell celda = fila.createCell(0); celda.setCellValue("TIENDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(1); celda.setCellValue("TIPO_DOC"); celda.setCellStyle(cellStyle4); celda = fila.createCell(2); celda.setCellValue("DOC_DIFERIDO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(3); celda.setCellValue("FECHA_EMI"); celda.setCellStyle(cellStyle4); celda = fila.createCell(4); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(5); celda.setCellValue("NOMBRE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(6); celda.setCellValue("COD_PROD"); celda.setCellStyle(cellStyle4); celda = fila.createCell(7); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(8); celda.setCellValue("MONEDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(9); celda.setCellValue("PRECIO_UNITARIO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(10); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle4); celda = fila.createCell(11); celda.setCellValue("CANT_FACTURADA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(12); celda.setCellValue("SALDO_TOTAL_X_ATENDER"); celda.setCellStyle(cellStyle4); celda = fila.createCell(13); celda.setCellValue("CANTIDAD_ATENDIDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(14); celda.setCellValue("TIPO_DOC_A"); celda.setCellStyle(cellStyle4); celda = fila.createCell(15); celda.setCellValue("DOCUMENTO_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(16); celda.setCellValue("FECHA_GUIA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(17); celda.setCellValue("TIENDA_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(18); celda.setCellValue("ALMACEN_ATIENDE"); celda.setCellStyle(cellStyle4); f = 1; reloj = new Timer(delay, new EjecutarTarea2()); reloj.start(); // // // while (rs.next()){ // // } }
From source file:functions.excels.exports.CarteSommeBiodiversiteExcel.java
License:Apache License
public CarteSommeBiodiversiteExcel(Map<String, String> info, CarteSommeBiodiversite csb) { super();// w w w. ja v a 2 s. c o m 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();/* ww w .j a v a 2 s . c om*/ 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();// ww w . j ava 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:guru.qas.martini.report.DefaultState.java
License:Apache License
@Override public void updateSuites(Sheet sheet) { int lastRowNum = sheet.getLastRowNum(); Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1); row.createCell(0, CellType.STRING).setCellValue("ID"); row.createCell(1, CellType.STRING).setCellValue("Date"); row.createCell(2, CellType.STRING).setCellValue("Name"); row.createCell(3, CellType.STRING).setCellValue("Hostname"); row.createCell(4, CellType.STRING).setCellValue("IP"); row.createCell(5, CellType.STRING).setCellValue("Username"); row.createCell(6, CellType.STRING).setCellValue("Profiles"); row.createCell(7, CellType.STRING).setCellValue("Environment Variables"); for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) { row = sheet.createRow(sheet.getLastRowNum() + 1); String id = mapEntry.getKey(); row.createCell(0, CellType.STRING).setCellValue(id); JsonObject suite = mapEntry.getValue(); JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp"); Long timestamp = null == primitive ? null : primitive.getAsLong(); Cell cell = row.createCell(1);/*from ww w . j ava2 s . c o m*/ if (null != timestamp) { Workbook workbook = sheet.getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper creationHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cellStyle.setVerticalAlignment(VerticalAlignment.TOP); cell.setCellValue(new Date(timestamp)); cell.setCellStyle(cellStyle); } cell = row.createCell(2); primitive = suite.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(3); JsonObject host = suite.getAsJsonObject("host"); primitive = null == host ? null : host.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(4); primitive = null == host ? null : host.getAsJsonPrimitive("ip"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(5); primitive = null == host ? null : host.getAsJsonPrimitive("username"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(6); JsonArray array = suite.getAsJsonArray("profiles"); List<String> profiles = Lists.newArrayList(); if (null != array) { int size = array.size(); for (int i = 0; i < size; i++) { JsonElement element = array.get(i); String profile = null == element ? null : element.getAsString(); profiles.add(profile); } String profilesValue = Joiner.on('\n').skipNulls().join(profiles); cell.setCellValue(profilesValue); } cell = row.createCell(7); JsonObject environmentVariables = suite.getAsJsonObject("environment"); Map<String, String> index = new TreeMap<>(); if (null != environmentVariables) { Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet(); for (Map.Entry<String, JsonElement> environmentEntry : entries) { String key = environmentEntry.getKey(); JsonElement element = environmentEntry.getValue(); String value = null == element ? "" : element.getAsString(); index.put(key, value); } String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index); cell.setCellValue(variablesValue); } } for (int i = 0; i < 8; i++) { sheet.autoSizeColumn(i, false); } }
From source file:it.cineca.pst.huborcid.web.rest.ReportatFileResource.java
License:Open Source License
/** * GET /reportat -> get all the relPersonApplications. *///from w ww . jav a 2s . c o m @RequestMapping(value = "/reportat/downloadExcel", method = RequestMethod.GET) @Timed public void getExcel(HttpServletResponse response) throws URISyntaxException { String currentLogin = SecurityUtils.getCurrentLogin(); Application application = applicationRepository.findOneByApplicationID(currentLogin); Sort sort = new Sort(Sort.Direction.ASC, "person.localID"); List<RelPersonApplication> listAccessToken = relPersonApplicationRepository .findAllByLastIsTrueAndApplicationIs(application, sort); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Report Access Token"); Object[] headerExcel = new Object[] { "LOCAL ID", "ORCID", "ORCID ASSOCIATION DATE", "ORCID ACCESS TOKEN", "ORCID ACCESS TOKEN RELEASED DATE" }; Row rowHeader = sheet.createRow(0); int cellnumHeader = 0; //header for (Object obj : headerExcel) { Cell cell = rowHeader.createCell(cellnumHeader++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } //data CellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); int rownum = 1; for (int i = 0; i < listAccessToken.size(); i++) { RelPersonApplication relPerson = listAccessToken.get(i); Row rowData = sheet.createRow(rownum++); int cellnumData = 0; //localid Cell cellLocalId = rowData.createCell(cellnumData++); cellLocalId.setCellValue(relPerson.getPerson().getLocalID()); //orcid Cell cellOrcid = rowData.createCell(cellnumData++); cellOrcid.setCellValue(relPerson.getPerson().getOrcid()); //orcidCreated Cell cellOrcidCreated = rowData.createCell(cellnumData++); if (relPerson.getPerson().getOrcidReleaseDate() != null) { cellOrcidCreated.setCellValue(relPerson.getPerson().getOrcidReleaseDate().toDate()); cellOrcidCreated.setCellStyle(cellStyle); } //orcid access token Cell callAccessToken = rowData.createCell(cellnumData++); if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) { callAccessToken.setCellValue(relPerson.getOauthAccessToken()); } else { callAccessToken.setCellValue((String) null); } //access token Created Cell cellAccessTokenCreated = rowData.createCell(cellnumData++); if (relPerson.getDateReleased() != null) { if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) { cellAccessTokenCreated.setCellValue(relPerson.getDateReleased().toDate()); cellAccessTokenCreated.setCellStyle(cellStyle); } else { //cellAccessTokenCreated.setCellValue((Date)null); cellAccessTokenCreated.setCellStyle(cellStyle); } } // //FIXME quando verr gestita la revoca // //denied // Cell cellDenied = rowData.createCell(cellnumData++); // if(relPerson.getDenied()!=null) // cellDenied.setCellValue(new Boolean(null)); } //autosize for (int i = 0; i < headerExcel.length; i++) { sheet.autoSizeColumn(i); } try { workbook.write(response.getOutputStream()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
public CellStyle getNumberFormat(int j, Map<Integer, CellStyle> decimalFormats, Sheet sheet, CreationHelper createHelper, CellType celltype) { int mapPosition = j; if (celltype.equals(CellType.TOTAL)) { mapPosition = j + 90000;//from ww w . j a va 2 s . c o m } else if (celltype.equals(CellType.SUBTOTAL)) { mapPosition = j + 80000; } else if (celltype.equals(CellType.CF)) { mapPosition = j + 60000; } if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition); if (celltype.equals(CellType.CF)) { j = this.getCalculatedFieldDecimals(); } String decimals = ""; for (int i = 0; i < j; i++) { decimals += "0"; } CellStyle cellStyle = this.buildDataCellStyle(sheet); DataFormat df = createHelper.createDataFormat(); String format = "#,##0"; if (decimals.length() > 0) { format += "." + decimals; } cellStyle.setDataFormat(df.getFormat(format)); if (celltype.equals(CellType.TOTAL)) { cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); } if (celltype.equals(CellType.CF)) { cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex()); } if (celltype.equals(CellType.SUBTOTAL)) { cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); } decimalFormats.put(mapPosition, cellStyle); return cellStyle; }
From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java
License:Mozilla Public License
public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes, int beginRowData, int beginColumnData) { CellStyle dCellStyle = this.buildCellStyle(sheet); Iterator it = dataStore.iterator(); int rownum = beginRowData; short formatIndexInt = this.getBuiltinFormat("#,##0"); CellStyle cellStyleInt = this.buildCellStyle(sheet); // cellStyleInt is the default cell style for integers cellStyleInt.cloneStyleFrom(dCellStyle); cellStyleInt.setDataFormat(formatIndexInt); CellStyle cellStyleDate = this.buildCellStyle(sheet); // cellStyleDate is the default cell style for dates cellStyleDate.cloneStyleFrom(dCellStyle); cellStyleDate.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); IMetaData d = dataStore.getMetaData(); while (it.hasNext()) { Row rowVal = sheet.getRow(rownum); IRecord record = (IRecord) it.next(); List fields = record.getFields(); int length = fields.size(); for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) { IField f = (IField) fields.get(fieldIndex); if (f != null && f.getValue() != null) { Class c = d.getFieldType(fieldIndex); logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]"); if (rowVal == null) { rowVal = sheet.createRow(rownum); }/* w w w. j av a2 s. co m*/ Cell cell = rowVal.createCell(fieldIndex + beginColumnData); cell.setCellStyle(dCellStyle); if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]"); IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex); String scaleFactor = (String) fieldMetaData.getProperty( WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR); Number val = (Number) f.getValue(); Double doubleValue = MeasureScaleFactorOption.applyScaleFactor(val.doubleValue(), scaleFactor); cell.setCellValue(doubleValue); cell.setCellType(this.getCellTypeNumeric()); cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt); } else if (Number.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]"); IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex); String decimalPrecision = (String) fieldMetaData .getProperty(IFieldMetaData.DECIMALPRECISION); CellStyle cs; if (decimalPrecision != null) { cs = getDecimalNumberFormat(new Integer(decimalPrecision), sheet, createHelper, dCellStyle); } else { cs = getDecimalNumberFormat(DEFAULT_DECIMAL_PRECISION, sheet, createHelper, dCellStyle); } Number val = (Number) f.getValue(); Double value = val.doubleValue(); String scaleFactor = (String) fieldMetaData.getProperty( WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR); cell.setCellValue(MeasureScaleFactorOption.applyScaleFactor(value, scaleFactor)); cell.setCellType(this.getCellTypeNumeric()); cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cs); } else if (String.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]"); String val = (String) f.getValue(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(this.getCellTypeString()); } else if (Boolean.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]"); Boolean val = (Boolean) f.getValue(); cell.setCellValue(val.booleanValue()); cell.setCellType(this.getCellTypeBoolean()); } else if (Date.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]"); Date val = (Date) f.getValue(); cell.setCellValue(val); cell.setCellStyle(cellStyleDate); } else { logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]"); String val = f.getValue().toString(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(this.getCellTypeString()); } } } rownum++; } }