List of usage examples for org.apache.poi.xssf.usermodel XSSFDataValidationHelper XSSFDataValidationHelper
public XSSFDataValidationHelper(XSSFSheet xssfSheet)
From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java
License:Apache License
private void createTicketTypeDropDown(Sheet formSheet, Sheet ticketTypeSheet, String[] ticketTypes) { XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet) formSheet); CellRangeAddressList ticketCellAddress = new CellRangeAddressList(7, 100, 6, 7); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(ticketTypeSheet.getSheetName() + "!$A$1:$A$" + ticketTypes.length); // constraint = validationHelper.createExplicitListConstraint(ticketTypes); DataValidation dataValidation = validationHelper.createValidation(constraint, ticketCellAddress); dataValidation.setSuppressDropDownArrow(true); formSheet.addValidationData(dataValidation); }
From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./*from ww w. j a v a 2 s. c om*/ * * @param colDesc 1717?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???1717 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?1717 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?.//w w w. j a v a2s . c om * * @param colDesc * "?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { // ??? List<String> colFieldList = getColumnFieldList(titlesList); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); // ? CellStyle dateCellStyle = getDateTimeCellStyle(wb); // ??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < titlesList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titlesList.get(i)); } ClassInfo classInfo = null; // ? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); if (classInfo == null) classInfo = ClassUtil.getClassInfo(obj.getClass()); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName); Object value = reflexField.getValue(obj); // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } // ?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date || value instanceof Timestamp) { cell.setCellStyle(dateCellStyle); cell.setCellValue(sdf.format((Date) value)); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } // ?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < titlesList.size(); i++) { // ?? sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./*w w w. java 2 s. com*/ * * @param colDesc ?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //??? //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //?? XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
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 + "'"; }// w ww . j a v a2s. 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 v a 2s. 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;// w ww .j av a 2s.c o m 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 ww .ja v a2 s. com 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:edu.casetools.rcase.extensions.excel.control.Exporter.java
License:Open Source License
private DataValidation createExcel2007CellStyle(Sheet sheet, String[] possibleValues, CellRangeAddressList addressList) { DataValidation validation;/* w ww.j av a2s . co m*/ DataValidationConstraint constraint; DataValidationHelper validationHelper; validationHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); constraint = validationHelper.createExplicitListConstraint(possibleValues); validation = validationHelper.createValidation(constraint, addressList); validation.setEmptyCellAllowed(false); validation.setShowErrorBox(true); validation.setErrorStyle(0); return validation; }
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 w w . j a v a 2 s . 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; }