List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
public static Boolean writeToExcel(String ctrlValue, WebElement webElement, String controlId, String controlType, String controlName, String rowNo, String colNo) throws Exception { try {/*w w w .j a v a 2s . com*/ FileInputStream in = new FileInputStream(Automation.configHashMap.get("TRANSACTION_INFO").toString()); HSSFWorkbook uniqueWB = new HSSFWorkbook(in); HSSFSheet uniqueNumberSheet = uniqueWB.getSheet("DataSheet"); HashMap<String, Object> uniqueValuesHashMap = getValueFromHashMap(uniqueNumberSheet); HSSFRow uniqueRow = null; int rowNum = uniqueNumberSheet.getPhysicalNumberOfRows(); for (int rIndex = 0; rIndex < rowNum; rIndex++) { uniqueRow = uniqueNumberSheet.getRow(rIndex); String uniqueTestcaseID = getCellData("TestCaseID", uniqueNumberSheet, rIndex, uniqueValuesHashMap); if (MainController.controllerTestCaseID.toString().equals(uniqueTestcaseID))//&& MainController.controllerTransactionType.toString().equals(uniqueTransactionType) { uniqueRow = uniqueNumberSheet.getRow(rIndex); break; } else if (rIndex == rowNum - 1) { uniqueRow = uniqueNumberSheet.createRow(rowNum); } } if (controlType.equalsIgnoreCase("WebTable")) { //TM:commented and updated the following 'if' statement //if(Integer.valueOf(rowNo).equals(null)||Integer.valueOf(colNo).equals(null)) if (StringUtils.isBlank(rowNo) || StringUtils.isBlank(colNo)) { MainController.pauseFun("RowNumber or ColumnNumber is Missing"); return false; } else { //ctrlValue = Automation.selenium.getTable(controlName+"."+ Integer.parseInt(rowNo) +"." + Integer.parseInt(colNo)); ctrlValue = Automation.driver .findElement(By.xpath(controlName + "/tr[" + rowNo + "]/td[" + colNo + "]")).getText(); } } else if (controlType.equalsIgnoreCase("ListBox") || controlType.equalsIgnoreCase("WebList")) { ctrlValue = new Select(webElement).getFirstSelectedOption().toString(); } else if (controlType.equalsIgnoreCase("DB")) { System.out.println(ctrlValue); } else { ctrlValue = webElement.getText(); } HSSFCell uniqueTestCaseID = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get("TestCaseID").toString())); HSSFCell uniqueCell = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get(columnName).toString())); uniqueTestCaseID.setCellValue(MainController.controllerTestCaseID.toString()); uniqueCell.setCellValue(ctrlValue); in.close(); FileOutputStream out = new FileOutputStream( Automation.configHashMap.get("TRANSACTION_INFO").toString()); uniqueWB.write(out); } catch (FileNotFoundException e) { throw e; } catch (Exception e) { throw e; } return true; }
From source file:ubic.BAMSandAllen.AllenDataLoaders.AllenTop50DataLoader.java
License:Apache License
public AllenTop50DataLoader() { try {//from w ww . j a va2 s. c o m 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); } }
From source file:ubic.basecode.io.excel.ExcelUtil.java
License:Apache License
/** * @param filename//from w w w .j av a 2 s. co m * @param sheetName * @return * @throws IOException */ @SuppressWarnings("resource") public static HSSFSheet getSheetFromFile(String filename, String sheetName) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb.getSheet(sheetName); }
From source file:uploads.uploadAllFacilities.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();// ww w . j a v a2 s .c o m dbConn conn = new dbConn(); checker_county = checker_district = ""; county_name = county_id = district_name = district_id = hf_name = hf_id = ""; checker_dist = checker_hf = mflcode = 0; file_source = new File("C:\\Users\\Geofrey Nyabuto\\Desktop\\hf\\supported_hf.xls"); System.out.println(" The file path is: " + file_source); FileInputStream fileInputStream = new FileInputStream(file_source); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Sheet2"); Iterator rowIterator = worksheet.iterator(); int i = 0, y = 0; while (rowIterator.hasNext()) { county_name = county_id = district_name = district_id = hf_name = hf_id = ""; mflcode = 0; HSSFRow rowi = worksheet.getRow(i); HSSFCell cell1 = rowi.getCell((short) 2); county_name = cell1.getStringCellValue(); HSSFCell cell2 = rowi.getCell((short) 3); district_name = cell2.getStringCellValue(); HSSFCell cell3 = rowi.getCell((short) 1); hf_name = cell3.getStringCellValue(); HSSFCell cell4 = rowi.getCell((short) 0); mflcode = (int) cell4.getNumericCellValue(); district_name = district_name.toUpperCase(); county_name = county_name.toUpperCase(); IdGenerator IG = new IdGenerator(); // hf_id=IG.sec+""+IG.micro; String county_che = "SELECT * FROM county WHERE county_name=?"; conn.pst = conn.conn.prepareStatement(county_che); conn.pst.setString(1, county_name); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } System.out.println("county : " + county_id); if (county_id.length() == 0) { county_id = IG.current_id(); String county_inserter = "INSERT INTO county (county_name) VALUES (?)"; conn.pst = conn.conn.prepareStatement(county_inserter); conn.pst.setString(1, county_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(county_id)FROM county"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } } String check_dist = "SELECT * FROM district WHERE district_name=? && county_id=?"; conn.pst = conn.conn.prepareStatement(check_dist); conn.pst.setString(1, district_name); conn.pst.setString(2, county_id); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } System.out.println("district : " + district_id); if (district_id.length() == 0) { district_id = IG.current_id(); String dist_inserter = "INSERT INTO district (county_id,district_name) VALUES (?,?)"; conn.pst = conn.conn.prepareStatement(dist_inserter); conn.pst.setString(1, county_id); conn.pst.setString(2, district_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(district_id)FROM district"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } } // if(checker_dist>0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String check_hf = "SELECT COUNT(hf_id) FROM health_facility WHERE hf_name=? && district_id=?"; conn.prest = conn.conn.prepareStatement(check_hf); conn.prest.setString(1, hf_name); conn.prest.setString(2, district_id); conn.rs = conn.prest.executeQuery(); if (conn.rs.next() == true) { checker_hf = conn.rs.getInt(1); } if (checker_hf == 0) { // ADD THE NEW HEALTH FACILITY TO THE SYSTEM......................... hf_id = IG.current_id(); String inserter = "INSERT INTO health_facility (hf_id,hf_name,mflcode,district_id) " + " VALUES(?,?,?,?)"; conn.prest = conn.conn.prepareStatement(inserter); conn.prest.setString(1, hf_id); conn.prest.setString(2, hf_name); conn.prest.setInt(3, mflcode); conn.prest.setString(4, district_id); conn.prest.executeUpdate(); System.out.println("" + i + " added : " + hf_name); } else { System.out.println("HEALTH FACILITY AT POSITION : " + i + " AL READY ADDED : " + hf_name); } // } // else{ // System.out.println("MISSING DISTRICT AT POSITION : "+i+" for the district "+district_name); // } i++; } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } }
From source file:velo.importer.AccountsList.java
License:Open Source License
public void importFromXls(InputStream is, String spreadSheetName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(is); //Count the number of sheets if (workbook.getNumberOfSheets() < 1) { throw new Exception("Number of sheets in excel is less than 1!"); }/* ww w . ja v a2s. c o m*/ HSSFSheet sheet = workbook.getSheet(spreadSheetName); if (sheet == null) { throw new Exception("Could not find sheet named '" + spreadSheetName + "'"); } //todo 3 cells at least! //Make sure the headers correspond to the expected values HSSFRow header = sheet.getRow(0); HSSFCell accountNameTitle = header.getCell((short) 0); HSSFCell targetNameTitle = header.getCell((short) 1); if (!accountNameTitle.toString().equalsIgnoreCase("ACCOUNT")) { throw new Exception( "Column one in first row must equal to 'ACCOUNT' and represents the Account to associate"); } if (!targetNameTitle.toString().equalsIgnoreCase("RESOURCE_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'RESOURCE_UNIQUE_NAME' and represents the resource unique name the account is related to!"); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell accountName = row.getCell((short) 0); HSSFCell targetName = row.getCell((short) 1); String targetNameErrMsg = "Target Name at row # '" + i + "' is empty!"; if (targetName == null) { throw new Exception(targetNameErrMsg); } else if (targetName.toString().length() < 1) { throw new Exception(targetNameErrMsg); } String accountErrMsg = "Account Name at row # '" + i + "' is empty!"; if (accountName == null) { throw new Exception(accountErrMsg); } else if (accountName.toString().length() < 1) { throw new Exception(accountErrMsg); } System.out.println("Row(" + i + ") - Account: '" + accountName.toString() + "', On Target: '" + targetName.toString() + "'"); velo.importer.ImportAccount ia = new velo.importer.ImportAccount(); ia.setResourceName(targetName.toString()); ia.setAccountName(accountName.toString()); this.add(ia); } }
From source file:velo.importer.AccountsToUsersList.java
License:Open Source License
public void importFromXls(InputStream is, String spreadSheetName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(is); //Count the number of shits if (workbook.getNumberOfSheets() < 1) { throw new Exception("Number of sheets in excel is less than 1!"); }/*ww w . j a va 2 s. co m*/ HSSFSheet sheet = workbook.getSheet(spreadSheetName); if (sheet == null) { throw new Exception("Could not find sheet named '" + spreadSheetName + "'"); } //todo 3 cells at least! //Make sure the headers correspond to the expected values HSSFRow header = sheet.getRow(0); HSSFCell userNameTitle = header.getCell((short) 0); HSSFCell accountNameTitle = header.getCell((short) 1); HSSFCell targetNameTitle = header.getCell((short) 2); if (!userNameTitle.toString().equalsIgnoreCase("USER")) { throw new Exception( "Column one in first row must equal to 'USER' and represents the User that owns the account!"); } if (!accountNameTitle.toString().equalsIgnoreCase("ACCOUNT")) { throw new Exception( "Column one in first row must equal to 'ACCOUNT' and represents the Account to associate"); } if (!targetNameTitle.toString().equalsIgnoreCase("RESOURCE_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'TARGET-SYSTEM' and represents the resource unique name the account is related to!"); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell userName = row.getCell((short) 0); HSSFCell accountName = row.getCell((short) 1); HSSFCell targetName = row.getCell((short) 2); String targetNameErrMsg = "Target Name at row # '" + i + "' is empty!"; if (targetName == null) { throw new Exception(targetNameErrMsg); } else if (targetName.toString().length() < 1) { throw new Exception(targetNameErrMsg); } String userNameErrMsg = "User Name at row # '" + i + "' is empty!"; if (userName == null) { throw new Exception(userNameErrMsg); } else if (userName.toString().length() < 1) { throw new Exception(userNameErrMsg); } String accountErrMsg = "Account Name at row # '" + i + "' is empty!"; if (accountName == null) { throw new Exception(accountErrMsg); } else if (accountName.toString().length() < 1) { throw new Exception(accountErrMsg); } //System.out.println("Row("+i+") - User: '" + userName.toString() + "', Account: '" + accountName.toString() + "', On Target: '" + targetName.toString() + "'"); velo.importer.ImportAccountToUser iatu = new velo.importer.ImportAccountToUser(); iatu.setUserName(userName.toString()); iatu.setResourceName(targetName.toString()); iatu.setAccountName(accountName.toString()); this.add(iatu); } }
From source file:velo.importer.RolesToRolesFolderList.java
License:Open Source License
public void importFromXls(InputStream is, String spreadSheetName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(is); //Count the number of sheets if (workbook.getNumberOfSheets() < 1) { throw new Exception("Number of sheets in excel is less than 1!"); }/*from w ww . j a v a2 s . co m*/ HSSFSheet sheet = workbook.getSheet(spreadSheetName); if (sheet == null) { throw new Exception("Could not find sheet named '" + spreadSheetName + "'"); } //todo 3 cells at least! //Make sure the headers correspond to the expected values HSSFRow header = sheet.getRow(0); HSSFCell accountNameTitle = header.getCell((short) 0); HSSFCell targetNameTitle = header.getCell((short) 1); if (!accountNameTitle.toString().equalsIgnoreCase("ROLE_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'ROLE_UNIQUE_NAME' and represents the Role to create"); } if (!targetNameTitle.toString().equalsIgnoreCase("ROLES_FOLDER_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'ROLES_FOLDER_UNIQUE_NAME' and represents the roles folder unique name the role is related to!"); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell roleName = row.getCell((short) 0); HSSFCell rolesFolderName = row.getCell((short) 1); String RolesFolderNameErrMsg = "Roles Folder Name at row # '" + i + "' is empty!"; if (rolesFolderName == null) { throw new Exception(RolesFolderNameErrMsg); } else if (rolesFolderName.toString().length() < 1) { throw new Exception(RolesFolderNameErrMsg); } String roleErrMsg = "Role Name at row # '" + i + "' is empty!"; if (roleName == null) { throw new Exception(roleErrMsg); } else if (roleName.toString().length() < 1) { throw new Exception(roleErrMsg); } System.out.println("Row(" + i + ") - Role: '" + roleName.toString() + "', On Roles Folder: '" + rolesFolderName.toString() + "'"); ImportRoleToRolesFolder role = new ImportRoleToRolesFolder(roleName.toString(), rolesFolderName.toString()); this.add(role); } }
From source file:voedselbank.MainFrame.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed JFileChooser fc = new JFileChooser(); fc.showOpenDialog(this); DataFormatter formatter = new DataFormatter(); DateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd"); File file = fc.getSelectedFile(); try {/*from w ww . j a v a 2 s .c o m*/ FileInputStream fileInputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Intakestatus"); boolean begin = false; boolean einde = false; for (int i = 0; begin == false; i++) { String content = formatter.formatCellValue(worksheet.getRow(i).getCell(0)); if (content.equals("Kaartnummer")) { begin = true; i = i + 1; while (einde == false) { if (!formatter.formatCellValue(worksheet.getRow(i).getCell(0)).equals("")) { Date startdatum_uitgifte = new Date(0); Date intakedatum = new Date(0); Date datum_herintake = new Date(0); Date datum_stopzetting = new Date(0); String kaartnummer = formatter.formatCellValue(worksheet.getRow(i).getCell(0)); String naam = formatter.formatCellValue(worksheet.getRow(i).getCell(1)); String naamPartner = formatter.formatCellValue(worksheet.getRow(i).getCell(2)); String telefoonnummer = formatter.formatCellValue(worksheet.getRow(i).getCell(3)); String email = formatter.formatCellValue(worksheet.getRow(i).getCell(4)); String mobiel = formatter.formatCellValue(worksheet.getRow(i).getCell(5)); String aantalPersonen = formatter.formatCellValue(worksheet.getRow(i).getCell(6)); String adres = formatter.formatCellValue(worksheet.getRow(i).getCell(13)); String postcode = formatter.formatCellValue(worksheet.getRow(i).getCell(14)); String plaats = formatter.formatCellValue(worksheet.getRow(i).getCell(15)); String status = formatter.formatCellValue(worksheet.getRow(i).getCell(16)); String intaker = formatter.formatCellValue(worksheet.getRow(i).getCell(17)); if (worksheet.getRow(i).getCell(19).getDateCellValue() != null) { startdatum_uitgifte = worksheet.getRow(i).getCell(19).getDateCellValue(); } if (worksheet.getRow(i).getCell(18).getDateCellValue() != null) { intakedatum = worksheet.getRow(i).getCell(18).getDateCellValue(); } if (worksheet.getRow(i).getCell(20).getDateCellValue() != null) { datum_herintake = worksheet.getRow(i).getCell(20).getDateCellValue(); } if (worksheet.getRow(i).getCell(21).getDateCellValue() != null) { datum_stopzetting = worksheet.getRow(i).getCell(21).getDateCellValue(); } String reden_stopzetting = formatter.formatCellValue(worksheet.getRow(i).getCell(22)); String uitgifte_punt = formatter.formatCellValue(worksheet.getRow(i).getCell(31)); String pakket = formatter.formatCellValue(worksheet.getRow(i).getCell(32)); Client client = new Client(Integer.parseInt(kaartnummer), naam, telefoonnummer, adres, postcode, plaats, email, mobiel, Integer.parseInt(aantalPersonen), status, naamPartner); Voedselpakket voedselpakket = new Voedselpakket(startdatum_uitgifte, pakket); Intake intake = new Intake(0, 0, intaker, intakedatum, startdatum_uitgifte, datum_herintake, datum_stopzetting, reden_stopzetting); client.checkBestaat(client, uitgifte_punt, voedselpakket, intake); i++; } else { einde = true; } } } } } catch (Exception e) { System.out.println(e); } }