Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:ExcelReadFile.ExcelReadSecretaire.java

public List<Secretaire> readBooksFromExcelFile(String excelFilePath) throws IOException {
    List<Secretaire> listMed = new ArrayList<>();
    FileInputStream file = new FileInputStream(new File(excelFilePath));

    //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);
    Sheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();/*w  ww.jav  a2 s . c  o m*/
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        Medecin s = new Medecin();
        Secretaire med = new Secretaire();
        Admin ad = new Admin();

        while (cellIterator.hasNext()) {
            Cell nextCell = cellIterator.next();
            int columnIndex = nextCell.getColumnIndex();

            switch (columnIndex) {
            case 0:
                ad.setId_admin((((Integer) getCellValue(nextCell)).intValue()));
                Admin a = serviceAdmin.findAdminById(ad.getId_admin());
                med.setAdmin(a);
                break;
            case 1:
                s.setId_utilisateur((((Integer) getCellValue(nextCell)).intValue()));
                Medecin sa = serviceMedecin.findMedecinById(s.getId_utilisateur());
                med.setMedecin(sa);
                break;
            case 2:
                med.setAdresse((String) getCellValue(nextCell));
                break;

            case 3:
                med.setDate_naissance((String) getCellValue(nextCell));
                break;

            case 4:
                med.setLogin((String) getCellValue(nextCell));
                break;

            case 5:
                med.setNom((String) getCellValue(nextCell));
                break;
            case 6:
                med.setNumero_telephone((String) getCellValue(nextCell));
                break;
            case 7:
                med.setPassword((String) getCellValue(nextCell));
                break;
            case 8:
                med.setPrenom((String) getCellValue(nextCell));
                break;
            }
        }
        listMed.add(med);

    }

    file.close();

    return listMed;
}

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.  jav a2 s  .  c  om*/
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
 *//*w w  w . j a v  a2 s . c o  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:File.XLSX.ReadXLSX.java

/**
 * @param args the command line arguments
 *//*from  w w w .ja  v a  2s  . c  o  m*/
public void readXLSXFile(String path, String filename, int Fcol, int Lcol, int Frow, int Lrow) {
    try {
        //String upper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        FileInputStream file = new FileInputStream(new File(path + filename + ".xlsx"));

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

        //Get first and last colomn in file
        //int Fcols = (int) upper.indexOf(Fcol.toUpperCase());
        //int Lcols = (int) upper.indexOf(Lcol.toUpperCase());
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        for (int x = Frow; x < Lrow; x++) {
            Row rows = sheet.getRow(x);
            if (rows != null) {
                String key = null;
                for (int colIndex = Fcol; colIndex <= Lcol; colIndex++) {
                    Cell cell = rows.getCell(colIndex);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        }
                    }
                }
            }
            System.out.println("");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:FileHelper.ExcelHelper.java

public SheetsOfFile ReadSheetsOfFile(String fileName) {
    try {// ww  w  .j a  v  a  2  s. com
        SheetsOfFile sheetsOfFile = new SheetsOfFile();
        ArrayList<String> sheets = new ArrayList<String>();
        File excel = new File(fileName);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook book = new XSSFWorkbook(fis);
        int size = book.getNumberOfSheets();
        if (size > 1) {
            for (int i = 0; i < size; i++) {
                XSSFSheet sheet = book.getSheetAt(i);
                String str = sheet.getSheetName();
                sheets.add(str);
            }
        }
        sheetsOfFile.setSheets(sheets);
        return sheetsOfFile;
    } catch (Throwable t) {
        System.out.println("Throwsable: " + t.getMessage());
        return new SheetsOfFile();
    }
}

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 {/*from   w ww  .  j av a  2 s. co  m*/
        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:FilesHandlers.ExcelHandler.java

/**
 * used for getting the content of the selected file
 *
 * @param file The name of the file to display
 * @param sheet The sheet number//from w w w.j  a  v  a 2s .com
 * @return The content of given sheet
 * @throws java.io.FileNotFoundException
 */
public ArrayList<String[]> getFileCtBySheet(String file, int sheet) throws FileNotFoundException, IOException {
    ArrayList<String[]> list = new ArrayList<String[]>();
    ArrayList<String> row = new ArrayList<>();
    File selectedFile = new File(this.workingDirectory.concat(file));
    FileInputStream inputStream = new FileInputStream(selectedFile);

    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(sheet);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                row.add((String) cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                row.add("" + cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                row.add("" + cell.getNumericCellValue());
                break;
            }

        }
        list.add(row.toArray(new String[list.size()]));
        row = new ArrayList<>();
    }

    workbook.close();
    inputStream.close();

    return list;
}

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.
 *///  w  w w  .  java  2 s. 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:FormatConvert.exceloperation.Excel2csv.java

public static void SeperatorExcel2sheet(String excelfile, String targetdir) {
    try {//from  ww w .j  a  va  2s  .  com
        FileInputStream is = (new FileInputStream(excelfile));
        if (excelfile.endsWith(".xlsx")) {
            XSSFWorkbook wb = new XSSFWorkbook(is);
            int sheetnub = wb.getNumberOfSheets();
            for (int i = 0; i < sheetnub; i++) {
                XSSFSheet sheet = wb.getSheetAt(i);
                if (sheet.toString() != null) {
                    String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv";
                    Excel2csv.copySheets2CSV(sheet, temfile);
                }
            }
        } else {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            int sheetnub = wb.getNumberOfSheets();
            for (int i = 0; i < sheetnub; i++) {
                HSSFSheet sheet = wb.getSheetAt(i);
                if (sheet.toString() != null) {
                    String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv";
                    Excel2csv.copySheets2CSV(sheet, temfile);
                }
            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

From source file:FormatConvert.exceloperation.ExcelOperation.java

public static void SeperatorExcel2sheet(String excelfile, String targetdir) {
    try {//from  w  w  w .j  a  v a2 s  .  com
        FileInputStream is = (new FileInputStream(excelfile));
        if (excelfile.endsWith(".xlsx")) {
            XSSFWorkbook wb = new XSSFWorkbook(is);
            int sheetnub = wb.getNumberOfSheets();
            for (int i = 0; i < sheetnub; i++) {
                XSSFSheet sheet = wb.getSheetAt(i);
                if (sheet.toString() != null) {
                    Workbook wb2 = new XSSFWorkbook();
                    XSSFSheet tempsheet = (XSSFSheet) wb2.createSheet();
                    Util.copySheets(tempsheet, sheet, true);
                    //tempsheet=wb.cloneSheet(i);
                    //tempsheet = sheet;
                    String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx";
                    FileOutputStream fileOut = new FileOutputStream(temfile);
                    wb2.write(fileOut);
                    fileOut.close();
                }
            }
        } else {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            int sheetnub = wb.getNumberOfSheets();
            for (int i = 0; i < sheetnub; i++) {
                HSSFSheet sheet = wb.getSheetAt(i);
                if (sheet.toString() != null) {
                    Workbook wb2 = new HSSFWorkbook();
                    HSSFSheet tempsheet = (HSSFSheet) wb2.createSheet();
                    Util.copySheets(tempsheet, sheet, true);
                    //tempsheet=wb.cloneSheet(i);
                    //tempsheet = sheet;
                    String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx";
                    FileOutputStream fileOut = new FileOutputStream(temfile);
                    wb2.write(fileOut);
                    fileOut.close();
                }
            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}