Java tutorial
package nc.noumea.mairie.appock.services.impl; /*- * #%L * Logiciel de Gestion des approvisionnements et des stocks des fournitures administratives de la Mairie de Nouma * %% * Copyright (C) 2017 Mairie de Nouma, Nouvelle-Caldonie * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as * published by the Free Software Foundation, either version 3 of the * License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public * License along with this program. If not, see * <http://www.gnu.org/licenses/gpl-3.0.html>. * #L% */ import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.nio.file.Files; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.context.annotation.ScopedProxyMode; import org.zkoss.zul.Messagebox; import nc.noumea.mairie.appock.comparator.ArticleCatalogueComparator; import nc.noumea.mairie.appock.core.entity.AbstractEntity; import nc.noumea.mairie.appock.core.security.AppUser; import nc.noumea.mairie.appock.core.utility.AppockUtil; import nc.noumea.mairie.appock.entity.*; import nc.noumea.mairie.appock.repositories.AppUserRepository; import nc.noumea.mairie.appock.repositories.MouvementStockRepository; import nc.noumea.mairie.appock.services.*; import nc.noumea.mairie.appock.services.CommandeService; @org.springframework.stereotype.Service("exportExcelService") @Scope(value = "singleton", proxyMode = ScopedProxyMode.TARGET_CLASS) public class ExportExcelServiceImpl implements ExportExcelService { private static final int LIGNE_NUMERO_MARCHE_ET_SOLDE_COMMANDE = 5; @Autowired CommandeService commandeService; @Autowired CatalogueService catalogueService; @Autowired DownloadService downloadService; @Autowired AppUserRepository appUserRepository; @Autowired ConfigService configService; @Autowired MouvementStockRepository mouvementStockRepository; @Override public void genereExcelFournisseur(Commande commande) throws IOException { List<String> listeMessageErreurAdresseService = construitMessageErreurAdresseService(commande); if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(listeMessageErreurAdresseService)) { Messagebox.show(StringUtils.join(listeMessageErreurAdresseService, "\n"), "Erreur d'adresse", Messagebox.OK, Messagebox.ERROR); return; } Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map = commandeService .construitMapFournisseurServiceListeArticleDemande(commande); List<AbstractEntity> listeAbstractEntity = new ArrayList(map.keySet()); Comparator abstractEntityComparator = Comparator .comparing((AbstractEntity abstractEntity) -> abstractEntity.getLibelleCourt()); Collections.sort(listeAbstractEntity, abstractEntityComparator); File result = File.createTempFile(UUID.randomUUID().toString(), ".zip"); FileOutputStream fos = new FileOutputStream(result); ZipOutputStream zos = new ZipOutputStream(fos); for (AbstractEntity abstractEntity : listeAbstractEntity) { String nomFichierSansExtension = construitNomFichier(abstractEntity); File tempFile = createExcelTempFile(map, abstractEntity, nomFichierSansExtension); addToZipFile(tempFile, nomFichierSansExtension + ".xlsx", zos); tempFile.delete(); } zos.close(); fos.close(); downloadService.downloadToUser(result, "Fichiers fournisseurs.zip"); result.delete(); } private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map, AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException { Workbook wb = new XSSFWorkbook(); Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity); for (Service service : mapServiceListeArticleDemande.keySet()) { Sheet sheet = wb .createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt()); sheet.protectSheet(configService.getMotDePasseVerrouExcel()); construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet); remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet); // Taille automatique des colonnes selon le contenu for (int i = 0; i < 20; i++) { sheet.autoSizeColumn(i); sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768); } } // Ecriture dans le fichier File result = File.createTempFile(nomFichierSansExtension, ".xlsx"); FileOutputStream fileStream = new FileOutputStream(result); wb.write(fileStream); return result; } private String construitNomFichier(AbstractEntity abstractEntity) { String nomFichier; if (abstractEntity instanceof SousMarche) { nomFichier = ((SousMarche) abstractEntity).getLibelle(); } else if (abstractEntity instanceof Fournisseur) { nomFichier = ((Fournisseur) abstractEntity).getNom(); } else { nomFichier = "SANS MARCHE NI FOURNISSEUR"; } return AppockUtil.replaceAllSpecialCharacter(nomFichier); } private List<String> construitMessageErreurAdresseService(Commande commande) throws IOException { Map<Service, List<Demande>> map = commandeService.construitMapServiceListeDemande(commande); List<String> result = new ArrayList<>(); for (Service service : map.keySet()) { if (service.getAdresse().isVide()) { result.add("Vous devez renseigner l'adresse du service " + service.getLibelleCourt()); } } return result; } private void construitEntete(Workbook wb, Map<Service, List<ArticleDemande>> map, AbstractEntity abstractEntity, Service service, Sheet sheet) { construitEnteteService(wb, service, sheet); construitEnteteContactLivraison(wb, service, sheet); construitEnteteAdresseLivraison(wb, service, sheet); construitEnteteSoldeCommande(wb, sheet); construitEnteteNumeroMarcheEtSoldeCommande(wb, map, abstractEntity, service, sheet); construitEnteteMarche(wb, abstractEntity, sheet); construitEnteteArticle(wb, sheet); } private void construitEnteteService(Workbook wb, Service service, Sheet sheet) { Row row = createRowGeneric(sheet, 1, 500); Cell cell = row.createCell(1); cell.setCellValue(service.getLibelle()); cell.setCellStyle( createCellWithBorderAndColor(wb, BorderStyle.MEDIUM, IndexedColors.LIGHT_TURQUOISE, true)); } private void construitEnteteContactLivraison(Workbook wb, Service service, Sheet sheet) { AppUser appUser = appUserRepository.findFirstByServiceAndTitulaire(service, true); if (appUser == null) { return; } Row row = createRowGeneric(sheet, 2, 500); Cell cell = row.createCell(1); List<String> listeInfoContact = new ArrayList<>(); listeInfoContact.add(appUser.getNomComplet()); if (StringUtils.isNotBlank(service.getTelephone())) { listeInfoContact.add(service.getTelephone()); // potentiellement null } if (StringUtils.isNotBlank(appUser.getPoste())) { listeInfoContact.add("Poste " + appUser.getPoste()); } cell.setCellValue(AppockUtil.joinListeStringNotBlank(listeInfoContact, " - ")); cell.setCellStyle(createCellWithBorderAndColor(wb, null, null, true)); } private void construitEnteteAdresseLivraison(Workbook wb, Service service, Sheet sheet) { Row row = createRowGeneric(sheet, 3, 500); Cell cell = row.createCell(1); cell.setCellValue(service.getAdresse().getReprMonoLigne()); cell.setCellStyle(createCellWithBorderAndColor(wb, null, null, true)); } private void construitEnteteSoldeCommande(Workbook wb, Sheet sheet) { Row row = createRowGeneric(sheet, 4, 500); Cell cell = row.createCell(6); cell.setCellValue("Montant de la commande"); cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_TURQUOISE, false)); } private void construitEnteteNumeroMarcheEtSoldeCommande(Workbook wb, Map<Service, List<ArticleDemande>> map, AbstractEntity abstractEntity, Service service, Sheet sheet) { Row row = createRowGeneric(sheet, LIGNE_NUMERO_MARCHE_ET_SOLDE_COMMANDE, 500); Cell cell = row.createCell(0); if (abstractEntity instanceof SousMarche) { cell.setCellValue("March Nouma n: " + ((SousMarche) abstractEntity).getNumero()); cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.TAN, false)); } // Cell Montant de la commande cell = row.createCell(6); int prixTotalService = 0; for (ArticleDemande articleDemande : map.get(service)) { prixTotalService += articleDemande.getTotalPrixCommande(); } cell.setCellValue(prixTotalService); CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, null, false); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); cell.setCellStyle(style); } private void construitEnteteMarche(Workbook wb, AbstractEntity abstractEntity, Sheet sheet) { Row row = createRowGeneric(sheet, 6, 500); Cell cell = row.createCell(0); cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.YELLOW, false)); if (abstractEntity instanceof SousMarche) { cell.setCellValue(((SousMarche) abstractEntity).getLibelle()); } cell = row.createCell(1); cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.YELLOW, false)); if (abstractEntity instanceof SousMarche) { cell.setCellValue(((SousMarche) abstractEntity).getFournisseur().getNom()); } else if (abstractEntity instanceof Fournisseur) { cell.setCellValue(((Fournisseur) abstractEntity).getNom()); } else { cell.setCellValue("SANS MARCHE NI FOURNISSEUR"); } } private Row createRowGeneric(Sheet sheet, int ligne, Integer taille) { Row row = sheet.createRow(ligne); if (taille != null) { row.setHeight((short) taille.intValue()); } return row; } private void construitEnteteArticle(Workbook wb, Sheet sheet) { Row row = createRowGeneric(sheet, 8, 500); row.createCell(0).setCellValue("Rfrence"); row.createCell(1).setCellValue("Dsignation"); row.createCell(2).setCellValue("Prix"); row.createCell(3).setCellValue("Colisage"); row.createCell(4).setCellValue("Commande"); row.createCell(5).setCellValue("Total"); CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.TAN, false); row.getCell(0).setCellStyle(style); row.getCell(1).setCellStyle(style); row.getCell(2).setCellStyle(style); row.getCell(3).setCellStyle(style); row.getCell(4).setCellStyle(style); row.getCell(5).setCellStyle(style); } private void remplitLigneArticle(Workbook wb, List<ArticleDemande> listeArticleDemande, Sheet sheet) { int numRow = 10; Map<ArticleCatalogue, Integer> mapArticleQuantite = new HashMap<>(); for (ArticleDemande articleDemande : listeArticleDemande) { ArticleCatalogue articleCatalogue = articleDemande.getArticleCatalogue(); Integer quantite = mapArticleQuantite.get(articleCatalogue); if (quantite != null) { mapArticleQuantite.replace(articleCatalogue, quantite + articleDemande.getQuantiteCommande()); } else { mapArticleQuantite.put(articleCatalogue, articleDemande.getQuantiteCommande()); } } List<ArticleCatalogue> listeArticleCatalogue = new ArrayList(mapArticleQuantite.keySet()); Collections.sort(listeArticleCatalogue, new ArticleCatalogueComparator()); for (ArticleCatalogue articleCatalogue : listeArticleCatalogue) { Row row = createRowGeneric(sheet, numRow, 500); row.createCell(0).setCellValue(articleCatalogue.getReference()); row.createCell(1).setCellValue(articleCatalogue.getLibelle()); row.createCell(2).setCellValue(articleCatalogue.getPrix()); row.createCell(3).setCellValue(articleCatalogue.getLibelleColisage()); row.createCell(4).setCellValue(mapArticleQuantite.get(articleCatalogue)); String strFormula = "C" + (numRow + 1) + "*E" + (numRow + 1) + ""; Cell cell = row.createCell(5); cell.setCellType(CellType.FORMULA); cell.setCellFormula(strFormula); CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW, false); CellStyle stylePrix = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW, false); stylePrix.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); row.getCell(0).setCellStyle(style); row.getCell(1).setCellStyle(style); row.getCell(2).setCellStyle(stylePrix); row.getCell(3).setCellStyle(style); row.getCell(4).setCellStyle(style); row.getCell(5).setCellStyle(stylePrix); numRow++; } } private CellStyle createCellWithBorderAndColor(Workbook wb, BorderStyle borderStyle, IndexedColors indexedColors, boolean bold) { CellStyle style = wb.createCellStyle(); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setAlignment(HorizontalAlignment.CENTER); if (borderStyle != null) { style.setBorderBottom(borderStyle); style.setBorderTop(borderStyle); style.setBorderLeft(borderStyle); style.setBorderRight(borderStyle); } if (indexedColors != null) { style.setFillForegroundColor(indexedColors.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (bold) { Font font = wb.createFont(); font.setBold(true); style.setFont(font); } return style; } private void addToZipFile(File file, String nomFichier, ZipOutputStream zos) throws IOException { FileInputStream fis = new FileInputStream(file); ZipEntry zipEntry = new ZipEntry(nomFichier); zos.putNextEntry(zipEntry); byte[] bytes = new byte[1024]; int length; while ((length = fis.read(bytes)) >= 0) { zos.write(bytes, 0, length); } zos.closeEntry(); fis.close(); } @Override public void exportCatalogue(Catalogue catalogue) throws IOException { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Feuil1"); construitEnteteExportCatalogue(wb, sheet); int i = 1; for (Famille famille : catalogue.getListeFamille()) { for (SousFamille sousFamille : famille.getListeSousFamille()) { for (ArticleCatalogue articleCatalogue : sousFamille.getListeArticleCatalogue()) { remplitLigneArticleExportCatalogue(wb, sheet, articleCatalogue, i++); } } } // Taille automatique des colonnes selon le contenu for (int colonne = 0; colonne <= 12; colonne++) { sheet.autoSizeColumn(colonne); sheet.setColumnWidth(colonne, sheet.getColumnWidth(colonne) + 768); } // Ecriture dans le fichier File result = File.createTempFile("Export Catalogue", ".xlsx"); FileOutputStream fileStream = new FileOutputStream(result); wb.write(fileStream); downloadService.downloadToUser(result, "Export Catalogue " + AppockUtil.replaceAllSpecialCharacter(catalogue.getLibelle()) + ".xlsx"); result.delete(); } private void construitEnteteExportCatalogue(Workbook wb, Sheet sheet) { Row row = createRowGeneric(sheet, 0, 1000); construitEnteteExportCatalogueGeneric(wb, row, "Famille", 0); construitEnteteExportCatalogueGeneric(wb, row, "Sous-Famille", 1); construitEnteteExportCatalogueGeneric(wb, row, "Rfrence", 2); construitEnteteExportCatalogueGeneric(wb, row, "Dsignation", 3); construitEnteteExportCatalogueGeneric(wb, row, "Prix unitaire", 4); construitEnteteExportCatalogueGeneric(wb, row, "Quantit", 5); construitEnteteExportCatalogueGeneric(wb, row, "Colisage", 6); construitEnteteExportCatalogueGeneric(wb, row, "March", 7); construitEnteteExportCatalogueGeneric(wb, row, "Sous-March", 8); construitEnteteExportCatalogueGeneric(wb, row, "Fournisseur", 9); construitEnteteExportCatalogueGeneric(wb, row, "Photo", 10); construitEnteteExportCatalogueGeneric(wb, row, "Lien vers site fournisseur", 11); } private void construitEnteteExportCatalogueGeneric(Workbook wb, Row row, String libelle, int colonne) { Cell cell = row.createCell(colonne); cell.setCellValue(libelle); cell.setCellStyle( createCellWithBorderAndColor(wb, BorderStyle.MEDIUM, IndexedColors.LIGHT_TURQUOISE, true)); } private void remplitLigneArticleExportCatalogue(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue, int ligne) throws IOException { Row row = createRowGeneric(sheet, ligne, 1000); boolean mouvementStock = CollectionUtils .isNotEmpty(mouvementStockRepository.findAllByReference(articleCatalogue.getReference())); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousFamille().getFamille().getLibelle(), 0, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousFamille().getLibelle(), 1, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getReference(), 2, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getLibelle(), 3, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getPrix().toString(), 4, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getQuantiteColisage() != null ? articleCatalogue.getQuantiteColisage().toString() : null, 5, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getTypeColisage() != null ? articleCatalogue.getTypeColisage().toString() : null, 6, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousMarche() != null ? articleCatalogue.getSousMarche().getMarche().getLibelle() : null, 7, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousMarche() != null ? articleCatalogue.getSousMarche().getLibelleCourt() : null, 8, mouvementStock); construitLigneExportCatalogueGeneric(wb, row, (articleCatalogue.getSousMarche() == null && articleCatalogue.getFournisseur() != null) ? articleCatalogue.getFournisseur().getNom() : null, 9, mouvementStock); if (articleCatalogue.getPhotoArticleCatalogue() != null) { insertPhotoArticleCatalogueInCell(wb, sheet, articleCatalogue, ligne, row, mouvementStock); } construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getLienFournisseur(), 11, mouvementStock); } private void insertPhotoArticleCatalogueInCell(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue, int ligne, Row row, boolean mouvementStock) throws IOException { File fichierPhotoArticleCatalogue = catalogueService .getFilePieceJointe(articleCatalogue.getPhotoArticleCatalogue()); byte[] bytes = Files.readAllBytes(fichierPhotoArticleCatalogue.toPath()); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper helper = wb.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(10); anchor.setRow1(ligne); anchor.setCol2(11); anchor.setRow2(ligne + 1); drawing.createPicture(anchor, pictureIdx); construitLigneExportCatalogueGeneric(wb, row, null, 10, mouvementStock); } private void construitLigneExportCatalogueGeneric(Workbook wb, Row row, String libelle, int colonne, boolean mouvementStock) { Cell cell = row.createCell(colonne); cell.setCellValue(libelle); cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.MEDIUM, mouvementStock ? IndexedColors.LIGHT_GREEN : null, false)); } }