List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
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 ww w. j a v a2 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 www. j ava 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 + "] = " + 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:Dao.DAO_Index.java
public void upload(InputStream fis, int numSheet) throws IOException, ParseException, Exception { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm"); tiketDAO = new DAO_Tiket(); // tiketWilayahDAO = new DAO_TiketWilayah(); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(numSheet); Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*from www.ja v a 2 s . com*/ // System.out.println(row.getCell(5).toString()); // tiketWilayahDAO.insert(row); tiketDAO.insert(row); } // tiketWilayahDAO.display(); tiketDAO.display(); }
From source file:Dao.XlsBillDao.java
public ArrayList<WorkItemBean> ReadXLS(File f) { WorkDao wdao = new WorkDao(); FileInputStream fis = null;//w w w . j av a 2s . com ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>(); try { String pono = null; XSSFRow row = null; //fis = new FileInputStream(new File("D:\\WO-2015-2008.xlsx")); fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); int i = 0; while (rowIterator.hasNext()) { i++; row = (XSSFRow) rowIterator.next(); if (i == 1) { Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { pono = cell.getStringCellValue(); pono = pono.substring((pono.indexOf(":") + 1)); } } } if (i > 2) { WorkItemBean bean = new WorkItemBean(); // System.out.println("ROW" + i); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= 0) { //System.out.print("COLUMN"); if (cell.getColumnIndex() == 1) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setITEM_ID(String.valueOf(cell.getNumericCellValue())); } else { bean.setITEM_ID(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 2) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setITEM_DESC(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 3) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setUOM(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setQTY((float) cell.getNumericCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setQTY(Float.parseFloat(cell.getStringCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 5) { //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setRATE((float) cell.getNumericCellValue()); } else { bean.setRATE(Float.parseFloat(cell.getStringCellValue())); } } else if (cell.getColumnIndex() == 7) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPLANT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPLANT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 8) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCC(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCC(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 9) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPROJ(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPROJ(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setPROJ("-"); } } else if (cell.getColumnIndex() == 10) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setTASK(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setTASK(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setTASK("-"); } } else if (cell.getColumnIndex() == 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCMT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCMT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { } } } //System.out.println(); itm.add(bean); } } Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "READ DONE !!"); fis.close(); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } finally { try { fis.close(); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } } for (WorkItemBean i : itm) { if (i.getQTY() != 0) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.INFO, "ITEM_ID : {0} QTY:{1} PLANT:{2} CC:{3}", new Object[] { i.getITEM_ID(), i.getQTY(), i.getPLANT(), i.getCC() }); } } return itm; }
From source file:Dao.XlsWoDao.java
public ArrayList<WorkItemBean> ReadXLS(File f) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "READ XLS CALLED"); FileInputStream fis = null;/*from w w w.ja va 2 s. c o m*/ ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>(); try { XSSFRow row = null; // fis = new FileInputStream(new File("D:\\CreateWO_Tmp.xlsx")); fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); int i = 0; while (rowIterator.hasNext()) { // System.out.println("NEW ROW"); i++; row = (XSSFRow) rowIterator.next(); if (i == 1) { Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { } } } if (i > 3) { WorkItemBean bean = new WorkItemBean(); // System.out.println("ROW" + i); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { // System.out.println("NEW COLUMN"); Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= 0) { // System.out.print("COLUMN"); if (cell.getColumnIndex() == 0) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setITEM_ID(String.valueOf(t)); } else { bean.setITEM_ID(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 1) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setRATE((float) cell.getNumericCellValue()); } else { bean.setRATE(Float.parseFloat(cell.getStringCellValue())); } } else if (cell.getColumnIndex() == 2) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setPLANT(String.valueOf(t)); } else { bean.setPLANT(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 3) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setPROJ(String.valueOf(t)); } else { bean.setPROJ(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setTASK(String.valueOf(t)); } else { bean.setTASK(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 5) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setCMT(String.valueOf(t)); } else { bean.setCMT(cell.getStringCellValue()); } } } } // if (bean.getITEM_ID() != null || !"".equals(bean.getITEM_ID())) { itm.add(bean); // } } } } catch (FileNotFoundException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } finally { try { fis.close(); } catch (IOException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } } return itm; }
From source file:dataaccess.SourceDataReader.java
public static Map readRawData(File f) { Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>(); try {/*from ww w .java 2s . c o m*/ FileInputStream is = new FileInputStream(f); XSSFWorkbook wb = new XSSFWorkbook(is); XSSFSheet dataSheet = wb.getSheetAt(0); int rowID = 0; XSSFRow currRow = dataSheet.getRow(rowID); while (currRow != null) { if (rowID == 0) { // rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } else { //?? String serialNumber = currRow.getCell(1).toString(); //??? String supplierName = currRow.getCell(2).toString(); //? String price = currRow.getCell(4).toString(); //? String time = currRow.getCell(6).toString(); ArrayList<String> info = new ArrayList<String>(); info.add(supplierName); info.add(price); info.add(time); map.put(serialNumber, info); rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:dataaccess.SourceDataReader.java
public static Map readPurchasingSupplierInformationSheet(XSSFWorkbook wb, int sheetNum) { Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>(); //?//from www .ja va2 s . c o m XSSFSheet dataSheet = wb.getSheetAt(sheetNum); int rowID = 0; XSSFRow currRow = dataSheet.getRow(rowID); while (currRow != null) { if (rowID == 0) { // rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } else { //?ID String recordID; if (currRow.getCell(0) != null) { recordID = currRow.getCell(0).toString(); } else { recordID = "null"; } //???? String companyName; if (currRow.getCell(1) != null) { companyName = currRow.getCell(1).toString(); } else { companyName = null; } //?? String companyID; if (currRow.getCell(2) != null) { companyID = currRow.getCell(2).toString(); } else { companyID = "null"; } //?? String supplierPackageNumber; if (currRow.getCell(3) != null) { supplierPackageNumber = currRow.getCell(3).toString(); } else { supplierPackageNumber = "null"; } //??? String supplierPackageName; if (currRow.getCell(4) != null) { supplierPackageName = currRow.getCell(4).toString(); } else { supplierPackageName = "null"; } //?PurchaseHeadID String PHID; if (currRow.getCell(6) != null) { PHID = currRow.getCell(6).toString(); } else { PHID = "null"; } //? String receivedTime; if (currRow.getCell(7) != null) { receivedTime = currRow.getCell(7).toString(); } else { receivedTime = "null"; } ArrayList<String> info = new ArrayList<String>(); info.add(companyName); info.add(companyID); info.add(supplierPackageNumber); info.add(supplierPackageName); info.add(PHID); info.add(receivedTime); map.put(recordID, info); rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } } return map; }
From source file:dataaccess.SourceDataReader.java
public static Map readBasicProcurementPlan(XSSFWorkbook wb, int sheetNum) { Map<String, Double> procurementPlan = new HashMap<String, Double>(); XSSFSheet sheet = wb.getSheetAt(sheetNum); int rowID = 0; XSSFRow currRow = sheet.getRow(rowID); while (currRow != null) { if ((rowID == 0) || (rowID == 1)) { //?/* ww w .ja v a2s . c om*/ rowID = rowID + 1; currRow = sheet.getRow(rowID); } else { //?PHID String PHID = currRow.getCell(1).toString(); //? Double price = Double.valueOf(currRow.getCell(7).toString()); procurementPlan.put(PHID, price); rowID = rowID + 1; currRow = sheet.getRow(rowID); } } return procurementPlan; }
From source file:database.scanners.Scanner.java
protected Iterator GetSheetIterator(String databaseName) throws IOException { InputStream input = new BufferedInputStream(new FileInputStream(databaseName)); XSSFWorkbook wb = new XSSFWorkbook(input); XSSFSheet sheet = wb.getSheetAt(0); input.close();// ww w .j a v a2 s.c o m return sheet.rowIterator(); }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null; List<String> result = new ArrayList<String>(); result.add(""); try {/*from ww w . j a va 2s . c o m*/ 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; }