List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Reads the dimensions for a matrix model from a spreadsheet. * This method is necessary because Excel spreadsheets * do not reliably store the row and column dimension * in the meta-information. The values that are stored * there are not guaranteed to be correct in all cases. * * @param fileName the file name * @return the matrix dimensions//from ww w . j a v a 2 s .co m */ public static MatrixDimensions readDimensions(String fileName) { // Create the results holder. MatrixDimensions matrixDimensions = new MatrixModel.MatrixDimensions(); // Try to read the spreadsheet. try { // Attempt to open the spreadsheet. XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName))); // Scan the sheets. Iterator<XSSFSheet> sheets = workbook.iterator(); // Skip the first sheet. XSSFSheet sheet = sheets.next(); // Move to the sheet for the first variable. sheet = sheets.next(); // Find the number of rows. matrixDimensions.rows = sheet.getLastRowNum(); // Prepare to check the first row. Iterator<Row> rowIterator = sheet.iterator(); // Check the header row length Row row = rowIterator.next(); matrixDimensions.columns = row.getLastCellNum() - 2; // Close the workbook. workbook.close(); // Catch errors. } catch (Exception e) { // Note an error. matrixDimensions = null; } // Return the results. return matrixDimensions; }
From source file:helpers.Excel.ExcelDataFormat.java
private Object unmarshalXLSX(Exchange exchng, InputStream in) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); if (importType != ImportType.FORMATTED) { return marshalAsArray(sheet.iterator()); } else {//from w w w . j a va 2s . com OneExcel excel = new OneExcel(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator); logger.info("Loading sheet:" + i); logger.info("Data:" + onesheet.data.size()); if (onesheet.data.size() > 0) excel.sheets.add(onesheet); } logger.info("Total sheets:" + excel.sheets.size()); ArrayList<HashMap<String, Object>> resu = excel.GenerateResult(); HashMap<String, Object> mappings = excel.GenerateMappings(); exchng.getOut().setHeader("mappings", mappings); exchng.getOut().setHeader("xlsdata", resu); return resu; } }
From source file:icalendarconverter.ExcelConverter.java
public List<ScheduleClass> readExcel() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add;/*from w ww .j a va 2 s . c om*/ int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(f); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Nama Mata Kuliah")) { colMatkulIdx = cell.getColumnIndex(); break outerloop; } } } //System.out.println("col matkul = "+colMatkulIdx); System.out.println("sheet = " + sheet.getLastRowNum()); outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { row = sheet.getRow(i); if (row == null) { i = sheet.getLastRowNum(); break outerloop2; } Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() != Cell.CELL_TYPE_BLANK && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } if (sumary[l].equalsIgnoreCase("Okt")) { sumary[l] = "10"; } if (sumary[l].equalsIgnoreCase("`16")) { sumary[l] = "2016"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); // System.out.println("LC = "+lc); // sp = new SimpleDateFormat("EEEE, MMMM d, yyyy"); // String b = sumary[3] + "/" + sumary[2] + "/" + sumary[5]; // date = new Date(b); //System.out.println(sp.format(date)); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { if (cell.getStringCellValue().equalsIgnoreCase("Shift 1") || cell.getStringCellValue().equalsIgnoreCase("Shift 2")) { CellReference cr = new CellReference(cell.getRowIndex() + 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); String delimsJam = "[-]"; String[] arrJam = c.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == colMatkulIdx) { subject = cell.getStringCellValue(); //System.out.println("Subject = "+subject); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colMatkulIdx + 1 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // location.add(String.valueOf((int)cell.getNumericCellValue())); // locationIdx.add(cell.getColumnIndex()); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); //System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } //System.out.print(cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); //System.out.println(splt2[l] + "= lab"); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } //System.out.print(c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } // System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(i)+",location = "+location.get(i)); // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } for (int j = 0; j < dosen.size(); j++) { //System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(j)+",location = "+location.get(j)); scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return mergeringList(scheduleList); }
From source file:icalendarconverter.ReadExcel.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in A3 CellReference cellReference = new CellReference("A3"); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); CellValue cellValue = evaluator.evaluate(cell); switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print((int) cell.getNumericCellValue() + " \t\t "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " \t\t "); break; }// w w w.java 2 s .c o m } System.out.println(); } fis.close(); }
From source file:ik1004labb5.DAOHundExcel.java
@Override public List<DTOHund> getHundar() { //Ls in frn inputstream, hmta workbooken, vlj rtt sheet och ls in rader List<DTOHund> hundar = new ArrayList<>(); XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = worksheet.iterator(); DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/* ww w .j av a2 s. c o m*/ //String id = row.getCell(0).getStringCellValue(); //Problem mot Excelfilen vid lgg till. Kan jag bestmma att cellen ska vara numerisk? String id = df.formatCellValue(row.getCell(0)); String namn = df.formatCellValue(row.getCell(1)); String ras = df.formatCellValue(row.getCell(2)); String bildURL = df.formatCellValue(row.getCell(3)); //String iHundgrd = df.formatCellValue(row.getCell(4)); DTOHund dtoHund = new DTOHund(Integer.parseInt(id), namn, ras, bildURL); hundar.add(dtoHund); } return hundar; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
public ArrayList<ErrorModel> startReporting_QtrVerification(int SORQ_tokenLocation, int EORQ_tokenLocation, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); boolean operationCheck; boolean financialCheck; int first_Occurance_Of_Financial_Comparision = 0; XSSFSheet Sheet = workbook.getSheet("Reporting_Qtr"); Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/* ww w . j a v a2 s . c o m*/ Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1) { String key = cell.getStringCellValue(); if ((key.equalsIgnoreCase("Financial Comparison")) && (first_Occurance_Of_Financial_Comparision == 0)) { first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1; } } } } operationCheck = operation_Standard_Workingsection_Verification(SORQ_tokenLocation, first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList, errorModelList, workbook); //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORQ_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook); //if return false that means no error. if (!operationCheck) { reporting_Qtr_operation_Standard_C_To_I_Column_Verification((SORQ_tokenLocation + 3), first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook); } /*if(!financialCheck) { }*/ return errorModelList; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
public ArrayList<ErrorModel> startReporting_YearVerification(int SORY_tokenLocation, int EORY_tokenLocation, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); boolean operationCheck; boolean financialCheck; int first_Occurance_Of_Financial_Comparision = 0; XSSFSheet Sheet = workbook.getSheet("Reporting_Year"); Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from w w w.jav a2 s . co m Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1) { String key = cell.getStringCellValue(); if ((key.equalsIgnoreCase("Financial Comparison")) && (first_Occurance_Of_Financial_Comparision == 0)) { first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1; } } } } operationCheck = operation_Standard_Workingsection_Verification(SORY_tokenLocation, first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList, errorModelList, workbook); //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORY_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook); //if return false that means no error. if (!operationCheck) { reporting_Year_operation_Standard_C_To_I_Column_Verification((SORY_tokenLocation + 3), first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook); } /*if(!financialCheck) { }*/ return errorModelList; }
From source file:in.expertsoftware.colorcheck.VerifyTokens.java
/** * Verify_tokens function takes two parameters first is number of sheets present in workbook and second is an instance of workbook. * it process each sheet individually and verifying the tokens position as well check tokens are present or not and error is handled by error model. * @param NumberOfSheets Number of Sheet present In this workbook * @param workbook an instance of XSSFWorkbook * @return ArrayList of type Error and Model * // w w w. jav a 2 s. c om */ public ArrayList verify_tokens(int NumberOfSheets, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); ArrayList<TokenModel> tokenModelList = new ArrayList<TokenModel>(); ArrayList<String> metadataCount = new ArrayList<String>(); ArrayList errorAndTokenList = new ArrayList(); for (int i = 0; i < NumberOfSheets; i++) { XSSFSheet Sheet = workbook.getSheetAt(i); if (containSheetName(Sheet.getSheetName())) { Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { String key = cell.getStringCellValue(); switch (Sheet.getSheetName()) { case "BasicInfo": if (!(cell.getCellType() == Cell.CELL_TYPE_BLANK)) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Vulnerable Token present at"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } break; case "User_Financial_Input": switch (key.trim()) { case "SOUFI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOUFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOUFI"); } break; case "EOUFI": tknmodel = new TokenModel(); if (metadataCount.contains("EOUFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOUFI"); } break; case "UFCS1": tknmodel = new TokenModel(); if (metadataCount.contains("UFCS1")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("UFCS1"); } break; case "UFCS2": tknmodel = new TokenModel(); if (metadataCount.contains("UFCS2")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("UFCS2"); } break; } break; case "User_Operation_Input": switch (key.trim()) { case "SOUOI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOUOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOUOI"); } break; case "EOUOI": tknmodel = new TokenModel(); if (metadataCount.contains("EOUOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOUOI"); } break; } break; case "Unit_Map": break; case "Operation_Standard": switch (key.trim()) { case "SOOI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOI"); } break; case "EOOI": tknmodel = new TokenModel(); if (metadataCount.contains("EOOI")) { ErrorModel errmodel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef1 = new CellReference(cell); errmodel.setCell_ref(cellRef1.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOI"); } break; case "SOOCS": tknmodel = new TokenModel(); if (metadataCount.contains("SOOCS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCS"); } break; case "EOOCS": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCS"); } break; case "SOOWD": tknmodel = new TokenModel(); if (metadataCount.contains("SOOWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOWD"); } break; case "EOOWD": tknmodel = new TokenModel(); if (metadataCount.contains("EOOWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOWD"); } break; } break; case "Financial_Standard": switch (key.trim()) { case "SOFI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFI"); } break; case "EOFI": tknmodel = new TokenModel(); if (metadataCount.contains("EOFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFI"); } break; case "FCS1": tknmodel = new TokenModel(); if (metadataCount.contains("FCS1")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("FCS1"); } break; case "FCS2": tknmodel = new TokenModel(); if (metadataCount.contains("FCS2")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("FCS2"); } break; case "SOFWD": tknmodel = new TokenModel(); if (metadataCount.contains("SOFWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFWD"); } break; case "EOFWD": tknmodel = new TokenModel(); if (metadataCount.contains("EOFWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFWD"); } break; case "SOFCP": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCP")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCP"); } break; case "EOFCP": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCP")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCP"); } break; } break; case "CrossCheck": switch (key.trim()) { case "SOCCK": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOCCK")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOCCK"); } break; case "EOCCK": tknmodel = new TokenModel(); if (metadataCount.contains("EOCCK")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOCCK"); } break; } break; case "Reporting_Qtr": switch (key.trim()) { case "SORQ": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SORQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SORQ"); } break; case "EORQ": tknmodel = new TokenModel(); if (metadataCount.contains("EORQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EORQ"); } break; } break; case "Reporting_Year": switch (key.trim()) { case "SORY": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SORY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SORY"); } break; case "EORY": tknmodel = new TokenModel(); if (metadataCount.contains("EORY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EORY"); } break; } break; case "Chart_Qtr": switch (key.trim()) { case "SOOCQ": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCQ"); } break; case "EOOCQ": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCQ"); } break; case "SOFCQ": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCQ"); } break; case "EOFCQ": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCQ"); } break; } break; case "Chart_Year": switch (key.trim()) { case "SOOCY": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCY"); } break; case "EOOCY": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCY"); } break; case "SOFCY": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCY"); } break; case "EOFCY": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCY"); } break; } break; case "Summary": switch (key.trim()) { case "SOS": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOS"); } break; case "EOS": tknmodel = new TokenModel(); if (metadataCount.contains("EOS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOS"); } break; } break; case "MetaDataSheet": break; } } } } } else { System.out.println("workbook have another sheet"); ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("This is an extra sheet in this workbook"); errmodel.setSheet_name(Sheet.getSheetName()); //errmodel.setRow(-2); errmodel.setError_level("Warning"); errorModelList.add(errmodel); } } //for checking every tocken is present or not; errorModelList.addAll(verifyAllTokenPresent(metadataCount)); errorAndTokenList.add(errorModelList); errorAndTokenList.add(tokenModelList); return errorAndTokenList; }
From source file:info.toegepaste.controller.UploadController.java
public void fileUploadListener(FileUploadEvent e) { // Get uploaded file from the FileUploadEvent to use with primefaces this.file = e.getFile(); // Get uploaded file to use with Apache POI InputStream POIFile;//w ww . ja v a2 s .com XSSFWorkbook workbook = null; try { POIFile = e.getFile().getInputstream(); //Create workbook workbook = new XSSFWorkbook(POIFile); } catch (IOException ex) { } // Print out the information of the file System.out.println( "Uploaded File Name Is :: " + file.getFileName() + " :: Uploaded File Size :: " + file.getSize()); //Create a worksheet (needed to get rows) XSSFSheet worksheet = workbook.getSheetAt(0); //Divide worksheet into rows Iterator<Row> rowIterator = worksheet.iterator(); //Get Classgroup from line 1 cell 2 XSSFRow currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> klasIter = currentRow.cellIterator(); XSSFCell klasCell = currentRow.getCell(1); //Get Course from line 2 cell 2 currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> courseIter = currentRow.cellIterator(); XSSFCell courseCell = currentRow.getCell(1); //Get subject from line 3 cell 2 System.out.println("Stuff"); currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> subjectIter = currentRow.cellIterator(); XSSFCell subjectCell = currentRow.getCell(1); System.out.println("Subject:" + subjectCell.toString()); System.out.println("Subject:" + subjectCell.getStringCellValue()); //Get total possible score from line 4 cell 2 currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> totalScoreIter = currentRow.cellIterator(); XSSFCell totalScoreCell = currentRow.getCell(1); //Skip line 5 & 6 currentRow = (XSSFRow) rowIterator.next(); currentRow = (XSSFRow) rowIterator.next(); currentRow = (XSSFRow) rowIterator.next(); List<Classgroup> group = classgroupService.getWithName(klasCell.getStringCellValue()); //Persist new exam to database Exam newExam = new Exam(); Classgroup newGroup = new Classgroup(); //Check if classgroup already exists, create if it doesnt if (group.isEmpty()) { newGroup.setName(klasCell.getStringCellValue()); newGroup.setCourses(null); classgroupService.insert(newGroup); } else { newGroup = group.get(0); } newExam.setClassgroup(newGroup); List<Course> course = courseService.getWithName(courseCell.getStringCellValue()); Course newCourse = new Course(); //Check if course exists, if not create if (course.isEmpty()) { newCourse.setName(courseCell.getStringCellValue()); int year = Calendar.getInstance().get(Calendar.YEAR); newCourse.setYear(year); newCourse.setClassgroup(newGroup); int maand = Calendar.getInstance().get(Calendar.MONTH); if (maand <= 6 && maand >= 1) { newCourse.setSemester(2); } else { newCourse.setSemester(1); } courseService.insert(newCourse); } else { newCourse = course.get(0); } newExam.setCourse(newCourse); newExam.setName(subjectCell.getStringCellValue()); // double totalScoreValue = ; // String totalScoreWorkaround =String.valueOf(totalScoreValue); newExam.setTotal((int) totalScoreCell.getNumericCellValue()); examService.insert(newExam); //Read file to end, cell 0 student number, cell 1 name, cell 2 score while (rowIterator.hasNext()) { XSSFCell userNrCell = currentRow.getCell(0); System.out.println(userNrCell.toString()); int StudentNumber = 0; // String StudentNumberWorkaround =userNrCell.getStringCellValue(); StudentNumber = (int) userNrCell.getNumericCellValue(); List<Student> currentStudent = studentsService.getStudentInListByNumber(StudentNumber); Student newStudent = new Student(); XSSFCell userNameCell = currentRow.getCell(1); //Check if student exists, else create if (currentStudent.isEmpty()) { String fullName = userNameCell.getStringCellValue(); String nameArray[] = fullName.split(" "); newStudent.setFirstname(nameArray[0]); newStudent.setLastname(nameArray[1]); newStudent.setNumber(StudentNumber); newStudent.setEmail("r0" + StudentNumber + "@student.thomasmore.be"); newStudent.setClassgroup(newGroup); newStudent.setPassword(null); studentsService.insert(newStudent); } else { newStudent = currentStudent.get(0); } //Add score to student List<Score> currentScore = scoreService.checkIfScoreExists(newStudent, newExam); XSSFCell scoreCell = currentRow.getCell(2); Score scoreEntry = new Score(); if (currentScore.isEmpty()) { scoreEntry.setExam(newExam); scoreEntry.setScore((int) scoreCell.getNumericCellValue()); if (currentStudent.isEmpty()) { scoreEntry.setStudent(newStudent); } else { scoreEntry.setStudent(currentStudent.get(0)); } scoreService.insert(scoreEntry); } else { scoreEntry = currentScore.get(0); } currentRow = (XSSFRow) rowIterator.next(); } }
From source file:info.toegepaste.www.service.ExcelServiceImpl.java
@Override @TransactionAttribute(REQUIRES_NEW)//from www. j a v a 2 s. co m public String upload(Part file) { try { //declaratie variabelen //Variabelen voor tijdelijke gegevens int cellInt; String cellString; int cellNr; String infoCel = " "; int scoresTeller = 1; String fout = " "; //debug of overzetvariabelen String klasDebug = " "; String vakDebug = " "; String testDebug = " "; int totaalDebug = 0; List<Integer> studentennrDebug = new ArrayList<Integer>(); List<String> naamDebug = new ArrayList<String>(); List<Integer> scoreDebug = new ArrayList<Integer>(); Iterator<Row> rowIterator = null; if (file.getSubmittedFileName().contains("xlsx")) { //lees de content stream in naar de hssfworkbook XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) XSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else if (file.getSubmittedFileName().contains("xls")) { //lees de content stream in naar de hssfworkbook HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) HSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else { fout = "Het geuploadde bestand heeft niet de juiste indeling"; } if (rowIterator != null) { while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Leest elke horizontale lijn van links naar rechts uit in de console Iterator<Cell> cellIterator = row.cellIterator(); cellNr = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cellNr++; //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); cellInt = (int) cell.getNumericCellValue(); if (infoCel.equals("totaal")) { totaalDebug = cellInt; } else { if (scoresTeller == 1) { studentennrDebug.add(cellInt); scoresTeller++; } else { scoreDebug.add(cellInt); scoresTeller = 1; } } break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); cellString = cell.getStringCellValue(); //Als er een titel in de cel zit en steek in infoCel if (cellString.toLowerCase().equals("vak") || cellString.toLowerCase().equals("klas") || cellString.toLowerCase().equals("test") || cellString.toLowerCase().equals("totaal") || cellString.toLowerCase().equals("score")) { infoCel = cellString.toLowerCase(); } else { //inhoud in de cell switch (infoCel) { case "klas": klasDebug = cellString; break; case "vak": vakDebug = cellString; break; case "test": testDebug = cellString; break; case "score": naamDebug.add(cellString); scoresTeller++; break; default: break; } } break; } } } } System.out.println(""); int testId = 0; int vakId = 0; int studentId = 0; try { //als er al een test is aangemaakt met die naam, voeg gewoon punten toe testId = getTestId(testDebug); } catch (Exception e) { try { //als er al een vak is aangemaakt met die naam, voeg gewoon een vak toe vakId = getVakId(vakDebug); } catch (Exception e1) { insertVak(vakDebug); vakId = getVakId(vakDebug); } //maak een nieuwe test aan insertTest(vakId, testDebug, totaalDebug); testId = getTestId(testDebug); } //Scores toevoegen int index = 0; for (int studentenNr : studentennrDebug) { studentId = getStudentId(studentenNr); insertScore(studentId, testId, scoreDebug.get(index), totaalDebug); index++; } return fout; } catch (IOException e) { // Error handling return "Er is iets misgelopen bij het inlezen van het bestand."; } }