Excel Read Multiple Files Java Apache POI

Question

I am able to read one sheet from one excel and write in another with this. However now i wanna read through a folder of excel files. How do i do that? this program is for one particular file. I wanna read from multiple xls files extract sheet number 1 from each and print in a new file only the first sheet.

public static boolean readWriteXLSXFile() throws IOException
{
 //File filename=new File("/temp/raw");
    //InputStream ExcelFileToRead = new FileInputStream(filename);

 String pathname="C:/temp/";
 String ffile="raw";
 String oldfilename = pathname + ffile;
    String newExcel = "C:/temp/updatedraw";
    String sheetname= ffile + "Sheet";
    InputStream ExcelFileToRead = new FileInputStream(oldfilename);

    XSSFWorkbook  workbook = new XSSFWorkbook(ExcelFileToRead);
    XSSFSheet datatypeSheet = workbook.getSheetAt(0);


            XSSFWorkbook workbook1 = new XSSFWorkbook(); 
    XSSFSheet newsheet = workbook1.createSheet(sheetname);

    XSSFRow currentRow, newrow; 
    XSSFCell currentCell, newcell;
            Iterator iterator = datatypeSheet.rowIterator();
            int rowIndex=0;

    while (iterator.hasNext())
    {        

        currentRow=(XSSFRow) iterator.next();             
        newrow=newsheet.createRow(currentRow.getRowNum());
        int cellIndex=0;
        Iterator cellIterator = currentRow.cellIterator();

                    while (cellIterator.hasNext())
                    {
                            currentCell=(XSSFCell) cellIterator.next();

                            XSSFCellStyle newCellStyle ;


                                    switch (currentCell.getCellType())
                                    {
                                            case XSSFCell.CELL_TYPE_STRING:

                                                    System.out.print(currentCell.getStringCellValue()+" ");

                                                    newcell= newrow.createCell(cellIndex);
                                                    newcell.setCellValue(currentCell.getStringCellValue());

                                                    newCellStyle = newcell.getSheet().getWorkbook().createCellStyle();
                                                    newCellStyle.cloneStyleFrom(currentCell.getCellStyle());
                                                    newcell.setCellStyle(newCellStyle);
                                                    cellIndex++;

                                                    break;


                                            case XSSFCell.CELL_TYPE_NUMERIC:

                                                    System.out.print(currentCell.getNumericCellValue()+" ");

                                                    newcell= newrow.createCell(cellIndex);
                                                    newcell.setCellValue(currentCell.getNumericCellValue());

                                                    newCellStyle = newcell.getSheet().getWorkbook().createCellStyle(); 
                                                    newCellStyle.cloneStyleFrom(currentCell.getCellStyle());
                                                    newcell.setCellStyle(newCellStyle);
                                                    cellIndex++;

                                                    break;


                                            default:

                                                    break;
                                    }
        }

        FileOutputStream fileOut = new FileOutputStream(newExcel);
        workbook1.write(fileOut);
        fileOut.close();
        System.out.println();
    }
            return true;

}`

Answer 1

Step 1: Define a file filter (this is an example for all files with typical Excel-Suffix, adjust to your needs):

    public class ExcelFileFilter implements java.io.FileFilter {
        @Override
        public boolean accept(File file) {
            return file != null &&
                file.isFile() &&
                file.canRead() &&
                (file.getName().endsWith("xls")
                || file.getName().endsWith("xlsx"));
        }
    }

Step 2: Use the filter to read all Excel files in a directory:

FileFilter filter = new ExcelFileFilter ();
File directory = new File("MyDirectoryWithExcelfiles");
File[] files = directory.listFiles(filter);
for (File file : files) {
    //init workbook and do stuff
}