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:ExcelCompare2.POISpreadSheet.java

License:Open Source License

public POISpreadSheet(String fName) throws Exception {
    try {/*  w  w  w .  j a va 2  s . c o  m*/
        _wb = loadSpreadSheet(fName);
        _iter = getSheetIterator();
        _sheet = _iter.next();
        _formatter = new DataFormatter(); //creating formatter using the default locale
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

From source file:features.UfmCreateXmlFilesForTestData.java

/**
 * Read the test data in Excel data file and convert data to HashMap and return as output.
 * @param locOfFile: location of test data file(Excel file with UFM test cases information).
 * argument is a specifier that is relative to the url argument.
 *//*w  w w.  j  ava2s.co  m*/
public static HashMap getUfmTestData(String locOfFile) {
    HashMap<String, String> rowData = new HashMap<String, String>();
    try {
        DataFormatter formatter = new DataFormatter();
        FileInputStream file = new FileInputStream(new File(locOfInputUfmExcelFile));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        int noOfRows = sheet.getLastRowNum();
        //System.out.println("no of rows:" + noOfRows);

        int i = 0;
        Row rowWithColumnNames = sheet.getRow(2);
        int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells();
        //System.out.println(noOfColumns);
        String testCaseName = "";
        String columnNamesAndValuesOfOneRow = "";

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();

        // System.out.println(rowIterator

        for (int m = 0; m < noOfRows; m++) {
            //System.out.println("Ieration number : " + m);
            Row rowCurrent = rowIterator.next();
            if (m <= 3) {
                continue;
            }
            testCaseName = String.valueOf(rowCurrent.getCell(0));
            //     System.out.println("test case name " + testCaseName);

            for (int p = 0; p < 84; p++) {
                //Igonre the columns without any column name in test case excel file
                if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") {
                    continue;
                }
                columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow
                        + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":"
                        + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";";

            }
            rowData.put(testCaseName, columnNamesAndValuesOfOneRow);
            columnNamesAndValuesOfOneRow = "";

        }
        file.close();
        return rowData;
    } catch (Exception e) {
        e.printStackTrace();
    }

    return null;

}

From source file:features.UfmCreateXmlFilesForTestData.java

/**
 * Method to read the Ufm input excel file location and the folder location where need store the xml file
 *///www . j  a  va2s  .  co  m
public static void getUfmLocations() {
    HashMap<String, String> rowData = new HashMap<String, String>();
    try {
        DataFormatter formatter = new DataFormatter();
        FileInputStream file = new FileInputStream(
                new File(ReportLibrary.getPath() + "\\testdata\\Ufm_InputData.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        int noOfRows = sheet.getLastRowNum();

        Row rowWithColumnNames = sheet.getRow(1);
        locOfInputUfmExcelFile = sheet.getRow(1).getCell(1).toString();
        locOfFolderInWhichXmlToBeStored = sheet.getRow(1).getCell(2).toString();
    } catch (Exception e) {

    }
}

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

static void printSheet(Sheet sheet) {
    final DataFormatter dataFormatter = new DataFormatter();
    List<List<String>> rows = new ArrayList<>();
    for (Row row : sheet) {
        List<String> r = new ArrayList<>();
        for (Cell cell : row) {
            r.add(dataFormatter.formatCellValue(cell));
        }/*  ww w . j av a  2 s  . c o  m*/
        rows.add(r);
    }
    printSheet(rows);
}

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

protected List<String> getDataFromRow(Row row) {
    final DataFormatter dataFormatter = new DataFormatter();
    return stream(spliteratorUnknownSize(row.cellIterator(), ORDERED), false)
            .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList());
}

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

protected List<String> getDataFromColumn(Sheet sheet, int colnum) {
    final DataFormatter dataFormatter = new DataFormatter();
    return stream(spliteratorUnknownSize(sheet.rowIterator(), ORDERED), false).map(row -> row.getCell(colnum))
            .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList());
}

From source file:Files.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 {/*  ww  w  .  ja  v  a2  s. c  om*/
        FileInputStream fis = new FileInputStream(myFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        List<String> errorList = new ArrayList<>();
        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()); // add the title in xlsx to keyList                  
                numCell++;
            }
            System.out.println("XLSX2BatchHandler.java UCFR loaded = " + DB.isUCFRLoaded());
            if (numCell == GlobalVar.LEAVE_TITLES_V1.length && DB.isUCFRLoaded()) { // check if the excel is the leave roster
                System.out.println("XLSX2BatchHandler.java:  V1, UCFR is loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    lineCount++;
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    Map<String, String> container = new HashMap<>();
                    int keys = 0; //index of the cell
                    // reset date every row
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V1
                                || keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) {
                            String value = cell.getStringCellValue();
                            container.put(keyList.get(keys), value);
                        } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) {
                            DataFormatter df = new DataFormatter();
                            //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd");   
                            String lastFour = df.formatCellValue(cell); //return ***-**-****
                            lastFour = GlobalVar.last4Generator(lastFour);
                            container.put(keyList.get(keys), lastFour);
                        } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1
                                || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1) {
                            DataFormatter df = new DataFormatter();
                            String dateString = df.formatCellValue(cell);
                            container.put(keyList.get(keys), dateString);
                        } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) {
                            int type = cell.getCellType();
                            String value = null;
                            if (type == HSSFCell.CELL_TYPE_STRING) {
                                value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue());
                            } else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
                                value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string
                            }
                            container.put(keyList.get(keys), value);
                        } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) {
                            String value = GlobalVar.getDMOLeaveType(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 = GlobalVar
                            .readCtrlNum(container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1)));
                    if (ctrlNum == null) {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": Invalid ctrl number received.");
                    }
                    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);
                    MyDate soDate = new MyDate(signOutDate);
                    MyDate siDate = new MyDate(signInDate);
                    String leaveDays = "---";
                    if (siDate != null && siDate.afterOrEqual(soDate)) {
                        leaveDays = GlobalVar.computeNumOfDays(soDate, siDate);
                    } else {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": How to sign in before sign out?");
                    }

                    if (leaveDays != null) {
                        int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, first5,
                                leaves); //add leave into leaves
                        globalCount = GlobalVar.batchGenerator(SSN, first5, lastName, signOutDate, signInDate,
                                leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount,
                                output, errorList, ADSN);
                    } else {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": Invalid dates are entered!");
                    }
                }
                String msg = "BatchGenerator.java:  The batch file is generated successfully! \n";
                if (!errorList.isEmpty()) {
                    for (String errorMsg : errorList) {
                        msg += errorMsg + "\n";
                    }
                }
                batchFileExists = true;
                JOptionPane.showMessageDialog(null, msg);
                fis.close();
                //                    output.close();
                //                    outputFile.close();
            } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && DB.isUCFRLoaded()) { // full SSN xlsx
                System.out.println("XLSX2BatchHandler.java: V2, UCFR is loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    //int cellCount = 0;             
                    lineCount++;
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    Map<String, String> rowContainer = new HashMap<>(); //store info of each row          
                    rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer                       
                    //    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 = GlobalVar
                            .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2)));
                    if (ctrlNum == null) {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": Invalid ctrl number received.");
                    }
                    // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2));
                    String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn
                    String lastName = DB.getLastNamefromSSN(fullSSN);
                    String lastFour = GlobalVar.last4Generator(fullSSN);
                    String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2));
                    String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2));
                    String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2));
                    String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2));

                    String first5 = DB.getFirst5(lastName, lastFour);
                    MyDate soDate = new MyDate(signOutDate);
                    MyDate siDate = new MyDate(signInDate);

                    String leaveDays = "---";
                    if (siDate != null && siDate.afterOrEqual(soDate)) {
                        leaveDays = GlobalVar.computeNumOfDays(soDate, siDate);
                    } else {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": How to sign in before sign out?");
                    }

                    int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5,
                            leaves);
                    globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate,
                            leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output,
                            errorList, ADSN);

                }
                String msg = "BatchGenerator.java:  The batch file is generated successfully! \n";

                if (!errorList.isEmpty()) {
                    for (String errorMsg : errorList) {
                        msg += errorMsg + "\n";
                    }
                }
                batchFileExists = true;
                JOptionPane.showMessageDialog(null, msg);
                fis.close();
                //                    output.close(); 
                //                    outputFile.close();
            } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && !DB.isUCFRLoaded()) {
                System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    //int cellCount = 0;             
                    lineCount++;
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    Map<String, String> rowContainer = new HashMap<>(); //store info of each row                                       
                    rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer

                    //    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 = GlobalVar
                            .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2)));
                    //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); 
                    if (ctrlNum == null) {
                        JOptionPane.showMessageDialog(null,
                                "Line " + lineCount + ": Invalid ctrl number received.");
                    }
                    // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2));
                    String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn
                    String lastName = GlobalVar.TEMP_DMO_NAME;
                    String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2));
                    String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2));
                    String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2));
                    String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2));
                    String first5 = GlobalVar.TEMP_DMO_NAME;
                    MyDate soDate = new MyDate(signOutDate);
                    MyDate siDate = new MyDate(signInDate);
                    String leaveDays = "---";
                    if (siDate != null && siDate.getDay() != null && soDate != null
                            && soDate.getDay() != null) {
                        if (siDate.afterOrEqual(soDate)) {
                            leaveDays = GlobalVar.computeNumOfDays(soDate, siDate);
                        } else {
                            JOptionPane.showMessageDialog(null,
                                    "Line " + lineCount + ": How to sign in before sign out?");
                        }
                    } else {
                        JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid date entered");
                    }

                    int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5,
                            leaves);

                    // write the leave on the batch file (PrintStream output)
                    globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate,
                            leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output,
                            errorList, ADSN);
                }
                String msg = "BatchGenerator.java:  The batch file is generated successfully! \n";

                if (!errorList.isEmpty()) {
                    for (String errorMsg : errorList) {
                        msg += errorMsg + "\n";
                    }
                }
                batchFileExists = true;
                JOptionPane.showMessageDialog(null, msg);
                fis.close();
            } else if (!DB.isUCFRLoaded()) {

                JOptionPane.showMessageDialog(null,
                        "XLSX2BatchHandler.java: UCFR needs to be loaded to process this type of xlsx.");
            } else {

                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!");
            }
        } 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
        output.close();
        outputFile.close();
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!");
        Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:Files.XLSX2BatchHandler.java

public void rowContainerBuilder(Map<String, String> rowContainer, List<String> keyList,
        Iterator<Cell> cellIterator) {
    int keys = 0;
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V2) {
            String value = cell.getStringCellValue();
            rowContainer.put(keyList.get(keys), value);
        } else if (keys == GlobalVar.FULL_SSN_CELL_INDEX_V2) {
            int type = cell.getCellType();
            String fullSSN = null;
            if (type == HSSFCell.CELL_TYPE_STRING) {
                fullSSN = cell.getStringCellValue(); //string type ssn
                fullSSN = fullSSN.replaceAll("-", "");
            } else {
                DataFormatter df = new DataFormatter();
                //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd");   
                fullSSN = df.formatCellValue(cell); //return ***-**-****
            }/*from   w  w w  .  j  a v  a2s .  c  o  m*/
            fullSSN = GlobalVar.fullSSNgenerator(fullSSN);
            rowContainer.put(keyList.get(keys), fullSSN);
        } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2
                || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2) {
            DataFormatter df = new DataFormatter();
            String dateString = df.formatCellValue(cell);
            rowContainer.put(keyList.get(keys), dateString);
        } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V2) {
            int type = cell.getCellType();
            String value = null;
            if (type == HSSFCell.CELL_TYPE_STRING) {
                value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue());
            } else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
                value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string
            }
            rowContainer.put(keyList.get(keys), value);
        } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V2) {
            String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue());
            rowContainer.put(keyList.get(keys), value);
        }
        keys++;
    }
}

From source file:FLCSS.floridaautomationtestsuite.ufm.UfmCreateXmlFilesForTestData.java

/**
 * Read the test data in Excel data file and convert data to HashMap and return as output.
 * @param locOfFile: location of test data file(Excel file with UFM test cases information).
 * argument is a specifier that is relative to the url argument.
 *///from w w w.  j a  v a 2s  . c o  m
public static HashMap getUfmTestData(String locOfFile) {
    HashMap<String, String> rowData = new HashMap<String, String>();
    try {
        DataFormatter formatter = new DataFormatter();
        FileInputStream file = new FileInputStream(
                new File("C:\\Users\\23319\\Downloads\\FLCSS-java(5)\\a1\\src\\QATP_R0.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        int noOfRows = sheet.getLastRowNum();
        //System.out.println("no of rows:" + noOfRows);

        int i = 0;
        Row rowWithColumnNames = sheet.getRow(2);
        int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells();
        //System.out.println(noOfColumns);
        String testCaseName = "";
        String columnNamesAndValuesOfOneRow = "";
        //            HashMap<String,String> headerColumnNames = new HashMap<String,String>();
        //            //int[][] rowWithData = new int[5][];
        //            for (i = 0; i < 84; i++) {
        //                // System.out.println("hello");
        //                headerColumnNames.put(formatter.formatCellValue((rowWithColumnNames.getCell(i))),"");
        //                //  System.out.println(headerColumnNames.size());
        //            }

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();

        // System.out.println(rowIterator

        for (int m = 0; m < noOfRows; m++) {
            //System.out.println("Ieration number : " + m);
            Row rowCurrent = rowIterator.next();
            if (m <= 3) {
                continue;
            }
            testCaseName = String.valueOf(rowCurrent.getCell(0));
            //     System.out.println("test case name " + testCaseName);

            for (int p = 0; p < 84; p++) {
                //Igonre the columns without any column name in test case excel file
                if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") {
                    continue;
                }
                columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow
                        + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":"
                        + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";";

            }
            rowData.put(testCaseName, columnNamesAndValuesOfOneRow);
            columnNamesAndValuesOfOneRow = "";

        }
        file.close();
        return rowData;
    } catch (Exception e) {
        e.printStackTrace();
    }

    return null;

}

From source file:fll.util.ExcelCellReader.java

License:Open Source License

/**
 * Read an excel file from the specified stream.
 * /*from  w w  w.j av a 2  s  .  c  o m*/
 * @param file where to read the excel file from, this is read into memory and
 *          then can be closed after the constructor is finished
 * @param sheetName the sheet to read
 * @throws IOException
 * @throws InvalidFormatException
 */
public ExcelCellReader(final InputStream file, final String sheetName)
        throws IOException, InvalidFormatException {
    workbook = createWorkbook(file);
    if (workbook instanceof HSSFWorkbook) {
        formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    } else if (workbook instanceof XSSFWorkbook) {
        formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    } else {
        throw new RuntimeException("Unknown wookbook class: " + workbook.getClass().getName());
    }

    sheet = workbook.getSheet(sheetName);

    formatter = new DataFormatter();
}