List of usage examples for org.apache.poi.ss.usermodel DataFormatter formatCellValue
public String formatCellValue(Cell cell)
Returns the formatted value of a cell as a String regardless of the cell type.
From source file:Utilities.BatchInDJMSHelper.java
private void foregroundColorSetUpV2(Row row, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String ssn = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell)); //System.out.println(lastName); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2); // String ctrlNumString = ctrlNumCell.getStringCellValue(); Cell soDateCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2); String soDate = df.formatCellValue(soDateCell); colorLeaves(ssn, ctrlNumCell, soDate, myWorkBook, legitLvMap); }//from w ww. ja v a 2s . c om } }
From source file:Utilities.BatchInDJMSHelper.java
private void foregroundColorSetUpV1(Row row, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String lastFour = GlobalVar.last4Generator(df.formatCellValue(ssnCell)); Cell lastNameCell = row.getCell(GlobalVar.LAST_NAME_CELL_INDEX_V1); String lastName = null;// w w w . ja v a2 s .co m if (lastNameCell != null) { lastName = lastNameCell.getStringCellValue(); } System.out.println(lastName); if (SSN_DATABASE != null) { String ssn = SSN_DATABASE.getSSN(lastName, lastFour); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); // String ctrlNumString = ctrlNumCell.getStringCellValue(); Cell soDateCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String soDate = df.formatCellValue(soDateCell); colorLeaves(ssn, ctrlNumCell, soDate, myWorkBook, legitLvMap); } } } }
From source file:utilities.DebtMgmtBatchInDJMS.java
private void buildSSNMap(String xlsxFileName) { File xlsxFile = new File(xlsxFileName); try {/*from ww w . java2s. co m*/ FileInputStream fis = new FileInputStream(xlsxFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // 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; DataFormatter df = new DataFormatter(); //for ssn // if (numCell == TITLE_LEN){ // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); Cell cell = row.getCell(SSN_INDEX); String ssn = df.formatCellValue(cell); //return ***-**-**** ssn = GlobalVar.fullSSNgenerator(ssn); SSN_MAP.put(ssn, INITIAL_MSG); //initial ssn map . value is for the status } // } else { // JOptionPane.showMessageDialog(null, "Invalid Xlsx file!"); // } } } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!"); } catch (IOException ex) { JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!"); Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:utilities.DebtMgmtBatchInDJMS.java
private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { if (row != null) { Cell ssnCell = row.getCell(SSN_CELL_INDEX); DataFormatter df = new DataFormatter(); String ssn = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell)); if (ssn != null) { colorTransactions(ssn, ssnCell, myWorkBook); }/*from www .j a va2s .c om*/ } }
From source file:utilities.DMPORosterToMapGenerator.java
public DMPORosterToMapGenerator(String xlsxFileName) throws FileNotFoundException, IOException { db = new TreeMap(); File myFile = new File(xlsxFileName); FileInputStream fis = new FileInputStream(myFile); System.out.println(xlsxFileName); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIterator = mySheet.iterator(); DataFormatter df = new DataFormatter(); // System.out.println(IndexedColors.YELLOW); // System.out.println(IndexedColors.BLUE.getIndex()); // System.out.println(IndexedColors.RED.getIndex()); // System.out.println(IndexedColors.WHITE); // System.out.println(IndexedColors.BLACK); // System.out.println(IndexedColors.GREEN); rowIterator.next(); // skip the header row while (rowIterator.hasNext()) { Row row = rowIterator.next();//from www .j a v a2 s . c om Cell cell = row.getCell(FILE_AT_DMPO_INDX); // File at DMPO int type = cell.getCellType(); //System.out.println(cell.getStringCellValue()); if (type == HSSFCell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Yes")) { // File at DMPO CellStyle style = cell.getCellStyle(); // getFillBackgroundColor() always 64. getFillForegroundColor() is 64 for white // getFillForegroundColor() is 0 for yellow // System.out.println(style.getFillForegroundColor()); // //if (style.getFillForegroundColorColor() != null) // System.out.println(style.getFillForegroundColorColor()); // // System.out.println(style.getFillBackgroundColor()); // //if (style.getFillBackgroundColorColor() != null) // System.out.println(style.getFillBackgroundColorColor()); //if(style.getFillForegroundColor() != IndexedColors.YELLOW.getIndex()) { Color color = style.getFillForegroundColorColor(); if (color == null || color.toString().equals(GlobalVar.WHITE)) { // no fill or fill with white Cell ssnCell = row.getCell(SSN_INDX); String ssnString = df.formatCellValue(ssnCell); //return ***-**-**** ssnString = readSSN(ssnString).trim(); if (!db.containsKey(ssnString)) { List<String> list = new LinkedList<String>(); String ssn = displayFormatSSN(ssnString).trim(); String name = row.getCell(NAME_INDX).getStringCellValue().trim(); String dutyStation = row.getCell(ORIGIN_INDX).getStringCellValue().trim(); String typeCM = row.getCell(TYPE_CM_INDX).getStringCellValue().trim(); //String typeCM = row.getCell(TYPE_CM_INDX).getStringCellValue(); list.add(ssn); list.add(name); list.add(dutyStation); list.add(typeCM); db.put(ssnString, list); } } } } }
From source file:utilities.RCFRosterToMapGenerator.java
private Map<String, List<String>> getPersonalInfo(String xlsxFileName) throws FileNotFoundException, IOException { Map<String, List<String>> map = new TreeMap<>(); File myFile = new File(xlsxFileName); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook wb = new XSSFWorkbook(fis); Sheet sheet = wb.getSheetAt(SHEET_NO_PERSONAL); Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.next(); //skip the header row; DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from www .ja va 2 s. c om Cell cellReg = row.getCell(REG_INDX_PERSONAL); String reg = GlobalVar.trimString(cellReg.getStringCellValue()); if (!map.containsKey(reg)) { List<String> list = new LinkedList<>(); String ssn = GlobalVar.fullSSNgenerator( GlobalVar.trimString(row.getCell(SSN_INDX_PERSONAL).getStringCellValue())); String name = GlobalVar.trimString(row.getCell(INMATE_NAME_INDX_PERSONAL).getStringCellValue()); String confinementType = GlobalVar .trimString(row.getCell(CONF_TYPE_INDX_PERSONAL).getStringCellValue()); //String arrDt = row.getCell(ARR_DT_INDX_PERSONAL).getStringCellValue(); //doesn't work since cell is numeric String arrDt = GlobalVar.trimString(df.formatCellValue(row.getCell(ARR_DT_INDX_PERSONAL))); list.add(ssn); // list.add(name); list.add(confinementType); list.add(arrDt); map.put(reg, list); } } return map; }
From source file:voedselbank.MainFrame.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed JFileChooser fc = new JFileChooser(); fc.showOpenDialog(this); DataFormatter formatter = new DataFormatter(); DateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd"); File file = fc.getSelectedFile(); try {/*w w w . j a va 2 s .c om*/ FileInputStream fileInputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Intakestatus"); boolean begin = false; boolean einde = false; for (int i = 0; begin == false; i++) { String content = formatter.formatCellValue(worksheet.getRow(i).getCell(0)); if (content.equals("Kaartnummer")) { begin = true; i = i + 1; while (einde == false) { if (!formatter.formatCellValue(worksheet.getRow(i).getCell(0)).equals("")) { Date startdatum_uitgifte = new Date(0); Date intakedatum = new Date(0); Date datum_herintake = new Date(0); Date datum_stopzetting = new Date(0); String kaartnummer = formatter.formatCellValue(worksheet.getRow(i).getCell(0)); String naam = formatter.formatCellValue(worksheet.getRow(i).getCell(1)); String naamPartner = formatter.formatCellValue(worksheet.getRow(i).getCell(2)); String telefoonnummer = formatter.formatCellValue(worksheet.getRow(i).getCell(3)); String email = formatter.formatCellValue(worksheet.getRow(i).getCell(4)); String mobiel = formatter.formatCellValue(worksheet.getRow(i).getCell(5)); String aantalPersonen = formatter.formatCellValue(worksheet.getRow(i).getCell(6)); String adres = formatter.formatCellValue(worksheet.getRow(i).getCell(13)); String postcode = formatter.formatCellValue(worksheet.getRow(i).getCell(14)); String plaats = formatter.formatCellValue(worksheet.getRow(i).getCell(15)); String status = formatter.formatCellValue(worksheet.getRow(i).getCell(16)); String intaker = formatter.formatCellValue(worksheet.getRow(i).getCell(17)); if (worksheet.getRow(i).getCell(19).getDateCellValue() != null) { startdatum_uitgifte = worksheet.getRow(i).getCell(19).getDateCellValue(); } if (worksheet.getRow(i).getCell(18).getDateCellValue() != null) { intakedatum = worksheet.getRow(i).getCell(18).getDateCellValue(); } if (worksheet.getRow(i).getCell(20).getDateCellValue() != null) { datum_herintake = worksheet.getRow(i).getCell(20).getDateCellValue(); } if (worksheet.getRow(i).getCell(21).getDateCellValue() != null) { datum_stopzetting = worksheet.getRow(i).getCell(21).getDateCellValue(); } String reden_stopzetting = formatter.formatCellValue(worksheet.getRow(i).getCell(22)); String uitgifte_punt = formatter.formatCellValue(worksheet.getRow(i).getCell(31)); String pakket = formatter.formatCellValue(worksheet.getRow(i).getCell(32)); Client client = new Client(Integer.parseInt(kaartnummer), naam, telefoonnummer, adres, postcode, plaats, email, mobiel, Integer.parseInt(aantalPersonen), status, naamPartner); Voedselpakket voedselpakket = new Voedselpakket(startdatum_uitgifte, pakket); Intake intake = new Intake(0, 0, intaker, intakedatum, startdatum_uitgifte, datum_herintake, datum_stopzetting, reden_stopzetting); client.checkBestaat(client, uitgifte_punt, voedselpakket, intake); i++; } else { einde = true; } } } } } catch (Exception e) { System.out.println(e); } }