List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java
License:Open Source License
public boolean doImport(final String filename, final JProgressBar progress, final boolean showResults) { Runnable runnable = new Runnable() { public void run() { try { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Importiere Excel Datei..."); progress.setMinimum(0); }/*from ww w . j ava2s . c om*/ InputStream is = null; System.out.println(filename); if (filename.startsWith("http://")) { URL url = new URL(filename); URLConnection uc = url.openConnection(); is = uc.getInputStream(); } else if (filename.startsWith("/de/bund/bfr/knime/openkrise/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } try (HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is))) { HSSFSheet sheet; HSSFRow row; int numSuccess = 0; int numFailed = 0; String unusedFields = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); String tableName = sheet.getSheetName(); MyTable myT = DBKernel.myDBi.getTable(tableName); if (myT != null) { int numRows = sheet.getLastRowNum(); if (progress != null) { progress.setMaximum(numRows); progress.setValue(0); } row = sheet.getRow(0); String sql1 = ""; String sql2 = ""; String sql3 = ""; Vector<String> codeSql1 = new Vector<>(); Vector<String> codeSql2 = new Vector<>(); LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>(); int numCols = row.getLastCellNum(); String[] fieldNames = new String[numCols]; String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT); String[] ffieldTypes = new String[numCols]; MyTable[] myForeignTables = new MyTable[numCols]; String[] kzS = new String[numCols]; String[] dbFieldnames = new String[numCols]; int lfdCol = 0; Hashtable<String, String> dbFieldNames = new Hashtable<>(); for (int j = 0; j < numCols; j++) { String fieldName = row.getCell(j).getStringCellValue(); fieldNames[j] = fieldName; int ffe; String dbFieldName = getDBFieldName(fieldName, myT, takecareofID); if (dbFieldName != null) { String ft = getForeignTable(dbFieldName, myT); if (ft != null && ft.equals("DoubleKennzahlen")) { kzS[j] = getKZ(fieldName, dbFieldName); dbFieldnames[j] = dbFieldName; } else if (!dbFieldNames.containsKey(dbFieldName)) { dbFieldNames.put(dbFieldName, dbFieldName); sql1 += DBKernel.delimitL(dbFieldName) + ","; sql2 += "?,"; sql3 += DBKernel.delimitL(dbFieldName) + "=?,"; lfdCol++; } fieldTypes[j] = getType(dbFieldName, myT, takecareofID); } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) { if (!foreignTables.containsKey(myT.getForeignFields()[ffe])) foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>()); ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false); foreignTables.get(myT.getForeignFields()[ffe]).add(j); myForeignTables[j] = myT.getForeignFields()[ffe]; } else if (DBKernel.showHierarchic(tableName) && fieldName.toLowerCase().endsWith("-code")) { codeSql1.add(DBKernel.delimitL("CodeSystem") + "," + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis")); codeSql2.add( "'" + fieldName.substring(0, fieldName.length() - "-code".length()) + "',?,?"); } else if (!fieldName.equalsIgnoreCase("id")) { unusedFields += "," + fieldName; } } if (sql1.length() > 0 && sql2.length() > 0) { String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " (" + sql1.substring(0, sql1.length() - 1) + ") VALUES (" + sql2.substring(0, sql2.length() - 1) + ")"; PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int idCol = lfdCol + 1; sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET " + sql3.substring(0, sql3.length() - 1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql); PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()]; boolean doCode[] = new boolean[codeSql1.size()]; int codesI; for (codesI = 0; codesI < codeSql1.size(); codesI++) { sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName)) + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI) + ")"; psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql); } LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>(); LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>(); for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) { Vector<Integer> vs = entry.getValue(); String ssql1 = "", ssql2 = "", ssql3 = ""; for (int ii = 0; ii < vs.size(); ii++) { ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]); ssql2 += ",?"; ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?"; } if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) { sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename()) + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1) + ")"; psForeign.put(entry.getKey(), DBKernel.getDBConnection() .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)); sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename()) + " SET " + ssql3.substring(1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; psForeignUpdate.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql)); } } LinkedHashMap<Object, String> hashBL = null; Iterator<Row> rows = sheet.rowIterator(); int lfd = 0; while (rows.hasNext()) { row = (HSSFRow) rows.next(); boolean setID = false; Integer lastID = null; ps.clearParameters(); psUpdate.clearParameters(); for (codesI = 0; codesI < codeSql1.size(); codesI++) { psCodes[codesI].clearParameters(); doCode[codesI] = false; } LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>(); for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate .entrySet()) { MyTable myT1 = entry.getKey(); psForeign.get(myT1).clearParameters(); psForeignUpdate.get(myT1).clearParameters(); lfdColsForeign.put(myT1, 0); } if (row.getRowNum() > 0) { lfdCol = 0; codesI = 0; Object[] kzVal = new Object[numCols]; for (int j = 0; j < numCols; j++) { if (fieldTypes[j] != null) { /* * if (fieldNames[j].equals( * "Bundesland")) { hashBL = * DBKernel * .myDBi.getHashMap("County"); * } else { hashBL = null; } */ lfdCol++; if (fieldTypes[j].startsWith("VARCHAR(") || fieldTypes[j].startsWith("CHAR(") || fieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL); else if (fieldTypes[j].equals("BOOLEAN")) manageBoolean(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("INTEGER")) manageInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("BIGINT")) manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DOUBLE")) { if (kzS[j] != null) { lfdCol--; //System.err.println(dbFieldnames[j] + "\t" + kzS[j]); if (DBKernel.kzIsString(kzS[j])) kzVal[j] = manageString(null, null, lfdCol, row.getCell(j)); else if (DBKernel.kzIsBoolean(kzS[j])) kzVal[j] = manageBoolean(null, null, lfdCol, row.getCell(j)); else kzVal[j] = manageDouble(null, null, lfdCol, row.getCell(j)); } else { manageDouble(ps, psUpdate, lfdCol, row.getCell(j)); } } else System.err.println( "Wasn hier los? Undefinierter Feldtyp???? ->\t" + fieldNames[j]); } else if (myForeignTables[j] != null && ffieldTypes[j] != null) { lfdColsForeign.put(myForeignTables[j], lfdColsForeign.get(myForeignTables[j]) + 1); if (ffieldTypes[j].startsWith("VARCHAR(") || ffieldTypes[j].startsWith("CHAR(") || ffieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j), hashBL); else if (ffieldTypes[j].equals("BOOLEAN")) manageBoolean(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("INTEGER")) manageInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("BIGINT")) manageBigInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("DOUBLE")) { manageDouble(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); } else System.err.println(fieldNames[j] + " Feldtype????"); } else if (fieldNames[j].equals("ID")) { lastID = manageInteger(null, null, 0, row.getCell(j)); if (lastID != null) { if (DBKernel.hasID(tableName, lastID.intValue())) { psUpdate.setInt(idCol, lastID.intValue()); setID = true; } } } else if (DBKernel.showHierarchic(tableName) && fieldNames[j].toLowerCase().endsWith("-code")) { String code = manageString(psCodes[codesI], null, 1, row.getCell(j)); if (code != null && code.length() > 0) doCode[codesI] = true; codesI++; } else { //System.out.println(fieldNames[j]); } } try { if (setID) { psUpdate.execute(); } else { if (ps.executeUpdate() > 0) {// execute() lastID = DBKernel.getLastInsertedID(ps); } else { System.err.println("W"); } } numSuccess++; if (lastID != null) { for (int j = 0; j < numCols; j++) { if (dbFieldnames[j] != null && kzVal[j] != null) { DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j], lastID, null, kzS[j], kzVal[j]); } } for (codesI = 0; codesI < codeSql1.size(); codesI++) { if (doCode[codesI]) { psCodes[codesI].setInt(2, lastID); try { psCodes[codesI].execute(); numSuccess++; } catch (SQLException e1) { numFailed++; System.err.println(psCodes[codesI]); } } } for (Map.Entry<MyTable, PreparedStatement> entry : psForeign .entrySet()) { MyTable myT1 = entry.getKey(); MyTable[] foreignTs = myT.getForeignFields(); for (int ii = 0; ii < foreignTs.length; ii++) { if (foreignTs[ii] != null && foreignTs[ii].equals(myT1)) { if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT int lID = DBKernel .getLastInsertedID(psForeign.get(myT1)); // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt DBKernel.sendRequest("UPDATE " + DBKernel.delimitL(tableName) + " SET " + DBKernel.delimitL( myT.getFieldNames()[ii]) + "=" + lID + " WHERE " + DBKernel.delimitL("ID") + "=" + lastID, false); } break; } } } /* * for (int j=0;j<numCols;j++) { * if (myForeignTables[j] != * null && ffieldTypes[j] != * null) { MyTable[] foreignTs = * myT.getForeignFields(); for * (int * ii=0;ii<foreignTs.length; * ii++) { if (foreignTs[ii] != * null && foreignTs[ii].equals( * myForeignTables[j])) { if * (psForeign * .get(myForeignTables * [j]).executeUpdate() > 0) { * // INSERT int lID = * DBKernel.getLastInsertedID * (psForeign * .get(myForeignTables[j])); * DBKernel * .sendRequest("UPDATE " + * DBKernel.delimitL(tableName) * + " SET " + * DBKernel.delimitL( * myT.getFieldNames()[ii]) + * "=" + lID + " WHERE " + * DBKernel.delimitL("ID") + "=" * + lastID, false); } break; } * } } } */ } } catch (Exception e1) { numFailed++; MyLogger.handleMessage(ps.toString()); MyLogger.handleException(e1); } } if (progress != null) { lfd++; progress.setValue(lfd); } } } myT.doMNs(); if (progress != null) { // Refreshen: MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable(); if (myDB.getActualTable() != null) { String actTablename = myDB.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myDB.setTable(myDB.getActualTable()); } } MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree(); if (myTR.getActualTable() != null) { String actTablename = myTR.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myTR.setTable(myTR.getActualTable()); } } } } else { System.err.println(tableName + " nicht in DB???"); } } if (progress != null) { progress.setVisible(false); } String log = numSuccess + " erfolgreiche Importe.\n"; log += numFailed + " fehlgeschlagene Importe.\n"; if (unusedFields.length() > 0) log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n"; if (showResults) { InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null); ib.setVisible(true); } else { System.out.println("GeneralXLSImporter (" + filename + "):\n" + log); } } catch (Exception e) { MyLogger.handleException(e); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return true; }
From source file:de.cenote.jasperstarter.ReportNGTest.java
License:Apache License
/** * Test of exportXls method, of class Report. *//*from w w w . j a v a 2 s. co m*/ @Test(dependsOnMethods = { "testFill" }) public void testExportXls() throws Exception { System.out.println("exportXls"); Config config = null; config = new Config(); config.input = "target/test-classes/reports/compileToFile.jrprint"; Report instance = new Report(config, new File(config.getInput())); instance.exportXls(); assertEquals(((File) new File("target/test-classes/reports/compileToFile.xls")).exists(), true); // Read the content of a cell: InputStream inputStream = new FileInputStream("target/test-classes/reports/compileToFile.xls"); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); // first sheet // select cell C12 HSSFRow row = sheet.getRow(11); HSSFCell cell = row.getCell(2); assertEquals(cell.getStringCellValue(), "Carl Grant"); }
From source file:de.cenote.jasperstarter.ReportNGTest.java
License:Apache License
/** * Test of exportXlsMeta method, of class Report. *///from w w w. j a va 2 s . c om @Test(dependsOnMethods = { "testFillMeta" }) public void testExportXlsMeta() throws Exception { System.out.println("exportXlsMeta"); Config config = null; config = new Config(); config.input = "target/test-classes/reports/csvMeta.jrprint"; Report instance = new Report(config, new File(config.getInput())); instance.exportXlsMeta(); assertEquals(((File) new File("target/test-classes/reports/csvMeta.xls")).exists(), true); // Read the content of a cell: InputStream inputStream = new FileInputStream("target/test-classes/reports/csvMeta.xls"); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); // first sheet // select cell C12 HSSFRow row = sheet.getRow(11); HSSFCell cell = row.getCell(2); assertEquals(cell.getStringCellValue(), "Dampremy"); }
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLS file and parse it// w w w . jav a 2s . com * @param file The XLS File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSFile(File file) { int index = -1; HSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new HSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } HSSFSheet sheet = workbook.getSheetAt(index); HSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:de.ma.it.common.excel.ExcelFileManager.java
License:Open Source License
/** * //ww w . j a v a 2 s. co m * @param row * @param cellIdx * @return * @throws IllegalArgumentException */ public HSSFCell getCell(HSSFRow row, int cellIdx) throws IllegalArgumentException { HSSFCell cell = null; try { cell = row.getCell(cellIdx); } catch (NullPointerException ex) { // Occurs if cell isn't present, is deliberately swallowed -> method returns null } if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) { throw new IllegalArgumentException("Value not valid because cell has an error!"); } return cell; }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private boolean readSheet(MBBundle bundle) { if (sheet.getLastRowNum() == 0) return false; HSSFRow row = sheet.getRow(0); if (row.getLastCellNum() < 1 || row.getCell(1) == null) return false; bundle.setBaseName(getStringValue(row.getCell(1))); row = sheet.getRow(1);// ww w . j ava 2 s .c o m if (row != null) { if (row.getCell(1) != null) { bundle.setInterfaceName(getStringValue(row.getCell(1))); } if (row.getCell(3) != null) { bundle.setSqldomain(getStringValue(row.getCell(3))); } } int firstCol = 2; rowNum = 3; row = sheet.getRow(rowNum++); // read locales String aliasOrDescriptionHeader = getStringValue(row.getCell(1)); // backward compatibility boolean aliasColumnAvailable = false; if (aliasOrDescriptionHeader != null && "Aliases".equals(aliasOrDescriptionHeader.trim())) { firstCol++; aliasColumnAvailable = true; } int colNum = firstCol; List<String> locales = new ArrayList<String>(); HSSFCell cell = row.getCell(colNum++); while (colNum <= row.getLastCellNum()) { if (cell != null) { locales.add(getStringValue(cell)); } if (row.getLastCellNum() >= colNum) { cell = row.getCell(colNum++); } else { cell = null; } } row = sheet.getRow(rowNum++); while (row != null) { if (row.getCell(0) != null) { MBEntry entry = new MBEntry(); bundle.getEntries().add(entry); entry.setKey(getStringValue(row.getCell(0))); if (aliasColumnAvailable) { // backward compatibility String aliasesCommaSeparated = getStringValue(row.getCell(1)); if (aliasesCommaSeparated != null) { StringTokenizer tokens = new StringTokenizer(aliasesCommaSeparated, ", "); List<String> aliases = new ArrayList<String>(); while (tokens.hasMoreTokens()) { aliases.add(tokens.nextToken()); } entry.setAliases(aliases); } } if (row.getCell(firstCol - 1) != null) { entry.setDescription(getStringValue(row.getCell(firstCol - 1))); } colNum = firstCol; for (String each : locales) { cell = row.getCell(colNum++); if (cell != null) { final String svalue = getStringValue(cell); if (StringUtils.isNotEmpty(svalue) || // detect STYLE_MISSING cell.getCellStyle() .getFillBackgroundColor() == HSSFColor.HSSFColorPredefined.BLUE_GREY .getIndex() || cell.getCellStyle() .getFillForegroundColor() == HSSFColor.HSSFColorPredefined.BLUE_GREY .getIndex() || cell.getCellStyle().getFont(wb).getColor() == Font.COLOR_RED) { MBText text = new MBText(); text.setLocale(each); text.setValue(svalue); text.setReview(cell.getCellStyle().getFont(wb).getColor() == Font.COLOR_RED); entry.getTexts().add(text); } } } } row = sheet.getRow(rowNum++); } return true; }
From source file:eafit.cdei.asignacion.input.ReadCurrentCourses.java
public List<Teacher> loadCurrentOffering() throws Exception { try {/*from w w w . ja v a2 s . c om*/ FileInputStream fileInputStream = new FileInputStream("current_classes.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("currentOffering"); boolean keepDoing = true; for (int i = 0; i < worksheet.getLastRowNum() + 1; i++) { HSSFRow row1 = worksheet.getRow(i); HSSFCell cellF1 = row1.getCell((short) 5); String f1Val = ((int) cellF1.getNumericCellValue()) + ""; HSSFCell cellW1 = row1.getCell((short) 22); String w1Val = cellW1.getStringCellValue(); HSSFCell cellX1 = row1.getCell((short) 23); String x1Val = cellX1.getStringCellValue(); HSSFCell cellY1 = row1.getCell((short) 24); String y1Val = cellY1.getStringCellValue(); HSSFCell cellAU1 = row1.getCell((short) 46); String au1Val = ((int) cellAU1.getNumericCellValue()) + ""; HSSFCell cellAW1 = row1.getCell((short) 48); String aw1Val = cellAW1.getStringCellValue(); if (f1Val == null || f1Val.length() == 0 || f1Val.equals("0")) { keepDoing = false; } Course c = new Course(); c.setHourStart(f1Val); c.setHourEnd(au1Val); c.setDowList(generateDaysList(aw1Val)); c.setNameCourse(x1Val); c.setCodeCourse(w1Val); c.setCodeCourse(y1Val); DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HHmm"); if (au1Val.length() == 3) { au1Val = "0" + au1Val; } if (f1Val.length() == 3) { f1Val = "0" + f1Val; } c.setHourEndLocal(LocalTime.parse(au1Val, dtf)); c.setHourStartLocal(LocalTime.parse(f1Val, dtf)); c.setDateStartLocal(LocalTime.parse(au1Val, dtf)); c.setDateEndLocal(LocalTime.parse(aw1Val, dtf)); System.out.println(c); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:eafit.cdei.asignacion.input.ReadCurrentOffering.java
public List<Teacher> loadCurrentOffering() throws Exception { try {// w w w .j a v a2 s.co m FileInputStream fileInputStream = new FileInputStream("currentOffering.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("currentOffering"); boolean keepDoing = true; for (int i = 0; i < worksheet.getLastRowNum() + 1; i++) { HSSFRow row1 = worksheet.getRow(i); HSSFCell cellF1 = row1.getCell((short) 5); String f1Val = ((int) cellF1.getNumericCellValue()) + ""; HSSFCell cellW1 = row1.getCell((short) 22); String w1Val = cellW1.getStringCellValue(); HSSFCell cellX1 = row1.getCell((short) 23); String x1Val = cellX1.getStringCellValue(); HSSFCell cellY1 = row1.getCell((short) 24); String y1Val = cellY1.getStringCellValue(); HSSFCell cellAU1 = row1.getCell((short) 46); String au1Val = ((int) cellAU1.getNumericCellValue()) + ""; HSSFCell cellAW1 = row1.getCell((short) 48); String aw1Val = cellAW1.getStringCellValue(); HSSFCell cellAB = row1.getCell((short) 27); String abVal = cellAB.getStringCellValue(); HSSFCell cellAC = row1.getCell((short) 28); String acVal = cellAC.getStringCellValue(); if (f1Val == null || f1Val.length() == 0 || f1Val.equals("0")) { keepDoing = false; } Course c = new Course(); c.setHourStart(f1Val); c.setHourEnd(au1Val); c.setDowList(generateDaysList(aw1Val)); c.setNameCourse(x1Val); c.setCodeCourse(w1Val); c.setCodeCourse(y1Val); DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HHmm"); if (au1Val.length() == 3) { au1Val = "0" + au1Val; } if (f1Val.length() == 3) { f1Val = "0" + f1Val; } c.setHourEndLocal(LocalTime.parse(au1Val, dtf)); c.setHourStartLocal(LocalTime.parse(f1Val, dtf)); DateTimeFormatter dtf1 = DateTimeFormatter.ofPattern("yyyyMMdd"); //c.setDateStart(LocalDate.parse(abVal,dtf1)); //c.setHourStartLocal(LocalTime.parse(f1Val,dtf)); System.out.println(c); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:eafit.cdei.asignacion.input.ReadTeacherAvailability.java
@Override public List<Teacher> loadTeacherAvailability() throws Exception { try {//from w w w . jav a 2 s.c o m FileInputStream fileInputStream = new FileInputStream("googleDoc.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Form Responses 1"); boolean keepDoing = true; for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) { HSSFRow row1 = worksheet.getRow(i); HSSFCell cellB1 = row1.getCell((short) 1); String b1Val = cellB1.getStringCellValue() + ""; HSSFCell cellG1 = row1.getCell((short) 6); String g1Val = cellG1.getStringCellValue() + ""; HSSFCell cellH1 = row1.getCell((short) 7); String h1Val = cellH1.getStringCellValue() + ""; HSSFCell cellJ1 = row1.getCell((short) 8); String j1Val = cellJ1.getStringCellValue() + ""; HSSFCell cellK1 = row1.getCell((short) 9); String k1Val = cellK1.getStringCellValue() + ""; HSSFCell cellI1 = row1.getCell((short) 10); String i1Val = cellI1.getStringCellValue() + ""; HSSFCell cellL1 = row1.getCell((short) 11); String l1Val = cellL1.getStringCellValue() + ""; HSSFCell cellM1 = row1.getCell((short) 12); String m1Val = cellM1.getStringCellValue() + ""; HSSFCell cellN1 = row1.getCell((short) 13); String n1Val = cellN1.getStringCellValue() + ""; HSSFCell cellO1 = row1.getCell((short) 14); String o1Val = cellO1.getStringCellValue() + ""; HSSFCell cellP1 = row1.getCell((short) 15); String p1Val = cellP1.getStringCellValue() + ""; HSSFCell cellQ1 = row1.getCell((short) 16); String q1Val = cellQ1.getStringCellValue() + ""; HSSFCell cellR1 = row1.getCell((short) 17); String r1Val = cellR1.getStringCellValue() + ""; HSSFCell cellS1 = row1.getCell((short) 18); String s1Val = cellS1.getStringCellValue() + ""; HSSFCell cellT1 = row1.getCell((short) 19); String t1Val = cellT1.getStringCellValue() + ""; HSSFCell cellU1 = row1.getCell((short) 20); String u1Val = cellU1.getStringCellValue() + ""; HSSFCell cellV1 = row1.getCell((short) 21); String v1Val = cellV1.getStringCellValue() + ""; HSSFCell cellW1 = row1.getCell((short) 22); String w1Val = cellW1.getStringCellValue() + ""; HSSFCell cellX1 = row1.getCell((short) 23); String x1Val = cellX1.getStringCellValue() + ""; HSSFCell cellY1 = row1.getCell((short) 24); String y1Val = cellY1.getStringCellValue() + ""; HSSFCell cellZ1 = row1.getCell((short) 25); String z1Val = cellZ1.getStringCellValue() + ""; HSSFCell cellAA1 = row1.getCell((short) 26); String aa1Val = cellAA1.getStringCellValue() + ""; HSSFCell cellAB1 = row1.getCell((short) 27); String ab1Val = cellAB1.getStringCellValue() + ""; if (b1Val.length() == 0) { keepDoing = false; } else { Teacher t = new Teacher(); t.setFullName(b1Val); t.addCourseAvaliability(generateMTF(getAnswer(g1Val), "06", "08")); t.addCourseAvaliability(generateMTF(getAnswer(h1Val), "08", "10")); t.addCourseAvaliability(generateMTF(getAnswer(i1Val), "10", "12")); t.addCourseAvaliability(generateMTF(getAnswer(j1Val), "12", "14")); t.addCourseAvaliability(generateMTF(getAnswer(k1Val), "14", "16")); t.addCourseAvaliability(generateMWF(getAnswer(l1Val), "06", "08")); t.addCourseAvaliability(generateMWF(getAnswer(n1Val), "08", "10")); t.addCourseAvaliability(generateMWF(getAnswer(m1Val), "10", "12")); t.addCourseAvaliability(generateMWF(getAnswer(o1Val), "12", "14")); t.addCourseAvaliability(generateMWF(getAnswer(p1Val), "14", "16")); t.addCourseAvaliability(generateMTT(getAnswer(q1Val), "18", "15", "20", "45")); t.addCourseAvaliability(generateMTW(getAnswer(r1Val), "18", "30", "20", "30")); t.addCourseAvaliability(generateTTT(getAnswer(s1Val), "10", "12")); t.addCourseAvaliability(generateTTT(getAnswer(t1Val), "12", "14")); t.addCourseAvaliability(generateTTT(getAnswer(u1Val), "14", "16")); t.addCourseAvaliability(generateTTT(getAnswer(v1Val), "18", "30", "20", "30")); t.addCourseAvaliability(generateTTT(getAnswer(w1Val), "06", "09")); t.addCourseAvaliability(generateTTT(getAnswer(x1Val), "09", "12")); t.addCourseAvaliability(generateWF(getAnswer(y1Val), "06", "09")); t.addCourseAvaliability(generateWF(getAnswer(z1Val), "07", "00", "08", "30")); t.addCourseAvaliability(generateTTT(getAnswer(aa1Val), "12", "00", "13", "30")); t.addCourseAvaliability(generateTTT(getAnswer(ab1Val), "07", "00", "08", "30")); System.out.println(t); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:eafit.cdei.asignacion.input.ReadTeacherPreferences.java
public List<Teacher> loadTeacherPreferences() throws Exception { try {// w w w . ja v a2 s . c o m FileInputStream fileInputStream = new FileInputStream("TeacherPreferences.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Preferences"); boolean keepDoing = true; for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) { HSSFRow row1 = worksheet.getRow(i); HSSFCell cellA1 = row1.getCell((short) 0); String a1Val = cellA1.getNumericCellValue() + ""; HSSFCell cellB1 = row1.getCell((short) 1); String b1Val = cellB1.getStringCellValue(); HSSFCell cellC1 = row1.getCell((short) 2); String c1Val = cellC1.getStringCellValue(); String d1Val = ""; HSSFCell cellD1 = row1.getCell((short) 3); if (cellD1 != null) { d1Val = cellD1.getStringCellValue(); } HSSFCell cellE1 = row1.getCell((short) 4); double e1Val = cellE1.getNumericCellValue(); if (a1Val == null || a1Val.length() == 0 || a1Val.equals("0")) { keepDoing = false; } Teacher t = new Teacher(); t.setFullName(b1Val); t.setMaxCourses((int) e1Val); String[] preferedCourses = c1Val.split(","); ArrayList<LevelCourse> preferedCoursed = new ArrayList<LevelCourse>(); for (String preferedCoursed1 : preferedCourses) { LevelCourse lc = new LevelCourse(); lc.setLevelName(preferedCoursed1); preferedCoursed.add(lc); } String[] preferedTimes = d1Val.split(","); ArrayList<String> preferedTime = new ArrayList<>(); for (String pf : preferedTimes) { preferedTime.add(pf); } t.setListPreferedCourses(preferedCoursed); System.out.println(t); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }