Java - Export Data from Database to Excel

Excel is a widely known and used tool to sort, structure, analyze, and manipulate large amounts of data. If you need to analyze the data in a database, you can convert your data into an Excel spreadsheet first. In this article, I am going to introduce how to export data from an Access table to Excel using Spire.XLS for Java.

Add Spire.Xls jar as dependency

If you are working on a maven project, you can include the dependency in pom.xml file using this:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>12.8.4</version>
    </dependency>
</dependencies>

If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.

Export Data from Database to Excel

The following are the steps to export data from database to Excel using Spire.XLS for Java.

• Create a Workbook object.
• Get the first worksheet using Workbook.getWorksheets().get() method.
• Create a DataTable object.
• Connect to the database and export data from database to the datatable.
• Write datatable to the worksheet using Worksheet.insertDataTable() method.
• Save the workbook to an Excel file using Workbook.saveToFile() method.

import com.spire.data.table.DataTable;
import com.spire.data.table.common.JdbcAdapter;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.sql.*;

public class ExportDataFromDatabaseToExcel {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook wb = new Workbook();

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Create a DataTable object
        DataTable dataTable = new DataTable();

        //Connect to database
        String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};"
                + "DBQ=C:\\Users\\Administrator\\Desktop\\Document\\data.mdb";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            try {
                Connection conn = DriverManager.getConnection(url);
                Statement sta = conn.createStatement();

                //Select table from the database
                ResultSet resultSet = sta.executeQuery("select * from vendors");
                JdbcAdapter jdbcAdapter = new JdbcAdapter();

                //Export data from database to datatable
                jdbcAdapter.fillDataTable(dataTable, resultSet);

            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        //Write datatable to the worksheet
        sheet.insertDataTable(dataTable, true, 1, 1);

        //Auto fit column width
        sheet.getAllocatedRange().autoFitColumns();

        //Save to an Excel file
        wb.saveToFile("output/ExportToExcel.xlsx", ExcelVersion.Version2016);
    }
}

Output