List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * export//from www . ja v a 2s . 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
/** * PopulateExistingShipment/*from w w w . j a v a 2 s . co m*/ * * @param templateFileName * @param populatedTemplateFileName * @param shippingId * @throws XlsUploadException * @throws Exception */ public void populateExistingShipment(String templateFileName, String populatedTemplateFileName, int shippingId) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileName)); HSSFWorkbook workbook = null; ShippingInformation shippingInformation = DBTools.getShippingInformation(shippingId); try { // Now extract the workbook workbook = new HSSFWorkbook(fs); int nbDewars = shippingInformation.getListDewars().size(); int nbSheetsForDewar = 6; int nbSheetstoCopy = (nbDewars == 0) ? 0 : (nbDewars * nbSheetsForDewar) - 1; // Copy right number of sheets = 1 per dewar for (int d = 1; d <= nbSheetstoCopy; d++) { workbook.cloneSheet(0); } // Populate Sheet for (int dew = 0; dew < nbDewars; dew++) { int sheetStart = (dew == 0) ? 0 : (dew * nbSheetsForDewar); int sheetStop = ((dew + 1) * nbSheetsForDewar) - 1; int puckNumber = 1; for (int s = sheetStart; s <= sheetStop; s++) { String dewarCode = shippingInformation.getListDewars().get(dew).dewar.getCode(); if (dewarCode == null || dewarCode.trim().equalsIgnoreCase("")) dewarCode = Integer.toString(dew); String puckCode = "Puck" + puckNumber; // Populate workbook.setSheetName(s, dewarCode + "_" + puckNumber); HSSFSheet sheet = workbook.getSheetAt(s); sheet.setProtect(false); // Dewar Code HSSFRow row = sheet.getRow(dewarRow); if (row == null) row = sheet.createRow(dewarRow); HSSFCell cell = row.getCell(dewarCol); if (cell == null) cell = row.createCell(dewarCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dewarCode)); // Puck Code row = sheet.getRow(puckRow); if (row == null) row = sheet.createRow(puckRow); cell = row.getCell(puckCol); if (cell == null) cell = row.createCell(puckCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(puckCode)); // LabelCode row = sheet.getRow(idLabelRow); if (row == null) row = sheet.createRow(idLabelRow); cell = row.getCell(idLabelCol); if (cell == null) cell = row.createCell(idLabelCol); cell.setCellValue(new HSSFRichTextString(ProposalAndShippingLabel)); // ProposalId Integer proposalId = shippingInformation.getShipping().getProposalVOId(); row = sheet.getRow(value1IdRow); if (row == null) row = sheet.createRow(value1IdRow); cell = row.getCell(value1IdCol); if (cell == null) cell = row.createCell(value1IdCol); cell.setCellValue(proposalId); // ShippingId row = sheet.getRow(value2IdRow); if (row == null) row = sheet.createRow(value2IdRow); cell = row.getCell(value2IdCol); if (cell == null) cell = row.createCell(value2IdCol); cell.setCellValue(shippingId); // Courrier Name String courrierName = shippingInformation.getShipping().getDeliveryAgentAgentName(); row = sheet.getRow(courrierNameRow); if (row == null) row = sheet.createRow(courrierNameRow); cell = row.getCell(courrierNameCol); if (cell == null) cell = row.createCell(courrierNameCol); cell.setCellValue(new HSSFRichTextString(courrierName)); // Tracking Number String trackingNumber = shippingInformation.getShipping().getDeliveryAgentAgentCode(); row = sheet.getRow(trackingNumberRow); if (row == null) row = sheet.createRow(trackingNumberRow); cell = row.getCell(trackingNumberCol); if (cell == null) cell = row.createCell(trackingNumberCol); cell.setCellValue(new HSSFRichTextString(trackingNumber)); // Shipping Date Date _shippingDate = shippingInformation.getShipping().getDeliveryAgentShippingDate(); String shippingDate = ""; if (_shippingDate != null) shippingDate = _shippingDate.getDay() + "/" + _shippingDate.getMonth() + "/" + (_shippingDate.getYear() + 1900); row = sheet.getRow(shippingDateRow); if (row == null) row = sheet.createRow(shippingDateRow); cell = row.getCell(shippingDateCol); if (cell == null) cell = row.createCell(shippingDateCol); cell.setCellValue(new HSSFRichTextString(shippingDate)); sheet.setProtect(true); puckNumber++; } } } 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; } // ave 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
/** * populate/* w w w . j a va 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/*from ww w . j a v a 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// www . j a va2 s . 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 ww w . ja v a 2 s .com*/ 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.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static HSSFSheet getSheetFromFile(String filename, String sheetname) { HSSFSheet sheet = null;// www . j a v a2 s.c o m try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); sheet = wb.getSheet(sheetname); } catch (Exception e) { log.error(e); } dumpSheet(sheet); return sheet; }
From source file:it.smartcommunitylab.riciclo.app.importer.converter.DataImporter.java
License:Apache License
private Rifiuti readExcel(InputStream inp) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); Rifiuti rifiuti = new Rifiuti(); Set<String> sheetNames = Sets.newHashSet(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheetNames.add(wb.getSheetAt(i).getSheetName()); }/*from w w w.j ava2 s .com*/ Set missingExpected = Sets.newHashSet(expectedSheets); missingExpected.removeAll(sheetNames); Set additionalFound = Sets.newHashSet(sheetNames); additionalFound.removeAll(expectedSheets); if (!missingExpected.isEmpty() || !additionalFound.isEmpty()) { throw new ImportError(Lists.newArrayList("Missing sheet(s) expected: " + missingExpected, "Additional sheet(s) found: " + additionalFound)); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Thread.sleep(500); // System.out.println(sheet.getSheetName()); // if (sheet.getRow(0).getLastCellNum() == 1 && !oneColumnAsMany.contains(sheet.getSheetName())) { // } else { { System.err.println(">" + sheet.getSheetName()); List<Map<String, String>> result = getSheetMap(sheet); mapMap(rifiuti, sheet.getSheetName(), result); } } completePuntiRaccolta(rifiuti); return rifiuti; }
From source file:it.smartcommunitylab.ungiorno.importer.Importer.java
License:Apache License
private HSSFWorkbook readFile(InputStream inp, Set<String> expected) throws IOException, ImportError { HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); Set<String> sheetNames = Sets.newHashSet(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheetNames.add(wb.getSheetAt(i).getSheetName()); }//from w ww . ja v a 2 s .c om Set<String> missingExpected = new HashSet<String>(expected); missingExpected.removeAll(sheetNames); Set<String> additionalFound = Sets.newHashSet(sheetNames); additionalFound.removeAll(expected); if (!missingExpected.isEmpty() || !additionalFound.isEmpty()) { System.err.println("Missing sheet(s) expected: " + missingExpected + " - Additional sheet(s) found: " + additionalFound); throw new ImportError(Lists.newArrayList("Missing sheet(s) expected: " + missingExpected, "Additional sheet(s) found: " + additionalFound)); } return wb; }
From source file:javaapplication1.HWPFTest.java
public static void doStuff() { String filePath = "D:\\insiders_report4.doc"; POIFSFileSystem fs = null;/*from www. j ava 2 s .c om*/ try { fs = new POIFSFileSystem(new FileInputStream(filePath)); HWPFDocument doc = new HWPFDocument(fs); doc = replaceText(doc, "#FIO#", " ? ?"); doc = replaceText(doc, "#BIN#", "900524300077"); doc = replaceText(doc, "#INCDAY#", "05"); doc = replaceText(doc, "#INCMONTH#", ""); doc = replaceText(doc, "#INCYEAR#", "2016"); doc = replaceText(doc, "#EXCDAY#", "05"); doc = replaceText(doc, "#EXCMONTH#", ""); doc = replaceText(doc, "#EXCYEAR#", "2016"); doc = replaceText(doc, "#MAINCHIEF#", " ?"); saveWord("D:\\result.doc", doc); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }