Extracting data from Excel (Spreadsheet) files

Do you have a huge spreadsheet file (an excel file) and wanting to take the data out and place it on a database, or process those data to get some results?

Yeah, I faced the same issue couple of months back, had a big excel file with information of people and wanted to extract them to a database. Here's how I tackled it.

Java Excel API - A Java API to read, write, and modify Excel spreadsheets

You can go to its page using this link

You can download the required library from here

Using that API you can,

• Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks
• Reads and writes formulas (Excel 97 and later only)
• Generates spreadsheets in Excel 2000 format
• Supports font, number and date formatting
• Supports shading, bordering, and coloring of cells
• Modifies existing worksheets

So here is how I used it. 

public ArrayList<Person> read(String inputFile) throws IOException {
        File inputWorkbook;
        Workbook w;
        Sheet sheet;
        private ArrayList<Person> peopleList;
    
        peopleList = new ArrayList<>();
        inputWorkbook = new File(inputFile);     // absolute path and name 
                                                       //of the spreadsheet file
        try {
            w = Workbook.getWorkbook(inputWorkbook);
            sheet = w.getSheet(0);//put the sheet number or you can automate this

            int numberOfRows = sheet.getRows();
            for (int i = 0; i < numberOfRows; i++) { //i=0 is the heading
                Person aPerson = new Person();
                aPerson.setName(sheet.getCell(1, i).getContents());
                aPerson.setContactNo(sheet.getCell(2, i).getContents());
                aPerson.setContactNo2(sheet.getCell(3, i).getContents());
                aPerson.setAddress(sheet.getCell(4, i).getContents().replaceAll("'", " "));
                aPerson.seteMail(sheet.getCell(5, i).getContents());
                if(!sheet.getCell(6, i).getContents().isEmpty()) {
                    aPerson.setbDay(new SimpleDateFormat("dd/MM/yyyy").parse(sheet.getCell(6, i).getContents()));
                } else {
                    aPerson.setbDay(new Date(0));
                }
                aPerson.setGroup(sheet.getCell(7, i).getContents());
                aPerson.setGender(sheet.getCell(8, i).getContents().charAt(0));
                peopleList.add(aPerson);
                System.out.println(aPerson);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return peopleList;
    }

PS : if you have a new version excel file, you need to save it as an 'xls' file to work. Read the documentation for more info

You can then use the extracted date to store in a database or for your calculations.

One thing to remember, if you have a problem (any), it is most likely that somebody might had faced the same problem before and has found a solution, So the golden rule is Google before you solve.

Happy coding folks..