List of usage examples for org.apache.poi.ss.usermodel Name setRefersToFormula
void setRefersToFormula(String formulaText);
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void writeNamedRegion(DataFrame data, String name, boolean header) { Name cname = getName(name); checkName(cname);/*from w w w. j a va2s .com*/ // Get sheet where name is defined in Sheet sheet = workbook.getSheet(cname.getSheetName()); AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get upper left corner CellReference topLeft = aref.getFirstCell(); // Compute bottom right cell coordinates int bottomRightRow = Math.max(topLeft.getRow() + data.rows() - 1, topLeft.getRow()); if (header) ++bottomRightRow; int bottomRightCol = Math.max(topLeft.getCol() + data.columns() - 1, topLeft.getCol()); // Create bottom right cell reference CellReference bottomRight = new CellReference(sheet.getSheetName(), bottomRightRow, bottomRightCol, true, true); // Define named range area aref = new AreaReference(topLeft, bottomRight); // Redefine named range cname.setRefersToFormula(aref.formatAsString()); writeData(data, sheet, topLeft.getRow(), topLeft.getCol(), header); }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
/** * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat} * //from w w w . j a v a2 s. c o m * @param cell * @param excell * @param style * @param font */ private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle, Workbook workbook, Font font, Font invisibleFont) { if (excell != null) { // [1] format cell formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont); // [2] set enum if (!excell.isConsiderEnum()) { if (StringUtils.isNotBlank(excell.getDisplayText())) { cell.setCellValue(excell.getDisplayText()); } if (!excell.isMultiSelect() && excell.isNumberValidation()) { addNumberValidation(cell); } } else { String[] list = (String[]) excell.getRestriction().getEnumValues() .toArray(new String[excell.getRestriction().getEnumValues().size()]); SXSSFSheet sheet = (SXSSFSheet) cell.getSheet(); DataValidationHelper dvHelper = sheet.getDataValidationHelper(); DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper .createExplicitListConstraint(list); CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()); DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.setSuppressDropDownArrow(true); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); try { if (isValidEnumList(list)) { sheet.addValidationData(dataValidation); } else { Sheet hidden = null; String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText()); Workbook wBook = cell.getSheet().getWorkbook(); if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) { hidden = wBook.getSheet(hiddenName); } else { hidden = wBook.createSheet(hiddenName); for (int i = 0, length = list.length; i < length; i++) { String name = list[i]; Row row = hidden.createRow(i); Cell cell1 = row.createCell(0); cell1.setCellValue(name); } Name namedCell = hidden.getWorkbook().getName(hiddenName); namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName(); namedCell.setNameName(hiddenName); namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length); } dvConstraint = (XSSFDataValidationConstraint) dvHelper .createFormulaListConstraint(hiddenName); dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); cell.getSheet().addValidationData(dataValidation); wBook.setSheetHidden(wBook.getSheetIndex(hidden), true); } } catch (Exception e) { String msg = "Excel creation failed while building cell: " + excell.getDisplayText(); throw new IllegalStateException(msg, e); } // cell.setCellValue(excelConfig.getDropDownMsg()); } } }
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 {//from w ww .jav a 2 s. c om 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 w ww. ja v a2 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 ww w . j a va2s . com*/ 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;//from w w w . ja va2 s . 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;//www . j a va 2s .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:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.TypeSheetGenerator.java
License:Open Source License
private void createNameListFormula() { ColumnContext nameColumn = sheetContext.getColumnByPersistentName(SheetContext.NAME_COLUMN); if (nameColumn != null) { Name rangeName = wbContext.getWb().createName(); rangeName.setNameName(ExcelGeneratorUtils.createNameForFormulaAllNames(getTypeExpression())); CellRangeAddress nameRange = CellRangeAddress.valueOf(nameColumn.getHeaderCellReference()); nameRange.setFirstRow(sheetContext.getFirstDataRowNumber()); nameRange.setLastRow(60000); // high constant last row number, so that manually added row entries will be considered as well, not only generated rows String nameRangeFormulaBase = "OFFSET({0},0,0,SUMPRODUCT(--({0}<>\"\")),1)"; // shows only non-empty entries in the name list String nameRangeFormula = MessageFormat.format(nameRangeFormulaBase, nameRange.formatAsString(sheetContext.getSheetName(), true)); rangeName.setRefersToFormula(nameRangeFormula); }/* w w w . j a v a 2 s. co m*/ }
From source file:mat.server.service.impl.XLSGenerator.java
/** Generate name. * /*from w w w .ja v a 2s . com*/ * @param wkbk - HSSFWorkbook. * @param nameStr - String. * @param referenceStr - String. * */ protected final void generateName(final HSSFWorkbook wkbk, final String nameStr, final String referenceStr) { // names are required for 508 testing Name name = wkbk.createName(); name.setNameName(nameStr); name.setRefersToFormula(referenceStr); }
From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java
License:LGPL
private void addWorkSheet(Workbook wb, String measure, List<FEMModelMeasure> measures, int maxTimePoints, Map<String, CellStyle> styles) { Sheet sheet = wb.createSheet(measure); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);//from ww w. j a va 2 s . co m sheet.setHorizontallyCenter(true); final int leadingHeaders = 4; int rowCounter = 0; // Create header row Row headerRow = sheet.createRow(rowCounter++); headerRow.setHeightInPoints(40); Cell headerCell; int colCtr = 0; { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ModelName"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("StartTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("EndTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MetaData"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ID"); headerCell.setCellStyle(styles.get("header")); } // Insert Time points for (int i = 0; i < maxTimePoints; i++) { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("" + i); headerCell.setCellStyle(styles.get("header")); } // Insert composite variables { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MAX"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MIN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MEAN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("SD"); headerCell.setCellStyle(styles.get("header")); } // Output the values for each measure for (FEMModelMeasure mes : measures) { double[][] strains = mes.getMeasure(measure); if (strains == null) continue; int numRows = strains.length + 1; // 1 for Avg int rowStarts = rowCounter; int colCounter = 0; for (int rctr = 0; rctr < numRows - 1; rctr++) { colCounter = 0; int myRowID = rowCounter + 1; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue("S" + (rctr + 1)); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[rctr].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); cell.setCellValue(strains[rctr][stc]); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { //String modelName = mes.getModelName(); //String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + "" + myRowID + ":" + strainEndXLColName + "" + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); /* * sname = * (modelName+"_"+measure+"_"+"MAX_S"+(rctr+1)..replaceAll * (" ", "_").trim(); Name namedCel = wb.createName(); * namedCel.setNameName(sname); String reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); /* * sname = modelName+"_"+measure+"_"+"MIN_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); /* * sname = modelName+"_"+measure+"_"+"AVERAGE_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); /* * sname = modelName+"_"+measure+"_"+"STDEV_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+""+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ } } // Add user defined series Hashtable<String, String> formulaMap = new Hashtable<String, String>(); for (String exp : userSeries) { // Replace all S[0-9]*, and D[0-9]* with appropriate column // values String toks[] = exp.split("="); int myRowID = rowCounter; colCounter = 0; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue(toks[0]); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[numRows - 2].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); // Get the expression String expression = toks[1].toLowerCase();// Regex is case // senstive, // since th // COLUMN // PREFIXs are // CAPS, // replaceAll // will work as // expected else // S17 will // mathc for S1 // (but not s1) for (int sCtr = mes.numSegments; sCtr > 0; sCtr--) { String XLColName = (char) ('A' + stc + leadingHeaders + 1) + "" + (rowStarts + sCtr); // Note // excel // formulas // need // base // 1 expression = expression.replaceAll("s" + sCtr + "{1}", XLColName); } cell.setCellFormula(expression); cell.setCellStyle(styles.get("AVGSERIES")); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { StringBuffer formulas = new StringBuffer(); String modelName = mes.getModelName(); char c = modelName.charAt(0); if (c >= '0' && c <= '9') { modelName = "_" + modelName; } String measureName = measure.replaceAll("\\(", "_").replaceAll("\\)", "_").replaceAll(" ", ""); String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + myRowID + ":" + strainEndXLColName + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); sname = (modelName + "_" + measureName + "_" + "MAX_" + toks[0]).replaceAll(" ", "_").trim(); Name namedCel = wb.createName(); namedCel.setNameName(sname); String reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base try { namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); sname = (modelName + "_" + measureName + "_" + "MIN_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); sname = (modelName + "_" + measureName + "_" + "AVERAGE_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); sname = (modelName + "_" + measureName + "_" + "STDEV_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference); formulaMap.put(toks[0], formulas.toString()); } catch (Exception exx) { //exx.printStackTrace(); System.out.println(exx + " occured for formula " + reference); } } } mes.addToFormulaMap(measure, formulaMap); // Set the commom columns Row row; Cell cell; row = sheet.getRow(rowStarts); cell = row.getCell(0); cell.setCellValue(mes.getModelName()); cell = row.getCell(1); cell.setCellValue(mes.getStartTime()); cell = row.getCell(2); cell.setCellValue(mes.getEndTime()); cell = row.getCell(3); cell.setCellValue(mes.getMetaData()); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (rowStarts + 1) + ":$A$" + (rowCounter))); // Since // excel // number // starts // from // 1 // but // api // is // 0 sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + (rowStarts + 1) + ":$B$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowStarts + 1) + ":$C$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + (rowStarts + 1) + ":$D$" + (rowCounter))); sheet.createRow(rowCounter++);// Create Empty row for model break } }