List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getSheetName
@SuppressWarnings("resource") @Override public String getSheetName()
From source file:servlets.servlets_resources.Analysis_XLS_parser.java
License:Open Source License
/** * * @param sheet/*w w w .ja v a2 s . c o m*/ * @param rawDataInstance * @return * @throws Exception */ public static ColumnChromatography parse_ColumnChromatography_XLSfile(HSSFSheet sheet, RAWdata rawDataInstance) throws Exception { ColumnChromatography columnChromatography = new ColumnChromatography(); //************************************************************************************************************************************** //**PARSING THE MIAPE-GC INFORMATION**************************************************************************************************** //************************************************************************************************************************************** if ("MIAPE-GC".equals(sheet.getSheetName())) { columnChromatography.setColumnChromatographyType("Gas"); } else if ("MIAPE-LC".equals(sheet.getSheetName())) { columnChromatography.setColumnChromatographyType("Liquid"); } else { throw new Exception( "Error trying to insert the GC-MS / LC-MS information: Invalid File, expected MIAPE-CC sheet not found."); } int i = 2; //General features rawDataInstance.setStepName(sheet.getRow(i).getCell(1).getStringCellValue()); i++; rawDataInstance.setAnalyticalSampleID(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setSampleDescription(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setSampleProcessing(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setSampleInjection(sheet.getRow(i).getCell(1).getStringCellValue()); //Equipment - Column: Product details i += 2; columnChromatography.setColumnManufacturer(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setColumnModel(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setSeparationMode(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDimensions(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDescriptionOfStationaryPhase(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setAdditionalAccessories(sheet.getRow(i).getCell(1).getStringCellValue()); //Equipment - Chromatography system used for separation i += 2; columnChromatography.setCombinedUnitManufacturer(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setCombinedUnitModel(sheet.getRow(i).getCell(1).getStringCellValue()); //Mobile phases (Describe all the mobile phases of the run) i += 2; int columnPosition = 1; HSSFRow namesRow = sheet.getRow(i); HSSFRow descriptionRow = sheet.getRow(i + 1); String mobilePhaseName = namesRow.getCell(columnPosition).getStringCellValue(); ColumnChromatography.MobilePhase mobilePhase = null; while (mobilePhaseName != "") { mobilePhase = columnChromatography.getNewMobilePhase(); mobilePhase.setName(mobilePhaseName); mobilePhase.setDescription(descriptionRow.getCell(columnPosition).getStringCellValue()); columnChromatography.addMobilePhase(mobilePhase); columnPosition++; mobilePhaseName = namesRow.getCell(columnPosition).getStringCellValue(); } //Column Run properties i += 3; columnChromatography.setTime(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setGradient(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setFlowRate(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setTemperature(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setTemperature(sheet.getRow(i).getCell(1).getStringCellValue()); //Pre and Post Run processes i += 2; columnChromatography.setPreRunProcessType(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setPreRunProcessSubstance(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setPreRunProcessTime(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setPreRunProcessFlowrate(sheet.getRow(i).getCell(1).getStringCellValue()); //Column outputs - Detection i += 2; columnChromatography.setDetectionEquipment(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDetectionType(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDetectionEquipmentSettings(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDetectionTimescale(sheet.getRow(i).getCell(1).getStringCellValue()); i++; columnChromatography.setDetectionTrace(sheet.getRow(i).getCell(1).getStringCellValue()); //Mobile phases (Describe all the mobile phases of the run) i += 2; columnPosition = 1; namesRow = sheet.getRow(i); descriptionRow = sheet.getRow(i + 1); String fractionName = namesRow.getCell(columnPosition).getStringCellValue(); ColumnChromatography.Fraction fraction = null; while (fractionName != "") { fraction = columnChromatography.getNewFraction(); fraction.setName(fractionName); fraction.setDescription(descriptionRow.getCell(columnPosition).getStringCellValue()); columnChromatography.addFraction(fraction); columnPosition++; fractionName = namesRow.getCell(columnPosition).getStringCellValue(); } return columnChromatography; }
From source file:servlets.servlets_resources.Analysis_XLS_parser.java
License:Open Source License
/** * * @param sheet//ww w .j a va 2 s . co m * @param rawDataInstance * @return * @throws Exception */ public static MassSpectrometry parse_MassSpectrometry_XLSfile(HSSFSheet sheet, RAWdata rawDataInstance) throws Exception { MassSpectrometry massSpectrometry = new MassSpectrometry(); //************************************************************************************************************************************** //**PARSING THE MIAPE-MS INFORMATION**************************************************************************************************** //************************************************************************************************************************************** if (!"MIAPE-MS".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the GC-MS / LC-MS information: Invalid File, expected MIAPE-MS sheet not found."); } int i = 2; //Equipment details massSpectrometry.setMassSpectrometerManufacturer(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setCustomizations(sheet.getRow(i).getCell(1).getStringCellValue()); i += 3; if (!"".equals(sheet.getRow(i).getCell(1).getStringCellValue())) { massSpectrometry.setIonizationSource("Electrospray Ionisation (ESI)"); massSpectrometry.setSupplyType(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setInterfaceManufacturerAndModel(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setSprayerTypeManufacturerAndModel(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setOtherElectrosprayIonisation(sheet.getRow(i).getCell(1).getStringCellValue()); } else if (!"".equals(sheet.getRow(i + 5).getCell(1).getStringCellValue())) { i += 5; massSpectrometry.setIonizationSource("MALDI"); massSpectrometry.setPlateComposition(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setMatrixComposition(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setPsdSummary(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setLaserTypeAndWavelength(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setOtherMALDI(sheet.getRow(i).getCell(1).getStringCellValue()); } else { i += 11; massSpectrometry.setIonizationSource("Other ion source"); massSpectrometry.setOtherIonizationDescription(sheet.getRow(i).getCell(1).getStringCellValue()); } //Post source component details (analyser) i = 19; massSpectrometry.setMassAnalyzerType(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setReflectronStatus(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setActivationLocation(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setGasType(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setActivationType(sheet.getRow(i).getCell(1).getStringCellValue()); //Spectrum and peak list generation and annotation (data acquisition) i += 2; massSpectrometry.setAcquisitionSoftware(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setAcquisitionParameters(sheet.getRow(i).getCell(1).getStringCellValue()); //Spectrum and peak list generation and annotation (data analysis) i += 2; massSpectrometry.setAnalysisSoftware(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setAnalysisParameters(sheet.getRow(i).getCell(1).getStringCellValue()); //Spectrum and peak list generation and annotation (resulting data i += 2; String fileLocations = sheet.getRow(i).getCell(1).getStringCellValue(); rawDataInstance.setFilesLocation(fileLocations.split("\n")); i++; massSpectrometry.setIntensityValues(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setMSlevel(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setIonMode(sheet.getRow(i).getCell(1).getStringCellValue()); i++; massSpectrometry.setAdditionalInfo(sheet.getRow(i).getCell(1).getStringCellValue()); return massSpectrometry; }
From source file:servlets.servlets_resources.BioCondition_XLS_parser.java
License:Open Source License
public static Object[] parseXLSfile(File file, String owner) throws Exception { HashMap<String, Batch> batchesTable = new HashMap<String, Batch>(); HashMap<String, Protocol> protocolTable = new HashMap<String, Protocol>(); HashMap<String, Bioreplicate> bioreplicatesTable = new HashMap<String, Bioreplicate>(); ArrayList<BioCondition> biocondition_list = new ArrayList<BioCondition>(); InputStream input = new BufferedInputStream(new FileInputStream(file)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); int sheetNumber = wb.getNumberOfSheets(); if (sheetNumber < 3) { throw new Exception( "Error trying to insert the Sample information: Invalid File, template file must have 3 sheets."); }/*from ww w .j a va2s. co m*/ //PARSE THE COMMON BIOLOGICAL CONDITION INFORMATION HSSFSheet sheet = wb.getSheetAt(0); if (!"COMMON BIOLOGICAL CONDITION INF".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the Sample information: Invalid File, expected COMMON BIOLOGICAL CONDITION INF sheet not found."); } BioCondition biocondition_common = new BioCondition(); biocondition_common.setTitle(sheet.getRow(3).getCell(1).getStringCellValue()); biocondition_common.setName(sheet.getRow(4).getCell(1).getStringCellValue()); biocondition_common.setOrganism(sheet.getRow(5).getCell(1).getStringCellValue()); biocondition_common.setTissueType(sheet.getRow(6).getCell(1).getStringCellValue()); biocondition_common.setCellType(sheet.getRow(7).getCell(1).getStringCellValue()); biocondition_common.setCellLine(sheet.getRow(8).getCell(1).getStringCellValue()); biocondition_common.setGender(sheet.getRow(9).getCell(1).getStringCellValue()); biocondition_common.setGenotype(sheet.getRow(10).getCell(1).getStringCellValue()); biocondition_common.setOtherBiomat(sheet.getRow(11).getCell(1).getStringCellValue()); biocondition_common.setTreatment(sheet.getRow(13).getCell(1).getStringCellValue()); biocondition_common.setDosis(sheet.getRow(14).getCell(1).getStringCellValue()); biocondition_common.setTime(sheet.getRow(15).getCell(1).getStringCellValue()); biocondition_common.setOtherExpCond(sheet.getRow(16).getCell(1).getStringCellValue()); biocondition_common.setProtocolDescription(sheet.getRow(17).getCell(1).getStringCellValue()); biocondition_common.setExternalLinks(sheet.getRow(19).getCell(1).getStringCellValue()); Date date_aux = new Date(); String today = String.format("%02d", date_aux.getYear() + 1900) + String.format("%02d", date_aux.getMonth() + 1) + String.format("%02d", date_aux.getDate()); biocondition_common.setLastEditionDate(today); biocondition_common.setSubmissionDate(today); biocondition_common.setBioConditionID("BC" + (biocondition_list.size() + 1)); //TODO: CAMBIAR ESTO!! biocondition_common.addOwner(new User(owner, "")); //************************************************************************************************************************************** //**BATCHES PARSING********************************************************************************************************************* //************************************************************************************************************************************** sheet = wb.getSheetAt(1); if (!"BATCHES INFO".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the Sample information: Invalid File, expected BATCHES INFO sheet not found."); } Iterator rows = sheet.rowIterator(); //IGNORE THE FIRST 4 ROWS for (int i = 0; i < 4; i++) { rows.next(); } Batch batch; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); String batch_id = row.getCell(0).getStringCellValue(); String batch_name = row.getCell(1).getStringCellValue(); if (batch_name.isEmpty()) { break; } Date batch_date = row.getCell(2).getDateCellValue(); String batch_date_string = String.format("%02d", batch_date.getYear() + 1900) + String.format("%02d", batch_date.getMonth() + 1) + String.format("%02d", batch_date.getDate()); String batch_description = row.getCell(3).getStringCellValue(); batch = new Batch(); batch.setBatchID(batch_id); batch.setBatchName(batch_name); batch.setBatchCreationDate(batch_date_string); batch.setDescription(batch_description); batch.addOwner(new User(owner, "")); batchesTable.put(batch_id, batch); } //************************************************************************************************************************************** //**PROTOCOL PARSING********************************************************************************************************************* //************************************************************************************************************************************** sheet = wb.getSheetAt(3); if (!"PROTOCOLS INFO".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the Sample information: Invalid File, expected PROTOCOLS INFO sheet not found."); } rows = sheet.rowIterator(); //IGNORE THE FIRST 4 ROWS for (int i = 0; i < 4; i++) { rows.next(); } Protocol protocol; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); String protocol_id = row.getCell(0).getStringCellValue(); String protocol_name = row.getCell(1).getStringCellValue(); if (protocol_name.isEmpty()) { break; } String extracted_molecule = row.getCell(2).getStringCellValue(); String protocol_description = row.getCell(3).getStringCellValue(); protocol = new Protocol(); protocol.setProtocolID(protocol_id); protocol.setProtocolName(protocol_name); protocol.setBiomolecule(extracted_molecule); protocol.setDescription(protocol_description); protocol.addOwner(new User(owner, "")); protocolTable.put(protocol_id, protocol); } //PARSE THE BIOLOGICAL CONDITION INFORMATION sheet = wb.getSheetAt(2); if (!"BIOLOGICAL REPLICATES INFO".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the Sample information: Invalid File, expected BIOLOGICAL REPLICATES INFO sheet not found."); } Bioreplicate bioreplicateInstance = null; rows = sheet.rowIterator(); //IGNORE THE FIRST 6 ROWS for (int i = 0; i < 6; i++) { rows.next(); } while (rows.hasNext()) { //GET THE ROW HSSFRow row = (HSSFRow) rows.next(); if (row.getCell(0) == null) { break; } //GET THE FIRST 3 FIELDS String bioreplicate_id = row.getCell(0).getStringCellValue(); String bioreplicate_name = row.getCell(1).getStringCellValue(); if (bioreplicate_name.isEmpty()) { break; } bioreplicateInstance = new Bioreplicate("", "", bioreplicate_name); String batch_id = row.getCell(2).getStringCellValue(); if (!batch_id.isEmpty()) { Batch associatedBatch = batchesTable.get(batch_id); //If the specified batch is not a to-be-added batch it should be added previously in the db if (associatedBatch == null) { associatedBatch = new Batch(); associatedBatch.setBatchID(batch_id); } bioreplicateInstance.setAssociatedBatch(associatedBatch); } bioreplicatesTable.put(bioreplicate_id, bioreplicateInstance); //PARSE THE VARITIONS IN BIOLOGICAL CONDITION (IF EXISTS) BioCondition biocondition_tmp = (BioCondition) biocondition_common.clone(); if (!row.getCell(3).getStringCellValue().isEmpty()) { biocondition_tmp.setTitle(row.getCell(3).getStringCellValue()); } if (!row.getCell(4).getStringCellValue().isEmpty()) { biocondition_tmp.setName(row.getCell(4).getStringCellValue()); } if (!row.getCell(5).getStringCellValue().isEmpty()) { biocondition_tmp.setOrganism(row.getCell(5).getStringCellValue()); } if (!row.getCell(6).getStringCellValue().isEmpty()) { biocondition_tmp.setTissueType(row.getCell(6).getStringCellValue()); } if (!row.getCell(7).getStringCellValue().isEmpty()) { biocondition_tmp.setCellType(row.getCell(7).getStringCellValue()); } if (!row.getCell(8).getStringCellValue().isEmpty()) { biocondition_tmp.setCellLine(row.getCell(8).getStringCellValue()); } if (!row.getCell(9).getStringCellValue().isEmpty()) { biocondition_tmp.setGender(row.getCell(9).getStringCellValue()); } if (!row.getCell(10).getStringCellValue().isEmpty()) { biocondition_tmp.setGenotype(row.getCell(10).getStringCellValue()); } if (!row.getCell(11).getStringCellValue().isEmpty()) { biocondition_tmp.setOtherBiomat(row.getCell(11).getStringCellValue()); } if (!row.getCell(12).getStringCellValue().isEmpty()) { biocondition_tmp.setTreatment(row.getCell(12).getStringCellValue()); } if (!row.getCell(13).getStringCellValue().isEmpty()) { biocondition_tmp.setDosis(row.getCell(13).getStringCellValue()); } if (!row.getCell(14).getStringCellValue().isEmpty()) { biocondition_tmp.setTime(row.getCell(14).getStringCellValue()); } if (!row.getCell(15).getStringCellValue().isEmpty()) { biocondition_tmp.setOtherExpCond(row.getCell(15).getStringCellValue()); } if (!row.getCell(16).getStringCellValue().isEmpty()) { biocondition_tmp.setProtocolDescription(row.getCell(16).getStringCellValue()); } //CHECK IF NO ONE FIELD WAS FILLED, IF SO THE BIOREPLICATE SHOULD BE ADDED TO THE //COMMON BIOLOGICAL CONDITION's BIOREPLICATE LIST int i = 0; for (i = 0; i < biocondition_list.size(); i++) { if (biocondition_list.get(i).hasSameValues(biocondition_tmp)) { biocondition_list.get(i).addAssociatedBioreplicate(bioreplicateInstance); break; } } //IF NO SIMILAR biocondition WAS FOUND, WE SHOULD ADD A NEW ONE if (i == biocondition_list.size()) { biocondition_tmp.addAssociatedBioreplicate(bioreplicateInstance); biocondition_tmp.setBioConditionID("BS" + (biocondition_list.size() + 1)); biocondition_list.add(biocondition_tmp); } } //************************************************************************************************************************************** //**PROTOCOL PARSING********************************************************************************************************************* //************************************************************************************************************************************** sheet = wb.getSheetAt(4); if (!"ANALYTICAL SAMPLES INFO".equals(sheet.getSheetName())) { throw new Exception( "Error trying to insert the Sample information: Invalid File, expected PROTOCOLS INFO sheet not found."); } rows = sheet.rowIterator(); //IGNORE THE FIRST 4 ROWS for (int i = 0; i < 4; i++) { rows.next(); } AnalyticalReplicate analyticalSampleInstance; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); String bioreplicateID = row.getCell(0).getStringCellValue(); String protocolID = row.getCell(1).getStringCellValue(); String analyticalSampleName = row.getCell(2).getStringCellValue(); if (analyticalSampleName.isEmpty()) { break; } bioreplicateInstance = bioreplicatesTable.get(bioreplicateID); analyticalSampleInstance = new AnalyticalReplicate(); analyticalSampleInstance.setBioreplicateID(bioreplicateInstance.getBioreplicateID()); analyticalSampleInstance.setProtocolID(protocolID); analyticalSampleInstance.setAnalyticalReplicateName(analyticalSampleName); bioreplicateInstance.addAssociatedAnalyticalReplicate(analyticalSampleInstance); } Object[] data = new Object[3]; data[0] = biocondition_list; data[1] = batchesTable; data[2] = protocolTable; return data; }
From source file:ve.zoonosis.model.imports.CasosDiarioPorMunicipio.java
License:Apache License
private void cargarObjects(HSSFSheet sheet, int row, int column, Object value) { if (value != null) { int monthIndex = Arrays.binarySearch(months, sheet.getSheetName(), new Comparator<String>() { @Override/* ww w. j a v a2 s.co m*/ public int compare(String o1, String o2) { return o1.compareToIgnoreCase(o2); } }); if (monthIndex == -1) { return; } int dia = getCellHeaderValue(sheet, 0, column); monthIndex++; String fecha = dia + "/" + monthIndex; HashMap<String, Caso> m = casos.get(fecha); if (m == null) { m = new HashMap<>(); } String parroquia = obtenrValor(sheet.getRow(row).getCell(0)); Caso c = m.get(parroquia); if (c == null) { c = new Caso(new Date(fecha + "/" + year), new Semana( (String) getCellHeaderValue(sheet, sheet.getPhysicalNumberOfRows() - 1, column), year)); c.setParroquia(new Parroquia(parroquia, municipio)); // v.getRegistroVacunacion().add(new RegistroVacunacion(v, LoginController.getUsuario())); } // RegistroVacunacion rv = v.getRegistroVacunacion().get(0); // rv.getVacunacion_has_Animal().add(new RegistroVacunacion_has_Animal(rv, // new Animal((String) obtenrValor(sheet.getRow(1).getCell(column))), // (Integer) value)); // m.put(parroquia, v); casos.put(fecha, m); } }
From source file:ve.zoonosis.model.imports.VacunadosDiarioPorMunicipio.java
License:Apache License
private void cargarObjects(HSSFSheet sheet, int row, int column, Object value) throws ParseException { if (value != null) { int monthIndex = Arrays.binarySearch(months, sheet.getSheetName(), new Comparator<String>() { @Override// www. j a va2s . c o m public int compare(String o1, String o2) { return o1.compareToIgnoreCase(o2); } }); if (monthIndex == -1) { return; } int dia = getCellHeaderValue(sheet, 0, column); monthIndex++; String fecha = dia + "/" + monthIndex; HashMap<String, Vacunacion> m = vacunacion.get(fecha); if (m == null) { m = new HashMap<>(); } String parroquia = obtenrValor(sheet.getRow(row).getCell(0)); Vacunacion v = m.get(parroquia); if (v == null) { v = new Vacunacion(format.parse(fecha + "/" + year), new Semana( (String) getCellHeaderValue(sheet, sheet.getPhysicalNumberOfRows() - 1, column), year)); v.setParroquia(new Parroquia(parroquia, municipio)); v.getRegistroVacunacion().add(new RegistroVacunacion(v, LoginController.getUsuario())); } RegistroVacunacion rv = v.getRegistroVacunacion().get(0); rv.getVacunacion_has_Animal().add(new RegistroVacunacion_has_Animal(rv, new Animal((String) obtenrValor(sheet.getRow(1).getCell(column))), (Integer) value)); m.put(parroquia, v); vacunacion.put(fecha, m); } }