I am trying to combine multiple csv files into one excel workbook as different sheets using java

Tags: ,



I am able to create the sheets for different .csv files present in a folder in one Excel workbook using below java code But i am getting the data of all the csv file data in to single worksheet. i am unable to get the data same as the orginal .csv files into specified sheets

public class CreateExlFile22{
     public static void main(String[]args){
    try{
           HSSFWorkbook workbook=new HSSFWorkbook();


    File folder = new File("F:/csvfiles/");
    File[] listOfFiles = folder.listFiles();

    for (File file : listOfFiles) {
        if (file.isFile()) {
     System.out.println(file.getName());




            String thisline;
            ArrayList<String> al = null;
            ArrayList<ArrayList<String>> arlist = new ArrayList<ArrayList<String>>();

            HSSFSheet sheet =  workbook.createSheet(file.getName());

            FilenameFilter filter = new FilenameFilter()
            {
                @Override public boolean accept(File dir, String name)
                {
                    return name.endsWith(".csv");
                }
            };


            File file1[]=folder.listFiles(filter);


            for(int r=0;r<file1.length;r++){
                File currentFile=file1[r];


                FileInputStream fis = new FileInputStream(currentFile);
                BufferedReader br = new BufferedReader(new InputStreamReader(fis));
                while ((thisline = br.readLine()) != null) {
                    al = new ArrayList<String>();
                    String strar[] = thisline.split(",");

                    for (int j = 0; j < strar.length; j++) {


                        al.add(strar[j]);
                    }

                    arlist.add(al);
                    //i++;

                } 

                fis.close();


                    for (int k = 0; k < arlist.size(); k++) {
                        ArrayList<String> ardata = (ArrayList<String>) arlist.get(k);
                        HSSFRow row = sheet.createRow((short) k);

                        for (int p = 0; p < ardata.size(); p++) {

                            HSSFCell cell = row.createCell((short) p);
                            cell.setCellValue(ardata.get(p).toString());
                        }
                    }

                    FileOutputStream fileOut = new FileOutputStream("F://NewWBFile.xls");
                    workbook.write(fileOut);
                    fileOut.flush();
                    fileOut.close();
                    br.close();


                    }


                    }

        }


    System.out.println("Your excel file has been generated!");

    } catch ( Exception ex ) {
        System.out.println(ex);

    }
       }
   }

Answer

public class csvxls{
     public static void main(String[]args){
    try{

    File folder = new File("F:/csvfiles/");
    File[] listOfFiles = folder.listFiles();
    HSSFWorkbook workbook=new HSSFWorkbook();

    for (File file : listOfFiles) {


        if (file.isFile()) {

            String thisline;
            ArrayList<String> al = null;
            ArrayList<ArrayList<String>> arlist = new ArrayList<ArrayList<String>>();

            HSSFSheet sheet =  workbook.createSheet(file.getName());  
                FileInputStream fis = new FileInputStream(file);
                BufferedReader br = new BufferedReader(new InputStreamReader(fis));

                while ((thisline = br.readLine()) != null) {
                    al = new ArrayList<String>();
                    String strar[] = thisline.split(",");

                    for (int j = 0; j < strar.length; j++) { 
                        for (int k = 0; k < arlist.size(); k++) {

                            ArrayList<String> ardata = (ArrayList<String>) arlist.get(k);

                            HSSFRow row = sheet.createRow((short) k);


                            for (int p = 0; p < ardata.size(); p++) {

                                HSSFCell cell = row.createCell((short) p);
                                cell.setCellValue(ardata.get(p).toString());

                            }
                        }

                        al.add(strar[j]);

                    } 

                  arlist.add(al);
                }

                fis.close();  
                FileOutputStream fileOut = new FileOutputStream("F://NewWBFile.xls");
                workbook.write(fileOut);
                fileOut.flush();
                fileOut.close();
                br.close();
        }
        }

    System.out.println("Your excel file has been generated!");

    } catch ( Exception ex ) {
        System.out.println(ex);

    }
       }
   }


Source: stackoverflow