Export Excel in Spring Boot (Using Rest API)
Today, We will export data in the database to an excel file using Apache Poi.
Let’s start.
First of all, we create a spring boot project with spring initializr. We will add Spring Web, Data JPA, MySql Driver and Lombok dependencies.
You can use your favorite database instead of MySql. After opening the project with our favorite IDE, we have to add the Apache Poi dependencies.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>To use in our example, we will create an entity named Product.
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
@Entity
@Table(name = "product")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String code;
private BigDecimal price;
private Long quantity;
}and DTO
@Getter
@Setter
public class ProductDTO {
private String name;
private String code;
private BigDecimal price;
private Long quantity;
}Next, we create a repository for database operations. And we will extend from JpaRepository.
public interface ProductRepository extends JpaRepository<Product,Long> {
}Let’s write the product service.
public interface ProductService {
Product create(ProductDTO productDTO);
List<Product> getAllProduct();
}@Service
@RequiredArgsConstructor
public class ProductServiceImpl implements ProductService {
private final ProductRepository productRepository;
@Override
public Product create(ProductDTO productDTO) {
final var product = new Product();
product.setCode(productDTO.getCode());
product.setPrice(productDTO.getPrice());
product.setName(productDTO.getName());
product.setQuantity(productDTO.getQuantity());
return productRepository.save(product);
}
@Override
public List<Product> getAllProduct() {
return productRepository.findAll();
}
}It’s time to write the controller. And we will create several products using Postman.
@RestController
@RequestMapping("/product")
@RequiredArgsConstructor
public class ProductController {
private final ProductService productService;
@PostMapping("/create")
public ResponseEntity<Product> create(@RequestBody ProductDTO productDTO) {
return ResponseEntity.ok(productService.create(productDTO));
}
@GetMapping("/get-all-products")
public ResponseEntity<List<Product>> getAllProducts() {
return ResponseEntity.ok(productService.getAllProduct());
}
}Let’s check our products.
Now we are ready to write the Excel service. First, we create the DTOs we need. There are two DTOs. First, to use it in the controller. There are 3 fields; Source, MediaType and File Name.
@Getter
@Setter
public class ResourceDTO {
private Resource resource;
private MediaType mediaType;
private String fileName;
}The main purpose of the second one is to make it dynamically available to other services.
@Getter
@Setter
public class ExcelMetadataDTO {
private String tableName;
private List<String> headers;
private List<Map<String, String>> datas;
}Thus, the services will be able to create their own data and use the excel service. Let’s start to write the Excel Service.
public interface ExcelService {
ResourceDTO exportExcel(ExcelMetadataDTO excelMetadataDTO);
}We have an exportExcel method that can be used externally.
@Service
public class ExcelServiceImpl implements ExcelService {
@Override
public ResourceDTO exportExcel(ExcelMetadataDTO excelMetadataDTO) {
Resource resource = prepareExcel(excelMetadataDTO);
final var resourceDTO = new ResourceDTO();
resourceDTO.setResource(resource);
resourceDTO.setMediaType(MediaType.parseMediaType
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
return resourceDTO;
}
private Resource prepareExcel(ExcelMetadataDTO excelMetadataDTO) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(excelMetadataDTO.getTableName());
prepareHeaders(workbook, sheet, excelMetadataDTO.getHeaders());
fillTable(workbook, sheet, excelMetadataDTO.getDatas(), excelMetadataDTO.getHeaders());
try (ByteArrayOutputStream byteArrayOutputStream
= new ByteArrayOutputStream()) {
workbook.write(byteArrayOutputStream);
return new
ByteArrayResource
(byteArrayOutputStream.toByteArray());
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException
("ERROR");
}
}
private void fillTable(Workbook workbook, Sheet sheet,
List<Map<String, String>> datas, List<String> headers) {
int rowNo = 1;
Font font = workbook.createFont();
font.setFontName("Arial");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
for (Map<String, String> data : datas) {
Row row = sheet.createRow(rowNo);
for (int columnNo = 0; columnNo < headers.size(); columnNo++) {
fillCell(sheet, row, columnNo,
String.valueOf(data.get(headers.get(columnNo))), cellStyle);
}
rowNo++;
}
}
private void fillCell(Sheet sheet, Row row, int columnNo,
String value, CellStyle cellStyle) {
Cell cell = row.createCell(columnNo);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
sheet.autoSizeColumn(columnNo);
}
private void prepareHeaders(Workbook workbook,
Sheet sheet, List<String> headers) {
Row headerRow = sheet.createRow(0);
Font font = workbook.createFont();
font.setBold(true);
font.setFontName("Arial");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
int columnNo = 0;
for (String header : headers) {
Cell headerCell = headerRow.createCell(columnNo++);
headerCell.setCellValue(header);
headerCell.setCellStyle(cellStyle);
}
}
}We create the headers and the rest of the table separately. With CellStyle, we can change many features such as background color and aligment.
Now, we can prepare metadata in our product service. And we can use excel service for export function.
private final ExcelService excelService;
@Override
public ExcelMetadataDTO prepareExcelData() {
final var excelMetadataDTO = new ExcelMetadataDTO();
excelMetadataDTO.setTableName("Products");
excelMetadataDTO.setHeaders(List.of("ID", "Name", "Code", "Quantity", "Price"));
final var products = getAllProduct();
List<Map<String, String>> metadata = new ArrayList<>();
for (Product product : products) {
Map<String, String> data = new HashMap<>();
data.put("ID", product.getId().toString());
data.put("Name", product.getName());
data.put("Code", product.getCode());
data.put("Quantity", product.getQuantity().toString());
data.put("Price", product.getPrice().toString());
metadata.add(data);
}
excelMetadataDTO.setDatas(metadata);
return excelMetadataDTO;
}
@Override
public ResourceDTO exportExcel() {
final var resourceDTO = excelService.exportExcel(prepareExcelData());
resourceDTO.setFileName("Products");
return resourceDTO;
}While preparing metadata, our headers and the key of the data should be the same. Our Excel service creates the table by accepting the headers as keys. That’s why we use Map. We call the regarding function from the Excel service. We add our filename.
Now it’s time to write the endpoint.
@GetMapping("/export")
public ResponseEntity<Resource> exportProducts() {
ResourceDTO resourceDTO = productService.exportExcel();
HttpHeaders httpHeaders = new HttpHeaders();
httpHeaders.add("Content-Disposition",
"attachment; filename=" + resourceDTO.getFileName() + ".xlsx");
return ResponseEntity.ok().contentType(resourceDTO.getMediaType())
.headers(httpHeaders).body(resourceDTO.getResource());
}Everything is ready. Let’s try.
After send the request, we’ll save the response as a File.
That’s all. If you want to check source code, click here.
