List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:org.addition.epanet.network.io.input.ExcelParser.java
License:Open Source License
private int parseWorksheet(Network net, List<XSSFSheet> sheets, Pattern tagPattern, int errSum) throws ENException { for (XSSFSheet sheet : sheets) { boolean lastRowNull = true; boolean lastRowHeader = false; Network.SectType lastType = null; for (int rowCount = 0, tRowId = 0; rowCount < sheet.getPhysicalNumberOfRows(); tRowId++) { XSSFRow row = sheet.getRow(tRowId); if (row != null) { List<String> tokens = new ArrayList<String>(); String comments = ""; boolean allAreBold = true; for (int cellCount = 0, tCellId = 0; cellCount < row.getPhysicalNumberOfCells(); tCellId++) { XSSFCell cell = row.getCell(tCellId); if (cell != null) { String value = convertCell(cell); if (value.startsWith(";")) { comments += value; } else tokens.add(value); allAreBold = allAreBold & cell.getCellStyle().getFont().getBold(); // TODO remover cellCount++;/*w w w . ja v a2s . c om*/ } } if (tokens.size() > 0) { if (lastRowNull && tagPattern.matcher(tokens.get(0)).matches()) { lastType = Network.SectType.parse(tokens.get(0)); lastRowHeader = true; } else { String[] tokArray = tokens.toArray(new String[tokens.size()]); if (lastRowHeader && allAreBold) { //System.out.println("Formating Header : " + tokens.toArray(new String[tokens.size()])); } else { try { parseSect(net, lastType, comments, tokArray); } catch (ENException e) { String line = ""; for (String tk : tokArray) line += tk + " "; logException(lastType, e, line, tokArray); errSum++; } } } } lastRowNull = false; rowCount++; } if (row == null || row != null && row.getPhysicalNumberOfCells() == 0) { lastRowNull = true; continue; } } } return errSum; }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public void parseRowByRow(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); List<Object[]> colNames = ColNamesList.get(pricatFileVersion); int colNumber = colNames.size(); int emptyRowStart = -1; int emptyRowEnd = -1; for (int i = headerRowNo + 1; i < rows; i++) { XSSFRow row = sheet.getRow(i);/*from w w w. j a va 2 s . com*/ if (UtilValidate.isEmpty(row) || isEmptyRow(row, colNumber, false)) { if (emptyRowStart == -1) { report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); emptyRowStart = i; } else { emptyRowEnd = i; } continue; } else { if (emptyRowStart != -1) { if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); } report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); emptyRowStart = -1; emptyRowEnd = -1; } } report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); List<Object> cellContents = getCellContents(row, colNames, colNumber); try { if (parseCellContentsAndStore(row, cellContents)) { report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } else { report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } } catch (GenericTransactionException e) { report.println(e); } } if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public boolean containsDataRows(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); if (rows > headerRowNo + 1) { report.println(/* www.java 2 s . c o m*/ UtilProperties .getMessage(resource, "PricatTableRows", new Object[] { String.valueOf(headerRowNo + 1), String.valueOf(rows - headerRowNo - 1), sheet.getSheetName() }, locale), InterfaceReport.FORMAT_NOTE); } else { report.println(UtilProperties.getMessage(resource, "PricatNoDataRows", new Object[] { sheet.getSheetName() }, locale), InterfaceReport.FORMAT_ERROR); return false; } return true; }
From source file:org.ecocean.servlet.importer.ImportExcelMetadata.java
License:Open Source License
public void processExcel(File dataFile, HttpServletResponse response, boolean committing, Hashtable<String, MediaAsset> assetIds, Shepherd myShepherd, PrintWriter out) throws IOException { FileInputStream fs = new FileInputStream(dataFile); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet; XSSFRow row;/* w ww . j ava 2s .c o m*/ sheet = wb.getSheetAt(0); if (wb.getNumberOfSheets() < 1) { out.println("!!! XSSFWorkbook did not find any sheets !!!"); } else if (sheet.getClass() == null) { out.println("!!! Sheet was not successfully extracted !!!"); } else { out.println("+++ Success creating FileInputStream and XSSF Worksheet +++"); } int numSheets = wb.getNumberOfSheets(); out.println("Num Sheets = " + numSheets); int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); out.println("Num Rows = " + physicalNumberOfRows); int rows = sheet.getPhysicalNumberOfRows(); ; // No of rows int cols = sheet.getRow(0).getPhysicalNumberOfCells(); // No of columns out.println("Num Cols = " + cols); out.println("committing = " + committing); int printPeriod = 25; out.println("+++++ LOOPING THROUGH FILE +++++"); String encId = null; boolean isValid = true; for (int i = 1; i < rows; i++) { try { if (committing) myShepherd.beginDBTransaction(); row = sheet.getRow(i); // example if (getStringOrIntString(row, 7) != null) { encId = String.valueOf(getInteger(row, 7)); } else { isValid = false; } out.println("---- CURRENT ID: " + encId + " ----"); Encounter enc = null; if (committing && isValid == true) { enc = parseEncounter(row, myShepherd, out); String indID = null; try { indID = getStringOrIntString(row, 7); } catch (Exception e) { out.println("Not a valid indy for this row!"); } MarkedIndividual ind = null; boolean needToAddEncToInd = false; if (indID != null) { ind = myShepherd.getMarkedIndividualQuiet(indID); if (ind == null) { ind = new MarkedIndividual(indID, enc); } else { needToAddEncToInd = true; } } try { out.println("Adding media asset : " + encId); enc.setState("approved"); myShepherd.beginDBTransaction(); if (committing && isValid == true) myShepherd.storeNewEncounter(enc, Util.generateUUID()); myShepherd.commitDBTransaction(); String encIdS = String.valueOf(encId); MediaAsset mal = assetIds.get(encIdS + "l"); MediaAsset mar = assetIds.get(encIdS + "r"); MediaAsset mac = assetIds.get(encIdS + "c"); MediaAsset map = assetIds.get(encIdS + "p"); try { myShepherd.beginDBTransaction(); if (mal != null) { enc.addMediaAsset(mal); } if (mac != null) { enc.addMediaAsset(mac); } if (map != null) { enc.addMediaAsset(map); } if (mar != null) { enc.addMediaAsset(mar); } myShepherd.commitDBTransaction(); } catch (Exception npe) { npe.printStackTrace(); out.println("!!! Failed to Add Media asset to Encounter !!!"); } } catch (Exception e) { e.printStackTrace(); out.println("!!! Failed to Store New Encounter !!!"); } if (committing && ind != null) { myShepherd.beginDBTransaction(); myShepherd.storeNewMarkedIndividual(ind); myShepherd.commitDBTransaction(); out.println("=== CREATED INDIVIDUAL " + ind.getName() + " ==="); } myShepherd.beginDBTransaction(); if (ind != null) ind.addEncounter(enc); myShepherd.commitDBTransaction(); // New Close it. if (i % printPeriod == 0) { out.println("Parsed row (" + i + "), containing Enc " + enc.getEncounterNumber() + " with Latitude " + enc.getDecimalLatitude() + " and Longitude " + enc.getDecimalLongitude() + ", dateInMillis " + enc.getDateInMilliseconds() + ", individualID " + enc.getIndividualID() + ", sex " + enc.getSex() + ", living status " + enc.getLivingStatus() + ", identification notes " + enc.getIdentificationRemarks()); } } } catch (Exception e) { fs.close(); out.println("!!! Encountered an error while Iterating through rows !!!"); e.printStackTrace(out); myShepherd.rollbackDBTransaction(); } isValid = true; } fs.close(); wb.close(); }
From source file:org.ohdsi.jCdmBuilder.EtlReport.java
License:Apache License
private void addRow(XSSFSheet sheet, Object... values) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); for (Object value : values) { Cell cell = row.createCell(row.getPhysicalNumberOfCells()); if (value instanceof Integer && value instanceof Long && value instanceof Double) cell.setCellValue(Double.valueOf(value.toString())); else/*from w w w.j av a 2 s . c o m*/ cell.setCellValue(value.toString()); } }
From source file:org.openstreetmap.josm.plugins.msf1.XLSX_Reader.java
public static void getIndexes(String arg) throws IOException { try {/*from w w w .j a v a 2 s .c o m*/ ExcelFileToRead = new FileInputStream(arg); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFCell cell; XSSFSheet sheet = wb.getSheetAt(0); Iterator cells = sheet.getRow(0).cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell != null) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { if (cell.getStringCellValue().equalsIgnoreCase("_Location_Latitude")) { lat_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("_LOCATION_longitude")) { lon_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("Village_name")) { villageName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("Alt_village_name")) { altVillageName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("HANDPUMP_WORKING")) { handpump_condition_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("WATERPOINT_NAME")) { waterPointName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("BOREHOLE_PROTECTED")) { borehole_access_index = cell.getColumnIndex(); } } } } lon_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(lon_index) == null || row.getCell(lon_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(lon_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(lon_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(lon_index).toString(); lon_array[i] = var; // System.out.println(var); } else { lon_array[i] = "null"; } } lat_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(lat_index) == null || row.getCell(lat_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(lat_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(lat_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(lat_index).toString(); lat_array[i] = var; // System.out.println(var); } else { lat_array[i] = "null"; } } villageName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(villageName_index) == null || row.getCell(villageName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(villageName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(villageName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(villageName_index).toString(); villageName_array[i] = var; // System.out.println(var); } else { villageName_array[i] = "null"; } } altVilageName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(altVillageName_index) == null || row.getCell(altVillageName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(altVillageName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(altVillageName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(altVillageName_index).toString(); altVilageName_array[i] = var; // System.out.println(var); } else { altVilageName_array[i] = "null"; } } borehole_access_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(borehole_access_index) == null || row.getCell(borehole_access_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(borehole_access_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(borehole_access_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(borehole_access_index).toString(); borehole_access_array[i] = var; // System.out.println(var); } else { borehole_access_array[i] = "null"; } } handpump_condition_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(handpump_condition_index) == null || row.getCell(handpump_condition_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(handpump_condition_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(handpump_condition_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(handpump_condition_index).toString(); handpump_condition_array[i] = var; //System.out.println(var); } else { handpump_condition_array[i] = "null"; } } waterPointName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(waterPointName_index) == null || row.getCell(waterPointName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(waterPointName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(waterPointName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(waterPointName_index).toString(); waterPointName_array[i] = var; // System.out.println(var); } else { waterPointName_array[i] = "null"; } } // public static String[] getLon_array() { // return lon_array; // } // // public static String[] getLat_array() { // return lat_array; // } // public static String[] getVillageName_array() { // return villageName_array; // } // public static String[] getAltVillageName_array() { // return altVilageName_array; // } // public static String[] getBoreholeAccess_array() { // return borehole_access_array; // } // public static String[] getHandPumpCondition_array() { // return handpump_condition_array; // } // public static String[] getWaterPoint_array() { // return waterPointName_array; // } wb.close(); } catch (IOException e) { } }
From source file:pl.exsio.ck.model.reader.XlsxEntryReaderImpl.java
License:Open Source License
@Override public Collection<Entry> readEntries(File file, String progressName, boolean serialsOnly) { ProgressPresenter progress = ProgressHelper.showProgressBar(progressName, false); Row currentRow = null;/*www. j a va2s . co m*/ Cell currentCell = null; ArrayList<Entry> entries = new ArrayList<>(); try { XSSFSheet sheet = this.openSheet(file); Iterator<Row> rowIterator = sheet.iterator(); int totalRowCount = sheet.getPhysicalNumberOfRows() - 1; int rowCounter = 0; while (rowIterator.hasNext()) { ProgressHelper.updateProgressBar(progress, (int) (rowCounter * 100 / totalRowCount)); currentRow = rowIterator.next(); if (currentRow.getRowNum() > 0) { Entry e = new EntryImpl(); Iterator<Cell> cellIterator = currentRow.cellIterator(); while (cellIterator.hasNext()) { currentCell = cellIterator.next(); if (!this.fillEntryField(currentCell, e, serialsOnly)) { break; } } if (e.getSerialNo() != null) { entries.add(e); } } rowCounter++; } } catch (IOException ex) { this.log.log("nieudana prba otwarcia pliku " + file.getAbsolutePath()); this.log.log(ExceptionUtils.getMessage(ex)); } catch (ParseException ex) { this.log.log("nieprawidowy format daty w komrce " + currentRow.getRowNum() + CellReference.convertNumToColString(currentCell.getColumnIndex()) + ". Akceptowalny format to 'yyyy-mm-dd'"); this.log.log(ExceptionUtils.getMessage(ex)); } System.gc(); ProgressHelper.hideProgressBar(progress); return entries; }
From source file:rapture.dp.invocable.workflow.ProcessFile.java
License:Open Source License
@SuppressWarnings({ "rawtypes", "unchecked" })
@Override/*from w w w . j a v a2 s.co m*/
public String invoke(CallingContext ctx) {
final int BATCH_LOAD_SIZE = 50; // TODO: move to config
OPCPackage pkg;
XSSFWorkbook wb;
List uris = new ArrayList<>();
// stores all documents for insertion
List<List<String>> allDocs = new ArrayList<List<String>>();
String file = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "filetoupload");
String blobUri = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "blobUri");
String folderName = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "folderName");
String repo = "document://data/" + folderName;
String docUri = repo + "#id";
try {
InputStream is = new ByteArrayInputStream(Kernel.getBlob().getBlob(ctx, blobUri).getContent());
pkg = OPCPackage.open(is);
wb = new XSSFWorkbook(pkg);
XSSFSheet sheet = wb.getSheetAt(0);
log.info("Loading " + sheet.getPhysicalNumberOfRows() + " rows from " + file + ". Batch size is "
+ BATCH_LOAD_SIZE);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
int remainder = physicalNumberOfRows % BATCH_LOAD_SIZE;
int div = physicalNumberOfRows / BATCH_LOAD_SIZE;
// this only needs to be done once as the uris dont change
for (int g = 1; g <= BATCH_LOAD_SIZE; g++) {
uris.add(docUri);
}
log.info("created uris list " + uris.size());
int j = 0;
int count = 0;
long startLoadTime = System.currentTimeMillis();
for (int i = 1; i <= div; i++) {
List docs = new ArrayList<>();
// Create a list of documents with size of BATCH_LOAD_SIZE
for (j = count; j < (BATCH_LOAD_SIZE * i); j++) {
Row row = sheet.getRow(j);
Map<String, Object> map = ImmutableMap.of("Row", row.getRowNum(), "DataPeriod",
row.getCell(0).toString(), "Industry", row.getCell(3).toString(), "Price",
row.getCell(7).toString());
docs.add(JacksonUtil.jsonFromObject(map));
}
allDocs.add(docs);
count = j;
}
long endLoadTime = System.currentTimeMillis();
ExecutorService executorService = Executors.newCachedThreadPool();
long startWriteTime = System.currentTimeMillis();
for (List<String> docList : allDocs) {
executorService.execute(new InsertData(ctx, docList, uris));
}
executorService.shutdown();
try {
// TODO: hardcoded timeout.ComparableFutures?
// Helpful:
// http://stackoverflow.com/questions/1250643/how-to-wait-for-all-threads-to-finish-using-executorservice
executorService.awaitTermination(60000L, TimeUnit.MILLISECONDS);
} catch (InterruptedException e) {
log.error(e.getStackTrace().toString(), e);
return "error";
}
long endWriteTime = System.currentTimeMillis();
log.info("Completed parallel load.");
// handle the remaining rows
if (remainder > 0) {
long remStartTime = System.currentTimeMillis();
for (int k = (count); k < (count + remainder); k++) {
Row row = sheet.getRow(k);
Map<String, Object> map = ImmutableMap.of("Row", row.getRowNum(), "DataPeriod",
row.getCell(0).toString(), "Industry", row.getCell(3).toString(), "Price",
row.getCell(7).toString());
Kernel.getDoc().putDoc(ctx, docUri, JacksonUtil.jsonFromObject(map));
}
long remEndTime = System.currentTimeMillis();
log.info("Remainders took " + (remEndTime - remStartTime) + "ms");
}
log.info("Populated uri " + repo + ". Took " + (endLoadTime - startLoadTime) + "ms. to load data. Took "
+ (endWriteTime - startWriteTime) + "ms. to write data.");
pkg.close();
Map<String, RaptureFolderInfo> listDocsByUriPrefix = Kernel.getDoc().listDocsByUriPrefix(ctx, repo, 1);
log.info("Count from repo is " + listDocsByUriPrefix.size());
if (listDocsByUriPrefix.size() == sheet.getPhysicalNumberOfRows()) {
return "ok";
} else {
return "error"; // TODO: add error step
}
} catch (InvalidFormatException | IOException | RaptureException e) {
log.error("ProcessFile error", e);
return "error";
}
}
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsxFile(String fileName) { try {/*from w w w . ja v a2 s . c o m*/ XSSFWorkbook wb = HSSFReadWrite.readxlsxFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { XSSFSheet 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++) { XSSFRow 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++) { XSSFCell 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:singleton.StaticClass.java
public synchronized void copyFromExcel(String fileName) throws IOException { //System.out.println("start"); Data temp;//from w w w . j a v a 2 s. co m String value = ""; File inFile = new File(fileName); FileInputStream file = new FileInputStream(inFile); XSSFWorkbook wb = new XSSFWorkbook(file); XSSFSheet sheet = wb.getSheetAt(4); // Build Image Analysis page is at // the 4th sheet of Open source // license excel file. int rows = sheet.getPhysicalNumberOfRows(); for (int i = 2; i < rows; ++i) { // start index should be 2 since the // 1st row is used for titles. XSSFRow row = sheet.getRow(i); if (row != null) { int cells = row.getPhysicalNumberOfCells(); // Number of cells // at each row. temp = new Data(); for (int colIndex = 1; colIndex <= cells; colIndex++) { XSSFCell cell = row.getCell(colIndex); if (colIndex == 1) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setBinary(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setBinary(cell.getStringCellValue()); break; } } else if (colIndex == 2) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setPath(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setPath(cell.getStringCellValue()); break; } } else if (colIndex == 3) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setOnok(""); break; case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); temp.setOnok(value); break; } } else if (colIndex == 4) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setOssComponent(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setOssComponent(cell.getStringCellValue()); break; } } else if (colIndex == 6) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setLicense(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setLicense(cell.getStringCellValue()); break; } } else { continue; } } if (temp != null) { if (value.equalsIgnoreCase("nok")) { nokList.add(temp); //System.out.println("nok count : " + nokList.size()); } else if (value.equalsIgnoreCase("ok")) { okList.add(temp); //System.out.println("ok count : " + okList.size()); } else if (value.equalsIgnoreCase("nok(na)")) { nokNaList.add(temp); //System.out.println("nok(na) count : " + nokNaList.size()); } else { blankList.add(temp); //System.out.println("blank count : " + blankList.size()); } System.out.println(temp.getBinary() + "\t" + temp.getPath() + "\t\t" + temp.getOnok() + "\t\t" + temp.getLicense()); } } } }