List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelObjectBinder.java
License:BSD License
/** * Create objects of type (target) populated from the given Excel file * @param excelFile//from www .jav a 2s . co m * @param target * @return Map of (target) objects keyed by worksheet name. Each map entry value is a List * of objects. * @throws IOException */ public Map bind(InputStream excelFile, Class target) throws IOException { HSSFWorkbook wb = ExcelUtility.createWorkbook(excelFile); int cnt = wb.getNumberOfSheets(); Map wsMap = new HashMap(); for (int i = 0; i < cnt; i++) { String name = wb.getSheetName(i); if (log.isDebugEnabled()) { log.debug("Worksheet binding => " + i + " (" + name + ")"); } List objects = bindWorksheet(target, wb.getSheet(name)); wsMap.put(name, objects); } return wsMap; }
From source file:gtu._work.etc.TestCaseExcelMakerUI.java
License:Open Source License
void loadInitExcel() throws Exception { File file = new File("C:/Users/gtu001/Desktop/RL-10?.xls"); HSSFWorkbook book = ExcelUtil.getInstance().readExcel(file); Map<String, String> map = new HashMap<String, String>(); for (int ii = 0; ii < book.getNumberOfSheets(); ii++) { HSSFSheet sheet = book.getSheetAt(ii); for (int jj = 0; jj <= sheet.getLastRowNum(); jj++) { if (sheet.getRow(jj).getCell(0) == null) { continue; }//from w ww .jav a 2 s .c o m if (sheet.getRow(jj).getCell(1) == null) { continue; } String key = ExcelUtil.getInstance().readHSSFCell(sheet.getRow(jj).getCell(0)).toUpperCase();//title num String value = ExcelUtil.getInstance().readHSSFCell(sheet.getRow(jj).getCell(1));//title chn map.put(key, value); } } File dir = new File("C:/Users/gtu001/Desktop/ (2)"); Set<String> list = new HashSet<String>(); for (File f : dir.listFiles()) { list.add(f.getName().replaceAll("\\.jpg", "").replaceAll("[a-zA-Z]$", "").toUpperCase()); } JTableUtil util = JTableUtil.newInstance(jTable1); StringBuilder sb = new StringBuilder(); for (Iterator<String> it = list.iterator(); it.hasNext();) { String key = it.next(); if (map.containsKey(key)) { util.getModel().addRow(new Object[] { "rl" + key, map.get(key), "", "" }); } else { sb.append(key + "\n"); } } if (sb.length() != 0) { JCommonUtil._jOptionPane_showMessageDialog_error(sb.toString()); } }
From source file:guineu.data.parser.impl.GCGCParserXLS.java
License:Open Source License
public String[] getSheetNames(String fileName) throws IOException { HSSFWorkbook wb = this.openExcel(fileName); String[] sheetsNames = new String[wb.getNumberOfSheets()]; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheetsNames[i] = wb.getSheetName(i); }/*from ww w . ja va2s. com*/ return sheetsNames; }
From source file:helpers.Excel.ExcelDataFormat.java
public Object unmarshal(Exchange exchng, InputStream in) throws Exception { logger.info("Unmarshalling XLS"); Object res = exchng.getIn().getBody(); GenericFile genfile = (GenericFile) res; if (genfile.getFileNameOnly().endsWith("xlsx")) { return unmarshalXLSX(exchng, in); }/* w w w . j av a 2s . c om*/ HSSFWorkbook workbook = new HSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); if (importType != ImportType.FORMATTED) { return marshalAsArray(sheet.iterator()); } else { OneExcel excel = new OneExcel(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator); logger.info("Loading sheet:" + i); logger.info("Data:" + onesheet.data.size()); if (onesheet.data.size() > 0) excel.sheets.add(onesheet); } logger.info("Total sheets:" + excel.sheets.size()); ArrayList<HashMap<String, Object>> resu = excel.GenerateResult(); HashMap<String, Object> mappings = excel.GenerateMappings(); exchng.getOut().setHeader("mappings", mappings); exchng.getOut().setHeader("xlsdata", resu); return resu; } }
From source file:Import.Utils.XSSFConvert.java
public static XSSFWorkbook convertWorkbookHSSFToXSSF(HSSFWorkbook source) { XSSFWorkbook retVal = new XSSFWorkbook(); for (int i = 0; i < source.getNumberOfSheets(); i++) { XSSFSheet xssfSheet = retVal.createSheet(); HSSFSheet hssfsheet = source.getSheetAt(i); copySheets(hssfsheet, xssfSheet); }/*from www .j av a 2 s . c om*/ return retVal; }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private static int getSheetIndex(final HSSFWorkbook workbook, final HSSFSheet sheet) { for (int i = workbook.getNumberOfSheets() - 1; i >= 0; i--) { if (sheet.equals(workbook.getSheetAt(i))) { return i; }//from www . java 2 s .c o m } throw new InvalidParameterException("HSSFSheet non trouv dans le HSSFWorkbook."); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * export//w w w.j a v a 2 s. c o m * * @param fullFileName * @param populatedTemplateFileName * @param shippingInformation * @throws XlsUploadException * @throws Exception */ public void export(String fullFileName, String populatedTemplateFileName, ShippingInformation shippingInformation) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(populatedTemplateFileName)); HSSFWorkbook workbook = null; try { // Now extract the workbook workbook = new HSSFWorkbook(fs); } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } HSSFSheet sheet = null; int nbSheetsInFile = workbook.getNumberOfSheets(); int nbSheetsInInfo = DBTools.GetNumberOfContainers(shippingInformation) - 1; int nbSheetsToDelete = nbSheetsInFile - nbSheetsInInfo; int i; // Create Additional Sheets if needed if (nbSheetsToDelete > 0) { for (i = nbSheetsInFile - 1; i >= nbSheetsInFile - nbSheetsToDelete; i--) { // workbook.removeSheetAt(i); } } // Populate Sheet int currentSheetNumber = -1; HSSFRow row = null; HSSFCell cell = null; try { DiffractionPlan3Service difPlanService = (DiffractionPlan3Service) ejb3ServiceLocator .getLocalService(DiffractionPlan3Service.class); for (int d = 0; d < shippingInformation.getListDewars().size(); d++) { // Dewar DewarInformation dewar = shippingInformation.getListDewars().get(d); // Container for (int c = 0; c < dewar.getListContainers().size(); c++) { currentSheetNumber++; sheet = workbook.getSheetAt(currentSheetNumber); ContainerInformation container = dewar.getListContainers().get(c); // Populate Courrier row = sheet.getRow(courrierNameRow); cell = row.getCell(courrierNameCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue( new HSSFRichTextString(shippingInformation.getShipping().getDeliveryAgentAgentName())); row = sheet.getRow(trackingNumberRow); cell = row.getCell(trackingNumberCol); cell.setCellValue( new HSSFRichTextString(shippingInformation.getShipping().getDeliveryAgentAgentCode())); row = sheet.getRow(shippingDateRow); cell = row.getCell(shippingDateCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(shippingInformation.getShipping().getDeliveryAgentShippingDate()); // Populate Puck row = sheet.getRow(puckRow); cell = row.getCell(puckCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(container.getContainer().getCode())); // Populate Dewar row = sheet.getRow(dewarRow); cell = row.getCell(dewarCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dewar.getDewar().getCode())); // Sample for (int s = 0; s < container.getListSamples().size(); s++) { SampleInformation sample = container.getListSamples().get(s); Crystal3VO crystal = sample.getCrystal(); Protein3VO protein = sample.getProtein(); DiffractionPlan3VO diffractionPlan = null; // DiffractionPlanLightValue diffractionPlan = sample.getSample().getDiffractionPlan(); if (sample.getSample().getDiffractionPlanVOId() != null) diffractionPlan = difPlanService.findByPk(sample.getSample().getDiffractionPlanVOId(), false, false); else if (diffractionPlan == null) diffractionPlan = difPlanService.findByPk(crystal.getDiffractionPlanVOId(), false, false); int currentRow = dataRow + s; // Try to extract Sample Location Integer locationIncontainer = null; try { String _locationInContainer = sample.getSample().getLocation(); locationIncontainer = Integer.parseInt(_locationInContainer); } catch (Exception e) { } if (locationIncontainer != null && locationIncontainer <= Constants.BASKET_SAMPLE_CAPACITY) { currentRow = dataRow + locationIncontainer - 1; } // Protein acronym - SpaceGroup row = sheet.getRow(currentRow); cell = row.getCell(proteinAcronymCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue( new HSSFRichTextString(protein.getAcronym() + " - " + crystal.getSpaceGroup())); // Sample Name row = sheet.getRow(currentRow); cell = row.getCell(sampleNameCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getName())); // Pin Barcode row = sheet.getRow(currentRow); cell = row.getCell(pinBarCodeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getCode())); // Pre-observed resolution row = sheet.getRow(currentRow); cell = row.getCell(preObsResolutionCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getObservedResolution() != null) cell.setCellValue(diffractionPlan.getObservedResolution()); else cell.setCellValue(new HSSFRichTextString("")); // Needed resolution row = sheet.getRow(currentRow); cell = row.getCell(neededResolutionCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getMinimalResolution() != null) cell.setCellValue(diffractionPlan.getMinimalResolution()); else cell.setCellValue(new HSSFRichTextString("")); // Preferred beam diameter row = sheet.getRow(currentRow); cell = row.getCell(preferredBeamCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null) { if (diffractionPlan.getPreferredBeamDiameter() != null) cell.setCellValue(diffractionPlan.getPreferredBeamDiameter()); } else cell.setCellValue(new HSSFRichTextString("")); // Experiment Type row = sheet.getRow(currentRow); cell = row.getCell(experimentTypeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getExperimentKind() != null) cell.setCellValue(new HSSFRichTextString(diffractionPlan.getExperimentKind())); else cell.setCellValue(new HSSFRichTextString(Constants.LIST_EXPERIMENT_KIND[0])); // // // Anomalous Scatterer // row = sheet.getRow(currentRow); // cell = row.getCell(anomalousScattererCol); // cell.setCellType(HSSFCell.CELL_TYPE_STRING); // if (crystal.getAnomalousScatterers().length>0) { // AnomalousScattererLightValue an = crystal.getAnomalousScatterers()[0]; // cell.setCellValue(new HSSFRichTextString(an.getElement())); // } // else // cell.setCellValue(new HSSFRichTextString("")); // Unit Cell a row = sheet.getRow(currentRow); cell = row.getCell(unitCellACol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellA() != null) ? crystal.getCellA() : 0); // Unit Cell b row = sheet.getRow(currentRow); cell = row.getCell(unitCellBCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellB() != null) ? crystal.getCellB() : 0); // Unit Cell c row = sheet.getRow(currentRow); cell = row.getCell(unitCellCCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellC() != null) ? crystal.getCellC() : 0); // Unit Cell alpha row = sheet.getRow(currentRow); cell = row.getCell(unitCellAlphaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellAlpha() != null) ? crystal.getCellAlpha() : 0); // Unit Cell beta row = sheet.getRow(currentRow); cell = row.getCell(unitCellBetaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellBeta() != null) ? crystal.getCellBeta() : 0); // Unit Cell gamma row = sheet.getRow(currentRow); cell = row.getCell(unitCellGammaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellGamma() != null) ? crystal.getCellGamma() : 0); // LoopType // row = sheet.getRow(currentRow); // cell = row.getCell(loopTypeCol); // cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellValue(new HSSFRichTextString(sample.getSample().getLoopType())); // HolderLength // row = sheet.getRow(currentRow); // cell = row.getCell(holderLengthCol); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(sample.getSample().getHolderLength()); // SMILES row = sheet.getRow(currentRow); cell = row.getCell(smilesCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getSmiles())); // min osc width row = sheet.getRow(currentRow); cell = row.getCell(minOscWidthCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null) { if (diffractionPlan.getMinOscWidth() != null) cell.setCellValue(diffractionPlan.getMinOscWidth()); } else cell.setCellValue(new HSSFRichTextString("")); // Comments row = sheet.getRow(currentRow); cell = row.getCell(commentsCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getComments())); } } } } catch (Exception e) { e.printStackTrace(); } // Save Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.close(); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * populate// w w w .jav a 2 s. c o m * * @param templateFileName * @param populatedTemplateFileName * @param listProteins * @param dmCodesinSC * @throws XlsUploadException * @throws Exception */ public void populate(String templateFileName, String populatedTemplateFileName, List listProteins, String[][] dmCodesinSC) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileName)); HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(fs); // Now extract the workbook } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } Protein3Service proteinService = (Protein3Service) Ejb3ServiceLocator.getInstance() .getLocalService(Protein3Service.class); HSSFSheet sheet = null; for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { sheet = workbook.getSheetAt(sheetNum); Iterator it = listProteins.iterator(); int currentRow = this.proteinAcronymRow; List<String> listProtein = new ArrayList<String>(); while (it.hasNext()) { Protein3VO protein = (Protein3VO) it.next(); // protein = proteinService.loadEager(protein); Crystal3VO[] crystals = protein.getCrystals(); // Retrieve Xtals for SpaceGroup for (int c = 0; c < crystals.length; c++) { String acronym = protein.getAcronym(); Crystal3VO xtal = crystals[c]; // Replace database empty values by 'Undefined' if (xtal.getSpaceGroup() != null && !xtal.getSpaceGroup().equals("")) { acronym += Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR + xtal.getSpaceGroup(); } else { acronym += Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR + "Undefined"; } // Add to list (but don't duplicate) if (!listProtein.contains(acronym)) { listProtein.add(acronym); // Populate Acronym - SpaceGroup try { HSSFRow row = sheet.getRow(currentRow); if (row == null) row = sheet.createRow(currentRow); HSSFCell cell = row.getCell(proteinAcronymCol); if (cell == null) cell = row.createCell(proteinAcronymCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(acronym); currentRow++; } catch (Exception e) { e.printStackTrace(); } } } } } // Populate DM Codes if (dmCodesinSC != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { sheet = workbook.getSheetAt(sheetNum); int basketLocation = sheetNum + 1; for (int sampleLocation = 0; sampleLocation < Constants.BASKET_SAMPLE_CAPACITY; sampleLocation++) { int rowNumber = dataRow + sampleLocation; String dmCode = dmCodesinSC[sheetNum + 1][sampleLocation + 1]; HSSFRow row = sheet.getRow(rowNumber); if (row == null) row = sheet.createRow(rowNumber); HSSFCell cell = row.getCell(pinBarCodeCol); if (cell == null) cell = row.createCell(pinBarCodeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dmCode)); } } } // Save Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.flush(); fout.close(); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * Validate/*w w w . j a v a 2s . c o m*/ * * @param file * @param _listProteinAcronym_SampleName * @param proposalId * @return * @throws XlsUploadException * @throws Exception */ @Override public List validate(InputStream file, Hashtable _listProteinAcronym_SampleName, Integer proposalId) throws XlsUploadException, Exception { Hashtable<String, Hashtable<String, Integer>> listProteinAcronym_SampleName = _listProteinAcronym_SampleName; // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(file); HSSFWorkbook workbook = null; HashMap usedSampleNames = new HashMap(); HashMap usedPuckCodes = new HashMap(); HashMap usedDMCodes = new HashMap(); Integer shippingId = null; Hashtable<String, Hashtable<String, String>> listSampleName_ProteinAcronym_InSpreadsheet = new Hashtable<String, Hashtable<String, String>>(); try { workbook = new HSSFWorkbook(fs); // Now extract the workbook this.mWorkbook = workbook; } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); this.getValidationErrors().add(ex); // No need to keep on validating ! return this.getValidationErrors(); } // Check the Shipment belongs to the right Proposal HSSFSheet firstsheet = workbook.getSheetAt(0); String idLabel = cellToString(firstsheet.getRow(idLabelRow).getCell(idLabelCol)); // Check proposalId based on file proposalId if (idLabel != null && idLabel.trim().equalsIgnoreCase(ProposalAndShippingLabel)) { boolean proposalOK = true; Integer sheetProposalId = Integer .decode(cellToString(firstsheet.getRow(value1IdRow).getCell(value1IdCol))); try { if (proposalId.intValue() != sheetProposalId.intValue()) proposalOK = false; } catch (Exception e) { proposalOK = false; } if (!proposalOK) { this.getValidationErrors() .add(new XlsUploadException( "Current Proposal <> XLS sheet Proposal : " + DBTools.GetProposalName(proposalId) + "<>" + DBTools.GetProposalName(sheetProposalId), "Check the XLS template was created for the right Proposal")); return this.getValidationErrors(); } } // Check proposalId based on file shippingId (for compatibility reasons) if (idLabel != null && idLabel.trim().equalsIgnoreCase(ShippingLabel)) { Integer sheetProposalId = null; boolean proposalOK = true; String _shippingId = cellToString(firstsheet.getRow(value1IdRow).getCell(value1IdCol)); try { shippingId = Integer.decode(_shippingId); sheetProposalId = DBTools.getProposalIdFromShipping(shippingId); if (proposalId.intValue() != sheetProposalId.intValue()) proposalOK = false; } catch (Exception e) { proposalOK = false; } if (!proposalOK) this.getValidationErrors() .add(new XlsUploadException( "Current Proposal <> XLS sheet Proposal : " + proposalId + "<>" + sheetProposalId, "Check the XLS template was created for the right Proposal")); } for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = workbook.getSheetAt(sheetNum); // // Version n-1 // if (cellToString(sheet.getRow(checkRow).getCell(checkCol)).indexOf(Constants.TEMPLATE_VERSION_N_1)==-1) { // XlsUploadException ex = new XlsUploadException( "The XLS template you are using is obsolete and will no // longer be supported in a near future.", // "Download and use latest template"); // this.getValidationErrors().add(ex); // } // Current Version try { if (cellToString(sheet.getRow(checkRow).getCell(checkCol)) .indexOf(Constants.TEMPLATE_VERSION) == -1) { XlsUploadException ex = new XlsUploadException( "The XLS template does not have the right version", "Download and use latest template"); this.getValidationErrors().add(ex); // No need to keep on validating ! return this.getValidationErrors(); } } catch (Exception e) { XlsUploadException ex = new XlsUploadException( "A problem occured while reading XLS template version on sheet #" + sheetNum, "Download and use latest template"); this.getValidationErrors().add(ex); // No need to keep on validating ! return this.getValidationErrors(); } // Dewar + Container String puckCode = cellToString(sheet.getRow(puckRow).getCell(puckCol)); String dewarCode = cellToString(sheet.getRow(dewarRow).getCell(dewarCol)); if (puckCode == "") this.getValidationErrors() .add(new XlsUploadException( "Puck name is empty for worksheet : " + workbook.getSheetName(sheetNum), "Fill in Puck name on top of the page")); if (dewarCode == "") this.getValidationErrors() .add(new XlsUploadException( "Dewar name is empty for worksheet : " + workbook.getSheetName(sheetNum), "Fill in Dewar name on top of the page")); // PuckCode not used twice if (puckCode != "" && usedPuckCodes.containsKey(puckCode)) { // PuckCode already used this.getValidationErrors() .add(new XlsUploadException( "Puck Code already used : " + puckCode + " (" + usedSampleNames.get(puckCode) + ")", "Change Puck Code")); } else if (puckCode != "") { // PuckCode is new usedSampleNames.put(puckCode, "Worksheet: " + workbook.getSheetName(sheetNum)); } boolean emptySheet = true; // Reset list of Sample Names usedSampleNames = new HashMap(); for (int i = dataRow; i < dataRow + Constants.BASKET_SAMPLE_CAPACITY; i += 1) { // Retrieve interesting values from spreadsheet String samplePos = cellToString(sheet.getRow(i).getCell(samplePosCol)); String proteinAcronym = cellToString(sheet.getRow(i).getCell(proteinAcronymCol)); String sampleName = cellToString(sheet.getRow(i).getCell(sampleNameCol)); String spaceGroup = cellToString(sheet.getRow(i).getCell(spaceGroupCol)).toUpperCase().trim() .replace(" ", ""); String dmCode = cellToString(sheet.getRow(i).getCell(pinBarCodeCol)).toUpperCase().trim() .replace(" ", ""); int separatorStart = proteinAcronym.indexOf(Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR); if (separatorStart != -1) { proteinAcronym = proteinAcronym.substring(0, separatorStart); } // SampleName + ProteinAcronym if (proteinAcronym != "" && sampleName != "") emptySheet = false; if (proteinAcronym == "" && sampleName != "") this.getValidationErrors() .add(new XlsUploadException("Protein Acronym is empty for worksheet : " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos, "Fill in Protein Acronym")); if (sampleName == "" && proteinAcronym != "") this.getValidationErrors() .add(new XlsUploadException("Sample Name is empty for worksheet : " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos, "Fill in Sample Name")); // SampleName does not contain forbidden characters if (sampleName != "" && !sampleName.matches(Constants.MASK_SHIPMENT_LEGAL_CHARACTERS)) { this.getValidationErrors().add(new XlsUploadException( "Sample Name contains forbidden characters : '" + sampleName + "' : for worksheet: " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos, "Use any of the following characters only : " + Constants.MASK_SHIPMENT_LEGAL_CHARACTERS)); } // DM code not used twice --- if (!dmCode.equalsIgnoreCase("") && usedDMCodes.containsKey(dmCode)) { // SampleName already used this.getValidationErrors() .add(new XlsUploadException( "Pin Barcode already used : " + dmCode + " (" + usedDMCodes.get(dmCode) + ")", "Change Pin Barcode")); } else if (!sampleName.equalsIgnoreCase("")) { // SampleName is new usedDMCodes.put(dmCode, "Worksheet: " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos); } // Space Group if (spaceGroup != "" && !SPACE_GROUPS.contains(spaceGroup)) { this.getValidationErrors() .add(new XlsUploadException( "In worksheet " + workbook.getSheetName(sheetNum) + " Spacegroup is unknown: " + spaceGroup, "Make sure you're using a value from the drop-down list")); } // (SampleName,Protein Acronym) not used twice // Check in ISPyB if (sampleName != "" && proteinAcronym != "") { if (listProteinAcronym_SampleName.containsKey(proteinAcronym)) { // Protein Acronym already used Hashtable<String, Integer> listSampleName = listProteinAcronym_SampleName .get(proteinAcronym); if (listSampleName.containsKey(sampleName)) { // Sample Name this.getValidationErrors().add(new XlsUploadException( "<Sample Name>_<Protein Acronym> : " + sampleName + "_" + proteinAcronym + " : already used for your Proposal in ISPyB: sheet " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos, "Change Sample Name")); } } // DLS ###### else if (Constants.ALLOWED_TO_CREATE_PROTEINS) { // Allow to add unknown proteins LOG.debug("protein acronym '" + proteinAcronym + "' is unknown and will be created"); } else { // Don't allow to add unknown proteins LOG.error("unknown protein acronym '" + proteinAcronym + "'"); this.getValidationErrors() .add(new XlsUploadException( "In worksheet " + workbook.getSheetName(sheetNum) + " Protein Acronym is unknown: '" + proteinAcronym + "'", "Make sure you're using a value from the drop-down list")); } } // Check in Spreadsheet if (sampleName != "" && proteinAcronym != "") { if (listSampleName_ProteinAcronym_InSpreadsheet.containsKey(sampleName)) { // SampleName used Hashtable<String, String> listProteinAcronym = listSampleName_ProteinAcronym_InSpreadsheet .get(sampleName); if (listProteinAcronym.containsKey(proteinAcronym)) { // SampleName + ProteinAcronym already used ! this.getValidationErrors().add(new XlsUploadException( "<Sample Name>_<Protein Acronym> : " + sampleName + "_" + proteinAcronym + " : already used inside the spreadsheet: sheet " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos, "Change Sample Name")); } else { // ProteinAcronym not used for this SampleName listProteinAcronym.put(proteinAcronym, "In worksheet " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos); listSampleName_ProteinAcronym_InSpreadsheet.put(sampleName, listProteinAcronym); } } else { // SampleName not used, create entry Hashtable<String, String> listProteinAcronym = new Hashtable<String, String>(); listProteinAcronym.put(proteinAcronym, "In worksheet " + workbook.getSheetName(sheetNum) + " Sample Position: " + samplePos); listSampleName_ProteinAcronym_InSpreadsheet.put(sampleName, listProteinAcronym); } } } // --- Empty Sheet --- if (emptySheet) { this.getValidationWarnings().add(new XlsUploadException( "Worksheet is empty : " + workbook.getSheetName(sheetNum), "It will not be uploaded")); } else { this.getValidationWarnings().add(new XlsUploadException( "Worksheet has data : " + workbook.getSheetName(sheetNum), "It will be uploaded")); } } return this.getValidationErrors(); }
From source file:ispyb.common.util.upload.UploadShipmentUtils.java
License:Open Source License
public static String[] importFromXls(InputStream file, Integer shippingId, boolean deleteAllShipment, List<String> allowedSpaceGroups) throws Exception { String msgError = ""; String msgWarning = ""; Protein3Service proteinService = (Protein3Service) ejb3ServiceLocator .getLocalService(Protein3Service.class); Crystal3Service crystalService = (Crystal3Service) ejb3ServiceLocator .getLocalService(Crystal3Service.class); BLSample3Service sampleService = (BLSample3Service) ejb3ServiceLocator .getLocalService(BLSample3Service.class); DiffractionPlan3Service difPlanService = (DiffractionPlan3Service) ejb3ServiceLocator .getLocalService(DiffractionPlan3Service.class); Container3Service containerService = (Container3Service) ejb3ServiceLocator .getLocalService(Container3Service.class); Shipping3Service shippingService = (Shipping3Service) ejb3ServiceLocator .getLocalService(Shipping3Service.class); Shipping3VO shipment = shippingService.findByPk(shippingId, true); Set<Dewar3VO> dewars = shipment.getDewarVOs(); HSSFWorkbook workbook = null; Integer sheetProposalId = DBTools.getProposalIdFromShipping(shippingId); String courrierName = ""; String shippingDate = ""; String trackingNumber = ""; POIFSFileSystem fs = new POIFSFileSystem(file); // Now extract the workbook workbook = new HSSFWorkbook(fs); // Working through each of the worksheets in the spreadsheet // ASSUMPTION: one excel file = one shipment for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { boolean sheetIsEmpty = true; HSSFSheet sheet = workbook.getSheetAt(sheetNum); // DeliveryAgent ---- // --- Retrieve Shipment related information if (sheetNum == 0) { if (sheet.getRow(courrierNameRow).getCell(courrierNameCol) == null) { msgError += "The format of the xls file is incorrect (courrier name missing)"; String[] msg = new String[2]; msg[0] = msgError;/*ww w . j a v a 2 s . c o m*/ msg[1] = msgWarning; return msg; } courrierName = (sheet.getRow(courrierNameRow).getCell(courrierNameCol)).toString(); if (sheet.getRow(shippingDateRow).getCell(shippingDateCol) == null) { msgError += "The format of the xls file is incorrect (shipping Date missing)"; String[] msg = new String[2]; msg[0] = msgError; msg[1] = msgWarning; return msg; } shippingDate = (sheet.getRow(shippingDateRow).getCell(shippingDateCol)).toString(); if (sheet.getRow(trackingNumberRow).getCell(trackingNumberCol) == null) { msgError += "The format of the xls file is incorrect (tracking number missing)"; String[] msg = new String[2]; msg[0] = msgError; msg[1] = msgWarning; return msg; } trackingNumber = (sheet.getRow(trackingNumberRow).getCell(trackingNumberCol)).toString(); // retrieveShippingId(file); DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy"); Date shipDate = null; Calendar shipCal = Calendar.getInstance(); try { shipDate = fmt.parse(shippingDate); shipCal.setTime(shipDate); } catch (Exception e) { shipCal = Calendar.getInstance(); } shipment.setDeliveryAgentAgentCode(trackingNumber); shipment.setDeliveryAgentAgentName(courrierName); shipment.setDeliveryAgentShippingDate(shipDate); } // Dewar String dewarCode = (sheet.getRow(dewarRow).getCell(dewarCol)).toString().trim(); Integer dewarId = null; Dewar3VO dewar = null; // check if dewar exists for (Dewar3VO dewar3vo : dewars) { if (dewar3vo.getCode().equals(dewarCode)) { dewarId = dewar3vo.getDewarId(); dewar = dewar3vo; break; } } Container3VO container = null; if (dewar == null) { msgError += "Dewar with code '" + dewarCode + "' does not correspond to any dewar. Please check the dewar's name.\n"; sheetIsEmpty = true; } else { // Puck container = new Container3VO(); container.setDewarVO(dewar); container.setContainerType("Puck"); container.setCode((sheet.getRow(puckRow).getCell(puckCol)).toString().trim()); container.setCapacity(Constants.BASKET_SAMPLE_CAPACITY); container.setTimeStamp(StringUtils.getCurrentTimeStamp()); if (!deleteAllShipment) { // check sheet empty before boolean sheetEmpty = true; for (int i = dataRow; i < dataRow + Constants.BASKET_SAMPLE_CAPACITY; i += 1) { boolean sampleRowOK = true; String puckCode = cellToString(sheet.getRow(puckRow).getCell(puckCol)); String proteinAcronym = cellToString(sheet.getRow(i).getCell(proteinAcronymCol)); String sampleName = cellToString(sheet.getRow(i).getCell(sampleNameCol)); boolean sampleNameRulesOk = sampleName .matches(Constants.MASK_BASIC_CHARACTERS_WITH_DASH_UNDERSCORE_NO_SPACE); if (puckCode.isEmpty() || dewarCode.isEmpty() || proteinAcronym.isEmpty() || sampleName.isEmpty() || !sampleNameRulesOk) { sampleRowOK = false; } if (!sampleRowOK) { // Skip this line we do not create the sample } else { sheetEmpty = false; break; } } List<Container3VO> listContainerFromDB = containerService.findByCode(dewar.getDewarId(), container.getCode()); if (listContainerFromDB != null && listContainerFromDB.size() > 0 && !sheetEmpty) { // delete it in // order to be // replaced by // the new one containerService.deleteByPk(listContainerFromDB.get(0).getContainerId()); msgWarning += "The Puck " + container.getCode() + " has been deleted and a new one has been added."; } } container = containerService.create(container); // List<Crystal3VO> listCrystalCreated = new ArrayList<Crystal3VO>(); List<Crystal3VO> listCrystalCreated = crystalService.findByProposalId(sheetProposalId); // TBD: need to add sanity check that this puck has not already been put in the dewar! for (int i = dataRow; i < dataRow + Constants.BASKET_SAMPLE_CAPACITY; i += 1) { // --- Retrieve interesting values from spreadsheet String puckCode = cellToString(sheet.getRow(puckRow).getCell(puckCol)); String proteinName = cellToString(sheet.getRow(i).getCell(proteinNameCol)); String proteinAcronym = cellToString(sheet.getRow(i).getCell(proteinAcronymCol)); String samplePos = cellToString(sheet.getRow(i).getCell(samplePosCol)); String sampleName = cellToString(sheet.getRow(i).getCell(sampleNameCol)); String pinBarCode = cellToString(sheet.getRow(i).getCell(pinBarCodeCol)); double preObsResolution = cellToDouble(sheet.getRow(i).getCell(preObsResolutionCol)); double neededResolution = cellToDouble(sheet.getRow(i).getCell(neededResolutionCol)); double preferedBeamDiameter = cellToDouble(sheet.getRow(i).getCell(preferredBeamCol)); String experimentType = cellToString(sheet.getRow(i).getCell(experimentTypeCol)); int nbOfPositions = cellToInt(sheet.getRow(i).getCell(nbOfPositionsCol)); String spaceGroup = cellToString(sheet.getRow(i).getCell(spaceGroupCol)).toUpperCase().trim() .replace(" ", ""); double unitCellA = cellToDouble(sheet.getRow(i).getCell(unitCellACol)); double radiationSensitivity = cellToDouble(sheet.getRow(i).getCell(radiationSensitivityCol)); double requiredCompleteness = cellToDouble(sheet.getRow(i).getCell(requiredCompletenessCol)); double requiredMultiplicity = cellToDouble(sheet.getRow(i).getCell(requiredMultiplicityCol)); double unitCellB = cellToDouble(sheet.getRow(i).getCell(unitCellBCol)); double unitCellC = cellToDouble(sheet.getRow(i).getCell(unitCellCCol)); double unitCellAlpha = cellToDouble(sheet.getRow(i).getCell(unitCellAlphaCol)); double unitCellBeta = cellToDouble(sheet.getRow(i).getCell(unitCellBetaCol)); double unitCellGamma = cellToDouble(sheet.getRow(i).getCell(unitCellGammaCol)); String sampleComments = cellToString(sheet.getRow(i).getCell(commentsCol)); String smiles = cellToString(sheet.getRow(i).getCell(smilesCol)); double minOscWidth = cellToDouble(sheet.getRow(i).getCell(minOscWidthCol)); // Fill in values by default // Protein Name if (proteinName.equalsIgnoreCase("")) proteinName = proteinAcronym; // --- Check the Sheet is not empty for this line and all required fields are present --- boolean sampleRowOK = true; boolean sampleNameRulesOk = sampleName .matches(Constants.MASK_BASIC_CHARACTERS_WITH_DASH_UNDERSCORE_NO_SPACE); if (puckCode.isEmpty() || dewarCode.isEmpty() || proteinAcronym.isEmpty() || sampleName.isEmpty() || sampleName.length() > Constants.BLSAMPLE_NAME_NB_CHAR || !sampleNameRulesOk) { sampleRowOK = false; if (!(sampleName.isEmpty() && proteinAcronym.isEmpty())) { msgError += "Error with the sample: " + sampleName; if (puckCode.isEmpty()) { msgError += " (The puck code is empty)"; } if (dewarCode.isEmpty()) { msgError += " (The dewar code is empty)"; } if (proteinAcronym.isEmpty()) { msgError += " (The protein acronym is empty)"; } if (sampleName.isEmpty()) { msgError += " (The sample name is empty)"; } if (sampleName.length() > Constants.BLSAMPLE_NAME_NB_CHAR) { msgError += " (The sample name is too long : max 8 characters)"; } if (!sampleNameRulesOk) { msgError += " (The sample name is not well formatted)"; } msgError += "\n."; } } if (!sampleRowOK) { // Skip this line we do not create the sample } else { sheetIsEmpty = false; String crystalID = UUID.randomUUID().toString(); // String diffractionPlanUUID = uuidGenerator.generateRandomBasedUUID().toString(); if ((null != crystalID) && (!crystalID.equals(""))) { // Parse ProteinAcronym - SpaceGroup // Pre-filled spreadsheet (including protein_acronym - SpaceGroup) int separatorStart = proteinAcronym .indexOf(Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR); if (separatorStart != -1) { String acronym = proteinAcronym.substring(0, separatorStart); String prefilledSpaceGroup = proteinAcronym.substring( separatorStart + Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR.length(), proteinAcronym.length()); proteinAcronym = acronym; if (allowedSpaceGroups.contains(spaceGroup.toUpperCase())) { // Do nothing = use spaceGroup from dropdown list } else if (allowedSpaceGroups.contains(prefilledSpaceGroup.toUpperCase())) { // Used pre-filled space group spaceGroup = prefilledSpaceGroup; } } // Protein // We might eventually want to include more details in the spreadsheet, but for the time // being // just the name is sufficient. List<Protein3VO> proteinTab = proteinService.findByAcronymAndProposalId(sheetProposalId, proteinAcronym); if (proteinTab == null || proteinTab.size() == 0) { msgError += "Protein '" + proteinAcronym + "' can't be found \n "; } else { Protein3VO protein = proteinTab.get(0); // unique sample name List<BLSample3VO> samplesWithSameName = sampleService .findByNameAndProteinId(sampleName, protein.getProteinId(), shippingId); boolean validName = true; if (!samplesWithSameName.isEmpty()) { validName = false; msgError += "[" + protein.getAcronym() + " + " + sampleName + "] is already existing, and should be unique.\n"; } if (validName) { // Diffraction Plan DiffractionPlan3VO difPlan = new DiffractionPlan3VO(); difPlan.setNumberOfPositions(nbOfPositions); difPlan.setObservedResolution(preObsResolution); difPlan.setRequiredResolution(neededResolution); difPlan.setExposureTime((double) 0); difPlan.setPreferredBeamDiameter(preferedBeamDiameter); if (experimentType == null || experimentType.isEmpty()) { experimentType = "Default"; } difPlan.setExperimentKind(experimentType); difPlan.setRadiationSensitivity(radiationSensitivity); difPlan.setRequiredCompleteness(requiredCompleteness); difPlan.setRequiredMultiplicity(requiredMultiplicity); difPlan.setMinOscWidth(minOscWidth); difPlan = difPlanService.create(difPlan); // Crystal Crystal3VO crystal = new Crystal3VO(); crystal.setProteinVO(protein); crystal.setDiffractionPlanVO(difPlan); crystal.setCrystalUUID(crystalID); crystal.setSpaceGroup(spaceGroup); if ((crystal.getSpaceGroup() == null) || (crystal.getSpaceGroup().equals(""))) { crystal.setSpaceGroup("Undefined"); } else { // TODO SD in the case where space group is not empty and no cell dimensions // have been // entered, // fill the crystal with the default value of the crystal = protein + space // group List<Crystal3VO> tab = crystalService.findFiltered(sheetProposalId, null, proteinAcronym, spaceGroup); if (tab != null && tab.size() > 0) { Crystal3VO newCrystal3VO = new Crystal3VO(); int j = 0; for (Crystal3VO crystal3vo : tab) { newCrystal3VO = tab.get(j); j = j + 1; } if (newCrystal3VO != null && unitCellA == 0 && unitCellB == 0 && unitCellC == 0 && unitCellAlpha == 0 && unitCellBeta == 0 && unitCellGamma == 0) { unitCellA = (newCrystal3VO.getCellA() == null ? 0 : newCrystal3VO.getCellA()); unitCellB = (newCrystal3VO.getCellB() == null ? 0 : newCrystal3VO.getCellB()); unitCellC = (newCrystal3VO.getCellC() == null ? 0 : newCrystal3VO.getCellC()); unitCellAlpha = (newCrystal3VO.getCellAlpha() == null ? 0 : newCrystal3VO.getCellAlpha()); unitCellBeta = (newCrystal3VO.getCellBeta() == null ? 0 : newCrystal3VO.getCellBeta()); unitCellGamma = (newCrystal3VO.getCellGamma() == null ? 0 : newCrystal3VO.getCellGamma()); } } } // crystal.setResolution(preObsResolution); // Create the crystal name from the uuid and ligandid String crystalName = crystal.getCrystalUUID(); crystal.setName(crystalName); crystal.setCellA(unitCellA); crystal.setCellB(unitCellB); crystal.setCellC(unitCellC); crystal.setCellAlpha(unitCellAlpha); crystal.setCellBeta(unitCellBeta); crystal.setCellGamma(unitCellGamma); // crystal = crystalService.create(crystal); Crystal3VO crystalC = getCrystal(listCrystalCreated, crystal); if (crystalC == null) { crystal = crystalService.create(crystal); listCrystalCreated.add(crystal); } else { crystal = crystalC; } if (!crystal.hasCellInfo()) { msgWarning += "Warning: the unit cell parameters are not filled for the spaceGroup " + crystal.getSpaceGroup() + " (" + proteinAcronym + ")!"; } // And add the crystal to the list // addCrystal(crystal); // Holder BLSample3VO holder = new BLSample3VO(); holder.setCrystalVO(crystal); holder.setName(sampleName); holder.setCode(pinBarCode); holder.setLocation(samplePos); holder.setSmiles(smiles); // ASSUMPTION: holder is SPINE standard! holder.setHolderLength(holderLength); holder.setLoopLength(0.5); holder.setLoopType(loopType); holder.setWireWidth(0.010); holder.setComments(sampleComments); // Add holder to the container... holder.setContainerVO(container); holder = sampleService.create(holder); // container.addSampleVO(holder); holder.setDiffractionPlanVO(difPlan); holder = sampleService.update(holder); } // end validName } // end protein } // end crystalID } // end sampleRowOK } // for dataRow } // end dewar != null // all samples were empty if (sheetIsEmpty) { if (container != null) { // remove the container containerService.deleteByPk(container.getContainerId()); } // TODO understand the following and remove it // remove the dewar if no containers // Dewar3Service dewarService = (Dewar3Service) ejb3ServiceLocator.getLocalService(Dewar3Service.class); // boolean removedOK = true; // if (dewar != null) { // Dewar3VO existingDewar = dewarService.findByPk(dewar.getDewarId(), false, false); // if (existingDewarList == null || existingDewarList.isEmpty()) { // Dewar did not exist // removedOK = false; // } else {// Dewar was there, deleting it ... // dewar = dewarService.findByPk(dewar.getDewarId(), true, true); // if (dewar.getContainerVOs().size() == 0) { // dewars.remove(dewar); // } // } // } } } String[] msg = new String[2]; msg[0] = msgError; msg[1] = msgWarning; return msg; }