List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * Validate/* w ww . j ava 2 s. co 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.ISPyBParser.java
License:Open Source License
/** * RetrieveShippingId/* w w w . j a va2s. c o m*/ * * @param file * @throws Exception */ @Override public void retrieveShippingId(InputStream file) throws Exception { HSSFWorkbook workbook = null; String _shippingId = ""; Integer shippingId = null; if (this.mWorkbook == null) { // Load from file // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(file); // Now extract the workbook workbook = new HSSFWorkbook(fs); this.mWorkbook = workbook; } else { // Use pre-loaded file workbook = this.mWorkbook; } HSSFSheet sheet = workbook.getSheetAt(0); String idLabel = cellToString(sheet.getRow(idLabelRow).getCell(idLabelCol)); if (idLabel != null && idLabel.trim().equalsIgnoreCase(ProposalAndShippingLabel)) _shippingId = cellToString(sheet.getRow(value2IdRow).getCell(value2IdCol)); else if (idLabel != null && idLabel.trim().equalsIgnoreCase(ShippingLabel)) _shippingId = cellToString(sheet.getRow(value1IdRow).getCell(value1IdCol)); else _shippingId = ""; try { shippingId = Integer.decode(_shippingId); this.setShippingId(shippingId); } catch (Exception e) { shippingId = null; } }
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;/*from w w w.j a va 2s .c o m*/ 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; 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; }
From source file:it.cineca.pst.huborcid.service.OrcidFundingFileService.java
License:Open Source License
private Funding createFunding(HSSFSheet sheet, Row row) throws Exception { Funding funding = new Funding(); int indexCol = 1; try {//w w w .j av a2s . com String valueCell = row.getCell(++indexCol).getStringCellValue(); funding.setFundingType(valueCell); FundingTitle fundingTitle = new FundingTitle(); valueCell = row.getCell(++indexCol).getStringCellValue(); fundingTitle.setTitle(valueCell); funding.setFundingTitle(fundingTitle); valueCell = row.getCell(++indexCol).getStringCellValue(); funding.setShortDescription(valueCell); valueCell = row.getCell(++indexCol).getStringCellValue(); JSONObject json = new JSONObject(valueCell); String valueCellCurrencyCode = json.getString("currency-code"); String valueCellAmount = json.getString("amount"); CurrencyCode currencyCode = CurrencyCode.fromValue(valueCellCurrencyCode.toUpperCase()); Amount amount = new Amount(); amount.setCurrencyCode(currencyCode); amount.setValue(valueCellAmount); funding.setAmount(amount); valueCell = row.getCell(++indexCol).getStringCellValue(); Url url = new Url(); url.setValue(valueCell); funding.setUrl(url); valueCell = row.getCell(++indexCol).getStringCellValue(); FuzzyDate fuzzyDate = createDateFunding(valueCell); funding.setStartDate(fuzzyDate); valueCell = row.getCell(++indexCol).getStringCellValue(); fuzzyDate = createDateFunding(valueCell); funding.setEndDate(fuzzyDate); valueCell = row.getCell(++indexCol).getStringCellValue(); FundingExternalIdentifiers fundingExternalIdentifiers = createFundingExternalIdentifiers(valueCell); funding.setFundingExternalIdentifiers(fundingExternalIdentifiers); valueCell = row.getCell(++indexCol).getStringCellValue(); FundingContributors fundingContributors = createFundingContributors(valueCell); funding.setFundingContributors(fundingContributors); valueCell = row.getCell(++indexCol).getStringCellValue(); Organization organization = createOrganization(valueCell); funding.setOrganization(organization); } catch (Throwable t) { Row rowHeader = sheet.getRow(0); Cell cellHeader = rowHeader.getCell(indexCol); throw new Exception(String.format("Errore colonna %s", cellHeader.getStringCellValue())); } return funding; }
From source file:it.cineca.pst.huborcid.service.OrcidWorksFileService.java
License:Open Source License
private OrcidWork createOrcidWork(HSSFSheet sheet, Row row) throws Exception { OrcidWork orcidWork = new OrcidWork(); int indexCol = 1; try {//from w w w .j av a 2s. c om WorkTitle workTitle = new WorkTitle(); String valueCellTitle = row.getCell(++indexCol).getStringCellValue(); workTitle.setTitle(valueCellTitle); String valueCellSubtitle = row.getCell(++indexCol).getStringCellValue(); Subtitle subtitle = new Subtitle(); subtitle.setContent(valueCellSubtitle); workTitle.setSubtitle(subtitle); String valueCellTranslatedTitle = row.getCell(++indexCol).getStringCellValue(); if (valueCellTranslatedTitle != null && !valueCellTranslatedTitle.isEmpty()) { JSONObject json = new JSONObject(valueCellTranslatedTitle); String translatedTitleStr = json.getString("title"); String languageCodeStr = json.getString("lang"); LanguageCode languageCode = LanguageCode.fromValue(languageCodeStr.toLowerCase()); TranslatedTitle translatedTitle = new TranslatedTitle(); translatedTitle.setLanguageCode(languageCode); translatedTitle.setValue(translatedTitleStr); workTitle.setTranslatedTitle(translatedTitle); } orcidWork.setWorkTitle(workTitle); String valueCellJournalTitle = row.getCell(++indexCol).getStringCellValue(); if (valueCellJournalTitle != null && !valueCellJournalTitle.isEmpty()) { JournalTitle journalTitle = new JournalTitle(); journalTitle.setContent(valueCellJournalTitle); orcidWork.setJournalTitle(journalTitle); } String valueCellShortDes = row.getCell(++indexCol).getStringCellValue(); orcidWork.setShortDescription(valueCellShortDes); String valueCellCitType = row.getCell(++indexCol).getStringCellValue(); if (valueCellCitType != null && !valueCellCitType.isEmpty()) { Citation citation = new Citation(); CitationType citationType = CitationType.fromValue(valueCellCitType.toLowerCase()); citation.setWorkCitationType(citationType); String valueCellCit = row.getCell(++indexCol).getStringCellValue(); if (valueCellCit != null && !valueCellCit.isEmpty()) { citation.setCitation(valueCellCit); orcidWork.setWorkCitation(citation); } } String valueCellPubbDate = row.getCell(++indexCol).getStringCellValue(); PublicationDate publicationDate = createPublicationDate(valueCellPubbDate); orcidWork.setPublicationDate(publicationDate); String valueCellWorkType = row.getCell(++indexCol).getStringCellValue(); if (valueCellWorkType != null && !valueCellWorkType.isEmpty()) { orcidWork.setWorkType(valueCellWorkType); } String valueCellExId = row.getCell(++indexCol).getStringCellValue(); WorkExternalIdentifiers workExternalIdentifiers = createWorkExternalIdentifiers(valueCellExId); orcidWork.setWorkExternalIdentifiers(workExternalIdentifiers); String valueCellUrl = row.getCell(++indexCol).getStringCellValue(); if (valueCellUrl != null && !valueCellUrl.isEmpty()) { Url url = new Url(); url.setValue(valueCellUrl); orcidWork.setUrl(url); } String valueCellContributors = row.getCell(++indexCol).getStringCellValue(); WorkContributors workContributors = createWorkContributors(valueCellContributors); orcidWork.setWorkContributors(workContributors); String valueCellLang = row.getCell(++indexCol).getStringCellValue(); if (valueCellLang != null && !valueCellLang.isEmpty()) { LanguageCode languageCode = LanguageCode.fromValue(valueCellLang.toLowerCase()); orcidWork.setLanguageCode(languageCode); } String valueCellCountry = row.getCell(++indexCol).getStringCellValue(); if (valueCellCountry != null && !valueCellCountry.isEmpty()) { Country country = new Country(); country.setValue(valueCellCountry); orcidWork.setCountry(country); } } catch (Throwable t) { Row rowHeader = sheet.getRow(0); Cell cellHeader = rowHeader.getCell(indexCol); throw new Exception(String.format("Errore colonna %s", cellHeader.getStringCellValue())); } return orcidWork; }
From source file:it.cnr.ibimet.bikeclimate.backingbeans.FotovoltaicDataBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //Create header for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); header.getCell(i).setCellValue(columns.get(i).getHeader()); }//from w w w.j av a 2s . c om //create data table for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { header = sheet.getRow(i); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { header.getCell(j).setCellValue(dati.get(i).get(columns.get(j).getProperty())); } } }
From source file:it.cnr.ibimet.bikeclimate.backingbeans.FotovoltaicDataBean.java
public void postProcessXLSStat(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //Create header for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); header.getCell(i).setCellValue(columnsStat.get(i).getHeader()); }/*from w w w .ja va 2s. c om*/ //create data table for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { header = sheet.getRow(i); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { header.getCell(j).setCellValue(datiStat.get(i).get(columnsStat.get(j).getProperty())); } } }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static List<String[]> getCells(HSSFSheet sheet, Short[] columnAbsolutePositions, boolean looseConstraint) { List<String[]> cells = new ArrayList<String[]>(); for (int rowAbsolutePosition = sheet.getFirstRowNum() + 1; rowAbsolutePosition <= sheet .getLastRowNum(); rowAbsolutePosition++) { HSSFRow row = sheet.getRow(rowAbsolutePosition); if (row == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem"); }/* w w w . j a va 2 s . c o m*/ continue; } String cellStringValues[] = new String[5]; for (int columnRelativePosition = 0; columnRelativePosition < cellStringValues.length; columnRelativePosition++) { HSSFCell cell = row.getCell(columnAbsolutePositions[columnRelativePosition]); String cellStringValue = cell != null ? cell.getStringCellValue() : null; if (cellStringValue != null && cellStringValue.trim().equals("")) { cellStringValue = null; } cellStringValues[columnRelativePosition] = cellStringValue; } if (cellStringValues[0] == null && cellStringValues[1] == null && cellStringValues[2] == null && cellStringValues[3] == null && cellStringValues[4] == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem, it's {null, null, null, null, null}"); } } else { cells.add(cellStringValues); } } dumpCells(cells); validateCells(cells, looseConstraint); return cells; }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
private static void dumpSheet(HSSFSheet sheet) { if (sheet == null) { log.debug("The sheet to dump is null!"); return;//from w ww. j a va 2 s . c o m } for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { log.debug("row#" + i + "="); HSSFRow row = sheet.getRow(i); if (row == null) { log.debug("\t| " + NULL_RAPPRESENTATION); continue; } for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell cell = row.getCell((short) j); String cellValue = getCellValue(cell); log.debug("\t| " + cellValue); } log.debug("\n"); } }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static String getCellStringValue(HSSFSheet sheet, int row, short column) { HSSFRow cellRow = sheet.getRow(row); return getCellStringValue(cellRow, column); }