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); } } }
Advertisement
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); } } }