Java - Export Data from Database to Excel
Tags: java, excel, database, export
Published: 2022-08-29 06:57:00

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](https://www.e-iceblue.com/Download/xls-for-java.html).

##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:

```java
<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](https://www.e-iceblue.com/Download/xls-for-java.html). 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.

```java
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
![ExportToExcel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/isuejenxd5k6kgv724aq.png)