Generating files by reading excel sheet

Question

There is a excel file which is having lot of file names in one of its column . I need to write a java code that should read those file names and generate the same in destination.Can any one help me ?

Answer 1

There are some libraries you can use to read excel files. In my case, I chose the Apache POI which you can download for free and then add to your project. Once you added it, create your Document:

HSSFWorkbook workbook = new HSSFWorkbook( new FileInputStream("filename.xls") );

or, if it's an excel document from 2007 or later:

XSSFWorkbook workbook = new XSSFWorkbook( new FileInputStream("filename.xlsx") );

Now you can iterate through each row and read out the first column:

for( Row row : workbook.getSheetAt(0) ) // Go through each row in sheet 0
    System.out.println( row.getCell(0).getStringCellValue() );

Answer 2

public class Test {

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

    try {

        FileInputStream file = new FileInputStream(new File("D:\\sampleFileNames.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        FileOutputStream outFile= null;
        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();
                    Cell cell = cellIterator.next();
                    File file1 = new File("D:\\Ganesh\\"+cell.getStringCellValue().toString());
                    if(!file1.createNewFile()) {
                        System.out.println("File already exists");


              /*  Cell cell = cellIterator.next();
                System.out.print(cell.getStringCellValue() + "\t\t");
                outFile =new FileOutputStream(new File("D:\\Ganesh\\"+cell.getStringCellValue().toString()));
                //workbook.write(outFile);
            System.out.println("");
        }
        file.close();
        outFile.close();
      /*  FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
        workbook.write(outFile);*/
        //outFile.close();


}
        }
        }catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

} }

Answer 3

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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

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

        try {

            FileInputStream file = new FileInputStream(new File("C:\\test.xls"));

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            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();
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    FileOutputStream outFile =new FileOutputStream(new File("C:\\"+cell.getStringCellValue()));
                    workbook.write(outFile);
                }
                System.out.println("");
            }
            file.close();

          /*  FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
            workbook.write(outFile);*/
            //outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}