Fetching latest headlines…

Dev

Building an Excel download API with TBEG and Spring Boot (Kotlin/Java)

Dev.toUnited States · NORTH AMERICA

In Part 3, we explored TBEG's template syntax in depth. Now let's build an Excel download API with Spring Boot + TBEG - the most common real-world use case. Add one dependency, and you get an ExcelGen...

2 views0 likes0 comments

In Part 3, we explored TBEG's template syntax in depth. Now let's build an Excel download API with Spring Boot + TBEG - the most common real-world use case.

Add one dependency, and you get an ExcelGenerator bean auto-registered - ready to serve Excel files straight from your controller.

Setup

Adding the Dependency 

// build.gradle.kts
dependencies {
    implementation("io.github.jogakdal:tbeg:1.2.5")
}

For Groovy DSL, Maven, and other build tools, see the GitHub documentation.

application.yml 

tbeg:
  file-naming-mode: timestamp       # none, timestamp
  timestamp-format: yyyyMMdd_HHmmss
  file-conflict-policy: sequence    # error, sequence
  preserve-template-layout: true
  missing-data-behavior: warn       # warn, throw
  unmarked-hide-policy: warn-and-hide  # warn-and-hide, error

These are the key options. For the complete list, see the Configuration Reference.

Auto-Configuration

Adding the tbeg dependency automatically activates TbegAutoConfiguration:

  • ExcelGenerator bean registered automatically
  • application.yml tbeg.* properties bound to TbegProperties
  • Automatic resource cleanup on application shutdown

No @Bean definitions or @EnableXxx annotations required.

Service Pattern

Kotlin 

@Service
class ReportService(
    private val excelGenerator: ExcelGenerator,
    private val resourceLoader: ResourceLoader,
    private val employeeRepository: EmployeeRepository
) {
    /** Generate a simple report using a Map */
    fun generateSimpleReport(): ByteArray {
        val template = resourceLoader.getResource("classpath:templates/simple.xlsx")

        val data = mapOf(
            "title" to "Simple Report",
            "date" to LocalDate.now().toString(),
            "author" to "Yongho Hwang"
        )

        return excelGenerator.generate(template.inputStream, data)
    }

    /** Generate an employee report using DataProvider */
    fun generateEmployeeReport(): Path {
        val template = resourceLoader.getResource("classpath:templates/employees.xlsx")
        val employeeCount = employeeRepository.count().toInt()

        val provider = simpleDataProvider {
            value("title", "Employee Status Report")
            value("date", LocalDate.now().toString())

            // Providing count upfront enables immediate formula range calculation
            // and prevents double iteration for better performance
            items("employees", employeeCount) {
                employeeRepository.findAll().iterator()
            }

            metadata {
                title = "Employee Status Report"
                author = "HR System"
                company = "Example Corp"
            }
        }

        return excelGenerator.generateToFile(
            template = template.inputStream,
            dataProvider = provider,
            outputDir = Path.of("/var/reports"),
            baseFileName = "employee_report"
        )
    }
}

Java 

@Service
public class ReportService {

    private final ExcelGenerator excelGenerator;
    private final ResourceLoader resourceLoader;
    private final EmployeeRepository employeeRepository;

    public ReportService(
            ExcelGenerator excelGenerator,
            ResourceLoader resourceLoader,
            EmployeeRepository employeeRepository) {
        this.excelGenerator = excelGenerator;
        this.resourceLoader = resourceLoader;
        this.employeeRepository = employeeRepository;
    }

    public byte[] generateSimpleReport() throws IOException {
        var template = resourceLoader.getResource("classpath:templates/simple.xlsx");

        Map<String, Object> data = new HashMap<>();
        data.put("title", "Simple Report");
        data.put("date", LocalDate.now().toString());
        data.put("author", "Yongho Hwang");

        return excelGenerator.generate(template.getInputStream(), data);
    }

    public Path generateEmployeeReport() throws IOException {
        var template = resourceLoader.getResource("classpath:templates/employees.xlsx");
        int employeeCount = (int) employeeRepository.count();

        var provider = SimpleDataProvider.builder()
            .value("title", "Employee Status Report")
            .value("date", LocalDate.now().toString())
            .itemsFromSupplier("employees", employeeCount,
                () -> employeeRepository.findAll().iterator())
            .metadata(meta -> meta
                .title("Employee Status Report")
                .author("HR System")
                .company("Example Corp"))
            .build();

        return excelGenerator.generateToFile(
            template.getInputStream(),
            provider,
            Path.of("/var/reports"),
            "employee_report"
        );
    }
}

Key points:

  • ExcelGenerator is injected via constructor - no extra setup
  • generate() -> returns ByteArray (ideal for controller responses)
  • generateToFile() -> returns file Path (saves to disk)
  • simpleDataProvider { } DSL for declarative data composition

Excel Download from Controller

The most common pattern in practice - GET request -> Excel file download.

Kotlin 

@RestController
@RequestMapping("/api/reports")
class ReportController(
    private val excelGenerator: ExcelGenerator,
    private val resourceLoader: ResourceLoader,
    private val employeeRepository: EmployeeRepository
) {
    @GetMapping("/employees/download")
    fun downloadEmployeeReport(): ResponseEntity<Resource> {
        val template = resourceLoader.getResource("classpath:templates/employees.xlsx")

        val data = mapOf(
            "title" to "Employee Status",
            "date" to LocalDate.now().toString(),
            "employees" to employeeRepository.findAll()
        )

        val bytes = excelGenerator.generate(template.inputStream, data)

        val filename = "employee_status_${LocalDate.now()}.xlsx"
        val encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8)

        return ResponseEntity.ok()
            .header(
                HttpHeaders.CONTENT_DISPOSITION,
                "attachment; filename*=UTF-8''$encodedFilename"
            )
            .contentType(MediaType.parseMediaType(
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            ))
            .contentLength(bytes.size.toLong())
            .body(ByteArrayResource(bytes))
    }
}

Java 

@RestController
@RequestMapping("/api/reports")
public class ReportController {

    private final ExcelGenerator excelGenerator;
    private final ResourceLoader resourceLoader;
    private final EmployeeRepository employeeRepository;

    // Constructor omitted...

    @GetMapping("/employees/download")
    public ResponseEntity<Resource> downloadEmployeeReport() throws IOException {
        var template = resourceLoader.getResource("classpath:templates/employees.xlsx");

        Map<String, Object> data = new HashMap<>();
        data.put("title", "Employee Status");
        data.put("date", LocalDate.now().toString());
        data.put("employees", employeeRepository.findAll());

        byte[] bytes = excelGenerator.generate(template.getInputStream(), data);

        String filename = "employee_status_" + LocalDate.now() + ".xlsx";
        String encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8);

        return ResponseEntity.ok()
            .header(
                HttpHeaders.CONTENT_DISPOSITION,
                "attachment; filename*=UTF-8''" + encodedFilename
            )
            .contentType(MediaType.parseMediaType(
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            ))
            .contentLength(bytes.length)
            .body(new ByteArrayResource(bytes));
    }
}

Key points:

  • Content-Disposition: Use filename*=UTF-8'' for proper filename encoding
  • Content-Type: Use the .xlsx-specific MIME type
  • ContentLength: Enables browser download progress display

Streaming Response

The controller example above uses generate() to create a ByteArray first, then wraps it in the response. For large files, generateToStream() writes directly to the HTTP response stream, reducing memory usage.

Kotlin 

@GetMapping("/employees/download/stream")
fun downloadEmployeeReportStream(): ResponseEntity<StreamingResponseBody> {
    val template = resourceLoader.getResource("classpath:templates/employees.xlsx")

    val data = mapOf(
        "title" to "Employee Status",
        "date" to LocalDate.now().toString(),
        "employees" to employeeRepository.findAll()
    )

    val filename = "employee_status_${LocalDate.now()}.xlsx"
    val encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8)

    val body = StreamingResponseBody { outputStream ->
        excelGenerator.generateToStream(template.inputStream, data, outputStream)
    }

    return ResponseEntity.ok()
        .header(
            HttpHeaders.CONTENT_DISPOSITION,
            "attachment; filename*=UTF-8''$encodedFilename"
        )
        .contentType(MediaType.parseMediaType(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        ))
        .body(body)
}

Java 

@GetMapping("/employees/download/stream")
public ResponseEntity<StreamingResponseBody> downloadEmployeeReportStream()
        throws IOException {
    var template = resourceLoader.getResource("classpath:templates/employees.xlsx");

    Map<String, Object> data = new HashMap<>();
    data.put("title", "Employee Status");
    data.put("date", LocalDate.now().toString());
    data.put("employees", employeeRepository.findAll());

    String filename = "employee_status_" + LocalDate.now() + ".xlsx";
    String encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8);

    StreamingResponseBody body = outputStream ->
        excelGenerator.generateToStream(template.getInputStream(), data, outputStream);

    return ResponseEntity.ok()
        .header(
            HttpHeaders.CONTENT_DISPOSITION,
            "attachment; filename*=UTF-8''" + encodedFilename
        )
        .contentType(MediaType.parseMediaType(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        ))
        .body(body);
}

Key points:

  • StreamingResponseBody runs on a separate thread, keeping the servlet thread non-blocking
  • Unlike generate() which returns a ByteArray, generateToStream() writes directly to an OutputStream, making response code more concise
  • ContentLength cannot be set (size is unknown upfront) - the browser shows download size instead of progress

Asynchronous Report Generation

Large reports can take a while. With submitToFile(), you return 202 Accepted immediately and get notified via events when generation completes.

Kotlin 

@PostMapping("/employees/async")
fun generateReportAsync(
    @RequestBody request: ReportRequest
): ResponseEntity<JobResponse> {
    val template = resourceLoader.getResource("classpath:templates/employees.xlsx")

    val provider = simpleDataProvider {
        value("title", request.title)
        items("employees") {
            employeeRepository.findByHireDateBetween(
                request.startDate, request.endDate
            ).iterator()
        }
    }

    val job = excelGenerator.submitToFile(
        template = template.inputStream,
        dataProvider = provider,
        outputDir = Path.of("/var/reports"),
        baseFileName = "employee_report",
        listener = object : ExcelGenerationListener {
            override fun onCompleted(jobId: String, result: GenerationResult) {
                eventPublisher.publishEvent(
                    ReportReadyEvent(jobId, result.filePath!!, result.rowsProcessed)
                )
            }

            override fun onFailed(jobId: String, error: Exception) {
                eventPublisher.publishEvent(
                    ReportFailedEvent(jobId, error.message)
                )
            }
        }
    )

    return ResponseEntity.accepted().body(JobResponse(job.jobId))
}

Java 

@PostMapping("/employees/async")
public ResponseEntity<JobResponse> generateReportAsync(
    @RequestBody ReportRequest request
) throws IOException {
    var template = resourceLoader.getResource("classpath:templates/employees.xlsx");

    var provider = SimpleDataProvider.builder()
        .value("title", request.getTitle())
        .itemsFromSupplier("employees",
            () -> employeeRepository.findByHireDateBetween(
                request.getStartDate(), request.getEndDate()
            ).iterator())
        .build();

    var job = excelGenerator.submitToFile(
        template.getInputStream(),
        provider,
        Path.of("/var/reports"),
        "employee_report",
        new ExcelGenerationListener() {
            @Override
            public void onCompleted(String jobId, GenerationResult result) {
                eventPublisher.publishEvent(
                    new ReportReadyEvent(jobId, result.getFilePath(), result.getRowsProcessed())
                );
            }

            @Override
            public void onFailed(String jobId, Exception error) {
                eventPublisher.publishEvent(
                    new ReportFailedEvent(jobId, error.getMessage())
                );
            }
        }
    );

    return ResponseEntity.accepted().body(new JobResponse(job.getJobId()));
}

ExcelGenerationListener callbacks:

Callback Trigger
onStarted(jobId) Generation begins
onProgress(jobId, progress) At configured row intervals
onCompleted(jobId, result) Generation complete
onFailed(jobId, error) Error occurred

Combine with Spring's @EventListener for email notifications, WebSocket push, and other post-processing workflows.

For a complete async API + WebSocket real-time progress example, see the GitHub documentation.

JPA Stream Integration

For datasets with hundreds of thousands of rows, JPA Streams provide memory-efficient processing.

Kotlin 

@Service
class LargeReportService(
    private val excelGenerator: ExcelGenerator,
    private val resourceLoader: ResourceLoader,
    private val employeeRepository: EmployeeRepository
) {
    @Transactional(readOnly = true)  // Required to keep the Stream alive
    fun generateLargeEmployeeReport(): Path {
        val template = resourceLoader.getResource("classpath:templates/employees.xlsx")
        val employeeCount = employeeRepository.count().toInt()

        val provider = simpleDataProvider {
            value("title", "All Employee Status")

            items("employees", employeeCount) {
                employeeRepository.streamAll().iterator()
            }
        }

        return excelGenerator.generateToFile(
            template = template.inputStream,
            dataProvider = provider,
            outputDir = Path.of("/var/reports"),
            baseFileName = "all_employees"
        )
    }
}

Java 

@Service
public class LargeReportService {

    private final ExcelGenerator excelGenerator;
    private final ResourceLoader resourceLoader;
    private final EmployeeRepository employeeRepository;

    // Constructor omitted...

    @Transactional(readOnly = true)
    public Path generateLargeEmployeeReport() throws IOException {
        var template = resourceLoader.getResource("classpath:templates/employees.xlsx");
        int employeeCount = (int) employeeRepository.count();

        var provider = SimpleDataProvider.builder()
            .value("title", "All Employee Status")
            .itemsFromSupplier("employees", employeeCount,
                () -> employeeRepository.streamAll().iterator())
            .build();

        return excelGenerator.generateToFile(
            template.getInputStream(),
            provider,
            Path.of("/var/reports"),
            "all_employees"
        );
    }
}

Important: When using JPA Streams, the @Transactional annotation is required. The Stream closes when the transaction ends, so the transaction must remain active until Excel generation completes.

For paged Iterator implementations, MyBatis integration, and more, see Advanced Examples.

Writing Tests

Service Unit Test (Kotlin) 

class ReportServiceTest {

    private val excelGenerator = ExcelGenerator()
    private val employeeRepository = mockk<EmployeeRepository>()

    @Test
    fun `employee report generation test`() {
        // Given
        every { employeeRepository.findAll() } returns listOf(
            Employee(1, "Yongho Hwang", "Platform Team", 5000),
            Employee(2, "Yongho Hong", "IT Strategy Team", 4500)
        )

        val template = ClassPathResource("templates/employees.xlsx")

        // When
        val bytes = excelGenerator.generate(
            template.inputStream,
            mapOf(
                "title" to "Test Report",
                "employees" to employeeRepository.findAll()
            )
        )

        // Then
        XSSFWorkbook(ByteArrayInputStream(bytes)).use { workbook ->
            val sheet = workbook.getSheetAt(0)
            assertEquals("Test Report", sheet.getRow(0).getCell(0).stringCellValue)
            assertEquals("Yongho Hwang", sheet.getRow(2).getCell(0).stringCellValue)
            assertEquals("Yongho Hong", sheet.getRow(3).getCell(0).stringCellValue)
        }
    }
}

Service Unit Test (Java) 

class ReportServiceTest {

    private final ExcelGenerator excelGenerator = new ExcelGenerator();

    @Test
    void employeeReportGenerationTest() throws Exception {
        // Given
        var employees = List.of(
            new Employee(1, "Yongho Hwang", "Platform Team", 5000),
            new Employee(2, "Yongho Hong", "IT Strategy Team", 4500)
        );

        var template = new ClassPathResource("templates/employees.xlsx");

        // When
        byte[] bytes = excelGenerator.generate(
            template.getInputStream(),
            Map.of(
                "title", "Test Report",
                "employees", employees
            )
        );

        // Then
        try (var workbook = new XSSFWorkbook(new ByteArrayInputStream(bytes))) {
            var sheet = workbook.getSheetAt(0);
            assertEquals("Test Report", sheet.getRow(0).getCell(0).getStringCellValue());
            assertEquals("Yongho Hwang", sheet.getRow(2).getCell(0).getStringCellValue());
            assertEquals("Yongho Hong", sheet.getRow(3).getCell(0).getStringCellValue());
        }
    }
}

Controller Integration Test (Kotlin) 

@SpringBootTest
@AutoConfigureMockMvc
class ReportControllerTest {

    @Autowired
    lateinit var mockMvc: MockMvc

    @Test
    fun `employee report download test`() {
        mockMvc.get("/api/reports/employees/download")
            .andExpect {
                status { isOk() }
                header {
                    string("Content-Type",
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                }
                header { exists("Content-Disposition") }
            }
    }

    @Test
    fun `async report generation request test`() {
        mockMvc.post("/api/reports/employees/async") {
            contentType = MediaType.APPLICATION_JSON
            content = """
                {
                    "title": "Test Report",
                    "startDate": "2026-01-01",
                    "endDate": "2026-01-31"
                }
            """.trimIndent()
        }.andExpect {
            status { isAccepted() }
            jsonPath("$.jobId") { exists() }
        }
    }
}

Controller Integration Test (Java) 

@SpringBootTest
@AutoConfigureMockMvc
class ReportControllerTest {

    @Autowired
    MockMvc mockMvc;

    @Test
    void downloadEmployeeReport() throws Exception {
        mockMvc.perform(get("/api/reports/employees/download"))
            .andExpect(status().isOk())
            .andExpect(header().string("Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
            .andExpect(header().exists("Content-Disposition"));
    }

    @Test
    void asyncReportGeneration() throws Exception {
        mockMvc.perform(post("/api/reports/employees/async")
                .contentType(MediaType.APPLICATION_JSON)
                .content("{\"title\":\"Test Report\",\"startDate\":\"2026-01-01\",\"endDate\":\"2026-01-31\"}"))
            .andExpect(status().isAccepted())
            .andExpect(jsonPath("$.jobId").exists());
    }
}

Wrap-Up

A summary of the key Spring Boot + TBEG patterns:

Pattern Description
Dependency Add tbeg - Auto-Configuration handles the rest
Service Inject ExcelGenerator -> generate() / generateToFile()
Controller ByteArray -> ResponseEntity<Resource> for downloads
Async submitToFile() + ExcelGenerationListener
Streaming generateToStream() + StreamingResponseBody
JPA Stream @Transactional + streamAll().iterator()
Testing MockMvc for controller integration tests

For complete source code and more examples, see the GitHub documentation.

Next up: Part 5 - TBEG Advanced Features: Charts, Images, Large Data. We'll cover chart auto-adjustment, image insertion, and large-scale data streaming.

⭐ Found TBEG useful? Star it on GitHub - it really helps the project grow.

Comments (0)

Sign in to join the discussion

Be the first to comment!