List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java
License:Open Source License
/** * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und * Listen von den Zeilen der sheets von der excel-Datei. * * @param excelSheet/*from w w w . j ava2 s . co m*/ * Die excel-Datei. * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit * den sheets beinhalten weitere Listen mit String die jeweils eine Zeile * reprsentieren. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); final List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); final List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { final List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { final HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { final int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { reihe.add(Boolean.toString(cell.getBooleanCellValue())); } else if (cellType == Cell.CELL_TYPE_ERROR) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_FORMULA) { reihe.add(cell.getCellFormula()); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { reihe.add(Double.toString(cell.getNumericCellValue())); } else if (cellType == Cell.CELL_TYPE_STRING) { reihe.add(cell.getRichStringCellValue().getString()); } } } excelSheetList.add(reihe); } } sl.add(excelSheetList); } wb.close(); return sl; }
From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java
License:Open Source License
/** * Replace null cells into empty cells./*from www. j a v a 2s .c o m*/ * * @param excelSheet * the excel sheet * @return the HSSF workbook * @throws IOException * Signals that an I/O exception has occurred. * @throws FileNotFoundException * the file not found exception */ public static HSSFWorkbook replaceNullCellsIntoEmptyCells(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (cell == null) { cell = row.createCell(j, Cell.CELL_TYPE_BLANK); } } } } } return wb; }
From source file:de.bezier.data.XlsReader.java
License:Creative Commons License
/** * XlsReader constructor.//from w ww.j a v a 2s . co m * * @param thePapplet Normally just pass "this", which is your sketch * @param theXlsFile Path to xls file */ public XlsReader(PApplet thePapplet, String theXlsFile) { papplet = thePapplet; xlsFile = theXlsFile; // read xls file InputStream is = papplet.createInput(xlsFile); if (is == null) { papplet.println("ERR, XlsReader(): file not found or unable to read"); return; } POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(is); workBook = new HSSFWorkbook(fs); /** TODO: * switch to event-api? * http://poi.apache.org/spreadsheet/how-to.html#event_api */ } catch (IOException ioe) { ioe.printStackTrace(); papplet.println("ERR, XlsReader(): file not found or unable to read"); } catch (Exception e) { e.printStackTrace(); papplet.println("ERR, XlsReader(): general error"); } openSheet(0); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
public void mergeIDs() { System.err.println("Merging..."); try (HSSFWorkbook wb = new HSSFWorkbook( new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls")))) { //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls"); 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); }//ww w.j a v a 2 s. co m } } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("Merging...Fin!"); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private HashMap<String, Integer> loadNodeIDs10000() { System.err.println("loadNodeIDs10000..."); nodeIds = new HashMap<String, Integer>(); try (HSSFWorkbook wb = new HSSFWorkbook( new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls")))) { //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls"); 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;//from ww w. j a v a 2 s . c o m 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:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
public boolean doImport(final String filename, final JProgressBar progress, final boolean showResults) { Runnable runnable = new Runnable() { public void run() { System.err.println("Importing " + filename); logMessages += "Importing " + filename + "\n"; try { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Importiere Lieferketten Datei..."); progress.setMinimum(0); }//from w w w . j a v a2 s. c o m loadNodeIDs10000(); InputStream is = null; if (filename.startsWith("http://")) { URL url = new URL(filename); URLConnection uc = url.openConnection(); is = uc.getInputStream(); } else if (filename.startsWith("/de/bund/bfr/knime/openkrise/db/res/")) { is = getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); boolean transformFormat = false; int[] nsf = new int[2]; if (filename.endsWith("LST_partners.xls")) { if (transformFormat) ; else nsf = doImportMaciel(wb, progress, "98"); // 98: LST } else if (filename.endsWith("ZAK_partners.xls")) { if (transformFormat) ; else nsf = doImportMaciel(wb, progress, "273"); // 273: ZAK } else if (filename.endsWith("NORIS.xls")) { if (transformFormat) ; else nsf = doImportMaciel(wb, progress, "115"); // 115: Noris } else if (filename.endsWith("agrifruct.xls")) { if (transformFormat) ; else nsf = doImportMaciel(wb, progress, "8"); // 8: Agrifruct } else if (filename.endsWith("Sunnyside_Suppliers.xls")) { if (transformFormat) ; else nsf = doImportMaciel(wb, progress, "238"); // 238: Sunnyside_Suppliers } else if (filename.endsWith("BfR_berry_supplier.xls")) { if (transformFormat) ; else nsf = doImportGaia(wb, progress); } else if (filename.endsWith("Erlenbacher.xls")) { if (transformFormat) ; else nsf = doImportErlenbacherFW(wb, progress, "1786"); } else { if (transformFormat) { InputStream isNew = new FileInputStream( "C:\\Users\\Armin\\Desktop\\AllKrisen\\NewFormat.xls"); POIFSFileSystem fsNew = new POIFSFileSystem(isNew); HSSFWorkbook wbNew = new HSSFWorkbook(fsNew); transformFormat(wb, wbNew); File f = new File(filename); File fd = new File(f.getParent() + "/NewFormat"); fd.mkdir(); FileOutputStream out = new FileOutputStream( f.getParent() + "/NewFormat/" + f.getName()); wbNew.write(out); /* * RaspYog nochmal genauer checken dutch file when * new format established */ } else { nsf = doImportStandard(wb, progress, filename); //nsf = doImportNewFormat(wb, progress); } } int numSuccess = nsf[0]; int numFails = nsf[1]; DBKernel.myDBi.getTable("Station").doMNs(); DBKernel.myDBi.getTable("Produktkatalog").doMNs(); DBKernel.myDBi.getTable("Chargen").doMNs(); DBKernel.myDBi.getTable("Lieferungen").doMNs(); if (progress != null) { // Refreshen: MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable(); if (myDB.getActualTable() != null) { String actTablename = myDB.getActualTable().getTablename(); if (actTablename.equals("Produktkatalog") || actTablename.equals("Lieferungen") || actTablename.equals("Station") || actTablename.equals("Chargen")) { myDB.setTable(myDB.getActualTable()); } } progress.setVisible(false); } if (showResults) { String log = numSuccess + " erfolgreiche Importe.\n"; log += numFails + " fehlgeschlagene Importe.\n"; InfoBox ib = new InfoBox(log, true, new Dimension(300, 150), null); ib.setVisible(true); } } catch (Exception e) { logMessages += "\nUnable to import file '" + filename + "'.\nWrong file format?\nImporter says: \n" + e.toString() + "\n" + getST(e, true) + "\n\n"; MyLogger.handleException(e); } System.err.println("Importing - Fin"); logMessages += "Importing - Fin" + "\n\n"; } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { logMessages += "\nUnable to run thread for '" + filename + "'.\nWrong file format?\nImporter says: \n" + e.toString() + "\n" + getST(e, true) + "\n\n"; MyLogger.handleException(e); } return true; }
From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java
License:Open Source License
public boolean 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 . j a v a 2 s.c o 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("/de/bund/bfr/knime/openkrise/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } try (HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is))) { 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); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return true; }
From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java
License:Apache License
public void initializeWorkbook() throws Exception { if (inputFile != null) { // open existing files if (currentType == SpreadsheetTyp.XLS) { if (readPassword != null) { try { // switch on decryption Biff8EncryptionKey.setCurrentUserPassword(readPassword); FileInputStream fin = new FileInputStream(inputFile); workbook = new HSSFWorkbook(fin); fin.close();/*from www . ja va 2 s .c om*/ } finally { // switch off Biff8EncryptionKey.setCurrentUserPassword(null); readPassword = null; } } else { FileInputStream fin = new FileInputStream(inputFile); workbook = new HSSFWorkbook(fin); fin.close(); } } else if (currentType == SpreadsheetTyp.XLSX) { if (createStreamingXMLWorkbook) { FileInputStream fin = new FileInputStream(inputFile); try { ZipSecureFile.setMinInflateRatio(0); workbook = new SXSSFWorkbook(new XSSFWorkbook(fin), rowAccessWindow); } finally { if (fin != null) { try { fin.close(); } catch (IOException ioe) { // ignore } } } } else { if (readPassword != null) { FileInputStream fin = new FileInputStream(inputFile); POIFSFileSystem filesystem = new POIFSFileSystem(fin); EncryptionInfo info = new EncryptionInfo(filesystem); Decryptor d = Decryptor.getInstance(info); InputStream dataStream = null; try { if (!d.verifyPassword(readPassword)) { throw new Exception( "Unable to process: document is encrypted and given password does not match!"); } // decrypt dataStream = d.getDataStream(filesystem); // use open input stream workbook = new XSSFWorkbook(dataStream); dataStream.close(); } catch (GeneralSecurityException ex) { throw new Exception("Unable to read and parse encrypted document", ex); } finally { if (dataStream != null) { try { dataStream.close(); } catch (IOException ioe) { // ignore } } if (fin != null) { try { fin.close(); } catch (IOException ioe) { // ignore } } } readPassword = null; } else { FileInputStream fin = new FileInputStream(inputFile); try { workbook = new XSSFWorkbook(fin); } finally { if (fin != null) { try { fin.close(); } catch (IOException ioe) { // ignore } } } } } } } else { // create new workbooks if (currentType == SpreadsheetTyp.XLS) { workbook = new HSSFWorkbook(); } else if (currentType == SpreadsheetTyp.XLSX) { if (createStreamingXMLWorkbook) { workbook = new SXSSFWorkbook(new XSSFWorkbook(), rowAccessWindow); } else { workbook = new XSSFWorkbook(); } } } setupDataFormatStyle(); }
From source file:de.maklerpoint.office.Lucene.Indexer.java
License:Open Source License
private void indexFileorDir(String fileName) throws IOException { listFiles(new File(fileName)); for (File f : queue) { FileReader fr = null;// ww w . j a v a 2 s . c om try { if (f.getName().startsWith(".")) { // System.out.println("Versteckte datei: " + f.getName()); // TODO add html, xml parsers } else if (f.getName().endsWith(".htm") || f.getName().endsWith(".html") || f.getName().endsWith(".xml") || f.getName().endsWith(".txt")) { Document doc = new Document(); //=================================================== // add contents of file //=================================================== fr = new FileReader(f); doc.add(new Field("contents", fr)); //=================================================== //adding second field which contains the path of the file //=================================================== doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.ANALYZED)); /** * Adding Typ */ doc.add(new Field("type", String.valueOf(FileTypes.TXT), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("modified", df.format(f.lastModified()), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("filesize", String.valueOf(FormatFileSize.formatSize(f.length(), FormatFileSize.KB)), Field.Store.YES, Field.Index.NOT_ANALYZED)); writer.addDocument(doc); } else if (f.getName().endsWith(".pdf")) { PDFParser parser = new PDFParser(new FileInputStream(f)); parser.parse(); COSDocument cd = parser.getDocument(); PDFTextStripper stripper = new PDFTextStripper(); String text = stripper.getText(new PDDocument(cd)); Document doc = new Document(); doc.add(new Field("contents", text, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("type", String.valueOf(FileTypes.PDF), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("modified", df.format(f.lastModified()), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("filesize", String.valueOf(FormatFileSize.formatSize(f.length(), FormatFileSize.KB)), Field.Store.YES, Field.Index.NOT_ANALYZED)); writer.addDocument(doc); cd.close(); } else if (f.getName().endsWith(".doc") || f.getName().endsWith(".docx")) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); WordExtractor extractor = new WordExtractor(fs); String wordText = extractor.getText(); Document doc = new Document(); doc.add(new Field("contents", wordText, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("type", String.valueOf(FileTypes.DOC), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("modified", df.format(f.lastModified()), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("filesize", String.valueOf(FormatFileSize.formatSize(f.length(), FormatFileSize.KB)), Field.Store.YES, Field.Index.NOT_ANALYZED)); writer.addDocument(doc); } else if (f.getName().endsWith(".xls") || f.getName().endsWith(".xlsx")) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); ExcelExtractor extractor = new ExcelExtractor(fs); String excelText = extractor.getText(); Document doc = new Document(); doc.add(new Field("contents", excelText, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("type", String.valueOf(FileTypes.XLS), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("modified", df.format(f.lastModified()), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("filesize", String.valueOf(FormatFileSize.formatSize(f.length(), FormatFileSize.KB)), Field.Store.YES, Field.Index.NOT_ANALYZED)); writer.addDocument(doc); } else if (f.getName().endsWith(".ppt") || f.getName().endsWith(".pptx")) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); PowerPointExtractor extractor = new PowerPointExtractor(fs); String ppttext = extractor.getText(); Document doc = new Document(); doc.add(new Field("contents", ppttext, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("path", f.getPath(), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("modified", df.format(f.lastModified()), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("type", String.valueOf(FileTypes.PPT), Field.Store.YES, Field.Index.NOT_ANALYZED)); doc.add(new Field("filesize", String.valueOf(FormatFileSize.formatSize(f.length(), FormatFileSize.KB)), Field.Store.YES, Field.Index.NOT_ANALYZED)); writer.addDocument(doc); } if (Log.logger.isDebugEnabled()) { Log.logger.debug("Lucene | Neue Datei indexiert: " + f); } } catch (Exception e) { if (Log.logger.isDebugEnabled()) { Log.logger.debug("Datei konnte nicht indexiert werden: " + f, e); } continue; } finally { // fr.close(); } } writer.optimize(); queue.clear(); }
From source file:dk.defxws.fedoragsearch.server.TransformerToText.java
License:Open Source License
private static Stream getTextFromXLS(InputStream doc) throws GenericSearchException { long time = System.currentTimeMillis(); boolean errorFlag = Boolean.parseBoolean(Config.getCurrentConfig().getIgnoreTextExtractionErrors()); HSSFWorkbook wb = null;//from w w w. j av a 2s. com ExcelExtractor excelExtractor = null; try { wb = new HSSFWorkbook(new POIFSFileSystem(doc)); excelExtractor = new ExcelExtractor(wb); StringBuffer buffer = new StringBuffer(excelExtractor.getText().trim()); Stream stream = new Stream(); stream.write(buffer.toString().getBytes(Constants.XML_CHARACTER_ENCODING)); stream.lock(); if (logger.isDebugEnabled()) { logger.debug("extracting text from xls needed " + (System.currentTimeMillis() - time)); } return stream; } catch (Exception e) { if (errorFlag) { logger.warn("", e); return createErrorStream(xlsTextExtractionErrorString); } else { throw new GenericSearchException("cannot parse xls-file", e); } } finally { excelExtractor = null; wb = null; } }