Java tutorial
package com.solidmaps.webapp.report.utils; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.solidmaps.webapp.entity.InvoiceInventoryMapEntity; import com.solidmaps.webapp.entity.InvoiceProductEntity; import com.solidmaps.webapp.entity.ProviderEntity; import com.solidmaps.webapp.utils.DateUtils; public class ExcelMapCivilGenerator { private final String TEMPLATE_FILE = "map_templates/template_pc_sp.xlsx"; private String usedFile; private String newFile; private final String LIST_PRODUCT_KEY = "${list.products}"; private final String LIST_INVOICE_BUY_KEY = "${list.invoices.buy}"; private final String LIST_INVOICE_SELL_KEY = "${list.invoices.sell}"; private XSSFWorkbook wb; private Map<String, String> mapParameters; private List<InvoiceInventoryMapEntity> listInventory; private List<InvoiceProductEntity> listInvoiceBuy; private List<InvoiceProductEntity> listInvoiceSell; private XSSFCellStyle styleProductList; public ExcelMapCivilGenerator(String filePath, List<InvoiceInventoryMapEntity> listInventory, List<InvoiceProductEntity> listInvoiceBuy, List<InvoiceProductEntity> listInvoiceSell) throws IOException { this.listInventory = listInventory; this.listInvoiceBuy = listInvoiceBuy; this.listInvoiceSell = listInvoiceSell; this.usedFile = filePath + "USED_FILE.xlsx"; this.newFile = filePath; this.init(); } private ExcelMapCivilGenerator init() throws IOException { this.createNewFile(); wb = new XSSFWorkbook(usedFile); mapParameters = new HashMap<>(); this.loadStyle(); return this; } public Map<String, String> addParam(String key, String value) { String keyFormated = "${" + key + "}"; mapParameters.put(keyFormated, value); return mapParameters; } public String generate() throws Exception { this.addListProducts(); this.addListInvoices(listInvoiceBuy, LIST_INVOICE_BUY_KEY, 1); this.addListInvoices(listInvoiceSell, LIST_INVOICE_SELL_KEY, 2); this.executeReplaceKeyValues(); return this.createFinalFile(); } public XSSFSheet getSheet(int i) { return wb.getSheetAt(i); } private void executeReplaceKeyValues() { for (Map.Entry<String, String> entry : mapParameters.entrySet()) { String key = entry.getKey(); String value = entry.getValue(); this.replaceStringCell(key, value); } } private void addListProducts() { Cell referenceCell = this.findCell(LIST_PRODUCT_KEY).get(0); XSSFSheet sheetMoves = this.getSheet(0); int i = 0; XSSFRow row = null; for (InvoiceInventoryMapEntity inventory : listInventory) { row = sheetMoves.createRow(referenceCell.getRowIndex() + i++); this.createCellProduct(sheetMoves, row, 0, inventory.getProduct().getName()); this.createCell(row, 4, inventory.getQtdInventoryPreviusTrimester().toString()); this.createCell(row, 5, inventory.getQtdBuyProduct().toString()); this.createCell(row, 6, inventory.getQtdInventoryPreviusTrimester().add(inventory.getQtdBuyProduct()).toString()); this.createCellUtilization(sheetMoves, row, inventory.getQtdUtilization().toString()); this.createCellSell(sheetMoves, row, inventory.getQtdSellProduct().toString()); this.createCellNextTrimester(sheetMoves, row, inventory.getQtdBuyProduct().subtract(inventory.getQtdSellProduct()).toString()); this.createCell(row, 14, inventory.getTypeQtdProduct()); } this.addBotton(row, sheetMoves); } private void addListInvoices(List<InvoiceProductEntity> listInvoice, String keyInvoice, Integer numSheet) { if (listInvoice == null || listInvoice.isEmpty()) { return; } Cell referenceCell = this.findCell(keyInvoice).get(0); XSSFSheet sheet = this.getSheet(numSheet); int i = 0; XSSFRow rowProviderPhoneCity = null; for (InvoiceProductEntity productInvoice : listInvoice) { XSSFRow row = sheet.createRow(referenceCell.getRowIndex() + i++); this.createCell(row, 0, DateUtils.format(productInvoice.getInvoice().getDateCreate())); this.createCell(row, 1, productInvoice.getInvoice().getNumInvoice()); String qtdtypeQtdInvoice = productInvoice.getQtdProduct().toString() + " " + productInvoice.getTypeQtdProduct(); this.createCell(row, 2, qtdtypeQtdInvoice); this.createCell(row, 3, productInvoice.getProduct().getProductOfficial().getName()); // Fornecedor ProviderEntity provider = productInvoice.getInvoice().getProvider(); String providerName = provider != null ? productInvoice.getInvoice().getProvider().getName() : ""; String providerStreet = provider != null ? productInvoice.getInvoice().getProvider().getStreet() : ""; String providerCep = provider != null ? productInvoice.getInvoice().getProvider().getCep() : ""; String providerCity = provider != null ? productInvoice.getInvoice().getProvider().getCity() : ""; ProviderEntity providerCarrier = productInvoice.getInvoice().getProviderCarrier(); String providerCarrierName = providerCarrier != null ? productInvoice.getInvoice().getProviderCarrier().getName() : ""; String providerCarrierStreet = providerCarrier != null ? productInvoice.getInvoice().getProviderCarrier().getStreet() : ""; String providerCarrierCep = providerCarrier != null ? productInvoice.getInvoice().getProviderCarrier().getCep() : ""; String providerCarrierCity = providerCarrier != null ? productInvoice.getInvoice().getProviderCarrier().getCity() : ""; this.createCell(row, 4, "Razo Social"); this.createCell(row, 5, providerName); this.createCell(row, 7, "Razo Social"); this.createCell(row, 8, providerCarrierName); XSSFRow rowProviderStreet = sheet.createRow(referenceCell.getRowIndex() + i++); this.createCell(rowProviderStreet, 4, "Endereo/Cep"); this.createCell(rowProviderStreet, 5, providerStreet + " " + providerCep); this.createCell(rowProviderStreet, 7, "Endereo/Cep"); this.createCell(rowProviderStreet, 8, providerCarrierStreet + " " + providerCarrierCep); rowProviderPhoneCity = sheet.createRow(referenceCell.getRowIndex() + i++); this.createCell(rowProviderPhoneCity, 4, "Cidade/UF/Fone"); this.createCell(rowProviderPhoneCity, 5, providerCity + "/" + productInvoice.getInvoice().getCompany().getPhoneNumber()); this.createCell(rowProviderPhoneCity, 7, "Cidade/UF/Fone"); this.createCell(rowProviderPhoneCity, 8, providerCarrierCity + "/" + " " + productInvoice.getInvoice().getCompany().getPhoneNumber()); } this.addBotton(rowProviderPhoneCity, sheet); } private void createCell(XSSFRow row, Integer index, String value) { XSSFCell cell = row.createCell(index); cell.setCellStyle(styleProductList); cell.setCellValue(value); } private void createCellProduct(XSSFSheet sheetBuy, XSSFRow row, Integer index, String value) { XSSFCell cell = row.createCell(index); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 3)); cell.setCellStyle(styleProductList); cell.setCellValue(value); } private void createCellUtilization(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(7); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 7, 8)); cell.setCellStyle(styleProductList); cell.setCellValue(value); } private void createCellSell(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(9); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 9, 11)); cell.setCellStyle(styleProductList); cell.setCellValue(value); } private void createCellNextTrimester(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(12); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 12, 13)); cell.setCellStyle(styleProductList); cell.setCellValue(value); } private String createFinalFile() throws Exception { String fileName = "Mapa Policia Civil - " + Math.random() + ".xlsx"; String filePathName = newFile + fileName; FileOutputStream fileOut = new FileOutputStream(filePathName); wb.write(fileOut); fileOut.close(); wb.close(); return fileName; } private void createNewFile() throws IOException { ClassLoader classLoader = ExcelMapCivilGenerator.class.getClassLoader(); File templateFile = new File(classLoader.getResource(TEMPLATE_FILE).getFile()); FileUtils.copyFile(templateFile, FileUtils.getFile(usedFile)); } private List<Cell> findCell(String key) { List<Cell> listCell = new ArrayList<Cell>(); Integer sheetNumber = wb.getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = wb.getSheetAt(i); for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(key)) { listCell.add(cell); } } } } } return listCell; } private void replaceStringCell(String key, String value) { List<Cell> listCells = this.findCell(key); for (Cell cell : listCells) { String strReplace = cell.getStringCellValue().replace(key, value); cell.setCellValue(strReplace); } } private void loadStyle() { styleProductList = wb.createCellStyle(); styleProductList.setBorderRight(HSSFCellStyle.BORDER_THIN); styleProductList.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleProductList.setBorderTop(HSSFCellStyle.BORDER_THIN); styleProductList.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleProductList.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont txtFont = wb.createFont(); txtFont.setFontName("Arial"); txtFont.setFontHeightInPoints((short) 8); styleProductList.setFont(txtFont); } private void addBotton(XSSFRow lastRowReference, XSSFSheet sheet) { int colField = 5; int colValue = 6; int intRef = 4; XSSFRow rowTerms = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowTerms.createCell(0).setCellValue("Ateno: O mapa dever ser entregue em 3 vias em at 10 dias"); rowTerms.createCell(colField).setCellValue("O que declaro a verdade, sob as penas da Lei."); XSSFRow rowCityDate = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowCityDate.createCell(0).setCellValue("aps o trmino de cada trimestre."); rowCityDate.createCell(colField).setCellValue("Cidade/data:"); rowCityDate.createCell(colValue).setCellValue("${date.city} - ${date.format}"); XSSFRow rowEmpty = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowEmpty.createCell(0); XSSFRow rowSignature = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowSignature.createCell(colField).setCellValue("Assinatura:"); rowSignature.createCell(colValue).setCellValue("____________________________________"); XSSFRow rowResponsable = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowResponsable.createCell(colField).setCellValue("Nome:"); rowResponsable.createCell(colValue).setCellValue("${license.responsable.name}"); XSSFRow rowOffice = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowOffice.createCell(colField).setCellValue("Cargo:"); rowOffice.createCell(colValue).setCellValue("${license.responsable.office}"); XSSFRow rowRG = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowRG.createCell(colField).setCellValue("R.G. N:"); rowRG.createCell(colValue).setCellValue("${license.responsable.rg}"); } }