List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getPhysicalNumberOfCells
@Override public int getPhysicalNumberOfCells()
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileTransformed() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/*w ww .j a va 2 s.com*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } XSSFWorkbook transformedWB = new XSSFWorkbook(); transformedWB.createSheet(); XSSFSheet transformedS = transformedWB.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { transformedS.createRow(i); } /* elkezdnk vgigmenni az alap sheeten ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre */ ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum()).setCellValue(sum); } } for (int index : matchingIndexes) { for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); row.getCell(index).setCellValue(3.14159); } /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { if(i == index){ for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); transformedS.getRow(j).createCell(transformedColumnCount).setCellValue( row.getCell(i).getRawValue() ); } transformedColumnCount++; } }*/ } int columnsInTransformed = 0; for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { String cellValue = row.getCell(j).getRawValue(); if (!cellValue.equals("3.14159")) { transformedS.getRow(i).createCell(columnsInTransformed); transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue); columnsInTransformed++; } } columnsInTransformed = 0; } row = transformedS.getRow(0); row.createCell(row.getLastCellNum()).setCellValue(atcMask); File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); transformedWB.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileFiltered() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/*from w w w . j a v a 2 s . c o m*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } row = sheet.getRow(0); row.createCell(row.getLastCellNum()).setCellValue("ATC mask:"); row.createCell(row.getLastCellNum()).setCellValue(atcMask); ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); System.out.println("SUMS"); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum() + 1).setCellValue(sum); } } File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); workbookToModify.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultTasks() { String path = "/Creator/textFiles/tasks.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedTasks = new ArrayList<>(); try {//from w ww .jav a 2 s .c o m XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: System.out.println("default @ [" + i + "][" + j + "]"); rowData[j] = "default @ [" + i + "][" + j + "]"; break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } rowData[5] = "'" + rowData[5] + "'"; importedTasks.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultAlerts() { String path = "/Creator/textFiles/alerts.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedAlerts = new ArrayList<>(); try {//from w ww .j a v a 2s . com XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: //System.out.println("default @ [" + i + "][" + j + "] = " + String.valueOf(cell.getRawValue())); rowData[j] = String.valueOf(cell.getRawValue()); break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } importedAlerts.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/*from ww w. ja v a 2 s. c o m*/ List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } License license = null; User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]); if (user != null) license = licenseDao.queryLicenseByUserID(user.getUserID()); if (user == null) { user = new User(); user.setUserID(IDGenerator.generateUserID(values[0] + values[1])); user.setUserPwd(StringUtil.MD5(user.getUserID())); user.setUserName(values[2]); user.setUserGender(XMLRepertory.genderStr2Num(values[3])); user.setUserIctype(Integer.parseInt(values[0])); user.setUserIcno(values[1]); user.setUserBdate(MyDateUtil.str2Date(values[4])); user.setUserPhone("00000000"); user.setUserNation(values[5]); user.setUserAddr(values[6]); user.setUserHead("user/default.png"); user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.addUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else if (license == null) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { // user has a license if (license.getLicnValid() == 0) { boolean nolimit = license.getLicnVinfo() == 12 && license.getLicnLimit() == null; boolean outlimit = license.getLicnLimit() != null && (license.getLicnLimit().getTime() - MyDateUtil.str2Date(values[8]).getTime() <= 0); if (nolimit || outlimit) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }
From source file:db.pj.util.excel.LicenseImporter.java
License:Open Source License
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/* www.j av a2 s . com*/ List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); AdministrationDao administrationDao = DaoFactory.getAdministrationDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } User user = new User(); user.setUserID(IDGenerator.generateUserID(values[5] + values[6])); user.setUserPwd(StringUtil.MD5(user.getUserID())); user.setUserName(values[0]); user.setUserGender(XMLRepertory.genderStr2Num(values[1])); user.setUserIctype(Integer.parseInt(values[5])); user.setUserIcno(values[6]); user.setUserBdate(MyDateUtil.str2Date(values[2])); user.setUserPhone("00000000"); user.setUserNation(values[3]); user.setUserAddr(values[4]); user.setUserHead("user/default.png"); user.setUserHealth(0); user.setUserState(5); user.setUserPdate(new Date(System.currentTimeMillis())); License license = new License(); license.setLicnID(values[11]); license.setLicnType(values[8]); license.setLicnGdate(MyDateUtil.str2Date(values[7])); license.setLicnVdlen(Integer.parseInt(values[9])); // if point>=12, set invalid license.setLicnVinfo(Integer.parseInt(values[12])); license.setLicnValid((license.getLicnVinfo() < 12) ? 1 : 0); // license is not valid, reset health and state if (license.getLicnValid() == 0) { user.setUserHealth(-1); user.setUserState(0); } user.setLicense(license); license.setUser(user); // get administration and check whether it is valid Administration administration = administrationDao.queryAdministrationByID(values[10]); if (administration == null) { fail++; result.add("" + values[11] + ""); } else { license.setAdministration(administration); boolean re1 = userDao.addUser(user); boolean re2 = licenseDao.addLicense(license); if (re1 || re2) success++; else { fail++; result.add("" + values[11] + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }
From source file:localization.excel.java
public static void convert(String filePath) { Vector<String> zFile; if (filePath.endsWith(".zip")) { zFile = readzipfile(filePath);/*from w w w .j a va 2 s. c o m*/ for (String s : zFile) { if (s.endsWith(".xlsx")) { //System.out.println(s); convert(s); } } } else if (!filePath.endsWith(".xlsx")) { return; } else { try { FileInputStream file = new FileInputStream(new File(filePath)); System.out.println(filePath); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row; XSSFCell cell; rowNumber = sheet.getPhysicalNumberOfRows(); try { for (int i = 0; i < rowNumber; i++) { row = sheet.getRow(i); if (row != null) { int columnNum = row.getPhysicalNumberOfCells(); //System.out.println(columnNum); for (int j = 0; j < columnNum; j++) { cell = row.getCell(j); if (j == 0) { String name = cell.getRichStringCellValue().getString(); if (name.equalsIgnoreCase("Esri")) { langNumber++; } //System.out.println(name); } } if (i == 3) { cell = row.getCell(30); XSSFCellStyle cs = cell.getCellStyle(); cell = row.createCell(32); cell.setCellValue("Additional Charge per language"); cell.setCellStyle(cs); } } } } catch (Exception e) { } System.out.println(langNumber); double total = Double.parseDouble(sheet.getRow(langNumber + 3).getCell(29).getRawValue()); double subTotal = total / langNumber; DecimalFormat df = new DecimalFormat("#.000"); for (int i = 0; i < langNumber; i++) { cell = sheet.getRow(i + 4).createCell(32); cell.setCellValue("$" + df.format(subTotal)); } file.close(); FileOutputStream outFile = new FileOutputStream(filePath); workbook.write(outFile); outFile.close(); rowNumber = 0; langNumber = 0; System.out.println("Done"); } catch (Exception e) { e.printStackTrace(); } } }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SEPatient> readPatients(XSSFSheet xlSheet) { String property, value, unit; List<SEPatient> patients = new ArrayList<SEPatient>(); try {//from w w w . ja v a2 s. co m int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) patients.add(new SEPatient()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; if (!setProperty(patients.get(c - 1), property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return patients; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, SESubstance> readSubstances(XSSFSheet xlSheet) { EnumAnatomy currCmpt = null;/*from ww w .j a va 2 s . c om*/ String property, value, unit; SESubstance substance = null; SESubstanceAnatomyEffect afx = null; List<SESubstance> substances = new ArrayList<SESubstance>(); Set<Integer> skipColumns = new HashSet<Integer>(); try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) { for (int c = 1; c < cells; c++) { property = row.getCell(c).getStringCellValue().trim(); if (property.equals("Reference Value") || property.equals("Reference Source") || property.equals("Notes/Page")) skipColumns.add(c); } } property = row.getCell(0).getStringCellValue().trim(); if (property == null || property.isEmpty()) continue; Log.info("Processing Substance Field : " + property); if (property.indexOf("Compartment Effects") > -1) { if (property.indexOf("Myocardium") > -1) currCmpt = EnumAnatomy.MYOCARDIUM; else if (property.indexOf("Fat") > -1) currCmpt = EnumAnatomy.FAT; else if (property.indexOf("Kidneys") > -1) currCmpt = EnumAnatomy.KIDNEYS; else if (property.indexOf("Brain") > -1) currCmpt = EnumAnatomy.BRAIN; else if (property.indexOf("Muscle") > -1) currCmpt = EnumAnatomy.MUSCLE; else if (property.indexOf("Skin") > -1) currCmpt = EnumAnatomy.SKIN; else if (property.indexOf("Bone") > -1) currCmpt = EnumAnatomy.BONE; else if (property.indexOf("Gut") > -1) currCmpt = EnumAnatomy.GUT; else if (property.indexOf("Splanchnic") > -1) currCmpt = EnumAnatomy.SPLANCHNIC; else if (property.indexOf("Spleen") > -1) currCmpt = EnumAnatomy.SPLEEN; else if (property.indexOf("Large Intestine") > -1) currCmpt = EnumAnatomy.LARGE_INTESTINE; else if (property.indexOf("Small Intestine") > -1) currCmpt = EnumAnatomy.SMALL_INTESTINE; else if (property.indexOf("Liver") > -1) currCmpt = EnumAnatomy.LIVER; else if (property.indexOf("Right Lung") > -1) currCmpt = EnumAnatomy.RIGHT_LUNG; else if (property.indexOf("Left Lung") > -1) currCmpt = EnumAnatomy.LEFT_LUNG; else { Log.error("Unsupported Anatomy Compartment : " + property); break; } } int s = -1; for (int c = 1; c < cells; c++) { if (skipColumns.contains(c)) continue; s++; String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_FORMULA: switch (evaluator.evaluateFormulaCell(cell)) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } if (cellValue == null) continue; cellValue = cellValue.trim(); if (cellValue.isEmpty()) continue; if (property.equals("Name")) { substance = new SESubstance(); substances.add(substance); } int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; substance = substances.get(c - (3 * s) - 1); if (currCmpt == null) afx = null; else afx = substance.getAnatomyEffect(currCmpt); if (!setProperty(substance, afx, property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } Map<String, SESubstance> map = new HashMap<String, SESubstance>(); for (SESubstance sub : substances) map.put(sub.getName(), sub); return map; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SESubstanceCompound> readCompounds(XSSFSheet xlSheet, Map<String, SESubstance> substances) { String property, value, unit; SESubstance s;// www .jav a 2 s . co m SESubstanceCompound compound = null; SESubstanceCompoundComponent component = null; List<SESubstanceCompound> compounds = new ArrayList<SESubstanceCompound>(); try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) compounds.add(new SESubstanceCompound()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); if (property.equals("Data Type")) continue;// Only one type at this point for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } compound = compounds.get(c - 1); if (property.equals("Compound Name")) { compound.setName(value); continue; } if (property.equals("Component Name")) { s = substances.get(value); component = compound.getComponent(s); continue; } if (!setProperty(component, property, value, unit)) { Log.error("Error setting property"); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return compounds; }