InputStream inp =getClass().getResourceAsStream("filename.xls")); Workbook wb = WorkbookFactory.create(inp); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb); Sheet sheet= wb.getSheetAt(0); Iterator<Row> objIterator = sheet.rowIterator(); while(objIterator.hasNext()){ Row row = objIterator.next(); Cell cellValue = row.getCell(0); objFormulaEvaluator.evaluate(cellValue); // This will evaluate the cell, And any type of cell will return string value String cellValueStr = objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator); }
private void handleNonStringCell(StringBuilder text, Cell cell, DataFormatter formatter) { CellType type = cell.getCellType(); if (type == CellType.FORMULA) { type = cell.getCachedFormulaResultType(); } if (type == CellType.NUMERIC) { CellStyle cs = cell.getCellStyle(); if (cs != null && cs.getDataFormatString() != null) { String contents = formatter.formatRawCellContents( cell.getNumericCellValue(), cs.getDataFormat(), cs.getDataFormatString()); checkMaxTextSize(text, contents); text.append(contents); return; } } // No supported styling applies to this cell String contents = ((XSSFCell)cell).getRawValue(); if (contents != null) { checkMaxTextSize(text, contents); text.append(contents); } }
/** * Enables excel style rounding mode (round half up) on the * Decimal Format given. */ public static void setExcelStyleRoundingMode(DecimalFormat format) { setExcelStyleRoundingMode(format, RoundingMode.HALF_UP); }
DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale Cell cell = sheet.getRow(i).getCell(0); String j_username = formatter.formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.
/** * 格式化数字或日期值 * * @param value 值 * @param numFmtIndex 数字格式索引 * @param numFmtString 数字格式名 * @return 格式化后的值 */ public static String formatCellContent(String value, int numFmtIndex, String numFmtString) { if (null != numFmtString) { try { value = new DataFormatter().formatRawCellContents(Double.parseDouble(value), numFmtIndex, numFmtString); } catch (NumberFormatException e) { // ignore } } return value; }
Sheet sheet = cell.getSheet(); Workbook wb = sheet.getWorkbook(); Row row = cell.getRow(); int column = cell.getColumnIndex(); if (region.isInRange(row.getRowNum(), column)) { if (!useMergedCells) { cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); cellType = cell.getCachedFormulaResultType(); Font font = wb.getFontAt(style.getFontIndexAsInt()); sval = formatter.formatCellValue(cell, dummyEvaluator); } catch (Exception e) { sval = String.valueOf(cell.getNumericCellValue());
Workbook wb = new HSSFWorkbook(); // Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(119710179); // Or do it on one line. row.createCell(1).setCellValue(119710179); row.createCell(2).setCellValue(createHelper.createRichTextString("119710179 ")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); String cellvalue = "" + cell.getNumericCellValue(); DataFormatter formatter = new DataFormatter(Locale.US); System.out.println("cellvalue--" + formatter.formatCellValue(cell));
Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet); Row rRow = rSheet.getRow(this.currentRow); if ((rRow==null) || (rRow.getLastCellNum()<0)) { this.currentRow++; return new SpreadSheetCellDAO[0]; // emtpy row result = new SpreadSheetCellDAO[rRow.getLastCellNum()]; for (int i=0;i<rRow.getLastCellNum();i++) { Cell currentCell=rRow.getCell(i); if (currentCell==null) { result[i]=null; } else { String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator); String formula = ""; if (currentCell.getCellType()==CellType.FORMULA) { formula = currentCell.getCellFormula(); Comment currentCellComment = currentCell.getCellComment(); String comment = ""; if (currentCellComment!=null) {
if (file11.exists()) { String dt = sh1.getRow(0).getCell(1).getStringCellValue(); if (!dt.equalsIgnoreCase("Date")) { Iterator<Row> rowIterator1 = sh1.iterator(); while (rowIterator1.hasNext()) { Row row = rowIterator1.next(); DataFormatter df = new DataFormatter();//instantiate DataFormatter class for reading the cell without changing the cell type Cell cl0 = row.getCell(0); Cell cl1 = row.getCell(1); CellStyle cs1 = cl0.getCellStyle(); CellStyle cs2 = cl1.getCellStyle(); String s1 = new String(df.formatCellValue(cl0));//store cell value as string String s2 = new String(df.formatCellValue(cl1));//store cell value as string cl1.setCellValue(s1);//perform swapping cl0.setCellStyle(cs2); cl1.setCellStyle(cs1);//perform swapping on formatting cl0.setCellValue(s2);//perform swapping } } } else { System.out.println("File does not exist.................."); } } catch (Exception e) { } finally { FileOutputStream out = new FileOutputStream(Report_File2); workbook11.write(out); out.close(); }
// Do this once DataFormatter formatter = new DataFormatter(); // Once per row for (Row row : sheet) { String exCode = formatter.formatCellValue( row.getCell(2) ); String prCode = formatter.formatCellValue( row.getCell(3) ); Cell code = row.createCell(4, Cell.CELL_TYPE_STRING); code.setCellValue(exCode + prCode); }
DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); System.out.println(" Row " + row.getRowNum()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndexAsInt()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); System.out.println(" " + formatter.formatCellValue(cell));
DataFormatter formatter = new DataFormatter(); Font defaultFont = wb.getFontAt((short) 0); if( row != null ) { Cell cell = row.getCell(column);
) { for (Row row : reader) { System.out.println("row[0] is of type " + row.getCell(0).getCellType()); Cell cell = row.getCell(0); String value = ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { value = new DataFormatter().formatCellValue(cell);
private List<Object> parseExcelRow(Row row, FormulaEvaluator evaluator, DataFormatter formatter) { List<Object> parsedRow = new ArrayList<Object>(); if(row != null) { int lastCellNum = row.getLastCellNum(); for(int i = 0; i < lastCellNum; i++) { Cell cell = row.getCell(i); String cellValue = ""; if(cell != null) { if(cell.getCellType() != Cell.CELL_TYPE_FORMULA) { cellValue = formatter.formatCellValue(cell); } else { cellValue = formatter.formatCellValue(cell, evaluator); } } parsedRow.add(cellValue); } } return parsedRow; }
Workbook workbook = WorkbookFactory.create(new File("input.xlsx")); DataFormatter formatter = new DataFormatter(); Sheet s = workbook.getSheetAt(0); for (Row r : s) { for (Cell c : r) { System.out.println(formatter.formatCellValue(c)); } }
InputStream is = new FileInputStream("Read.xlsx"); Workbook wb = WorkbookFactory.create(is); Sheet sheet = wb.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); Row r = (Row)rowIter.next(); short lastCellNum = r.getLastCellNum(); int[] dataCount = new int[lastCellNum]; int col = 0; while(cellIter.hasNext()) { Cell cell = (Cell)cellIter.next(); col = cell.getColumnIndex(); dataCount[col] += 1; DataFormatter df = new DataFormatter(); data = df.formatCellValue(cell); System.out.println("Data: " + data);
DataFormatter fmt = new DataFormatter(); Row r = sheet.getRow(10); Cell c = r.getCell(2, Row.RETURN_BLANK_AS_NULL); if (c == null) { // There's no value in this cell } else { System.out.println("Cell K2 is " + fmt.formatCellValue(c)); }
DataFormat df = workBook.createDataFormat(); CellStyle dateStyle = wb.createCellStye(); setDataFormat.setDataFormat(df.getFormat("dd-mmm-yy")); Cell cell = row.createCell(0); cell.setCellStyle(dateStyle); // Set it to be a date Calendar c = Calendar.getInstance(); c.set(2013,9-1,1); // Don't forget months are 0 based on Calendar cell.setCellValue( c.getTime() ); // Get it formatted as a string DataFormatter formatter = new DataFormatter(); String cellAsStr = formatter.formatCellValue(cell); // cellAsStr will now be "01-Sep-13" // based on the format rules applied to the cell
DataFormatter formatter; if(locale == null) { formatter = new DataFormatter(); } else { formatter = new DataFormatter(locale); for(Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) { Cell cell = ri.next(); if(cell.getCellType() == CellType.FORMULA) { if (formulasNotResults) { String contents = cell.getCellFormula(); checkMaxTextSize(text, contents); text.append(contents); } else { if (cell.getCachedFormulaResultType() == CellType.STRING) { handleStringCell(text, cell); } else {
String toFind = "needle in haystack"; Workbook wb = WorkbookFactory.create(new File("input.xlsx")); DataFormatter formatter = new DataFormatter(); Sheet sheet1 = wb.getSheetAt(0); for (Row row : sheet1) { for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); // is it an exact match? if (toFind.equals(text)) { System.out.println("Text matched at " + cellRef.formatAsString()); } // is it a partial match? else if (text.contains(toFind)) { System.out.println("Text found as part of " + cellRef.formatAsString()); } } }