List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
From source file:POS.test2.pisps_items.java
public static List<cost> showCost() { String path = "C:\\\\Users\\\\Guinness\\\\Documents\\\\Projects\\\\Algorithm\\\\pisps records\\\\cost.xls"; FileInputStream fis = null;/*from w w w . j a va 2s . c o m*/ List sheetData = new ArrayList(); try { fis = new FileInputStream(path); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<cost> datas = new ArrayList(); try { fis = new FileInputStream(path); int r = 0; int r_set = 1; String prev_no = ""; for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); int size = list.size(); List<String> record = new ArrayList(); for (int j = 0; j < list.size(); j++) { HSSFCell cell = (HSSFCell) list.get(j); HSSFDataFormatter hdf = new HSSFDataFormatter(); String data = ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mydata = cell.getStringCellValue(); data = data + "" + mydata + ""; record.add(data); } else { data = data + cell.getNumericCellValue() + ""; record.add(data); } } String[] aw = new String[size]; int jj = 0; for (String s : record) { aw[jj] = s; jj++; } String item_code = FitIn.fmt_woc(aw[0]); double cost = FitIn.toDouble(aw[1]); double price = FitIn.toDouble(aw[2]); cost cos = new cost(item_code, cost, price); datas.add(cos); } return datas; } catch (FileNotFoundException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } return datas; }
From source file:Principal.Main.java
public static void main(String[] args) throws Exception { ////from ww w . ja v a2 s .c o m // An excel file name. You can create a file name with a full // path information. // String filename = "listado.xls"; // // Create an ArrayList to store the data read from excel sheet. // List sheetData = new ArrayList(); FileInputStream fis = null; try { // // Create a FileInputStream that will be use to read the // excel file. // fis = new FileInputStream(filename); // // Create an excel workbook from the file system. // HSSFWorkbook workbook = new HSSFWorkbook(fis); // // Get the first sheet on the workbook. // HSSFSheet sheet = workbook.getSheetAt(0); // // When we have a sheet object in hand we can iterator on // each sheet's rows and on each row's cells. We store the // data read on an ArrayList so that we can printed the // content of the excel to the console. // Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // System.out.println("Aadiendo Celda: " + cell.toString()); data.add(cell); } sheetData.add(data); } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } showExelData(sheetData); }
From source file:qa.dataset.java
public void readfile() { try {//from ww w .ja v a2 s . com InputStream input = new BufferedInputStream(new FileInputStream("E:/Tugas Akhir Ali/dataset.xls")); POIFSFileSystem filesystem = new POIFSFileSystem(input); HSSFWorkbook workbook = new HSSFWorkbook(filesystem); HSSFSheet sheet = workbook.getSheetAt(0); String a; String b; MaxentTagger tagger = new MaxentTagger( "E:/Tugas Akhir Ali/stanford-postagger-2011-04-20/models/left3words-wsj-0-18.tagger"); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); System.out.println("\n"); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) System.out.print(cell.getNumericCellValue() + " "); else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) System.out.print(cell.getStringCellValue() + " "); else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("Blank"); else System.out.print("Unknown"); } } } catch (Exception e) { e.printStackTrace(); } }
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."); }// w ww .ja v a 2 s . 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:shopv2.work.java
public void Filestoreread() throws IOException { //? ? try {//from w w w .j a v a 2s . c o m POIFSFileSystem fileSystem = new POIFSFileSystem( new FileInputStream("C:\\Users\\?\\Desktop\\store.xls")); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); int id = (int) row.getCell(0).getNumericCellValue(); String name = (String) row.getCell(1).getStringCellValue(); int count = (int) row.getCell(3).getNumericCellValue(); int price = (int) row.getCell(2).getNumericCellValue(); goods p = new goods(id, name, price, count); this.goodsID.put(p.getID(), p); ids.add(id); z++; } } catch (Exception e) { System.out.println(""); } }
From source file:shopv2.work.java
public void Filesaleread() throws IOException { //? ? ?? try {//w w w. j av a 2s. c om POIFSFileSystem fileSystem = new POIFSFileSystem( new FileInputStream("C:\\Users\\?\\Desktop\\sale.xls")); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); int id = (int) row.getCell(0).getNumericCellValue(); int idtov = (int) row.getCell(1).getNumericCellValue(); int price = (int) row.getCell(2).getNumericCellValue(); String name = (String) row.getCell(3).getStringCellValue(); int count = (int) row.getCell(4).getNumericCellValue(); salegoods q = new salegoods(id, idtov, price, name, count); this.salesID.put(q.getID(), q); a++; } } catch (Exception e) { System.out.println(""); } }
From source file:spires.printing.Dlg_printing_funeral.java
private void show_dialog() { jProgressBar3.setString("Loading...Please wait..."); jProgressBar3.setIndeterminate(true); FileDialog fd = new FileDialog(new JDialog(), "Choose .XLS FILE"); fd.setVisible(true);/*from ww w . ja v a 2 s .co m*/ if (fd.getDirectory() == null) { return; } String file = fd.getDirectory() + "" + fd.getFile(); file = file.replace("\\", "\\\\"); final String file2 = file; lbl_file.setText(file); if (file == null || file.isEmpty()) { return; } FileInputStream fis = null; final List sheetData = new ArrayList(); try { fis = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Dlg_printing_funeral.class.getName()).log(Level.SEVERE, null, ex); } } } Thread t = new Thread(new Runnable() { @Override public void run() { List<Encoded_Funeral.to_encoded> datas = Encoded_Funeral.showExcelData(sheetData, file2); loadData_encoded(datas); lbl_records.setText("" + datas.size()); jProgressBar3.setString("Finished..."); jProgressBar3.setIndeterminate(false); } }); t.start(); }
From source file:spires.printing.Encoded_baptism.java
public static void main(String[] args) { String path = "C:\\Users\\Guinness\\Documents\\Documents\\Cathedral Books\\book_27a_ok - Copy.xls"; FileInputStream fis = null;// ww w. j av a 2 s. co m final List sheetData = new ArrayList(); try { fis = new FileInputStream(path); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Dlg_printing_funeral.class.getName()).log(Level.SEVERE, null, ex); } } } List<Encoded_baptism> datas = showExcelData(sheetData, path); System.out.println("Size: " + datas.size()); // for (Encoded_baptism to : datas) { // System.out.println("" + to.index_no); // } add_parishioners_1(datas, "27"); }
From source file:Teste.importarExcel2.java
public static List ReadFile(String fileName) { List cellVectorHolder = new ArrayList(); try {/*from w ww . j a v a 2 s. co m*/ FileInputStream myInput = new FileInputStream(fileName); POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); List cellStoreVector = new ArrayList(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); cellStoreVector.add(myCell); } cellVectorHolder.add(cellStoreVector); } } catch (Exception e) { } return cellVectorHolder; }
From source file:transactionController.PurchaseController.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {// w w w. j a v a 2s .c o m JFileChooser fileChooser = new JFileChooser(); int returnVal = fileChooser.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File file = fileChooser.getSelectedFile(); List sheetData = new ArrayList(); FileInputStream fis = null; fis = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell.toString().toUpperCase().replaceAll("!", "").trim()); } sheetData.add(data); } dtm.setRowCount(0); for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); String sr_cd = ""; if (itemCode.get(list.get(1).toString()) == null) { sr_cd = lb.getRetrofit().create(SupportAPI.class) .validateData("SERIESMST", "SR_CD", "SR_NAME", list.get(1).toString()).execute() .body().get("data").getAsString(); if (sr_cd.equalsIgnoreCase("")) { dtm.setRowCount(0); return; } else { itemCode.put(list.get(1).toString(), sr_cd); } } else { sr_cd = itemCode.get(list.get(1).toString()); } Vector row = new Vector(); row.add(list.get(0).toString()); row.add(list.get(1).toString()); row.add(list.get(2).toString()); row.add(list.get(3).toString()); row.add("1"); row.add(list.get(5).toString()); row.add(""); row.add("0"); row.add(list.get(8).toString()); row.add(list.get(9).toString()); row.add(list.get(10).toString()); row.add(list.get(11).toString()); row.add("0"); row.add("0"); row.add(list.get(5).toString()); row.add(list.get(15).toString()); row.add(sr_cd); dtm.addRow(row); } setTotal(); } else { System.out.println("File access cancelled by user."); } } catch (Exception ex) { System.out.println(ex.getMessage()); } }