List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
/** * * @param file//ww w . j av a 2s. com * @return * @throws FileNotFoundException * @throws InvalidFormatException * @throws IOException */ /*this method convert a multipart file to json object */ @Override public JSONObject convert(MultipartFile file, String name) throws FileNotFoundException, InvalidFormatException, IOException { // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx"); File filez = File.createTempFile("fichier", "xslx"); file.transferTo(filez); FileInputStream inp = new FileInputStream(filez); Workbook workbook = WorkbookFactory.create(inp); //Sheet sheet = workbook.getSheetAt( 0 ); Sheet sheet = workbook.getSheet(name); // Start constructing JSON. JSONObject json = new JSONObject(); // Iterate through the rows. JSONArray rows = new JSONArray(); for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); JSONObject jRow = new JSONObject(); // Iterate through the cells. JSONArray cells = new JSONArray(); for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) { Cell cell = cellsIT.next(); // System.out.println(cell.getCellType()); // cells.put(cell.getDateCellValue()); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cells.put(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cells.put(cell.getDateCellValue()); } else { cells.put(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cells.put(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cells.put(cell.getCellFormula()); break; default: System.out.println(); } } jRow.put("cell", cells); rows.put(cells); //rows.put( jRow ); } // Create the JSON. json.put("rows", rows); System.out.println(json.toString()); return json; }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
@Override public JSONObject convert(String file, String name) throws FileNotFoundException, InvalidFormatException, IOException { // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx"); File filez = File.createTempFile("fichier", "xslx"); byte[] data = Base64.decodeBase64(file); FileOutputStream fos = new FileOutputStream(filez); fos.write(data);//ww w. ja v a2 s . c o m fos.close(); //file.transferTo(filez); FileInputStream inp = new FileInputStream(filez); Workbook workbook = WorkbookFactory.create(inp); //Sheet sheet = workbook.getSheetAt( 0 ); Sheet sheet = workbook.getSheet(name); // Start constructing JSON. JSONObject json = new JSONObject(); // Iterate through the rows. JSONArray rows = new JSONArray(); for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); JSONObject jRow = new JSONObject(); // Iterate through the cells. JSONArray cells = new JSONArray(); for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) { Cell cell = cellsIT.next(); if (cell.getCellType() == CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { cells.put(cell.getDateCellValue()); } else cells.put(cell.getNumericCellValue()); } else cells.put(cell.getStringCellValue()); } jRow.put("cell", cells); rows.put(cells); //rows.put( jRow ); } // Create the JSON. json.put("rows", rows); System.out.println(json.toString()); return json; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public List<Object> readexcel(String path) { // String data = ""; List<Object> table = new ArrayList<Object>(); try {/* www . j av a 2 s . c o m*/ // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rows = new ArrayList<String>(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: rows.add(cell.getRichStringCellValue().getString()); // data+=cell.getRichStringCellValue().getString(); // System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { rows.add(cell.getDateCellValue() + ""); // data+=cell.getDateCellValue(); // System.out.print(cell.getDateCellValue()); } else { rows.add(cell.getNumericCellValue() + ""); // data+=cell.getNumericCellValue(); // System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: rows.add(cell.getBooleanCellValue() + ""); // data+=cell.getBooleanCellValue(); // System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: rows.add(cell.getCellFormula() + ""); // data+=cell.getCellFormula(); // System.out.print(cell.getCellFormula()); break; default: // System.out.print(""); } // data += "-"; // System.out.print(" - "); } table.add(rows); // data += ";;;"; // System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return table; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public String readexcel2(String path) { try {/*from w w w . ja v a 2 s . c om*/ // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.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_STRING: System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.print(cell.getDateCellValue()); } else { System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.print(cell.getCellFormula()); break; default: System.out.print(""); } System.out.print(" - "); } System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return null; }
From source file:com.vodafone.poms.ii.helpers.ActivityLoader.java
public Date getDateCellValue(Cell cell) { return cell.getDateCellValue(); }
From source file:com.wabacus.system.dataimport.filetype.XlsFileProcessor.java
License:Open Source License
private Object getCellValue(Cell cell) { if (cell == null) return null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {/*ww w.ja v a2s . c o m*/ return String.valueOf(cell.getNumericCellValue()); /*double d=cell.getNumericCellValue(); if(d-(int)d<Double.MIN_VALUE) { // ?int return (int)d; }else { return cell.getNumericCellValue(); }*/ } case Cell.CELL_TYPE_BLANK: return ""; default: return null; } }
From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java
License:Open Source License
/** * ?Cell?/* w w w. ja va2 s . co m*/ * @param cell * @return */ private Object getCellFormatValue(Cell cell) { if (null == cell) { return ""; } DataFormatter formatter = new DataFormatter(); switch (cell.getCellTypeEnum()) { case STRING: return cell.getRichStringCellValue().getString(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return Math.round(cell.getNumericCellValue()); } case BOOLEAN: return cell.getBooleanCellValue(); case FORMULA: return cell.getCellFormula(); case BLANK: return ""; default: return ""; } }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?excel/* w ww . ja v a 2s . c o m*/ * @param cell * @return */ private static String getCellValue(Cell cell) { String cellValue = ""; DecimalFormat df = new DecimalFormat("#"); if (cell == null || cell.equals("") || cell.getCellType() == Cell.CELL_TYPE_BLANK) { System.out.println(cellValue); return cellValue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString().trim(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = cell.getDateCellValue().toString(); } else { cellValue = df.format(cell.getNumericCellValue()).toString(); } break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()).trim(); break; default: cellValue = ""; } return cellValue; }
From source file:connect.Bootstrap.java
public static boolean bootstrap() { Connection conn = DatabaseConnectionManager.connect(); try {/* w w w.j av a2s. co m*/ int noOfLines = 0; ArrayList<Data> list = new ArrayList<>(); Set<Customer> clist = new HashSet<>(); Set<Outlet> olist = new HashSet<>(); InputStream is = new FileInputStream(new File("./excel/SMUX - Outlet Data V1.xlsx")); StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .sheetIndex(0) // index of sheet to use (defaults to 0) .read(is); // InputStream or File for XLSX file (required) int counter = 0; for (Row r : reader) { counter++; //initialize a data object Data data = new Data(0, 0, "NULL", 0, "NULL", "", "Outlet", 0, 0, "", "", 0, 0, 0); if (noOfLines > 0) { // For each row, iterate through each columns Iterator<Cell> cellIterator = r.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: //customer id int custid = (int) cell.getNumericCellValue(); data.setCustomerId(custid); break; case 1: //age try { int age = (int) cell.getNumericCellValue(); data.setAge(age); } catch (Exception e) { //leave it as 0 } break; case 2: //gender String gender = cell.getStringCellValue(); data.setGender(gender); //System.out.println("case 2"); break; case 3: //transact id data.setTransactId((int) cell.getNumericCellValue()); break; case 4: //transact date data.setTransactDate(df.format(cell.getDateCellValue())); break; case 5: //transact time time.setTime(cell.getDateCellValue()); data.setTransactTime(df2.format(time.getTime())); break; case 6: //outlet data.setOutlet(cell.getStringCellValue()); break; case 7: //outlet district data.setOutletDistrict((int) cell.getNumericCellValue()); break; case 8: //transact details id data.setTransactDetailsId((int) cell.getNumericCellValue()); break; case 9: //item //try { data.setItem(cell.getStringCellValue()); break; case 10: //item description data.setItemDesc(cell.getStringCellValue()); break; case 11: //quantity data.setQuantity((int) cell.getNumericCellValue()); break; case 12: //price data.setPrice(cell.getNumericCellValue()); break; case 13: //spending double spending = cell.getNumericCellValue(); data.setSpending(spending); break; default: } } list.add(data); } noOfLines++; if (noOfLines == 32740) { //establish connection, sql, execute sql try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeBatch(); pstmt.close(); conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } noOfLines = 1; //System.out.println("batch submitted"); } else if (counter > 556560 && counter <= 556580) { try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); //pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeUpdate(); //conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } } } } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return true; }
From source file:csv.impl.ExcelReader.java
License:Open Source License
/** * Returns the value of the specified cell. * If the cell contained// w ww . j a v a 2s . c o m * a formula, the formula is evaluated before returning the row. * @param cell cell object * @return value of cell */ public Object getValue(Cell cell) { if (cell == null) return null; int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA && !isEvaluateFormulas()) { cellType = cell.getCachedFormulaResultType(); } switch (cellType) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: return evaluateCellValue(cell); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellValue(); } return null; }