List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:com.mgmtp.jfunk.data.excel.ExcelDataSourceTest.java
License:Apache License
private ExcelDataSource createDataSource(final String path, final DataOrientation dataOrientation) { Configuration config = new Configuration(Charsets.UTF_8); config.put("dataSource.excel.0.path", path); config.put("dataSource.excel.0.dataOrientation", dataOrientation.name()); return new ExcelDataSource(config, new DataFormatter()); }
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
public Column buildStringColumn() { String[] colValues = new String[values.size()]; boolean[] missing = new boolean[values.size()]; Iterator<CellValue> it = values.iterator(); Iterator<Cell> jt = cells.iterator(); DataFormatter fmt = new DataFormatter(); int counter = 0; while (it.hasNext()) { CellValue cv = it.next();//ww w.j a v a 2s . c om Cell cell = jt.next(); if (cv == null) { missing[counter] = true; } else { switch (detectedTypes.get(counter)) { case Boolean: case Numeric: // format according to Excel format colValues[counter] = fmt.formatCellValue(cell, this.evaluator); break; case DateTime: // format according to dateTimeFormatter colValues[counter] = Workbook.dateTimeFormatter .format(DateUtil.getJavaDate(cv.getNumberValue()), dateTimeFormat); break; case String: colValues[counter] = cv.getStringValue(); break; default: throw new IllegalArgumentException("Unknown data type detected!"); } } ++counter; } return new Column(colValues, missing, DataType.String); }
From source file:com.myjeeva.poi.CustomXSSFSheetXMLHandler.java
License:Apache License
/** * Accepts objects needed while parsing. * * @param styles Table of styles// w w w .j ava2 s. c o m * @param strings Table of shared strings */ public CustomXSSFSheetXMLHandler(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetContentsHandler, boolean formulasNotResults) { this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults); }
From source file:com.simopuve.helper.ExcelFiller.java
public static void fillHeader(Sheet sheet) { Map headerMap = PropertiesMap.getPDVHeaderPropertiesCoordinatesByName(); try {/*from www . j av a 2 s . c om*/ PDVHeader bean = getFilledHeader(); BeanInfo beanInfo = Introspector.getBeanInfo(PDVHeader.class); DataFormatter formatter = new DataFormatter(); for (PropertyDescriptor propertyDesc : beanInfo.getPropertyDescriptors()) { String propertyName = propertyDesc.getName(); if (!UsefulConstants.T_CLASS.equals(propertyName)) { PropertyCoordinates tmpCoordinate = (PropertyCoordinates) headerMap.get(propertyName); Object value = propertyDesc.getReadMethod().invoke(bean); Row row = sheet.getRow(tmpCoordinate.getRowX()); Cell cell = row.getCell(tmpCoordinate.getCellY()); Class valueClass = value.getClass(); valueClass.cast(value); if (null != valueClass.getName()) { switch (valueClass.getName()) { case UsefulConstants.T_STRING: cell.setCellValue((String) value); break; case UsefulConstants.T_INTEGER: cell.setCellValue((Integer) value); break; case UsefulConstants.T_DATE: Date date = (Date) value; Calendar cal = Calendar.getInstance(); cal.setTime(date); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH); int day = cal.get(Calendar.DAY_OF_MONTH); cell.setCellValue((Integer) day); cell = row.getCell(tmpCoordinate.getCellY() + 1); cell.setCellValue((Integer) month); cell = row.getCell(tmpCoordinate.getCellY() + 2); cell.setCellValue((Integer) year); break; default: break; } } } } } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException ex) { Logger.getLogger(ExcelFiller.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.simopuve.helper.ExcelFiller.java
public static void fillHeader(PDVHeader header, Sheet sheet) { Map headerMap = PropertiesMap.getPDVHeaderPropertiesCoordinatesByName(); try {/*from ww w . ja va 2 s . c o m*/ PDVHeader bean = header; BeanInfo beanInfo = Introspector.getBeanInfo(PDVHeader.class); DataFormatter formatter = new DataFormatter(); for (PropertyDescriptor propertyDesc : beanInfo.getPropertyDescriptors()) { String propertyName = propertyDesc.getName(); if (!UsefulConstants.T_CLASS.equals(propertyName)) { PropertyCoordinates tmpCoordinate = (PropertyCoordinates) headerMap.get(propertyName); if (tmpCoordinate == null) continue; Object value = propertyDesc.getReadMethod().invoke(bean); Row row = sheet.getRow(tmpCoordinate.getRowX()); Cell cell = row.getCell(tmpCoordinate.getCellY()); Class valueClass = value.getClass(); valueClass.cast(value); if (null != valueClass.getName()) { switch (valueClass.getName()) { case UsefulConstants.T_STRING: cell.setCellValue((String) value); break; case UsefulConstants.T_INTEGER: cell.setCellValue((Integer) value); break; case UsefulConstants.T_DATE: Date date = (Date) value; Calendar cal = Calendar.getInstance(); cal.setTime(date); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); cell.setCellValue((Integer) day); cell = row.getCell(tmpCoordinate.getCellY() + 1); cell.setCellValue((Integer) month); cell = row.getCell(tmpCoordinate.getCellY() + 2); cell.setCellValue((Integer) year); break; case UsefulConstants.T_BOOLEAN: cell.setCellValue(((Boolean) value) ? "SI" : "NO"); default: break; } } } } } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException ex) { Logger.getLogger(ExcelFiller.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.simopuve.helper.ReadPVDFromFile.java
private static void fillHeader(PDVHeader PDVHeader, Sheet sheet, Boolean mall) { PropertyCoordinates tmpCoordinate;/*from w w w.ja v a2 s . c o m*/ String text; DataFormatter formatter = new DataFormatter(); Map headerMap = getPDVHeaderPropertiesCoordinatesByName(); PDVHeader.setMall(mall); tmpCoordinate = (PropertyCoordinates) headerMap.get("pointOfSaleName"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setPointOfSaleName(text); tmpCoordinate = (PropertyCoordinates) headerMap.get("address"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setAddress(text); tmpCoordinate = (PropertyCoordinates) headerMap.get("comuna"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setComuna(text); tmpCoordinate = (PropertyCoordinates) headerMap.get("completeName"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setCompleteName(text); tmpCoordinate = (PropertyCoordinates) headerMap.get("numberOfPeopleAM"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setNumberOfPeopleAM(Integer.parseInt(text)); tmpCoordinate = (PropertyCoordinates) headerMap.get("numberOfPeoplePM"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setNumberOfPeoplePM(Integer.parseInt(text)); tmpCoordinate = (PropertyCoordinates) headerMap.get("peopleWithBags"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); PDVHeader.setPeopleWithBags(Integer.parseInt(text)); tmpCoordinate = (PropertyCoordinates) headerMap.get("surveyDate"); String day = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, null); String month = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY() + 1, formatter, sheet, null); String year = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY() + 2, formatter, sheet, null); Date currentDate; try { Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "convirtiendo valores:" + month); int tmpMoth = Integer.parseInt(month) + 1; currentDate = new SimpleDateFormat("dd/MM/yyyy").parse(day + "/" + tmpMoth + "/" + year); PDVHeader.setSurveyDate(currentDate); } catch (ParseException ex) { Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.simopuve.helper.ReadPVDFromFile.java
private static List<PDVRow> getRowsFromWorkbook(Sheet sheet) { Map rowMap = getPDVRowPropertiesCoordinatesByName(); List<PDVRow> PDVRowList = new ArrayList<>(); DataFormatter formatter = new DataFormatter(); PropertyCoordinates tmpCoordinate;// w w w . j a v a2s.co m String text; sheet.getPhysicalNumberOfRows(); //TODO revisar si es siempre as int longitud = (sheet.getPhysicalNumberOfRows() - FIRST_ROW) - 2; int endRows = FIRST_ROW + longitud; int i = FIRST_ROW; String personNum = getTextFromCell(FIRST_ROW, 0, formatter, sheet, sheet.getRow(FIRST_ROW)); boolean isPersonNumberEmpty = personNum.isEmpty(); while (!isPersonNumberEmpty) { Row row = sheet.getRow(i); PDVRow PDVRow = new PDVRow(); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceBrand"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceBrand(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceModel"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceModel(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("contractType"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setContractType(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceMode"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceMode(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("additionalCharacteristics"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setAdditionalCharacteristics(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("planRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPlanRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChange"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChange(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChangeReason"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChangeReason(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("personNumber"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPersonNumber(Integer.parseInt(text)); isPersonNumberEmpty = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, sheet.getRow(i + 1)).isEmpty(); tmpCoordinate = (PropertyCoordinates) rowMap.get("expressRefillValue"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); String tmpTxt = text.replace("$", ""); tmpTxt = tmpTxt.replaceAll("\\s+", ""); tmpTxt = tmpTxt.replaceAll(",", ""); tmpTxt = tmpTxt.replaceAll(".", ""); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "valor a convertir:" + tmpTxt); if (tmpTxt.isEmpty()) PDVRow.setExpressRefillValue(0); else if (tmpTxt == "" || tmpTxt == null) PDVRow.setExpressRefillValue(0); else PDVRow.setExpressRefillValue(Integer.parseInt(tmpTxt)); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtCard"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtCard(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtChip"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtChip(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtAccessory"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtAccessory(!("No".equals(text))); PDVRowList.add(PDVRow); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, PDVRow.toString()); i++; } Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "Tama\u00f1o de rows: {0}", PDVRowList.size()); return PDVRowList; }
From source file:com.sonicle.webtop.core.io.input.ExcelFileReader.java
License:Open Source License
public ExcelFileReader() { this.fmt = new DataFormatter(); }
From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java
License:Open Source License
public MemoryExcelFileReader() { this.fmt = new DataFormatter(); }
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);/*from w ww . j a v a 2s . c o m*/ startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }