Import Data from Microsoft Access Database ResultSet Object to the Worksheet

Possible Usage Scenarios

Aspose.Cells can import data to worksheets from ResultSet object which can be created from any database. However, this article specifically creates a ResultSet object from Microsoft Access Database. Since the code is the same for all types of databases, so you can use it in general.

UCanAccess - Required to Connect to Microsoft Access Database

Please download UCanAccess. It includes the following JAR files. Add all of them in the classpath.

• ucanaccess-4.0.1.jar
• commons-lang-2.6.jar
• commons-logging-1.1.1.jar
• hsqldb.jar
• jackcess-2.1.6.jar

For more help, please visit this Stack Overflow link.

• Manually adding the JARs to your project

Sample Microsoft Access 2016 Database File used inside Sample Code

The following sample Microsoft Access 2016 Database File was used inside the sample code. You can use any database file or create your own.

• Students.accdb

The following screenshot shows the database file when opened in Microsoft Access 2016.

Import Data from Microsoft Access Database ResultSet Object to the Worksheet.

The following sample code executes SQL query from Microsoft Access Database and creates a ResultSet object. Then it imports data from ResultSet object into worksheet using Worksheet.getCells().importResultSet() method. The first time, it uses row and column indices and then it uses cell name to import data into the worksheet. Finally, it saves the workbook as an Output Excel File. The screenshot shows the effect of the sample code on the output Excel file for a reference.

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Create Connection object - connect to Microsoft Access Students Database
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:ucanaccess://" + srcDir + "Students.accdb");

// Create SQL Statement with Connection object
java.sql.Statement st = conn.createStatement();

// Execute SQL Query and obtain ResultSet
java.sql.ResultSet rs = st.executeQuery("SELECT * FROM Student");

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

// Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

// Access cells collection
Cells cells = ws.getCells();

// Create import table options
ImportTableOptions options = new ImportTableOptions();

// Import Result Set at (row=2, column=2)
cells.importResultSet(rs, 2, 2, options);

// Execute SQL Query and obtain ResultSet again
rs = st.executeQuery("SELECT * FROM Student");

// Import Result Set at cell G10
cells.importResultSet(rs, "G10", options);

// Autofit columns
ws.autoFitColumns();

// Save the workbook
wb.save(outDir + "outputImportResultSet.xlsx");