List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted
public static boolean isCellDateFormatted(Cell cell)
From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java
private static Object getFormulaValue(Cell cell) { Object cv = null;/*from ww w . j a v a2s . c om*/ FormulaEvaluator fe = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); CellValue v = fe.evaluate(cell); switch (v.getCellType()) { case Cell.CELL_TYPE_BLANK: { break; } case Cell.CELL_TYPE_BOOLEAN: { cv = v.getBooleanValue(); break; } case Cell.CELL_TYPE_ERROR: { cv = v.getErrorValue(); break; } //case Cell.CELL_TYPE_FORMULA: { // cv = cell.getCellFormula(); // break; //} case Cell.CELL_TYPE_NUMERIC: { double d = v.getNumberValue(); if (DateUtil.isCellDateFormatted(cell)) { Calendar cal = Calendar.getInstance(); cal.setTime(DateUtil.getJavaDate(d)); cv = cal.getTime(); } else { cv = d; } break; } case Cell.CELL_TYPE_STRING: { cv = v.getStringValue(); break; } default: { logger.log(Level.WARNING, "Unexpected formula cell type = {0}", v.getCellType()); break; } } return cv; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid/* ww w.j av a 2 s . co m*/ * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLSX(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); FileInputStream fs = new FileInputStream(filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { XSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { XSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLSX has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * @param filename/*from w ww . ja v a2 s .co m*/ * @param sheetNo * @param startindex * @param importDao * @return * @throws ServiceException */ public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao, HibernateTransactionManager txnManager) throws ServiceException { boolean commitedEx = false; DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("import_Tx"); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txnManager.getTransaction(def); Session session = txnManager.getSessionFactory().getCurrentSession(); try { String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df_full); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); int flushCounter = 0; for (int i = startindex; i <= maxRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { XSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) importDao.dumpFileRow(tableName, dataArray.toArray()); if (flushCounter % 30 == 0) { session.flush(); session.clear(); } flushCounter++; } } try { txnManager.commit(status); } catch (Exception ex) { commitedEx = true; throw ex; } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } catch (Exception ex) { if (!commitedEx) { //if exception occurs during commit then dont call rollback txnManager.rollback(status); } throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Get the value contained in the cell.//ww w. j av a 2s. c o m * * @param cell the examined cell * @return the value as Boolean, Numeric, String, Blank, Error or Formula */ public static Object getCellValue(Cell cell) { return cell == null ? "" : cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ? cell.getBooleanCellValue() : cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? (DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() : cell.getNumericCellValue()) : cell.getCellType() == Cell.CELL_TYPE_STRING ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_BLANK ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_ERROR ? cell.getErrorCellValue() : cell.getCellType() == Cell.CELL_TYPE_FORMULA ? cell.getCachedFormulaResultType() : cell.getStringCellValue(); }
From source file:com.larasolution.serverlts.FileUploadHandler.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // tablename=request.getParameter(tablename) //process only if its multipart content FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv"); String list = ""; List<List> allData = new ArrayList<List>(); List<String> parameters = new ArrayList<String>(); if (ServletFileUpload.isMultipartContent(request)) { try {/* w w w .ja v a 2 s.c o m*/ StringBuilder data = new StringBuilder(); List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); System.out.println(multiparts); for (FileItem item : multiparts) { if (item.isFormField()) { parameters.add(item.getFieldName()); System.out.println(parameters); } if (!item.isFormField()) { String name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); //System.out.println(File.separator); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook( new FileInputStream(UPLOAD_DIRECTORY + File.separator + name)); XSSFSheet zz = wBook.getSheetAt(0); FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator(); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = zz.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue()).append(","); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data.append( com.larasolution.modle.getDate.getDate5(cell.getDateCellValue())) .append(","); } else { data.append(cell.getNumericCellValue()).append(","); } break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue()).append(","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; case Cell.CELL_TYPE_FORMULA: Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString()); data.append(String.format("%.2f", value)).append(","); break; default: data.append(cell).append(""); } } data.append("\r\n"); //String k = data.substring(0, data.length() - 3); //ls.add(k); // data.setLength(0); } fos.write(data.toString().getBytes()); fos.close(); // } } savetosql(); request.setAttribute("message", "successfully uploaded "); } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.setAttribute("arrayfile", allData); request.setAttribute("names", parameters); RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp"); disp.forward(request, response); // System.out.println(allData.size()); // response.sendRedirect("send.jsp?arrayfile=" + list + ""); //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response); }
From source file:com.miraisolutions.xlconnect.data.DefaultColumnBuilder.java
License:Open Source License
protected void handleCell(Cell c, CellValue cv) { String msg;/*from w ww. ja v a 2 s . c om*/ // Determine (evaluated) cell data type switch (cv.getCellType()) { case Cell.CELL_TYPE_BLANK: addMissing(); return; case Cell.CELL_TYPE_BOOLEAN: addValue(c, cv, DataType.Boolean); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) addValue(c, cv, DataType.DateTime); else { boolean missing = false; for (int i = 0; i < missingValueNumbers.length; i++) { if (cv.getNumberValue() == missingValueNumbers[i]) { missing = true; break; } } if (missing) addMissing(); else addValue(c, cv, DataType.Numeric); } break; case Cell.CELL_TYPE_STRING: boolean missing = false; for (int i = 0; i < missingValueStrings.length; i++) { if (cv.getStringValue() == null || cv.getStringValue().equals(missingValueStrings[i])) { missing = true; break; } } if (missing) addMissing(); else addValue(c, cv, DataType.String); break; case Cell.CELL_TYPE_FORMULA: msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!"; cellError(msg); break; case Cell.CELL_TYPE_ERROR: msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - " + CellUtils.getErrorMessage(cv.getErrorValue()); cellError(msg); break; default: msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!"; cellError(msg); } }
From source file:com.mycompany.chartproject.ExcelReader.java
public Map<String, Double> getPieChartData(String repo) { Map<String, Double> map = new HashMap<>(); try {/*ww w .j a v a 2 s . c om*/ String fileName = "src/main/resources/Stabilityfinal.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int total = -1; int success = 0; int failure = 0; int unstable = 0; int aborted = 0; while (rowIterator.hasNext()) { ++total; Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue() + "\t\t"); } else { System.out.print(cell.getNumericCellValue() + "\t\t"); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equalsIgnoreCase("SUCCESS")) { ++success; } else if (cell.getStringCellValue().equalsIgnoreCase("FAILURE")) { ++failure; } else if (cell.getStringCellValue().equalsIgnoreCase("UNSTABLE")) { ++unstable; } else if (cell.getStringCellValue().equalsIgnoreCase("ABORTED")) { ++aborted; } System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); file.close(); FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } System.out.println("Total " + total); System.out.println("no. Successful " + success); System.out.println("no. Failures " + failure); System.out.println("no. Unstable " + unstable); int green = ((success * 100 / total)); double passedPercentage = (double) green / 100; System.out.println("Passed: " + passedPercentage); int red = ((failure * 100 / total)); double failedPercentage = (double) red / 100; System.out.println("Failed: " + failedPercentage); int orange = ((unstable * 100 / total)); double unstablePercentage = (double) orange / 100; System.out.println("Unstable: " + unstablePercentage); int abort = ((aborted * 100 / total)); double abortedPercentage = (double) abort / 100; System.out.println("Aborted: " + abortedPercentage); map.put("Failed", failedPercentage); map.put("Unstable", unstablePercentage); map.put("Passed", passedPercentage); map.put("Aborted", abortedPercentage); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:com.mycompany.gannaraputakehomeexam.ReadingFromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from w ww. j a va 2 s . com try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { SongsList song = new SongsList(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;/* w w w . j a va2s . co m*/ try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return songList; }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
private static String getTextValue(Cell cell, int treatAsCellType) { if (cell == null) { return null; }/*from www . ja v a 2s . c om*/ switch (treatAsCellType) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { Calendar cal = Calendar.getInstance(); cal.setTime(date); @SuppressWarnings("deprecation") int year = date.getYear(); if (year == -1) { // equivalent to 1899 which is the first data .. assume its a time String s = ODL_TIME_FORMATTER.format(date); return s; } // System.out.println(year); } return cell.getDateCellValue().toString(); } else { String ret = Double.toString(cell.getNumericCellValue()); if (ret.endsWith(".0")) { ret = ret.substring(0, ret.length() - 2); } return ret; } case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "T" : "F"; case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_BLANK: return null; } return ""; }