DEV Community: Yongho Hwang

Excel report generation in Java and Kotlin: Apache POI vs JXLS vs TBEG

Yongho Hwang — Tue, 20 Oct 2026 08:59:54 +0000

When a single line - "Excel download" - lands in a spec, the work that reaches the developer is far larger than that line suggests.

If dumping data into a grid were all that was needed, CSV would be enough. Real requirements rarely stop there: a header with the company logo, merged title cells, per-category background colors, a formula in the totals row, a chart at the bottom. And the layout has usually already been drawn in Excel by a planner or designer and handed over as a file.

This post compares three ways of meeting that requirement on the JVM, along with the conditions under which each one fits and the conditions under which it does not.

Disclosure: I am the developer of TBEG, one of the three options compared here. The feature comparison is based on running JXLS 3.1.0 directly and inspecting its output, and the performance figures were measured on the same machine under the same workload. The raw measurements and reproduction conditions are linked at the end.

Two Approaches

Generating by code, and binding data into a template.

The first builds cells one at a time with Apache POI and assigns styles in code. The second takes a finished Excel file as a template and binds data into the positions marked inside it. Among the template-based options, JXLS is the oldest and TBEG is a recent arrival.

Apache POI, Written Directly

The most fundamental approach. The other two libraries both use POI internally.

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Employees");

CellStyle header = wb.createCellStyle();
Font bold = wb.createFont();
bold.setBold(true);
header.setFont(bold);
header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
header.setBorderBottom(BorderStyle.THIN);

Row r0 = sheet.createRow(0);
String[] titles = {"Department", "Name", "Position"};
for (int i = 0; i < titles.length; i++) {
    Cell c = r0.createCell(i);
    c.setCellValue(titles[i]);
    c.setCellStyle(header);
}

Six lines go into setting a single background color on the header. Add merging, borders, number formats, column widths, images, and charts, and the code grows to hundreds of lines. And when the designer changes a color, the code has to change.

There are conditions where POI is the right answer: the layout is simple and unlikely to change, the data matters more than the formatting, or dependencies must be kept to a minimum. Writing thirty lines instead of adding another dependency is often the better trade.

Large volumes are handled with SXSSFWorkbook. It keeps a fixed number of rows in memory and flushes the rest to disk, so hundreds of thousands of rows are fine. Rows already written, however, can no longer be modified.

JXLS, Commands in Cell Comments

JXLS is the oldest and most widely used library in this space. Currently 3.1.0, Apache 2.0 licensed.

Expressions such as ${e.name} go into cell values. Commands such as repetition go into Excel cell comments (notes).

Cell A1 value:   ${e.department}
Cell A1 comment: jx:each(items="employees" var="e" lastCell="C2")

The call looks like this.

Map<String, Object> data = new HashMap<>();
data.put("employees", employees);

JxlsPoiTemplateFillerBuilder.newInstance()
    .withTemplate("template.xlsx")
    .build()
    .fill(data, new File("report.xlsx"));

JXLS has two strengths.

The first is maturity. It has been in use for over a decade, and usage examples and troubleshooting material have accumulated. When something breaks, there is prior art to consult. On a large team, or on a project where long-term maintenance matters, that is worth a great deal.

The second is the expressive range of its data-transformation syntax. jx:if shows or hides a region based on a condition, groupBy repeats data in groups, jx:grid builds a table at runtime that was not in the template, jx:each can generate entire sheets, and nested master-detail repetition is available declaratively. On the formula side there is also jointed reference (U_()), which combines separated ranges, and parameterized formulas ($[...]), which inject context variables.

The weakness is that commands are invisible. A cell comment only appears when you hover over it. Opening the template does not tell you at a glance which commands are attached where, and when a lastCell range is off, tracing the cause takes time. The developer has to write comments into a file the designer produced - and if the designer later edits that file again, the comments sometimes disappear.

TBEG, Markers in Cell Text

TBEG is a recent library written in Kotlin. Currently 1.2.5, published to Maven Central as io.github.jogakdal:tbeg.

The approach differs: commands go directly into cell text rather than into comments.

Cell A3: ${emp.department}
Cell B3: ${emp.name}
Cell C3: ${emp.position}
Cell A5: ${repeat(employees, A3:C3, emp)}

ExcelGenerator().use { generator ->
    val bytes = generator.generate(template, data)
}

In Spring Boot, ExcelGenerator is registered as a bean automatically.

The strength is template readability. Open the file and you see what goes where. It is easy to tell a designer "please write this in that cell," and because the markers are plain text, editing the file again does not remove them. Other features include image insertion (${image(...)}), automatic cell merging during repetition (${merge(...)}), conditional field hiding, and automatic range adjustment for charts and pivot tables.

The weakness is plain: it is a new library. Almost no production usage has been published, and searching for a problem turns up little. On a project where long-term maintenance comes first, that should weigh heavily. On a new project where reducing template maintenance cost matters, it is worth evaluating.

Where the Three Diverge - The Location of Commands

The difference among the three comes down to one thing.

	Where formatting is defined	Where repetition and conditions are defined
Apache POI	Code	Code
JXLS	Template file	Cell comments in the template file
TBEG	Template file	Cell text in the template file

It looks minor, but it matters in operation. On a project where the layout changes often, whether the structure is legible the moment you open the template is what determines maintenance cost.

Feature Comparison - Shared Support and Divergent Items

Running both libraries against the same template makes the supported and unsupported items clear.

Start with what both do, which removes most of the confusion. Vertical and horizontal repeat expansion; layout preservation including column widths and row heights; shifting elements below by the expansion amount; automatic formula range adjustment (=SUM(C3) becoming =SUM(C3:C5)); variable injection into formulas; adjustment of formulas referencing another sheet; conditional formatting and data validation range expansion; image insertion; merged cells; SXSSF streaming. All of that works on both sides.

The divergent items are these. Every judgment below was verified by running the libraries.

Feature	JXLS 3.1.0	TBEG 1.2.5
Declarative conditional branching (`jx:if`)	Supported	Filter the data in code instead
Group repeat (`groupBy`)	Supported	No declarative syntax
Nested (master-detail) repeat	Supported	Not supported
Dynamic grid / dynamic sheet generation	Supported	Not supported (by design)
Variable-field tables (hiding columns)	Header row only, so columns misalign	Header and totals removed together
Automatic merging of consecutive identical values	Not supported	Supported
Replacement content for an empty collection	Not supported (rows are deleted)	Supported
Automatic chart range adjustment	Manual (dynamic named range)	Automatic
Pivot tables	Refresh on file open	Cache regenerated
Forward-reference formulas during streaming	Not supported	Supported
Asynchronous generation	None	Coroutines and Future
Spring Boot auto-configuration	None	Available
Document metadata / open password	None	Available

The two aim at different things. JXLS is strong on data-transformation syntax; TBEG is strong on automatic adjustment of native Excel elements such as charts and pivot tables, and on large-scale processing and integration.

Dynamic grids and dynamic sheet generation are marked "by design" because they conflict with the template-based premise. If the goal is binding data into a fixed layout drawn by a designer, there is no need to create a table at runtime that the template does not contain. If that capability is in the requirements, JXLS is the right fit.

Large Data and Streaming

Once row counts pass tens of thousands, memory becomes the constraint. All three rely on Apache POI's SXSSF, but they apply it differently.

POI drives SXSSFWorkbook directly. It offers the widest control, but rows already written cannot be modified.

In JXLS, streaming is optional and off by default. The official documentation states the constraint explicitly: a formula in row 1 cannot reference data in row 2, because row 2 does not exist yet. Streaming is therefore usable only for a simple data list with no forward-reference formulas, and the documentation advises testing each template to see whether streaming is possible. The common layout with a totals row beneath the data falls under this constraint.

TBEG always streams, and performs formula adjustment in a step after rendering. Because final positions are computed before cells are written, formulas that reference rows below - rows not yet written - still resolve correctly. Without a pivot table, it processes up to one million rows.

This is a difference in structure rather than in speed. For a large template that contains formulas, there are cases where streaming cannot be enabled in JXLS at all, and the full in-memory mode becomes the only option.

Performance Measured Under Identical Conditions

A performance comparison means nothing unless the conditions match. Numbers taken on different hardware, different JDKs, and different data shapes are not comparable. Only results measured on the same machine, the same POI version, the same template, and the same workload are given here.

The conditions: Apple Silicon 12-core, OpenJDK 21, 8GB max heap, Apache POI 5.5.1, JMH (fork 1 / warmup 1 / measurement 3), with a 3-column repeat plus a SUM formula as the workload. Since TBEG always streams, the direct comparison is JXLS STREAMING_ON, which shares the same output condition; STREAMING_OFF, the full in-memory mode, is included for reference. All figures below are measured.

Data	Configuration	Duration	Heap allocation
10,000 rows	TBEG	47ms	31MB
10,000 rows	JXLS STREAMING_ON	78ms	67MB
10,000 rows	(ref.) JXLS STREAMING_OFF	393ms	457MB
50,000 rows	TBEG	187ms	134MB
50,000 rows	JXLS STREAMING_ON	378ms	328MB
50,000 rows	(ref.) JXLS STREAMING_OFF	1,306ms	2,244MB
100,000 rows	TBEG	354ms	256MB
100,000 rows	JXLS STREAMING_ON	728ms	641MB
100,000 rows	(ref.) JXLS STREAMING_OFF	2,626ms	4,490MB

Under the same output condition, TBEG is roughly 2x faster. One point in that table is easy to misread, so it is worth spelling out.

Heap allocation is not memory footprint. The heap allocation column is the total volume of temporary objects created and discarded during processing. Peak heap - how much is in use at any single moment - is 353MB for TBEG and 383MB for JXLS STREAMING_ON at 100,000 rows, which is essentially the same. Both stream, so the peak stays bounded as data grows. JXLS is not holding more memory; it creates more temporary objects, so GC runs more often.

The allocation profile shows where the difference comes from. Most of the temporary objects in JXLS originate in the per-cell expression evaluation engine (Apache Commons JEXL) and its own cell-reference objects. That is the cost of building on a general-purpose expression engine. TBEG parses once with a dedicated marker parser and writes straight to POI, so those intermediate objects barely appear. In exchange, that same engine is what gives JXLS the expressive range seen earlier - jx:if, groupBy, and the rest. The slower path is not slower for nothing; it is traded for expressiveness.

Where peak heap does become a real constraint is STREAMING_OFF. It loads all data into the workbook, so the peak climbs to 1,621MB at 100,000 rows. As noted in the previous section, templates containing formulas can force that mode, which makes it the first thing to check for large volumes.

One caveat belongs here. These are our own measurements for a single workload. Column count, formula complexity, and the presence of charts or pivot tables all change the result. If performance is the deciding factor, measuring with your own template and data is the reliable answer.

Selection Criteria

Condition	Choice
Layout is simple and unlikely to change	Apache POI
Dependencies must be kept to a minimum	Apache POI
Hundreds of thousands of rows, minimal formatting	Apache POI (SXSSF) or CSV
Maturity, documentation, and prior art matter	JXLS
Conditional branching, grouping, or nested repeats needed	JXLS
Tables or sheets generated at runtime, not in the template	JXLS
Already running fine on JXLS	Keep it
Designer-authored layouts updated frequently	TBEG
Layouts containing charts or pivot tables	TBEG
Tables whose visible columns depend on permissions	TBEG
Large volumes with formulas, streamed	TBEG
Asynchronous generation or Spring Boot integration	TBEG

There is no reason to replace an implementation that runs fine. If a report built on JXLS is operating without trouble, leaving it alone is the right call. The comparison above is for new work, or for when the current approach has started to hurt.

References

Apache POI
JXLS documentation / GitHub
TBEG on GitHub
TBEG vs JXLS: a detailed comparison - basis for the feature comparison
Detailed performance benchmarks - measurement environment and full figures

Want to see TBEG in practice? The 6-part TBEG series walks through it from your first template-bound report to production troubleshooting.

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

TBEG tips and troubleshooting: template design, performance, and production (Kotlin/Java)

Yongho Hwang — Tue, 20 Oct 2026 04:51:40 +0000

In Part 5, we covered TBEG's advanced features. This final post gathers the practical tips and troubleshooting you'll need to run TBEG in production.

What This Post Covers

When you adopt TBEG in a real project, you'll encounter various situations. This post covers common problems and solutions, along with tips for better results.

Template Design Tips - Building maintainable templates
Performance Optimization - 3-step guide by data size
Common Errors and Solutions - Diagnosing by symptom
Hideable Issues - Selective field visibility troubleshooting
Production Guide - Memory sizing, concurrent requests

1. Template Design Tips

Place repeat markers outside the repeat range

${repeat(...)} markers can be placed anywhere in the workbook. Placing them in the row above the data area improves readability.

	A	B	C
1	${repeat(employees, A2:C2, emp)}
2	${emp.name}	${emp.position}	${emp.salary}

Place formulas below the data area

Formula ranges are automatically expanded regardless of position. However, placing them below follows the natural data -> aggregate order for better readability in Excel.

	A	B	C
1	${repeat(items, A2:C2, item)}
2	${item.name}	${item.value}	${item.qty}
3	Total	=SUM(B2:B2)	=AVERAGE(C2:C2)

All range-referencing formulas including SUM, AVERAGE, COUNT, MAX, and MIN are automatically adjusted.

One row per data item as a rule

One row per data item is the most intuitive approach. For complex layouts, use multi-row repeat (A2:B3).

Use hideable markers for fields that might need hiding

${hideable(emp.salary, C1:C3, dim)}

bundle range manages title and totals together
DIM mode: preserves layout + deactivation style
DELETE mode (default): physically removes the column

Avoid overlapping repeat regions

When placing multiple repeat regions on the same sheet, overlapping column ranges cause errors. Separate by columns or rows.

Full guide: Best Practices

2. Performance Optimization - 3-Step Guide

Apply progressively based on data size.

Step 1: Provide count

Providing the total collection count prevents double data traversal.

val count = employeeRepository.count().toInt()

val provider = simpleDataProvider {
    items("employees", count) {
        employeeRepository.findAll().iterator()
    }
}

Step 2: Lazy loading

Don't preload all data into memory. Use lambdas to load at the point of need.

// Not recommended: preload all data
val allEmployees = employeeRepository.findAll()
items("employees", allEmployees)

// Recommended: lazy loading
items("employees", count) {
    employeeRepository.findAll().iterator()
}

Step 3: DB streaming

For 100K+ rows, use JPA Stream or MyBatis Cursor.

@Transactional(readOnly = true)
fun generateLargeReport(): Path {
    val count = employeeRepository.count().toInt()

    val provider = simpleDataProvider {
        items("employees", count) {
            employeeRepository.streamAll().iterator()
        }
    }

    return excelGenerator.generateToFile(
        template = template,
        dataProvider = provider,
        outputDir = outputDir,
        baseFileName = "large_report"
    )
}

Recommended Settings by Data Size

Data Size	Est. Time	Recommended Approach
~1,000 rows	~20ms	Map is sufficient
~10,000 rows	~110ms	simpleDataProvider + count
~50,000 rows	~500ms	+ DB Stream
~100,000 rows	~1s	+ DB Stream
~500,000 rows	~5s	Custom DataProvider + DB Stream + `generateToFile()`
~1,000,000 rows	~9s	Custom DataProvider + DB Stream + `generateToFile()`

Based on 3-column repeat + SUM formula (DataProvider + generateToFile). Varies by column count, formula complexity, and server specs.

3. Common Errors and Solutions

Markers remain in the output file

When ${title}, ${emp.name} etc. are not substituted:

Cause	Solution
Key missing from data	Add the variable to your data map or DataProvider
Variable name typo	Match marker names exactly with data keys
Repeat variable used outside range	`${emp.name}` etc. are only valid inside the repeat range

Tip: Set TbegConfig(missingDataBehavior = MissingDataBehavior.THROW) to get exceptions for missing data, making it easier to diagnose.

`TemplateProcessingException`

Thrown when syntax errors are found during template parsing.

ErrorType	Cause	Solution
`INVALID_MARKER_SYNTAX`	Marker syntax error	Check parentheses and parameter format
`MISSING_REQUIRED_PARAMETER`	Required param missing	Check `collection`, `range`, etc.
`INVALID_RANGE_FORMAT`	Invalid cell range	Use proper ranges like `A2:C2`
`RANGE_CONFLICT`	Range overlap or crossing	Separate overlapping ranges or adjust to full containment

`OutOfMemoryError`

Occurs when JVM memory is insufficient for large data processing.

Use lazy loading in DataProvider
Increase JVM heap: -Xmx2g
Split data into multiple files

Templates with pivot tables load the entire result file into memory during regeneration, with a practical limit of ~300K rows.

Merge results are not as expected

Merge only combines consecutive cells with the same value. Non-adjacent identical values become separate merge groups.

// Before sort: [Sales1, Sales2, Sales1] -> Sales1 split into 2 groups
// After sort: [Sales1, Sales1, Sales2] -> Sales1 merged into one
val employees = employeeRepository.findAll().sortedBy { it.department }

4. Hideable Issues

hideFields specified but fields are not hidden

Three checkpoints:

Field name mismatch: The field name in hideFields("employees", "salary") must match the template marker (${emp.salary}).
Collection name mismatch: The first argument must match the repeat's collection name.
Field outside repeat: hideFields only applies to repeat item fields.

Only data area is hidden, titles/totals remain

The hideable marker has no bundle parameter.

${hideable(emp.salary, C1:C4)}

Include the field title (C1), data (C2~C3), and total (C4) in the C1:C4 range to process them together.

Bundle range doesn't match the cell

DOWN repeat: The bundle's column range must match the hideable cell's column.
RIGHT repeat: The bundle's row range must match the hideable cell's row.

5. Production Guide

Memory Sizing

Data Scale	Recommended -Xmx	Notes
Under 10K rows	512MB	Most standard reports
100K rows	1~2GB
500K rows	4GB
1M rows	8GB

For concurrent report generation, multiply by the number of concurrent requests.

Concurrent Request Handling

ExcelGenerator is thread-safe, so use it as a singleton bean.

@Configuration
class TbegConfig {
    @Bean
    fun excelGenerator() = ExcelGenerator()
}

Dev/Prod Environment Separation

# application-dev.yml
tbeg:
  missing-data-behavior: throw

# application-prod.yml
tbeg:
  missing-data-behavior: warn

Use THROW in dev to catch missing data immediately, WARN in production.

JPA Stream Caution

JPA Streams close when the transaction ends. The transaction must remain active until Excel generation completes.

@Transactional(readOnly = true)  // Required!
fun generateReport(): ByteArray {
    val provider = simpleDataProvider {
        items("employees", count) {
            employeeRepository.streamAll().iterator()
        }
    }
    return excelGenerator.generate(template, provider)
}

Without @Transactional, you may encounter LazyInitializationException.

Wrap-up

Key takeaways:

Template design: Repeat markers outside range, formulas below, one row per data item
Performance: Provide count -> lazy loading -> DB streaming (3 steps)
Errors: Use MissingDataBehavior.THROW during development
Hideable: Check bundle range and field name alignment
Production: Thread-safe singleton + environment-specific config

For full details, see the GitHub documentation:

Best Practices

Troubleshooting Guide

That wraps up the 6-part TBEG series - from your first template-bound report to production troubleshooting. Thanks for reading.

GitHub: jogakdal/data-processors-with-excel
Maven Central: io.github.jogakdal:tbeg

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

TBEG advanced features: formulas, images, and large-scale streaming (Kotlin/Java)

Yongho Hwang — Mon, 19 Oct 2026 01:46:57 +0000

In Part 4, we integrated TBEG with Spring Boot to build an Excel download API. Now let's explore the features that carry real production reports.

Binding dynamic values into formulas, inserting images, automatically merging cells with the same value, protecting independent regions, selectively hiding fields, and processing 100,000 rows in 0.35 seconds - all with a single library.

Formula Integration

Excel formulas are one of the most powerful features of spreadsheets. TBEG doesn't try to reinvent them - instead, it lets you use formulas as-is in your templates and automatically adjusts their references when data expands.

Binding Variables in Excel Formulas

You can embed TBEG variables directly inside Excel formulas. This is useful when the formula's range depends on data.

Template

	A	B
1	Start	${startRow}
2	End	${endRow}
3	Total	`=SUM(B${startRow}:B${endRow})`

Code

val data = mapOf("startRow" to 4, "endRow" to 6)

ExcelGenerator().use { generator ->
    val bytes = generator.generate(template, data)
    File("output.xlsx").writeBytes(bytes)
}

Result: Cell B3 becomes =SUM(B4:B6) - a live Excel formula.

Hyperlinks

The same technique works with HYPERLINK:

Template cell

=HYPERLINK("${url}", "${text}")

Code

val data = mapOf(
    "url" to "https://github.com/jogakdal/data-processors-with-excel",
    "text" to "View on GitHub"
)

The result is a clickable hyperlink in Excel - no POI hyperlink API needed.

Automatic Formula Range Adjustment

When a repeat region expands, TBEG automatically updates formulas that reference that region.

Template

	A	B
1	${repeat(items, A2:B2, item)}
2	${item.name}	${item.value}
3	Total	`=SUM(B2:B2)`

Result (3 items)

	A	B
2	Item A	100
3	Item B	200
4	Item C	300
5	Total	`=SUM(B2:B4)`

The formula =SUM(B2:B2) is expanded to =SUM(B2:B4) to cover all three rows. The same applies to AVERAGE, COUNT, and any other formula referencing the repeat range. Charts and pivot tables that reference the range are also updated automatically.

Image Insertion

Reports often need logos, stamps, charts, or dynamically generated images. TBEG supports inserting images from both local files and URLs.

Local Files

val provider = simpleDataProvider {
    value("company", "Acme Corp.")
    image("logo", File("logo.png").readBytes())
}

In your template, place ${image(logo)} in the cell where you want the image. The image scales to fit the cell (or merged region) by default.

You can also control the size explicitly:

${image(logo, B2, 200:150)}    // 200px wide, 150px tall
${image(logo, B2, original)}   // Original size
${image(logo, B2, fit)}        // Fit to cell (default)

URL Auto-Download

Don't have the image file locally? Just provide a URL:

val provider = simpleDataProvider {
    imageUrl("chart", "https://example.com/quarterly-chart.png")
}

The image is downloaded during generate() and embedded directly in the Excel file. The generated file works offline - no network access required when opening it.

Within a single generate() call, the same URL is downloaded only once. For caching across multiple calls, configure the TTL:

val config = TbegConfig(imageUrlCacheTtlSeconds = 300)  // Cache for 5 minutes
val generator = ExcelGenerator(config)

If a download fails (network error, 404, etc.), a warning is logged and the image is simply skipped. Excel generation continues normally.

Automatic Cell Merge

When generating grouped reports - sales by department, employees by team - you often want to merge consecutive cells with the same value. Doing this manually with POI is tedious and error-prone. TBEG handles it with a single marker.

How It Works

Place ${merge(emp.dept)} in the template cell instead of ${emp.dept}. During repeat expansion, consecutive rows with the same value are automatically merged.

Template

Code

val data = mapOf(
    "employees" to listOf(
        mapOf("dept" to "Sales",       "name" to "Yongho Hwang", "rank" to "Staff"),
        mapOf("dept" to "Sales",       "name" to "Minjun Park",  "rank" to "Manager"),
        mapOf("dept" to "Engineering", "name" to "Soyeon Kim",   "rank" to "Senior"),
    )
)

Result

Rows 2-3 in column A are merged because they share the value "Sales".

Important: Data must be pre-sorted by the merge key. TBEG merges consecutive identical values - it does not scan the entire column for matches.

You can apply merge to multiple columns for multi-level grouping (e.g., department + team), and each column merges independently.

Bundle (Element Grouping)

When a sheet has two or more repeat regions and certain elements are affected by multiple repeat expansions independently, the intended template layout can break apart.

In such cases, wrapping the elements that should stay intact with a bundle marker causes the entire bundled region to move as if it were a single cell, preserving the layout.

Example

Template (bundle_template.xlsx)

Code

data class Employee(val name: String, val salary: Int)
data class Department(val name: String, val budget: Int)

fun main() {
    val data = mapOf(
        "employees" to listOf(
            Employee("Yongho Hwang", 8000),
            Employee("Yongho Han", 6500),
            Employee("Yongho Hong", 4500),
            Employee("Cheolsu Kim", 5500),
            Employee("Younghee Lee", 7000),
        ),
        "departments" to listOf(
            Department("Common Platform", 50000),
            Department("IT Strategy", 30000),
        )
    )

    ExcelGenerator().use { generator ->
        val template = javaClass.getResourceAsStream("/templates/bundle_template.xlsx")
            ?: throw IllegalStateException("Template not found")

        val bytes = generator.generate(template, data)
        File("bundle_output.xlsx").writeBytes(bytes)
    }
}

With bundle vs Without bundle

Without bundle: Columns A-B are pushed down 4 rows by the employees expansion, columns D-E are pushed down 1 row by the departments expansion, and columns C and F stay at their original template position (row 8) since nothing pushes them. In other words, the carefully designed table layout breaks apart:

With bundle: The bundled region moves together as if it were a single cell, shifting by the maximum expansion amount, so the table layout stays intact.

With bundle applied, the entire specified range moves together as if it were a single cell.

📌 A bundle range must not partially overlap with other range-based elements (merged cells, repeat markers, other bundle markers, etc.). The entire range of any such element must be fully contained within the bundle.

Selective Field Visibility (hideable)

Sometimes you need two versions of the same report - a full version with salary data for HR, and a condensed version without it for general distribution. Instead of maintaining two templates, use hideable.

How It Works

Replace the field marker with a hideable marker:

${hideable(value=emp.salary)}

Then specify which fields to hide in code:

val provider = simpleDataProvider {
    items("employees", employeeList)
    hideFields("employees", "salary")  // Hide the salary field
}

When hideFields is specified, the salary column is removed and remaining columns shift left. When it's not specified, the hideable marker behaves like a normal field - salary values render normally.

One template, two reports.

DELETE vs DIM Mode

Mode	Behavior	Best for
`DELETE`	Physically removes the column, shifts remaining elements (default)	Completely removing sensitive data
`DIM`	Applies gray background + light text, clears data values	Preserving layout while indicating restricted data

${hideable(emp.salary, C2:C4)}           // DELETE mode (default)
${hideable(emp.salary, C2:C4, dim)}      // DIM mode

Hiding Multiple Fields at Once

val provider = simpleDataProvider {
    items("employees", employeeList)
    hideFields("employees", "dept", "salary")  // Hide department and salary together
}

💡 Key point: If you don't call hideFields(), hideable markers behave like normal fields. This means a single template can produce both full and reduced reports.

Bundle Range for Header+Data Hiding

By default, hideable only hides the cell where the marker is placed. Use the bundle parameter to hide the header and totals row together:

Template

	A	B	C	D
1	${repeat(employees, A3:D3, emp)}
2	Name	Position	Salary	Department
3	${emp.name}	${emp.position}	${hideable(emp.salary, C2:C4)}	${emp.dept}
4	Total		=SUM(C3:C3)

The bundle range C2:C4 includes the field title (C2), data (C3), and total (C4). When salary is hidden, the entire column - header, data, and total - is removed together.

Code

val provider = simpleDataProvider {
    items("employees", employeeList)
    hideFields("employees", "salary")
}

Multi-Sheet

TBEG processes all sheets in a workbook. The same data automatically binds to markers across every sheet - no extra configuration needed.

Summary + Detail Pattern

This makes the Summary + Detail pattern straightforward:

Summary sheet

	A	B
1	Title	${title}
2	Total Employees	${size(employees)}

Detail sheet

	A	B	C
1	${repeat(employees, A3:C3, emp)}
2	Name	Position	Salary
3	${emp.name}	${emp.position}	${emp.salary}

Code

val data = mapOf(
    "title" to "Employee Status",
    "employees" to listOf(
        Employee("Yongho Hwang", "Director", 8000),
        Employee("Minjun Park", "Manager", 6500),
        Employee("Soyeon Kim", "Assistant Manager", 4500)
    )
)

ExcelGenerator().use { generator ->
    val bytes = generator.generate(template, data)
    File("multi_sheet_report.xlsx").writeBytes(bytes)
}

The summary sheet shows 3 for ${size(employees)}, while the detail sheet expands all three rows. One data map, all sheets handled.

Asynchronous Processing

For large reports that take seconds to generate, you don't want to block the request thread. TBEG offers three async approaches.

Kotlin Coroutines

ExcelGenerator().use { generator ->
    val path = generator.generateToFileAsync(
        template = template,
        dataProvider = provider,
        outputDir = Path.of("./output"),
        baseFileName = "async_report"
    )
    println("File created: $path")
}

Java CompletableFuture

try (ExcelGenerator generator = new ExcelGenerator();
     InputStream template = new FileInputStream("template.xlsx")) {

    CompletableFuture<Path> future = generator.generateToFileFuture(
        template,
        Map.of("title", "Async Report"),
        Path.of("./output"),
        "async_report"
    );

    future.thenAccept(path -> System.out.println("File created: " + path));
    Path result = future.get();
}

Background Job + Listener

This is the pattern for API servers that need to respond immediately:

val job = generator.submitToFile(
    template = template,
    dataProvider = provider,
    outputDir = Path.of("./output"),
    baseFileName = "background_report",
    listener = object : ExcelGenerationListener {
        override fun onStarted(jobId: String) {
            println("[Started] Job ID: $jobId")
        }

        override fun onCompleted(jobId: String, result: GenerationResult) {
            println("[Completed] File: ${result.filePath}")
            println("[Completed] Rows: ${result.rowsProcessed}")
        }

        override fun onFailed(jobId: String, error: Exception) {
            println("[Failed] ${error.message}")
        }
    }
)

// Respond immediately - don't wait
return ResponseEntity.accepted().body(mapOf("jobId" to job.jobId))

The client gets an HTTP 202 response with the job ID. You can implement a polling endpoint or use WebSocket/SSE to notify when the file is ready.

You can also call job.cancel() to stop generation cooperatively - the renderer detects it and stops, emitting onCancelled, and any partially written file is cleaned up automatically.

Large-Scale Data Processing

This is where TBEG really shines. Processing 100,000 rows without running out of memory is table stakes for enterprise reporting.

Streaming Mode

TBEG uses Apache POI's SXSSF (Streaming Usermodel API) under the hood, maintaining a 100-row buffer that writes sequentially to disk. This means memory usage stays constant regardless of data size.

The key to optimal performance is combining count with a lazy iterator through DataProvider:

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

    // Provide count + lazy iterator for optimal performance
    items("employees", employeeRepository.count().toInt()) {
        employeeRepository.streamAll().iterator()
    }
}

Why provide the count? Without it, TBEG has to traverse the data once just to count it before processing. With the count, it skips straight to rendering.

Performance Benchmark

Test environment: Java 21, macOS, 3 columns repeat + SUM formula

Data Size	Time
1,000 rows	13ms
10,000 rows	47ms
30,000 rows	117ms
50,000 rows	192ms
100,000 rows	354ms

100,000 rows in about 0.35 seconds. The streaming approach makes the growth near-linear.

Comparison with Other Libraries (100,000 rows)

Library	Time
TBEG	0.35s
JXLS (STREAMING_ON)	0.73s
(ref.) JXLS (STREAMING_OFF)	2.63s

Under identical streaming conditions, TBEG leads JXLS STREAMING_ON by roughly 2x in time and 2.5x in heap allocation. STREAMING_OFF (full in-memory mode) is a reference figure. TBEG calls the POI API directly and writes in a single pass with streaming, whereas JXLS goes through an abstraction layer performing multiple passes of template parsing, transformation, and writing.

Detailed benchmark results: benchmark-results.md

Comprehensive Example: Quarterly Sales Report

Let's tie everything together with a real-world example that uses nearly every feature covered in this series.

Template

Download template (rich_sample_template.xlsx)

This template includes:

Variable markers: report title, period, author, date
Image markers: company logo, CI
Three repeat regions: department performance, product categories, employee details
Auto-merge markers: department and team columns in the employee table
Bundle marker: protects the employee table from department repeat expansion
Formulas: SUM, AVERAGE for totals, inter-cell calculations for profit and achievement rate
Conditional formatting: green/red highlights based on achievement thresholds
Charts: bar chart for department performance, pie chart for product categories

Code

import io.github.jogakdal.tbeg.ExcelGenerator
import io.github.jogakdal.tbeg.simpleDataProvider
import java.io.File
import java.nio.file.Path
import java.time.LocalDate

data class DeptResult(val deptName: String, val revenue: Long, val cost: Long, val target: Long)
data class ProductCategory(val category: String, val revenue: Long)
data class Employee(
    val dept: String, val team: String, val name: String, val rank: String,
    val revenue: Long, val cost: Long, val target: Long
)

fun main() {
    val data = simpleDataProvider {
        value("reportTitle", "Q1 2026 Sales Performance Report")
        value("period", "Jan 2026 ~ Mar 2026")
        value("author", "Yongho Hwang")
        value("reportDate", LocalDate.now().toString())
        value("subtitle_emp", "Employee Performance Details")
        image("logo", File("company_logo.png").readBytes())
        image("ci", File("company_ci.png").readBytes())

        items("depts") {
            listOf(
                DeptResult("Common Platform", 52000, 31000, 50000),
                DeptResult("IT Strategy",     38000, 22000, 40000),
                DeptResult("HR Management",   28000, 19000, 30000),
                DeptResult("Education Biz",   95000, 61000, 90000),
                DeptResult("Content Dev",     42000, 28000, 45000),
            ).iterator()
        }

        items("products") {
            listOf(
                ProductCategory("Online Courses", 128000),
                ProductCategory("Consulting", 67000),
                ProductCategory("Certification", 45000),
                ProductCategory("Contents License", 15000),
            ).iterator()
        }

        items("employees") {
            listOf(
                Employee("Common Platform", "Strategy", "Yongho Hwang", "Manager", 18000, 11000, 17000),
                Employee("Common Platform", "Strategy", "Sungjun Park", "Senior", 15000, 9000, 14000),
                Employee("Common Platform", "Backend", "Changmin Choi", "Senior", 12000, 7000, 13000),
                Employee("Common Platform", "Backend", "Hyunkyung Kim", "Junior", 7000, 4000, 6000),
                Employee("IT Strategy", "Planning", "Jaemyung Byun", "Manager", 20000, 12000, 20000),
                Employee("IT Strategy", "Planning", "Minchul Kim", "Senior", 11000, 6000, 12000),
                Employee("IT Strategy", "Analysis", "Minhee Kim", "Senior", 7000, 4000, 8000),
                Employee("Education Biz", "Sales", "Seojin Yoon", "Manager", 35000, 22000, 30000),
                Employee("Education Biz", "Sales", "Minwoo Kang", "Senior", 28000, 18000, 25000),
                Employee("Education Biz", "Sales", "Soyeon Lim", "Junior", 15000, 10000, 15000),
                Employee("Education Biz", "Support", "Junhyeok Oh", "Senior", 17000, 11000, 20000),
            ).iterator()
        }
    }

    ExcelGenerator().use { generator ->
        val template = File("rich_sample_template.xlsx").inputStream()
        generator.generateToFile(template, data, Path.of("output"), "quarterly_report")
    }
}

Result

Everything TBEG handled automatically:

Variable substitution - title, period, author, date, section subtitle
Image insertion - logo, CI
Repeat expansion - departments (5 rows), products (4 rows), employees (11 rows)
Automatic cell merge - consecutive department/team names merged
Bundle - employee table protected from department expansion
Formula range adjustment - SUM(C8:C8) -> SUM(C8:C12), etc.
Conditional formatting replication - color rules applied to all expanded rows
Chart data range reflection - charts reference the expanded data

All from a single template and a single generate() call.

Wrap-Up

Here's a summary of the advanced features covered in this post:

Feature	What It Does	Key Syntax / API
Formula integration	Bind variables inside Excel formulas	`=SUM(B${startRow}:B${endRow})`
Image insertion	Embed images from files or URLs	`image()`, `imageUrl()`
Automatic cell merge	Merge consecutive identical values	`${merge(item.field)}`
Bundle	Group elements to move as a unit	`${bundle(range)}`
Selective field visibility	Hide/show fields conditionally	`${hideable(value=item.field)}` + `hideFields()`
Multi-sheet	Same data binds across all sheets	Automatic - no extra config
Async processing	Coroutines, CompletableFuture, background jobs	`generateToFileAsync()`, `submitToFile()`
Streaming mode	Constant-memory large data processing	Built-in - always active

In the next and final post, Part 6: Tips and Troubleshooting, we'll cover best practices for template design, common pitfalls, debugging techniques, and performance optimization tips.

Detailed Docs

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

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

Yongho Hwang — Mon, 12 Oct 2026 07:24:24 +0000

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.

Tune your Django/SQLAlchemy queries with a Claude Code plugin

Yongho Hwang — Thu, 08 Oct 2026 09:03:01 +0000

query-inspector is a Claude Code plugin that inspects the SQL and ORM-generated queries in your code without running the app.

It reports N+1s, missing indexes, and other anti-patterns with paste-ready fixes. It reads the code and infers the SQL your ORM will generate - no app boot, no production database.

As of 1.1.0 it covers Python (Django/SQLAlchemy) alongside the JVM stacks it started with.

The problem

An ORM hides the SQL it generates. To find a query problem with the usual tools (APM, slow-query logs, django-debug-toolbar), you first have to run it - and reproduce the exact situation where that query fires.

Worse, most DB problems only show up once real data has grown large or varied. They slip past QA and usually surface in production - often only when things are near an outage - and then you're patching a running service.

query-inspector reads the code statically and surfaces the SQL problem without running anything.

Run it once right before a commit and it scans only the diff, so you catch and fix issues quickly.

What it checks (Python)

It routes Django/SQLAlchemy code through adapters, reconstructs the SQL, and checks it against your schema.

N+1 - repeated lookups outside the view: loops, templates, serializer fields, a model in an admin list.
Missing indexes - WHERE/join/order-by columns checked against your models or migrations, with a paste-ready CREATE INDEX.
Hidden correctness/permission defects - problems that surface only in the compiled SQL (e.g. a filter that actually passes every row).
Dialect & migrations - Alembic and Django migrations read as a first-class index source.

Inferred queries are labeled INFERRED/AMBIGUOUS, and the key ones are compile-verified.

How it differs from a linter or APM

No run required - no waiting for a slow request to show up in a trace.
Wider N+1 reach - templates, serializers, admin, not just the view.
Fixes, not just findings - a CREATE INDEX, a selectinload(...), paste-ready. It suggests; it never edits your code.

How it works

Three tiers, degrading automatically:

Static heuristics - no DB, always on.
Schema context - models/migrations confirm index coverage; concrete CREATE INDEX.
Live EXPLAIN - only with --db, read-only, production hosts blocked.

Kotlin/Java is the primary focus and Python is an extension; 1.1.0 validated the Python adapters against real open-source projects.

Try it

claude plugin marketplace add jogakdal/query-inspector
claude plugin install query-inspector@query-inspector-marketplace

In your project directory, open a new Claude session and run:

/query-inspector:tuning-report

/query-inspector:inventory-report catalogs every query the project runs. For the full manual, see the README in the repo below.

Repo (MIT): https://github.com/jogakdal/query-inspector

Run it on your own project and tell me what it caught - or got wrong.

TBEG template syntax: repeat, merge, bundle, and hideable (Kotlin/Java)

Yongho Hwang — Thu, 08 Oct 2026 08:26:01 +0000

In Part 2, we walked through the basics of TBEG - creating an Excel template, binding data, and generating a report. Now it's time to dive deeper.

This part is a guide to TBEG's template syntax. We'll cover every ${...} marker in detail - what it does, how to use it, and how to combine them for complex reports.

Two Types of Markers

Everything dynamic in a TBEG template is expressed through markers. There are two types.

Variable Markers

${variableName}
${emp.name}
${emp.department.name}

These are replaced with actual data values. Dot notation enables nested property access.

Function Markers

${repeat(employees, A3:C3, emp)}
${image(logo, B2, 200:150)}
${merge(emp.dept)}
${bundle(A5:H12)}
${size(employees)}

These perform special operations. You can also write them in formula style:

Text Form	Formula Form
`${repeat(col, range, var)}`	`=TBEG_REPEAT(col, range, var)`
`${image(name)}`	`=TBEG_IMAGE(name)`
`${merge(item.field)}`	`=TBEG_MERGE(item.field)`
`${bundle(range)}`	`=TBEG_BUNDLE(range)`

Both forms behave identically. Writing in formula form is convenient because you can use Excel's cell reference features (click, drag) to specify ranges.

Case sensitivity: Function names and keywords (DOWN, RIGHT, fit, etc.) are case-insensitive. Data keys (emp.name, employeeName, etc.) are case-sensitive.

Variable Substitution

Marker: ${variableName}

Simple Variables

Template	Result
`${title}`	Monthly Report
`${date}`	2026-01-15
`${author}`	Yongho Hwang

Multiple variables can appear in a single cell:

Author: ${author} (${department})

Formula Binding

When a bound value starts with =, TBEG treats it as an Excel formula.

mapOf("formula" to "=SUM(A1:A10)")
// Template: ${formula}
// Result: the cell contains the actual formula =SUM(A1:A10)

This works inside repeat items too - formulas are automatically adjusted for row shifting and range expansion.

Auto-Adjustment Inside Repeat

Formula bindings within a repeat block automatically get row-shifted and range-expanded.

Template

	A	B	C	D
1	${repeat(sales, A3:D3, s)}
2	Name	Revenue	Target	Rate
3	${s.name}	${s.amount}	${s.target}	${s.rateFormula}
4	Total	${totalRevenue}

Data

mapOf(
    "sales" to listOf(
        mapOf("name" to "Team A", "amount" to 15000, "target" to 20000, "rateFormula" to "=B3/C3"),
        mapOf("name" to "Team B", "amount" to 22000, "target" to 18000, "rateFormula" to "=B3/C3"),
    ),
    "totalRevenue" to "=SUM(B3:B3)"
)

Result (2 items)

	A	B	C	D
2	Name	Revenue	Target	Rate
3	Team A	15,000	20,000	=B3/C3
4	Team B	22,000	18,000	=B4/C4
5	Total	=SUM(B3:B4)

rateFormula (=B3/C3) becomes =B3/C3, =B4/C4 - row-shifted per item
totalRevenue (=SUM(B3:B3)) becomes =SUM(B3:B4) - range expanded to cover all items

Tip: All string values starting with = are treated as formulas. To output literal text starting with =, prepend a space.

Repeat Processing

Marker: ${repeat(collection, range, variable, direction, fallbackRange)}

The most commonly used marker in practice. Repeats a template range once for each item in a collection.

Basic Repeat (DOWN)

Template

	A	B	C
1	`${repeat(employees, A3:C3, emp)}`
2	Name	Position	Salary
3	`${emp.name}`	`${emp.position}`	`${emp.salary}`

Result (3 items)

	A	B	C
2	Name	Position	Salary
3	Yongho Hwang	Director	8,000
4	Yongho Han	Manager	6,500
5	Yongho Hong	Asst. Manager	4,500

Rightward Repeat (RIGHT)

Add RIGHT as the 4th parameter to expand columns instead of rows.

Template

	A	B
1	`${repeat(months, B1:B2, m, RIGHT)}`	`${m.month}`
2		`${m.sales}`

Result

	A	B	C	D
1		Jan	Feb	Mar
2		100	150	200

Multi-Row Repeat

Specifying multiple rows repeats them as a group.

Template

	A	B
1	`${repeat(employees, A2:B3, emp)}`
2	Name: `${emp.name}`	Position: `${emp.position}`
3	Salary: `${emp.salary}`

A2:B3 (2 rows) is one repeat unit - each employee renders as 2 rows.

Result (2 items)

	A	B
1
2	Name: Yongho Hwang	Position: Director
3	Salary: 8,000
4	Name: Yongho Han	Position: Manager
5	Salary: 6,500

Empty Collection Handling

The 5th parameter specifies a fallback range displayed when the collection is empty.

Template

	A	B	C
1	`${repeat(employees, A3:C3, emp, DOWN, A10)}`
2	Name	Position	Salary
3	`${emp.name}`	`${emp.position}`	`${emp.salary}`
...
10	(No data available)

When the fallback range is a single cell (A10), the entire repeat region (A3:C3) is automatically merged.

Result (empty collection)

The content and style from A10 are copied to A3, and A3:C3 is merged into a single cell
If the fallback range spans multiple cells (e.g., A10:C10), each cell is copied individually without merging

Image Insertion

Marker: ${image(name, position, size)}

Inserts images into the cell (or merged region) where the marker is placed.

val provider = simpleDataProvider {
    image("logo", logoBytes)                             // ByteArray
    imageUrl("banner", "https://example.com/banner.png") // URL (auto-downloaded)
}

Size Options

Notation	Description
`fit` or `0:0`	Fit to cell/range (default)
`original` or `-1:-1`	Original size
`200:150`	200px wide, 150px tall
`200:-1`	200px wide, proportional height
`-1:150`	150px tall, proportional width

${image(logo, B2)}            // Fit to cell (default)
${image(logo, B2, original)}  // Original size
${image(logo, B2, 200:150)}   // 200x150 pixels

Automatic Cell Merge

Marker: ${merge(item.field)}

Automatically merges consecutive cells with the same value during repeat expansion. Useful for grouped tables like department-based reports.

Example

Template

	A	B	C	D
1	Department	Name	Rank	`${repeat(employees, A2:C2, emp)}`
2	`${merge(emp.dept)}`	`${emp.name}`	`${emp.rank}`

Data - employee list sorted by department

mapOf(
    "employees" to listOf(
        mapOf("dept" to "Sales", "name" to "Yongho Hwang", "rank" to "Staff"),
        mapOf("dept" to "Sales", "name" to "Yongho Han", "rank" to "Assistant Manager"),
        mapOf("dept" to "Engineering", "name" to "Yongho Hong", "rank" to "Manager"),
    )
)

Result - "Sales" is automatically merged across rows 2-3

Column A's "Sales" appears in 2 consecutive rows, so TBEG automatically merges them vertically. For multi-column merging (e.g., department + team), simply add a merge marker to each column.

Data must be pre-sorted by the merge key field.

Element Bundling

Marker: ${bundle(range)}

Treats all elements within the specified range as a single unit, ensuring they move together during repeat expansion.

Need for Bundling

When repeat adds rows, only the columns within the repeat range shift down - columns outside the range stay at their original rows. This causes different content to end up on the same row, breaking the table layout.

Example

Consider a template where columns A-B have a department repeat, and a detailed table spanning columns A-E sits below (rows 4-6).

Template

	A	B	C	D	E
1	`${repeat(depts, A2:B2, dept)}`
2	`${dept.name}`	`${dept.revenue}`
3	`${bundle(A4:E6)}`
4	Name	Revenue	Cost	Profit	Total
5	Yongho Hwang	1000	500	500	2000
6	Total				=SUM()

Without bundle - only repeat columns (A-B) shift, the C-E table stays put:

	A	B	C	D	E
2	Sales	52000
3	Engineering	38000
4	Planning	28000	Cost	Profit	Total
5			500	500	2000
6	Name	Revenue			=SUM()
7	Yongho Hwang	1000
8	Total

The A-B table (Name, Revenue, ...) was pushed down to row 6 by the repeat expansion, but the C-E table (Cost, Profit, Total) stayed at its original row 4. Columns from the same table are scattered across different rows - the layout is broken.

With bundle(A4:E6) - all columns A-E move together:

	A	B	C	D	E
2	Sales	52000
3	Engineering	38000
4	Planning	28000
5
6	Name	Revenue	Cost	Profit	Total
7	Yongho Hwang	1000	500	500	2000
8	Total				=SUM()

All columns start at the same row - the table stays intact.

Other Useful Markers

Collection Size

Marker: ${size(collection)}

Total employees: ${size(employees)}  ->  Total employees: 5

Variables in Formulas

TBEG variables can be used within Excel formulas.

=HYPERLINK("${linkUrl}", "${linkText}")
=SUM(B${startRow}:B${endRow})

Automatic Adjustment of Related Elements

When a repeat region expands, TBEG automatically adjusts:

Element	Adjustment
Formula references	`=SUM(B2:B2)` -> `=SUM(B2:B4)` (range expanded)
Charts	Data source ranges expanded
Pivot tables	Source data ranges expanded
Merged cells	Positions shifted
Conditional formats	Applied ranges expanded

Controlling Expansion with Absolute References

Use absolute references ($) when you want a formula range to stay fixed despite repeat expansion.

=SUM(B3:B3)      -> =SUM(B3:B5)     // Relative: expanded
=SUM($B$3:$B$3)  -> =SUM($B$3:$B$3) // Absolute: preserved
=SUM($B3:$B3)    -> =SUM($B3:$B5)   // Column-absolute: row expansion applies

Selective Field Visibility (hideable)

Marker: ${hideable(value=item.field, ...)}

You can hide or deactivate specific fields (columns) based on conditions. For example, generate both a "salary-included report" and a "salary-excluded report" from the same template.

Basic Usage

Place a hideable marker on the field that can be hidden:

${hideable(value=emp.salary)}

In code, specify which fields to hide:

val provider = simpleDataProvider {
    items("employees", employeeList)
    hideFields("employees", "salary")  // Hide salary field
}

If hideFields is not specified, the value is rendered like a regular field.

HideMode

Mode	Behavior	Example
DELETE (default)	Physically removes the column/row and shifts the rest	Salary column disappears entirely
DIM	Removes value and applies deactivation style (column/row preserved)	Salary column exists but empty + gray background

${hideable(value=emp.salary, mode=dim)}

Bundle Range

To hide a header and data cells together, specify the range with the bundle parameter:

${hideable(value=emp.salary, bundle=C1:C3)}

This hides everything from C1 (header) to C3 (data) at once.

Formula Marker

=TBEG_HIDEABLE(value=emp.salary, bundle=C1:C3, mode=DELETE)

Note: The hideable marker can only be used on item fields within a repeat region. It cannot be used on simple variables.

Practical Tips

Marker placement: Function markers can be placed anywhere outside the target range - even on a different sheet. Grouping them on a separate sheet improves template readability.

Explicit parameters: For long parameter lists, naming them improves clarity.

${repeat(collection=employees, range=A3:C3, var=emp, direction=DOWN)}

No boundary overlap: Ranges of repeat, bundle, merge, etc. must not partially overlap. Each element must be either fully contained within another or fully outside.

Wrap-Up

A summary of everything covered:

Marker	Purpose
`${variableName}`	Simple substitution, dot notation, formula binding
`${repeat(...)}`	Expand lists into rows/columns, empty collection handling
`${image(...)}`	Insert images, auto-download from URL
`${merge(...)}`	Auto-merge consecutive identical values
`${bundle(...)}`	Keep elements moving as a unit
`${size(...)}`	Collection item count
`${hideable(...)}`	Selective field visibility (DELETE/DIM)

For the full parameters and edge cases of each syntax, see the Template Syntax Reference on GitHub.

Next up: Part 4 - Spring Boot Integration. Auto-configuration, @RestController Excel responses, and more.

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

Your first Excel report in 5 minutes - a TBEG quick start (Kotlin/Java)

Yongho Hwang — Wed, 07 Oct 2026 04:11:13 +0000

In Part 1, we explored what TBEG is. In this post, we'll write actual code and experience TBEG's core features hands-on.

Step 1. Add Dependency

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

Groovy / Maven:

// build.gradle
dependencies {
    implementation 'io.github.jogakdal:tbeg:1.2.5'
}

<!-- pom.xml -->
<dependency>
    <groupId>io.github.jogakdal</groupId>
    <artifactId>tbeg</artifactId>
    <version>1.2.5</version>
</dependency>

The version above is as of this writing. Check the latest version on Maven Central.

Step 2. Simplest Example - Variable Substitution

Create a Template

Open Excel and enter the following:

${...} is TBEG's variable marker. The values passed from code will fill these placeholders.

Write the Code

import io.github.jogakdal.tbeg.ExcelGenerator
import java.io.File
import java.time.LocalDate

fun main() {
    val data = mapOf(
        "title" to "Monthly Report",
        "date" to LocalDate.now().toString(),
        "author" to "Yongho Hwang"
    )

    ExcelGenerator().use { generator ->
        val template = File("template.xlsx").inputStream()
        val bytes = generator.generate(template, data)
        File("output.xlsx").writeBytes(bytes)
    }
}

Result

That's it. Open output.xlsx and you'll find "Monthly Report" where ${title} was, and today's date where ${date} was.

Core pattern: ExcelGenerator().use { ... } → read template → pass data → get byte array. This pattern is the same in every example that follows.

Java version:

import io.github.jogakdal.tbeg.ExcelGenerator;
import java.io.*;
import java.time.LocalDate;
import java.util.*;

public class QuickStart {
    public static void main(String[] args) throws Exception {
        Map<String, Object> data = new HashMap<>();
        data.put("title", "Monthly Report");
        data.put("date", LocalDate.now().toString());
        data.put("author", "Yongho Hwang");

        try (ExcelGenerator generator = new ExcelGenerator();
             InputStream template = new FileInputStream("template.xlsx")) {
            byte[] bytes = generator.generate(template, data);
            try (FileOutputStream output = new FileOutputStream("output.xlsx")) {
                output.write(bytes);
            }
        }
    }
}

Step 3. Repeating List Data - repeat

This is the most commonly used feature in practice. Use it whenever you need to expand a list into rows - employee lists, order histories, sales data, etc.

Template

Row 1: ${repeat(employees, A3:C3, emp)} - "For each item in the employees collection, name it emp and repeat the A3:C3 range"
Row 2: Header (outside the repeat range, stays as-is)
Row 3: Repeat range - emp's fields are filled into each row

Code

data class Employee(val name: String, val position: String, val salary: Int)

fun main() {
    val data = mapOf(
        "employees" to listOf(
            Employee("Yongho Hwang", "Director", 8000),
            Employee("Yongho Han", "Manager", 6500),
            Employee("Yongho Hong", "Assistant Manager", 4500),
            Employee("Yongho Kim", "Staff", 3500)
        )
    )

    ExcelGenerator().use { generator ->
        val bytes = generator.generate(File("template.xlsx").inputStream(), data)
        File("output.xlsx").writeBytes(bytes)
    }
}

Result

4 rows of data expanded into 4 rows. If there were formulas (like =SUM()), their ranges would be automatically adjusted.

Step 4. DataProvider - Structured Data Delivery

You can simply use a Map, but for advanced features like images or lazy loading, it's recommended to use DataProvider.

Kotlin DSL

import io.github.jogakdal.tbeg.simpleDataProvider

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

    // Collection (eager loading)
    items("employees", employeeList)

    // Collection (lazy loading) - called when data is needed
    items("bigData") {
        repository.streamAll().iterator()
    }

    // Images
    image("logo", logoBytes)
    imageUrl("banner", "https://example.com/banner.png")
}

ExcelGenerator().use { generator ->
    val bytes = generator.generate(template, provider)
}

Java Builder

import io.github.jogakdal.tbeg.SimpleDataProvider;

SimpleDataProvider provider = SimpleDataProvider.builder()
    .value("title", "Employee Status")
    .value("date", LocalDate.now().toString())
    .items("employees", employeeList)
    .itemsFromSupplier("bigData", () -> repository.streamAll().iterator())
    .image("logo", logoBytes)
    .build();

Approach	Advantages	Best For
Map	Simple, less code	Small data, simple reports
DataProvider	Lazy loading, images, metadata	Large data, images, production

In production, you'll almost always use DataProvider.

Step 5. Image Insertion

Place a ${image(logo)} marker in the template and an image will be inserted at that cell position.

val provider = simpleDataProvider {
    value("company", "TBEG Inc.")

    // From file
    image("logo", File("logo.png").readBytes())

    // From URL (automatically downloaded during rendering)
    imageUrl("banner", "https://example.com/banner.png")
}

Images can be provided as byte arrays or URLs. The URL approach requires no separate download code - it's handled automatically during rendering.

Step 6. File Saving Options

Instead of getting a byte array, you can save directly to a file.

ExcelGenerator().use { generator ->
    val path = generator.generateToFile(
        template = template,
        dataProvider = SimpleDataProvider.of(data),
        outputDir = Path.of("./output"),
        baseFileName = "report"
    )
    // Result: ./output/report_20260115_143052.xlsx
}

A timestamp is automatically appended to prevent filename collisions.

Putting It All Together

1. Design a template in Excel (freely use formatting, charts, formulas, etc.)
2. Place ${variableName} markers where data should go
3. Use ${repeat(collection, range, variable)} for list data
4. Pass only the data from code
5. TBEG combines them → final Excel generated

Formatting, charts, conditional formatting, and formulas are all managed in the template.

Your code focuses solely on data binding.

Next Up

In the next post, we'll master TBEG's template syntax. We'll cover the various options of repeat (DOWN/RIGHT directions, nested repeats), automatic cell merge (merge), bundle (bundle), empty collection handling, and more.

[query-inspector] A Claude Code skill for extracting and tuning SQL/ORM queries

Yongho Hwang — Tue, 06 Oct 2026 07:13:37 +0000

query-inspector is a Claude Code skill that extracts the SQL/ORM queries from your project's source, then diagnoses and tunes missing indexes / N+1 problems / anti-patterns.

Queries that run fine until the data piles up. N+1 problems that sail through code review. The real SQL your ORM generates, invisible in the code. It catches all of this right before you commit, not after it reaches production.

Common pitfalls

A query shipped without an index suddenly turns into a full scan once there are tens of thousands of rows.
An N+1 problem slips through code review, and a single list API fires dozens of queries.
You can't tell from the code alone what SQL JPA or QueryDSL will actually generate.
Code review looks at logic, not "the queries this change will produce" from a SQL angle.
QA rarely catches it either, because its test data is too small for a missing index or an N+1 to surface as a slowdown.

These problems share one trait: they surface only after you ship.

query-inspector moves that moment to just before the commit.

Overview

It's a Claude Code plugin, made of two skills:

tuning-report - extracts the SQL/ORM queries in your git changes, diagnoses missing indexes / N+1s / anti-patterns, and produces a report with fixes you can apply right away.
inventory-report - catalogs the queries your project runs, down to the full query body and index coverage.

Neither one touches your code; they only produce reports.

Run it right before a commit, and it surfaces performance problems that would otherwise appear only after you ship.

Highlights

There were already ways to check query performance: runtime SQL logging (p6spy, Hibernate statistics) or APM. But those surface a problem only after the query runs.

query-inspector checks before anything runs, on the changes you're about to commit. Inferring the SQL an ORM will generate without executing it is an approach that only became practical with recent advances in AI.

Only your changes, in your commit flow

It analyzes only what changed since your last tuning, not the whole project every time. That keeps it fast and lets it sit naturally in your usual git add -> run query-inspector -> commit flow.

A full-project scan is available too.

SQL the code doesn't directly reveal

Explicit SQL is taken as-is, and for JPA derived methods / @Query / QueryDSL / Kotlin JDSL / MyBatis dynamic queries it infers the SQL that will actually be generated, then analyzes or catalogs it.

Every query carries an EXACT / INFERRED / AMBIGUOUS confidence label, so you know how far to trust each one.

From static analysis to a live EXPLAIN - as deep as you want

Even without a database it catches most problems (missing indexes / N+1s / SQL-dialect mismatches / anti-patterns).

It connects to your development database and runs a real EXPLAIN only when you want more certainty.

For safety, production hosts are blocked by default, and only read-only queries run.

Reports you can act on immediately

It doesn't stop at "this might be slow."

You get a CREATE INDEX statement with the right column order, the exact file:line, and copy-ready Action Items.

A developer, or the AI assisting that developer, can read the report and start fixing right away.

Follow-up on past suggestions

It compares the previous run's suggestions against your current code and flags the ones still not applied.

Even files you didn't touch this run are included in the check.

JVM-first stacks, localized reports

The current version centers on Kotlin/Java (JPA, Hibernate, QueryDSL, MyBatis, native SQL), with Python (Django/SQLAlchemy) as an extension. 1.1.0 validated the Python side against real open-source projects to raise its coverage.

More stacks are planned.

SQL dialects (MySQL/MariaDB, PostgreSQL) are auto-detected.

Reports come out in the language you're chatting with Claude in (you can also force a specific one).

Sample reports

A slice of what tuning-report produces after scanning your changes:

# Query Tuning Report - incremental (2 changed files)
- Severity (open): 🔴 3 / 🟡 2 / ⚪ 1   /   Follow-up: ✅ 2 resolved / ⚠️ 2 still open

## 🔴 [critical] Missing index - FK `orders.user_id` - OrderMapper.xml
- Why critical: it is the N+1 child query, so every user triggers a full scan of `orders`.
- Suggestion:  CREATE INDEX idx_orders_user_id ON orders (user_id);
- Verify (live EXPLAIN): `type: ALL -> ref`.

Point the same project at inventory-report and it lists the queries as a catalog instead of tuning:

# Query Inventory
- Queries: 24 ( SELECT 18 / INSERT 3 / UPDATE 2 / DELETE 1 )

#### [order-03] Orders for a given user
- Source: OrderMapper.xml:42 (selectOrdersByUser) / Type: SELECT
- Target: orders / Access columns: WHERE user_id, ORDER BY created_at
- Index coverage: ❌ uncovered (no index on user_id)

Use the tuning report to find what to fix, and the inventory to see everything that runs.

Getting started

Install and usage are in the GitHub README: github.com/jogakdal/query-inspector

If you have Claude Code, you can install it with these two commands:

claude plugin marketplace add jogakdal/query-inspector
claude plugin install query-inspector@query-inspector-marketplace

Want to see the output first? Browse the sample report and the Docker example in the repo.

It's still v1.0.0. After trying it, please file feedback via GitHub issues and it'll go into the next iteration; a star is welcome if you find it useful.

Run it before your next commit.

Stop writing Excel reports cell by cell - bind data to templates instead (Kotlin/Java)

Yongho Hwang — Tue, 06 Oct 2026 06:43:47 +0000

Still crafting Excel reports cell by cell with POI?

If you've ever built Excel reports in a JVM environment, you know the pain of Apache POI.

// Using Apache POI directly...
val workbook = XSSFWorkbook()
val sheet = workbook.createSheet("Employee Status")
val headerRow = sheet.createRow(0)
headerRow.createCell(0).setCellValue("Name")
headerRow.createCell(1).setCellValue("Position")
headerRow.createCell(2).setCellValue("Salary")

employees.forEachIndexed { index, emp ->
    val row = sheet.createRow(index + 1)
    row.createCell(0).setCellValue(emp.name)
    row.createCell(1).setCellValue(emp.position)
    row.createCell(2).setCellValue(emp.salary.toDouble())
}

// Column widths, styles, formulas, charts... it never ends

One header, one cell, one style at a time... hundreds of lines for a single report.

Add charts, conditional formatting, formulas, and images on top? The code spirals out of control.

TBEG (Template Based Excel Generator) solves this at its root - design the report in Excel, then just bind your data.

What is TBEG?

A JVM library that generates reports by binding data to Excel templates.

The core idea is simple:

A designer (or you) designs the report layout directly in Excel
Place ${variableName} markers where data should go
In your code, just pass the data
TBEG combines template + data to produce the final report

// Using TBEG - this is all you need to do
val data = mapOf(
    "title" to "Employee Status",
    "employees" to employeeList
)

ExcelGenerator().use { generator ->
    val bytes = generator.generate(template, data)
    File("output.xlsx").writeBytes(bytes)
}

Formatting, charts, formulas, and conditional formatting are all managed in the template. Your code focuses solely on data binding.

Design Philosophy

We don't reinvent what Excel already provides - we use it as-is.

Aggregation with Excel's =SUM() formula, averages with =AVERAGE()
Conditional highlighting with conditional formatting
Visualization with charts

Use the familiar Excel features as they are.

TBEG adds dynamic data binding on top, and automatically adjusts these features to work as intended even when data expands.

At a Glance

Template

Code

val data = simpleDataProvider {
    value("reportTitle", "Q1 2026 Sales Performance Report")
    value("period", "Jan 2026 ~ Mar 2026")
    value("author", "Yongho Hwang")
    value("reportDate", LocalDate.now().toString())
    image("logo", logoBytes)
    imageUrl("ci", "https://example.com/ci.png")  // URL supported
    items("depts") { deptList.iterator() }
    items("products") { productList.iterator() }
    items("employees") { employeeList.iterator() }
}

ExcelGenerator().use { generator ->
    generator.generateToFile(template, data, outputDir, "quarterly_report")
}

Result

Variable substitution, image insertion, repeat data expansion, automatic cell merge, formula range adjustment, conditional formatting duplication, and chart data reflection - TBEG handles all of this automatically.

Key Features

Feature	Description
Template-based generation	Generate reports by binding data to Excel templates
Repeat data processing	Expand list data into rows/columns with ${repeat(...)} syntax
Variable substitution	Bind values to cells, charts, shapes, headers/footers, formula arguments.
Image insertion	Insert dynamic images via byte arrays or URLs
Automatic cell merge	Auto-merge consecutive cells with the same value in repeat data
Bundle	Group multiple elements into a single unit that moves together
Selective field visibility	Restrict visibility of specific fields based on conditions. Choose between DELETE or DIM modes
Formula auto-adjustment	Automatically update formula ranges (SUM, AVERAGE, etc.) on data expansion
Automatic conditional formatting	Automatically apply original conditional formatting to repeated rows
Automatic data validation expansion	Automatically apply original data validation (dropdowns, etc.) to repeated rows
Chart/pivot table auto-reflection	Automatically reflect expanded data ranges in charts. Pivot table source range auto-adjustment
File encryption	Set open password for generated Excel files
Document metadata	Set document properties such as title, author, keywords, etc.
Large-scale processing	Reliably process over 1 million rows with low CPU utilization
Asynchronous processing	Process large data in the background
Lazy loading	Memory-efficient data processing via DataProvider
Spring Boot support	Easy integration with auto-configuration

Template Syntax Preview

Syntax	Description	Example
${variableName}	Variable substitution	${title}
${object.field}	Repeat item field	${emp.name}
${repeat(collection, range, object)}	Repeat processing	${repeat(items, A2:C2, item)}
${image(name)}	Image insertion	${image(logo)}
${merge(object.field)}	Automatic cell merge	${merge(emp.dept)}
${bundle(range)}	Bundle	${bundle(A5:H12)}
${hideable(object.field, range, mode)}	Selective field visibility	${hideable(emp.salary, C1:C3, DIM)}

Performance

TBEG internally generates data using streaming, producing 1 million rows in about 3.2 seconds while using around 9% of system CPU. Both rendering and post-processing operate in streaming mode, using a constant memory buffer regardless of data size.

Data Size	Time	CPU Usage
1,000 rows	13ms	25.0%
10,000 rows	47ms	17.4%
30,000 rows	117ms	13.8%
100,000 rows	354ms	11.6%
1,000,000 rows	3,203ms	9.0%

Comparison with Other Libraries (100,000 rows)

Library	Time
TBEG	0.35s
JXLS (STREAMING_ON)	0.73s
(ref.) JXLS (STREAMING_OFF)	2.63s

The direct comparison target is JXLS STREAMING_ON, which shares the same output conditions. STREAMING_OFF (full in-memory mode) is a reference figure.

Most Common Use Case: Excel Download API

In practice, TBEG is most frequently used for Excel download features in web applications.

@GetMapping("/api/reports/download")
fun downloadReport(response: HttpServletResponse) {
    val data = reportService.getReportData()
    val template = resourceLoader.getResource("classpath:templates/report.xlsx")

    response.contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    response.setHeader("Content-Disposition", "attachment; filename=report.xlsx")

    excelGenerator.generate(template.inputStream, data)
        .let { response.outputStream.write(it) }
}

Admin page list exports, settlement report downloads, monthly report generation - set up one template and this is all the code you need.

When to Use TBEG

✅ Generating standardized reports/statements
✅ Filling data into Excel forms provided by a designer
✅ Reports requiring complex formatting (conditional formatting, charts)
✅ Processing large datasets with tens to hundreds of thousands of rows
✅ Tables where the number of rows or columns changes with the data
✅ Implementing Excel download APIs in Spring Boot environments
❌ Reading/parsing Excel files (TBEG is generation-only)

Getting Started

Add Dependency

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

// Gradle (Groovy DSL)
dependencies {
    implementation 'io.github.jogakdal:tbeg:1.2.5'
}

<!-- Maven -->
<dependency>
    <groupId>io.github.jogakdal</groupId>
    <artifactId>tbeg</artifactId>
    <version>1.2.5</version>
</dependency>

The version above is as of this writing. Check the latest version on Maven Central.

Quick Start (Kotlin)

import io.github.jogakdal.tbeg.ExcelGenerator
import java.io.File

data class Employee(val name: String, val position: String, val salary: Int)

fun main() {
    val data = mapOf(
        "title" to "Employee Status",
        "employees" to listOf(
            Employee("Yongho Hwang", "Director", 8000),
            Employee("Yongho Han", "Manager", 6500)
        )
    )

    ExcelGenerator().use { generator ->
        val template = File("template.xlsx").inputStream()
        val bytes = generator.generate(template, data)
        File("output.xlsx").writeBytes(bytes)
    }
}

Quick Start (Java)

import io.github.jogakdal.tbeg.ExcelGenerator;
import java.io.*;
import java.nio.file.*;
import java.util.*;

public class Example {
    public static void main(String[] args) throws Exception {
        var data = Map.<String, Object>of(
            "title", "Employee Status",
            "employees", List.of(
                Map.of("name", "Yongho Hwang", "position", "Director", "salary", 8000),
                Map.of("name", "Yongho Han", "position", "Manager", "salary", 6500)
            )
        );

        try (var generator = new ExcelGenerator()) {
            byte[] bytes = generator.generate(
                new FileInputStream("template.xlsx"), data);
            Files.write(Path.of("output.xlsx"), bytes);
        }
    }
}

Learn More

This is the first post in the TBEG series. In the next post, we'll walk through creating a template and binding data step by step.

GitHub: jogakdal/data-processors-with-excel
Maven Central: tbeg

📖 Documentation

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

DEV Community: Yongho Hwang

Excel report generation in Java and Kotlin: Apache POI vs JXLS vs TBEG

Two Approaches

Apache POI, Written Directly

JXLS, Commands in Cell Comments

TBEG, Markers in Cell Text

Where the Three Diverge - The Location of Commands

Feature Comparison - Shared Support and Divergent Items

Large Data and Streaming

Performance Measured Under Identical Conditions

Selection Criteria

References

TBEG tips and troubleshooting: template design, performance, and production (Kotlin/Java)

What This Post Covers

1. Template Design Tips

Place repeat markers outside the repeat range

Place formulas below the data area

One row per data item as a rule

Use hideable markers for fields that might need hiding

Avoid overlapping repeat regions

2. Performance Optimization - 3-Step Guide

Step 1: Provide count

Step 2: Lazy loading

Step 3: DB streaming

Recommended Settings by Data Size

3. Common Errors and Solutions

Markers remain in the output file

TemplateProcessingException

OutOfMemoryError

Merge results are not as expected

4. Hideable Issues

hideFields specified but fields are not hidden

Only data area is hidden, titles/totals remain

Bundle range doesn't match the cell

5. Production Guide

Memory Sizing

Concurrent Request Handling

Dev/Prod Environment Separation

JPA Stream Caution

Wrap-up

TBEG advanced features: formulas, images, and large-scale streaming (Kotlin/Java)

Formula Integration

Binding Variables in Excel Formulas

Hyperlinks

Automatic Formula Range Adjustment

Image Insertion

Local Files

URL Auto-Download

Automatic Cell Merge

How It Works

Bundle (Element Grouping)

Example

Template (bundle_template.xlsx)

Code

With bundle vs Without bundle

Selective Field Visibility (hideable)

How It Works

DELETE vs DIM Mode

Hiding Multiple Fields at Once

Bundle Range for Header+Data Hiding

Multi-Sheet

Summary + Detail Pattern

Asynchronous Processing

Kotlin Coroutines

Java CompletableFuture

Background Job + Listener

Large-Scale Data Processing

Streaming Mode

Performance Benchmark

Comparison with Other Libraries (100,000 rows)

Comprehensive Example: Quarterly Sales Report

Template

Code

Result

Wrap-Up

Links

Detailed Docs

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

Setup

Adding the Dependency

`TemplateProcessingException`

`OutOfMemoryError`