Home Tutorial Java Core Access Excel file through JDBC

 
 

Share on Google+Share on Google+
Access Excel file through JDBC
Posted on: July 9, 2010 at 12:00 AM
Advertisement
In this section, you will learn how to access excel file through Jdbc and display records in JTable.

Access Excel file through JDBC

In this section, you will learn how to access excel file through Jdbc and display records in JTable. As you know Excel comes with an ODBC driver, so we are using JDBC-ODBC bridge driver to connect jdbc and the excel file. Here the name of the worksheet is equivalent to any database table name and the header names found on the first row of the worksheet is equivalent to the field names of the database table. Accordingly we are retrieving all the data using ResultSet.

To create a new ODBC Data Source, follow these steps:

1. Open Data Sources (Start->Settings->Control Panel->Administrative Tool->Data Sources (ODBC)
2. Open User DSN tab
3. Add a user DSN
4. Select Microsoft Excel Driver(*.xls)
5. Select work book or excel file and name the DSN (e.g excel)
6. Click "Ok" and restart your compiler.

Here is the code:

import java.sql.*;
import java.util.*;
import javax.swing.*;
import java.awt.event.*;
import javax.swing.table.*;

public class JDBCExcel {
	public static void main(String args[]) {
		Vector columnNames = new Vector();
		Vector data = new Vector();
		JPanel p = new JPanel();
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn = DriverManager.getConnection("jdbc:odbc:excel","", "");
			Statement st = conn.createStatement();
			String query = "select * from [Excel sheet$]";
			ResultSet rs = st.executeQuery(query);
			ResultSetMetaData md = rs.getMetaData();
			int columns = md.getColumnCount();
			for (int i = 1; i <= columns; i++) {
				columnNames.addElement(md.getColumnName(i));
			}
			while (rs.next()) {
				Vector row = new Vector(columns);
				for (int i = 1; i <= columns; i++) {
					row.addElement(rs.getObject(i));
				}
				data.addElement(row);
			}
			rs.close();
			st.close();
		} catch (Exception e) {
			System.out.println(e);
		}
		JTable table = new JTable(data, columnNames);
		TableColumn col;
		for (int i = 0; i < table.getColumnCount(); i++) {
			col = table.getColumnModel().getColumn(i);
			col.setMaxWidth(250);
		}
		JScrollPane scrollPane = new JScrollPane(table);
		p.add(scrollPane);
		JFrame f = new JFrame();
		f.add(p);
		f.setSize(600, 400);
		f.setVisible(true);
	}
}

Output:

Advertisement

Related Tags for Access Excel file through JDBC:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: July 9, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com