Example usage for org.apache.poi.xssf.usermodel XSSFCell setCellValue

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. setCellValue

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFCell setCellValue.

Prototype

@Override
public void setCellValue(boolean value)

Source Link

Document

Set a boolean value for the cell

Usage

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  w w .  j a  v  a2  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 ava 2 s.co 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;
        String sid = null;/*  w w w .java2 s.  c o  m*/
        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  w  w w  .j a  v  a2 s .  co 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.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private String fillRow(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, ResultSet rs, XSSFRow row,
        FormulaEvaluator evaluator, LinkedHashSet<String> de, Boolean isForward,
        LinkedHashMap<Integer, String> lotDb2Number) throws SQLException {
    String result = null;/*  www. j a va  2  s  .c  o m*/

    XSSFCell cell;
    if (isForward == null || isForward) {
        cell = row.getCell(1);
        if (rs.getObject("Produktkatalog.Bezeichnung") != null)
            cell.setCellValue(rs.getString("Produktkatalog.Bezeichnung"));
        else
            cell.setCellValue("");
        cell = row.getCell(2);
        if (rs.getObject("Chargen.ChargenNr") != null)
            cell.setCellValue(rs.getString("Chargen.ChargenNr"));
        else
            cell.setCellValue("(autoLot" + row.getRowNum() + ")");
        result = cell.getStringCellValue();
    } else {
        cell = row.getCell(0);
        if (rs.getObject("Chargen.ChargenNr") != null)
            cell.setCellValue(rs.getString("Chargen.ChargenNr"));
        else
            cell.setCellValue("(autoLot" + row.getRowNum() + ")");
        result = cell.getStringCellValue();
    }
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 3, "1", "31");
    cell = row.getCell(3);
    if (rs.getObject("Lieferungen.dd_day") != null)
        cell.setCellValue(rs.getInt("Lieferungen.dd_day"));
    else
        cell.setCellValue("");
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 4, "1", "12");
    cell = row.getCell(4);
    if (rs.getObject("Lieferungen.dd_month") != null)
        cell.setCellValue(rs.getInt("Lieferungen.dd_month"));
    else
        cell.setCellValue("");
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 5, "1900", "3000");
    cell = row.getCell(5);
    if (rs.getObject("Lieferungen.dd_year") != null)
        cell.setCellValue(rs.getInt("Lieferungen.dd_year"));
    else
        cell.setCellValue("");
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 6, "1", "31");
    cell = row.getCell(6);
    if (rs.getObject("Lieferungen.ad_day") != null)
        cell.setCellValue(rs.getInt("Lieferungen.ad_day"));
    else
        cell.setCellValue("");
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 7, "1", "12");
    cell = row.getCell(7);
    if (rs.getObject("Lieferungen.ad_month") != null)
        cell.setCellValue(rs.getInt("Lieferungen.ad_month"));
    else
        cell.setCellValue("");
    insertCondition(dvHelper, sheetTracing, row.getRowNum(), 8, "1900", "3000");
    cell = row.getCell(8);
    if (rs.getObject("Lieferungen.ad_year") != null)
        cell.setCellValue(rs.getInt("Lieferungen.ad_year"));
    else
        cell.setCellValue("");
    insertDecCondition(dvHelper, sheetTracing, row.getRowNum(), 9);
    cell = row.getCell(9);
    if (rs.getObject("Lieferungen.numPU") != null)
        cell.setCellValue(rs.getDouble("Lieferungen.numPU"));
    else
        cell.setCellValue("");
    insertDropBox(dvHelper, sheetTracing, row.getRowNum(), 10, "=Units");
    cell = row.getCell(10);
    if (rs.getObject("Lieferungen.typePU") != null)
        cell.setCellValue(rs.getString("Lieferungen.typePU"));
    else
        cell.setCellValue("");

    cell = row.getCell(11);
    String stationBez = "Lieferungen.Empfnger";
    if (isForward == null || isForward)
        stationBez = "Produktkatalog.Station";
    if (rs.getObject(stationBez) != null)
        cell.setCellValue(getStationLookup(rs.getString(stationBez)));
    else
        cell.setCellValue("");
    //cell = row.getCell(12);
    //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)");
    //evaluator.evaluateFormulaCell(cell);

    if (isForward == null) {
        cell = row.getCell(0);
        if (rs.getObject("ChargenVerbindungen.Produkt") != null && lotDb2Number != null
                && lotDb2Number.containsKey(rs.getInt("ChargenVerbindungen.Produkt")))
            cell.setCellValue(lotDb2Number.get(rs.getInt("ChargenVerbindungen.Produkt")));
        else
            cell.setCellValue("");
    }
    // DeliveryID
    cell = row.getCell(12);
    if (rs.getObject("Lieferungen.Serial") != null)
        cell.setCellValue(rs.getString("Lieferungen.Serial"));
    else if (rs.getObject("Lieferungen.ID") != null)
        cell.setCellValue(rs.getString("Lieferungen.ID"));
    else
        cell.setCellValue("");

    if (isForward == null || isForward)
        result = cell.getStringCellValue();

    fillExtraFields("Lieferungen", rs.getObject("Lieferungen.ID"), row, de, 13);
    /*
    // ExtraFields
    if (rs.getObject("Lieferungen.ID") != null) {
       String sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='Lieferungen' AND " + MyDBI.delimitL("id") + "=" + rs.getInt("Lieferungen.ID");
       ResultSet rs2 = DBKernel.getResultSet(sql, false);
       if (rs2 != null && rs2.first()) {
    do {
       String s = rs2.getString("attribute");
       int j=0;
       for (String e : de) {
          if (s.equals(e)) {
             cell = row.getCell(13+j);
             if (cell == null) cell = row.createCell(13+j);
             cell.setCellValue(rs2.getString("value"));
             break;
          }
          j++;
       }
    } while (rs2.next());
       }   
    }
    */

    return result;
}

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

License:Open Source License

private void fillExtraFields(String tablename, Object id, XSSFRow row, LinkedHashSet<String> de, int startCol)
        throws SQLException {
    // ExtraFields
    if (id != null) {
        String sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename")
                + "='" + tablename + "' AND " + MyDBI.delimitL("id") + "=" + id;
        ResultSet rs2 = DBKernel.getResultSet(sql, false);
        if (rs2 != null && rs2.first()) {
            do {// w  w  w  .  j  a  v  a2 s.  c om
                String s = rs2.getString("attribute");
                int j = 0;
                for (String e : de) {
                    if (s.equalsIgnoreCase(e)) {
                        XSSFCell cell = row.getCell(startCol + j);
                        if (cell == null)
                            cell = row.createCell(startCol + j);
                        cell.setCellValue(rs2.getString("value"));
                        break;
                    }
                    j++;
                }
            } while (rs2.next());
        }
    }
}

From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java

License:Open Source License

@SuppressWarnings("unchecked")
private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass,
        String i18nPrefix) {/*from w ww .  java2s . c  om*/
    String name = I18nProperties.getCaption(i18nPrefix);
    String safeName = WorkbookUtil.createSafeSheetName(name);
    XSSFSheet sheet = workbook.createSheet(safeName);

    // Create
    XSSFTable table = sheet.createTable();
    String safeTableName = safeName.replaceAll("\\s", "_");
    table.setName(safeTableName);
    table.setDisplayName(safeTableName);

    XssfHelper.styleTable(table, 1);

    int columnCount = EntityColumn.values().length;
    int rowNumber = 0;
    // header
    XSSFRow headerRow = sheet.createRow(rowNumber++);
    for (EntityColumn column : EntityColumn.values()) {
        table.addColumn();
        String columnCaption = column.toString();
        columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase();
        headerRow.createCell(column.ordinal()).setCellValue(columnCaption);
    }

    // column width
    sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60);
    sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10);
    sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45);
    sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10);

    CellStyle defaultCellStyle = workbook.createCellStyle();
    defaultCellStyle.setWrapText(true);

    List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>();

    for (Field field : entityClass.getDeclaredFields()) {
        if (java.lang.reflect.Modifier.isStatic(field.getModifiers()))
            continue;
        XSSFRow row = sheet.createRow(rowNumber++);

        // field name
        XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal());
        fieldNameCell.setCellValue(field.getName());

        // value range
        XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal());
        fieldValueCell.setCellStyle(defaultCellStyle);
        Class<?> fieldType = field.getType();
        if (fieldType.isEnum()) {
            // use enum type name - values are added below
            //            Object[] enumValues = fieldType.getEnumConstants();
            //            StringBuilder valuesString = new StringBuilder();
            //            for (Object enumValue : enumValues) {
            //               if (valuesString.length() > 0)
            //                  valuesString.append(", ");
            //               valuesString.append(((Enum) enumValue).name());
            //            }
            //            fieldValueCell.setCellValue(valuesString.toString());
            fieldValueCell.setCellValue(fieldType.getSimpleName());
            if (!usedEnums.contains(fieldType)) {
                usedEnums.add((Class<Enum<?>>) fieldType);
            }
        } else if (EntityDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (ReferenceDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (String.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("text"));
        } else if (Date.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("date"));
        } else if (Number.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("number"));
        } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString());
        }

        // caption
        XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal());
        captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), ""));

        // description
        XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal());
        descriptionCell.setCellStyle(defaultCellStyle);
        descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), ""));

        // required
        XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal());
        if (field.getAnnotation(Required.class) != null)
            requiredCell.setCellValue(true);

        // diseases
        XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal());
        diseasesCell.setCellStyle(defaultCellStyle);
        Diseases diseases = field.getAnnotation(Diseases.class);
        if (diseases != null) {
            StringBuilder diseasesString = new StringBuilder();
            for (Disease disease : diseases.value()) {
                if (diseasesString.length() > 0)
                    diseasesString.append(", ");
                diseasesString.append(disease.toShortString());
            }
            diseasesCell.setCellValue(diseasesString.toString());
        } else {
            diseasesCell.setCellValue("All");
        }

        // outbreak
        XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal());
        if (field.getAnnotation(Outbreaks.class) != null)
            outbreakCell.setCellValue(true);
    }

    AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0),
            new CellReference(rowNumber - 1, columnCount - 1));
    table.setCellReferences(reference);
    table.getCTTable().addNewAutoFilter();

    for (Class<Enum<?>> usedEnum : usedEnums) {
        rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum);
    }

    return sheet;
}

From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java

License:Open Source License

private int createEnumTable(XSSFSheet sheet, int startRow, Class<Enum<?>> enumType) {

    // Create/*from w w w. j  a v  a2s  .  co  m*/
    XSSFTable table = sheet.createTable();
    String safeTableName = (sheet.getSheetName() + enumType.getSimpleName()).replaceAll("\\s", "_");
    table.setName(safeTableName);
    table.setDisplayName(safeTableName);
    XssfHelper.styleTable(table, 2);

    int columnCount = EnumColumn.values().length;
    int rowNumber = startRow;

    // header
    XSSFRow headerRow = sheet.createRow(rowNumber++);
    for (EnumColumn column : EnumColumn.values()) {
        table.addColumn();
        String columnCaption = column.toString();
        columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase();
        headerRow.createCell(column.ordinal()).setCellValue(columnCaption);
    }

    Object[] enumValues = enumType.getEnumConstants();
    for (Object enumValueObject : enumValues) {
        XSSFRow row = sheet.createRow(rowNumber++);
        XSSFCell cell;
        Enum<?> enumValue = ((Enum<?>) enumValueObject);

        cell = row.createCell(EnumColumn.TYPE.ordinal());
        if (enumValueObject == enumValues[0]) {
            cell.setCellValue(enumType.getSimpleName());
        }

        cell = row.createCell(EnumColumn.VALUE.ordinal());
        cell.setCellValue(enumValue.name());

        cell = row.createCell(EnumColumn.CAPTION.ordinal());
        String caption = enumValue.toString();
        cell.setCellValue(caption);

        cell = row.createCell(EnumColumn.DESCRIPTION.ordinal());
        String desc = I18nProperties.getEnumDescription(enumValue);
        cell.setCellValue(DataHelper.equal(caption, desc) ? "" : desc);

        cell = row.createCell(EnumColumn.SHORT.ordinal());
        String shortCaption = I18nProperties.getEnumCaptionShort(enumValue);
        cell.setCellValue(DataHelper.equal(caption, shortCaption) ? "" : shortCaption);
    }

    AreaReference reference = new AreaReference(new CellReference(startRow, 0),
            new CellReference(rowNumber - 1, columnCount - 1), SpreadsheetVersion.EXCEL2007);
    table.setCellReferences(reference);
    table.getCTTable().addNewAutoFilter();

    return rowNumber;
}

From source file:de.symeda.sormas.api.doc.XssfHelper.java

License:Open Source License

public static void addAboutSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("About");
    XSSFRow row = sheet.createRow(0);//from w  ww  .jav  a  2s .co m
    XSSFCell cell = row.createCell(0);
    cell.setCellValue("SORMAS Version");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(InfoProvider.get().getVersion());
}

From source file:de.tuttas.servlets.MyTableDataModel.java

public XSSFWorkbook toExcel(XSSFWorkbook wb, int sheetNumer) {
    XSSFSheet sh = wb.getSheetAt(sheetNumer);
    for (int y = 0; y < rows; y++) {
        XSSFRow r = sh.getRow(y);//from w  ww .java 2s .co  m
        for (int x = 0; x < cols; x++) {
            XSSFCell c = r.getCell(x);
            String d = data[y][x];
            Log.d("Write to Cell " + d);
            if (d != null) {
                try {
                    double value = Double.parseDouble(d);
                    c.setCellValue(value);
                } catch (NumberFormatException nux) {
                    c.setCellValue(d);
                }
            }

        }
    }
    return wb;
}