List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java
License:Apache License
public static void main(String[] args) { if (args.length != 2) { System.out.println("usage: UserDefinedFunctionExample fileName cellId"); return;//from ww w.jav a 2 s. c om } System.out.println("fileName: " + args[0]); System.out.println("cell: " + args[1]); File workbookFile = new File(args[0]); try { FileInputStream fis = new FileInputStream(workbookFile); Workbook workbook = WorkbookFactory.create(fis); fis.close(); String[] functionNames = { "calculatePayment" }; FreeRefFunction[] functionImpls = { new CalculateMortgage() }; UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls); // register the user-defined function in the workbook workbook.addToolPack(udfToolpack); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); CellReference cr = new CellReference(args[1]); String sheetName = cr.getSheetName(); Sheet sheet = workbook.getSheet(sheetName); int rowIdx = cr.getRow(); int colIdx = cr.getCol(); Row row = sheet.getRow(rowIdx); Cell cell = row.getCell(colIdx); CellValue value = evaluator.evaluate(cell); System.out.println("returns value: " + value); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:Contabilidad.Egresos.java
public void generaExcel(String noPoliza, String noMes, String ruta) { DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); formatoPorcentaje.setMinimumFractionDigits(2); File archivoXLS = new File(ruta + "/" + noPoliza + "-" + noMes + "-Eg.xls"); File plantilla = new File("imagenes/Diario.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); try {//w ww . j a v a 2 s.com Query query1 = session.createQuery("SELECT DISTINCT reg FROM Asiento reg " + "LEFT JOIN reg.excelPago ex " + "where ex.poliza=" + noPoliza + " AND MONTH(ex.fecha)=" + noMes + " AND ex.tipo='Eg' ORDER BY reg.idAsiento ASC"); Asiento[] Asientos = (Asiento[]) query1.list().toArray(new Asiento[0]); Path FROM = Paths.get("imagenes/Diario.xls"); Path TO = Paths.get(ruta + "/" + noPoliza + "-" + noMes + "-Eg.xls"); //sobreescribir el fichero de destino, si existe, y copiar los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); Workbook libro = new HSSFWorkbook(fsFileSystem); //Cargamos las cabeceras libro.getSheet("Hoja1").createRow(2); libro.getSheet("Hoja1").getRow(2).createCell(0).setCellValue("Eg"); libro.getSheet("Hoja1").getRow(2).createCell(1).setCellValue(Integer.parseInt(noPoliza)); if (Asientos.length > 0) { libro.getSheet("Hoja1").getRow(2).createCell(2) .setCellValue(Asientos[0].getExcelProvision().getConcepto()); Calendar calendario = Calendar.getInstance(); calendario.setTime(Asientos[0].getExcelProvision().getFecha()); libro.getSheet("Hoja1").getRow(2).createCell(3).setCellValue(calendario.get(Calendar.DAY_OF_MONTH)); } double total = 0.0D; int renglon = 3; for (int ren = 0; ren < Asientos.length; ren++) { Registro[] registros = (Registro[]) session.createCriteria(Registro.class) .createAlias("asiento", "asc") .add(Restrictions.eq("asc.idAsiento", Asientos[ren].getIdAsiento())) .add(Restrictions.eq("tipoAsiento", "Eg")).addOrder(Order.desc("tipo")) .addOrder(Order.asc("idRegistro")).list().toArray(new Registro[0]); for (int r = 0; r < registros.length; r++) { libro.getSheet("Hoja1").createRow(renglon); libro.getSheet("Hoja1").getRow(renglon).createCell(1) .setCellValue(registros[r].getCuentas().getIdCuentas()); libro.getSheet("Hoja1").getRow(renglon).createCell(2) .setCellValue(Integer.parseInt(registros[r].getDepto())); libro.getSheet("Hoja1").getRow(renglon).createCell(3).setCellValue(registros[r].getConcepto()); libro.getSheet("Hoja1").getRow(renglon).createCell(4).setCellValue(registros[r].getCambio()); if (registros[r].getTipo().compareTo("d") == 0) libro.getSheet("Hoja1").getRow(renglon).createCell(5) .setCellValue(registros[r].getCantidad()); else libro.getSheet("Hoja1").getRow(renglon).createCell(6) .setCellValue(registros[r].getCantidad()); renglon++; } } int celda = renglon; libro.getSheet("Hoja1").createRow(renglon); libro.getSheet("Hoja1").getRow(renglon).createCell(1);// libro.getSheet("Hoja1").getRow(renglon).getCell(1).setCellValue("FIN_PARTIDAS"); FileOutputStream archivo = new FileOutputStream(archivoXLS); libro.write(archivo); archivo.close(); //Desktop.getDesktop().open(archivoXLS); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte"); } if (session != null) if (session.isOpen()) session.close(); }
From source file:Contabilidad.Provision.java
public void generaExcel(String noPoliza, String noMes, String ruta) { DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); formatoPorcentaje.setMinimumFractionDigits(2); File archivoXLS = new File(ruta + "/" + noPoliza + "-" + noMes + ".xls"); File plantilla = new File("imagenes/Diario.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); try {/*from ww w. j a v a 2s. c o m*/ Query query1 = session.createQuery("SELECT DISTINCT reg FROM Asiento reg " + "LEFT JOIN reg.excelProvision ex " + "where ex.poliza = " + noPoliza + " AND MONTH(ex.fecha)=" + noMes + " and ex.tipo='Dr' ORDER BY reg.idAsiento ASC"); Asiento[] Asientos = (Asiento[]) query1.list().toArray(new Asiento[0]); Path FROM = Paths.get("imagenes/Diario.xls"); Path TO = Paths.get(ruta + "/" + noPoliza + "-" + noMes + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); Workbook libro = new HSSFWorkbook(fsFileSystem); //Cargamos las cabeceras libro.getSheet("Hoja1").createRow(2); libro.getSheet("Hoja1").getRow(2).createCell(0).setCellValue("Dr"); libro.getSheet("Hoja1").getRow(2).createCell(1).setCellValue(Integer.parseInt(noPoliza)); if (Asientos.length > 0) { libro.getSheet("Hoja1").getRow(2).createCell(2) .setCellValue(Asientos[0].getExcelProvision().getConcepto()); Calendar calendario = Calendar.getInstance(); calendario.setTime(Asientos[0].getExcelProvision().getFecha()); libro.getSheet("Hoja1").getRow(2).createCell(3).setCellValue(calendario.get(Calendar.DAY_OF_MONTH)); } double total = 0.0D; int renglon = 3; for (int ren = 0; ren < Asientos.length; ren++) { Registro[] registros = (Registro[]) session.createCriteria(Registro.class) .createAlias("asiento", "asc") .add(Restrictions.eq("asc.idAsiento", Asientos[ren].getIdAsiento())) .add(Restrictions.eq("tipoAsiento", "Dr")).addOrder(Order.desc("tipo")) .addOrder(Order.asc("idRegistro")).list().toArray(new Registro[0]); for (int r = 0; r < registros.length; r++) { libro.getSheet("Hoja1").createRow(renglon); libro.getSheet("Hoja1").getRow(renglon).createCell(1) .setCellValue(registros[r].getCuentas().getIdCuentas()); libro.getSheet("Hoja1").getRow(renglon).createCell(2) .setCellValue(Integer.parseInt(registros[r].getDepto())); libro.getSheet("Hoja1").getRow(renglon).createCell(3).setCellValue(registros[r].getConcepto()); libro.getSheet("Hoja1").getRow(renglon).createCell(4).setCellValue(registros[r].getCambio()); if (registros[r].getTipo().compareTo("d") == 0) libro.getSheet("Hoja1").getRow(renglon).createCell(5) .setCellValue(registros[r].getCantidad()); else libro.getSheet("Hoja1").getRow(renglon).createCell(6) .setCellValue(registros[r].getCantidad()); renglon++; } } int celda = renglon; libro.getSheet("Hoja1").createRow(renglon); libro.getSheet("Hoja1").getRow(renglon).createCell(1);// libro.getSheet("Hoja1").getRow(renglon).getCell(1).setCellValue("FIN_PARTIDAS"); FileOutputStream archivo = new FileOutputStream(archivoXLS); libro.write(archivo); archivo.close(); //Desktop.getDesktop().open(archivoXLS); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte"); } if (session != null) if (session.isOpen()) session.close(); }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean consolidateFiles() { boolean result = false; AtomicInteger rowIndex = new AtomicInteger(3); String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx"; Workbook consolidateWb = new XSSFWorkbook(); try {/*from ww w.j av a2s.c o m*/ Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado"); Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).sorted((p1, p2) -> { String acronym = getAcromynName(p1); String acronym2 = getAcromynName(p2); return acronym.compareToIgnoreCase(acronym2); }).forEach(p -> { try { Workbook wb = WorkbookFactory.create(p.toFile()); Sheet sheet = wb.getSheet("Procesado"); updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s", p.toString(), outputName)); for (int index = 3; index < sheet.getLastRowNum(); index++) { Row row = sheet.getRow(index); Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement()); row.forEach(c -> { if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType()); updateMessages( String.format("Copiando los datos de la fila: #%d", c.getRowIndex())); switch (c.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(c.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(c.getRichStringCellValue()); break; } } }); row = null; } sheet = null; wb.close(); wb = null; } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } }); Path path = Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).findFirst().get(); createHeadersConsolidateFile(consolidateWb, path); for (int i = 0; i < 155; i++) sheetConsolidate.autoSizeColumn(i); sheetConsolidate.setAutoFilter( CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum()))); try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), outputName))); consolidateWb.write(fos); result = true; } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, "Ocurrio un error al intenatr guardar el archivo consolidado", ex); } finally { consolidateWb.close(); } } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } consolidateWb = null; return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java
License:Open Source License
private List<Exception> doTheImport(Workbook wb, String filename) { // throws Exception List<Exception> exceptions = new ArrayList<>(); Sheet stationSheet = wb.getSheet("Stations"); Sheet deliverySheet = wb.getSheet("Deliveries"); Sheet d2dSheet = wb.getSheet("Deliveries2Deliveries"); Sheet transactionSheet = wb.getSheet("BackTracing"); Sheet lookupSheet = wb.getSheet("LookUp"); Sheet forwardSheet = wb.getSheet("Opt_ForwardTracing"); Sheet forwardSheetNew = wb.getSheet("ForwardTracing_Opt"); Sheet forSheet = wb.getSheet("ForTracing"); Sheet fwdSheet = wb.getSheet("FwdTracing"); if (forSheet == null) forSheet = fwdSheet;/*from w ww . ja v a2 s . c om*/ boolean isForTracing = forSheet != null; if (isForTracing) transactionSheet = forSheet; if (stationSheet == null || transactionSheet == null && deliverySheet == null) { exceptions.add(new Exception("Wrong template format!")); return exceptions; } checkStationsFirst(exceptions, stationSheet); if (deliverySheet != null) { checkDeliveriesFirst(exceptions, deliverySheet); // load all Stations HashMap<String, Station> stations = new HashMap<>(); int numRows = stationSheet.getLastRowNum() + 1; Row titleRow = stationSheet.getRow(0); for (classRowIndex = 1; classRowIndex < numRows; classRowIndex++) { Station s = getStation(titleRow, stationSheet.getRow(classRowIndex)); if (s == null) break; if (stations.containsKey(s.getId())) exceptions.add(new Exception("Station defined twice -> Row " + (classRowIndex + 1) + "; Station Id: '" + s.getId() + "'")); stations.put(s.getId(), s); } // load all Deliveries HashMap<String, Delivery> deliveries = new HashMap<>(); numRows = deliverySheet.getLastRowNum() + 1; titleRow = deliverySheet.getRow(0); HashMap<String, String> definedLots = new HashMap<>(); HashMap<String, Integer> deliveryRows = new HashMap<>(); for (classRowIndex = 2; classRowIndex < numRows; classRowIndex++) { Delivery d = getMultiOutDelivery(exceptions, stations, titleRow, deliverySheet.getRow(classRowIndex), definedLots, classRowIndex, filename, d2dSheet != null); if (d == null) break; if (deliveries.containsKey(d.getId())) exceptions.add(new Exception("Delivery defined twice -> in Row " + (classRowIndex + 1) + " and in Row " + deliveryRows.get(d.getId()) + "; Delivery Id: '" + d.getId() + "'")); else deliveryRows.put(d.getId(), classRowIndex + 1); deliveries.put(d.getId(), d); } // load Recipes HashSet<D2D> recipes = new HashSet<>(); if (d2dSheet != null) { numRows = d2dSheet.getLastRowNum() + 1; titleRow = d2dSheet.getRow(0); for (classRowIndex = 1; classRowIndex < numRows; classRowIndex++) { D2D dl = getD2D(exceptions, deliveries, titleRow, d2dSheet.getRow(classRowIndex), classRowIndex); if (dl == null) break; recipes.add(dl); } } MetaInfo mi = new MetaInfo(); mi.setFilename(filename); if (lookupSheet != null) loadLookupSheet(lookupSheet); Integer miDbId = null; try { miDbId = mi.getID(mydbi); } catch (Exception e) { exceptions.add(e); } if (miDbId == null) exceptions.add(new Exception("File already imported")); for (Delivery d : deliveries.values()) { try { d.getID(miDbId, false, mydbi); } catch (Exception e) { exceptions.add(e); } //if (!d.getLogMessages().isEmpty()) logMessages += d.getLogMessages() + "\n"; if (d.getExceptions().size() > 0) exceptions.addAll(d.getExceptions()); } HashMap<Delivery, HashSet<Integer>> ingredients = new HashMap<>(); for (D2D dl : recipes) { try { dl.getId(miDbId, mydbi); } catch (Exception e) { exceptions.add(e); } // collect data for checks if data is missing... Delivery d = dl.getTargetDelivery(); if (!ingredients.containsKey(d)) ingredients.put(d, new HashSet<Integer>()); HashSet<Integer> hd = ingredients.get(d); if (dl.getIngredient() != null) hd.add(dl.getIngredient().getDbId()); } return exceptions; } int borderRowLotStart = 0; Row row = transactionSheet.getRow(0); Row titleRow; Cell cell; HashMap<String, Delivery> outDeliveries = new HashMap<>(); HashMap<String, Lot> outLots = new HashMap<>(); Station sif; MetaInfo mi; boolean isNewFormat_151105 = false; if (forwardSheet != null) { // Station in focus cell = row.getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) exceptions.add(new Exception("Station in Focus not defined")); cell.setCellType(Cell.CELL_TYPE_STRING); sif = getStation(exceptions, stationSheet, cell.getStringCellValue(), row); // Delivery(s) Outbound classRowIndex = 5; titleRow = transactionSheet.getRow(classRowIndex - 2); for (;; classRowIndex++) { row = transactionSheet.getRow(classRowIndex); if (row == null) continue; if (isBlockEnd(row, 13, "Reporter Information")) break; Delivery d = getDelivery(exceptions, stationSheet, sif, row, true, titleRow, filename, false, null, outDeliveries, false, isNewFormat_151105); if (d == null) continue; outDeliveries.put(d.getId(), d); outLots.put(d.getLot().getNumber(), d.getLot()); } // Metadata on Reporter classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Reporter Information") + 2; row = transactionSheet.getRow(classRowIndex); mi = getMetaInfo(exceptions, row, transactionSheet.getRow(classRowIndex - 1)); mi.setFilename(filename); } else { // Reporter shifted to the top // Metadata on Reporter classRowIndex = getNextBlockRowIndex(transactionSheet, 0, "Reporter Information") + 2; row = transactionSheet.getRow(classRowIndex); mi = getMetaInfo(exceptions, row, transactionSheet.getRow(classRowIndex - 1)); mi.setFilename(filename); // Station in focus classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Station in Focus:"); row = transactionSheet.getRow(classRowIndex); cell = row.getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) exceptions.add(new Exception("Station in Focus not defined")); cell.setCellType(Cell.CELL_TYPE_STRING); sif = getStation(exceptions, stationSheet, cell.getStringCellValue(), row); String label = "Products Out"; if (isForTracing) label = "Ingredients In for Lot(s)"; // Delivery(s) Outbound classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, label) + 3; titleRow = transactionSheet.getRow(classRowIndex - 2); cell = titleRow.getCell(0); isNewFormat_151105 = cell.getStringCellValue().equals("Product Lot Number"); for (;; classRowIndex++) { row = transactionSheet.getRow(classRowIndex); if (row == null) continue; if (isBlockEnd(row, 13, "Lot Information")) break; Delivery d = getDelivery(exceptions, stationSheet, sif, row, !isForTracing, titleRow, filename, isForTracing, outLots, outDeliveries, false, isNewFormat_151105); if (d == null) continue; outDeliveries.put(d.getId(), d); if (!isForTracing) outLots.put(d.getLot().getNumber(), d.getLot()); } } String label = "Ingredients In for Lot(s)"; if (isForTracing) label = "Products Out"; // Lot(s) classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Lot Information") + 3; borderRowLotStart = classRowIndex; titleRow = transactionSheet.getRow(classRowIndex - 2); for (;; classRowIndex++) { row = transactionSheet.getRow(classRowIndex); if (row == null) continue; if (isBlockEnd(row, 13, label)) break; if (!fillLot(exceptions, row, sif, outLots, titleRow, isForTracing ? outDeliveries : null, classRowIndex + 1, isNewFormat_151105)) { exceptions.add(new Exception("Lot number unknown in Row number " + (classRowIndex + 1))); } } checkTraceDeliveries(exceptions, transactionSheet, borderRowLotStart, isForTracing, isNewFormat_151105); // Deliveries/Recipe Inbound boolean hasIngredients = false; label = "Ingredients for Lot(s)"; if (isForTracing) label = "Products Out"; classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, label) + 3; HashMap<String, Delivery> inDeliveries = new HashMap<>(); int numRows = transactionSheet.getLastRowNum() + 1; titleRow = transactionSheet.getRow(classRowIndex - 2); for (; classRowIndex < numRows; classRowIndex++) { row = transactionSheet.getRow(classRowIndex); if (row == null) continue; if (isBlockEnd(row, 13, null)) break; Delivery d = getDelivery(exceptions, stationSheet, sif, row, isForTracing, titleRow, filename, isForTracing, outLots, inDeliveries, false, isNewFormat_151105); if (d == null) continue; if (!isForTracing && d.getTargetLotIds().size() == 0) exceptions.add(new Exception("Lot number unknown in Row number " + (classRowIndex + 1))); inDeliveries.put(d.getId(), d); hasIngredients = true; } if (!hasIngredients) { warns.put("No " + (isForTracing ? "Products Out" : "ingredients") + " defined...", null); } // Opt_ForwardTracing HashSet<Delivery> forwDeliveries = new HashSet<>(); if (!isForTracing) { if (forwardSheet == null) forwardSheet = forwardSheetNew; numRows = forwardSheet.getLastRowNum() + 1; titleRow = forwardSheet.getRow(0); for (classRowIndex = 2; classRowIndex < numRows; classRowIndex++) { row = transactionSheet.getRow(classRowIndex); if (row == null) continue; Delivery d = getForwardDelivery(exceptions, stationSheet, outLots, titleRow, forwardSheet.getRow(classRowIndex), isNewFormat_151105); if (d == null) continue; forwDeliveries.add(d); } } if (lookupSheet != null) loadLookupSheet(lookupSheet); Integer miDbId = null; try { miDbId = mi.getID(mydbi); } catch (Exception e) { exceptions.add(e); } if (miDbId == null) exceptions.add(new Exception("File already imported")); if (isForTracing) try { insertForIntoDb(exceptions, miDbId, inDeliveries, outDeliveries); } catch (Exception e) { exceptions.add(e); } else try { insertIntoDb(exceptions, miDbId, inDeliveries, outDeliveries, forwDeliveries); } catch (Exception e) { exceptions.add(e); } return exceptions; }
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); warnings.clear();// w w w . j a v a2 s .c o m 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); warnings.clear();/*w w w . j a v a 2s .c o m*/ 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); evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); }/*from www.ja va2 s .c om*/ 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); evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (s == null) { throw new Exception("Sheet not found"); }/*from ww w . ja v a 2 s.c o m*/ 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); 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);/* www. j a v a 2 s. c o m*/ break; } } } } return missing; }