List of usage examples for org.apache.poi.ss.usermodel Workbook getCreationHelper
CreationHelper getCreationHelper();
From source file:controller.FileUploadView.java
public void uploadEstudiantes(FileUploadEvent event) throws SQLException, ClassNotFoundException { ////from www .ja v a 2 s.c o m FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String APELLIDO_PATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); APELLIDO_PATERNO = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); APELLIDO_PATERNO = row.getCell(0).getStringCellValue(); break; } String APELLIDO_MATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); APELLIDO_MATERNO = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); APELLIDO_MATERNO = row.getCell(1).getStringCellValue(); break; } String NOMBRES = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); NOMBRES = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); NOMBRES = row.getCell(2).getStringCellValue(); break; } String CEDULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); CEDULA = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); CEDULA = row.getCell(3).getStringCellValue(); break; } String CARRERA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CARRERA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CARRERA = row.getCell(4).getStringCellValue(); break; } String CURSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); CURSO = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); CURSO = row.getCell(5).getStringCellValue(); break; } String CODIGOMATRICULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); CODIGOMATRICULA = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); CODIGOMATRICULA = row.getCell(6).getStringCellValue(); break; } String DIRECCION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); DIRECCION = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); DIRECCION = row.getCell(7).getStringCellValue(); break; } String TELEFONO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(8)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(8).getNumericCellValue() + " "); TELEFONO = Integer.toString((int) row.getCell(8).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(8).getStringCellValue() + " "); TELEFONO = row.getCell(8).getStringCellValue(); break; } String CELULAR = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(9)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(9).getNumericCellValue() + " "); CELULAR = Integer.toString((int) row.getCell(9).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(9).getStringCellValue() + " "); CELULAR = row.getCell(9).getStringCellValue(); break; } String CORREO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(10)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(10).getNumericCellValue() + " "); CORREO = Integer.toString((int) row.getCell(10).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(10).getStringCellValue() + " "); CORREO = row.getCell(10).getStringCellValue(); break; } String CORREOUCE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(11)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(11).getNumericCellValue() + " "); CORREOUCE = Integer.toString((int) row.getCell(11).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(11).getStringCellValue() + " "); CORREOUCE = row.getCell(11).getStringCellValue(); break; } String CORREOCONTACTO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(12)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(12).getNumericCellValue() + " "); CORREOCONTACTO = Integer.toString((int) row.getCell(12).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(12).getStringCellValue() + " "); CORREOCONTACTO = row.getCell(12).getStringCellValue(); break; } String ESTADOCIVIL = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(13)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(13).getNumericCellValue() + " "); ESTADOCIVIL = Integer.toString((int) row.getCell(13).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(13).getStringCellValue() + " "); ESTADOCIVIL = row.getCell(13).getStringCellValue(); break; } String PAIS = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(14)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(14).getNumericCellValue() + " "); PAIS = Integer.toString((int) row.getCell(14).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(14).getStringCellValue() + " "); PAIS = row.getCell(14).getStringCellValue(); break; } String PROVINCIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(15)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(15).getNumericCellValue() + " "); PROVINCIA = Integer.toString((int) row.getCell(15).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(15).getStringCellValue() + " "); PROVINCIA = row.getCell(15).getStringCellValue(); break; } String CANTON = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(16)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(16).getNumericCellValue() + " "); CANTON = Integer.toString((int) row.getCell(16).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(16).getStringCellValue() + " "); CANTON = row.getCell(16).getStringCellValue(); break; } String SEXO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(17)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(17).getNumericCellValue() + " "); SEXO = Integer.toString((int) row.getCell(17).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(17).getStringCellValue() + " "); SEXO = row.getCell(17).getStringCellValue(); break; } String NACIONALIDAD = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(18)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(18).getNumericCellValue() + " "); NACIONALIDAD = Integer.toString((int) row.getCell(18).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(18).getStringCellValue() + " "); NACIONALIDAD = row.getCell(18).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String sql = "INSERT INTO ESTUDIANTE (APELLIDO_PATERNO,APELLIDO_MATERNO,NOMBRES,CEDULA,CARRERA,CURSO,CODIGO_MATRICULA,DIRECCION,TELEFONO,CELULAR,CORREO,CORREO_UCE,CORREO_CONTACTO,ESTADO_CIVIL,PAIS,PROVINCIA,CANTON,SEXO,NACIONALIDAD) " + "VALUES('" + APELLIDO_PATERNO + "','" + APELLIDO_MATERNO + "','" + NOMBRES + "','" + CEDULA + "','" + CARRERA + "','" + CURSO + "','" + CODIGOMATRICULA + "','" + DIRECCION + "','" + TELEFONO + "','" + CELULAR + "','" + CORREO + "','" + CORREOUCE + "','" + CORREOCONTACTO + "','" + ESTADOCIVIL + "','" + PAIS + "','" + PROVINCIA + "','" + CANTON + "','" + SEXO + "','" + NACIONALIDAD + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }
From source file:controller.FileUploadView.java
public void uploadDocente(FileUploadEvent event) throws SQLException, ClassNotFoundException { //// ww w . j av a2s . c om FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); //readExcelFile(destination+event.getFile().getFileName()); String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String NOMBRE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); NOMBRE = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); NOMBRE = row.getCell(0).getStringCellValue(); break; } String CI = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); CI = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); CI = row.getCell(1).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String EMAIL_INST = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); EMAIL_INST = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); EMAIL_INST = row.getCell(2).getStringCellValue(); break; } String EMAIL_PERSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); EMAIL_PERSO = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); EMAIL_PERSO = row.getCell(3).getStringCellValue(); break; } String CATEGORIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CATEGORIA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CATEGORIA = row.getCell(4).getStringCellValue(); break; } String DEDICACION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); DEDICACION = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); DEDICACION = row.getCell(5).getStringCellValue(); break; } String ESTADO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); ESTADO = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); ESTADO = row.getCell(6).getStringCellValue(); break; } String TIEMPO_HORA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); TIEMPO_HORA = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); TIEMPO_HORA = row.getCell(7).getStringCellValue(); break; } String sql = "INSERT INTO DOCENTE (NOMBRE,CI,EMAIL_INSTITUCIONAL,EMAIL_PERSONAL,CATEGORIA,DEDICACION,ESTADO,TIEMPOHORA) VALUES('" + NOMBRE + "','" + CI + "','" + EMAIL_INST + "','" + EMAIL_PERSO + "','" + CATEGORIA + "','" + DEDICACION + "','" + ESTADO + "','" + TIEMPO_HORA + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }
From source file:Data.Database.java
public void dumpExcel() throws FileNotFoundException, IOException { //Workbook wb = new HSSFWorkbook(); Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("leaguedata"); // set headers on excel sheet Row row = sheet.createRow((short) 0); String headers[] = new String[] { "Team", "Player", "Position", "Age", "Yrs Played", "GP", "G", "A", "PTS", "+/-", "STP", "SOG", "SH%", "Hits", "Blocks", "TOI", "G/60", "A/60", "PTS/60", "STP/60", "SOG/60", "Hits/60", "Blocks/60" }; for (int i = 0; i < headers.length; i++) { Cell cell = row.createCell(i);/*from w ww. ja va 2 s .com*/ cell.setCellValue(createHelper.createRichTextString(headers[i])); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cell.setCellStyle(cellStyle); } // add player data int track = 2; // // dump ALL players!!!!! // for (Map.Entry<String, Player> entry : players.entrySet()){ // Row newrow = sheet.createRow((short)track); // entry.getValue().dumpExcel(newrow, "null"); // track++; // } // // // Write the output to a file // FileOutputStream fileOut = new FileOutputStream("RFHL_allplayers.xlsx"); // wb.write(fileOut); // fileOut.close(); // dump fantasy teams!!! for (int i = 0; i < fh_teams.size(); i++) { track = fh_teams.get(i).dumpExcel(sheet, track); track++; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("RFHL.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:de.alpharogroup.export.excel.poi.ExcelPoiFactory.java
License:Open Source License
/** * Creates a new CellStyle with the given date format. * * @param workbook//from ww w .j a v a 2s. com * the workbook * @param dateFormat * the date format * @return the cell style */ public static CellStyle newDateCellStyle(final Workbook workbook, final String dateFormat) { final CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat(dateFormat)); return dateCellStyle; }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public Map<String, KnimeTuple> getTimeSeriesTuples(File file, String sheet, Map<String, Object> columnMappings, String timeUnit, String concentrationUnit, String agentColumnName, Map<String, AgentXml> agentMappings, String matrixColumnName, Map<String, MatrixXml> matrixMappings, boolean preserveIds, List<Integer> usedIds) throws Exception { Workbook wb = getWorkbook(file); Sheet s = wb.getSheet(sheet);//from ww w.j a v a 2 s . c o m warnings.clear(); evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); } Map<String, KnimeTuple> tuples = new LinkedHashMap<>(); Map<String, Integer> columns = getColumns(s); Map<String, Integer> miscColumns = new LinkedHashMap<>(); Integer idColumn = null; Integer commentColumn = null; Integer timeColumn = null; Integer logcColumn = null; Integer stdDevColumn = null; Integer nMeasureColumn = null; Integer agentDetailsColumn = null; Integer matrixDetailsColumn = null; Integer agentColumn = null; Integer matrixColumn = null; String timeColumnName = null; String logcColumnName = null; String stdDevColumnName = null; String nMeasureColumnName = null; if (agentColumnName != null) { agentColumn = columns.get(agentColumnName); } if (matrixColumnName != null) { matrixColumn = columns.get(matrixColumnName); } for (String column : columns.keySet()) { if (columnMappings.containsKey(column)) { Object mapping = columnMappings.get(column); if (mapping instanceof MiscXml) { miscColumns.put(column, columns.get(column)); } else if (mapping.equals(ID_COLUMN)) { idColumn = columns.get(column); } else if (mapping.equals(MdInfoXml.ATT_COMMENT)) { commentColumn = columns.get(column); } else if (mapping.equals(AttributeUtilities.TIME)) { timeColumn = columns.get(column); timeColumnName = column; } else if (mapping.equals(AttributeUtilities.CONCENTRATION)) { logcColumn = columns.get(column); logcColumnName = column; } else if (mapping.equals(XLSReader.CONCENTRATION_STDDEV_COLUMN)) { stdDevColumn = columns.get(column); stdDevColumnName = column; } else if (mapping.equals(XLSReader.CONCENTRATION_MEASURE_NUMBER)) { nMeasureColumn = columns.get(column); nMeasureColumnName = column; } else if (mapping.equals(AttributeUtilities.AGENT_DETAILS)) { agentDetailsColumn = columns.get(column); } else if (mapping.equals(AttributeUtilities.MATRIX_DETAILS)) { matrixDetailsColumn = columns.get(column); } } } List<Integer> newIds = new ArrayList<>(); ListMultimap<String, Row> rowsById = LinkedListMultimap.create(); if (idColumn != null) { for (int i = 1; !isEndOfFile(s, i); i++) { Row row = s.getRow(i); Cell idCell = row.getCell(idColumn); if (hasData(idCell)) { rowsById.put(getData(idCell), row); } } } for (Map.Entry<String, List<Row>> entry : Multimaps.asMap(rowsById).entrySet()) { KnimeTuple tuple = new KnimeTuple(SchemaFactory.createDataSchema()); PmmXmlDoc timeSeriesXml = new PmmXmlDoc(); String idString = entry.getKey(); Row firstRow = entry.getValue().get(0); Cell commentCell = null; Cell agentDetailsCell = null; Cell matrixDetailsCell = null; Cell agentCell = null; Cell matrixCell = null; if (commentColumn != null) { commentCell = firstRow.getCell(commentColumn); } if (agentDetailsColumn != null) { agentDetailsCell = firstRow.getCell(agentDetailsColumn); } if (matrixDetailsColumn != null) { matrixDetailsCell = firstRow.getCell(matrixDetailsColumn); } if (agentColumn != null) { agentCell = firstRow.getCell(agentColumn); } if (matrixColumn != null) { matrixCell = firstRow.getCell(matrixColumn); } int id; if (preserveIds && !usedIds.isEmpty()) { id = usedIds.remove(0); } else { id = MathUtilities.getRandomNegativeInt(); } newIds.add(id); tuple = new KnimeTuple(SchemaFactory.createDataSchema()); tuple.setValue(TimeSeriesSchema.ATT_COMBASEID, idString); tuple.setValue(TimeSeriesSchema.ATT_CONDID, id); timeSeriesXml = new PmmXmlDoc(); PmmXmlDoc dataInfo = new PmmXmlDoc(); PmmXmlDoc agentXml = new PmmXmlDoc(); PmmXmlDoc matrixXml = new PmmXmlDoc(); if (commentCell != null) { dataInfo.add(new MdInfoXml(null, null, getData(commentCell), null, null)); } else { dataInfo.add(new MdInfoXml(null, null, null, null, null)); } if (hasData(agentCell) && agentMappings.get(getData(agentCell)) != null) { agentXml.add(agentMappings.get(getData(agentCell))); } else { agentXml.add(new AgentXml()); } if (hasData(matrixCell) && matrixMappings.get(getData(matrixCell)) != null) { matrixXml.add(matrixMappings.get(getData(matrixCell))); } else { matrixXml.add(new MatrixXml()); } if (hasData(agentDetailsCell)) { ((AgentXml) agentXml.get(0)).setDetail(getData(agentDetailsCell)); } if (hasData(matrixDetailsCell)) { ((MatrixXml) matrixXml.get(0)).setDetail(getData(matrixDetailsCell)); } tuple.setValue(TimeSeriesSchema.ATT_MDINFO, dataInfo); tuple.setValue(TimeSeriesSchema.ATT_AGENT, agentXml); tuple.setValue(TimeSeriesSchema.ATT_MATRIX, matrixXml); PmmXmlDoc miscXML = new PmmXmlDoc(); for (String column : miscColumns.keySet()) { MiscXml misc = (MiscXml) columnMappings.get(column); Cell cell = firstRow.getCell(miscColumns.get(column)); if (hasData(cell)) { try { misc.setValue(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(column + " value in row " + (firstRow.getRowNum() + 1) + " is not valid (" + getData(cell) + ")"); misc.setValue(null); } } else { misc.setValue(null); } misc.setOrigUnit(misc.getUnit()); miscXML.add(misc); } tuple.setValue(TimeSeriesSchema.ATT_MISC, miscXML); for (Row row : entry.getValue()) { Cell timeCell = null; Cell logcCell = null; Cell stdDevCell = null; Cell nMeasureCell = null; if (timeColumn != null) { timeCell = row.getCell(timeColumn); } if (logcColumn != null) { logcCell = row.getCell(logcColumn); } if (stdDevColumn != null) { stdDevCell = row.getCell(stdDevColumn); } if (nMeasureColumn != null) { nMeasureCell = row.getCell(nMeasureColumn); } Double time = null; Double logc = null; Double stdDev = null; Integer nMeasure = null; if (hasData(timeCell)) { try { time = Double.parseDouble(getData(timeCell).replace(",", ".")); } catch (NumberFormatException e) { warnings.add(timeColumnName + " value in row " + (row.getRowNum() + 1) + " is not valid (" + getData(timeCell) + ")"); } } else if (timeColumn != null) { warnings.add(timeColumnName + " value in row " + (row.getRowNum() + 1) + " is missing"); } if (hasData(logcCell)) { try { logc = Double.parseDouble(getData(logcCell).replace(",", ".")); } catch (NumberFormatException e) { warnings.add(logcColumnName + " value in row " + (row.getRowNum() + 1) + " is not valid (" + getData(logcCell) + ")"); } } else if (logcColumn != null) { warnings.add(logcColumnName + " value in row " + (row.getRowNum() + 1) + " is missing"); } if (hasData(stdDevCell)) { try { stdDev = Double.parseDouble(getData(stdDevCell).replace(",", ".")); } catch (NumberFormatException e) { warnings.add(stdDevColumnName + " value in row " + (row.getRowNum() + 1) + " is not valid (" + getData(stdDevCell) + ")"); } } else if (stdDevColumn != null) { warnings.add(stdDevColumnName + " value in row " + (row.getRowNum() + 1) + " is missing"); } if (hasData(nMeasureCell)) { try { String number = getData(nMeasureCell).replace(",", "."); if (number.contains(".")) { number = number.substring(0, number.indexOf(".")); } nMeasure = Integer.parseInt(number); } catch (NumberFormatException e) { warnings.add(nMeasureColumnName + " value in row " + (row.getRowNum() + 1) + " is not valid (" + getData(nMeasureCell) + ")"); } } else if (nMeasureColumn != null) { warnings.add(nMeasureColumnName + " value in row " + (row.getRowNum() + 1) + " is missing"); } for (String column : miscColumns.keySet()) { PmmXmlDoc misc = tuple.getPmmXml(TimeSeriesSchema.ATT_MISC); Cell cell = row.getCell(miscColumns.get(column)); if (hasData(cell)) { try { String param = ((MiscXml) columnMappings.get(column)).getName(); double value = Double.parseDouble(getData(cell).replace(",", ".")); if (!hasSameValue(param, value, misc)) { warnings.add("Variable conditions cannot be imported: " + "Only first value for " + column + " is used"); } } catch (NumberFormatException e) { } } } timeSeriesXml .add(new TimeSeriesXml(null, time, timeUnit, logc, concentrationUnit, stdDev, nMeasure)); } tuple.setValue(TimeSeriesSchema.ATT_TIMESERIES, timeSeriesXml); tuples.put(idString, tuple); } usedIds.clear(); usedIds.addAll(newIds); return tuples; }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public Map<String, KnimeTuple> getModelTuples(File file, String sheet, Map<String, Object> columnMappings, String agentColumnName, Map<String, AgentXml> agentMappings, String matrixColumnName, Map<String, MatrixXml> matrixMappings, KnimeTuple modelTuple, Map<String, String> modelMappings, Map<String, String> modelParamErrors, String modelDepMin, String modelDepMax, String modelDepUnit, String modelIndepMin, String modelIndepMax, String modelIndepUnit, String modelRmse, String modelR2, String modelAic, String modelDataPoints, Map<String, KnimeTuple> secModelTuples, Map<String, Map<String, String>> secModelMappings, Map<String, Map<String, String>> secModelParamErrors, Map<String, Map<String, String>> secModelIndepMins, Map<String, Map<String, String>> secModelIndepMaxs, Map<String, Map<String, String>> secModelIndepCategories, Map<String, Map<String, String>> secModelIndepUnits, Map<String, String> secModelRmse, Map<String, String> secModelR2, Map<String, String> secModelAic, Map<String, String> secModelDataPoints, boolean preserveIds, List<Integer> usedIds, Map<String, List<Integer>> secUsedIds, List<Integer> globalUsedIds) throws Exception { Workbook wb = getWorkbook(file); Sheet s = wb.getSheet(sheet);/*from ww w. j a va 2s . c om*/ warnings.clear(); evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); } Map<String, KnimeTuple> tuples = new LinkedHashMap<>(); Map<String, Integer> columns = getColumns(s); Map<String, Integer> miscColumns = new LinkedHashMap<>(); Integer idColumn = null; Integer commentColumn = null; Integer agentDetailsColumn = null; Integer matrixDetailsColumn = null; Integer agentColumn = columns.get(agentColumnName); Integer matrixColumn = columns.get(matrixColumnName); Integer depMinColumn = columns.get(modelDepMin); Integer depMaxColumn = columns.get(modelDepMax); Integer indepMinColumn = columns.get(modelIndepMin); Integer indepMaxColumn = columns.get(modelIndepMax); Integer rmseColumn = columns.get(modelRmse); Integer r2Column = columns.get(modelR2); Integer aicColumn = columns.get(modelAic); Integer dataPointsColumn = columns.get(modelDataPoints); for (String column : columns.keySet()) { if (columnMappings.containsKey(column)) { Object mapping = columnMappings.get(column); if (mapping instanceof MiscXml) { miscColumns.put(column, columns.get(column)); } else if (mapping.equals(NAME_COLUMN)) { idColumn = columns.get(column); } else if (mapping.equals(MdInfoXml.ATT_COMMENT)) { commentColumn = columns.get(column); } else if (mapping.equals(AttributeUtilities.AGENT_DETAILS)) { agentDetailsColumn = columns.get(column); } else if (mapping.equals(AttributeUtilities.MATRIX_DETAILS)) { matrixDetailsColumn = columns.get(column); } } } int index = 0; List<Integer> newIds = new ArrayList<>(); Map<String, List<Integer>> newSecIds = new LinkedHashMap<>(); List<Integer> newGlobalIds = new ArrayList<>(); for (int rowNumber = 1;; rowNumber++) { if (isEndOfFile(s, rowNumber)) { break; } int globalID; if (preserveIds && !globalUsedIds.isEmpty()) { globalID = globalUsedIds.remove(0); } else { globalID = MathUtilities.getRandomNegativeInt(); } newGlobalIds.add(globalID); KnimeTuple dataTuple = new KnimeTuple(SchemaFactory.createDataSchema()); Row row = s.getRow(rowNumber); Cell idCell = getCell(row, idColumn); Cell commentCell = getCell(row, commentColumn); Cell agentDetailsCell = getCell(row, agentDetailsColumn); Cell matrixDetailsCell = getCell(row, matrixDetailsColumn); Cell agentCell = getCell(row, agentColumn); Cell matrixCell = getCell(row, matrixColumn); Cell depMinCell = getCell(row, depMinColumn); Cell depMaxCell = getCell(row, depMaxColumn); Cell indepMinCell = getCell(row, indepMinColumn); Cell indepMaxCell = getCell(row, indepMaxColumn); Cell rmseCell = getCell(row, rmseColumn); Cell r2Cell = getCell(row, r2Column); Cell aicCell = getCell(row, aicColumn); Cell dataPointsCell = getCell(row, dataPointsColumn); dataTuple.setValue(TimeSeriesSchema.ATT_CONDID, MathUtilities.getRandomNegativeInt()); PmmXmlDoc dataInfo = new PmmXmlDoc(); PmmXmlDoc agentXml = new PmmXmlDoc(); PmmXmlDoc matrixXml = new PmmXmlDoc(); if (hasData(commentCell)) { dataInfo.add(new MdInfoXml(null, null, getData(commentCell), null, null)); } else { dataInfo.add(new MdInfoXml(null, null, null, null, null)); if (commentColumn != null) { // warnings.add(MdInfoXml.ATT_COMMENT + " value in row " // + (rowNumber + 1) + " is missing"); } } if (hasData(agentCell) && agentMappings.get(getData(agentCell)) != null) { agentXml.add(new AgentXml(agentMappings.get(getData(agentCell)))); } else { agentXml.add(new AgentXml()); if (agentColumn != null) { warnings.add(TimeSeriesSchema.ATT_AGENT + " value in row " + (rowNumber + 1) + " is missing"); } } if (hasData(matrixCell) && matrixMappings.get(getData(matrixCell)) != null) { matrixXml.add(new MatrixXml(matrixMappings.get(getData(matrixCell)))); } else { matrixXml.add(new MatrixXml()); if (matrixColumn != null) { warnings.add(TimeSeriesSchema.ATT_MATRIX + " value in row " + (rowNumber + 1) + " is missing"); } } if (hasData(agentDetailsCell)) { ((AgentXml) agentXml.get(0)).setDetail(getData(agentDetailsCell)); } if (hasData(matrixDetailsCell)) { ((MatrixXml) matrixXml.get(0)).setDetail(getData(matrixDetailsCell)); } dataTuple.setValue(TimeSeriesSchema.ATT_MDINFO, dataInfo); dataTuple.setValue(TimeSeriesSchema.ATT_AGENT, agentXml); dataTuple.setValue(TimeSeriesSchema.ATT_MATRIX, matrixXml); PmmXmlDoc miscXML = new PmmXmlDoc(); for (String column : miscColumns.keySet()) { MiscXml misc = new MiscXml((MiscXml) columnMappings.get(column)); Cell cell = row.getCell(miscColumns.get(column)); if (hasData(cell)) { try { misc.setValue(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(column + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(column + " value in row " + (rowNumber + 1) + " is missing"); } misc.setOrigUnit(misc.getUnit()); miscXML.add(misc); } dataTuple.setValue(TimeSeriesSchema.ATT_MISC, miscXML); PmmXmlDoc modelXml = modelTuple.getPmmXml(Model1Schema.ATT_MODELCATALOG); PmmXmlDoc paramXml = modelTuple.getPmmXml(Model1Schema.ATT_PARAMETER); PmmXmlDoc estXml = modelTuple.getPmmXml(Model1Schema.ATT_ESTMODEL); PmmXmlDoc depXml = modelTuple.getPmmXml(Model1Schema.ATT_DEPENDENT); PmmXmlDoc indepXml = modelTuple.getPmmXml(Model1Schema.ATT_INDEPENDENT); int primId; if (preserveIds && !usedIds.isEmpty()) { primId = usedIds.remove(0); } else { primId = MathUtilities.getRandomNegativeInt(); } newIds.add(primId); if (modelDepUnit != null && !modelDepUnit.equals(((DepXml) depXml.get(0)).getUnit())) { ((DepXml) depXml.get(0)).setUnit(modelDepUnit); ((CatalogModelXml) modelXml.get(0)).setId(MathUtilities.getRandomNegativeInt()); } if (hasData(depMinCell)) { try { ((DepXml) depXml.get(0)).setMin(Double.parseDouble(getData(depMinCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelDepMin + " value in row " + (rowNumber + 1) + " is not valid (" + getData(depMinCell) + ")"); } } else if (modelDepMin != null) { warnings.add(modelDepMin + " value in row " + (rowNumber + 1) + " is missing"); } if (hasData(depMaxCell)) { try { ((DepXml) depXml.get(0)).setMax(Double.parseDouble(getData(depMaxCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelDepMax + " value in row " + (rowNumber + 1) + " is not valid (" + getData(depMaxCell) + ")"); } } else if (modelDepMax != null) { warnings.add(modelDepMax + " value in row " + (rowNumber + 1) + " is missing"); } if (hasData(indepMinCell)) { try { ((IndepXml) indepXml.get(0)) .setMin(Double.parseDouble(getData(indepMinCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelIndepMin + " value in row " + (rowNumber + 1) + " is not valid (" + getData(indepMinCell) + ")"); } } else if (modelIndepMin != null) { warnings.add(modelIndepMin + " value in row " + (rowNumber + 1) + " is missing"); } if (hasData(indepMaxCell)) { try { ((IndepXml) indepXml.get(0)) .setMax(Double.parseDouble(getData(indepMaxCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelIndepMax + " value in row " + (rowNumber + 1) + " is not valid (" + getData(indepMaxCell) + ")"); } } else if (modelIndepMax != null) { warnings.add(modelIndepMax + " value in row " + (rowNumber + 1) + " is missing"); } if (modelIndepUnit != null && !modelIndepUnit.equals(((IndepXml) indepXml.get(0)).getUnit())) { ((IndepXml) indepXml.get(0)).setUnit(modelIndepUnit); ((CatalogModelXml) modelXml.get(0)).setId(MathUtilities.getRandomNegativeInt()); } ((EstModelXml) estXml.get(0)).setId(primId); ((EstModelXml) estXml.get(0)).setComment(getData(commentCell)); if (hasData(rmseCell)) { try { ((EstModelXml) estXml.get(0)).setRms(Double.parseDouble(getData(rmseCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelRmse + " value in row " + (rowNumber + 1) + " is not valid (" + getData(rmseCell) + ")"); } } if (hasData(r2Cell)) { try { ((EstModelXml) estXml.get(0)).setR2(Double.parseDouble(getData(r2Cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelR2 + " value in row " + (rowNumber + 1) + " is not valid (" + getData(r2Cell) + ")"); } } if (hasData(aicCell)) { try { ((EstModelXml) estXml.get(0)).setAic(Double.parseDouble(getData(aicCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(modelAic + " value in row " + (rowNumber + 1) + " is not valid (" + getData(aicCell) + ")"); } } if (hasData(dataPointsCell)) { String data = getData(dataPointsCell).replace(".0", "").replace(",0", ""); try { ((EstModelXml) estXml.get(0)).setDof(Integer.parseInt(data) - paramXml.size()); } catch (NumberFormatException e) { warnings.add( modelDataPoints + " value in row " + (rowNumber + 1) + " is not valid (" + data + ")"); } } if (hasData(idCell)) { ((EstModelXml) estXml.get(0)).setName(getData(idCell)); } for (PmmXmlElementConvertable el : paramXml.getElementSet()) { ParamXml element = (ParamXml) el; String mapping = modelMappings.get(element.getName()); if (mapping != null) { Cell cell = row.getCell(columns.get(mapping)); if (hasData(cell)) { try { element.setValue(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(mapping + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(mapping + " value in row " + (rowNumber + 1) + " is missing"); } } String errorMapping = modelParamErrors.get(element.getName()); if (errorMapping != null) { Cell cell = row.getCell(columns.get(errorMapping)); if (hasData(cell)) { try { element.setError(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(errorMapping + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(errorMapping + " value in row " + (rowNumber + 1) + " is missing"); } } } modelTuple.setValue(Model1Schema.ATT_DEPENDENT, depXml); modelTuple.setValue(Model1Schema.ATT_INDEPENDENT, indepXml); modelTuple.setValue(Model1Schema.ATT_MODELCATALOG, modelXml); modelTuple.setValue(Model1Schema.ATT_PARAMETER, paramXml); modelTuple.setValue(Model1Schema.ATT_ESTMODEL, estXml); if (secModelTuples.isEmpty()) { tuples.put(index + "", new KnimeTuple(SchemaFactory.createM1DataSchema(), modelTuple, dataTuple)); index++; } else { for (String param : secModelTuples.keySet()) { KnimeTuple secTuple = secModelTuples.get(param); PmmXmlDoc secParamXml = secTuple.getPmmXml(Model2Schema.ATT_PARAMETER); PmmXmlDoc secDepXml = secTuple.getPmmXml(Model2Schema.ATT_DEPENDENT); PmmXmlDoc secEstXml = secTuple.getPmmXml(Model2Schema.ATT_ESTMODEL); PmmXmlDoc secModelXml = secTuple.getPmmXml(Model2Schema.ATT_MODELCATALOG); PmmXmlDoc secIndepXml = secTuple.getPmmXml(Model2Schema.ATT_INDEPENDENT); String formula = ((CatalogModelXml) secModelXml.get(0)).getFormula(); int secID; if (preserveIds && secUsedIds.containsKey(param) && !secUsedIds.get(param).isEmpty()) { secID = secUsedIds.get(param).remove(0); } else { secID = MathUtilities.getRandomNegativeInt(); } if (!newSecIds.containsKey(param)) { newSecIds.put(param, new ArrayList<Integer>()); } newSecIds.get(param).add(secID); formula = MathUtilities.replaceVariable(formula, ((DepXml) secDepXml.get(0)).getName(), param); ((CatalogModelXml) secModelXml.get(0)).setFormula(formula); ((DepXml) secDepXml.get(0)).setName(param); ((EstModelXml) secEstXml.get(0)).setId(secID); for (PmmXmlElementConvertable el : secParamXml.getElementSet()) { ParamXml element = (ParamXml) el; String mapping = secModelMappings.get(param).get(element.getName()); String error = secModelParamErrors.get(param).get(element.getName()); if (mapping != null) { Cell cell = row.getCell(columns.get(mapping)); if (hasData(cell)) { try { element.setValue(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(mapping + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(mapping + " value in row " + (rowNumber + 1) + " is missing"); } } if (error != null) { Cell cell = row.getCell(columns.get(error)); if (hasData(cell)) { try { element.setError(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(error + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(error + " value in row " + (rowNumber + 1) + " is missing"); } } } for (PmmXmlElementConvertable el : secIndepXml.getElementSet()) { IndepXml element = (IndepXml) el; String category = secModelIndepCategories.get(param).get(element.getName()); String unit = secModelIndepUnits.get(param).get(element.getName()); if (category == null || unit == null) { continue; } if (!category.equals(element.getCategory())) { element.setCategory(category); ((CatalogModelXml) secModelXml.get(0)).setId(MathUtilities.getRandomNegativeInt()); } if (!unit.equals(element.getUnit())) { element.setUnit(unit); ((CatalogModelXml) secModelXml.get(0)).setId(MathUtilities.getRandomNegativeInt()); } String minColumn = secModelIndepMins.get(param).get(element.getName()); String maxColumn = secModelIndepMaxs.get(param).get(element.getName()); if (minColumn != null) { Cell minCell = row.getCell(columns.get(minColumn)); if (hasData(minCell)) { try { element.setMin(Double.parseDouble(getData(minCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(minColumn + " value in row " + (rowNumber + 1) + " is not valid (" + getData(minCell) + ")"); } } else { warnings.add(minColumn + " value in row " + (rowNumber + 1) + " is missing"); } } if (maxColumn != null) { Cell maxCell = row.getCell(columns.get(maxColumn)); if (hasData(maxCell)) { try { element.setMax(Double.parseDouble(getData(maxCell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(maxColumn + " value in row " + (rowNumber + 1) + " is not valid (" + getData(maxCell) + ")"); } } else { warnings.add(maxColumn + " value in row " + (rowNumber + 1) + " is missing"); } } } String rmse = secModelRmse.get(param); String r2 = secModelR2.get(param); String aic = secModelAic.get(param); String dataPoints = secModelDataPoints.get(param); if (rmse != null) { Cell cell = row.getCell(columns.get(rmse)); if (hasData(cell)) { try { ((EstModelXml) secEstXml.get(0)) .setRms(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(rmse + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(rmse + " value in row " + (rowNumber + 1) + " is missing"); } } if (r2 != null) { Cell cell = row.getCell(columns.get(r2)); if (hasData(cell)) { try { ((EstModelXml) secEstXml.get(0)) .setR2(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(r2 + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(r2 + " value in row " + (rowNumber + 1) + " is missing"); } } if (aic != null) { Cell cell = row.getCell(columns.get(aic)); if (hasData(cell)) { try { ((EstModelXml) secEstXml.get(0)) .setAic(Double.parseDouble(getData(cell).replace(",", "."))); } catch (NumberFormatException e) { warnings.add(aic + " value in row " + (rowNumber + 1) + " is not valid (" + getData(cell) + ")"); } } else { warnings.add(aic + " value in row " + (rowNumber + 1) + " is missing"); } } if (dataPoints != null) { Cell cell = row.getCell(columns.get(dataPoints)); if (hasData(cell)) { String data = getData(cell).replace(".0", "").replace(",0", ""); try { ((EstModelXml) secEstXml.get(0)) .setDof(Integer.parseInt(data) - secParamXml.size()); } catch (NumberFormatException e) { warnings.add(dataPoints + " value in row " + (rowNumber + 1) + " is not valid (" + data + ")"); } } else { warnings.add(dataPoints + " value in row " + (rowNumber + 1) + " is missing"); } } secTuple.setValue(Model2Schema.ATT_MODELCATALOG, secModelXml); secTuple.setValue(Model2Schema.ATT_PARAMETER, secParamXml); secTuple.setValue(Model2Schema.ATT_DEPENDENT, secDepXml); secTuple.setValue(Model2Schema.ATT_ESTMODEL, secEstXml); secTuple.setValue(Model2Schema.ATT_INDEPENDENT, secIndepXml); secTuple.setValue(Model2Schema.ATT_GLOBAL_MODEL_ID, globalID); tuples.put(index + "", new KnimeTuple(SchemaFactory.createM12DataSchema(), new KnimeTuple(SchemaFactory.createM1DataSchema(), modelTuple, dataTuple), secTuple)); index++; } } } usedIds.clear(); usedIds.addAll(newIds); secUsedIds.clear(); secUsedIds.putAll(newSecIds); globalUsedIds.clear(); globalUsedIds.addAll(newGlobalIds); return tuples; }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public List<String> getColumns(File file, String sheet) throws Exception { Workbook wb = getWorkbook(file); Sheet s = wb.getSheet(sheet);/* ww w . jav a2 s .com*/ evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); } return new ArrayList<>(getColumns(s).keySet()); }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public Set<String> getValuesInColumn(File file, String sheet, String column) throws Exception { Set<String> valueSet = new LinkedHashSet<>(); Workbook wb = getWorkbook(file); Sheet s = wb.getSheet(sheet);// w w w .j a v a 2 s. co m evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); } Map<String, Integer> columns = getColumns(s); int columnId = columns.get(column); for (int i = 1; i <= s.getLastRowNum(); i++) { if (s.getRow(i) != null) { Cell cell = s.getRow(i).getCell(columnId); if (hasData(cell)) { valueSet.add(getData(cell)); } } } return valueSet; }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public List<Integer> getMissingData(File file, String sheet, String column) throws Exception { List<Integer> missing = new ArrayList<>(); Workbook wb = getWorkbook(file); Sheet s = wb.getSheet(sheet);/* w w w. j ava 2s .co m*/ evaluator = wb.getCreationHelper().createFormulaEvaluator(); Map<String, Integer> columns = getColumns(s); int columnId = columns.get(column); for (int i = 1; i <= s.getLastRowNum(); i++) { if (s.getRow(i) != null && !hasData(s.getRow(i).getCell(columnId))) { for (int c : columns.values()) { if (hasData(s.getRow(i).getCell(c))) { missing.add(i + 1); break; } } } } return missing; }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
/** * This method adds a new cell to the sheet of a workbook. It could * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is * tied to a specific Excel API. Having those methods here allows the Report to become * an interface if a second engine (i.e. JXL) should be added in the future. * @param workbook/*from ww w. j ava 2 s. c om*/ * @param sheet * @param cellDefinition */ private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) { final int columnNum = cellDefinition.column, rowNum = cellDefinition.row; Row row = sheet.getRow(rowNum); if (row == null) row = sheet.createRow(rowNum); Cell cell = row.getCell(columnNum); // If the cell already exists and is no blank cell // it will be used including all formating if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { cell = fill(workbook, cell, cellDefinition, false); } // Otherwise a new cell will be created, the datatype set and // optionally a format will be created else { cell = fill(workbook, row.createCell(columnNum), cellDefinition, true); final Sheet referenceSheet; if (cellDefinition.getReferenceCell() != null && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) { final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row); final Cell referenceCell = referenceRow == null ? null : referenceRow.getCell(cellDefinition.getReferenceCell().column); if (referenceCell != null && referenceCell.getCellStyle() != null) cell.setCellStyle(referenceCell.getCellStyle()); } } // Add an optional comment if (cellDefinition.hasComment()) { final CreationHelper factory = workbook.getCreationHelper(); final Drawing drawing = sheet.createDrawingPatriarch(); final ClientAnchor commentAnchor = factory.createClientAnchor(); final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1 : cellDefinition.comment.column; final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row; commentAnchor.setCol1(col1); commentAnchor.setRow1(row1); commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width)); commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height)); final Comment comment = drawing.createCellComment(commentAnchor); comment.setString(factory.createRichTextString(cellDefinition.comment.text)); comment.setAuthor(cellDefinition.comment.author); comment.setVisible(cellDefinition.comment.visible); cell.setCellComment(comment); } }