List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:uk.co.spudsoft.birt.emitters.excel.tests.NumberFormatsTest.java
License:Open Source License
@Test public void testRunReport() throws BirtException, IOException { debug = false;//ww w. java 2 s .c om InputStream inputStream = runAndRenderReport("NumberFormats.rptdesign", "xlsx"); assertNotNull(inputStream); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Number Formats Test Report", workbook.getSheetAt(0).getSheetName()); Sheet sheet = workbook.getSheetAt(0); assertEquals(22, this.firstNullRow(sheet)); assertEquals(3035, sheet.getColumnWidth(0)); assertEquals(3913, sheet.getColumnWidth(1)); assertEquals(7021, sheet.getColumnWidth(2)); assertEquals(4205, sheet.getColumnWidth(3)); assertEquals(3474, sheet.getColumnWidth(4)); assertEquals(2852, sheet.getColumnWidth(5)); assertEquals(3510, sheet.getColumnWidth(6)); assertEquals(2889, sheet.getColumnWidth(7)); assertEquals(2048, sheet.getColumnWidth(8)); DataFormatter formatter = new DataFormatter(); Locale locale = Locale.getDefault(); assertEquals("1", formatter.formatCellValue(sheet.getRow(1).getCell(1))); assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(2))); assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(3))); assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(4))); assertEquals("false", formatter.formatCellValue(sheet.getRow(1).getCell(5))); if (locale.getDisplayName().equals("en-US")) { assertEquals("Oct 11, 2019", formatter.formatCellValue(sheet.getRow(1).getCell(6))); assertEquals("1:18:46 PM", formatter.formatCellValue(sheet.getRow(1).getCell(7))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11-Oct-2019", formatter.formatCellValue(sheet.getRow(1).getCell(6))); assertEquals("13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(7))); } assertEquals("2", formatter.formatCellValue(sheet.getRow(2).getCell(1))); assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(2).getCell(2))); assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(2).getCell(3))); assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(2).getCell(4))); assertEquals("true", formatter.formatCellValue(sheet.getRow(2).getCell(5))); if (locale.getDisplayName().equals("en-US")) { assertEquals("Oct 11, 2019", formatter.formatCellValue(sheet.getRow(1).getCell(6))); assertEquals("1:18:46 PM", formatter.formatCellValue(sheet.getRow(1).getCell(7))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11-Oct-2019", formatter.formatCellValue(sheet.getRow(1).getCell(6))); assertEquals("13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(7))); } assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(5).getCell(1))); assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(5).getCell(2))); assertEquals("3.14", formatter.formatCellValue(sheet.getRow(5).getCell(3))); assertEquals("3.14", formatter.formatCellValue(sheet.getRow(5).getCell(4))); assertEquals("314.16%", formatter.formatCellValue(sheet.getRow(5).getCell(5))); assertEquals("3.14E00", formatter.formatCellValue(sheet.getRow(5).getCell(6))); assertEquals("3.14E00", formatter.formatCellValue(sheet.getRow(5).getCell(7))); assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(6).getCell(1))); assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(6).getCell(2))); assertEquals("6.28", formatter.formatCellValue(sheet.getRow(6).getCell(3))); assertEquals("6.28", formatter.formatCellValue(sheet.getRow(6).getCell(4))); assertEquals("628.32%", formatter.formatCellValue(sheet.getRow(6).getCell(5))); assertEquals("6.28E00", formatter.formatCellValue(sheet.getRow(6).getCell(6))); assertEquals("6.28E00", formatter.formatCellValue(sheet.getRow(6).getCell(7))); assertEquals("1", formatter.formatCellValue(sheet.getRow(9).getCell(1))); if (locale.getDisplayName().equals("en-US")) { assertEquals("October 11, 2019 1:18:46 PM", formatter.formatCellValue(sheet.getRow(9).getCell(2))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11 October 2019 13:18:46", formatter.formatCellValue(sheet.getRow(9).getCell(2))); } assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(9).getCell(3))); assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(9).getCell(4))); assertEquals("false", formatter.formatCellValue(sheet.getRow(9).getCell(5))); if (locale.getDisplayName().equals("en-US")) { assertEquals("10/11/19", formatter.formatCellValue(sheet.getRow(9).getCell(6))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11/10/19", formatter.formatCellValue(sheet.getRow(9).getCell(6))); } assertEquals("13:18", formatter.formatCellValue(sheet.getRow(9).getCell(7))); assertEquals("2", formatter.formatCellValue(sheet.getRow(10).getCell(1))); if (locale.getDisplayName().equals("en-US")) { assertEquals("October 11, 2019 1:18:46 PM", formatter.formatCellValue(sheet.getRow(9).getCell(2))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11 October 2019 13:18:46", formatter.formatCellValue(sheet.getRow(9).getCell(2))); } assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(10).getCell(3))); assertEquals("6.2831853072", formatter.formatCellValue(sheet.getRow(10).getCell(4))); assertEquals("true", formatter.formatCellValue(sheet.getRow(10).getCell(5))); if (locale.getDisplayName().equals("en-US")) { assertEquals("10/11/19", formatter.formatCellValue(sheet.getRow(9).getCell(6))); } else if (locale.getDisplayName().equals("en-GB")) { assertEquals("11/10/19", formatter.formatCellValue(sheet.getRow(9).getCell(6))); } assertEquals("13:18", formatter.formatCellValue(sheet.getRow(10).getCell(7))); assertEquals("MSRP $3.14", formatter.formatCellValue(sheet.getRow(15).getCell(1))); assertEquals("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-", sheet.getRow(19).getCell(1).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(2).getStringCellValue(), sheet.getRow(19).getCell(2).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(3).getStringCellValue(), sheet.getRow(19).getCell(3).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(4).getStringCellValue(), sheet.getRow(19).getCell(4).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(5).getStringCellValue(), sheet.getRow(19).getCell(5).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(6).getStringCellValue(), sheet.getRow(19).getCell(6).getCellStyle().getDataFormatString()); assertEquals(sheet.getRow(18).getCell(7).getStringCellValue(), sheet.getRow(19).getCell(7).getCellStyle().getDataFormatString()); } finally { inputStream.close(); } }
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); }/*www. ja v a 2 s. co m*/ } }
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;//from www. j a va2s . c om 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 {/*ww w. j a v a 2 s . c o 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); }//w ww . j a v a 2 s.c o m } }
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 w w w . j a v a2s .co m*/ 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 w w w .jav a 2 s. c o m 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:utilities.SmapSheetXMLHandler.java
License:Apache License
/** * Accepts objects needed while parsing. * * @param styles Table of styles//from ww w. j av a2s. c o m * @param strings Table of shared strings */ public SmapSheetXMLHandler(Styles styles, SharedStrings strings, SheetContentsHandler sheetContentsHandler, boolean formulasNotResults, SimpleDateFormat sdf) { this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults, sdf); }
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 {//from ww w. j a va 2s .c o m 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); } }