Building an Excel Download API with TBEG + Spring Boot

🇰🇷 한국어 버전

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.3")
}

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 = "Hunet Inc."
            }
        }

        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("Hunet Inc."))
            .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", "Common 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", "Common 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

Here's 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.