List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue
public HSSFRichTextString getRichStringCellValue()
From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java
License:Apache License
/** * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils * ein sheet in der excel-Datei.//from w w w.ja va 2 s.c om * * @param excelSheet * Die excel-Datei. * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei * zurck. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<String[][]> exportWorkbook(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<String[][]> sheetList = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); String[][] excelSheetInTDArray = null; excelSheetInTDArray = new String[rows][columns]; for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { excelSheetInTDArray[i][j] = ""; } else { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cellType == Cell.CELL_TYPE_ERROR) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_FORMULA) { excelSheetInTDArray[i][j] = cell.getCellFormula(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue()); } else if (cellType == Cell.CELL_TYPE_STRING) { excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString(); } } } } } sheetList.add(excelSheetInTDArray); } return sheetList; }
From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java
License:Apache 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 ww .j a v a2 s. c o 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(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { 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); } return sl; }
From source file:no.abmu.common.excel.PoiExcelImpl.java
License:Open Source License
/** * Returns the value of the specified column as a String. * * @param row/*ww w. java2 s .c om*/ * @param columnNumber * @return */ private String getStringValue(HSSFRow row, int columnNumber) { Assert.checkRequiredArgument("row", row); Assert.checkRequiredArgument("columnNumber", columnNumber); HSSFCell cell = row.getCell(columnNumber); if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_NUMERIC: /* For now - convert to long */ return Long.toString(Double.valueOf(cell.getNumericCellValue()).longValue()); case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString richTextString = cell.getRichStringCellValue(); return richTextString.toString(); default: return null; } }
From source file:no.trank.openpipe.parse.ms.ExcelParser.java
License:Apache License
private String getCellText(final HSSFCell cell) { String ret = null;/*from w ww . j a v a 2 s . c om*/ // skip formula/error cells switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: ret = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: ret = cell.getRichStringCellValue() + ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: ret = cell.getBooleanCellValue() ? "true" : "false"; } if (ret != null) { ret = ret.trim(); } return ret != null && ret.length() > 0 ? ret : null; }
From source file:org.adempiere.impexp.AbstractExcelExporter.java
License:Open Source License
/** * Export to given stream/* ww w.j a v a 2 s. co m*/ * @param out * @throws Exception */ private void export(OutputStream out) throws Exception { HSSFSheet sheet = createTableSheet(); String sheetName = null; // int colnumMax = 0; for (int rownum = 0, xls_rownum = 1; rownum < getRowCount(); rownum++, xls_rownum++) { setCurrentRow(rownum); boolean isPageBreak = false; HSSFRow row = sheet.createRow(xls_rownum); // for all columns int colnum = 0; for (int col = 0; col < getColumnCount(); col++) { if (colnum > colnumMax) colnumMax = colnum; // if (isColumnPrinted(col)) { HSSFCell cell = row.createCell(colnum); // line row Object obj = getValueAt(rownum, col); int displayType = getDisplayType(rownum, col); if (obj == null) ; else if (DisplayType.isDate(displayType)) { Timestamp value = (Timestamp) obj; cell.setCellValue(value); } else if (DisplayType.isNumeric(displayType)) { double value = 0; if (obj instanceof Number) { value = ((Number) obj).doubleValue(); } cell.setCellValue(value); } else if (DisplayType.YesNo == displayType) { boolean value = false; if (obj instanceof Boolean) value = (Boolean) obj; else value = "Y".equals(obj); cell.setCellValue( new HSSFRichTextString(Msg.getMsg(getLanguage(), value == true ? "Y" : "N"))); } else { String value = fixString(obj.toString()); // formatted cell.setCellValue(new HSSFRichTextString(value)); } // HSSFCellStyle style = getStyle(rownum, col); cell.setCellStyle(style); // Page break if (isPageBreak(rownum, col)) { isPageBreak = true; sheetName = fixString(cell.getRichStringCellValue().getString()); } // colnum++; } // printed } // for all columns // // Page Break if (isPageBreak) { closeTableSheet(sheet, sheetName, colnumMax); sheet = createTableSheet(); xls_rownum = 0; isPageBreak = false; } } // for all rows closeTableSheet(sheet, sheetName, colnumMax); // m_workbook.write(out); out.close(); // // Workbook Info if (CLogMgt.isLevelFine()) { log.fine("Sheets #" + m_sheetCount); log.fine("Styles used #" + m_styles.size()); } }
From source file:org.alinous.poi.PoiManager.java
License:GNU General Public License
public String getValue(int col, int row, int sheetNum) { HSSFSheet sheet = this.wb.getSheetAt(sheetNum); HSSFRow hssfRow = sheet.getRow(row); if (hssfRow == null) { hssfRow = sheet.createRow(row);//from ww w .j ava 2 s . c o m } HSSFCell cell = hssfRow.getCell(col); if (cell == null) { cell = hssfRow.createCell(col); } HSSFRichTextString text = cell.getRichStringCellValue(); return text.getString(); }
From source file:org.amanzi.splash.importer.ExcelImporter.java
License:Open Source License
/** * Creates a child spreadsheet inside parent spreadsheet * * @param sheet sheet to import//from ww w . j ava2s . c o m * @param sheetName name of sheet * @param monitor monitor */ @SuppressWarnings(value = { "deprecation", "unchecked" }) private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) { spreadsheetNode = null; spreadsheetName = sheetName; createSpreadsheet(rootSpreadsheet); try { Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); //display row number in the console. LOGGER.debug("Row No.: " + row.getRowNum()); //once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator(); int R = row.getRowNum(); if ((R % 20) == 0) { updateTransaction(transaction); } while (cells.hasNext()) { HSSFCell cell = cells.next(); LOGGER.debug("Cell No.: " + cell.getCellNum()); int C = cell.getCellNum(); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. LOGGER.debug("===================================================="); LOGGER.debug("Numeric value: " + cell.getNumericCellValue()); LOGGER.debug("===================================================="); String def = Double.toString(cell.getNumericCellValue()); Cell c = new Cell(R, C, def, def, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); LOGGER.debug("===================================================="); LOGGER.debug("String value: " + richTextString.getString()); LOGGER.debug("===================================================="); Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(), new CellFormat()); saveCell(c); break; } case HSSFCell.CELL_TYPE_FORMULA: // cell type string. String cellFormula = "=" + cell.getCellFormula().toLowerCase(); Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); LOGGER.debug("===================================================="); LOGGER.debug("Formula value: " + cellFormula); LOGGER.debug("===================================================="); break; default: { // types other than String and Numeric. LOGGER.debug("Type not supported."); break; } } } } } finally { updateTransaction(transaction); } }
From source file:org.apache.ofbiz.product.spreadsheetimport.ImportProductServices.java
License:Apache License
/** * This method is responsible to import spreadsheet data into "Product" and * "InventoryItem" entities into database. The method uses the * ImportProductHelper class to perform its operation. The method uses "Apache * POI" api for importing spreadsheet (xls files) data. * * Note : Create the spreadsheet directory in the ofbiz home folder and keep * your xls files in this folder only./*from w w w . j ava2 s. c o m*/ * * @param dctx the dispatch context * @param context the context * @return the result of the service execution * @throws IOException */ public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx, Map<String, ? extends Object> context) throws IOException { Delegator delegator = dctx.getDelegator(); Locale locale = (Locale) context.get("locale"); // System.getProperty("user.dir") returns the path upto ofbiz home // directory String path = System.getProperty("user.dir") + "/spreadsheet"; List<File> fileItems = new LinkedList<File>(); if (UtilValidate.isNotEmpty(path)) { File importDir = new File(path); if (importDir.isDirectory() && importDir.canRead()) { File[] files = importDir.listFiles(); // loop for all the containing xls file in the spreadsheet // directory for (int i = 0; i < files.length; i++) { if (files[i].getName().toUpperCase().endsWith("XLS")) { fileItems.add(files[i]); } } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale)); } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale)); } if (fileItems.size() < 1) { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale) + path); } for (File item : fileItems) { // read all xls file and create workbook one by one. List<Map<String, Object>> products = new LinkedList<Map<String, Object>>(); List<Map<String, Object>> inventoryItems = new LinkedList<Map<String, Object>>(); POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(new FileInputStream(item)); wb = new HSSFWorkbook(fs); } catch (IOException e) { Debug.logError("Unable to read or create workbook from file", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotCreateWorkbookFromFile", locale)); } // get first sheet HSSFSheet sheet = wb.getSheetAt(0); wb.close(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (row != null) { // read productId from first column "sheet column index // starts from 0" HSSFCell cell2 = row.getCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); String productId = cell2.getRichStringCellValue().toString(); // read QOH from ninth column HSSFCell cell5 = row.getCell(5); BigDecimal quantityOnHand = BigDecimal.ZERO; if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) quantityOnHand = new BigDecimal(cell5.getNumericCellValue()); // check productId if null then skip creating inventory item // too. boolean productExists = ImportProductHelper.checkProductExists(productId, delegator); if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) { products.add(ImportProductHelper.prepareProduct(productId)); if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0) inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand, delegator.getNextSeqId("InventoryItem"))); else inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO, delegator.getNextSeqId("InventoryItem"))); } int rowNum = row.getRowNum() + 1; if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) { Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module); } } } // create and store values in "Product" and "InventoryItem" entity // in database for (int j = 0; j < products.size(); j++) { GenericValue productGV = delegator.makeValue("Product", products.get(j)); GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j)); if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) { try { delegator.create(productGV); delegator.create(inventoryItemGV); } catch (GenericEntityException e) { Debug.logError("Cannot store product", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotStoreProduct", locale)); } } } int uploadedProducts = products.size() + 1; if (products.size() > 0) Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module); } return ServiceUtil.returnSuccess(); }
From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java
License:Open Source License
/** * ????/*from ww w . j a v a2s . com*/ * * @param sheet * @param rowIndex * @return */ protected String[] readLine(HSSFSheet sheet, int rowIndex) { HSSFRow row = sheet.getRow(rowIndex); if (logger.isDebugEnabled()) { logger.debug("values count:{}" + row.getLastCellNum()); } List attrList = new ArrayList(); for (short i = 0; i < row.getLastCellNum(); i++) { HSSFCell cell = row.getCell(i); if (null != cell) { String attr = cell.getRichStringCellValue().getString(); if (StringUtils.isEmpty(attr)) { break; } else { attrList.add(attr.trim()); } } else { break; } } String[] attrs = new String[attrList.size()]; attrList.toArray(attrs); logger.debug("has attrs {}", attrs); return attrs; }
From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java
License:Open Source License
/** * @see ?cell??/*from ww w . ja va 2 s.c om*/ * @param cell * @param objClass * @return */ private String getCelValue(HSSFCell cell) { if ((cell == null) || (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)) return ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getRichStringCellValue().getString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return numberFormat.format(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (cell.getBooleanCellValue()) return "true"; else return "false"; } else { return ""; } }