List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:com.vsquaresystem.safedeals.readyreckoner.ReadyReckonerService.java
public Vector read() throws IOException { File excelFile = attachmentUtils .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.READY_RECKONER); File[] listofFiles = excelFile.listFiles(); String fileName = excelFile + "/" + listofFiles[0].getName(); Vector cellVectorHolder = new Vector(); int type;/*from ww w . ja v a2s.c om*/ try { FileInputStream myInput = new FileInputStream(fileName); XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { XSSFRow myRow = (XSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); List list = new ArrayList(); while (cellIter.hasNext()) { XSSFCell myCell = (XSSFCell) cellIter.next(); if (myCell != null) { switch (myCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(new DataFormatter().formatCellValue(myCell)); list.add(new DataFormatter().formatCellValue(myCell)); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(new DataFormatter().formatCellValue(myCell)); list.add(new DataFormatter().formatCellValue(myCell)); break; case Cell.CELL_TYPE_STRING: System.out.println(new DataFormatter().formatCellValue(myCell)); list.add(new DataFormatter().formatCellValue(myCell)); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: System.out.println(new DataFormatter().formatCellValue(myCell)); list.add(new DataFormatter().formatCellValue(myCell)); break; case Cell.CELL_TYPE_FORMULA: break; } } } cellVectorHolder.addElement(list); } } catch (Exception e) { e.printStackTrace(); } return cellVectorHolder; }
From source file:com.vsquaresystem.safedeals.readyreckoner.ReadyReckonerService.java
private boolean saveToDatabase(Vector dataHolder) throws IOException { readyReckonerDAL.truncateAll();/*from ww w . ja va2s .co m*/ dataHolder.remove(0); Readyreckoner readyReckoner = new Readyreckoner(); String id = ""; String locationId = ""; String rrYear = ""; String rrRateLand = ""; String rrRatePlot = ""; String rrRateApartment = ""; System.out.println(dataHolder); DataFormatter formatter = new DataFormatter(); for (Iterator iterator = dataHolder.iterator(); iterator.hasNext();) { List list = (List) iterator.next(); logger.info("list", list); locationId = list.get(1).toString(); rrYear = list.get(2).toString(); rrRateLand = list.get(3).toString(); rrRatePlot = list.get(4).toString(); rrRateApartment = list.get(5).toString(); try { readyReckoner.setLocationId(Integer.parseInt(locationId)); readyReckoner.setRrYear(Double.parseDouble(rrYear)); readyReckoner.setRrRateLand(Double.parseDouble(rrRateLand)); readyReckoner.setRrRatePlot(Double.parseDouble(rrRatePlot)); readyReckoner.setRrRateApartment(Double.parseDouble(rrRateApartment)); readyReckonerDAL.insert(readyReckoner); } catch (Exception e) { e.printStackTrace(); } } File excelFile = attachmentUtils .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.READY_RECKONER); FileUtils.cleanDirectory(excelFile); return true; }
From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java
License:Open Source License
/** * ?Cell?/*from w ww. j a v a2s . c o 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:Controller.ThreadExcelImport.java
private String getStringFromCell(Cell cell) { int tipo = cell.getCellType(); String value = ""; switch (tipo) { //NUMERIC//from www . java2 s . c om case 0: DataFormatter df = new DataFormatter(); // value = df.formatCellValue(row.getCell(columnPos)); value = String.valueOf(cell.getNumericCellValue()).trim().toLowerCase(); //at.setString(columnName, value); break; //STRING case 1: value = cell.getStringCellValue().trim().toLowerCase(); //at.setString(columnName, value); break; // FORMULA // BLANK case 3: value = ""; //row.getCell(columnPos).getStringCellValue(); //at.setString(columnName, value); break; // BOOLEAN case 4: value = String.valueOf(cell.getBooleanCellValue()).trim().toLowerCase(); // at.setString(columnName, value); break; // NONE (ERROR) case 5: value = cell.getStringCellValue().trim().toLowerCase(); //at.setString(columnName, value); break; } return value; //return String.valueOf( cell.getNumericCellValue()); }
From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java
License:Apache License
protected DataFormatter getDataFormatter() { if (dataFormatter == null) { if (workbook instanceof HSSFWorkbook) { dataFormatter = new HSSFDataFormatter(); } else {/*from w w w. j av a 2s. com*/ dataFormatter = new DataFormatter(); } } return dataFormatter; }
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Returns the type of value from a cell *///ww w .ja va 2s . co m private static Object getTypeValue(final Class<?> type, final Cell cell) { Object typedValue = null; final DataFormatter formatter = new DataFormatter(); if (type == int.class) { typedValue = (int) cell.getNumericCellValue(); } else if (type == double.class) { typedValue = cell.getNumericCellValue(); } else if (type == boolean.class) { typedValue = cell.getBooleanCellValue(); } else if (type == String.class) { typedValue = formatter.formatCellValue(cell); } return typedValue; }
From source file:es.us.isa.jdataset.loader.ExcelToCSV.java
License:Apache License
/** * Open an Excel workbook ready for conversion. * * @param file An instance of the File class that encapsulates a handle * to a valid Excel workbook. Note that the workbook can be in * either binary (.xls) or SpreadsheetML (.xlsx) format. * @throws java.io.FileNotFoundException Thrown if the file cannot be located. * @throws java.io.IOException Thrown if a problem occurs in the file system. * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown * if invalid xml is found whilst parsing an input SpreadsheetML * file./*w ww . ja v a2 s . co m*/ */ private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream fis = null; try { System.out.println("Opening workbook [" + file.getName() + "]"); fis = new FileInputStream(file); // Open the workbook and then create the FormulaEvaluator and // DataFormatter instances that will be needed to, respectively, // force evaluation of forumlae found in cells and create a // formatted String encapsulating the cells contents. this.workbook = WorkbookFactory.create(fis); this.evaluator = this.getWorkbook().getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(); } finally { if (fis != null) { fis.close(); } } }
From source file:es.us.isa.jdataset.loader.ExcelToCSV.java
License:Apache License
/** * Open an Excel workbook ready for conversion. * * @param file An instance of the File class that encapsulates a handle * to a valid Excel workbook. Note that the workbook can be in * either binary (.xls) or SpreadsheetML (.xlsx) format. * @throws java.io.FileNotFoundException Thrown if the file cannot be located. * @throws java.io.IOException Thrown if a problem occurs in the file system. * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown * if invalid xml is found whilst parsing an input SpreadsheetML * file.//from ww w .j ava 2s.c om */ public void openWorkbook(InputStream fis) throws IOException, InvalidFormatException { try { System.out.println("Opening workbook [from input stream]"); // Open the workbook and then create the FormulaEvaluator and // DataFormatter instances that will be needed to, respectively, // force evaluation of forumlae found in cells and create a // formatted String encapsulating the cells contents. this.workbook = WorkbookFactory.create(fis); this.evaluator = this.getWorkbook().getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(); } finally { if (fis != null) { fis.close(); } } }
From source file:eventHandlers.CompareDrotVSRoster.java
private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); DataFormatter df = new DataFormatter(); String ssnString = df.formatCellValue(ssnCell); //return ***-**-**** ssnString = ssnString.replace("-", ""); Cell soCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String signOutDateString = df.formatCellValue(soCell); //return ***-**-**** Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssnString)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> leaves = proclibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); }//from w w w . j ava 2 s. c o m } } else if (rejlibrary.containsKey(ssnString)) { Map<String, String> leaves = rejlibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } else if (recylibrary.containsKey(ssnString)) { Map<String, String> leaves = recylibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } }
From source file:eventHandlers.XLSX2BatchHandler.java
public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try {//from w w w . j a v a2 s.c o m FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // check if the excel is the leave roster int globalCount = 1; MyDate soDate = new MyDate(); MyDate siDate = new MyDate(); while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<String, String>(); int keys = 0; //index of the cell // reset date every row Boolean soDateAssigned = false; Boolean siDateAssigned = false; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int type = cell.getCellType(); // distinguish the cell content, in the xlsx file, // SSN and dates are CELL_TYPE_NUMERIC // number of leave days is CELL_TYPE_FORMULA // the rest is CELL_TYPE_STRING if (type == HSSFCell.CELL_TYPE_NUMERIC && keys != GlobalVar.LAST4_CELL_INDEX_V1) { // dates DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String dateString = df.formatCellValue(cell); if (!soDateAssigned && !siDateAssigned) { //System.out.println(dateString); //150201 soDate = new MyDate(dateString); soDateAssigned = true; } else if (soDateAssigned && !siDateAssigned) { siDate = new MyDate(dateString); siDateAssigned = true; } else { // do nothing //System.out.println("We should never come here!"); } //String value = (int) cell.getNumericCellValue() + ""; // number of days container.put(keyList.get(keys), dateString); // System.out.println(value); // } else if (type == HSSFCell.CELL_TYPE_STRING // && keys == GlobalVar.SSN_CELL_INDEX){ } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** // String lastFour = cell.getStringCellValue(); //ssnString = ssnString.replace("-",""); lastFour = GlobalVar.last4Generator(lastFour); // System.out.println("SSN: " + lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); // } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // String days = soDate.getDaysDifftoString(siDate); // container.put(keyList.get(keys),days); // //reset the dates } else if (type == HSSFCell.CELL_TYPE_STRING) { String value; if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { String str2 = cell.getStringCellValue(); //System.out.println(str2); value = GlobalVar.getDMOLeaveArea(str2); //System.out.println(value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); //System.out.println(value); } else { value = cell.getStringCellValue(); } container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1)); String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); String leaveDays = GlobalVar.computeNumOfDays(new MyDate(signOutDate), new MyDate(signInDate)); int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, leaves); if (leaveMsg == GlobalVar.GOOD_LEAVE) { String lc = GlobalVar.LC; String inputSource = ctrlNum.substring(0, 2); String cycle = GlobalVar.CYCLE; String data = "0SB03" + SSN + first5 + signOutDate + signInDate + leaveType + leaveDays + leaveArea + ctrlNum + "000" + GlobalVar.whiteSpace() + lc + inputSource + cycle + "@" + GlobalVar.globalCountGenerator(globalCount); output.println(data); globalCount++; } else if (leaveMsg == GlobalVar.OVERLAP_LEAVE) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); String msg = "Line" + lineCount + " : " + ctrlNum + " " + lastName + " " + signOutDate + " - " + signInDate; JOptionPane.showMessageDialog(null, msg + "\n is overlapping with a leave posted before."); } else if (leaveMsg == GlobalVar.DUPLICATE_CTRL_NUM) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); JOptionPane.showMessageDialog(null, "Line" + lineCount + " : Duplicate control number " + ctrlNum + " for " + lastName + "."); } } fis.close(); output.close(); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } }