List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createName
@Override
public XSSFName createName()
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private void fillLookup(XSSFWorkbook workbook, XSSFSheet sheetLookup) throws SQLException { String sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Sampling'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; ResultSet rs = DBKernel.getResultSet(sql, false); int rownum = 1; if (rs != null && rs.first()) { do {/* w w w . j av a2s. com*/ XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } Name reference = workbook.createName(); reference.setNameName("Sampling"); String referenceString = sheetLookup.getSheetName() + "!$A$2:$A$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='TypeOfBusiness'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(1); if (cell == null) cell = row.createCell(1); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("ToB"); referenceString = sheetLookup.getSheetName() + "!$B$2:$B$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Treatment'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(2); if (cell == null) cell = row.createCell(2); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Treatment"); referenceString = sheetLookup.getSheetName() + "!$C$2:$C$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Units'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Units"); referenceString = sheetLookup.getSheetName() + "!$D$2:$D$" + (rownum); reference.setRefersToFormula(referenceString); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getFortraceRequests(String outputFolder, List<String> business2Trace) throws SQLException, IOException { int result = 0; String tracingBusinessesSQL = ""; for (String s : business2Trace) { tracingBusinessesSQL += " OR " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " = '" + s + "'"; }/*from www .j a va2 s .c om*/ String sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " WHERE " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Produkt") + " IS NULL " + " AND (" + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " IS NULL " + tracingBusinessesSQL + ")" + " ORDER BY " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " ASC," + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { do { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Fortrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("FwdTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell; String sid = null; if (rs.getObject("Lieferungen.Empfnger") != null) { sid = getStationLookup(rs.getString("Lieferungen.Empfnger")); cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Ingredients for Lot(s) row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); } rs.previous(); // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; int i = 0; row = sheetTracing.getRow(rowIndex); for (String dns : deliveryNumbers) { if (!dns.isEmpty()) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); //todo cell = row.getCell(4); cell.setCellValue(dns); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; } } Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); // Products Out row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; for (i = 0; i < 86; i++) { insertCondition(dvHelper, sheetTracing, rowIndex + i, 3, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 4, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 5, "1900", "3000"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 6, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 7, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 8, "1900", "3000"); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 9); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 10, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 11, "=StationIDs"); //row = sheetTracing.getRow(rowIndex+i); //cell = row.getCell(12); //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)"); //evaluator.evaluateFormulaCell(cell); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 0, "=LotNumbers"); } for (i = 0; i < deliveryNumbers.size(); i++) { insertDropBox(dvHelper, sheetTracing, 9 + i, 0, "=LotNumbers"); } //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID")); if (save(workbook, outputFolder + File.separator + "Fwdtrace_request_" + getValidFileName(rs.getString("Station.Serial")) + ".xlsx")) { // + "_" + getFormattedDate() result++; } myxls.close(); } while (rs.next()); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getFwdStationRequests(String outputFolder, Station station) throws SQLException, IOException { int result = 0; String sql = "Select * from " + MyDBI.delimitL("Station") + " AS " + MyDBI.delimitL("S") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + " WHERE " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " AND " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " IS NOT NULL" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; /*//from w w w. j a va 2 s . c o m String sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; */ //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Fortrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("FwdTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell; String sid = station.getId(); if (sid != null) { cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Ingredients for Lot(s) row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); List<Integer> dbLots = new ArrayList<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); dbLots.add(rs.getInt("ChargenVerbindungen.Produkt")); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); dbLots.add(rs.getInt("ChargenVerbindungen.Produkt")); } // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC"; rs = DBKernel.getResultSet(sql, false); int i = 0; row = sheetTracing.getRow(rowIndex); LinkedHashMap<Integer, String> lotDb2Number = new LinkedHashMap<>(); if (rs != null && rs.first()) { do { if (rs.getObject("Chargen.ID") != null && dbLots.contains(rs.getInt("Chargen.ID")) && !lotDb2Number.containsKey(rs.getInt("Chargen.ID"))) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); if (rs.getObject("Chargen.ChargenNr") != null) { cell = row.getCell(0); cell.setCellValue(rs.getString("Chargen.ChargenNr")); } if (rs.getObject("Chargen.Menge") != null) { cell = row.getCell(1); cell.setCellValue(rs.getDouble("Chargen.Menge")); } if (rs.getObject("Chargen.Einheit") != null) { cell = row.getCell(2); cell.setCellValue(rs.getString("Chargen.Einheit")); } if (rs.getObject("Produktkatalog.Bezeichnung") != null) { cell = row.getCell(3); cell.setCellValue(rs.getString("Produktkatalog.Bezeichnung")); } insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; lotDb2Number.put(rs.getInt("Chargen.ID"), rs.getString("Chargen.ChargenNr")); } } while (rs.next()); } if (i == 0) i = 1; Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); for (int ii = 0; ii < dbLots.size(); ii++) { if (lotDb2Number.containsKey(dbLots.get(ii))) { row = sheetTracing.getRow(9 + ii); cell = row.getCell(0); if (cell == null) cell = row.createCell(0); cell.setCellValue(lotDb2Number.get(dbLots.get(ii))); } insertDropBox(dvHelper, sheetTracing, 9 + ii, 0, "=LotNumbers"); } // Products Out row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; if (rs != null && rs.first() && rs.getObject("Chargen.ChargenNr") != null) { boolean didOnce = false; do { if (didOnce) row = copyRow(workbook, sheetTracing, rowIndex - 1, rowIndex); else row = sheetTracing.getRow(rowIndex); fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); rowIndex++; didOnce = true; } while (rs.next()); } for (i = 0; i < 85; i++) { doFormats(dvHelper, sheetTracing, rowIndex + i, evaluator); } if (save(workbook, outputFolder + File.separator + "StationFwdtrace_request_" + getValidFileName(station.getId()) + ".xlsx")) { // + "_" + getFormattedDate() result++; } myxls.close(); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getBackStationRequests(String outputFolder, Station station) throws SQLException, IOException { int result = 0; String sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC"; //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Backtrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("BackTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell;/* www.j a v a 2 s. c om*/ String sid = null; if (rs.getObject("Station.Serial") != null) { sid = getStationLookup(rs); cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Products Out row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashMap<String, Lot> lotNumbers = new LinkedHashMap<>(); LinkedHashMap<Integer, String> lotDb2Number = new LinkedHashMap<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } lotDb2Number.put(rs.getInt("Chargen.ID"), ln); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } lotDb2Number.put(rs.getInt("Chargen.ID"), ln); } rs.previous(); // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; int i = 0; row = sheetTracing.getRow(rowIndex); for (Lot lot : lotNumbers.values()) { if (lot != null && !lot.getNumber().isEmpty()) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); cell = row.getCell(0); cell.setCellValue(lot.getNumber()); if (lot.getUnitNumber() != null) { cell = row.getCell(1); cell.setCellValue(lot.getUnitNumber()); } if (lot.getUnitUnit() != null) { cell = row.getCell(2); cell.setCellValue(lot.getUnitUnit()); } if (lot.getProduct() != null && lot.getProduct().getName() != null) { cell = row.getCell(3); cell.setCellValue(lot.getProduct().getName()); } LinkedHashSet<String> le0 = new LinkedHashSet<>(); le0.add("Production Date"); le0.add("Best before date"); le0.add("Treatment of product during production"); le0.add("Sampling"); le0.addAll(le); fillExtraFields("Chargen", lot.getDbId(), row, le0, 13); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; } } Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); String sif = getValidFileName(rs.getString("Station.Serial")); // + "_" + getFormattedDate() // Ingredients for Lot(s) row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; sql = "Select * from " + MyDBI.delimitL("Station") + " AS " + MyDBI.delimitL("S") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + " WHERE " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " AND " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " IS NOT NULL" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; //System.out.println(sql); rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, null, lotDb2Number); doFormats(dvHelper, sheetTracing, rowIndex, evaluator); deliveryNumbers.add(dn); boolean didOnce = false; while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; if (didOnce) row = copyRow(workbook, sheetTracing, rowIndex - 1, rowIndex); else row = sheetTracing.getRow(rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, null, lotDb2Number); doFormats(dvHelper, sheetTracing, rowIndex, evaluator); deliveryNumbers.add(dn); didOnce = true; } rowIndex++; } for (i = 0; i < 84; i++) { doFormats(dvHelper, sheetTracing, rowIndex + i, evaluator); } //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID")); if (save(workbook, outputFolder + File.separator + "StationBacktrace_request_" + sif + ".xlsx")) { result++; } myxls.close(); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getBacktraceRequests(String outputFolder, List<String> business2Backtrace) throws SQLException, IOException { int result = 0; String sql;/* w w w . j a v a 2 s . c o m*/ String backtracingBusinessesSQL = ""; for (String s : business2Backtrace) { backtracingBusinessesSQL += " OR " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " = '" + s + "'"; } sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Produkt") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " WHERE " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " IS NULL " + " AND (" + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " IS NULL " + backtracingBusinessesSQL + ")" + " ORDER BY " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " ASC," + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC"; //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { do { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Backtrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("BackTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell; String sid = null; if (rs.getObject("Station.Serial") != null) { sid = getStationLookup(rs); cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Products Out row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashMap<String, Lot> lotNumbers = new LinkedHashMap<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } } rs.previous(); // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; int i = 0; row = sheetTracing.getRow(rowIndex); for (Lot lot : lotNumbers.values()) { if (lot != null && !lot.getNumber().isEmpty()) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); cell = row.getCell(0); cell.setCellValue(lot.getNumber()); if (lot.getUnitNumber() != null) { cell = row.getCell(1); cell.setCellValue(lot.getUnitNumber()); } if (lot.getUnitUnit() != null) { cell = row.getCell(2); cell.setCellValue(lot.getUnitUnit()); } if (lot.getProduct() != null && lot.getProduct().getName() != null) { cell = row.getCell(3); cell.setCellValue(lot.getProduct().getName()); } LinkedHashSet<String> le0 = new LinkedHashSet<>(); le0.add("Production Date"); le0.add("Best before date"); le0.add("Treatment of product during production"); le0.add("Sampling"); le0.addAll(le); fillExtraFields("Chargen", lot.getDbId(), row, le0, 13); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; } } Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); // Ingredients for Lot(s) row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; for (i = 0; i < 86; i++) { insertCondition(dvHelper, sheetTracing, rowIndex + i, 3, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 4, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 5, "1900", "3000"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 6, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 7, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 8, "1900", "3000"); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 9); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 10, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 11, "=StationIDs"); //row = sheetTracing.getRow(rowIndex+i); //cell = row.getCell(12); //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)"); //evaluator.evaluateFormulaCell(cell); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 0, "=LotNumbers"); } //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID")); if (save(workbook, outputFolder + File.separator + "Backtrace_request_" + getValidFileName(rs.getString("Station.Serial")) + ".xlsx")) { // + "_" + getFormattedDate() result++; } myxls.close(); } while (rs.next()); } return result; }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;// w ww. j a v a 2s . c o m //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }