Simple JDBC Program to Access/Read Microsoft Excel

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

public static Connection getConnection() throws Exception {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:excelDB";
String username = "yourName";
String password = "yourPass";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}

public static void main(String args[]) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

conn = getConnection();
stmt = conn.createStatement();
String excelQuery = "select * from [Sheet1$]";
rs = stmt.executeQuery(excelQuery);

while (rs.next()) {
System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName"));
}

rs.close();
stmt.close();
conn.close();
}
}

Read and Write Microsoft Excel ( .xls ) Documents with Jakarta's POI Open source

Reading an Excel Document
Reading an existing Excel document is just as simple as creating one. HSSF provides a custom POIFSFileSystem object that specializes in reading Microsoft's OLE 2 documents. Using the POIFSFileSystem object, you construct an HSSFWorkbook object from a specified Excel document. Here's an example.


//create a POIFSFileSystem object to read the data
POIFSFileSystem fs = new POIFSFileSystem(
new FileInputStream(
"c:/dev/src/customs/Book1.xls"));
Next, create a new workbook from the input stream. You can obtain a reference to any of the worksheets within the workbook. Notice that the worksheet index starts at 0.


// create a workbook out of the input stream
HSSFWorkbook wb = new HSSFWorkbook(fs);
// get a reference to the worksheet
HSSFSheet sheet = wb.getSheetAt(0);
A Little Bit More
Some of you may need to use Java to access Excel from a Web application. In such cases, you may need to construct an Excel document and show it in a browser. That's not difficult but does require a little trick. You send the Excel document as the response, but to make it appear in Excel, you need to set the MIME type of the response to the appropriate type. This method is browser independent. Here's an example:


try {
OutputStream out = res.getOutputStream();

// SET THE MIME TYPE
res.setContentType("application/vnd.ms-excel");

// set content dispostion to attachment in
// case the open/save dialog needs to appear
res.setHeader("Content-disposition",
"inline; filename=sample");
workBook.write(out);
out.flush();
out.close();
} catch (FileNotFoundException fne) {
System.out.println("File not found...");
} catch (IOException ioe) {
System.out.println("IOException..." +
ioe.toString());
}
The following points are important:


Use the MIME type string "application/vnd.ms-excel"
In the response header, the content-disposition entry defines how to deliver this content to the browser. For example, an inline attribute specifies that the browser should present an Open/Save/Cancel dialog before opening this document. The filename attribute specifies a default file name for the document.
This tutorial should help you to get started in the world of Java-to-Microsoft bridges. The POI project offers considerably more than I've discussed here, such as the capability to interact with Microsoft Word and with Adobe's PDF files. The project is very active, so you should monitor the POI project and keep up what's going on.

Q : How to read a Microsoft Excel ( .xls ) file from Java?

June 29, 2001

Can I read a Microsoft Excel file from Java? If so, how?

Yes, you can read Microsoft Excel files from Java. Microsoft provides an ODBC driver for Excel, thus allowing you to use JDBC
and the Sun JDBC-ODBC driver to read Excel files.

Assume you have an Excel file named c:\qa.xls . Also suppose that the data is in a worksheet named qas and takes the following excel/04-qa-0629-excel-table.html Following format .

Microsoft's ODBC driver treats the first row in a spreadsheet as the column names and the worksheet name as the database
name.

To access a spreadsheet with JDBC, you need to create a new ODBC data source. To create one in Windows 2000:

1. Go to "Control Panel"


2. Go to "Administrative Tools"


3. Go to "Data Sources"


4. Select "Add"


5. Choose "Microsoft Excel Driver" and "Finish," as seen in Figure 1

Figure 1. Create new data source

Then give the "Data Source Name" qa-list and select the workbook, shown in Figure 2.

Figure 2. ODBC Microsoft Excel Setup

When you are done you should see your new qa-list data source name:

Figure 3. New listing of user data sources

Now the spreadsheet is available as an ODBC source.

Say that you would like to pull out all March 2000 entries. You will need to hit the data source with the following SQL query:

select URL from [qas$] where Month='March' and Year=2000;

Note that the table name is the name of the worksheet with a $ appended to the end. You have to append the $ in order for the query to work. Why? Because. The brackets are there because $ is a reserved character in SQL. Life is never easy.

Now try ExcelReader on for size:

import java.sql.Connection;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.DriverManager;

public class ExcelReader 

{

    public static void main( String [] args )

    {

        Connection c = null;

        Statement stmnt = null;

        try

        {

            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

            c = DriverManager.getConnection( "jdbc:odbc:qa-list", "", "" );

            stmnt = c.createStatement();

            String query = "select URL from [qas$] where Month='March' and Year=2000;";

            ResultSet rs = stmnt.executeQuery( query );

            

            System.out.println( "Found the following URLs for March 2000:" );

            while( rs.next() )

            {

                System.out.println( rs.getString( "URL" ) );

            }

        }

        catch( Exception e )

        {

            System.err.println( e );

        }

        finally

        {

            try

            {

                stmnt.close();

                c.close();

            }

            catch( Exception e )

            {

                System.err.println( e );

            }

        }

    }

}

In ExcelReader, the main() gets a connection to the spreadsheet, then pulls out all of the entries for March 2000.