Example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell

List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. FormulaEvaluator
  6. evaluateFormulaCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell.

Prototype

CellType evaluateFormulaCell(Cell cell);

Source Link

Document

If cell contains formula, it evaluates the formula, and saves the result of the formula.

Usage

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);/*from ww w  .j  av  a 2s.c  om*/

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createContent(Workbook wb, Sheet s, String map) {
    Map<String, String> out = new HashMap<String, String>();

    int rownum = 2;
    int cellnum = 0;

    // create a merged list of domains.
    domains.clear();/*from w  ww .j ava2  s .c  o m*/
    for (String database : results.keySet()) {
        if (database.equals("baseline")) {
            Analyzer ra = results.get(database);
            Map<String, Integer> mapToUse = this.getMap(map, ra);

            for (String domain : mapToUse.keySet()) {
                if ((!domains.contains(domain)) && !exclusions.contains(domain)) {
                    domains.add(domain);
                    out.put(domain, "");
                }
            }
        }
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));
    s.setColumnWidth(0, 5000);

    for (String domain : domains) {
        cellnum = 0;

        Row r = s.createRow(rownum);
        Cell c = r.createCell(cellnum);
        c.setCellValue(domain);
        cellnum++;

        for (String database : results.keySet()) {
            Analyzer ra = results.get(database);

            Map<String, Integer> mapToUse = this.getMap(map, ra);

            c = r.createCell(cellnum);
            try {
                if (mapToUse.containsKey(domain)) {
                    c.setCellValue(mapToUse.get(domain));
                } else {
                    c.setCellValue(0);
                }
            } catch (Exception e) {
                c.setCellValue(0);
            }

            c.setCellStyle(numberStyle);

            cellnum++;
        }
        rownum++;
    }

    // Totals.
    rownum++;
    cellnum = 1;
    Row r = s.createRow(rownum);

    Cell c = r.createCell(0);
    c.setCellValue("Totals:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }

    // Delta/Reduction
    rownum++;
    cellnum = 1;
    r = s.createRow(rownum);

    c = r.createCell(0);
    c.setCellValue("Tracking Decrease:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

public void createSpreadSheet() throws Exception {
    int row = 2, cell = 0, sheet = 0;
    FileOutputStream file = new FileOutputStream(path + "analysis.xls");

    Workbook wb = new HSSFWorkbook();

    // content: total content length sheet.
    Sheet s = wb.createSheet();//w w w.ja  v  a2  s .  c o  m
    wb.setSheetName(sheet, "Content Length");
    this.createHeader(wb, s, "Total Content Length in MB", 0);

    Row r = s.createRow(row);
    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        c.setCellValue(results.get(database).totalContentLength / 1024 / 1024);
        cell++;
    }

    row++;
    cell = 0;
    r = s.createRow(row);

    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        if (database.equals("baseline")) {
            c.setCellValue("Decrease:");

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), "0");
            decrease.put(database, contents);
        } else {
            c = r.createCell(cell);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)");

            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateFormulaCell(c);

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");
            decrease.put(database, contents);
        }
        cell++;
    }
    sheet++;

    // When content is created, baseline is used as a base for every entry. For example,
    // if baseline contained doubleclick.com, this will be output and each other analyzer's
    // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick.
    // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never
    // shown in the spreadsheet or any other results.

    // so this means if we have tracker/whatever URLs in a non-baseline profile
    // and these URLs are NOT in the baseline profile,
    // we wouldn't see those trackers/whatever in the final comparison.

    // content: HTTP Requests
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Requests");
    this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1);
    this.createContent(wb, s, "requestCountPerDomain");
    sheet++;

    // content: HTTP Set-Cookie Responses
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Set-Cookie Responses");
    this.createHeader(wb, s,
            "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1);
    this.createContent(wb, s, "setCookieResponses");
    sheet++;

    // content: Cookie Added - Cookie Deleted
    s = wb.createSheet();
    wb.setSheetName(sheet, "Cookies Added-Deleted");
    this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1);
    this.createContent(wb, s, "cookieTotals");
    sheet++;

    // content: Local Storage counts per domain
    s = wb.createSheet();
    wb.setSheetName(sheet, "Local Storage");
    this.createHeader(wb, s, "Local Storage counts per domain", 1);
    this.createContent(wb, s, "localStorageContents");
    sheet++;

    // content: Pretty Chart
    s = wb.createSheet();
    wb.setSheetName(sheet, "Overall");

    int rownum = 0, cellnum = 0;

    // Header
    r = s.createRow(rownum);
    Cell c = r.createCell(0);
    s.setColumnWidth(0, 8000);
    c.setCellValue(
            "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)");

    rownum++;
    r = s.createRow(rownum);

    cellnum++;

    for (String database : decrease.keySet()) {
        if (database.equals("baseline")) {
            continue;
        }

        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));

    // Content
    for (String type : decrease.get("baseline").keySet()) {
        cellnum = 0;
        rownum++;

        r = s.createRow(rownum);

        c = r.createCell(cellnum);
        c.setCellValue(type);
        cellnum++;

        for (String database : decrease.keySet()) {
            if (database.equals("baseline")) {
                continue;
            }

            c = r.createCell(cellnum);
            c.setCellStyle(numberStyle);

            double decreaseValue = Double.parseDouble(decrease.get(database).get(type));

            if (decreaseValue < 0)
                decreaseValue = 0;

            c.setCellValue(decreaseValue);
            cellnum++;
        }
    }

    /*
    for (String database : decrease.keySet()) {
       for (String type : decrease.get(database).keySet()) {
    System.out.println(database + "|" + type + "|" + decrease.get(database).get(type));
       }
    }
    */

    wb.write(file);
    file.close();
}

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

private void evaluate(Sheet sheet, FormulaEvaluator evaluator) {
    for (int i = 0; i < ROW_MAX; i++) {
        Row row = sheet.getRow(i);//from  w  ww . j  a va  2s  .c o m
        if (row == null) {
            continue;
        }
        for (int j = 0; j < COL_MAX; j++) {
            Cell cell = row.getCell(j);
            if (cell == null) {
                continue;
            }

            if (cell.getCellTypeEnum() == CellType.FORMULA) {
                evaluator.evaluateFormulaCell(cell);
            }
        }
    }
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

private static <T> Map<String, String> genRowMap(Row row, List<String> cols, FormulaEvaluator evaluator) {
    Map<String, String> map = new HashMap<String, String>();
    int j = 0;//from   w ww .j ava 2  s .  co m
    for (String col : cols) {
        Cell cell = row.getCell(j);// ??
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            map.put(col, String.valueOf(cell.getBooleanCellValue()));
            break;
        case Cell.CELL_TYPE_NUMERIC:
            map.put(col, String.valueOf(cell.getNumericCellValue()));

            break;
        case Cell.CELL_TYPE_STRING:

            map.put(col, String.valueOf(cell.getStringCellValue()));
            break;
        case Cell.CELL_TYPE_BLANK:
            map.put(col, String.valueOf(""));

            break;
        case Cell.CELL_TYPE_ERROR:
            map.put(col, String.valueOf("error"));
            break;

        // CELL_TYPE_FORMULA will never occur  
        case Cell.CELL_TYPE_FORMULA:

            evaluator.evaluateFormulaCell(cell);
            map.put(col, String.valueOf(cell.getNumericCellValue()));
            break;

        }
        j++;
    }

    return map;
}

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  w  w .j a va 2  s  . c  o  m
    String sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen")
            + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "="
            + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN "
            + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "."
            + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel")
            + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "."
            + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger")
            + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Lieferungen")
            + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "."
            + MyDBI.delimitL("Zutat") + " WHERE " + MyDBI.delimitL("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  ww.  j a v  a 2  s. c  o  m
    String sql = "Select * from " + MyDBI.delimitL("Lieferungen") +
    " LEFT JOIN " + MyDBI.delimitL("Chargen") +
    " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") +
    " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") +
    " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") +
    " LEFT JOIN " + MyDBI.delimitL("Station") +
    " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") +
    " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") +
    " ON " + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") +
    " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" +
    " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC";
    */
    //System.err.println(sql);
    ResultSet rs = DBKernel.getResultSet(sql, false);
    if (rs != null && rs.first()) {
        InputStream myxls = this.getClass().getResourceAsStream(
                "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Fortrace_sug.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(myxls);
        XSSFSheet sheetTracing = workbook.getSheet("FwdTracing");
        XSSFSheet sheetStations = workbook.getSheet("Stations");
        XSSFSheet sheetLookup = workbook.getSheet("LookUp");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        fillStations(sheetStations, evaluator);
        fillLookup(workbook, sheetLookup);
        LinkedHashSet<String> le = getLotExtra();
        LinkedHashSet<String> de = getDeliveryExtra();

        // Station in Focus
        XSSFRow row = sheetTracing.getRow(4);
        XSSFCell cell;
        String sid = station.getId();
        if (sid != null) {
            cell = row.getCell(1);
            cell.setCellValue(sid);
            cell = row.getCell(2);
            evaluator.evaluateFormulaCell(cell);
        }

        // Ingredients for Lot(s)
        row = sheetTracing.getRow(7);
        int j = 0;
        for (String e : de) {
            if (e != null && !e.isEmpty()) {
                cell = row.getCell(13 + j);
                if (cell == null)
                    cell = row.createCell(13 + j);
                cell.setCellValue(e);
                j++;
            }
        }

        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing);
        LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>();
        List<Integer> dbLots = new ArrayList<>();
        int rowIndex = 9;
        row = sheetTracing.getRow(rowIndex);
        String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null);
        deliveryNumbers.add(dn);
        dbLots.add(rs.getInt("ChargenVerbindungen.Produkt"));

        while (rs.next()) {
            if (rs.getObject("Station.Serial") == null)
                break;
            String sl = getStationLookup(rs);
            if (!sl.equals(sid))
                break;
            rowIndex++;
            row = copyRow(workbook, sheetTracing, 9, rowIndex);
            dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null);
            deliveryNumbers.add(dn);
            dbLots.add(rs.getInt("ChargenVerbindungen.Produkt"));
        }

        // Lot Information
        row = sheetTracing.getRow(rowIndex + 3);
        j = 0;
        for (String e : le) {
            if (e != null && !e.isEmpty()) {
                cell = row.getCell(17 + j);
                if (cell == null)
                    cell = row.createCell(17 + j);
                cell.setCellValue(e);
                j++;
            }
        }

        rowIndex += 5;
        sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog")
                + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "="
                + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN "
                + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "."
                + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel")
                + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "."
                + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge")
                + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '"
                + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Chargen") + "."
                + MyDBI.delimitL("ChargenNr") + " ASC";
        rs = DBKernel.getResultSet(sql, false);
        int i = 0;
        row = sheetTracing.getRow(rowIndex);
        LinkedHashMap<Integer, String> lotDb2Number = new LinkedHashMap<>();
        if (rs != null && rs.first()) {
            do {
                if (rs.getObject("Chargen.ID") != null && dbLots.contains(rs.getInt("Chargen.ID"))
                        && !lotDb2Number.containsKey(rs.getInt("Chargen.ID"))) {
                    if (i > 0)
                        row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i);
                    if (rs.getObject("Chargen.ChargenNr") != null) {
                        cell = row.getCell(0);
                        cell.setCellValue(rs.getString("Chargen.ChargenNr"));
                    }
                    if (rs.getObject("Chargen.Menge") != null) {
                        cell = row.getCell(1);
                        cell.setCellValue(rs.getDouble("Chargen.Menge"));
                    }
                    if (rs.getObject("Chargen.Einheit") != null) {
                        cell = row.getCell(2);
                        cell.setCellValue(rs.getString("Chargen.Einheit"));
                    }
                    if (rs.getObject("Produktkatalog.Bezeichnung") != null) {
                        cell = row.getCell(3);
                        cell.setCellValue(rs.getString("Produktkatalog.Bezeichnung"));
                    }

                    insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1);
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units");
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment");
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling");
                    i++;
                    lotDb2Number.put(rs.getInt("Chargen.ID"), rs.getString("Chargen.ChargenNr"));
                }
            } while (rs.next());
        }
        if (i == 0)
            i = 1;

        Name reference = workbook.createName();
        reference.setNameName("LotNumbers");
        String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$"
                + (rowIndex + i);
        reference.setRefersToFormula(referenceString);

        for (int ii = 0; ii < dbLots.size(); ii++) {
            if (lotDb2Number.containsKey(dbLots.get(ii))) {
                row = sheetTracing.getRow(9 + ii);
                cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                cell.setCellValue(lotDb2Number.get(dbLots.get(ii)));
            }
            insertDropBox(dvHelper, sheetTracing, 9 + ii, 0, "=LotNumbers");
        }

        // Products Out
        row = sheetTracing.getRow(rowIndex + i + 2);
        j = 0;
        for (String e : de) {
            if (e != null && !e.isEmpty()) {
                cell = row.getCell(13 + j);
                if (cell == null)
                    cell = row.createCell(13 + j);
                cell.setCellValue(e);
                j++;
            }
        }

        rowIndex += i + 4;

        if (rs != null && rs.first() && rs.getObject("Chargen.ChargenNr") != null) {
            boolean didOnce = false;
            do {
                if (didOnce)
                    row = copyRow(workbook, sheetTracing, rowIndex - 1, rowIndex);
                else
                    row = sheetTracing.getRow(rowIndex);
                fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null);
                rowIndex++;
                didOnce = true;
            } while (rs.next());
        }

        for (i = 0; i < 85; i++) {
            doFormats(dvHelper, sheetTracing, rowIndex + i, evaluator);
        }

        if (save(workbook, outputFolder + File.separator + "StationFwdtrace_request_"
                + getValidFileName(station.getId()) + ".xlsx")) { //  + "_" + getFormattedDate()
            result++;
        }
        myxls.close();
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private int getBackStationRequests(String outputFolder, Station station) throws SQLException, IOException {
    int result = 0;
    String sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog")
            + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "="
            + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN "
            + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID")
            + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN "
            + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID")
            + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " WHERE "
            + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'"
            + " ORDER BY " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC";
    //System.err.println(sql);
    ResultSet rs = DBKernel.getResultSet(sql, false);
    if (rs != null && rs.first()) {
        InputStream myxls = this.getClass().getResourceAsStream(
                "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Backtrace_sug.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(myxls);
        XSSFSheet sheetTracing = workbook.getSheet("BackTracing");
        XSSFSheet sheetStations = workbook.getSheet("Stations");
        XSSFSheet sheetLookup = workbook.getSheet("LookUp");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        fillStations(sheetStations, evaluator);
        fillLookup(workbook, sheetLookup);
        LinkedHashSet<String> le = getLotExtra();
        LinkedHashSet<String> de = getDeliveryExtra();

        // Station in Focus
        XSSFRow row = sheetTracing.getRow(4);
        XSSFCell cell;//from w  ww.  ja  v  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;//from  ww  w. j av  a2s .  c o m
    String backtracingBusinessesSQL = "";
    for (String s : business2Backtrace) {
        backtracingBusinessesSQL += " OR " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart")
                + " = '" + s + "'";
    }
    sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON "
            + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "."
            + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON "
            + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "="
            + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Produkt") + " LEFT JOIN "
            + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "."
            + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel")
            + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "."
            + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station")
            + " WHERE " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " IS NULL "
            + " AND (" + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " IS NULL "
            + backtracingBusinessesSQL + ")" + " ORDER BY " + MyDBI.delimitL("Station") + "."
            + MyDBI.delimitL("ID") + " ASC," + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr")
            + " ASC";
    //System.err.println(sql);
    ResultSet rs = DBKernel.getResultSet(sql, false);
    if (rs != null && rs.first()) {
        do {
            InputStream myxls = this.getClass().getResourceAsStream(
                    "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Backtrace_sug.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(myxls);
            XSSFSheet sheetTracing = workbook.getSheet("BackTracing");
            XSSFSheet sheetStations = workbook.getSheet("Stations");
            XSSFSheet sheetLookup = workbook.getSheet("LookUp");
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            fillStations(sheetStations, evaluator);
            fillLookup(workbook, sheetLookup);
            LinkedHashSet<String> le = getLotExtra();
            LinkedHashSet<String> de = getDeliveryExtra();

            // Station in Focus
            XSSFRow row = sheetTracing.getRow(4);
            XSSFCell cell;
            String sid = null;
            if (rs.getObject("Station.Serial") != null) {
                sid = getStationLookup(rs);
                cell = row.getCell(1);
                cell.setCellValue(sid);
                cell = row.getCell(2);
                evaluator.evaluateFormulaCell(cell);
            }

            // Products Out
            row = sheetTracing.getRow(7);
            int j = 0;
            for (String e : de) {
                if (e != null && !e.isEmpty()) {
                    cell = row.getCell(13 + j);
                    if (cell == null)
                        cell = row.createCell(13 + j);
                    cell.setCellValue(e);
                    j++;
                }
            }

            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing);
            LinkedHashMap<String, Lot> lotNumbers = new LinkedHashMap<>();
            int rowIndex = 9;
            row = sheetTracing.getRow(rowIndex);
            String ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null);
            if (!lotNumbers.containsKey(ln)) {
                Lot l = new Lot();
                l.setNumber(ln);
                if (rs.getObject("Chargen.Menge") != null)
                    l.setUnitNumber(rs.getDouble("Chargen.Menge"));
                if (rs.getObject("Chargen.Einheit") != null)
                    l.setUnitUnit(rs.getString("Chargen.Einheit"));
                if (rs.getObject("Produktkatalog.Bezeichnung") != null) {
                    Product p = new Product();
                    p.setName(rs.getString("Produktkatalog.Bezeichnung"));
                    l.setProduct(p);
                }
                l.setDbId(rs.getInt("Chargen.ID"));
                lotNumbers.put(ln, l);
            }

            while (rs.next()) {
                if (rs.getObject("Station.Serial") == null)
                    break;
                String sl = getStationLookup(rs);
                if (!sl.equals(sid))
                    break;
                rowIndex++;
                row = copyRow(workbook, sheetTracing, 9, rowIndex);
                ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null);
                if (!lotNumbers.containsKey(ln)) {
                    Lot l = new Lot();
                    l.setNumber(ln);
                    if (rs.getObject("Chargen.Menge") != null)
                        l.setUnitNumber(rs.getDouble("Chargen.Menge"));
                    if (rs.getObject("Chargen.Einheit") != null)
                        l.setUnitUnit(rs.getString("Chargen.Einheit"));
                    if (rs.getObject("Produktkatalog.Bezeichnung") != null) {
                        Product p = new Product();
                        p.setName(rs.getString("Produktkatalog.Bezeichnung"));
                        l.setProduct(p);
                    }
                    l.setDbId(rs.getInt("Chargen.ID"));
                    lotNumbers.put(ln, l);
                }
            }
            rs.previous();

            // Lot Information
            row = sheetTracing.getRow(rowIndex + 3);
            j = 0;
            for (String e : le) {
                if (e != null && !e.isEmpty()) {
                    cell = row.getCell(17 + j);
                    if (cell == null)
                        cell = row.createCell(17 + j);
                    cell.setCellValue(e);
                    j++;
                }
            }

            rowIndex += 5;
            int i = 0;
            row = sheetTracing.getRow(rowIndex);
            for (Lot lot : lotNumbers.values()) {
                if (lot != null && !lot.getNumber().isEmpty()) {
                    if (i > 0)
                        row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i);
                    cell = row.getCell(0);
                    cell.setCellValue(lot.getNumber());
                    if (lot.getUnitNumber() != null) {
                        cell = row.getCell(1);
                        cell.setCellValue(lot.getUnitNumber());
                    }
                    if (lot.getUnitUnit() != null) {
                        cell = row.getCell(2);
                        cell.setCellValue(lot.getUnitUnit());
                    }
                    if (lot.getProduct() != null && lot.getProduct().getName() != null) {
                        cell = row.getCell(3);
                        cell.setCellValue(lot.getProduct().getName());
                    }
                    LinkedHashSet<String> le0 = new LinkedHashSet<>();
                    le0.add("Production Date");
                    le0.add("Best before date");
                    le0.add("Treatment of product during production");
                    le0.add("Sampling");
                    le0.addAll(le);
                    fillExtraFields("Chargen", lot.getDbId(), row, le0, 13);
                    insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1);
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units");
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment");
                    insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling");
                    i++;
                }
            }

            Name reference = workbook.createName();
            reference.setNameName("LotNumbers");
            String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$"
                    + (rowIndex + i);
            reference.setRefersToFormula(referenceString);

            // Ingredients for Lot(s)
            row = sheetTracing.getRow(rowIndex + i + 2);
            j = 0;
            for (String e : de) {
                if (e != null && !e.isEmpty()) {
                    cell = row.getCell(13 + j);
                    if (cell == null)
                        cell = row.createCell(13 + j);
                    cell.setCellValue(e);
                    j++;
                }
            }

            rowIndex += i + 4;
            for (i = 0; i < 86; i++) {
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 3, "1", "31");
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 4, "1", "12");
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 5, "1900", "3000");
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 6, "1", "31");
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 7, "1", "12");
                insertCondition(dvHelper, sheetTracing, rowIndex + i, 8, "1900", "3000");
                insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 9);
                insertDropBox(dvHelper, sheetTracing, rowIndex + i, 10, "=Units");
                insertDropBox(dvHelper, sheetTracing, rowIndex + i, 11, "=StationIDs");
                //row = sheetTracing.getRow(rowIndex+i);
                //cell = row.getCell(12);
                //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)");
                //evaluator.evaluateFormulaCell(cell);
                insertDropBox(dvHelper, sheetTracing, rowIndex + i, 0, "=LotNumbers");
            }

            //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID"));
            if (save(workbook, outputFolder + File.separator + "Backtrace_request_"
                    + getValidFileName(rs.getString("Station.Serial")) + ".xlsx")) { //  + "_" + getFormattedDate()
                result++;
            }
            myxls.close();
        } while (rs.next());
    }
    return result;
}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

public List<CellDefinition> evaluateWorkbook() {
    final List<CellDefinition> rv = new ArrayList<CellDefinition>();

    boolean reevaluate = false;
    if (workbook instanceof HSSFWorkbook) {
        try {//from  w  ww.  jav a2  s.com
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        } catch (Exception e) {
            reevaluate = true;
        }
    }

    final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook,
            IStabilityClassifier.TOTALLY_IMMUTABLE);
    formulaEvaluator.clearAllCachedResultValues();

    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        final Sheet sheet = workbook.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    try {
                        formulaEvaluator.evaluateFormulaCell(cell);
                    } catch (Exception e) {
                        ReportEngine.logger.log(Level.WARNING,
                                String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s",
                                        cell.getCellFormula(), CellReferenceHelper
                                                .getCellReference(cell.getColumnIndex(), row.getRowNum()),
                                        sheet.getSheetName(), e.getMessage()));
                    }
                }

                final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey(
                        new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell)
                                : null;
                if (cellDefinition != null)
                    rv.add(cellDefinition);
            }
        }
    }

    return rv;
}