Blog

How to Export Mysql Data to Excel In Java

  • Post last modified:December 15, 2022
  • Reading time:3 mins read

Introduction

  • Exporting data from one system to another is a very common use case in companies.
  • In this article, we will build a logic to export data from an SQL database and write it to an Excel file that might be useful for some other teams like sales/ marketing for their operation.

Development

Schema

  • We will create a NEWS_ARTICLES table that contains all the data of news.
CREATE TABLE `NEWS_ARTICLES` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `link` varchar(300) DEFAULT NULL,
  `image_url` varchar(300) DEFAULT NULL,
  `category` varchar(1000) DEFAULT NULL,
  `published_date` datetime NOT NULL,
  `create_timestamp` datetime NOT NULL,
  `update_timestamp` datetime NOT NULL,
  `provider` varchar(100) NOT NULL,
  `manual_tags` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8375 DEFAULT CHARSET=latin1;

Client Code

  • At first, we are executing SQL Query on our input table NEWS_ARTICLE.
    This execution will return Resultset object.
  • Once We have ResultSet we will call writeToSheet method whose responsibility is to read ResultSet and write to an excel sheet.
  • After that, we write created a sheet to output the file.

Exporting Data

  • getNewsArticle method takes sqlQuery as a parameter, this defines the query that we would like to execute on the table.
  • We create a JDBC Connection object, then create a Statement object, and finally, executeQuery will take the query as an argument.
  • executeQuery method returns ResultSet which contains the result of the query that was executed against the table.

Write to Excel Sheet

  • We will use a third-party library called Apache POI to operate on an Excel file.
  • In our writeToSheet method, we first create a sheet object and then iterate over Resultset and createRow for the sheet.
  • We are interested in the title of the article and the link to the article. We are also tracking row numbers so that we write different articles for different rows.
  • Now once our sheet is ready and all the content are written then we can finally write the sheet to output file.

Execution

  • When we run our client code we can check if the file and sheet is created or not.

Code Repository

  • The project is available on GitHub.

Conclusion

  • In this article, we develop a program to export the data from MySQL database and write to excel sheet using Apache POI library.

Bonus Tip

Tags: , , , ,

Leave a Reply