List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:Excel.ExcelToJTable.java
static void fillData(File file) { try {/* w w w.ja v a2 s .c om*/ FileInputStream fs = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(fs); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() == 0) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); columns.add(cell.getStringCellValue()); } } else { dataTemp = new Vector(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: dataTemp.add(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: dataTemp.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: dataTemp.add(""); break; default: break; } } data.add(dataTemp); fs.close(); } } } catch (Exception ex) { System.out.print("Cause: \n" + ex.getCause() + "\n Message: \n" + ex.getMessage() + "\n Stack Trace: \n" + Arrays.toString(ex.getStackTrace())); } }
From source file:excel2sql.util.ExcelReader.java
public ArrayList<String> read(int pageIndex) { ArrayList<String> queryes = new ArrayList<String>(); boolean first = true; ArrayList<String> params = new ArrayList<String>(); ArrayList<String> values = new ArrayList<String>(); try {// ww w .j av a 2s .co m FileInputStream file = new FileInputStream(new File(path)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(pageIndex); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (first) { params.add(String.valueOf((int) Math.floor(cell.getNumericCellValue()))); } else { values.add(String.valueOf((int) Math.floor(cell.getNumericCellValue()))); } break; case Cell.CELL_TYPE_STRING: if (first) { params.add(cell.getStringCellValue()); } else { values.add(cell.getStringCellValue()); } break; } } String[] stockParams = new String[params.size()]; stockParams = params.toArray(stockParams); String[] stockValues = new String[values.size()]; stockValues = values.toArray(stockValues); values.clear(); if (!first) { queryes.add(QueryBuilder.getQuery(sheet.getSheetName(), stockParams, stockValues)); } first = false; } file.close(); return queryes; } catch (IOException ioe) { ioe.printStackTrace(); } return null; }
From source file:ExcelInputSimulation.ExcelReader.java
public void readExcel() { try {//from w w w . j ava 2 s. c o m this.workbook = new XSSFWorkbook(this.file); } catch (IOException ex) { } XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); StatisticsGenerator gen = new StatisticsGenerator(); int counterrow = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (counterrow > 0) { int i = 0; while (cellIterator.hasNext()) { Customer temp = new Customer(); Cell cell = cellIterator.next(); if (i == 0) { temp.setNumber((int) cell.getNumericCellValue()); System.out.print(cell.getNumericCellValue() + "\t"); } else if (i == 1) { temp.setJenis(cell.getStringCellValue()); } else if (i == 2) { String temprealtime = cell.getStringCellValue(); temp.setArrivaltime(gen.convertToRealTime(temprealtime)); System.out.print(temprealtime + "\t"); } else if (i == 3) { String temprealtime = cell.getStringCellValue(); temp.setArrivaltime(gen.convertToRealTime(temprealtime)); System.out.print(temprealtime + "\t"); } else if (i == 4) { String tempboolean = cell.getStringCellValue(); if (tempboolean.equals("Y")) { temp.setToPoliklinik(true); } else { temp.setToPoliklinik(false); } System.out.print(tempboolean + "\t"); } i++; } } System.out.println(""); counterrow++; } try { file.close(); } catch (IOException ex) { } }
From source file:ExcelReadFile.ExcellReadSpecialite.java
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException { List<Specialite> listSpecialite = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); Specialite sp = new Specialite(); Admin admin = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: sp.setIntitule((String) getCellValue(nextCell)); break; }/*from w ww. jav a 2 s .com*/ } listSpecialite.add(sp); } file.close(); return listSpecialite; }
From source file:ExcelReadFile.ExcelReadMedecin.java
public List<Medecin> readBooksFromExcelFile(String excelFilePath) { List<Medecin> listMed = null; try {//from www.j ava2s . c o m listMed = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); Medecin med = new Medecin(); Specialite s = new Specialite(); Admin ad = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: ad.setId_admin(((Integer) getCellValue(nextCell))); med.setAdmin(serviceAdmin.findAdminById(ad.getId_admin())); break; case 1: s.setId_specialite((((Integer) getCellValue(nextCell)).intValue())); med.setSpecialite(serviceSpecialite.findSpecialiteById(s.getId_specialite())); break; case 2: med.setAdresse((String) getCellValue(nextCell)); break; case 3: med.setDate_naissance((String) getCellValue(nextCell)); break; case 4: med.setLogin((String) getCellValue(nextCell)); break; case 5: med.setNom((String) getCellValue(nextCell)); break; case 6: med.setNumero_telephone((String) getCellValue(nextCell)); break; case 7: med.setPassword((String) getCellValue(nextCell)); break; case 8: med.setPrenom((String) getCellValue(nextCell)); break; } } listMed.add(med); } file.close(); } catch (IOException e) { e.printStackTrace(); } return listMed; }
From source file:ExcelReadFile.ExcelReadSecretaire.java
public List<Secretaire> readBooksFromExcelFile(String excelFilePath) throws IOException { List<Secretaire> listMed = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); Medecin s = new Medecin(); Secretaire med = new Secretaire(); Admin ad = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: ad.setId_admin((((Integer) getCellValue(nextCell)).intValue())); Admin a = serviceAdmin.findAdminById(ad.getId_admin()); med.setAdmin(a);// w w w.j av a2 s. com break; case 1: s.setId_utilisateur((((Integer) getCellValue(nextCell)).intValue())); Medecin sa = serviceMedecin.findMedecinById(s.getId_utilisateur()); med.setMedecin(sa); break; case 2: med.setAdresse((String) getCellValue(nextCell)); break; case 3: med.setDate_naissance((String) getCellValue(nextCell)); break; case 4: med.setLogin((String) getCellValue(nextCell)); break; case 5: med.setNom((String) getCellValue(nextCell)); break; case 6: med.setNumero_telephone((String) getCellValue(nextCell)); break; case 7: med.setPassword((String) getCellValue(nextCell)); break; case 8: med.setPrenom((String) getCellValue(nextCell)); break; } } listMed.add(med); } file.close(); return listMed; }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
protected List<String> getDataFromRow(Row row) { final DataFormatter dataFormatter = new DataFormatter(); return stream(spliteratorUnknownSize(row.cellIterator(), ORDERED), false) .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList()); }
From source file:FileHelper.ExcelHelper.java
public DataSheet ReadTestCaseFileFromSheet(String fileName, String sheetName, MyDataHash myDataHash, String rawData) {// w w w .j ava2 s.co m try { File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); XSSFSheet sheet = book.getSheet(sheetName); Iterator<Row> itr = sheet.iterator(); DataSheet dataSheet = new DataSheet(); ArrayList<RowDataFromFile> datas = new ArrayList<RowDataFromFile>(); ArrayList<DataHash> dataHash = new ArrayList<>(); int colmnDataStart = 0, colmnDataStop = 0, numReal = 0; ArrayList<NameDynamic> nameDynamic = new ArrayList<NameDynamic>(); ArrayList<DataInput> listDataInput = new ArrayList<>(); ArrayList<DataInputLevel2> dataInputLevel2 = new ArrayList<>(); while (itr.hasNext()) { RowDataFromFile dataRow = new RowDataFromFile(); JsonObject jObjReq = new JsonObject(); String caller = ""; Row row = itr.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: { String str = cell.getStringCellValue(); if (str.equals("STT")) { while (cellIterator.hasNext()) { Cell cell1 = cellIterator.next(); switch (cell1.getCellType()) { case Cell.CELL_TYPE_STRING: { // System.out.println(cell1.getStringCellValue()); if (cell1.getStringCellValue().equals("Data Request")) { colmnDataStart = cell1.getColumnIndex(); } if (cell1.getStringCellValue().equals("Threads")) { colmnDataStop = cell1.getColumnIndex() - 1; } if (cell1.getStringCellValue().equals("Result Real")) { // System.out.println("Colmn Reail: " + cell1.getColumnIndex()); numReal = cell1.getColumnIndex(); } break; } case Cell.CELL_TYPE_NUMERIC: { System.out.println(cell1.getNumericCellValue()); break; } } } Row row1 = sheet.getRow(1); Row row2 = sheet.getRow(2); Row row3 = sheet.getRow(3); Row row4 = sheet.getRow(4); Cell cellColmn; Cell cellColmn2; int numColmn = colmnDataStart; while (numColmn <= colmnDataStop) { cellColmn = row1.getCell(numColmn); String temp = GetValueStringFromCell(cellColmn); cellColmn2 = row2.getCell(numColmn); NameDynamic nameDy = CutStrGetNameDynamic(GetValueStringFromCell(cellColmn2)); if (nameDy.getIsDyn().equals("1")) { // Check Data is change when run Thread nameDynamic.add(nameDy); } // Add to list save data api listDataInput.add(new DataInput(temp, nameDy.getName())); DataHash dataHt = myDataHash.CheckNameDataIsHash(sheetName, nameDy.getName()); if (dataHt != null) { dataHt.setNumColumn(numColmn); dataHash.add(dataHt); } if (temp.equals("Object")) { // Exist object group datas name ArrayList<DataInput> listDataIputLevel2 = new ArrayList<>(); cellColmn = row3.getCell(numColmn); cellColmn2 = row4.getCell(numColmn); String tempT = GetValueStringFromCell(cellColmn); if (!tempT.equals("")) { while (!GetValueStringFromCell(cellColmn).equals("")) { nameDy = CutStrGetNameDynamic(GetValueStringFromCell(cellColmn2)); if (nameDy.getIsDyn().equals("1")) { // Check Data is change when run Thread nameDynamic.add(nameDy); } dataHt = myDataHash.CheckNameDataIsHash(sheetName, nameDy.getName()); if (dataHt != null) { dataHt.setNumColumn(numColmn); dataHash.add(dataHt); } listDataIputLevel2.add( new DataInput(GetValueStringFromCell(cellColmn), nameDy.getName())); numColmn++; cellColmn = row3.getCell(numColmn); cellColmn2 = row4.getCell(numColmn); } numColmn--; dataInputLevel2.add(new DataInputLevel2(listDataIputLevel2)); } else { dataInputLevel2.add(new DataInputLevel2(listDataIputLevel2)); } } numColmn++; } Gson gson = new Gson(); System.out.println(gson.toJson(listDataInput)); System.out.println(gson.toJson(dataHash)); } break; } case Cell.CELL_TYPE_NUMERIC: { // System.out.println(cell.getNumericCellValue()); if (cell.getNumericCellValue() > 0) { dataRow.setId(row.getRowNum()); String isSecutiry = "no"; int arrIndex = 0; int arrIndexReq = 0; // Object con int arrIndexRow = 0; while (cellIterator.hasNext()) { Cell cell1 = cellIterator.next(); if ((cell1.getColumnIndex() >= colmnDataStart) && (cell1.getColumnIndex() < colmnDataStop)) { if (listDataInput.get(arrIndex).getType().equals("Object")) { JsonObject jObj = new JsonObject(); int i = 0; int size = dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().size(); while (i < size) { if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("String")) { String value = GetValueStringFromCell(cell1); if (!dataHash.isEmpty()) { for (DataHash dataH : dataHash) { if (dataH.getNumColumn() == cell1.getColumnIndex()) { value = EncryptHelper.EncryptData(value, dataH.getAlgorithm(), dataH.getKey(), dataH.getIv()); } } } jObj.addProperty(dataInputLevel2.get(arrIndexReq) .getListDataIputLevel2().get(i).getName(), value); } else if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("Integer")) { int value = GetValueIntegerFromCell(cell1); jObj.addProperty(dataInputLevel2.get(arrIndexReq) .getListDataIputLevel2().get(i).getName(), value); } else if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("Object")) { String value = GetValueStringFromCell(cell1); Gson gson = new Gson(); JsonObject obj = gson.fromJson(value, JsonObject.class); jObj.add(dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getName(), obj); } i++; if (i < size) { cell1 = cellIterator.next(); } } arrIndexReq++; jObjReq.add(listDataInput.get(arrIndex).getName(), jObj); } else if (listDataInput.get(arrIndex).getType().equals("String")) { String value = GetValueStringFromCell(cell1); if (!dataHash.isEmpty()) { for (DataHash dataH : dataHash) { if (dataH.getNumColumn() == cell1.getColumnIndex()) { value = EncryptHelper.EncryptData(value, dataH.getAlgorithm(), dataH.getKey(), dataH.getIv()); } } } jObjReq.addProperty(listDataInput.get(arrIndex).getName(), value); } else if (listDataInput.get(arrIndex).getType().equals("Integer")) { int value = GetValueIntegerFromCell(cell1); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), value); } arrIndex++; } else if (cell1.getColumnIndex() == colmnDataStop) { isSecutiry = GetValueStringFromCell(cell1); dataRow.setNameAlgorithm(isSecutiry); } else if (cell1.getColumnIndex() > colmnDataStop) { if (arrIndexRow == 0) { dataRow.setThread(GetValueIntegerFromCell(cell1)); } else if (arrIndexRow == 1) { dataRow.setResultExpect(GetValueStringFromCell(cell1)); } arrIndexRow++; } } // System.out.println("data: " + jObj.toString()); // System.out.println("data Req: " + jObjReq.toString()); String[] arrR = rawData.split(","); String rawDataNew = ""; char a = '"'; for (String str : arrR) { if (str.charAt(0) == a) { String value = str.substring(1, str.length() - 1); rawDataNew += value; } else { JsonElement je = jObjReq.get(str); if (je.isJsonObject()) { String value = je.toString(); rawDataNew += value; } else { String value = je.getAsString(); rawDataNew += value; } } } String[] arr = isSecutiry.split("-"); if (arr[0].equals("chksum")) { String chksum = CheckSumInquireCard.createCheckSum(isSecutiry, rawDataNew); // System.out.println("chksum: " + chksum); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), chksum); } else if (arr[0].equals("signature")) { String signature = RSASHA1Signature.getSignature(isSecutiry, rawDataNew); // System.out.println("signature: " + signature); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), signature); } // System.out.println("data Request: " + jObjReq.toString()); dataRow.setData(jObjReq); dataRow.setNumReal(numReal); Gson gson = new Gson(); System.out.println("data row: " + gson.toJson(dataRow)); datas.add(dataRow); } break; } } } dataSheet.setDatas(datas); dataSheet.setNameDynamic(nameDynamic); dataSheet.setListDataInput(listDataInput); dataSheet.setDataInputLevel2(dataInputLevel2); Gson gson = new Gson(); // System.out.println("save data: " + gson.toJson(datas)); fis.close(); return dataSheet; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new DataSheet(); } }
From source file:Files.XLSX2BatchHandler.java
public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try {/*from w ww .j av a 2 s . co m*/ FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); List<String> errorList = new ArrayList<>(); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList numCell++; } System.out.println("XLSX2BatchHandler.java UCFR loaded = " + DB.isUCFRLoaded()); if (numCell == GlobalVar.LEAVE_TITLES_V1.length && DB.isUCFRLoaded()) { // check if the excel is the leave roster System.out.println("XLSX2BatchHandler.java: V1, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<>(); int keys = 0; //index of the cell // reset date every row while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V1 || keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** lastFour = GlobalVar.last4Generator(lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1 || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); String dateString = df.formatCellValue(cell); container.put(keyList.get(keys), dateString); } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { int type = cell.getCellType(); String value = null; if (type == HSSFCell.CELL_TYPE_STRING) { value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string } container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } if (leaveDays != null) { int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, first5, leaves); //add leave into leaves globalCount = GlobalVar.batchGenerator(SSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid dates are entered!"); } } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && DB.isUCFRLoaded()) { // full SSN xlsx System.out.println("XLSX2BatchHandler.java: V2, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = DB.getLastNamefromSSN(fullSSN); String lastFour = GlobalVar.last4Generator(fullSSN); String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && !DB.isUCFRLoaded()) { System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = GlobalVar.TEMP_DMO_NAME; String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = GlobalVar.TEMP_DMO_NAME; MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.getDay() != null && soDate != null && soDate.getDay() != null) { if (siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid date entered"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); // write the leave on the batch file (PrintStream output) globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); } else if (!DB.isUCFRLoaded()) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: UCFR needs to be loaded to process this type of xlsx."); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file output.close(); outputFile.close(); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:FilesHandlers.ExcelHandler.java
/** * used for getting the content of the selected file * * @param file The name of the file to display * @param sheet The sheet number/* w w w. ja va 2s . c om*/ * @return The content of given sheet * @throws java.io.FileNotFoundException */ public ArrayList<String[]> getFileCtBySheet(String file, int sheet) throws FileNotFoundException, IOException { ArrayList<String[]> list = new ArrayList<String[]>(); ArrayList<String> row = new ArrayList<>(); File selectedFile = new File(this.workingDirectory.concat(file)); FileInputStream inputStream = new FileInputStream(selectedFile); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(sheet); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: row.add((String) cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: row.add("" + cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: row.add("" + cell.getNumericCellValue()); break; } } list.add(row.toArray(new String[list.size()])); row = new ArrayList<>(); } workbook.close(); inputStream.close(); return list; }