Access Excel file through JDBC


 

Access Excel file through JDBC

In this section, you will learn how to access excel file through Jdbc and display records in JTable.

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:

Ads