Easy methods to Export Information into Excel in Spring Boot Software

by

[ad_1]

A very powerful factor in any utility is the info. No matter utility kind, exporting information from the applying to an exterior doc format is important. To be particular, we’d like information exporting for numerous reporting functions, tally information, audit functions, and many others. In our earlier tutorial, we realized easy methods to export information into PDF paperwork within the Spring Boot utility. Now it’s time to be taught to export information into an excel doc.

We’re going to use Apache POI for creating, writing, and exporting information into an excel doc.

What do we’d like?

We’d like the next dependencies/applied sciences in our venture:

  1. Java 8
  2. Spring Boot
  3. Apache POI
  4. Spring Internet, Spring Information JPA
  5. Maven

Create the Spring Boot venture

Let’s begin with creating the Spring boot venture with the required dependencies. We’ll create our venture utilizing Spring Boot Initializer API. First, go to begin.spring.io and add Spring internet, Spring Information JPA, H2 database dependencies beneath the dependency part and export the venture.

create Spring Boot project

After downloading the venture, import it into an IDE. We’re utilizing Eclipse IDE right here. To import the venture in eclipse IDE, go to File >> import >> current maven venture and choose the venture to import.

importing the spring boot project

Configure Apache POI

It’s time so as to add maven dependency for Apache POI. We’re utilizing Apache POI for writing into excel paperwork. We’ll want under two dependencies from the Apache POI library.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <model>5.1.0</model>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <model>5.1.0</model>
</dependency>

Configuring H2 database

We have to add the next properties in our utility.properties file:

#H2 Database
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

We’re utilizing the H2 database and the aim of this text is to clarify easy methods to export information into an excel doc. If you wish to use one other database like MySQL, you possibly can configure it right here.

What are we creating?

We are going to take one easy instance right here to elaborate utilization of Apache POI. Let’s create an Examination file utility that’ll include the scholars’ examination information. Additionally, we are going to clarify easy methods to create and export the examination information in an excel doc file.

Create Mannequin Class

We are going to create our mannequin/entity class. It’s going to include the mandatory fields like id, studentName, examYear, rating and many others. 

bundle com.instance.apachepoi.mannequin;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class ExamRecord {

	@Id
	@GeneratedValue(technique = GenerationType.IDENTITY)
	non-public lengthy id;
	
	@Column(title = "title")
	non-public String studentName;
	
	@Column(title = "12 months")
	non-public String examYear;
	
	@Column(title = "rating")
	non-public String rating;

    // Getters and Setters
}

Create a Repository to deal with database operations

Repositories in a Spring boot utility works as a medium between the database and the applying. We use the JPARepository interface to create our Repository interface for our ExamRecord utility.

bundle com.instance.apachepoi.repository;

import org.springframework.information.jpa.repository.JpaRepository;
import com.instance.apachepoi.mannequin.ExamRecord;

public interface ExamRecordRepository extends JpaRepository<ExamRecord, Lengthy> {}

Create Service class

Let’s create our service class now. The service class can have strategies that can name the respective technique from the ExamRecordRepository class to extract outcomes from the database and to insert an examination file within the database.

We’re utilizing the findAll and save technique from the repository that’ll discover all of the information and save a selected examination file respectively within the database.

bundle com.instance.apachepoi.service;

import java.util.Record;
import org.springframework.stereotype.Service;
import com.instance.apachepoi.mannequin.ExamRecord;
import com.instance.apachepoi.repository.ExamRecordRepository;

@Service
public class ExamRecordService {

    @Autowired
	non-public ExamRecordRepository repo;

	public Record<ExamRecord> getAllRecords() {
		return repo.findAll();
	}

	public void addExamRecord(ExamRecord file) {
		repo.save(file);
	}
}

Easy methods to export information into an Excel file?

We’re creating our utility class ExcelGenerator now to create an excel file and add examination information in an excel file. We can be utilizing Apache POI.

bundle com.instance.apachepoi.util;

import java.io.IOException;
import java.util.Record;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.instance.apachepoi.mannequin.ExamRecord;

public class ExcelGenerator {

	non-public Record<ExamRecord> listRecords;
	non-public XSSFWorkbook workbook;
	non-public XSSFSheet sheet;

	public ExcelGenerator(Record<ExamRecord> listRecords) {
		this.listRecords = listRecords;
		workbook = new XSSFWorkbook();
	}

	non-public void writeHeader() {
		sheet = workbook.createSheet("Examination Information");

		Row row = sheet.createRow(0);

		CellStyle type = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setBold(true);
		font.setFontHeight(16);
		type.setFont(font);

		createCell(row, 0, "ID", type);
		createCell(row, 1, "Pupil Identify", type);
		createCell(row, 2, "Examination 12 months", type);
		createCell(row, 3, "Rating", type);

	}

	non-public void createCell(Row row, int columnCount, Object worth, CellStyle type) {
		sheet.autoSizeColumn(columnCount);
		Cell cell = row.createCell(columnCount);
		if (worth instanceof Integer) {
			cell.setCellValue((Integer) worth);
		} 
        else if (worth instanceof Lengthy) {
			cell.setCellValue((Lengthy) worth);
		} else if (worth instanceof Boolean) {
			cell.setCellValue((Boolean) worth);
		} else {
			cell.setCellValue((String) worth);
		}
		cell.setCellStyle(type);
	}

	non-public void write() {
		int rowCount = 1;

		CellStyle type = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setFontHeight(14);
		type.setFont(font);

		for (ExamRecord file : listRecords) {
			Row row = sheet.createRow(rowCount++);
			int columnCount = 0;

			createCell(row, columnCount++, file.getId(), type);
			createCell(row, columnCount++, file.getStudentName(), type);
			createCell(row, columnCount++, file.getExamYear(), type);
			createCell(row, columnCount++, file.getScore(), type);

		}
	}

	public void generate(HttpServletResponse response) throws IOException {
		writeHeader();
		write();
		ServletOutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		workbook.shut();

		outputStream.shut();

	}
}

Let’s perceive the code:

  • We’re utilizing XSSFWorkbook to create the workbook that’s our excel file. Apache POI’s XSSFSheet is used to create the sheet inside our excel workbook.
  • First, we’re creating an occasion of XSSFWorkbook after which calling createSheet technique that’ll create our first sheet contained in the workbook.
  • After, we’re creating rows and cells. In our first row, we’re creating headers.
  • Then, we’re creating extra rows to carry our college students’ examination information information.
  • Lastly, in any case the info is written right into a sheet, we’re writing the outputStream. Don’t forget to shut the assets.

Create Controller class

Now, we are going to create our controller class. This can name the utility class to generate and export the info into an excel doc.

bundle com.instance.apachepoi.controller;

import java.io.IOException;
import java.textual content.DateFormat;
import java.textual content.SimpleDateFormat;
import java.util.Date;
import java.util.Record;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.manufacturing unit.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.internet.bind.annotation.GetMapping;

import com.instance.apachepoi.mannequin.ExamRecord;
import com.instance.apachepoi.service.ExamRecordService;
import com.instance.apachepoi.util.ExcelGenerator;

@Controller
public class ExamRecordController {

	@Autowired
	non-public ExamRecordService service;

	@GetMapping("/information/export/excel")
	public void exportIntoExcel(HttpServletResponse response) throws IOException {
		response.setContentType("utility/octet-stream");
		DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
		String currentDateTime = dateFormatter.format(new Date());

		String headerKey = "Content material-Disposition";
		String headerValue = "attachment; filename=records_" + currentDateTime + ".xlsx";
		response.setHeader(headerKey, headerValue);

		Record<ExamRecord> listOfRecords = service.getAllRecords();

		ExcelGenerator generator = new ExcelGenerator(listOfRecords);

		generator.generate(response);
	}

}

Within the above code, we’re calling the utility class to generate the excel file. We’re setting the content material kind in response, retrieving the listing of all information, then lastly calling the utility technique.

Check the applying

So, there are two methods to check this utility. You possibly can create a view to including pupil examination information after which generate and export the excel utilizing the URL by way of the view.

As the aim of this text is to know how we will export the info into an excel doc, we aren’t making a view for it. Nevertheless, we will use CommandLineRunner to attain the identical outcomes and not using a view.

Let’s implement CommandLineRunner in our Spring boot essential utility:

bundle com.instance.apachepoi;

import org.springframework.beans.manufacturing unit.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.instance.apachepoi.mannequin.ExamRecord;
import com.instance.apachepoi.service.ExamRecordService;

@SpringBootApplication
public class ExamRecordApplication implements CommandLineRunner {

	@Autowired
	non-public ExamRecordService service;

	public static void essential(String[] args) {
		SpringApplication.run(ExamRecordApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {

		for (int i = 0; i < 11; i++) {
			ExamRecord file = new ExamRecord();
			file.setId(i);
			file.setStudentName("Pupil " + i);
			file.setExamYear("2021");
			file.setScore("90%");
			
			service.addExamRecord(file);
		}
	}

}

Outcomes

So let’s take a look at the results of our how-to export information into excel within the spring boot utility. The next are the outcomes of our utility. Nevertheless, check the applying to see the consequence your self.

results in exported excel

Conclusion

On this article, we have now realized to export the excel from spring boot utility. We’ve realized the usage of Apache POI in java. All of the offered instance codes are copy-paste runnable.

[ad_2]

Source_link

Leave a Comment