Skip to Content

Reading Excel Sheet from Java – 2

In my previous Bolg Reading Excel Sheet from Java without using any Framework I’ve written a code sample that will Read the Excel Sheet using the JDBC-ODBC
Bridge for Java and querying. Here we’ve assumed the Excel Sheet as a database and queried (using SELECT statement) according to our requirement.

But my previous example only queries on only one Sheet (Sheet 1) of Excel Document. As I’ve already discussed the Excel Document as a database, so we
cant expect the data to be stored only in one sheet. Data may be stored in multiple no.of sheets in a single excel file due to the limitation for the no.of rows.

As shown in the following picture data stored in Sheet 2, and may have stored in next sheets too. Here in this case we need to query on multiple no.of sheets
which is my current blog explains about.

image

The following Code Snippet explains you how to query on multiple sheets and fetch data which has the same column structure.

Connection connection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + fileName); Statement statement = connection.createStatement(); DatabaseMetaData dtmt = connection.getMetaData(); ResultSet rs = dtmt.getTables(null, null, null, null); String datasheets = “”; String queryStringHistory = “”; while(rs.next()){ dataSheets = dataSheets + rst.getString(3); } StringTokenizer sheetToken = new StringTokenizer(dataSheets); int totSheets = sheetToken.countTokens(); for(int i = 0; i < totSheets; i++) { queryStringHistory = "Select [Name] from [" + sheetToken.nextToken() + "] where Salary > 20000"; }

What exactly happening:
1#

ResultSet rs = dtmt.getTables(null, null, null, null) String datasheets = “”; while(rs.next()){ dataSheets = dataSheets + rst.getString(3); }

In the previous code the string variable ‘dataSheets’ gets all the available Sheets (which are typically TABLES) in the excel file.
For ex: we have three Sheets as per the screen shot.

2#

for(int i = 0; i < totSheets; i++) { queryStringHistory = "Select [Name] from [" + sheetToken.nextToken() + "] where Salary > 20000"; }

The queryStringHistory is the SELECT statement in the above code will loop through ‘for()-loop’, which looks in to all the Sheets that are available. This is the
easiest ways to fetch the data stored in multiple excel sheets in a single document.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Helmut Tammen
    Hi Munna,

    this is a nice excursion to the JDBC-ODBC Driver but where is the relation to Web Dynpro?
    Web Dynpro applications run at the serverside. So you 1. have to install the jdbc-odbc-driver there and 2. the excel-sheet has also to be at the serverside.

    1. Because most servers run at a unix os it is not as easy as at your local Windows PC to get the jdbc-odbc-driver installed (if it is at all).
    2. Excel-Sheets are normally located at local PC so you or the user of your application first has to upload the sheet to the server and than you can read the data from the file.

    Best regards
    Helmut

    (0) 
    1. Naga Devisetty Post author
      Hi Helmut:
           Thanks for your comment, well this Blog is specifically for the WAS servers on Windows env,. for any windows env we don’t have to setup any odbc & excel installations, since it comes by default.
           In most scenarios the historical data is maintained in the form of excel sheets (bulk data), which can be places over servers and build the GUI (iViews) using web-dynpro according to client rqmt. We do have a scenario where users upload excel sheets (with a specific format) and query according to their rqmt.

           My Blog doesn’t support for Unix envmt.

      Thanks,
      Munna SAP

      (0) 
    2. satya salini
      Hi Munna,

      Can u suggest me how to go ahead with this Blog.
      Do i need to configure JDBC driver in Webdynpro?
      And creat the DSN?

      Thanks in Advance.

      (0) 

Leave a Reply