Java tutorial
package nc.noumea.mairie.appock.util; /*- * #%L * Logiciel de Gestion des approvisionnements et des stocks des fournitures administratives de la Mairie de * Nouma * %% * Copyright (C) 2017 - 2018 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 nc.noumea.mairie.appock.entity.ArticleStock; import nc.noumea.mairie.appock.entity.Service; import nc.noumea.mairie.appock.services.CatalogueService; import org.apache.commons.io.IOUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Units; import org.apache.poi.xssf.usermodel.*; import org.docx4j.openpackaging.exceptions.Docx4JException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.xml.bind.JAXBException; import java.io.*; import java.util.List; public class StockSpreadsheetExporter { private static Logger log = LoggerFactory.getLogger(StockSpreadsheetExporter.class); private static final int ROW_HEIGHT_TWIPS = 1400; // in "twips" or 1/20th of a point. private static final int COLUMN_WIDTH_PX = 100; public static void exportToXls(Service service, List<ArticleStock> articleStockList, CatalogueService catalogueService, OutputStream out) throws JAXBException, Docx4JException, IOException { createSpreadsheet(service, articleStockList, catalogueService, out); } public static void createSpreadsheet(Service service, List<ArticleStock> articleStockList, CatalogueService catalogueService, OutputStream out) throws JAXBException, Docx4JException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("Inventaire"); Integer rowNumber = 0; rowNumber = generateHeader(worksheet, workbook, rowNumber); generateLineOfStock(worksheet, workbook, articleStockList, catalogueService, rowNumber); autosize(workbook); workbook.write(out); out.flush(); out.close(); } private static int createTitle(XSSFWorkbook workbook, XSSFSheet worksheet, Service service) { XSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 11); txtFont.setBold(true); titleStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue( "Inventaire " + service.getDirection().getLibelleCourt() + " - " + service.getLibelleCourt()); cell.setCellStyle(titleStyle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); return 2; } private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) { // Now add XSSFRow row = worksheet.createRow(rowNum); XSSFCell cell; XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setBorderBottom(BorderStyle.MEDIUM); headerStyle.setBorderLeft(BorderStyle.MEDIUM); headerStyle.setBorderRight(BorderStyle.MEDIUM); headerStyle.setBorderTop(BorderStyle.MEDIUM); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(true); headerStyle.setFont(txtFont); cell = row.createCell(0); cell.setCellValue("Photo"); cell.setCellStyle(headerStyle); worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387 cell = row.createCell(1); cell.setCellValue("Rfrence"); cell.setCellStyle(headerStyle); cell = row.createCell(2); cell.setCellValue("Libell"); cell.setCellStyle(headerStyle); cell = row.createCell(3); cell.setCellValue("Stock\n Appock"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); cell = row.createCell(4); cell.setCellValue("Stock\n rel"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); row.setHeight((short) 600); return rowNum + 1; } private static void generateLineOfStock(XSSFSheet worksheet, XSSFWorkbook workbook, List<ArticleStock> articlesList, CatalogueService catalogueService, int rowNum) throws IOException { for (ArticleStock article : articlesList) { createRow(worksheet, workbook, article, catalogueService, rowNum); rowNum = rowNum + 1; } } private static void addImage(XSSFWorkbook workbook, XSSFSheet worksheet, File file, int rowNum) throws IOException { //add picture data to this workbook. InputStream is = new FileInputStream(file); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); is.close(); XSSFDrawing drawing = worksheet.createDrawingPatriarch(); //add a picture shape XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor(); anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(0); anchor.setRow1(rowNum); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); //get the picture width int pictWidthPx = pict.getImageDimension().width; int pictHeightPt = pict.getImageDimension().height; //get the cell width float cellWidthPx = worksheet.getColumnWidthInPixels(0); float cellHeightPx = ConvertImageUnits.heightUnits2Pixel(worksheet.getRow(rowNum).getHeight()); //calculate the center position int centerPosPx = Math.round(cellWidthPx / 2f - (float) pictWidthPx / 2f); int centerPosPy = Math.round(cellHeightPx / 2f - (float) pictHeightPt / 2f + 10); //set the new upper left anchor position anchor.setCol1(0); //set the remaining pixels up to the center position as Dx in unit EMU anchor.setDx1(centerPosPx * Units.EMU_PER_PIXEL); anchor.setDy1(centerPosPy * Units.EMU_PER_PIXEL); //resize the pictutre to original size again //this will determine the new bottom rigth anchor position pict.resize(); } private static void createRow(XSSFSheet worksheet, XSSFWorkbook workbook, ArticleStock article, CatalogueService catalogueService, int rowNumber) throws IOException { int col = 0; XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFCellStyle cellImageStyle = workbook.createCellStyle(); cellImageStyle.setBorderBottom(BorderStyle.THIN); cellImageStyle.setBorderLeft(BorderStyle.THIN); cellImageStyle.setBorderRight(BorderStyle.THIN); cellImageStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellImageStyle.setAlignment(HorizontalAlignment.CENTER); if (rowNumber != 1) { cellStyle.setBorderTop(BorderStyle.THIN); } XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(false); cellStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(rowNumber); row.setHeight((short) ROW_HEIGHT_TWIPS);//80px 1600 // Photo File image = null; try { image = catalogueService.getFilePieceJointe(article.getArticleCatalogue().getPhotoArticleCatalogue()); } catch (IllegalArgumentException e) { log.warn("No image to display for article " + article.getArticleCatalogue().getLibelle()); } XSSFCell cell = row.createCell(col); cell.setCellStyle(cellImageStyle); if (image != null) addImage(workbook, worksheet, image, rowNumber); col = col + 1; // Rfrence cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getReferenceArticleStock()); // Libell cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getArticleCatalogue().getLibelle()); // Appock Stock cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getQuantiteStock()); cell.setCellType(CellType.NUMERIC); // Stock reel cell = row.createCell(col); cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); } private static void autosize(Workbook workbook) { Row row = workbook.getSheetAt(0).getRow(1); for (int colNum = 1; colNum < row.getLastCellNum() - 1; colNum++) workbook.getSheetAt(0).autoSizeColumn(colNum); int appockStockColumnWidth = workbook.getSheetAt(0).getColumnWidth(row.getLastCellNum() - 1); workbook.getSheetAt(0).setColumnWidth(row.getLastCellNum(), appockStockColumnWidth); } }