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.
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.
3 Comments
