Example usage for org.apache.poi.ss.usermodel DataFormatter DataFormatter

List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataFormatter
  6. DataFormatter

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DataFormatter DataFormatter.

Prototype

public DataFormatter()

Source Link

Document

Creates a formatter using the Locale#getDefault() default locale .

Usage

From source file:com.talend.excel.xssf.event.ExcelReader.java

License:Open Source License

public Object call() throws Exception {
    OPCPackage pkg = null;/*from   w  w  w  . j  a  v a2 s . c o m*/
    try {
        if (fileURL != null) {
            pkg = OPCPackage.open(fileURL);
        } else {
            pkg = PackageHelper.open(is);
        }
        XSSFReader r = new XSSFReader(pkg);

        StylesTable styles = r.getStylesTable();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        sheetContentsHandler = new DefaultTalendSheetContentsHandler(cache);
        DataFormatter formatter = new DataFormatter();
        boolean formulasNotResults = false;

        XMLReader parser = XMLReaderFactory.createXMLReader();
        ContentHandler handler = new TalendXSSFSheetXMLHandler(styles, strings, sheetContentsHandler, formatter,
                formulasNotResults);
        parser.setContentHandler(handler);

        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) r.getSheetsData();
        List<InputStream> iss = new ArrayList<InputStream>();
        while (sheets.hasNext()) {
            InputStream sheet = sheets.next();
            String sheetName = sheets.getSheetName();

            boolean match = false;

            for (int i = 0; i < sheetNames.size(); i++) {
                if ((asRegexs.get(i) && sheetName.matches(sheetNames.get(i)))
                        || (!asRegexs.get(i) && sheetName.equals(sheetNames.get(i)))) {
                    match = true;
                    iss.add(sheet);
                    break;
                }
            }

            if (!match) {
                sheet.close();
            }
        }

        if (iss.size() < 1) {
            throw new RuntimeException("No match sheets");
        }

        for (InputStream is : iss) {
            try {
                InputSource sheetSource = new InputSource(is);
                sheetSource.setEncoding(charset);
                parser.parse(sheetSource);
            } finally {
                is.close();
            }
        }

    } finally {
        if (pkg != null) {
            pkg.revert();
        }
        cache.notifyErrorOccurred();
    }
    return null;
}

From source file:com.teradata.demo.utils.excel.XSSFSheetXMLHandler.java

License:Apache License

/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles//w w w.  j a  va2  s . c  om
 * @param strings Table of shared strings
 */
public XSSFSheetXMLHandler(StylesTable styles, ReadOnlySharedStringsTable strings,
        SheetContentsHandler sheetContentsHandler, boolean formulasNotResults) {
    this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults);
}

From source file:com.test.demo.ccbpay.XLSX2CSV.java

License:Apache License

/**
 * Parses and shows the content of one sheet
 * using the specified styles and shared-strings tables.
 *
 * @param styles/*from  w  ww. j  av a2s .c  o  m*/
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
        SheetContentsHandler sheetHandler, InputStream sheetInputStream)
        throws IOException, ParserConfigurationException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}

From source file:com.vsquaresystem.safedeals.amenity.AmenityService.java

public Vector read() throws IOException {
    File excelFile = attachmentUtils.getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.LOCATION);
    File[] listofFiles = excelFile.listFiles();
    String fileName = excelFile + "/" + listofFiles[0].getName();

    Vector cellVectorHolder = new Vector();
    int type;//  w  w  w  .  ja  v  a 2s . co  m
    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:

                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:

                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_STRING:

                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:

                        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.location.LocationService.java

public Vector read() throws IOException {
    File excelFile = attachmentUtils.getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.LOCATION);
    File[] listofFiles = excelFile.listFiles();
    String fileName = excelFile + "/" + listofFiles[0].getName();
    Vector cellVectorHolder = new Vector();
    int type;/*from  w  ww  .  ja  v  a2s .  com*/
    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:
                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        list.add(new DataFormatter().formatCellValue(myCell));
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        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.location.LocationService.java

public boolean saveExcelToDatabase() throws IOException {
    Vector dataHolder = read();/*from w w w . ja  va  2  s.co m*/
    dataHolder.remove(0);
    Location location = new Location();
    String id = "";
    String name = "";
    String description = "";
    String cityId = "";
    String locationTypeId = "";
    String locationCategories = "";
    String safedealZoneId = "";
    String majorApproachRoad = "";
    String advantage = "";
    String disadvantage = "";
    String population = "";
    String latitude = "";
    String longitude = "";
    String sourceOfWater = "";
    String publicTransport = "";
    String migrationRate = "";
    String distanceCenterCity = "";
    String isCommercialCenter = "";
    String distanceCommercialCenter = "";
    String imageUrl = "";

    System.out.println("line1785SAVE sop" + dataHolder);
    DataFormatter formatter = new DataFormatter();
    for (Iterator iterator = dataHolder.iterator(); iterator.hasNext();) {
        List list = (List) iterator.next();
        name = list.get(0).toString();
        description = list.get(1).toString();
        cityId = list.get(2).toString();
        locationTypeId = list.get(3).toString();
        locationCategories = list.get(4).toString();
        safedealZoneId = list.get(5).toString();
        majorApproachRoad = list.get(6).toString();
        advantage = list.get(7).toString();
        disadvantage = list.get(8).toString();
        population = list.get(9).toString();
        latitude = list.get(10).toString();
        longitude = list.get(11).toString();
        sourceOfWater = list.get(12).toString();
        publicTransport = list.get(13).toString();
        migrationRate = list.get(14).toString();
        distanceCenterCity = list.get(15).toString();
        isCommercialCenter = list.get(16).toString();
        distanceCommercialCenter = list.get(17).toString();
        imageUrl = list.get(18).toString();
        List<String> strList = new ArrayList<String>(Arrays.asList(locationCategories.split(",")));
        List<Integer> numberList = new ArrayList<Integer>();
        for (String number : strList) {
            numberList.add(Integer.parseInt(number));
        }
        try {
            location.setName(name);
            location.setDescription(description);
            location.setCityId(Integer.parseInt(cityId));
            location.setLocationTypeId(Integer.parseInt(locationTypeId));
            location.setLocationCategories(numberList);
            location.setSafedealZoneId(Integer.parseInt(safedealZoneId));
            location.setMajorApproachRoad(majorApproachRoad);
            location.setAdvantage(advantage);
            location.setDisadvantage(disadvantage);
            location.setPopulation(Integer.parseInt(population));
            location.setLatitude(Double.parseDouble(latitude));
            location.setLongitude(Double.parseDouble(longitude));
            location.setMigrationRatePerAnnum(MigrationRatePerAnnum.valueOf(migrationRate));
            location.setDistanceFromCentreOfCity(Double.parseDouble(distanceCenterCity));
            location.setDistanceFromCommercialCenter(Double.parseDouble(distanceCommercialCenter));
            location.setIsCommercialCenter(Boolean.valueOf(isCommercialCenter));
            location.setImageUrl(imageUrl);

            System.out.println("location line167 CHECKcheck" + location);
            System.out.println("numberList" + numberList);
            locationDAL.insert(location);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    File excelFile = attachmentUtils.getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.LOCATION);
    FileUtils.cleanDirectory(excelFile);
    return true;

}

From source file:com.vsquaresystem.safedeals.marketprice.MarketPriceService.java

public Vector read() throws IOException {

    File excelFile = attachmentUtils.getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.MARKET_PRICE);
    File[] listofFiles = excelFile.listFiles();
    String fileName = excelFile + "/" + listofFiles[0].getName();

    Vector cellVectorHolder = new Vector();
    int type;/*w ww .j  a  v  a2 s  . c o  m*/
    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;
                    }
                }

            }
            logger.info("Line Line108 {}" + list);
            System.out.println("MAINlist" + list);
            cellVectorHolder.addElement(list);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    return cellVectorHolder;

}

From source file:com.vsquaresystem.safedeals.marketprice.MarketPriceService.java

public boolean saveExcelToDatabase() throws IOException {

    Vector dataHolder = read();//from w ww. jav a2s.c  om
    marketPriceDAL.truncateAll();
    dataHolder.remove(0);
    System.out.println("data line 147" + dataHolder);
    MarketPrice marketPrice = new MarketPrice();
    String id = "";
    String cityId = "";
    String locationId = "";
    String year = "";
    String month = "";
    String mpAgriLandLowest = "";
    String mpAgriLandHighest = "";
    String mpPlotLowest = "";
    String mpPlotHighest = "";
    String mpResidentialLowest = "";
    String mpResidentialHighest = "";
    String mpCommercialLowest = "";
    String mpCommercialHighest = "";
    String sdZoneId = "";
    String locationTypeId = "";
    String locationCategories = "";
    String description = "";
    String majorApproachRoad = "";
    String sourceOfWater = "";
    String publicTransport = "";
    String advantage = "";
    String disadvantage = "";
    String population = "";
    String migrationRate = "";
    String isCommercialCenter = "";

    DataFormatter formatter = new DataFormatter();
    for (Iterator iterator = dataHolder.iterator(); iterator.hasNext();) {

        List list = (List) iterator.next();

        System.out.println("list for save" + list);
        cityId = list.get(1).toString();
        locationId = list.get(2).toString();
        year = list.get(3).toString();
        month = list.get(4).toString();
        mpAgriLandLowest = list.get(5).toString();
        mpAgriLandHighest = list.get(6).toString();
        mpPlotLowest = list.get(7).toString();
        mpPlotHighest = list.get(8).toString();
        mpResidentialLowest = list.get(9).toString();
        mpResidentialHighest = list.get(10).toString();
        mpCommercialLowest = list.get(11).toString();
        mpCommercialHighest = list.get(12).toString();

        List<Integer> numberList = new ArrayList<Integer>();

        try {

            marketPrice.setCityId(Integer.parseInt(cityId));
            marketPrice.setLocationId(Integer.parseInt(locationId));
            marketPrice.setYear(Integer.parseInt(year));
            marketPrice.setMonth(Integer.parseInt(month));
            marketPrice.setMpAgriLandLowest(Double.parseDouble(mpAgriLandLowest));
            marketPrice.setMpAgriLandHighest(Double.parseDouble(mpAgriLandHighest));

            marketPrice.setMpPlotLowest(Double.parseDouble(mpPlotLowest));
            marketPrice.setMpPlotHighest(Double.parseDouble(mpPlotHighest));

            marketPrice.setMpResidentialLowest(Double.parseDouble(mpResidentialLowest));
            marketPrice.setMpResidentialHighest(Double.parseDouble(mpResidentialHighest));

            marketPrice.setMpCommercialLowest(Double.parseDouble(mpCommercialLowest));
            marketPrice.setMpCommercialHighest(Double.parseDouble(mpCommercialHighest));

            marketPriceDAL.insert(marketPrice);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    File excelFile = attachmentUtils.getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.MARKET_PRICE);
    FileUtils.cleanDirectory(excelFile);
    return true;

}

From source file:com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java

public Vector read() throws IOException {

    File excelFile = attachmentUtils
            .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.RAW_MARKET_PRICE);
    File[] listofFiles = excelFile.listFiles();
    String fileName = excelFile + "/" + listofFiles[0].getName();

    Vector cellVectorHolder = new Vector();
    int type;/* w w  w.  j av a 2 s  .co  m*/
    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.rawmarketprice.RawMarketPriceService.java

public boolean saveExcelToDatabase() throws IOException {

    Vector dataHolder = read();/*w w w  .  ja v a2 s .  c  om*/
    rawMarketPriceDAL.truncateAll();
    dataHolder.remove(0);

    RawMarketPrice rawMarketPrice = new RawMarketPrice();
    String id = "";
    String cityId = "";
    String locationName = "";
    String year = "";
    String month = "";
    String mpAgriLandLowest = "";
    String mpAgriLandHighest = "";
    String mpPlotLowest = "";
    String mpPlotHighest = "";
    String mpResidentialLowest = "";
    String mpResidentialHighest = "";
    String mpCommercialLowest = "";
    String mpCommercialHighest = "";
    String sdZoneId = "";
    String locationTypeId = "";
    String locationCategories = "";
    String description = "";
    String majorApproachRoad = "";
    String sourceOfWater = "";
    String publicTransport = "";
    String advantage = "";
    String disadvantage = "";
    String population = "";
    String migrationRate = "";
    String isCommercialCenter = "";
    System.out.println(dataHolder);
    DataFormatter formatter = new DataFormatter();
    for (Iterator iterator = dataHolder.iterator(); iterator.hasNext();) {
        List list = (List) iterator.next();
        logger.info("list", list);
        cityId = list.get(1).toString();
        locationName = list.get(2).toString();
        year = list.get(3).toString();
        month = list.get(4).toString();
        mpAgriLandLowest = list.get(5).toString();
        mpAgriLandHighest = list.get(6).toString();
        mpPlotLowest = list.get(7).toString();
        mpPlotHighest = list.get(8).toString();
        mpResidentialLowest = list.get(9).toString();
        mpResidentialHighest = list.get(10).toString();
        mpCommercialLowest = list.get(11).toString();
        mpCommercialHighest = list.get(12).toString();
        sdZoneId = list.get(13).toString();
        locationTypeId = list.get(14).toString();
        locationCategories = list.get(15).toString();
        description = list.get(16).toString();
        majorApproachRoad = list.get(17).toString();
        sourceOfWater = list.get(18).toString();
        publicTransport = list.get(19).toString();
        advantage = list.get(20).toString();
        disadvantage = list.get(21).toString();
        population = list.get(22).toString();
        migrationRate = list.get(23).toString();
        isCommercialCenter = list.get(24).toString();
        List<String> strList = new ArrayList<String>(Arrays.asList(locationCategories.split(",")));
        List<Integer> numberList = new ArrayList<Integer>();
        for (String number : strList) {
            numberList.add(Integer.parseInt(number));
        }

        try {
            rawMarketPrice.setCityId(Integer.parseInt(cityId));
            rawMarketPrice.setLocationName(locationName);
            rawMarketPrice.setYear(Integer.parseInt(year));
            rawMarketPrice.setMonth(Integer.parseInt(month));
            rawMarketPrice.setMpAgriLandLowest(Double.parseDouble(mpAgriLandLowest));
            rawMarketPrice.setMpAgriLandHighest(Double.parseDouble(mpAgriLandHighest));
            rawMarketPrice.setMpPlotLowest(Double.parseDouble(mpPlotLowest));
            rawMarketPrice.setMpPlotHighest(Double.parseDouble(mpPlotHighest));
            rawMarketPrice.setMpResidentialLowest(Double.parseDouble(mpResidentialLowest));
            rawMarketPrice.setMpResidentialHighest(Double.parseDouble(mpResidentialHighest));
            rawMarketPrice.setMpCommercialLowest(Double.parseDouble(mpCommercialLowest));
            rawMarketPrice.setMpCommercialHighest(Double.parseDouble(mpCommercialHighest));
            rawMarketPrice.setSafedealZoneId(Integer.parseInt(sdZoneId));
            rawMarketPrice.setLocationTypeId(Integer.parseInt(locationTypeId));
            rawMarketPrice.setLocationCategories(numberList);
            rawMarketPrice.setDescription(description);
            rawMarketPrice.setMajorApproachRoad(majorApproachRoad);
            rawMarketPrice.setSourceOfWater(sourceOfWater);
            rawMarketPrice.setPublicTransport(publicTransport);
            rawMarketPrice.setAdvantage(advantage);
            rawMarketPrice.setDisadvantage(disadvantage);
            rawMarketPrice.setPopulation(Integer.parseInt(population));
            rawMarketPrice.setMigrationRate(Integer.parseInt(migrationRate));
            rawMarketPrice.setIsCommercialCenter((Boolean.parseBoolean(isCommercialCenter)));
            rawMarketPriceDAL.insert(rawMarketPrice);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    File excelFile = attachmentUtils
            .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.RAW_MARKET_PRICE);
    FileUtils.cleanDirectory(excelFile);
    return true;

}