Skip to main content

Java : Read from Excel (xls) file

Java : Reaf from Excel (xls) file

Reading from a text file is common. But, reading from an excel(.xls) file is not.
Recently, I got to do so. Little bit Google search and experimentation resulted in the following.

I used HSSF from Apache-POI.
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;

public void readFromXlsFile (String pathToFile, String sheetName){
    try {
        FileInputStream file = new FileInputStream(pathToFile);

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheet(sheetName);

        Iterator<Row> rowIterator = sheet.rowIterator();
           
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            if (row.getRowNum() == 0) {
                continue;// skip first row, as it contains column names
            }

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() == 0) {
                    System.out.print(cell.getStringCellValue() + "\t\t");
                } else if (cell.getColumnIndex() == 1) {
                    System.out.print((long) cell.getNumericCellValue()+ "\t\t");
                } else if (cell.getColumnIndex() == 2) {
                    System.out.print((int) Math.round(cell.getNumericCellValue())+ "\t\t");
                }
            }
            System.out.println("");
        }

        file.close();

    } catch (FileNotFoundException fnfe) {
        fnfe.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}
To run the above example, create a sheet in excel file with String, Long and Integer columns respectively.

To print column names, run a while loop with cell.getStringCellValue() on every cell in first row.

getStringNumericCellvalue() returns 'double' and you can see from the example that I've type-casted it to 'long' and 'int'.

To get a sheet by its index, use
getSheetAt(index)
in place of
getSheet(sheetName)

Comments

Popular posts from this blog

Ubuntu: Access a usb flash drive from the terminal

    1. Find what the drive is called You'll need to know what the drive is called to mount it. To do that fire off: sudo fdisk -l You're looking for a partition that should look something like:   /dev/sdb1 . Remember what it's called. 2. Create a mount point Create a new directory in   /media   so you can mount the drive onto the filesystem: sudo mkdir /media/usb 3. Mount! sudo mount /dev/sdb1 /media/usb When you're done, just fire off: sudo umount /media/usb Source: StackOverflow

Karabiner: Mouse/keyboard customizer for OS X

For beginners, or the one who migrated from Windows environment, Natural Gestures (Scrolling and Swiping) might be bit confusing. But, once you get familiarized with them, it may feel like "What was I doing, all those days?". It all changed, when I connected external mouse to my Macbook. When you start using that WHEEL, you will be confused. Luckily there is a setting for mouse, to change scroll behavior (natural or the other way). But, here's the catch. If you toggle that setting, it also toggles the same for TRACKPAD!!!!! I've seen that many people were freaked out and even raised BUG report to Apple. But, all those reports were closed, saying that is not a bug, but intentional feature!!! For those, who can't leave with such one-sided settings, here is a simple util, which came to my rescue: Karabiner It's simple, powerful and stable mouse/keyboard customizer for OSX. Without going into much detail, here's the configuration I used to ret

Java: Use BigInteger in for-loop

In my previous post , I mentioned a way to handle large integers by using BigInteger. Now I'm going to provide a very important usage of it. We often use for-loop. So here is the way to use it: Ordinary integers: for(int i = 1; i <= n; i++) {  //Task to do } BigInteger: for (BigInteger bi = BigInteger.valueOf(1);                 bi.compareTo(n) <= 0;                 bi = bi.add(BigInteger.ONE)) { //Task to do } here n is a BigInteger variable.