List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:Controller.ThreadExcelImport.java
@Override public void run() { //******/* w w w . j a v a2s . co m*/ // CRIA STREAM DAS PLANILHAS // ******************* // stream planilha 1 InputStream stream1 = null; try { stream1 = new FileInputStream(new File(srcFileP1)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook1 = 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) .open(stream1); // stream planilha 2 InputStream stream2 = null; try { stream2 = new FileInputStream(new File(srcFileP2)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook2 = 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) .open(stream2); //****** // VERIFICA OS CABECALHOS // ******************* // cabealhos da planilha 1 Sheet sheet1 = null; sheet1 = workbook1.getSheetAt(0); // Pega de acordo com o cabealho as opes for (Row r : sheet1) { if (r.getRowNum() > 0) break; for (Integer i = 0; i < headerP1.size(); i++) { for (Cell c : r) { if (c.getStringCellValue().toLowerCase() .equals(headerP1.get(i).getColumnName().toLowerCase())) { // Adiciona o numero da coluna ao header headerP1.get(i).setColumnNumber(c.getColumnIndex()); break; } } if (headerP1.get(i).getColumnNumber() == null) { // Alguma coluna do template est ausente JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase() + " do template no existe como cabealho na planilha 1"); System.exit(0); } } } // cabealhos da planilha 2 Sheet sheet2 = null; sheet2 = workbook2.getSheetAt(0); // Pega de acordo com o cabealho as opes for (Row r : sheet2) { if (r.getRowNum() > 0) break; for (Integer i = 0; i < headerP2.size(); i++) { for (Cell c : r) { if (c.getStringCellValue().toLowerCase() .equals(headerP2.get(i).getColumnName().toLowerCase())) { // Adiciona o numero da coluna ao header headerP2.get(i).setColumnNumber(c.getColumnIndex()); break; } } if (headerP2.get(i).getColumnNumber() == null) { // Alguma coluna do template est ausente JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase() + " do template no existe como cabealho na planilha 2"); System.exit(0); } } } //****** // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1 // ******************* stream2 = null; try { stream2 = new FileInputStream(new File(srcFileP2)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } workbook2 = 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) .open(stream2); sheet2 = null; sheet2 = workbook2.getSheetAt(0); for (Row rowP2 : sheet2) { if (rowP2.getRowNum() > 0) { InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName); // calcula o hash String hashChaveP2 = ""; for (String chaveP2 : colunaChave) { Integer columIndex = -1; for (Header he2 : headerP2) { if (he2.getColumnName().equals(chaveP2)) { columIndex = he2.getColumnNumber(); break; } } if (columIndex > -1) { Cell cell = null; cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK); // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 ); hashChaveP2 = DigestUtils .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2); } } for (Header he2 : headerP2) { Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK); objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase()); objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1)); //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() ); } if (hashChaveP2.equals("")) { JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1)) + " da planilha 2 tem as colunas chaves nula"); System.exit(0); } else listaP2.put(hashChaveP2, objInterfaceP2); } } // limpa da memoria a workbook2 try { if (workbook2 != null) workbook2.close(); } catch (IOException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } // limpa da memoria o stream com workbook2 if (stream2 != null) try { stream2.close(); } catch (IOException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } //****** // FAZ A VALIDAO // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING // ******************* // Executa o loop nas linhas da planilha stream1 = null; try { stream1 = new FileInputStream(new File(srcFileP1)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } workbook1 = 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) .open(stream1); sheet1 = null; sheet1 = workbook1.getSheetAt(0); InterfaceMigracao objInterfaceP1 = null; for (Row rowP1 : sheet1) { // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1 String hashChaveP1 = ""; for (String chaveP1 : colunaChave) { Integer columIndex = -1; for (Header he1 : headerP1) { if (he1.getColumnName().equals(chaveP1)) { columIndex = he1.getColumnNumber(); break; } } if (columIndex > -1) { Cell cell = null; cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK); // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 ); hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1); } } objInterfaceP1 = Factory.getInstance(templateName); // objInterfaceP2 = Factory.getInstance(templateName); objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1)); Notify notify = new Notify(); if (hashChaveP1.equals("")) notify.setLocalizadoP1(false); else { notify.setLocalizadoP1(true); //seta o numero da linha no excel // Preenche o objeto de interface da planilha 1 com seus respectivos dados for (Header he1 : headerP1) { Cell cell = null; cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK); objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase()); } boolean p2Localizado = false; // Preenche o objeto de interface da planilha 2 com seus respectivos dados if (rowP1.getRowNum() > 0) { InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1); if (objInterfaceMigracaoP2 != null) { p2Localizado = true; notify.setEntidadeP2(objInterfaceMigracaoP2); } } notify.setLocalizadoP2(p2Localizado); } isRunning = true; objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1)); notify.setEntidadeP1(objInterfaceP1); notify.setTotalRow((sheet1.getLastRowNum() + 1)); notify.setRunning(isRunning); notify.setHeaderP1(headerP1); notify.setHeaderP2(headerP2); setChanged(); notifyObservers(notify); } isRunning = false; // Notifica os observadores de que a execuo terminou Notify notify = new Notify(); notify.setRunning(false); setChanged(); notifyObservers(notify); listaP2 = null; }
From source file:Controller.ThreadExcelImport.java
private String getStringFromCell(Cell cell) { int tipo = cell.getCellType(); String value = ""; switch (tipo) { //NUMERIC// w w w . j a v a 2 s . co 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 {//w w w . j a v a 2 s .co 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 {/*w ww . ja v a2 s . 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 {/* www . j av a 2 s . 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"); } } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void configure(File... file) { //need to popup a secondary dialog; this must be done differently try {/*from ww w . j av a 2 s. co m*/ 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; } //also need to get the top 100 rows + all columns 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 iterating all sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); //int returnVal = JOptionPane.showMessageDialog(CoolMapMaster.getCMainFrame(), configPanel); 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]; importOntology = configPanel.getImportOntology(); rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { CMConsole.logError(" failed to import numeric matrix data from: " + file); e.printStackTrace(); } }
From source file:coverageqc.data.DoNotCall.java
public static DoNotCall populate(Row xslxHeadingRow, Row xslxDataRow, Integer calltype) { DoNotCall donotcall = new DoNotCall(); int columnNumber; int cellIndex; String[] headerArray;//w w w .j ava 2 s . co m HashMap<String, Integer> headings = new HashMap<String, Integer>(); columnNumber = xslxHeadingRow.getLastCellNum(); headerArray = new String[columnNumber]; Iterator<Cell> cellIterator = xslxHeadingRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cellIndex = cell.getColumnIndex(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: headerArray[cellIndex] = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: headerArray[cellIndex] = Double.toString(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: headerArray[cellIndex] = cell.getStringCellValue(); break; default: headerArray[cellIndex] = ""; } } //end while celliterator for (int x = 0; x < headerArray.length; x++) { headings.put(headerArray[x].substring(0, headerArray[x].indexOf("_")), x); } //String[] dataArray = xslxDataLine.split("\t"); if (xslxDataRow.getCell(headings.get("HGVSc")) != null) { donotcall.hgvsc = xslxDataRow.getCell(headings.get("HGVSc").intValue()).getStringCellValue(); } //donotcall.hgvsp = xslxDataRow.getCell(headings.get("HGVSp").intValue()).getStringCellValue(); if (xslxDataRow.getCell(headings.get("ENSP")) != null) { donotcall.ensp = xslxDataRow.getCell(headings.get("ENSP").intValue()).getStringCellValue(); } if (xslxDataRow.getCell(headings.get("Transcript")) != null) { donotcall.transcript = xslxDataRow.getCell(headings.get("Transcript").intValue()).getStringCellValue(); } else { System.out.println( "Transcript_27 column entry is negative! This is essential to do not call! Do not call list needs to be fixed! Crashing to prevent abnormal behavior!"); System.exit(1); } donotcall.coordinate = (long) xslxDataRow.getCell(headings.get("Coordinate").intValue()) .getNumericCellValue(); // CallType is the page of the xlsx : // 1 => Always_Not_Real // 2 => Not_Real_When_Percentage_Low // 3 => Undetermined_Importance if (calltype == 1) { donotcall.callType = "Don't call, always"; } else if (calltype == 2) { donotcall.callType = "If percentage low, don't call"; } else { donotcall.callType = "On lab list, Unknown significance"; } return donotcall; }
From source file:crygetter.gui.MainWindow.java
/** * Loads Cry Data (order affect)/*from ww w. j a v a 2 s .c o m*/ */ private void loadCryData() { try { Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/cryData.xlsx")); Sheet sheet = wb.getSheetAt(0); Row orderNameRow = sheet.getRow(1); cryOrderData = new LinkedHashMap<>(); for (int i = 2;; i++) { Cell orderNameCell = orderNameRow.getCell(i); if (orderNameCell != null) { List<String> affectList = new ArrayList<>(); for (int j = 2;; j++) { Row orderValueRow = sheet.getRow(j); if (orderValueRow != null) { Cell cryValueCell = orderValueRow.getCell(1); Cell orderValueCell = orderValueRow.getCell(i); if (orderValueCell != null && !orderValueCell.getStringCellValue().equals("")) { String value = orderValueCell.getStringCellValue(); if (value.equals("A") || value.equals("P")) { affectList.add("Cry" + cryValueCell.getStringCellValue()); } } } else { break; } } if (!affectList.isEmpty()) { cryOrderData.put(orderNameCell.getStringCellValue(), affectList); } } else { break; } } } catch (IOException | InvalidFormatException exc) { Utils.showExceptionMessage(this, exc); } }
From source file:csv.impl.ExcelReader.java
License:Open Source License
/** * Returns the value of the specified cell. * If the cell contained//from ww w. jav a2 s .c om * a formula, the formula is evaluated before returning the row. * @param cell cell object * @return value of cell */ public Object getValue(Cell cell) { if (cell == null) return null; int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA && !isEvaluateFormulas()) { cellType = cell.getCachedFormulaResultType(); } switch (cellType) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: return evaluateCellValue(cell); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellValue(); } return null; }
From source file:Dao.XlsBillDao.java
public ArrayList<WorkItemBean> ReadXLS(File f) { WorkDao wdao = new WorkDao(); FileInputStream fis = null;/*ww w. ja va 2 s.c o m*/ ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>(); try { String pono = null; XSSFRow row = null; //fis = new FileInputStream(new File("D:\\WO-2015-2008.xlsx")); fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); int i = 0; while (rowIterator.hasNext()) { i++; row = (XSSFRow) rowIterator.next(); if (i == 1) { Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { pono = cell.getStringCellValue(); pono = pono.substring((pono.indexOf(":") + 1)); } } } if (i > 2) { WorkItemBean bean = new WorkItemBean(); // System.out.println("ROW" + i); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= 0) { //System.out.print("COLUMN"); if (cell.getColumnIndex() == 1) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setITEM_ID(String.valueOf(cell.getNumericCellValue())); } else { bean.setITEM_ID(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 2) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setITEM_DESC(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 3) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setUOM(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setQTY((float) cell.getNumericCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setQTY(Float.parseFloat(cell.getStringCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 5) { //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setRATE((float) cell.getNumericCellValue()); } else { bean.setRATE(Float.parseFloat(cell.getStringCellValue())); } } else if (cell.getColumnIndex() == 7) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPLANT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPLANT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 8) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCC(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCC(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 9) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPROJ(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPROJ(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setPROJ("-"); } } else if (cell.getColumnIndex() == 10) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setTASK(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setTASK(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setTASK("-"); } } else if (cell.getColumnIndex() == 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCMT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCMT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { } } } //System.out.println(); itm.add(bean); } } Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "READ DONE !!"); fis.close(); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } finally { try { fis.close(); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } } for (WorkItemBean i : itm) { if (i.getQTY() != 0) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.INFO, "ITEM_ID : {0} QTY:{1} PLANT:{2} CC:{3}", new Object[] { i.getITEM_ID(), i.getQTY(), i.getPLANT(), i.getCC() }); } } return itm; }