How to export data from database to excel sheet

It is very simple to import data from database to Excel, follow below steps

I have “student” table in “stu” database and book1.xlsx excel file in x drive.

Student table has three field name, roll, and marks

Before importing data you must have an Excel file in which data is imported and specify field name in cell like NAME, ROLL, and MARKS in different cells.

NAME	ROLL	MARKS

I am using MySql database.

Steps:

1.      Get the reference of Database

   Class.forName("com.mysql.jdbc.Driver");

   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "root", "");

2.      Get the reference of Ms Excel File

static String url = "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};" +

            "DBQ=X:/book1.xlsx;ReadOnly=0;";

Connection con=DriverManager.getConnection(url);

In above url only change the path of your Excel file with extension (checking by right click on file name)

3.      Get the data from a table as ArrayList

ArrayList getDataFromDB(){} 

A user define method used for getting data form table

4.      Get data from ArrayList and insert into Excel Sheet

void setDataIntoSheet(){}

A user define method used for inserting data into excel sheet

Example:

/*

 *

 * Import Data From Database to MsExcel

 */

package mysqltoexcel;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.logging.Level;

import java.util.logging.Logger;

/**

 *

 * @author Navindra Jha

 */

public class MysqlToExcel {

    Connection con = null;

    static String url = "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};"

            + "DBQ=X:/book1.xlsx;ReadOnly=0;";

    private Connection getConnection() {

        try {

            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "root", "");

            return con;

        } catch (Exception ex) {

            return null;

        }

    }

    private Connection getExcelRef() {

        try {

            con = DriverManager.getConnection(url);

            return con;

        } catch (SQLException ex) {

            System.out.println(ex);

            return null;

        }

    }

    private ArrayList getDataFromDB() {

        try {

            con = getConnection();

            ArrayList list = new ArrayList();

            Statement stmt = con.createStatement();

            ResultSet rset = stmt.executeQuery("select * from student");

            while (rset.next()) {

                Student student = new Student(rset.getString("name"), rset.getInt("roll"), rset.getInt("marks"));

                list.add(student);

            }

            return list;

        } catch (SQLException ex) {

            System.out.println(ex);

            return null;

        }

    }

    public void setDataIntoSheet() {

        PreparedStatement ps;

        ArrayList<Student> list = getDataFromDB();

        int i = 0;

        for (Student s : list) {

            try {

                ps = getExcelRef().prepareStatement("insert into [Sheet1$](NAME,ROLL,MARKS) values(?,?,?) ");

                ps.setString(1, s.getName());

                ps.setInt(2, s.getRoll());

                ps.setInt(3, s.getMarks());

                i = ps.executeUpdate();

            } catch (SQLException ex) {

               

            } finally {

                try {

                    con.close();

                } catch (SQLException ex) {

                    

                }

            }

        }

        if (i > 0) {

            System.out.println("Data imported successfully");

        } else {

            System.out.println("Problem in data insertion");

        }

    }

}

Output:

book1.xlsx

NAME	ROLL	MARKS
Naveen	101	800
Rajesh	102	789
Manish	103	678