How to read data from Excel using Java?

#java #springboot #springframework #javafullstackdeveloper #javabackenddeveloper #javajobs #javadevelopment #javaprogramming #javabackenddeveloper #javacommunity #javafullstack

Hello everyone!!!

I just published my new article about Reading Excel data and converting it to a Java object and then transferring it to a database. As I developer we need to know efficient way to handle excel data and dump into database. As my 2 years experience, I try to handle about 1 lack excel line data and dump into database, if you have more than efficient code then please let me know and shared the idea I appreciate to share the knowledge.

Here in my article I will not deal with spring batch concept, I only deal with multithreading thread concept available in Java.

There are two concepts of Java code running

1) Synchronous: Sync is single-thread, so only one operation or program will run at a time.

2) Asynchronous: Async is multi-thread, which means operations or programs can run in parallel. Async is non-blocking, which means it will send multiple requests to a server.

First I will write a code without for loop to iterate 1 lack data

With synchronous simple thread and make some observation of time reading excels data.

Let start with code and only read one by one code, I will provide whole source code link of my gitHub account at the end of the article

I assume that you are familiar with the basic spring boot project

Here is our spring boot project

you can make project using

Starting with Spring Initializr

https://start.spring.io/

mainly we need 3 important dependency

now we modify our application properties

we make configuration database and auto table creation ,

i make here create-drop for each testing for production env you should be as

none or update

spring.jpa.hibernate.ddl-auto= create-drop

first start with controller I made 3 endpoint for testing and i will explain one by one

FIRST START WITH

1) /withoutAsync

@RestControlle
@RequestMapping("/api/v1/users")
@AllArgsConstructor
public class UserAccountController {


	private final UserAccountService userAccountService;


	@PostMapping("/withoutAsync")
	public ResponseEntity<?> batchInsertWithoutAsync(@RequestParam("file") MultipartFile multipartFile) {
		userAccountService.batchInsertWithoutAsync(multipartFile);
		return ResponseEntity.ok("File Reading complete without async");
	}r        

then we need to write UserAccountService.java for processing business logic

Here I make generic code for reading excel cell

now we ready to test our first test case for simple sync way

i am using postman for testing and make one excel for contain 1 lack row data

here is my sample header excel file

now try to hit api using postman

Observation 1:

1)time taken for excel read and convert to java object is 11.67 sec

2) 100000 [ 1 lack ] data saving for 43.39 sec

in postman you can see total request process time is= 55.23 sec

ours first testing is completed with sync thread.

also check in database 1 lack data is inserted

2) /Async

here is our second end point

batch Insert Without Async Without Object split

	@PostMapping("/Async"
	public ResponseEntity<?> batchInsertWithoutAsyncWithoutObjectsplit(
			@RequestParam("file") MultipartFile multipartFile) {
		userAccountService.batchInsertWithoutAsyncWithoutObjectsplit(multipartFile);
		return ResponseEntity.ok("File Reading complete with async");
	})        

here in service layer i make code for async executions'

only i have make save data in repo async

What is CompletableFuture? A CompltableFuture is used for asynchronous programming. Asynchronous programming means writing non-blocking code. It runs a task on a separate thread than the main application thread and notifies the main thread about its progress, completion or failure.

Observation 2:

1) time taken for excel read and convert to java object is 13.20 sec

2) 100000 [ 1 lack ] data saving for 0.002 sec because background inserting data asynchronous.

3) in postman you can see total request process time is= 13.93 sec

ours secod testing is completed with Async thread.

also check in database 1 lack data is inserted

3)Async/split/object

here is our third end point

why we need splitting object and save the data because some time excel data over the 10 lack or something then for inserting data we need to split object list and save and clear the object list.

batch Insert Without Async With Object split

@PostMapping("/Async/split/object"
	public ResponseEntity<?> batchInsertWithoutAsyncWithObjectsplit(@RequestParam("file") MultipartFile multipartFile) {
		userAccountService.batchInsertWithoutAsyncWithObjectsplit(multipartFile);
		return ResponseEntity.ok("File Reading complete with async object split");
	})        

now in service layer i am writing splitting object code

here now last end testing with postman

o/p

batch insert start
Execution time for excel read is = 13.78200 seconds
total user Accounts size=100000
sub partitions=25
executeBatch=0
executeBatch=1
Current Thread Name: pool-1-thread-1
executeBatch=2
executeBatch=3
executeBatch=4
executeBatch=5
executeBatch=6
executeBatch=7
executeBatch=8
executeBatch=9
executeBatch=10
executeBatch=11
executeBatch=12
executeBatch=13
executeBatch=14
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-3
executeBatch=15
executeBatch=16
executeBatch=17
executeBatch=18
executeBatch=19
executeBatch=20
executeBatch=21
executeBatch=22
executeBatch=23
executeBatch=24
execute Batch done
Execution time is for all save data=0.01100 seconds
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3
Current Thread Name: pool-1-thread-2
Current Thread Name: pool-1-thread-1
Current Thread Name: pool-1-thread-3t        

Observation 3:

1) time taken for excel read and convert to java object is 13.79 sec

2) 100000 [ 1 lack ] data saving for 0.01 sec because background inserting data asynchronous.

3) in postman you can see total request process time is= 12.69 sec

ours end testing is completed with Async thread.

also check in database 1 lack data is inserted

This article covered the basics of spring boot + Excel data reading in efficient way, complete source code for this article can be found over on https://github.com/bhushan0109/Spring-Boot-Excel-Reader-.git

That’s it for today.

@Author Bhushan Patil