List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:org.encuestame.business.search.SearchUtils.java
License:Apache License
/** * Create Spreadsheets Document.//w w w . jav a2 s . c o m * @param file Spreadsheet {@link File}. * @param Long attachmentId. * @return {@link Document} * @throws FileNotFoundException */ public static Document createSpreadsheetsDocument(final File file) throws Exception { InputStream is = new FileInputStream(file); StringBuilder contents = new StringBuilder(); POIFSFileSystem fileSystem = new POIFSFileSystem(is); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } Document doc = SearchUtils.addFields(file, contents.toString()); return doc; }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * /* w w w.j a va 2 s .com*/ * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } final StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } HSSFWorkbook wb; try { wb = new HSSFWorkbook(is); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { final HSSFCell cell = row.getCell((short) k); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case HSSFCell.CELL_TYPE_FORMULA: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getCellFormula().toString()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_BOOLEAN: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getBooleanCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_ERROR: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getErrorCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_STRING: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getStringCellValue().toString()).append(" "); return null; } }); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.forzaframework.util.XlsUtils.java
License:Apache License
public static HSSFSheet getSheet(HSSFWorkbook wb, String sheetName, Boolean createNewSheet, Integer indexSheet) {//from w ww .j a va 2 s . c o m HSSFSheet sheet = null;//Revisamos si vamos a crear una hoja nueva o con una ya existente. if ((createNewSheet != null && createNewSheet) || wb.getNumberOfSheets() == 0) { //Creamos una hoja nueva if (sheetName != null) { sheet = wb.createSheet(sheetName); } else { sheet = wb.createSheet(); } } else { //Revisamos si existe la hoja con el nombre especificado if (indexSheet == null && sheetName != null) { sheet = wb.getSheet(sheetName); } if (sheet == null) { //Trabajamos con una hoja ya existente if (indexSheet == null) { indexSheet = 0; } if (sheetName != null) { wb.setSheetName(indexSheet, sheetName); } sheet = wb.getSheetAt(indexSheet); } } return sheet; }
From source file:org.gageot.excel.core.ExcelTemplate.java
License:Apache License
/** * Read the sheet names of an Excel file. * @return an array containing a java.lang.String for each sheet. Empty if not sheet. * @throws DataAccessException if there is any problem *///from w ww .j a v a 2s . co m public String[] getSheetNames() { return read(new Function<HSSFWorkbook, String[]>() { @Override public String[] apply(HSSFWorkbook workbook) { int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetCount; i++) { sheetNames[i] = workbook.getSheetName(i); } return sheetNames; } }); }
From source file:org.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel.//from w ww .j a v a 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.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 . j a va2s . 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.isource.util.CSVUtils.java
private static HSSFWorkbook evaluateFormulas(HSSFWorkbook wb) { FormulaEvaluator evaluator = null;//from w w w. ja v a 2 s . c o m evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = wb.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); if (sheetNum == 0 && c.getColumnIndex() == r.getPhysicalNumberOfCells() - 1) { switch (c.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: break; case Cell.CELL_TYPE_STRING: break; } } } } } } return wb; }
From source file:org.jlibrary.core.search.extraction.ExcelExtractor.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * /*from www .j a v a 2s. co m*/ * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuilder result = new StringBuilder(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if ((excelWb.getSheetName(i) != null) && !excelWb.getSheetName(i).equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && !text.equals("")) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:org.lisapark.octopus.util.json.ExcelSardineUtils.java
License:Open Source License
public static void main(String[] args) { Map<String, Integer> prodCellIndexMap = Maps.newHashMap(); prodCellIndexMap.put(SHOP, 0);// w ww . j a va 2 s . co m prodCellIndexMap.put(SHIFT, 0); prodCellIndexMap.put(MACHINE, 0); prodCellIndexMap.put(PRODUCT, 0); prodCellIndexMap.put(PRODUCT_TYPE, 0); prodCellIndexMap.put(MATERIAL_TYPE, 0); prodCellIndexMap.put(RAW_MATERIAL, 4); prodCellIndexMap.put(TOTAL_MATERIALS, 5); prodCellIndexMap.put(TOTAL_PRODUCTS, 6); Map<String, Integer> wrhCellIndexMap = Maps.newHashMap(); wrhCellIndexMap.put(WAREHOUSE, 1); wrhCellIndexMap.put(ITEM, 1); wrhCellIndexMap.put(ITEM_TYPE, 1); wrhCellIndexMap.put(BEGINING, 0); wrhCellIndexMap.put(INCOMING, 1); wrhCellIndexMap.put(OUTGOING, 2); wrhCellIndexMap.put(ENDING, 3); try { String excelFile = "http://173.72.110.131:8080/WebDavServer/iPlast/Warehouse/"; // Get all xml files Sardine sardine = SardineFactory.begin("", ""); List<DavResource> resources = sardine.getResources(excelFile); for (DavResource res : resources) { String url = res.getPath(); //getAbsoluteUrl(); if (res.isDirectory()) { continue; } else { Map<String, String> props = res.getCustomProps(); if (props.get(PROCESSED) == null) { InputStream isData = sardine.get(url); HSSFWorkbook book = new HSSFWorkbook(isData); int index = 0; int increament = 1; if (book.getNumberOfSheets() > index) { if (increament == 0) { // increament = PROD_OUTLINE_INCREAMENT; increament = WRH_OUTLINE_INCREAMENT; } Sheet sheet = book.getSheetAt(index); if (sheet == null) { continue; } // Iterate through the rows. int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } Map<String, Object> rowMap = Maps.newHashMap(); int start = 2; Row dateRow = sheet.getRow(8); int end = dateRow.getLastCellNum(); for (int dateShift = start; dateShift < end - 4; dateShift = dateShift + 4) { rowMap.put(DATE, formatDate(dateRow.getCell(dateShift).getStringCellValue())); System.out.println(dateRow.getCell(dateShift).getStringCellValue()); Sheet _sheet = book.getSheetAt(index); for (Iterator<Row> rowsIt = _sheet.rowIterator(); rowsIt.hasNext();) { Row row = rowsIt.next(); if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) { continue; } Cell cell = row.getCell(1); int indent = cell.getCellStyle().getIndention(); int absIndent = indent / increament; // if (processRowWrhSs(rowMap, row, wrhCellIndexMap, absIndent, dateShift)) { System.out.println(rowMap); } } } } props.put(PROCESSED, TRUE); sardine.setCustomProps(url, props, null); } else { System.out.println("Property PROCESSED: " + props.get(PROCESSED)); List<String> removeProps = new ArrayList<String>(1); removeProps.add(PROCESSED); sardine.setCustomProps(url, null, removeProps); } break; } } } catch (FileNotFoundException ex) { Exceptions.printStackTrace(ex); } catch (IOException ex) { Exceptions.printStackTrace(ex); } }
From source file:org.lisapark.octopus.util.json.JsonUtils.java
License:Open Source License
public String jsonStringFromSSbyIndex(HSSFWorkbook workbook, int index) throws JSONException, FileNotFoundException, IOException { // Get Sheet by index. if (workbook.getNumberOfSheets() > index) { Sheet sheet = workbook.getSheetAt(index); return jsonFromSS(sheet, 2); } else {//from www . j a v a2 s . co m return null; } }