Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package se.nrm.dina.inventory.client.fileupload; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.Serializable; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.function.Predicate; import java.util.stream.Collectors; import java.util.stream.IntStream; import javax.ejb.Stateless; import javax.faces.context.FacesContext; import javax.servlet.http.HttpServletRequest; import org.apache.poi.hssf.record.RecordFormatException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.primefaces.model.UploadedFile; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import se.nrm.dina.inventory.client.exception.InvalidExcelFileException; import se.nrm.dina.inventory.client.util.CommonMessages; import se.nrm.dina.inventory.client.vo.ObservationData; import se.nrm.dina.inventory.client.vo.ExcelFileType; import se.nrm.dina.inventory.client.vo.ExcelMetadata; import se.nrm.dina.inventory.client.vo.TaxaData; /** * * @author idali */ @Stateless public class ExcelFileHandler implements Serializable { private final Logger logger = LoggerFactory.getLogger(this.getClass()); private final static String LOCAL_TEMP_FILE_DIRECTORY = "/Users/idali/temp/inventory_temp_file"; private final static String REMOTE_TEMP_FILE_DIRECTORY = "/home/admin/wildfly-8.1.0-0/excel/"; private String TEMP_FILE_PATH = null; private int loanNoColumn; private int determinerColumn; private int submittedDateColumn; private int taxonColumn; private String taxonFamily; private String loanNumber; private String determiner; private String dyntaxaDumpDate; private String genus; private String species; private String determinedDate; private int trapId; private int numOfMales; private int eventId; private int numOfFemales; private int total; private String storage; private String media; private Map<String, Integer> obsvTitleMap; private Map<Integer, Integer> eventIdMap; private String author; private String guid; private String source; private String notes; private String url; private String computedName; private Map<String, Integer> taxonListTitleMap; private List<String> usedTaxonList; private String servername = null; private final static String LOCAL_HOST = "localhost"; public ExcelFileHandler() { if (TEMP_FILE_PATH == null || TEMP_FILE_PATH.isEmpty()) { servername = ((HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest()) .getServerName(); logger.info("server name : {}", servername); TEMP_FILE_PATH = servername.equals(LOCAL_HOST) ? LOCAL_TEMP_FILE_DIRECTORY : REMOTE_TEMP_FILE_DIRECTORY; } } public Map<String, Integer> readInSheetTitles(Sheet sheet, int maxNumColumn) { Map<String, Integer> map = new HashMap<>(); Row row = sheet.getRow(0); IntStream.range(0, maxNumColumn).filter(i -> row.getCell(i) != null).mapToObj(i -> row.getCell(i)) .forEach(c -> { map.put(c.getStringCellValue(), c.getColumnIndex()); }); return map; } public ExcelFileType findFileType(String fileName, Map<String, Integer> map) { logger.info("findFileType"); if (fileName.contains("_C_") || fileName.contains("_C.xls") || fileName.contains("_C-") || fileName.contains("_C(")) { if (!map.containsKey("Cat. No")) { return ExcelFileType.CFile; } else { throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_C_FILE); } } else if (fileName.contains("_D_") || fileName.contains("_D.xls") || fileName.contains("_D-") || fileName.contains("_D(")) { if (map.containsKey("Cat. No")) { return ExcelFileType.DFile; } else { throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_D_FILE); } } else { throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE_NAME); } } public boolean validateSheets(Workbook workbook) { if (!workbook.getSheetName(0).equals("Metadata")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_METADAT_SHEET); } if (!workbook.getSheetName(1).equals("Observation")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_OBSERVATION_SHEET); } if (!workbook.getSheetName(2).equals("Trap")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TRAP_SHEET); } if (!workbook.getSheetName(3).equals("CollectingEvent")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_COLLECTINGEVENT_SHEET); } if (!workbook.getSheetName(4).equals("TaxonList")) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXONLIST_SHEET); } return true; } private void validateMetadataTitle(Sheet sheet) { loanNoColumn = -1; determinerColumn = -1; submittedDateColumn = -1; taxonColumn = -1; List<String> errorMsgs = new ArrayList(); IntStream.range(0, 10).parallel().mapToObj(i -> sheet.getRow(3).getCell(i)).filter(c -> c != null) .forEach(c -> { if (c.getStringCellValue().equals("Loan no")) { loanNoColumn = c.getColumnIndex(); } if (c.getStringCellValue().equals("Determined by")) { determinerColumn = c.getColumnIndex(); } if (c.getStringCellValue().equals("Submitted date")) { submittedDateColumn = c.getColumnIndex(); } if (c.getStringCellValue().equals("Taxon")) { taxonColumn = c.getColumnIndex(); } }); if (loanNoColumn == -1) { errorMsgs.add(CommonMessages.getInstance().MISSING_LOAN_NUMBER_TITLE); } if (submittedDateColumn == -1) { errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE_TITLE); } if (determinerColumn == -1) { errorMsgs.add(CommonMessages.getInstance().MISSING_DETERMINAER_TITLE); } if (taxonColumn == -1) { errorMsgs.add(CommonMessages.getInstance().MISSING_TAXON_TITLE); } if (!errorMsgs.isEmpty()) { throw new InvalidExcelFileException(errorMsgs.toString()); } } private void buildMetadata(Sheet sheet) { List<String> errorMsgs = new ArrayList(); Row row = sheet.getRow(4); Cell cell = row.getCell(taxonColumn); if (cell == null || cell.getStringCellValue().isEmpty()) { throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXON); } else { taxonFamily = cell.getStringCellValue(); } cell = row.getCell(loanNoColumn); if (cell == null || cell.getStringCellValue().isEmpty()) { errorMsgs.add(CommonMessages.getInstance().MISSING_LOAN_NUMBER); } else { loanNumber = cell.getStringCellValue(); } cell = row.getCell(submittedDateColumn); if (cell == null) { errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE); } else { dyntaxaDumpDate = (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) ? String.valueOf((int) cell.getNumericCellValue()) : cell.getStringCellValue(); if (dyntaxaDumpDate == null || dyntaxaDumpDate.isEmpty()) { errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE); } } cell = row.getCell(determinerColumn); if (cell == null || cell.getStringCellValue().isEmpty()) { errorMsgs.add(CommonMessages.getInstance().MISSING_DETERMINAER); } else { determiner = cell.getStringCellValue(); } if (!errorMsgs.isEmpty()) { throw new InvalidExcelFileException(errorMsgs.toString()); } } public ExcelMetadata validateMetadata(Sheet sheet) { try { validateMetadataTitle(sheet); buildMetadata(sheet); return new ExcelMetadata(loanNumber, taxonFamily, determiner, dyntaxaDumpDate); } catch (InvalidExcelFileException e) { throw e; } } /** * readInEventIds read in all the unique event id from excel observation sheet * @param sheet: Observation sheet * @param index: the index of eventId column * @return a list of eventId */ public List<Integer> readInEventIds(Sheet sheet, int index) { logger.info("readInEventIds : {} -- number of rows: {}", index, sheet.getLastRowNum()); return IntStream.range(1, sheet.getLastRowNum() + 1).mapToObj(i -> sheet.getRow(i)) .filter(r -> r.getCell(0) != null && !r.getCell(0).getStringCellValue().isEmpty()) .map(r -> r.getCell(index)) .map(c -> c == null ? 0 : c.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(c.getStringCellValue()) : (int) c.getNumericCellValue()) .collect(Collectors.toList()); } private void resetData() { genus = ""; species = ""; determinedDate = ""; eventId = 0; trapId = 0; numOfMales = 0; numOfFemales = 0; total = 0; storage = ""; media = ""; } private ObservationData buildCollectionObjData(Row row) { resetData(); Cell cell = row.getCell(obsvTitleMap.get("Genus")); genus = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(obsvTitleMap.get("Species")); species = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(obsvTitleMap.get("Det. date")); determinedDate = cell == null ? "" : cell.getCellType() == Cell.CELL_TYPE_STRING ? cell.getStringCellValue() : String.valueOf((int) cell.getNumericCellValue()); cell = row.getCell(obsvTitleMap.get("EventID")); eventId = cell == null ? 0 : cell.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(cell.getStringCellValue()) : (int) cell.getNumericCellValue(); cell = row.getCell(obsvTitleMap.get("TrapID")); trapId = cell == null ? 0 : cell.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(cell.getStringCellValue()) : (int) cell.getNumericCellValue(); cell = row.getCell(obsvTitleMap.get("Males")); numOfMales = cell == null ? 0 : cell.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(cell.getStringCellValue()) : (int) cell.getNumericCellValue(); cell = row.getCell(obsvTitleMap.get("Females")); numOfFemales = cell == null ? 0 : cell.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(cell.getStringCellValue()) : (int) cell.getNumericCellValue(); cell = row.getCell(obsvTitleMap.get("Total")); total = cell == null ? 0 : cell.getCellType() == Cell.CELL_TYPE_STRING ? Integer.parseInt(cell.getStringCellValue()) : (int) cell.getNumericCellValue(); if (obsvTitleMap.containsKey("Placed/Stored")) { cell = row.getCell(obsvTitleMap.get("Placed/Stored")); } else if (obsvTitleMap.containsKey("Stored")) { cell = row.getCell(obsvTitleMap.get("Stored")); } else if (obsvTitleMap.containsKey("Storage")) { cell = row.getCell(obsvTitleMap.get("Storage")); } else { cell = null; } storage = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(obsvTitleMap.get("Media")); media = cell == null ? "" : cell.getStringCellValue(); return new ObservationData(eventId, eventIdMap.get(eventId), trapId, genus, species, getComputedName(genus, species), "", storage, media, determinedDate, "", numOfMales, numOfFemales, total); } private String getComputedName(String genus, String species) { StringBuilder sb = new StringBuilder(); if (species.equals("sp.")) { sb.append(genus); } else { sb.append(genus); sb.append(" "); sb.append(species); } return sb.toString(); } public List<ObservationData> readInObservation(Sheet sheet, Map<Integer, Integer> map, Map<String, Integer> titleMap) { this.obsvTitleMap = titleMap; this.eventIdMap = map; return IntStream.range(1, sheet.getLastRowNum() + 1).mapToObj(i -> sheet.getRow(i)) .filter(r -> r.getCell(0) != null && !r.getCell(0).getStringCellValue().isEmpty()) .map(this::buildCollectionObjData).collect(Collectors.toList()); } private Predicate<Row> isInList(int index) { return r -> r.getCell(index) != null && usedTaxonList.contains(r.getCell(index).getStringCellValue()); } public List<TaxaData> readInTaxon(Sheet sheet, Map<String, Integer> titleMap, List<String> usedTaxonList) { this.usedTaxonList = usedTaxonList; this.taxonListTitleMap = titleMap; int index = titleMap.get("Taxon name (computed)"); try { return IntStream.range(1, sheet.getLastRowNum() + 1).mapToObj(i -> sheet.getRow(i)) .filter(r -> r.getCell(0) != null && !r.getCell(0).getStringCellValue().isEmpty()) .filter(isInList(index)).map(this::buildTaxaData).collect(Collectors.toList()); } catch (InvalidExcelFileException e) { throw e; } } private TaxaData buildTaxaData(Row row) { Cell cell = row.getCell(taxonListTitleMap.get("Genus")); genus = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(taxonListTitleMap.get("Species")); species = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(taxonListTitleMap.get("Author")); author = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(taxonListTitleMap.get("GUID")); guid = cell == null ? "" : cell.getStringCellValue(); cell = row.getCell(taxonListTitleMap.get("Source")); source = cell == null ? "" : cell.getStringCellValue(); if (taxonListTitleMap.containsKey("Notes (errors, synonyms, undescribed etc)")) { cell = row.getCell(taxonListTitleMap.get("Notes (errors, synonyms, undescribed etc)")); } else if (taxonListTitleMap.containsKey("Notes")) { cell = row.getCell(taxonListTitleMap.get("Notes")); } else { cell = null; } notes = cell == null ? "" : cell.getStringCellValue(); String msg; cell = row.getCell(taxonListTitleMap.get("Taxon name (computed)")); computedName = cell == null ? "" : cell.getStringCellValue(); if (computedName == null || computedName.isEmpty()) { msg = CommonMessages.getInstance().buildMessage(CommonMessages.getInstance().TAXON_COMPUTED_IS_EMPTY, row.getRowNum() + 1); throw new InvalidExcelFileException(msg); } else if (!computedName.trim().equals((genus + " " + species).trim())) { msg = CommonMessages.getInstance() .buildMessage(CommonMessages.getInstance().TAXON_COMPUTED_IS_INCORRECT, row.getRowNum() + 1); throw new InvalidExcelFileException(msg); } return new TaxaData(0, genus, species, computedName, guid, author, notes, source, "", null); } /** * getWorkbook method to build Workbook from excel file * * @param excelFile * @param workbook * @return * @throws InvalidExcelFileException */ public Workbook buildWorkbook(String excelFile, Workbook workbook) { File file = new File(TEMP_FILE_PATH, excelFile); try { return WorkbookFactory.create(file); } catch (IOException | InvalidFormatException | RecordFormatException ex) { throw new InvalidExcelFileException(ex.getMessage()); } } /** * saveTempFile to save uploaded file into temp directory * * @param uploadFile * @return */ public String saveTempFile(UploadedFile uploadFile) { logger.info("saveTempFile : {} ", uploadFile.getFileName()); File targetFile; String fileName = uploadFile.getFileName(); try (InputStream initialStream = uploadFile.getInputstream()) { byte[] buffer = new byte[initialStream.available()]; initialStream.read(buffer); targetFile = new File(TEMP_FILE_PATH, fileName); try (OutputStream outStream = new FileOutputStream(targetFile)) { outStream.write(buffer); outStream.close(); } initialStream.close(); return targetFile.getPath(); } catch (IOException ex) { throw new InvalidExcelFileException(ex.getMessage()); } } public void removeFile(String fileName) { File thisFile = new File(TEMP_FILE_PATH, fileName); if (thisFile.exists()) { thisFile.delete(); } } }