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);
}
}
Top comments (0)