How to deal with the changing file names.using jxl?

Question

I want to read xls file using JXl where xls file name is always changing, how to read that please help. i tried below code

FileInputStream filepath = new FileInputStream("C:\\Users\\sameer.joshi\\Downloads\\*.xls");

FileInputStream filepath = new FileInputStream("C:\\Users\\sameer.joshi\\Downloads\\");

Answer 1

Try to add all fileNames inside a list and read all data of Excel file.

List<String>ArrayList xlsFiles=new ArrayList<String>();
xlsFiles.add("your all files Names");
for (String str:xlsFiles) {
    readExcellData(str);
}

public List<String> readExcellData(String fileNameToProcess) throws IOException {
    List<String> dataList = new ArrayList<String>();
    List<Integer> rowNo=new ArrayList<Integer>();
    List<Integer> colNo=new ArrayList<Integer>();
    int countRow=1;
    int countCol=1;
    try {

        FileInputStream fis = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            rowNo.add(countRow);
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING: {
                    dataList.add(cell.getStringCellValue());
                    System.out.println(cell.getStringCellValue());
                }
                    break;
                }
            }
        }
        return dataList;
    } catch (FileNotFoundException ee) {
        ee.printStackTrace();
    } catch (IOException ee) {
        ee.printStackTrace();
    }

    return dataList;
}

Answer 2

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class Test {    
public static void main(String[] args) throws FileNotFoundException, IOException {

    File directory = new File("C:\\Users\\sameer.joshi\\Downloads\\");
    File[] all_XLS_Files = directory.listFiles(); //all files in that directory

    for (File file : all_XLS_Files) { // iterate through all files in that directory
      if(file.getName().endsWith(".xls")){ // select only xls files
        //do something with your xls files here
        //for example print out the file name
        System.out.println(file.getName());
        //or read one or all of them 
        FileInputStream fileInputStream = new FileInputStream(new File(file.getPath()));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch(cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t\t");
                        break;
                }
            }
            System.out.println("");
        }
        fileInputStream.close();
        FileOutputStream out = 
            new FileOutputStream(new File(file.getPath()));
        workbook.write(out);
        out.close();
      }
    }  
}

}

Answer 3

I think the issue is not how to read a xls file, but how to deal with the changing file names. if that is the case try to use a FilenameFilter to get your .xls files. Example :

public class Test {

public static void main(String[] args) throws FileNotFoundException, IOException {

    File directory = new File("C:\\Users\\sameer.joshi\\Downloads\\");

    //get all files which ends with ".xls"
    FilenameFilter textFilter = new FilenameFilter() { 
        public boolean accept(File dir, String name) {
            return name.endsWith(".xls");
        }
    };

    // all xls files are listed in this File[] array
    File[] files = directory.listFiles(textFilter);
    // iterate through your array and do something 

    for (File file : files) {
      //read your .xls files here 
      System.out.println(file.getCanonicalPath());
    }   
}

}