List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:org.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel.//from w w w. j ava 2 s .c o m * * @param <T> the generic type * @param type the type * @param in the in * @return the list */ @SuppressWarnings("rawtypes") public <T> List<T> importExcel(Class<T> type, InputStream in) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } List<T> list = new ArrayList<T>(); Map<String, Method> mapping = new LinkedHashMap<String, Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { Method setMethod = ReflectionUtils.setValueMethod(field, type); mapping.put(column.value(), setMethod); if (column.converter() != TypeHandler.class) { converters.put(setMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(setMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } T temp = null; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ?Sheet List<Method> methods = new ArrayList<Method>(); for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // ? if (rowNum == 0) { for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { String title = hssfRow.getCell(cellNum).getStringCellValue(); Method me = mapping.get(title); if (me == null) { continue; } methods.add(me); } continue; } temp = type.newInstance(); for (int cellNum = 0; cellNum < methods.size(); cellNum++) { HSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } Method m = methods.get(cellNum); TypeHandler handler = converters.get(m.getName()); if (handler == null) { continue; } String value = xh.getStringCellValue(); if (StringUtils.isEmpty(value)) { continue; } Object val = null; try { val = handler.stringToType(value); } catch (Exception e) { throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1) + "" + (cellNum + 1) + "" + value + "??"); } methods.get(cellNum).invoke(temp, val); } list.add(temp); } } } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } return list; }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
public void mergeIDs() { System.err.println("Merging..."); try {//from w w w . j ava 2 s .c o m //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet mergeSheet = wb.getSheet("mergeList"); int numRows = mergeSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = mergeSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); if (cell != null) { Integer oldEfsaID = (int) cell.getNumericCellValue(); if (cell != null) { cell = row.getCell(1); Integer newEfsaID = (int) cell.getNumericCellValue(); DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID); } } } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("Merging...Fin!"); }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private HashMap<String, Integer> loadNodeIDs10000() { System.err.println("loadNodeIDs10000..."); nodeIds = new HashMap<String, Integer>(); try {//from w ww .ja v a 2 s . c o m //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet defaultSheet = wb.getSheet("default"); int numRows = defaultSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = defaultSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); Integer id = (int) cell.getNumericCellValue(); if (id > maxNodeID) maxNodeID = id; cell = row.getCell(1); String name = cell.getStringCellValue(); nodeIds.put(name, id); } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("loadNodeIDs10000...Fin!"); return nodeIds; }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private int[] doImportStandard(HSSFWorkbook wb, JProgressBar progress, String filename) { int numSuccess = 0; int numFails = 0; HSSFSheet transactionSheet = wb.getSheet("Transactions"); boolean isSimpleFormat = transactionSheet != null && isSimple(transactionSheet.getRow(0)); boolean isNewFormat = transactionSheet == null && wb.getSheet("NewTransactions") != null || isNew(transactionSheet.getRow(0)); if (isNewFormat && !isSimpleFormat) { doImportNewFormat(wb, progress); } else {//from ww w. ja v a 2 s . c o m boolean isBvl = isBVL(transactionSheet.getRow(0)); HSSFSheet businessSheet = wb.getSheet("Business_List"); int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows); progress.setValue(0); for (int i = isBvl ? 6 : 1; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String serial = getStrVal(row.getCell(0)); // Serial_number String BL0 = getStrVal(row.getCell(1)); // Contact_Region String KP = getStrVal(row.getCell(2)); // Contact_person String idRec = getStrVal(row.getCell(3)); // ID_Address String adressRec = getStrVal(row.getCell(4)); // Address if ((serial == null || serial.trim().isEmpty()) && (adressRec == null || adressRec.trim().isEmpty())) { continue;//break; } if (serials.containsKey(serial)) { String msg = "Row: " + (i + 1) + "\tSerial '" + serial + "' already defined in file '" + serials.get(serial) + "' -> not importing this row!"; System.err.println(msg); logMessages += msg + "\n"; continue; } serials.put(serial, filename); String activityRec = getStrVal(row.getCell(5)); // Activity String nameRec = adressRec; String streetRec = null; String streetNoRec = null; String zipRec = null; String cityRec = null; String countyRec = null; String countryRec = null; String vatRec = null; HSSFRow busRow = getRow(businessSheet, idRec, 0); if (busRow != null) { nameRec = getStrVal(busRow.getCell(1)); // streetRec = getStrVal(busRow.getCell(2)); // streetNoRec = getStrVal(busRow.getCell(3), 10); // zipRec = getStrVal(busRow.getCell(4), 10); // cityRec = getStrVal(busRow.getCell(5)); // countyRec = getStrVal(busRow.getCell(6), 30); countryRec = getStrVal(busRow.getCell(7)); // vatRec = getStrVal(busRow.getCell(8)); // if (!adressRec.toUpperCase().startsWith(nameRec.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on recs...\t" + nameRec + " <> " + adressRec; System.err.println(msg); logMessages += msg + "\n"; } } else if (idRec != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidReceived: " + idRec; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidRec is null???\t" + adressRec + (adressRec != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String prodNameOut = getStrVal(row.getCell(6)); // ProductName String prodNumOut = getStrVal(row.getCell(7)); // ProductNo String dayOut = getStrVal(row.getCell(8)); // Day String monthOut = getStrVal(row.getCell(9)); // Month String yearOut = getStrVal(row.getCell(10)); // Year String amountKG_Out = getStrVal(row.getCell(11)); // amountKG String typePUOut = getStrVal(row.getCell(12)); // typePU String numPUOut = getStrVal(row.getCell(13)); // numPU String lotNo_Out = getStrVal(row.getCell(14)); // String dayMHDOut = getStrVal(row.getCell(15)); String monthMHDOut = getStrVal(row.getCell(16)); String yearMHDOut = getStrVal(row.getCell(17)); // String dayPDOut = getStrVal(row.getCell(18)); String monthPDOut = getStrVal(row.getCell(19)); String yearPDOut = getStrVal(row.getCell(20)); //Date dateOut = getDate(dayOut, monthOut, yearOut); //Date dateMHDOut = getDate(dayMHDOut, monthMHDOut, yearMHDOut); //Date datePDOut = getDate(dayPDOut, monthPDOut, yearPDOut); String idInsp = getStrVal(row.getCell(21)); // ID_Address String adressInsp = getStrVal(row.getCell(22)); // Address String activityInsp = getStrVal(row.getCell(23)); // Activity String nameInsp = adressInsp; String streetInsp = null; String streetNoInsp = null; String zipInsp = null; String cityInsp = null; String countyInsp = null; String countryInsp = null; String vatInsp = null; busRow = getRow(businessSheet, idInsp, 0); if (busRow != null) { nameInsp = getStrVal(busRow.getCell(1)); // streetInsp = getStrVal(busRow.getCell(2)); // streetNoInsp = getStrVal(busRow.getCell(3), 10); // zipInsp = getStrVal(busRow.getCell(4), 10); // cityInsp = getStrVal(busRow.getCell(5)); // countyInsp = getStrVal(busRow.getCell(6), 30); countryInsp = getStrVal(busRow.getCell(7)); // vatInsp = getStrVal(busRow.getCell(8)); // if (!adressInsp.toUpperCase().startsWith(nameInsp.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on insps...\t" + nameInsp + " <> " + adressInsp; System.err.println(msg); logMessages += msg + "\n"; } } else if (idInsp != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidInspected: " + idInsp; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidInsp is null???\t" + adressInsp + (adressInsp != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String oc = ""; String cqr = ""; if (!isSimpleFormat) { oc = getStrVal(row.getCell(44)); // OriginCountry cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks } Integer c1 = null; if (nameInsp != null && !nameInsp.trim().isEmpty()) { Integer[] c = getCharge_Lieferung(idInsp, nameInsp, streetInsp, streetNoInsp, zipInsp, cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, prodNameOut, prodNumOut, null, lotNo_Out, dayMHDOut, monthMHDOut, yearMHDOut, dayPDOut, monthPDOut, yearPDOut, oc, dayOut, monthOut, yearOut, amountKG_Out, typePUOut, numPUOut, idRec, nameRec, streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, activityRec, vatRec, serial, cqr, null, null, null, null); if (c != null) c1 = c[2]; } if (isSimpleFormat) continue; String prodNameIn = getStrVal(row.getCell(24)); // ProductName String prodNumIn = getStrVal(row.getCell(25)); // ProductNo String dayIn = getStrVal(row.getCell(26)); // Day String monthIn = getStrVal(row.getCell(27)); // Month String yearIn = getStrVal(row.getCell(28)); // Year String amountKG_In = getStrVal(row.getCell(29)); // amountKG String typePUIn = getStrVal(row.getCell(30)); // typePU String numPUIn = getStrVal(row.getCell(31)); // numPU String lotNo_In = getStrVal(row.getCell(32)); // String dayMHDIn = getStrVal(row.getCell(33)); String monthMHDIn = getStrVal(row.getCell(34)); String yearMHDIn = getStrVal(row.getCell(35)); // String dayPDIn = getStrVal(row.getCell(36)); String monthPDIn = getStrVal(row.getCell(37)); String yearPDIn = getStrVal(row.getCell(38)); //Date dateIn = getDate(dayIn, monthIn, yearIn); //Date dateMHDIn = getDate(dayMHDIn, monthMHDIn, yearMHDIn); //Date datePDIn = getDate(dayPDIn, monthPDIn, yearPDIn); String idSup = getStrVal(row.getCell(39)); // ID_Address String adressSup = getStrVal(row.getCell(40)); // Address String activitySup = getStrVal(row.getCell(41)); // Activity String nameSup = adressSup; String streetSup = null; String streetNoSup = null; String zipSup = null; String citySup = null; String countySup = null; String countrySup = null; String vatSup = null; busRow = getRow(businessSheet, idSup, 0); if (busRow != null) { nameSup = getStrVal(busRow.getCell(1)); // streetSup = getStrVal(busRow.getCell(2)); // streetNoSup = getStrVal(busRow.getCell(3), 10); // zipSup = getStrVal(busRow.getCell(4), 10); // citySup = getStrVal(busRow.getCell(5)); // countySup = getStrVal(busRow.getCell(6), 30); countrySup = getStrVal(busRow.getCell(7)); // vatSup = getStrVal(busRow.getCell(8)); // if (!adressSup.toUpperCase().startsWith(nameSup.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on sups...\t" + nameSup + " <> " + adressSup; System.err.println(msg); logMessages += msg + "\n"; } } else if (idSup != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidSupplier: " + idSup; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidSup is null???\t" + adressSup + (adressSup != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String ec = getStrVal(row.getCell(42)); // EndChain String ece = getStrVal(row.getCell(43)); // Explanation_EndChain String ft = getStrVal(row.getCell(46)); // Further_Traceback String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample //if (amountKG_Out != null && amountKG_In != null && Integer.parseInt(amountKG_Out) > Integer.parseInt(amountKG_In)) System.err.println("amountOut > aomountIn!!! Row " + i + "; amountKG_Out: " + amountKG_Out + "; amountKG_In: " + amountKG_In); if (is1SurelyNewer(dayIn, monthIn, yearIn, dayOut, monthOut, yearOut)) { String msg = "Row: " + (i + 1) + "\tDates not in temporal order, dateOut < dateIn!!! , KP: " + KP + ", BL0: " + BL0 + "; dateOut: " + sdfFormat(dayOut, monthOut, yearOut) + "; dateIn: " + sdfFormat(dayIn, monthIn, yearIn); System.err.println(msg); logMessages += msg + "\n"; } Integer c2 = null; if (nameSup != null && !nameSup.trim().isEmpty()) { Integer[] c = getCharge_Lieferung(idSup, nameSup, streetSup, streetNoSup, zipSup, citySup, countySup, countrySup, activitySup, vatSup, prodNameIn, prodNumIn, null, lotNo_In, dayMHDIn, monthMHDIn, yearMHDIn, dayPDIn, monthPDIn, yearPDIn, oc, dayIn, monthIn, yearIn, amountKG_In, typePUIn, numPUIn, idInsp, nameInsp, streetInsp, streetNoInsp, zipInsp, cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, serial, cqr, ec, ece, ft, ms); if (c != null) c2 = c[3]; } if (c1 == null) { // Chargen String msg = "Row: " + (i + 1) + "\tError Type 1 (Batches)!!"; // Fehlerchenchen_1 System.err.println(msg); logMessages += msg + "\n"; numFails++; } else if (c2 == null) { // Lieferungen String msg = "Row: " + (i + 1) + "\tError Type 2 (Deliveries)!! E.g. Station not defined?"; // Fehlerchenchen_2 System.err.println(msg); logMessages += msg + "\n"; /* * getCharge_Lieferung(nameSup, streetSup, streetNoSup, * zipSup, citySup, countySup, countrySup, activitySup, * vatSup, prodNameIn, prodNumIn, lotNo_In, dateMHDIn, * datePDIn, oc, dateIn, amountKG_In, typePUIn, numPUIn, * nameSup, streetSup, streetNoSup, zipSup, citySup, * countySup, countrySup, activityInsp, vatInsp, comment, * false); */ numFails++; } else { if (c2 != null) { Integer cvID = getID("ChargenVerbindungen", new String[] { "Zutat", "Produkt" }, new String[] { c2.toString(), c1.toString() }, null, null); if (cvID == null) { String msg = "Row: " + (i + 1) + "\tError Type 4 (Links)!!"; // Fehlerchenchen_4 System.err.println(msg); logMessages += msg + "\n"; numFails++; } else { numSuccess++; } } } } } } return new int[] { numSuccess, numFails }; }
From source file:org.hsh.bfr.db.imports.GeneralXLSImporter.java
License:Open Source License
public String doImport(final String filename, final JProgressBar progress, final boolean showResults) { Runnable runnable = new Runnable() { public void run() { try { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Importiere Excel Datei..."); progress.setMinimum(0); }/*w w w .jav a 2 s.co m*/ InputStream is = null; System.out.println(filename); if (filename.startsWith("http://")) { URL url = new URL(filename); URLConnection uc = url.openConnection(); is = uc.getInputStream(); } else if (filename.startsWith("/org/hsh/bfr/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet; HSSFRow row; int numSuccess = 0; int numFailed = 0; String unusedFields = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); String tableName = sheet.getSheetName(); MyTable myT = DBKernel.myDBi.getTable(tableName); if (myT != null) { int numRows = sheet.getLastRowNum(); if (progress != null) { progress.setMaximum(numRows); progress.setValue(0); } row = sheet.getRow(0); String sql1 = ""; String sql2 = ""; String sql3 = ""; Vector<String> codeSql1 = new Vector<>(); Vector<String> codeSql2 = new Vector<>(); LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>(); int numCols = row.getLastCellNum(); String[] fieldNames = new String[numCols]; String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT); String[] ffieldTypes = new String[numCols]; MyTable[] myForeignTables = new MyTable[numCols]; String[] kzS = new String[numCols]; String[] dbFieldnames = new String[numCols]; int lfdCol = 0; Hashtable<String, String> dbFieldNames = new Hashtable<>(); for (int j = 0; j < numCols; j++) { String fieldName = row.getCell(j).getStringCellValue(); fieldNames[j] = fieldName; int ffe; String dbFieldName = getDBFieldName(fieldName, myT, takecareofID); if (dbFieldName != null) { String ft = getForeignTable(dbFieldName, myT); if (ft != null && ft.equals("DoubleKennzahlen")) { kzS[j] = getKZ(fieldName, dbFieldName); dbFieldnames[j] = dbFieldName; } else if (!dbFieldNames.containsKey(dbFieldName)) { dbFieldNames.put(dbFieldName, dbFieldName); sql1 += DBKernel.delimitL(dbFieldName) + ","; sql2 += "?,"; sql3 += DBKernel.delimitL(dbFieldName) + "=?,"; lfdCol++; } fieldTypes[j] = getType(dbFieldName, myT, takecareofID); } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) { if (!foreignTables.containsKey(myT.getForeignFields()[ffe])) foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>()); ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false); foreignTables.get(myT.getForeignFields()[ffe]).add(j); myForeignTables[j] = myT.getForeignFields()[ffe]; } else if (DBKernel.showHierarchic(tableName) && fieldName.toLowerCase().endsWith("-code")) { codeSql1.add(DBKernel.delimitL("CodeSystem") + "," + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis")); codeSql2.add("'" + fieldName.substring(0, fieldName.length() - "-code".length()) + "',?,?"); } else if (!fieldName.equalsIgnoreCase("id")) { unusedFields += "," + fieldName; } } if (sql1.length() > 0 && sql2.length() > 0) { String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " (" + sql1.substring(0, sql1.length() - 1) + ") VALUES (" + sql2.substring(0, sql2.length() - 1) + ")"; PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int idCol = lfdCol + 1; sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET " + sql3.substring(0, sql3.length() - 1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql); PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()]; boolean doCode[] = new boolean[codeSql1.size()]; int codesI; for (codesI = 0; codesI < codeSql1.size(); codesI++) { sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName)) + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI) + ")"; psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql); } LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>(); LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>(); for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) { Vector<Integer> vs = entry.getValue(); String ssql1 = "", ssql2 = "", ssql3 = ""; for (int ii = 0; ii < vs.size(); ii++) { ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]); ssql2 += ",?"; ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?"; } if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) { sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename()) + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1) + ")"; psForeign.put(entry.getKey(), DBKernel.getDBConnection() .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)); sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename()) + " SET " + ssql3.substring(1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; psForeignUpdate.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql)); } } LinkedHashMap<Object, String> hashBL = null; Iterator<Row> rows = sheet.rowIterator(); int lfd = 0; while (rows.hasNext()) { row = (HSSFRow) rows.next(); boolean setID = false; Integer lastID = null; ps.clearParameters(); psUpdate.clearParameters(); for (codesI = 0; codesI < codeSql1.size(); codesI++) { psCodes[codesI].clearParameters(); doCode[codesI] = false; } LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>(); for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate.entrySet()) { MyTable myT1 = entry.getKey(); psForeign.get(myT1).clearParameters(); psForeignUpdate.get(myT1).clearParameters(); lfdColsForeign.put(myT1, 0); } if (row.getRowNum() > 0) { lfdCol = 0; codesI = 0; Object[] kzVal = new Object[numCols]; for (int j = 0; j < numCols; j++) { if (fieldTypes[j] != null) { /* * if (fieldNames[j].equals( * "Bundesland")) { hashBL = * DBKernel * .myDBi.getHashMap("County"); * } else { hashBL = null; } */ lfdCol++; if (fieldTypes[j].startsWith("VARCHAR(") || fieldTypes[j].startsWith("CHAR(") || fieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL); else if (fieldTypes[j].equals("BOOLEAN")) manageBoolean(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("INTEGER")) manageInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("BIGINT")) manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DOUBLE")) { if (kzS[j] != null) { lfdCol--; //System.err.println(dbFieldnames[j] + "\t" + kzS[j]); if (DBKernel.kzIsString(kzS[j])) kzVal[j] = manageString(null, null, lfdCol, row.getCell(j)); else if (DBKernel.kzIsBoolean(kzS[j])) kzVal[j] = manageBoolean(null, null, lfdCol, row.getCell(j)); else kzVal[j] = manageDouble(null, null, lfdCol, row.getCell(j)); } else { manageDouble(ps, psUpdate, lfdCol, row.getCell(j)); } } else System.err .println("Wasn hier los? Undefinierter Feldtyp???? ->\t" + fieldNames[j]); } else if (myForeignTables[j] != null && ffieldTypes[j] != null) { lfdColsForeign.put(myForeignTables[j], lfdColsForeign.get(myForeignTables[j]) + 1); if (ffieldTypes[j].startsWith("VARCHAR(") || ffieldTypes[j].startsWith("CHAR(") || ffieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j), hashBL); else if (ffieldTypes[j].equals("BOOLEAN")) manageBoolean(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("INTEGER")) manageInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("BIGINT")) manageBigInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("DOUBLE")) { manageDouble(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); } else System.err.println(fieldNames[j] + " Feldtype????"); } else if (fieldNames[j].equals("ID")) { lastID = manageInteger(null, null, 0, row.getCell(j)); if (lastID != null) { if (DBKernel.hasID(tableName, lastID.intValue())) { psUpdate.setInt(idCol, lastID.intValue()); setID = true; } } } else if (DBKernel.showHierarchic(tableName) && fieldNames[j].toLowerCase().endsWith("-code")) { String code = manageString(psCodes[codesI], null, 1, row.getCell(j)); if (code != null && code.length() > 0) doCode[codesI] = true; codesI++; } else { //System.out.println(fieldNames[j]); } } try { if (setID) { psUpdate.execute(); } else { if (ps.executeUpdate() > 0) {// execute() lastID = DBKernel.getLastInsertedID(ps); } else { System.err.println("W"); } } numSuccess++; if (lastID != null) { for (int j = 0; j < numCols; j++) { if (dbFieldnames[j] != null && kzVal[j] != null) { DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j], lastID, null, kzS[j], kzVal[j]); } } for (codesI = 0; codesI < codeSql1.size(); codesI++) { if (doCode[codesI]) { psCodes[codesI].setInt(2, lastID); try { psCodes[codesI].execute(); numSuccess++; } catch (SQLException e1) { numFailed++; System.err.println(psCodes[codesI]); } } } for (Map.Entry<MyTable, PreparedStatement> entry : psForeign .entrySet()) { MyTable myT1 = entry.getKey(); MyTable[] foreignTs = myT.getForeignFields(); for (int ii = 0; ii < foreignTs.length; ii++) { if (foreignTs[ii] != null && foreignTs[ii].equals(myT1)) { if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT int lID = DBKernel .getLastInsertedID(psForeign.get(myT1)); // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt DBKernel.sendRequest("UPDATE " + DBKernel.delimitL(tableName) + " SET " + DBKernel.delimitL(myT.getFieldNames()[ii]) + "=" + lID + " WHERE " + DBKernel.delimitL("ID") + "=" + lastID, false); } break; } } } /* * for (int j=0;j<numCols;j++) { * if (myForeignTables[j] != * null && ffieldTypes[j] != * null) { MyTable[] foreignTs = * myT.getForeignFields(); for * (int * ii=0;ii<foreignTs.length; * ii++) { if (foreignTs[ii] != * null && foreignTs[ii].equals( * myForeignTables[j])) { if * (psForeign * .get(myForeignTables * [j]).executeUpdate() > 0) { * // INSERT int lID = * DBKernel.getLastInsertedID * (psForeign * .get(myForeignTables[j])); * DBKernel * .sendRequest("UPDATE " + * DBKernel.delimitL(tableName) * + " SET " + * DBKernel.delimitL( * myT.getFieldNames()[ii]) + * "=" + lID + " WHERE " + * DBKernel.delimitL("ID") + "=" * + lastID, false); } break; } * } } } */ } } catch (Exception e1) { numFailed++; MyLogger.handleMessage(ps.toString()); MyLogger.handleException(e1); } } if (progress != null) { lfd++; progress.setValue(lfd); } } } myT.doMNs(); if (progress != null) { // Refreshen: MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable(); if (myDB.getActualTable() != null) { String actTablename = myDB.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myDB.setTable(myDB.getActualTable()); } } MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree(); if (myTR.getActualTable() != null) { String actTablename = myTR.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myTR.setTable(myTR.getActualTable()); } } } } else { System.err.println(tableName + " nicht in DB???"); } } if (progress != null) { progress.setVisible(false); } String log = numSuccess + " erfolgreiche Importe.\n"; log += numFailed + " fehlgeschlagene Importe.\n"; if (unusedFields.length() > 0) log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n"; if (showResults) { InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null); ib.setVisible(true); } else { System.out.println("GeneralXLSImporter (" + filename + "):\n" + log); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return ""; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ?PageSize?/*from ww w .ja va 2 s . c o m*/ * @param sheet -- ? * @param startRow -- ???PageSize * @param rows -- ? * @return */ public static HSSFSheet insertSheetRow(HSSFSheet sheet, int startRow, int rows) { if (sheet == null) return null; sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false); HSSFCell sourcell = null, descell = null; HSSFRow sourow = sheet.getRow(startRow - 1); for (int i = 0; i < rows; i++) { HSSFRow descrow = sheet.createRow(startRow + i); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); descell.setCellType(sourcell.getCellType()); descell.setCellStyle(sourcell.getCellStyle()); } } //?? insertSheetRegions(sheet, startRow, rows); return sheet; }
From source file:org.jxstar.report.util.XlsToHtml.java
public String parserXls(String fileName) throws ReportException { ////from w ww .j ava2 s . co m HSSFWorkbook hssfWB = ReportXlsUtil.readWorkBook(fileName); if (hssfWB == null) {//"?{0}??" throw new ReportException(JsMessage.getValue("xlstohtml.hint01"), fileName); } HSSFSheet sheet = hssfWB.getSheetAt(0); //???0 int lastRowNum = sheet.getLastRowNum(); if (lastRowNum == 0) { _log.showDebug("xls file row num is 0!!"); return ""; } //?xls?tablehtml StringBuilder sbTable = new StringBuilder(); sbTable.append("<table id='" + PARSERTABLEID + "' class='xls_table' >\n"); //? List<Integer> lsemp = FactoryUtil.newList(); //?1 int hasnum = 0, tableColNum = 0; //??1IE1? //table-layout:fixed;??????1?? List<Integer> lswidth = FactoryUtil.newList(); //?? for (int i = 0; i <= lastRowNum; i++) { HSSFRow row = sheet.getRow(i); if (row == null) {//?? lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } //??? int lastCellNum = row.getLastCellNum(); //cells-1? if (lastCellNum <= 0) { lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } else { //? if (hasnum == 0) tableColNum = lastCellNum + EMPTY_COLNUM; hasnum++; } //tr sbTable.append("<tr height='" + getHeightPixel(row.getHeightInPoints()) + "px' >\n"); //_log.showDebug("row=" + i + "; nums=" + cells); for (int j = 0; j < tableColNum; j++) { HSSFCell cell = null; if (j < lastCellNum) cell = row.getCell(j); //?ID String tdid = i + "," + j; // if (cell == null) { String ls = ""; if (hasnum == 1) { //?30px int width = (j < lastCellNum) ? 10 : 30; ls = " style='width:" + width + "px;'"; lswidth.add(width); } sbTable.append("\t<td id='" + tdid + "' class='xls_emp'" + ls + " > </td>\n"); continue; } //td? String style = getCellStyle(cell); //???cssName String cssName = getTdCss(style); //?td String value = getCellValue(cell); if (value == null || value.length() == 0) { value = " "; } else { value = value.replaceAll("\\s", " "); } //1????? if (hasnum == 1) { // int colw = getWidthPixel(sheet.getColumnWidth(j)); lswidth.add(colw); //td sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' style='width:" + colw + "px;' >" + value + "</td>\n"); } else { sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' >" + value + "</td>\n"); } } sbTable.append("</tr>\n"); } //_log.showDebug(sbTable.toString()); //?? for (int i = 1; i <= EMPTY_ROWNUM; i++) { lsemp.add(lastRowNum + i); //tr sbTable.append("<tr height='22px' >\n"); sbTable.append("{EMPTY_LINE}"); sbTable.append("</tr>\n"); } //cells-1? if (tableColNum > 0 && lsemp.size() > 0) { sbTable = fillEmptyLine(sbTable, tableColNum, lsemp, lswidth); lsemp.clear(); } sbTable.append("</table>\n"); //?html StringBuilder sbHtml = new StringBuilder(); sbHtml.append("<html>\n<body style='margin:1px;'>\n"); sbHtml.append(getCssStyle()); sbHtml.append(sbTable); //??? sbHtml.append(mergedRegion(sheet)); //html sbHtml.append("</body>\n</html>\n"); //_log.showDebug(sbHtml.toString()); return sbHtml.toString(); }
From source file:org.kitodo.production.plugin.importer.massimport.PicaMassImport.java
License:Open Source License
private List<Record> getRecordsForXLS(InputStream xls) throws IOException { List<Record> records = new ArrayList<>(); HSSFWorkbook wb = new HSSFWorkbook(xls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet // loop over all rows for (int j = 0; j <= sheet.getLastRowNum(); j++) { // loop over all cells HSSFRow row = sheet.getRow(j);/*ww w .j av a 2 s. c o m*/ if (Objects.nonNull(row)) { for (int i = 0; i < row.getLastCellNum(); i++) { HSSFCell cell = row.getCell(i); // changing all cell types to String if (Objects.nonNull(cell)) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); Record record = changeCellTypeToString(cell, i, j); if (Objects.nonNull(record)) { records.add(record); } } } } } return records; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public List parseGroupSpreadsheet(FormFile fileItem) throws IOException { results = new ArrayList<ArrayList>(); parentOrg = service.getRootOrganisation(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); HSSFRow row;//www . ja v a2 s.c o m Organisation org = null; int successful = 0; for (int i = startRow + 1; i < endRow + 1; i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<String>(); row = sheet.getRow(i); if (row != null) { org = parseGroup(row, i); } // an empty row signifies a new group if (emptyRow) { log.debug("Row " + i + " is empty."); parentOrg = service.getRootOrganisation(); continue; } if (hasError) { log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); continue; } else { org = service.saveOrganisation(org, getCurrentUserId()); successful++; rowResult.add(org.getOrganisationId().toString()); rowResult.add(org.getName()); rowResult.add(org.getParentOrganisation().getOrganisationId().toString()); rowResult.add(org.getOrganisationType().getOrganisationTypeId().toString()); writeOrgAuditLog(org); // if we just added a group, then the rows under it become it's subgroups if (parentOrg.getOrganisationType().getOrganisationTypeId().equals(OrganisationType.ROOT_TYPE)) { parentOrg = org; } results.add(rowResult); } } log.debug("Found " + results.size() + " orgs in spreadsheet."); writeSuccessAuditLog(successful, null, "audit.successful.organisation.import"); return results; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public int getNumRows(FormFile fileItem) throws IOException { HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); return endRow - startRow; }