List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main//from w ww.ja va 2s .c om * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {/* ww w .j a v a 2 s. co m*/ HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:rocky.sizecounter.SizeCounterUtil.java
License:Apache License
/** * Count size of Excel file.//from w w w . j a v a2 s .c o m * * @param filePath path of Excel file * @return SizeMetaData: Unit,Size: SHEET; Unit1,Size1:PAGE */ public static SizeMetaData countSpreadSheet(String filePath) { InputStream is = null; SizeMetaData sizeMD = new SizeMetaData(); int nmPage = 0; int nmSheet; try { is = CommonUtil.loadResource(filePath); if (CommonUtil.getExtension(filePath).equals("xls")) { HSSFWorkbook wb = new HSSFWorkbook(is); wb.getDocumentSummaryInformation().getLineCount(); nmSheet = wb.getNumberOfSheets(); HSSFSheet sheet; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); // Count approximately number of page nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight(); } sizeMD.setUnit(UnitType.SHEET); sizeMD.setSize(nmSheet); sizeMD.setUnit1(UnitType.PAGE); sizeMD.setSize1(nmPage); } else if (CommonUtil.getExtension(filePath).equals("xlsx")) { XSSFWorkbook xwb = new XSSFWorkbook(is); nmSheet = xwb.getNumberOfSheets(); XSSFSheet sheet; for (int i = 0; i < xwb.getNumberOfSheets(); i++) { sheet = xwb.getSheetAt(i); // Count approximately number of page nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight(); } sizeMD.setUnit(UnitType.SHEET); sizeMD.setSize(nmSheet); sizeMD.setUnit1(UnitType.PAGE); sizeMD.setSize1(nmPage); } } catch (FileNotFoundException ex) { LOG.warn("Invalid when reading file.", ex); } catch (IOException ex) { LOG.warn("Invalid when reading file.", ex); } catch (Exception e) { LOG.warn("Can not count file " + new File(filePath).getName(), e); } finally { if (is != null) { try { is.close(); } catch (IOException ex) { LOG.warn("Close the file input stream", ex); } } } return sizeMD; }
From source file:rocky.sizecounter.SizeCounterUtil.java
License:Apache License
/** * Count excel's number of sheet from input directory. * //ww w .j a va 2s . c o m * @param filePath path of Excel file * @return sheet */ public int countSheet(String filePath) { FileInputStream fis = null; int sheet = 0; try { fis = new FileInputStream(filePath); if (CommonUtil.getExtension(filePath).equals("xls")) { HSSFWorkbook doc = new HSSFWorkbook(fis); sheet = doc.getNumberOfSheets(); } else if (CommonUtil.getExtension(filePath).equals("xlsx")) { XSSFWorkbook doc = new XSSFWorkbook(fis); sheet = doc.getNumberOfSheets(); } } catch (FileNotFoundException ex) { LOG.warn("Invalid when reading file.", ex); } catch (IOException ex) { LOG.warn("Invalid when reading file.", ex); } catch (Exception e) { LOG.warn("Can not count file " + new File(filePath).getName(), e); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { LOG.warn("Close the file input stream", ex); } } } return sheet; }
From source file:service.EventService.java
public void readXls(MultipartFile fileXls, Long[] tagIds, Long pkId, Long campaignId, Boolean update) throws Exception { Campaign campaign = campaignDao.find(campaignId); //List<String> uniqs = campaignDao.getUniqs(campaignId, pkId); if (!Objects.equals(campaign.getStatus(), Campaign.CLOSE)) { PersonalCabinet pk = personalCabinetDao.find(pkId); HashMap<String, String> commentMap = new HashMap(); List<Client> clientsListForSave = new ArrayList(); List<Client> clientsListForUpdate = new ArrayList(); List<Event> eventsListForSave = new ArrayList(); HashMap<String, Client> addedInPkClientsMap = getClientsMapInPk(pkId); HashMap<String, Client> addedInCampaignClientsMap = getClientsMapInCampaign(campaignId, pkId); //HashMap<String, Client> addingClientsMapForSave = new HashMap(); List<Client> noContactList = new ArrayList(); List<Integer> noUniqueIdList = new ArrayList(); InputStream fis = fileXls.getInputStream(); HSSFWorkbook inputWorkbook = new HSSFWorkbook(fis); int sheetCount = inputWorkbook.getNumberOfSheets(); boolean nomoreclients = false; for (int i = 0; i < sheetCount; i++) { HSSFSheet hss = inputWorkbook.getSheetAt(i); int rowCount = 0; Iterator<Row> it = hss.iterator(); while (it.hasNext()) { rowCount++;/*from www . ja v a2 s. c o m*/ Row rw = it.next(); if (!(StringAdapter.getString(rw.getCell(0))).trim() .equals("? ")) { String uid = StringAdapter.HSSFSellValue(rw.getCell(0)); String name = StringAdapter.HSSFSellValue(rw.getCell(1)); String secretaryPhone = HSSFPhoneValue(rw.getCell(2)); String comment = StringAdapter.HSSFSellValue(rw.getCell(3)); String contface = StringAdapter.HSSFSellValue(rw.getCell(4)); String lprPhone = HSSFPhoneValue(rw.getCell(5)); String namelpr = StringAdapter.HSSFSellValue(rw.getCell(6)); String adress = StringAdapter.HSSFSellValue(rw.getCell(7)); if (uid.equals("") && (!name.equals("") || !secretaryPhone.equals("") || !lprPhone.equals(""))) { noUniqueIdList.add(rowCount); } else if (!uid.equals("") && !name.equals("") && (!secretaryPhone.equals("") || !lprPhone.equals(""))) { // if (!addedInPkClientsMap.keySet().contains(uid)) { Client cl = new Client(); cl.setUniqueId(uid); cl.setNameCompany(name); cl.setNameSecretary(contface); cl.setNameLpr(namelpr); cl.setPhoneSecretary(secretaryPhone); cl.setPhoneLpr(lprPhone); cl.setAddress(adress); commentMap.put(uid, comment); cl.setCabinet(pk); if (validate(cl)) { if ((secretaryPhone != null && !secretaryPhone.equals("")) || (lprPhone != null && !lprPhone.equals(""))) { if (adminService.mayAddClient(pkId)) { clientsListForSave.add(cl); } else { nomoreclients = true; } } else { noContactList.add(cl); } } } else { Client cl = addedInPkClientsMap.get(uid); if (update) { cl.setUniqueId(uid); cl.setNameCompany(name); cl.setNameSecretary(contface); cl.setNameLpr(namelpr); cl.setPhoneSecretary(secretaryPhone); cl.setPhoneLpr(lprPhone); cl.setAddress(adress); commentMap.put(uid, comment); cl.setCabinet(pk); if (validate(cl)) { if ((secretaryPhone != null && !secretaryPhone.equals("")) || (lprPhone != null && !lprPhone.equals(""))) { clientsListForUpdate.add(cl); } else { noContactList.add(cl); } } } // ?, if (!addedInCampaignClientsMap.keySet().contains(uid)) { Event event = new Event(); event.setCabinet(pk); event.setClient(cl); event.setUniqueId(uid); event.setCampaign(campaign); event.setComment(StringAdapter.getString(commentMap.get(cl.getUniqueId()))); event.setStatus(Event.UNASSIGNED); if (validate(event)) { eventsListForSave.add(event); } } } } } } if (nomoreclients) { addError( " ? ?? ? ? "); } } if (noContactList.isEmpty() && noUniqueIdList.isEmpty()) { for (Client cl : clientsListForSave) { clientDao.save(cl); if (tagIds != null && tagIds.length > 0) { tagService.addTagsToClient(cl.getId(), tagIds, pkId); } Event event = new Event(); event.setCabinet(pk); event.setClient(cl); event.setUniqueId(cl.getUniqueId()); event.setCampaign(campaign); event.setComment(StringAdapter.getString(commentMap.get(cl.getUniqueId()))); event.setStatus(Event.UNASSIGNED); if (validate(event)) { eventsListForSave.add(event); } } for (Client cl : clientsListForUpdate) { clientDao.update(cl); if (tagIds != null && tagIds.length > 0) { tagService.addTagsToClient(cl.getId(), tagIds, pkId); } } for (Event ev : eventsListForSave) { eventDao.save(ev); addEventComment(" ", EventComment.CREATE, ev, pkId); addEventComment(": " + ev.getComment(), EventComment.COMMENTED, ev, pkId); } } else { if (!noContactList.isEmpty()) { String err = "? ? : "; for (Client cl : noContactList) { err += cl.getUniqueId() + "; "; } addError(err + " ? ? ."); } if (!noUniqueIdList.isEmpty()) { String err = "? ? ?: "; for (Integer rc : noUniqueIdList) { err += rc + "; "; } addError(err); } } } else { addError("?? ."); } }
From source file:servlets.servlets_resources.Analysis_XLS_parser.java
License:Open Source License
/** * * @param file//w w w. j a v a2 s. c o m * @param owners * @return * @throws Exception */ public static RAWdata parse_GCMS_XLSfile(File file, String[] owners) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(file)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); int sheetNumber = wb.getNumberOfSheets(); if (sheetNumber < 2) { throw new Exception( "Error trying to insert the GC-MS information: Invalid File, template file must have 2 sheets."); } RAWdata rawDataInstance = new RAWdata("STxxxxx.1"); rawDataInstance.setRawDataType("Proteomics"); ColumnChromatography columnChromatography = parse_ColumnChromatography_XLSfile(wb.getSheetAt(0), rawDataInstance); MassSpectrometry massSpectrometry = parse_MassSpectrometry_XLSfile(wb.getSheetAt(1), rawDataInstance); massSpectrometry.setSeparationMethod(columnChromatography); rawDataInstance.setExtractionMethod(massSpectrometry); return rawDataInstance; }
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 w ww .j a v a2 s . c o 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:shouQiSystem.user.ReadDriverEXL.java
public ArrayList<Driver> readXls(String filePath) throws IOException { InputStream is = new FileInputStream(filePath); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); Driver driver = null;//from w ww . j ava 2 s . c om ArrayList<Driver> list = new ArrayList<Driver>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { driver = new Driver(); HSSFCell employeeCardNum = hssfRow.getCell(0); HSSFCell name = hssfRow.getCell(1); HSSFCell sex = hssfRow.getCell(2); HSSFCell ID = hssfRow.getCell(3); HSSFCell birthDate = hssfRow.getCell(4); HSSFCell nation = hssfRow.getCell(5); HSSFCell education = hssfRow.getCell(6); HSSFCell politicsStatus = hssfRow.getCell(7); HSSFCell workPlace = hssfRow.getCell(8); HSSFCell phone = hssfRow.getCell(9); HSSFCell address = hssfRow.getCell(10); HSSFCell workType = hssfRow.getCell(11); HSSFCell workerDegreeTech = hssfRow.getCell(12); HSSFCell beginWorkDate = hssfRow.getCell(13); HSSFCell enterWorkPlaceDate = hssfRow.getCell(14); HSSFCell domicilePlace = hssfRow.getCell(15); HSSFCell postalcode = hssfRow.getCell(16); HSSFCell drivingLicenseFileNum = hssfRow.getCell(17); HSSFCell carNum = hssfRow.getCell(18); HSSFCell singleDouble = hssfRow.getCell(19); driver.setEmployeeCardNum(getValue(employeeCardNum)); driver.setName(getValue(name)); driver.setSex(getValue(sex)); driver.setID(getValue(ID)); driver.setBirthDate(getValue(birthDate)); driver.setNation(getValue(nation)); driver.setEducation(getValue(education)); driver.setPoliticsStatus(getValue(politicsStatus)); driver.setWorkPlace(getValue(workPlace)); driver.setPhone(getValue(phone)); driver.setAddress(getValue(address)); driver.setWorkType(getValue(workType)); driver.setWorkerDegreeTech(getValue(workerDegreeTech)); driver.setBeginWorkDate(getValue(beginWorkDate)); driver.setEnterWorkPlaceDate(getValue(enterWorkPlaceDate)); driver.setDomicilePlace(getValue(domicilePlace)); driver.setPostalcode(getValue(postalcode)); driver.setDrivingLicenseFileNum(getValue(drivingLicenseFileNum)); driver.setCarNum(getValue(carNum)); driver.setSingleDouble(getValue(singleDouble)); list.add(driver); } System.out.println("********************************"); } return list; } return list; }
From source file:temp.ExcelReader.java
public static void readFromFile(File file) { try {/*from w w w . ja v a 2s . c o m*/ // XSSFWorkbook workbookXls; // can load up every sheet as previews HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file)); System.out.println("Number of sheets:" + workbook.getNumberOfSheets()); //say I want sheet 0 HSSFSheet sheet = workbook.getSheetAt(5); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // Iterator<Cell> cellI = row.cellIterator(); // // while(cellI.hasNext()){ // Cell cell = cellI.next(); // //This is wrong -> the blank cell should not return 0! // //use toString would work; but it could cause the formulas to go wrong // // // // System.out.print(cell + "|"); // } for (int i = 0; i < row.getLastCellNum(); i++) { System.out.print(row.getCell(i) + "|"); //this you won't miss any cells! right way to do } System.out.println(""); } } catch (FileNotFoundException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ubic.BAMSandAllen.AllenDataLoaders.AllenTop50DataLoader.java
License:Apache License
public AllenTop50DataLoader() { try {/*w ww.j av a 2 s .com*/ NeuroNamesMappingLoader NNLoader = new NeuroNamesMappingLoader(); allenRegions = new HashSet<String>(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(spreadSheetLocation)); HSSFWorkbook allen = new HSSFWorkbook(fs); HSSFSheet sheet; for (int i = 0; i < allen.getNumberOfSheets(); i++) { sheet = allen.getSheetAt(i); allenRegions.add(allen.getSheetName(i)); // System.out.println( allen.getSheetName( i ) ); } Set<String> allenRegions = getAllenRegions(); Set<NomenClatureEntry> dongEntries = NNLoader.getDongEntries(); allenEnrichedEntries = new HashSet<NomenClatureEntry>(); for (String allenEnrichedAcro : allenRegions) { for (NomenClatureEntry dongEntry : dongEntries) { if (allenEnrichedAcro.equals(dongEntry.acro)) { allenEnrichedEntries.add(dongEntry); } } } // make entry <-> gene links for (NomenClatureEntry dongEntry : allenEnrichedEntries) { sheet = allen.getSheet(dongEntry.acro); // System.out.println(dongEntry.acro); for (short i = 2; true; i++) { String gene = ExcelUtil.getValue(sheet, i, (short) 0); if (gene == null) break; dongEntry.expressedGenes.add(gene); // System.out.println( gene ); } } } catch (Exception e) { e.printStackTrace(); System.exit(1); } }