Trang chủ » Java » Reading Excel Sheet Documents in Java

Reading Excel Sheet Documents in Java


For some reason i found Apache POI difficult to use for reading excel sheet, in terms of naming conventions and the code so i was searching for another Library for reading Excel Sheet in Java and i stopped at JXL.
Java Excel API namely JXL is an Java based API that allows us to read/write/edit the Excel Sheet. The main think i liked about it was its simplicity over Apache POI Package. This article will show you how we can read Excel Sheet using JXL.

Now here i am going to read an excel sheet having content:

EmpId Name Designation

1

ABC

Software Engineer

2

DFG

Sr Software Engineer

3

LOI

Team Leader

4 LKJ Project Manager
5 QWE Software Architech

I have uploaded the same excel sheet on the server you can download it from here: Download Excel Sheet

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Locale;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.DateCell;
public class ReadXLSheet {
    public void init(String filePath) {
        FileInputStream fs = null;
        try {
            fs = new FileInputStream(new File(filePath));
            contentReading(fs);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                fs.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    //Returns the Headings used inside the excel sheet
    public void getHeadingFromXlsFile(Sheet sheet) {
        int columnCount = sheet.getColumns();
        for (int i = 0; i < columnCount; i++) {
            System.out.println(sheet.getCell(i, 0).getContents());
        }
    }
    public void contentReading(InputStream fileInputStream) {
        WorkbookSettings ws = null;
        Workbook workbook = null;
        Sheet s = null;
        Cell rowData[] = null;
        int rowCount = ‘0’;
        int columnCount = ‘0’;
        DateCell dc = null;
        int totalSheet = 0;
        try {
            ws = new WorkbookSettings();
            ws.setLocale(new Locale("en", "EN"));
            workbook = Workbook.getWorkbook(fileInputStream, ws);
            totalSheet = workbook.getNumberOfSheets();
            if(totalSheet > 0) {
                System.out.println("Total Sheet Found:" + totalSheet);
                for(int j=0;j<totalsheet ;j++) {
                    System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
                }
            }
            //Getting Default Sheet i.e. 0
            s = workbook.getSheet(0);
            //Reading Individual Cell
            getHeadingFromXlsFile(s);
            //Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
            System.out.println("Total Rows inside Sheet:" + s.getRows());
            rowCount = s.getRows();
            //Total Total No Of Columns in Sheet
            System.out.println("Total Column inside Sheet:" + s.getColumns());
            columnCount = s.getColumns();
            //Reading Individual Row Content
            for (int i = 0; i < rowCount; i++) {
                //Get Individual Row
                rowData = s.getRow(i);
                if (rowData[0].getContents().length() != 0) { // the first date column must not null
                    for (int j = 0; j < columnCount; j++) {
                        switch (j) {
                        case 0:
                            System.out.println("Employee Id:" + rowData[j].getContents());
                        case 1:
                            System.out.println("Employee Name:" + rowData[j].getContents());
                        case 2:
                            System.out.println("Employee Designation:" + rowData[j].getContents());
                        default:
                            break;
                        }
                    }
                }
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
    }
        public static void main(String[] args) {
        try {
            ReadXLSheet xlReader = new ReadXLSheet();
            xlReader.init("/home/hitesh/Desktop/test.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

Total Sheet Found:3
----------------------------------------------
Sheet Name:Sheet1
Sheet Name:Sheet2
Sheet Name:Sheet3
----------------------------------------------
Column Heading:EmpId
Column Heading:Name
Column Heading:Designation
----------------------------------------------
Total Rows inside Sheet:6
----------------------------------------------
Total Column inside Sheet:3
----------------------------------------------
Employee Id:EmpId
Employee Name:Name
Employee Designation:Designation
----------------------------------------------
Employee Id:1
Employee Name:ABC
Employee Designation:Software Engineer
----------------------------------------------
Employee Id:2
Employee Name:DFG
Employee Designation:Sr Software Engineer
----------------------------------------------
Employee Id:3
Employee Name:LOI
Employee Designation:Team Leader
----------------------------------------------
Employee Id:4
Employee Name:LKJ
Employee Designation:Project Manager
----------------------------------------------
Employee Id:5
Employee Name:QWE
Employee Designation:Software Architect

 

With respect to the following code written i am able to perform the following operation:

  • Calculate total no of Sheets inside the Excel Sheet
  • List all the Sheet Name used inside Excel Sheet
  • Read Individual Cell for specifying the cell position – in code have read the column heading i.e. 1st row
  • Read Total Modified Rows in the Excel Sheet
  • Read Total Modified Columns in the Excel Sheet
  • Read Individual Row Content

 

Source : Techie Zone Blog

Bình luận

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s