List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private void fillStations(XSSFSheet sheetStations, FormulaEvaluator evaluator) throws SQLException { LinkedHashSet<String> se = getStationExtra(); XSSFRow row = sheetStations.getRow(0); int j = 0;//from w w w . j av a 2s . co m for (String e : se) { if (e != null && !e.isEmpty()) { XSSFCell cell = row.getCell(11 + j); if (cell == null) cell = row.createCell(11 + j); cell.setCellValue(e); j++; } } String sql = "Select * from " + MyDBI.delimitL("Station") + " ORDER BY " + MyDBI.delimitL("Serial") + " ASC"; ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { int rownum = 1; do { row = sheetStations.getRow(rownum); if (row == null) row = sheetStations.createRow(rownum); rownum++; XSSFCell cell; if (rs.getObject("Serial") != null) { cell = row.createCell(0); cell.setCellValue(rs.getString("Serial")); } else if (rs.getObject("ID") != null) { cell = row.createCell(0); cell.setCellValue(rs.getString("ID")); } if (rs.getObject("Name") != null) { cell = row.createCell(1); cell.setCellValue(rs.getString("Name")); } if (rs.getObject("Strasse") != null) { cell = row.createCell(2); cell.setCellValue(rs.getString("Strasse")); } if (rs.getObject("Hausnummer") != null) { cell = row.createCell(3); cell.setCellValue(rs.getString("Hausnummer")); } if (rs.getObject("PLZ") != null) { cell = row.createCell(4); cell.setCellValue(rs.getString("PLZ")); } if (rs.getObject("Ort") != null) { cell = row.createCell(5); cell.setCellValue(rs.getString("Ort")); } if (rs.getObject("District") != null) { cell = row.createCell(6); cell.setCellValue(rs.getString("District")); } if (rs.getObject("Bundesland") != null) { cell = row.createCell(7); cell.setCellValue(rs.getString("Bundesland")); } if (rs.getObject("Land") != null) { cell = row.createCell(8); cell.setCellValue(rs.getString("Land")); } if (rs.getObject("Betriebsart") != null) { cell = row.createCell(9); cell.setCellValue(rs.getString("Betriebsart")); } //cell = row.getCell(10); evaluator.evaluateFormulaCell(cell); fillExtraFields("Station", rs.getObject("ID"), row, se, 11); /* if (rs.getObject("ID") != null) { sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='Station' AND " + MyDBI.delimitL("id") + "=" + rs.getInt("ID"); ResultSet rs2 = DBKernel.getResultSet(sql, false); if (rs2 != null && rs2.first()) { do { String s = rs2.getString("attribute"); j=0; for (String e : se) { if (s.equals(e)) { cell = row.getCell(11+j); if (cell == null) cell = row.createCell(11+j); cell.setCellValue(rs2.getString("value")); break; } j++; } } while (rs2.next()); } } */ } while (rs.next()); } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private void fillLookup(XSSFWorkbook workbook, XSSFSheet sheetLookup) throws SQLException { String sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Sampling'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; ResultSet rs = DBKernel.getResultSet(sql, false); int rownum = 1; if (rs != null && rs.first()) { do {// ww w . ja va 2s . c o m XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } Name reference = workbook.createName(); reference.setNameName("Sampling"); String referenceString = sheetLookup.getSheetName() + "!$A$2:$A$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='TypeOfBusiness'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(1); if (cell == null) cell = row.createCell(1); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("ToB"); referenceString = sheetLookup.getSheetName() + "!$B$2:$B$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Treatment'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(2); if (cell == null) cell = row.createCell(2); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Treatment"); referenceString = sheetLookup.getSheetName() + "!$C$2:$C$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Units'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Units"); referenceString = sheetLookup.getSheetName() + "!$D$2:$D$" + (rownum); reference.setRefersToFormula(referenceString); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getFortraceRequests(String outputFolder, List<String> business2Trace) throws SQLException, IOException { int result = 0; String tracingBusinessesSQL = ""; for (String s : business2Trace) { tracingBusinessesSQL += " OR " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " = '" + s + "'"; }// w ww.j av a2s. com String sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " WHERE " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Produkt") + " IS NULL " + " AND (" + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Betriebsart") + " IS NULL " + tracingBusinessesSQL + ")" + " ORDER BY " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " ASC," + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { do { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Fortrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("FwdTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell; String sid = null; if (rs.getObject("Lieferungen.Empfnger") != null) { sid = getStationLookup(rs.getString("Lieferungen.Empfnger")); cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Ingredients for Lot(s) row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); } rs.previous(); // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; int i = 0; row = sheetTracing.getRow(rowIndex); for (String dns : deliveryNumbers) { if (!dns.isEmpty()) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); //todo cell = row.getCell(4); cell.setCellValue(dns); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; } } Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); // Products Out row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; for (i = 0; i < 86; i++) { insertCondition(dvHelper, sheetTracing, rowIndex + i, 3, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 4, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 5, "1900", "3000"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 6, "1", "31"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 7, "1", "12"); insertCondition(dvHelper, sheetTracing, rowIndex + i, 8, "1900", "3000"); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 9); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 10, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 11, "=StationIDs"); //row = sheetTracing.getRow(rowIndex+i); //cell = row.getCell(12); //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)"); //evaluator.evaluateFormulaCell(cell); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 0, "=LotNumbers"); } for (i = 0; i < deliveryNumbers.size(); i++) { insertDropBox(dvHelper, sheetTracing, 9 + i, 0, "=LotNumbers"); } //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID")); if (save(workbook, outputFolder + File.separator + "Fwdtrace_request_" + getValidFileName(rs.getString("Station.Serial")) + ".xlsx")) { // + "_" + getFormattedDate() result++; } myxls.close(); } while (rs.next()); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getFwdStationRequests(String outputFolder, Station station) throws SQLException, IOException { int result = 0; String sql = "Select * from " + MyDBI.delimitL("Station") + " AS " + MyDBI.delimitL("S") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + " WHERE " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " AND " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " IS NOT NULL" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; /*//from w w w .ja v a 2s .c o m String sql = "Select * from " + MyDBI.delimitL("Lieferungen") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; */ //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Fortrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("FwdTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell; String sid = station.getId(); if (sid != null) { cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Ingredients for Lot(s) row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); List<Integer> dbLots = new ArrayList<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); dbLots.add(rs.getInt("ChargenVerbindungen.Produkt")); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, true, null); deliveryNumbers.add(dn); dbLots.add(rs.getInt("ChargenVerbindungen.Produkt")); } // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC"; rs = DBKernel.getResultSet(sql, false); int i = 0; row = sheetTracing.getRow(rowIndex); LinkedHashMap<Integer, String> lotDb2Number = new LinkedHashMap<>(); if (rs != null && rs.first()) { do { if (rs.getObject("Chargen.ID") != null && dbLots.contains(rs.getInt("Chargen.ID")) && !lotDb2Number.containsKey(rs.getInt("Chargen.ID"))) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); if (rs.getObject("Chargen.ChargenNr") != null) { cell = row.getCell(0); cell.setCellValue(rs.getString("Chargen.ChargenNr")); } if (rs.getObject("Chargen.Menge") != null) { cell = row.getCell(1); cell.setCellValue(rs.getDouble("Chargen.Menge")); } if (rs.getObject("Chargen.Einheit") != null) { cell = row.getCell(2); cell.setCellValue(rs.getString("Chargen.Einheit")); } if (rs.getObject("Produktkatalog.Bezeichnung") != null) { cell = row.getCell(3); cell.setCellValue(rs.getString("Produktkatalog.Bezeichnung")); } insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; lotDb2Number.put(rs.getInt("Chargen.ID"), rs.getString("Chargen.ChargenNr")); } } while (rs.next()); } if (i == 0) i = 1; Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); for (int ii = 0; ii < dbLots.size(); ii++) { if (lotDb2Number.containsKey(dbLots.get(ii))) { row = sheetTracing.getRow(9 + ii); cell = row.getCell(0); if (cell == null) cell = row.createCell(0); cell.setCellValue(lotDb2Number.get(dbLots.get(ii))); } insertDropBox(dvHelper, sheetTracing, 9 + ii, 0, "=LotNumbers"); } // Products Out row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; if (rs != null && rs.first() && rs.getObject("Chargen.ChargenNr") != null) { boolean didOnce = false; do { if (didOnce) row = copyRow(workbook, sheetTracing, rowIndex - 1, rowIndex); else row = sheetTracing.getRow(rowIndex); fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); rowIndex++; didOnce = true; } while (rs.next()); } for (i = 0; i < 85; i++) { doFormats(dvHelper, sheetTracing, rowIndex + i, evaluator); } if (save(workbook, outputFolder + File.separator + "StationFwdtrace_request_" + getValidFileName(station.getId()) + ".xlsx")) { // + "_" + getFormattedDate() result++; } myxls.close(); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getBackStationRequests(String outputFolder, Station station) throws SQLException, IOException { int result = 0; String sql = "Select * from " + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " WHERE " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " ORDER BY " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ChargenNr") + " ASC"; //System.err.println(sql); ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { InputStream myxls = this.getClass().getResourceAsStream( "/de/bund/bfr/knime/openkrise/db/imports/custom/bfrnewformat/BfR_Format_Backtrace_sug.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(myxls); XSSFSheet sheetTracing = workbook.getSheet("BackTracing"); XSSFSheet sheetStations = workbook.getSheet("Stations"); XSSFSheet sheetLookup = workbook.getSheet("LookUp"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); fillStations(sheetStations, evaluator); fillLookup(workbook, sheetLookup); LinkedHashSet<String> le = getLotExtra(); LinkedHashSet<String> de = getDeliveryExtra(); // Station in Focus XSSFRow row = sheetTracing.getRow(4); XSSFCell cell;//w ww . j a v a 2 s .c o m String sid = null; if (rs.getObject("Station.Serial") != null) { sid = getStationLookup(rs); cell = row.getCell(1); cell.setCellValue(sid); cell = row.getCell(2); evaluator.evaluateFormulaCell(cell); } // Products Out row = sheetTracing.getRow(7); int j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetTracing); LinkedHashMap<String, Lot> lotNumbers = new LinkedHashMap<>(); LinkedHashMap<Integer, String> lotDb2Number = new LinkedHashMap<>(); int rowIndex = 9; row = sheetTracing.getRow(rowIndex); String ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } lotDb2Number.put(rs.getInt("Chargen.ID"), ln); while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; row = copyRow(workbook, sheetTracing, 9, rowIndex); ln = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, false, null); if (!lotNumbers.containsKey(ln)) { Lot l = new Lot(); l.setNumber(ln); if (rs.getObject("Chargen.Menge") != null) l.setUnitNumber(rs.getDouble("Chargen.Menge")); if (rs.getObject("Chargen.Einheit") != null) l.setUnitUnit(rs.getString("Chargen.Einheit")); if (rs.getObject("Produktkatalog.Bezeichnung") != null) { Product p = new Product(); p.setName(rs.getString("Produktkatalog.Bezeichnung")); l.setProduct(p); } l.setDbId(rs.getInt("Chargen.ID")); lotNumbers.put(ln, l); } lotDb2Number.put(rs.getInt("Chargen.ID"), ln); } rs.previous(); // Lot Information row = sheetTracing.getRow(rowIndex + 3); j = 0; for (String e : le) { if (e != null && !e.isEmpty()) { cell = row.getCell(17 + j); if (cell == null) cell = row.createCell(17 + j); cell.setCellValue(e); j++; } } rowIndex += 5; int i = 0; row = sheetTracing.getRow(rowIndex); for (Lot lot : lotNumbers.values()) { if (lot != null && !lot.getNumber().isEmpty()) { if (i > 0) row = copyRow(workbook, sheetTracing, rowIndex, rowIndex + i); cell = row.getCell(0); cell.setCellValue(lot.getNumber()); if (lot.getUnitNumber() != null) { cell = row.getCell(1); cell.setCellValue(lot.getUnitNumber()); } if (lot.getUnitUnit() != null) { cell = row.getCell(2); cell.setCellValue(lot.getUnitUnit()); } if (lot.getProduct() != null && lot.getProduct().getName() != null) { cell = row.getCell(3); cell.setCellValue(lot.getProduct().getName()); } LinkedHashSet<String> le0 = new LinkedHashSet<>(); le0.add("Production Date"); le0.add("Best before date"); le0.add("Treatment of product during production"); le0.add("Sampling"); le0.addAll(le); fillExtraFields("Chargen", lot.getDbId(), row, le0, 13); insertDecCondition(dvHelper, sheetTracing, rowIndex + i, 1); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 2, "=Units"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 15, "=Treatment"); insertDropBox(dvHelper, sheetTracing, rowIndex + i, 16, "=Sampling"); i++; } } Name reference = workbook.createName(); reference.setNameName("LotNumbers"); String referenceString = sheetTracing.getSheetName() + "!$A$" + (rowIndex + 1) + ":$A$" + (rowIndex + i); reference.setRefersToFormula(referenceString); String sif = getValidFileName(rs.getString("Station.Serial")); // + "_" + getFormattedDate() // Ingredients for Lot(s) row = sheetTracing.getRow(rowIndex + i + 2); j = 0; for (String e : de) { if (e != null && !e.isEmpty()) { cell = row.getCell(13 + j); if (cell == null) cell = row.createCell(13 + j); cell.setCellValue(e); j++; } } rowIndex += i + 4; sql = "Select * from " + MyDBI.delimitL("Station") + " AS " + MyDBI.delimitL("S") + " LEFT JOIN " + MyDBI.delimitL("Lieferungen") + " ON " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Empfnger") + " LEFT JOIN " + MyDBI.delimitL("Chargen") + " ON " + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("Charge") + " LEFT JOIN " + MyDBI.delimitL("Produktkatalog") + " ON " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Chargen") + "." + MyDBI.delimitL("Artikel") + " LEFT JOIN " + MyDBI.delimitL("Station") + " ON " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + "=" + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Station") + " LEFT JOIN " + MyDBI.delimitL("ChargenVerbindungen") + " ON " + MyDBI.delimitL("ChargenVerbindungen") + "." + MyDBI.delimitL("Zutat") + "=" + MyDBI.delimitL("Lieferungen") + "." + MyDBI.delimitL("ID") + " WHERE " + MyDBI.delimitL("S") + "." + MyDBI.delimitL("Serial") + " = '" + station.getId() + "'" + " AND " + MyDBI.delimitL("Station") + "." + MyDBI.delimitL("ID") + " IS NOT NULL" + " ORDER BY " + MyDBI.delimitL("Produktkatalog") + "." + MyDBI.delimitL("Bezeichnung") + " ASC"; //System.out.println(sql); rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { LinkedHashSet<String> deliveryNumbers = new LinkedHashSet<>(); row = sheetTracing.getRow(rowIndex); String dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, null, lotDb2Number); doFormats(dvHelper, sheetTracing, rowIndex, evaluator); deliveryNumbers.add(dn); boolean didOnce = false; while (rs.next()) { if (rs.getObject("Station.Serial") == null) break; String sl = getStationLookup(rs); if (!sl.equals(sid)) break; rowIndex++; if (didOnce) row = copyRow(workbook, sheetTracing, rowIndex - 1, rowIndex); else row = sheetTracing.getRow(rowIndex); dn = fillRow(dvHelper, sheetTracing, rs, row, evaluator, de, null, lotDb2Number); doFormats(dvHelper, sheetTracing, rowIndex, evaluator); deliveryNumbers.add(dn); didOnce = true; } rowIndex++; } for (i = 0; i < 84; i++) { doFormats(dvHelper, sheetTracing, rowIndex + i, evaluator); } //System.err.println(rs.getInt("Lieferungen.ID") + "\t" + rs.getInt("Chargen.ID")); if (save(workbook, outputFolder + File.separator + "StationBacktrace_request_" + sif + ".xlsx")) { result++; } myxls.close(); } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private int getBacktraceRequests(String outputFolder, List<String> business2Backtrace) throws SQLException, IOException { int result = 0; String sql;//w ww .j a v a 2s . c om 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 XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { XSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false); XSSFRow newRow = worksheet.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;/*www . j a va 2 s . co m*/ continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } newRow.setHeight(sourceRow.getHeight()); return newRow; }
From source file:de.contentcreation.pplive.controller.ReportingController.java
public void postProcessXLSX(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet sheet = wb.getSheetAt(0); XSSFRow header = sheet.getRow(0); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < 11; i++) { // XSSFCell cell = header.getCell(i); sheet.autoSizeColumn(i);//w w w . jav a 2 s . co m // cell.setCellStyle(cellStyle); } }
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLSX file and parse it/*from ww w . j av a2 s . co m*/ * @param file The XLSX File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSXFile(File file) { int index = -1; XSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } XSSFSheet sheet = workbook.getSheetAt(index); XSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { XSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Loads the retrieved data from excel to the list *//*from w w w. j a v a 2 s . c o m*/ private static List<RealtimeData> loadDataToList(final XSSFSheet sheet, final List<RealtimeData> data, final RealtimeData bluetoothData) throws InstantiationException, IllegalAccessException, SecurityException, NoSuchFieldException { final Row column = sheet.getRow(0); final String columnNames[] = getColumnNames(column); final Iterator<Row> rowIterator = sheet.iterator(); int rowOne = 0; while (rowIterator.hasNext()) { final Row row = rowIterator.next(); final Iterator<Cell> cellIterator = row.cellIterator(); final RealtimeData newRecord = bluetoothData.getClass().newInstance(); if (rowOne > 0) { int i = 0; while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); final String columnName = columnNames[i++]; final Field f1 = bluetoothData.getClass().getDeclaredField(columnName.trim()); f1.setAccessible(true); f1.set(newRecord, getTypeValue(f1.getType(), cell)); } data.add(newRecord); } rowOne++; } return data; }