Java Program to Insert Data from a Database to a Spread Sheet

Last Updated : 02 Jan, 2023

A database is a persistent collection of data and information which is organized in a particular manner for quick access similarly spreadsheets are another way to store data in tabular form. There are two types of databases of which structured database particularly MySQL database is illustrated here and LibreOffice Calc as the spreadsheet tool. Insertion of data from MySQL to spreadsheet is very useful as it brings ease in viewing and retrieving of data to everyone who is not familiar with SQL. Working with spreadsheets using java requires the use of APACHE POI. Apache Poi is the Java API for working with Microsoft documents. Download the below-mentioned jar files related to apache which will help us to work with spreadsheets. Similarly, JDBC is the Java API used for connecting java to MySQL. Download the mysql-connector-java-5.1.6-bin.jar file from the link given below.

mysql-connector-java-5.1.6-bin.jar
dom4j-1.6.jar
poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
xmlbeans-2.3.0.jar

Given the following table structure, all the data present in this table should be stored in the spreadsheet with table attributes as the cell names of the spreadsheet.

Algorithm:

Open a new connection to the database using the database login credentials. This is done using the getConnection() method. Each object to the Connection class represents a new connection to the database.
Execute the SQL query and store the result.
Create a new workbook -> sheet -> row.
Create the new cells corresponding to each column in the database table.
Iterate through the stored resultset and store the values in the corresponding column.

Syntax, Parameters, and Return Type of Inbuilt functions used:

1. Connection getConnection(String URL, String username, String Password);
2. Statement createStatement();
3. ResultSet executeQuery(String sql_query);
4. XSSFSheet createSheet(String sheet_name);
5. XSSFRow createRow(int row_no);
6. XSSFCell createCell(int cell_no);
7. void setCellValue(String cell_name);
8. bool next();
9. void write(FileOutputStream output);

Implementation:

Java

// Java Program to Insert Data
// from a Database to a Spread Sheet

// Importing required modules

// File libraries
import java.io.File;
import java.io.FileOutputStream;
// Step 1: Importing Database modules
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
// Importing API modules
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Main (App) class shown only
// not its Connection class
public class GFG {

    // Main driver method
    public static void main(String[] args) throws Exception
    {

        // Step 2 : Load and Register drivers

        // Loading drivers using forName() method
        Class.forName("com.mysql.jdbc.Driver");

        // Registering drivers using Driver Manager
        // Step 3: Establish. a connection
        Connection connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/students", "root",
            "Swapnil@123");

        // Step 4: Process the statement
        // Getting data from the table details
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
            "select * from details");

        // Step 5: Execute a query
        // Create a workbook
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Create a spreadsheet inside a workbook
        XSSFSheet spreadsheet1
            = workbook.createSheet("student db");
        XSSFRow row = spreadsheet1.createRow(1);
        XSSFCell cell;

        // Step 6: Process the results
        cell = row.createCell(1);
        cell.setCellValue("RollNo");

        cell = row.createCell(2);
        cell.setCellValue("Name");

        // i=2 as we will start writing from the
        // 2'nd row
        int i = 2;

        while (resultSet.next()) {
            row = spreadsheet1.createRow(i);
            cell = row.createCell(1);
            cell.setCellValue(resultSet.getInt("RollNo"));

            cell = row.createCell(2);
            cell.setCellValue(resultSet.getString("Name"));

            i++;
        }

        // Local directory on computer
        FileOutputStream output = new FileOutputStream(new File(
            "/home/swapnil/Desktop/sem9/student_database_geeks_for_geeks.xlsx"));

        // write
        workbook.write(output);

        // Step 7: Close the connection
        output.close();

        // Display message for successful compilation of
        // program
        System.out.println(
            "exceldatabase.xlsx written successfully");
    }
}

Output: Internal output: SQL table generated in the terminal(CMD for Windows) reflecting changes made in the database created which are illustrated.