List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:comparararchivos.CompararArchivos.java
/** * @param args the command line arguments *//*from w w w.j a v a 2s. c o m*/ public static void main(String[] args) { // TODO code application logic here File excel1 = null; FileInputStream fl1 = null; XSSFWorkbook book1 = null; File excel2 = null; FileInputStream fl2 = null; XSSFWorkbook book2 = null; try { excel1 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorB.xlsx"); excel2 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorD.xlsx"); PrintWriter file = new PrintWriter("diferencias.txt", "UTF-8"); fl1 = new FileInputStream(excel1); fl2 = new FileInputStream(excel2); book1 = new XSSFWorkbook(fl1); book2 = new XSSFWorkbook(fl2); XSSFSheet sheet_A = book1.getSheetAt(0); XSSFSheet sheet_B = book2.getSheetAt(0); Iterator<Row> itrA = sheet_A.iterator(); Iterator<Row> itrB = sheet_B.iterator(); int totalDiferencias = 0; int numFila = 2; while (itrA.hasNext() && itrB.hasNext()) { Row rowA = itrA.next(); Row rowB = itrB.next(); if (rowA.getRowNum() == 0) continue; Iterator<Cell> cellitA = rowA.cellIterator(); Iterator<Cell> cellitB = rowB.cellIterator(); Cell celA = cellitA.next(); Cell celB = cellitB.next(); //Se esta en las celdas del numero de Aviso celA = cellitA.next(); celB = cellitB.next(); int numAvisoA = (int) celA.getNumericCellValue(); int numAvisoB = (int) celB.getNumericCellValue(); if (numAvisoA != numAvisoB) { System.out.println("Numero de Aviso: " + numAvisoA); continue; } //Se esta en las celdas de la categoria celA = cellitA.next(); celB = cellitB.next(); String textA = celA.getStringCellValue(); //System.out.println("Categoria A: "+textA); String textB = celB.getStringCellValue(); //System.out.println("Categoria B: "+textB); if (!textA.equals(textB)) { System.out.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); file.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); totalDiferencias++; } numFila++; } System.out.println("\nTotal diferencias: " + totalDiferencias); file.println("\nTotal diferencias: " + totalDiferencias); file.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException ie) { ie.printStackTrace(); } }
From source file:connect.Bootstrap.java
public static boolean bootstrap() { Connection conn = DatabaseConnectionManager.connect(); try {//from w ww . j a va 2s.c o m int noOfLines = 0; ArrayList<Data> list = new ArrayList<>(); Set<Customer> clist = new HashSet<>(); Set<Outlet> olist = new HashSet<>(); InputStream is = new FileInputStream(new File("./excel/SMUX - Outlet Data V1.xlsx")); StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .sheetIndex(0) // index of sheet to use (defaults to 0) .read(is); // InputStream or File for XLSX file (required) int counter = 0; for (Row r : reader) { counter++; //initialize a data object Data data = new Data(0, 0, "NULL", 0, "NULL", "", "Outlet", 0, 0, "", "", 0, 0, 0); if (noOfLines > 0) { // For each row, iterate through each columns Iterator<Cell> cellIterator = r.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: //customer id int custid = (int) cell.getNumericCellValue(); data.setCustomerId(custid); break; case 1: //age try { int age = (int) cell.getNumericCellValue(); data.setAge(age); } catch (Exception e) { //leave it as 0 } break; case 2: //gender String gender = cell.getStringCellValue(); data.setGender(gender); //System.out.println("case 2"); break; case 3: //transact id data.setTransactId((int) cell.getNumericCellValue()); break; case 4: //transact date data.setTransactDate(df.format(cell.getDateCellValue())); break; case 5: //transact time time.setTime(cell.getDateCellValue()); data.setTransactTime(df2.format(time.getTime())); break; case 6: //outlet data.setOutlet(cell.getStringCellValue()); break; case 7: //outlet district data.setOutletDistrict((int) cell.getNumericCellValue()); break; case 8: //transact details id data.setTransactDetailsId((int) cell.getNumericCellValue()); break; case 9: //item //try { data.setItem(cell.getStringCellValue()); break; case 10: //item description data.setItemDesc(cell.getStringCellValue()); break; case 11: //quantity data.setQuantity((int) cell.getNumericCellValue()); break; case 12: //price data.setPrice(cell.getNumericCellValue()); break; case 13: //spending double spending = cell.getNumericCellValue(); data.setSpending(spending); break; default: } } list.add(data); } noOfLines++; if (noOfLines == 32740) { //establish connection, sql, execute sql try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeBatch(); pstmt.close(); conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } noOfLines = 1; //System.out.println("batch submitted"); } else if (counter > 556560 && counter <= 556580) { try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); //pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeUpdate(); //conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } } } } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return true; }
From source file:controller.DAOData.java
public Plan readPlan() { ArrayList<Course> courses = new ArrayList<>(); //takes the sheet you ask for XSSFSheet sheet = workbook.getSheet("PLAN"); //for to go over the sheet info - like the rows for (Row row : sheet) { String code = null;/*from www .j a va 2s . c o m*/ String name = null; double credits = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: name = cell.getStringCellValue(); break; case 1: code = cell.getStringCellValue(); break; case 2: credits = cell.getNumericCellValue(); break; } } } if (code != null) { Course course = new Course(code, name, credits); courses.add(course); code = null; name = null; credits = 0; } } Plan plan = new Plan(410, 2010, courses); return plan; }
From source file:controller.DAOData.java
public ArrayList<Object> readGroups() { ArrayList<Object> groups = new ArrayList(); XSSFSheet sheet = workbook.getSheet("OFERTA"); //for to go over the sheet info - like the rows for (Row row : sheet) { String period = null;/*from w ww . java 2s . c o m*/ Course course = null; int numberGroup = 0; Employee employee = null; ArrayList<Schedule> schedules = new ArrayList(); String scheduleS = null; String classroom = null; for (Cell cell : row) { if (row.getRowNum() != 0) { Schedule schedule = new Schedule(); switch (cell.getColumnIndex()) { case 0: period = cell.getStringCellValue(); break; case 1: course = School.getInstance().selectCourse(cell.getStringCellValue()); break; case 2: numberGroup = (int) cell.getNumericCellValue(); break; case 3: employee = School.getInstance().findEmployee(cell.getStringCellValue()); break; case 4: scheduleS = cell.getStringCellValue(); break; case 5: classroom = cell.getStringCellValue(); break; } } } if (period != null) { schedules = identifySchedules(scheduleS, classroom); groups.add(new Group(numberGroup, period, true, employee, schedules, course)); numberGroup = 0; course = null; period = null; employee = null; schedules = null; } } return groups; }
From source file:controller.DAORequest.java
public ArrayList<Object> readRequests() { ArrayList<Object> requests = new ArrayList(); ArrayList<Resolution> resolutions = readResolutions(); XSSFSheet sheet = workbook.getSheetAt(0); for (Row row : sheet) { System.out.println("1"); Date date = null;/*from w ww. java 2 s .c o m*/ Student affected; String carnet = null; String name = null; String email = null; String celStu = null; Group group; String period = null; String course = null; int numberGroup = 0; EInconsistencie einconsistencie; String inconsistencie = null; String description = null; Person requester; String idReq = null; String nameReq = null; ERequestState reqState; String sreqState = null; int numRes = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: date = row.getCell(0).getDateCellValue(); break; case 1: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) carnet = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) carnet = cell.getStringCellValue(); break; case 2: //es el nombre del estudiante name = cell.getStringCellValue(); break; case 3: email = cell.getStringCellValue(); break; case 4: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) celStu = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) celStu = cell.getStringCellValue(); break; case 5: period = cell.getStringCellValue(); break; case 6: course = cell.getStringCellValue(); break; case 7: numberGroup = (int) cell.getNumericCellValue(); break; case 8: inconsistencie = cell.getStringCellValue(); break; case 9: description = cell.getStringCellValue(); break; case 10: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) idReq = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) idReq = cell.getStringCellValue(); break; case 11: nameReq = cell.getStringCellValue(); break; case 12: sreqState = cell.getStringCellValue(); break; case 13: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) numRes = (int) cell.getNumericCellValue(); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) numRes = Integer.parseInt(cell.getStringCellValue()); break; } } } if (carnet != null) { affected = new Student(carnet, name, email, celStu); requester = new Person(idReq, nameReq, null, null); group = School.getInstance().selectGroup(period, numberGroup, course); einconsistencie = identifyEInconsistencie(inconsistencie); if (sreqState == null) { sreqState = "PENDIENTE"; } reqState = identifyEReqState(sreqState); Request request = new Request(date, description, einconsistencie, reqState, affected, requester, group); request.setRequestState(reqState); requests.add(request); if (numRes != 0) { for (Resolution r : resolutions) { if (r.getId() == numRes) request.setResolution(r); System.out.println("linquea numRes: " + numRes + " con ReqId:" + request.getId()); } } } } return requests; }
From source file:controller.DAORequest.java
private ArrayList<Resolution> readResolutions() { ArrayList<Resolution> resolutions = new ArrayList(); try {//from w w w .j a va 2 s .com FileInputStream fis = new FileInputStream(new File("src//files//DatosResolucion.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); for (Row row : sheet) { int id = 0; String attention = null; String title = null; String intro = null; String result = null; String resolve = null; String notify = null; String considerations = null; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: id = (int) cell.getNumericCellValue(); break; case 1: attention = cell.getStringCellValue(); break; case 2: title = cell.getStringCellValue(); break; case 3: intro = cell.getStringCellValue(); break; case 4: result = cell.getStringCellValue(); break; case 5: resolve = cell.getStringCellValue(); break; case 6: notify = cell.getStringCellValue(); break; case 7: considerations = cell.getStringCellValue(); break; } } } if (id != 0) { System.out.println("Resolution: [id: " + id + " attention: " + attention + "\ntitle: " + title + " \nintro: " + intro + " \nresult: " + result + " \nresolve: " + resolve + " \nnotify: " + notify + " \nconsiderations: " + considerations + "\n]"); resolutions.add( new Resolution(id, attention, title, intro, result, resolve, notify, considerations)); } } } catch (FileNotFoundException e) { System.out.println("No hay archivo que cargar de Resolutions"); } catch (IOException ex) { Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex); } return resolutions; }
From source file:Controller.ThreadExcelImport.java
private String getStringFromCell(Cell cell) { int tipo = cell.getCellType(); String value = ""; switch (tipo) { //NUMERIC//www. j a va2 s . c o m case 0: DataFormatter df = new DataFormatter(); // value = df.formatCellValue(row.getCell(columnPos)); value = String.valueOf(cell.getNumericCellValue()).trim().toLowerCase(); //at.setString(columnName, value); break; //STRING case 1: value = cell.getStringCellValue().trim().toLowerCase(); //at.setString(columnName, value); break; // FORMULA // BLANK case 3: value = ""; //row.getCell(columnPos).getStringCellValue(); //at.setString(columnName, value); break; // BOOLEAN case 4: value = String.valueOf(cell.getBooleanCellValue()).trim().toLowerCase(); // at.setString(columnName, value); break; // NONE (ERROR) case 5: value = cell.getStringCellValue().trim().toLowerCase(); //at.setString(columnName, value); break; } return value; //return String.valueOf( cell.getNumericCellValue()); }
From source file:controller.UploadExcelStudentsFile.java
public String readFromExcel(String path, MyPerson p) { String messages = ""; String ColumnsMesages = ""; try {/*from w w w .j a va 2 s .c o m*/ FileInputStream file; file = new FileInputStream(new File(path)); //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); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); int rowCount = 0; boolean isEverythingIsOK = true; while (rowIterator.hasNext()) { Row row = rowIterator.next(); rowCount++; System.out.println("rcount:" + rowCount); if (rowCount > 1) { //For each row, iterate through all the columns int Code = 0; String Fname = "", Lname = "", Email = ""; String password = ""; int userType = 0; int level = 0; String gender = ""; Iterator<Cell> cellIterator = row.cellIterator(); int ColumnCount = 0; int rowCountMesages = rowCount - 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); ColumnCount++; //Check the cell type and format accordingly System.out.println("ccount:" + ColumnCount); /* switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue() + ""); System.out.println("numeric type case:" + Cell.CELL_TYPE_NUMERIC);// numeric type case:0 System.out.println("type:" + cell.getCellType());//type:0 break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); System.out.print("string type case:" + Cell.CELL_TYPE_STRING + "");//string type case:0 System.out.println("type:" + cell.getCellType());//type:1 break; }*/ if (ColumnCount == 1) { if (cell.getCellType() == 0) { Code = (int) cell.getNumericCellValue(); password = String.valueOf(Code); System.out.println("Code:" + Code); System.out.println("pass:" + password); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'>First Column is Code must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 2) { if (cell.getCellType() == 1) { Fname = cell.getStringCellValue(); System.out.println("Fname:" + Fname); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'>Column number 2 is Fname must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 3) { if (cell.getCellType() == 1) { Lname = cell.getStringCellValue(); System.out.println("Lname:" + Lname); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 3 is Lname must be a String please in student number (" + rowCountMesages + ")</font><br>"; } } /*else if(ColumnCount==4){ String Pass=cell.getStringCellValue(); System.out.println("Pass:"+Pass); }*/ else if (ColumnCount == 4) { if (cell.getCellType() == 1) { Email = cell.getStringCellValue(); System.out.println("Email:" + Email); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'>Column number 4 is Email must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 5) { if (cell.getCellType() == 1) { gender = cell.getStringCellValue(); System.out.println("gender:" + gender); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 5 is gender must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 6) { if (cell.getCellType() == 0) { userType = (int) cell.getNumericCellValue(); System.out.println("userType:" + userType); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'> Column number 6 is userType must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 7) { if (cell.getCellType() == 0) { level = (int) cell.getNumericCellValue(); System.out.println("level:" + level); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 7 is level must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } } //end of celIterator int rowAffected = 0; if (isEverythingIsOK) { try { SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss a"); String RegistrationDate = sdf1.format(new Date()); SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddhhmmss"); String MyUniversityCodeString = sdf2.format(new Date()) + p.getFaculityID() + Code; rowAffected = p.RegisterUser(Code, Fname, Lname, Email, password, userType, 1, gender, p.getFaculityID(), p.getUniversityID(), RegistrationDate, 1, MyUniversityCodeString); if (rowAffected > 0) { //response.sendRedirect("MyAccount.jsp?page=CourseViewPOSTContents&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+""); //response.sendRedirect("MyAccount.jsp?page=viewPost&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+""); messages += "<font color='blue'>Student(" + rowCountMesages + ")was Adding Successfully ^_^</font>" + "<br/>"; System.out.println( "<script type='text/javascript' > alert('Student was Adding Successfully ^_^ ');history.back();</script>"); } else { messages += "<font color='red'> Student(" + rowCountMesages + ")was Adding Failed ^_^</font>" + "<br/>"; System.out.println( "<script type='text/javascript' > alert('Student was Failed ^_^ ');history.back();</script>"); } } catch (Exception ex) { System.err.println("Add Students Error" + ex.getMessage()); messages += "<font color='red'>Adding Students Error" + ex.getMessage() + "</font><br/>"; messages += "<center><a href='index.jsp' >Home</a></center>"; } } //end of if IsEverythingIsOk Or Not System.out.println(""); } //end of if this is not first row } //end of while rowIterator file.close(); } //end of try catch (Exception e) { e.printStackTrace(); messages += "<font color='red'>" + e.getMessage() + "</font><br/>"; } messages += ColumnsMesages; return messages; }
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
@Override public void configure(File... file) { try {//from w w w. j a va 2s.c om File inFile = file[0]; String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetNames.length; i++) { String sheetName = workbook.getSheetAt(i).getSheetName(); sheetNames[i] = sheetName == null || sheetName.length() == 0 ? "Untitled" : sheetName; } DefaultTableModel tableModels[] = new DefaultTableModel[sheetCount]; Cell cell; Row row; ArrayList<ArrayList<ArrayList<Object>>> previewData = new ArrayList(); for (int si = 0; si < sheetCount; si++) { //The row iterator automatically skips the blank rows //so only need to figure out how many rows to skip; which is nice //columns, not the same though Sheet sheet = workbook.getSheetAt(si); Iterator<Row> rowIterator = sheet.rowIterator(); int ri = 0; ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); while (rowIterator.hasNext()) { row = rowIterator.next(); ArrayList<Object> rowData = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + ri + "," + j + "]"); rowData.add(null); } } data.add(rowData); ri++; if (ri == previewNum) { break; } } //end // System.out.println(data); // now the data is the data // ugh-> this is not a generic importer previewData.add(data); } //end of loop sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); int returnVal = JOptionPane.showConfirmDialog(CoolMapMaster.getCMainFrame(), configPanel, "Import from Excel: " + inFile.getAbsolutePath(), JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE, null); if (returnVal == JOptionPane.OK_OPTION) { proceed = true; inStream.close(); workbook = null; //set parameters inFile = file[0]; rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); formatIndex = configPanel.getFormatIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void importFromFile(File inFile) throws Exception { //Ignore the file, choose only a single file //I actually don't know the row count if (!proceed) { throw new Exception("Import from excel was cancelled"); } else {/*w ww. j av a 2s. c om*/ try { String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } Sheet sheet = workbook.getSheetAt(sheetIndex); int rowCounter = 0; //need to first copy the file over ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // if (rowCounter < rowStart) { // rowCounter++; // //import ontology rows // // continue; // // //skip first rows // } ArrayList<Object> rowData = new ArrayList<Object>(); for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); // System.out.print(cell + " "); // now add data try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + row + "," + i + "]"); rowData.add(null); } } // System.out.println(""); data.add(rowData); } //now I have row data int rowCount = data.size() - rowStart - 1; int columnCount = data.get(0).size() - columnStart - 1; DoubleCMatrix matrix = new DoubleCMatrix(Tools.removeFileExtension(inFile.getName()), rowCount, columnCount); String[] rowNames = new String[rowCount]; String[] columnNames = new String[columnCount]; for (int i = rowStart; i < data.size(); i++) { ArrayList row = data.get(i); if (i == rowStart) { //first row contains names for (int j = columnStart + 1; j < row.size(); j++) { try { columnNames[j - columnStart - 1] = row.get(j).toString(); } catch (Exception e) { columnNames[j - columnStart - 1] = "Untitled " + Tools.randomID(); } } continue; } for (int j = columnStart; j < row.size(); j++) { Object cell = row.get(j); if (j == columnStart) { try { rowNames[i - rowStart - 1] = cell.toString(); } catch (Exception e) { rowNames[i - rowStart - 1] = "Untitled" + Tools.randomID(); } } else { //set values try { Object value = (Double) row.get(j); if (value == null) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } else if (value instanceof Double) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, (Double) value); } else { matrix.setValue(i - rowStart - 1, j - columnStart - 1, Double.NaN); } } catch (Exception e) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } } } //end of iterating columns } //end of iterating rows // matrix.printMatrix(); // matrix.setRowLabels(rowNames); matrix.setColLabels(columnNames); CoolMapObject object = new CoolMapObject(); object.setName(Tools.removeFileExtension(inFile.getName())); object.addBaseCMatrix(matrix); ArrayList<VNode> nodes = new ArrayList<VNode>(); for (Object label : matrix.getRowLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertRowNodes(nodes); nodes.clear(); for (Object label : matrix.getColLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertColumnNodes(nodes); object.setAggregator(new DoubleDoubleMean()); object.setSnippetConverter(new DoubleSnippet1_3()); object.setViewRenderer(new NumberToColor(), true); object.getCoolMapView().addColumnMap(new ColumnLabels(object)); object.getCoolMapView().addColumnMap(new ColumnTree(object)); object.getCoolMapView().addRowMap(new RowLabels(object)); object.getCoolMapView().addRowMap(new RowTree(object)); importedCoolMaps.clear(); importedCoolMaps.add(object); //////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////// // //let's add COntologies if (columnStart > 0) { COntology columnOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " column ontology", null); ArrayList<Object> columnLabels = data.get(rowStart); //these are column labels for (int i = 0; i < rowStart; i++) { ArrayList ontologyColumn = data.get(i); for (int j = columnStart + 1; j < columnLabels.size(); j++) { Object parent = ontologyColumn.get(j); Object child = columnLabels.get(j); if (parent != null && child != null) { columnOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } //Also need to create presets } } columnOntology.validate(); // COntologyUtils.printOntology(columnOntology); importedOntologies.add(columnOntology); // need to finish the preset } if (rowStart > 0) { COntology rowOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " row ontology", null); List rowLabels = Arrays.asList(rowNames); for (int j = 0; j < columnStart; j++) { for (int i = rowStart + 1; i < data.size(); i++) { Object parent = data.get(i).get(j); Object child = rowLabels.get(i - rowStart - 1); if (parent != null && child != null) { rowOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } } } rowOntology.validate(); COntologyUtils.printOntology(rowOntology); importedOntologies.add(rowOntology); } // create row and column complex combinatorial ontology (intersections) } catch (Exception e) { // e.printStackTrace(); throw new Exception("File error"); } } }