# XLFill - Complete Documentation
> A Go library for template-first Excel report generation. Design in Excel, fill with Go.
> This file contains the complete documentation content for LLM consumption.
---
## Overview
XLFill is a Go library that revolutionizes Excel report generation by using a template-first approach. Instead of writing verbose code to style cells, merge ranges, and format data, you design your report visually in Excel and let XLFill fill it with data.
### Key Advantages
- **Visual Design**: Create templates in Excel, Google Sheets, or LibreOffice
- **Zero Styling Code**: All formatting is preserved from the template
- **Business User Friendly**: Non-developers can update templates
- **Formula Support**: Excel formulas are automatically expanded
- **Rich Features**: Loops, conditionals, grids, images, merged cells
- **Extensible**: Create custom commands for specific needs
### Installation
```bash
go get github.com/javajack/xlfill
```
### Basic Usage
```go
package main
import "github.com/javajack/xlfill"
func main() {
data := map[string]interface{}{
"employees": []Employee{
{Name: "John", Department: "Sales", Salary: 50000},
{Name: "Jane", Department: "Engineering", Salary: 75000},
},
}
err := xlfill.Fill("template.xlsx", "output.xlsx", data)
if err != nil {
panic(err)
}
}
```
---
## Complete Documentation
### XLFill
**Description**: A Go library for template-first Excel report generation. Design in Excel, fill with Go.
**URL**: https://javajack.github.io/xlfill/unknown
import { Card, CardGrid } from '@astrojs/starlight/components';
import { Image } from 'astro:assets';
import templateImg from '../../assets/images/each-1.png';
import outputImg from '../../assets/images/each-2.png';
## The idea? Beautifully simple.
You know that feeling when you open Excel and just *make* a report look right? The fonts, the colors, the borders — it takes a few minutes and it looks professional. Now imagine dropping a Go library into your project that fills that template with live data. That's XLFill.
**Here's a template you'd design:**
**And here's what XLFill produces:**
Same fonts. Same colors. Same borders. You didn't write a single line of code for any of that styling. The template *is* the design.
---
## Four steps. That's the whole workflow.
Open any spreadsheet editor — Excel, Google Sheets, LibreOffice, whatever you prefer. Make your report look exactly right. Headers, colors, borders, number formats, logos. Save as `.xlsx`.
Put `${employee.Name}` in cells where data should go. Add a `jx:each(...)` command in a cell comment to define a loop. That's it — two things to learn.
One function call: `xlfill.Fill("template.xlsx", "output.xlsx", data)`. Your data flows into the template. Every pixel of formatting — preserved.
Your users get a real `.xlsx` file. Opens in Excel, Google Sheets, LibreOffice. Not a CSV. Not an HTML table someone has to squint at. A proper, beautiful spreadsheet.
---
## The old way vs. XLFill
If you've ever built Excel reports in Go (or any language, really), this table will feel painfully familiar:
| | The old way (cell-by-cell code) | With XLFill (template-first) |
|---|---|---|
| **How you design** | Line by line in Go code | Visually in Excel — like a normal person |
| **Changing the layout** | Edit code, rebuild, redeploy, pray | Edit the `.xlsx` file. Done. |
| **Who can update it?** | Only developers | Anyone who knows Excel |
| **Styling & formatting** | 20 lines of verbose struct literals per style | It's already in the template |
| **Formulas** | Build formula strings by hand | Write them in Excel — XLFill expands them |
| **Merged cells** | Code the exact ranges | Just merge them in Excel |
| **Conditional formatting** | Most libraries don't even support it | It's in the template — just works |
| **Code complexity** | Grows with every column, every section | Stays tiny — your code only provides data |
| **When something breaks** | Good luck debugging `fmt.Sprintf("A%d", row+2)` | Open the template. Look at it. |
---
## Think about it this way
> *"I used to spend 200 lines of Go code wrestling with cell styles, merging headers, and adjusting column widths. Then one day our finance team redesigned the whole report. I had to rewrite everything. Now? My business analyst designs the template in Excel, and my Go code is 10 lines. When they want changes, they just update the template. I don't even hear about it."*
That's the shift. The template becomes the single source of truth for how the report looks. Your code becomes the single source of truth for the data. Nobody steps on each other's toes.
---
## Curious? Two ways to dive in.
Read [Why XLFill?](/xlfill/guides/why-xlfill/) — a detailed breakdown with real Go code showing 40+ lines of hard-coded pain reduced to 3 lines.
[Get started](/xlfill/guides/getting-started/) — install, build a template, fill it with data. Five minutes, start to finish.
---
### jx:each
**Description**: Loop over a collection, repeating a template area for each item.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import template1 from '../../../assets/images/each-1.png';
import output1 from '../../../assets/images/each-2.png';
import rightTemplate from '../../../assets/images/each-right-1.png';
import rightOutput from '../../../assets/images/each-right-2.png';
import varindexTemplate from '../../../assets/images/each-varindex-1.png';
import varindexOutput from '../../../assets/images/each-varindex-2.png';
import orderbyTemplate from '../../../assets/images/each-orderby-1.png';
import orderbyOutput from '../../../assets/images/each-orderby-2.png';
import selectTemplate from '../../../assets/images/each-select-1.png';
import selectOutput from '../../../assets/images/each-select-2.png';
import groupbyTemplate from '../../../assets/images/each-groupby-asc-1.png';
import groupbyOutput from '../../../assets/images/each-groupby-asc-2.png';
import multisheetTemplate from '../../../assets/images/each-multisheet-1.png';
import multisheetOutput from '../../../assets/images/each-multisheet-2.png';
import multisheetOutput2 from '../../../assets/images/each-multisheet-3.png';
`jx:each` is the workhorse command. It loops over a collection and repeats its template area for each item — like a `for` loop, but in your spreadsheet.
## Syntax
```
jx:each(items="employees" var="e" lastCell="C1")
```
## Attributes
| Attribute | Description | Default | Required |
|-----------|-------------|---------|----------|
| `items` | Expression for the collection to iterate | — | Yes |
| `var` | Loop variable name | — | Yes |
| `lastCell` | Bottom-right cell of the repeating area | — | Yes |
| `varIndex` | Variable name for the 0-based index | — | No |
| `direction` | `DOWN` or `RIGHT` | `DOWN` | No |
| `select` | Filter expression (must return bool) | — | No |
| `orderBy` | Sort spec: `"e.Name ASC, e.Age DESC"` | — | No |
| `groupBy` | Property to group by | — | No |
| `groupOrder` | Group sort: `ASC` or `DESC` | `ASC` | No |
| `multisheet` | Variable with sheet names (one sheet per item) | — | No |
## Basic loop
The simplest and most common use case — repeat a row for each item:
**Template:**
**Output:**
The comment on the top-left cell:
```
jx:area(lastCell="C2")
jx:each(items="employees" var="e" lastCell="C2")
```
Each employee produces one row. The header stays, the data row repeats, all formatting carries over.
## Iteration index
Need a row number? Use `varIndex`:
```
jx:each(items="employees" var="e" varIndex="i" lastCell="D1")
```
**Template:**
**Output:**
The index `i` is 0-based (0, 1, 2, ...).
## Expand RIGHT instead of DOWN
By default, rows expand downward. Set `direction="RIGHT"` to expand across columns instead:
```
jx:each(items="months" var="m" direction="RIGHT" lastCell="A2")
```
**Template:**
**Output:**
Great for time series, calendar layouts, or cross-tab reports.
## Filtering with select
Only include items that match a condition:
```
jx:each(items="employees" var="e" select="e.payment > 2000" lastCell="C1")
```
**Template:**
**Output:**
The `select` expression must return a boolean. Only items where it evaluates to `true` appear in the output.
## Sorting with orderBy
Sort items before looping:
```
jx:each(items="employees" var="e" orderBy="e.name ASC" lastCell="C1")
```
**Template:**
**Output:**
Multiple sort keys:
```
jx:each(items="employees" var="e" orderBy="e.department ASC, e.name DESC" lastCell="C1")
```
## Grouping with groupBy
Group items by a property. Each group becomes a `GroupData` object with `Item` (the key) and `Items` (the group members):
```
jx:each(items="employees" var="g" groupBy="department" groupOrder="ASC" lastCell="C5")
```
**Template:**
**Output:**
Inside the loop, `g.Item` is the group key (e.g., `"Engineering"`) and `g.Items` is the slice of items in that group. Nest another `jx:each` inside to iterate over `g.Items`.
## Multisheet mode
Generate **
[Content truncated - see full documentation at URL above]
---
### jx:grid
**Description**: Fill a dynamic grid with headers and data rows.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import gridTemplate from '../../../assets/images/grid-1.png';
import gridOutput from '../../../assets/images/grid-2.png';
`jx:grid` fills a rectangular area with header values across columns and data rows downward. Perfect for pivot-style reports, dynamic-column tables, or any report where the columns aren't known at template design time.
## Syntax
```
jx:grid(headers="headerList" data="dataRows" lastCell="A1")
```
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `headers` | Expression for header values (1D slice) | Yes |
| `data` | Expression for data rows (2D slice) | Yes |
| `lastCell` | Bottom-right cell of the grid area | Yes |
## Example
**Template:**
**Output:**
Go code:
```go
data := map[string]any{
"headers": []string{"Q1", "Q2", "Q3", "Q4"},
"dataRows": [][]any{
{100, 200, 150, 180},
{90, 110, 130, 160},
},
}
```
Headers expand to the right. Data rows expand downward. The template cell's formatting is applied to every generated cell.
## When to use grid vs. each
- Use **`jx:each`** when your columns are fixed and known at template design time (the common case)
- Use **`jx:grid`** when the number of columns is dynamic — the data determines how many columns appear
## Try it
Download the runnable example: **template** [t09.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t09.xlsx) | **output** [09_grid.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/09_grid.xlsx) | [code snippet](/xlfill/reference/examples/#09-dynamic-grid)
## Next command
Need to insert images into your report?
**[jx:image →](/xlfill/commands/image/)**
---
### jx:if
**Description**: Conditionally include or exclude a template area.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import ifTemplate from '../../../assets/images/if-1.png';
import ifOutput from '../../../assets/images/if-2.png';
import ifNoElseTemplate from '../../../assets/images/if-3.png';
import ifNoElseOutput from '../../../assets/images/if-4.png';
`jx:if` conditionally includes or excludes a template area based on a boolean expression. Think of it as an `if` statement for your spreadsheet layout.
## Syntax
```
jx:if(condition="e.Active" lastCell="C1")
```
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `condition` | Boolean expression | Yes |
| `lastCell` | Bottom-right cell of the conditional area | Yes |
| `ifArea` | Area ref to render when true (advanced) | No |
| `elseArea` | Area ref to render when false (advanced) | No |
## Basic usage
When the condition is `true`, the area is rendered. When `false`, it's omitted and subsequent rows shift up to fill the gap.
**Template:**
**Output (condition true):**
## If without else
The most common pattern — show something only when a condition is met:
**Template:**
**Output:**
When the condition is false, the area simply disappears and rows below shift up.
## Inside a loop
`jx:if` is powerful when combined with `jx:each`. Only show rows for items matching a condition:
```
Cell A1 comment:
jx:area(lastCell="C2")
jx:each(items="employees" var="e" lastCell="C2")
Cell A2 comment:
jx:if(condition="e.Active" lastCell="C2")
```
Only active employees appear in the output.
## Complex conditions
Any expression returning a boolean works:
```
jx:if(condition="e.Age >= 18 && e.Department == 'Engineering'" lastCell="C1")
```
```
jx:if(condition="len(items) > 0" lastCell="D5")
```
## If/Else areas
For more advanced control, render different template regions depending on the condition:
```
jx:if(condition="hasData" ifArea="A2:C5" elseArea="A7:C8" lastCell="C8")
```
When `hasData` is true, the A2:C5 region is rendered. When false, A7:C8 is rendered instead. This lets you show a data table or a "no results" message from the same template.
## Try it
Download the runnable example: **template** [t07.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t07.xlsx) | **output** [07_if_command.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/07_if_command.xlsx) | [code snippet](/xlfill/reference/examples/#07-conditional)
## Next command
Need to fill a dynamic grid with headers and data rows?
**[jx:grid →](/xlfill/commands/grid/)**
---
### jx:area
**Description**: Define the working region of an XLFill template.
**URL**: https://javajack.github.io/xlfill/unknown
The `jx:area` command marks the rectangular region of your spreadsheet that XLFill will process. It's the first command you'll put on every template.
## Syntax
```
jx:area(lastCell="D10")
```
Place this in a **cell comment** on the top-left cell of your template region. The `lastCell` attribute is the bottom-right corner.
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `lastCell` | Bottom-right cell of the template area | Yes |
## Why it exists
XLFill needs to know which part of your spreadsheet is the template and which part is static content. The `jx:area` command draws that boundary.
- Everything **inside** the area is processed — expressions are evaluated, commands are executed
- Everything **outside** the area is left untouched in the output
This means you can have headers, footers, or instructions outside the area that won't be affected.
## Typical usage
You almost always combine `jx:area` with another command in the same cell comment:
```
jx:area(lastCell="D5")
jx:each(items="employees" var="e" lastCell="D1")
```
This says: *"The template region is A1:D5. Within that region, loop over `employees`."*
## Tips
- Only one `jx:area` per template sheet
- The area defines what gets **processed**, not what gets output — a `jx:each` inside may expand beyond the original area boundaries
- If your template has a header row above the repeating row, include the header in the area so it appears in the output
## Try it
Every example in the test suite uses `jx:area`. Browse all 19 runnable examples with input templates and filled outputs on the [Examples](/xlfill/reference/examples/) page.
## Next command
The command you'll pair with `jx:area` on almost every template:
**[jx:each →](/xlfill/commands/each/)**
---
### jx:mergeCells
**Description**: Merge cells in a specified range.
**URL**: https://javajack.github.io/xlfill/unknown
`jx:mergeCells` merges cells in a rectangular range during template processing. This is essential when you need section headers that span multiple columns inside a loop.
## Syntax
```
jx:mergeCells(lastCell="C1" cols="3" rows="1")
```
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `lastCell` | Bottom-right cell of the command area | Yes |
| `cols` | Number of columns to merge | Yes |
| `rows` | Number of rows to merge | Yes |
## Why you need this
You can merge cells in your template file directly — and for static headers, you should. But when merges happen **inside a loop** (like a department header that spans 3 columns, repeated for each department), you need `jx:mergeCells` because the merge positions change with each iteration.
## Example
A department report with a merged header per department:
```
Cell A1 comment:
jx:area(lastCell="C5")
jx:each(items="departments" var="dept" lastCell="C5")
Cell A1 also has:
jx:mergeCells(lastCell="C1" cols="3" rows="1")
```
Cell A1 value: `${dept.Name}`
For each department, the department name cell spans columns A through C, creating a clean section header.
## Try it
Download the runnable example: **template** [t11.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t11.xlsx) | **output** [11_mergecells.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/11_mergecells.xlsx) | [code snippet](/xlfill/reference/examples/#11-merge-cells)
## Next command
Need to set a summary or total cell?
**[jx:updateCell →](/xlfill/commands/updatecell/)**
---
### Commands Overview
**Description**: How commands work in XLFill templates and what each one does.
**URL**: https://javajack.github.io/xlfill/unknown
Commands are the structural directives in your template. They go in **cell comments** and control how XLFill processes regions of your spreadsheet — looping, branching, inserting images, merging cells, and more.
## How commands work
Every command follows this pattern:
```
jx:commandName(attr1="value1" attr2="value2" lastCell="ref")
```
- The command name follows `jx:`
- Attributes are `key="value"` pairs
- Most commands require `lastCell` — the bottom-right corner of the command's area
- The cell containing the comment is the top-left corner
### Multiple commands per cell
Put multiple commands in the same cell comment, separated by newlines:
```
jx:area(lastCell="D10")
jx:each(items="employees" var="e" lastCell="D1")
```
### Automatic nesting
Commands whose areas are strictly inside another command's area are automatically treated as children. You don't need to declare nesting explicitly — XLFill figures it out from the geometry.
## The commands
Here's every command available, in order of how often you'll use them:
### Core (you'll use these on every template)
| Command | What it does | Page |
|---------|-------------|------|
| **[jx:area](/xlfill/commands/area/)** | Defines the template working region. Required on every template. | [Details →](/xlfill/commands/area/) |
| **[jx:each](/xlfill/commands/each/)** | Loops over a collection, repeating rows or columns for each item. The workhorse command. | [Details →](/xlfill/commands/each/) |
| **[jx:if](/xlfill/commands/if/)** | Conditionally shows or hides a template area. | [Details →](/xlfill/commands/if/) |
### Specialized (use when you need them)
| Command | What it does | Page |
|---------|-------------|------|
| **[jx:grid](/xlfill/commands/grid/)** | Fills a dynamic grid with headers and data rows. Great for pivot-style reports. | [Details →](/xlfill/commands/grid/) |
| **[jx:image](/xlfill/commands/image/)** | Inserts an image from byte data. Photos, logos, charts. | [Details →](/xlfill/commands/image/) |
| **[jx:mergeCells](/xlfill/commands/mergecells/)** | Merges cells in a range. Useful for section headers in loops. | [Details →](/xlfill/commands/mergecells/) |
| **[jx:updateCell](/xlfill/commands/updatecell/)** | Sets a single cell's value from an expression. For totals and summaries. | [Details →](/xlfill/commands/updatecell/) |
| **[jx:autoRowHeight](/xlfill/commands/autorowheight/)** | Auto-fits row height after content is written. For cells with wrapped text. | [Details →](/xlfill/commands/autorowheight/) |
## A typical template uses 2-3 commands
Don't be overwhelmed by the list. Most real-world templates use just `jx:area` + `jx:each`, and occasionally `jx:if`. The specialized commands are there when you need them, but you can build powerful reports with just the basics.
## What's next?
Start with the most important command — the one you'll use on every template:
**[jx:area →](/xlfill/commands/area/)**
Or jump straight to the loop command that does most of the heavy lifting:
**[jx:each →](/xlfill/commands/each/)**
---
### jx:autoRowHeight
**Description**: Auto-fit row height after content is written.
**URL**: https://javajack.github.io/xlfill/unknown
`jx:autoRowHeight` adjusts the row height after content is written to fit wrapped text. Without this, rows with long text content may appear truncated.
## Syntax
```
jx:autoRowHeight(lastCell="C1")
```
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `lastCell` | Bottom-right cell of the command area | Yes |
## When to use this
Use it when your template cells have **word wrap enabled** and the data may contain text of varying lengths. Without auto-fitting, Excel uses the row height from the template, which may be too short for longer content.
## Example
Template cell A1 comment:
```
jx:autoRowHeight(lastCell="C1")
```
After XLFill writes the cell content, it recalculates the row height so all wrapped text is visible.
## Inside loops
Combine with `jx:each` to auto-fit every generated row:
```
Cell A1 comment:
jx:area(lastCell="C1")
jx:each(items="items" var="e" lastCell="C1")
Cell A1 also has:
jx:autoRowHeight(lastCell="C1")
```
Each row gets its height adjusted based on its content.
---
That's every command in XLFill. You now know the full template language. For most reports, you'll use `jx:area` + `jx:each` and occasionally `jx:if`. The rest are there when you need them.
## Try it
Download the runnable example: **template** [t17.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t17.xlsx) | **output** [17_autorowheight.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/17_autorowheight.xlsx) | [code snippet](/xlfill/reference/examples/#17-auto-row-height)
## What's next?
Learn how formulas in your templates are automatically expanded when rows are inserted:
**[Formulas →](/xlfill/guides/formulas/)**
---
### jx:updateCell
**Description**: Update a single cell's value using an expression.
**URL**: https://javajack.github.io/xlfill/unknown
`jx:updateCell` replaces a single cell's value with the result of an expression. Use it for summary cells, totals, timestamps, or any standalone computed value.
## Syntax
```
jx:updateCell(lastCell="A1" updater="totalAmount")
```
## Attributes
| Attribute | Description | Required |
|-----------|-------------|----------|
| `lastCell` | The cell to update (typically same as the command cell) | Yes |
| `updater` | Expression whose result becomes the cell value | Yes |
## Example
```go
data := map[string]any{
"employees": employees,
"totalAmount": 15750.50,
"reportDate": "2024-01-15",
}
```
Template cell A10 comment:
```
jx:updateCell(lastCell="A10" updater="totalAmount")
```
Cell A10 in the template can contain any placeholder text — it gets replaced with `15750.50`.
## When to use this vs. expressions
Regular `${...}` expressions work for most cases. `jx:updateCell` is useful when:
- The cell is outside a loop and you want to set it from a specific context variable
- You need the update to happen after other commands have run (it respects command ordering)
## Try it
Browse all 19 runnable examples with input templates and filled outputs on the [Examples](/xlfill/reference/examples/) page.
## Next command
Auto-fit row heights for cells with wrapped text:
**[jx:autoRowHeight →](/xlfill/commands/autorowheight/)**
---
### jx:image
**Description**: Insert an image into the spreadsheet from byte data.
**URL**: https://javajack.github.io/xlfill/unknown
`jx:image` inserts an image from byte data into the specified cell area. Use it for employee photos, company logos, product images, chart screenshots, or any visual content.
## Syntax
```
jx:image(src="employee.Photo" imageType="PNG" lastCell="C5")
```
## Attributes
| Attribute | Description | Default | Required |
|-----------|-------------|---------|----------|
| `src` | Expression for image bytes (`[]byte`) | — | Yes |
| `imageType` | Format: `PNG`, `JPEG`, `GIF`, etc. | — | Yes |
| `lastCell` | Bottom-right cell defining the image area | — | Yes |
| `scaleX` | Horizontal scale factor | 1.0 | No |
| `scaleY` | Vertical scale factor | 1.0 | No |
## Example
```go
logoBytes, _ := os.ReadFile("logo.png")
data := map[string]any{
"company": map[string]any{
"Name": "Acme Corp",
"Logo": logoBytes,
},
}
```
Template cell A1 comment:
```
jx:image(src="company.Logo" imageType="PNG" lastCell="C3")
```
The image fills the A1:C3 area. The cell dimensions control the image size.
## Scaling
Adjust the image size relative to the cell area:
```
jx:image(src="logo" imageType="PNG" scaleX="0.5" scaleY="0.5" lastCell="B2")
```
## Inside loops
Combine with `jx:each` for a different image per row — employee photos, product thumbnails, etc.:
```
Cell A1 comment:
jx:area(lastCell="D3")
jx:each(items="employees" var="e" lastCell="D3")
Cell A1 also has:
jx:image(src="e.Photo" imageType="JPEG" lastCell="A3")
```
Each employee gets their photo in column A, with name/details in columns B-D.
## Try it
Download the runnable example: **template** [t10.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t10.xlsx) | **output** [10_image.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/10_image.xlsx) | [code snippet](/xlfill/reference/examples/#10-embed-image)
## Next command
Need to merge cells for section headers?
**[jx:mergeCells →](/xlfill/commands/mergecells/)**
---
### Custom Commands
**Description**: Extend XLFill with your own template commands.
**URL**: https://javajack.github.io/xlfill/unknown
XLFill ships with a rich set of built-in commands, but every project is different. Custom commands let you add your own `jx:` directives to handle domain-specific logic.
## The Command interface
Every command — built-in or custom — implements this interface:
```go
type Command interface {
Name() string
ApplyAt(cellRef CellRef, ctx *Context, transformer Transformer) (Size, error)
Reset()
}
```
- **`Name()`** — the command name used after `jx:` in templates
- **`ApplyAt()`** — called when XLFill processes this command. Receives the cell position, the data context, and the transformer for reading/writing cells. Returns the `Size` of the output area.
- **`Reset()`** — called before each fill operation to reset state
## Registering a custom command
Use `WithCommand` with a factory function. The factory receives the attributes from the template comment and returns a command instance:
```go
filler := xlfill.NewFiller(
xlfill.WithTemplate("template.xlsx"),
xlfill.WithCommand("highlight", func(attrs map[string]string) (xlfill.Command, error) {
return &HighlightCommand{
Color: attrs["color"],
}, nil
}),
)
```
Now you can use it in templates:
```
jx:highlight(color="yellow" lastCell="C1")
```
## Full example
A command that sets a background color on a cell range:
```go
type HighlightCommand struct {
Color string
}
func (c *HighlightCommand) Name() string { return "highlight" }
func (c *HighlightCommand) ApplyAt(
cellRef xlfill.CellRef,
ctx *xlfill.Context,
tx xlfill.Transformer,
) (xlfill.Size, error) {
// Use the transformer to read cells, apply styles, write values
// Return the size of the area produced
return xlfill.Size{Width: 3, Height: 1}, nil
}
func (c *HighlightCommand) Reset() {}
```
## Parsing attributes
The factory function receives all template attributes as `map[string]string`. Parse them as needed:
```go
xlfill.WithCommand("repeat", func(attrs map[string]string) (xlfill.Command, error) {
count, err := strconv.Atoi(attrs["count"])
if err != nil {
return nil, fmt.Errorf("invalid count: %w", err)
}
return &RepeatCommand{Count: count}, nil
})
```
Template usage: `jx:repeat(count="3" lastCell="C1")`
## What's next?
For hooking into cell processing without creating a full command:
**[Area Listeners →](/xlfill/guides/area-listeners/)**
---
### Area Listeners
**Description**: Hook into cell processing for conditional styling, logging, and validation.
**URL**: https://javajack.github.io/xlfill/unknown
Area listeners let you intercept every cell transformation — before and after — without writing a custom command. Use them for conditional row styling, audit logging, data validation, or any cross-cutting concern.
## The AreaListener interface
```go
type AreaListener interface {
BeforeTransformCell(src, target CellRef, ctx *Context, tx Transformer) bool
AfterTransformCell(src, target CellRef, ctx *Context, tx Transformer)
}
```
- **`BeforeTransformCell`** — called before each cell is processed. Return `false` to skip the default transformation (you handle it yourself). Return `true` to proceed normally.
- **`AfterTransformCell`** — called after each cell is processed. Use it for post-processing like styling.
Both receive the source cell (template position), target cell (output position), the data context, and the transformer.
## Registering a listener
```go
xlfill.Fill("template.xlsx", "output.xlsx", data,
xlfill.WithAreaListener(&MyListener{}),
)
```
## Example: Alternate row colors
```go
type AlternateRowListener struct{}
func (l *AlternateRowListener) BeforeTransformCell(
src, target xlfill.CellRef,
ctx *xlfill.Context,
tx xlfill.Transformer,
) bool {
return true // proceed with default transformation
}
func (l *AlternateRowListener) AfterTransformCell(
src, target xlfill.CellRef,
ctx *xlfill.Context,
tx xlfill.Transformer,
) {
if target.Row%2 == 0 {
// Use transformer API to apply a light background
}
}
```
## Example: Audit logging
```go
type AuditListener struct{}
func (l *AuditListener) BeforeTransformCell(
src, target xlfill.CellRef, ctx *xlfill.Context, tx xlfill.Transformer,
) bool {
log.Printf("Processing cell %s -> %s", src, target)
return true
}
func (l *AuditListener) AfterTransformCell(
src, target xlfill.CellRef, ctx *xlfill.Context, tx xlfill.Transformer,
) {}
```
## PreWrite callback
For logic that runs **after all template processing** but **before writing the output**, use `WithPreWrite`:
```go
xlfill.Fill("template.xlsx", "output.xlsx", data,
xlfill.WithPreWrite(func(tx xlfill.Transformer) error {
// Set print area, add final calculations, etc.
return nil
}),
)
```
## Template sheet control
Related options for controlling the output:
```go
// Keep the template sheet in output (default: removed)
xlfill.WithKeepTemplateSheet(true)
// Hide the template sheet instead of removing
xlfill.WithHideTemplateSheet(true)
// Don't clear unexpanded ${...} expressions (default: cleared)
xlfill.WithClearTemplateCells(false)
```
## What's next?
Area listeners are one of several debugging tools. For the complete toolkit — including `Validate()`, `Describe()`, and common troubleshooting tips:
**[Debugging & Troubleshooting →](/xlfill/guides/debugging/)**
For the complete list of functions, options, and types:
**[API Reference →](/xlfill/reference/api/)**
---
### Expressions
**Description**: Everything you can put inside ${...} in XLFill templates.
**URL**: https://javajack.github.io/xlfill/unknown
Expressions are the values inside `${...}` in your template cells. They're evaluated against the current data context and replaced with the result.
## Field access
The most common use — access fields on your data:
```
${employee.Name}
${dept.Manager.Email}
${company.Address.City}
```
This works with Go structs, maps, or any nested combination.
## Arithmetic
Do math directly in the template:
```
${price * quantity}
${subtotal + tax}
${total / count}
${score * 100 / maxScore}
```
## Comparisons and logic
```
${age >= 18} // true or false
${status == "active"} // string comparison
${price > 100 && inStock} // logical AND
${!expired} // negation
```
## Ternary expressions
Conditional values without needing a `jx:if` command:
```
${age >= 18 ? "Adult" : "Minor"}
${score > 90 ? "A" : score > 80 ? "B" : "C"}
${active ? "Yes" : "No"}
```
Great for status columns, pass/fail indicators, and conditional labels.
## Indexing
Access items by position:
```
${items[0].Name} // first item
${matrix[row][col]} // 2D access
${months[quarterStart]} // dynamic index
```
## String concatenation
```
${firstName + " " + lastName}
${prefix + phoneNumber}
```
## Mixed text and expressions
A single cell can contain plain text alongside expressions:
```
Employee: ${e.Name} (${e.Department})
Total: ${amount} USD
Report generated for ${company.Name}
```
XLFill replaces only the `${...}` parts and keeps the surrounding text.
## Built-in variables
These are available in every expression automatically:
| Variable | Type | Description |
|----------|------|-------------|
| `_row` | int | Current output row number (1-based) |
| `_col` | int | Current output column index (0-based) |
```
Row ${_row}: ${e.Name}
```
Useful for row numbering, conditional formatting logic, or debugging.
## Built-in functions
### hyperlink(url, display)
Creates a clickable Excel hyperlink:
```
${hyperlink("https://example.com", "Click here")}
${hyperlink(e.ProfileURL, e.Name)}
```
The cell becomes a real Excel hyperlink — blue, underlined, clickable.
## Custom delimiters
If `${...}` conflicts with content in your spreadsheet (rare, but possible), change the delimiters:
```go
xlfill.Fill("template.xlsx", "output.xlsx", data,
xlfill.WithExpressionNotation("<<", ">>"),
)
```
Then use `<>` in your template instead.
## Expression engine
Under the hood, expressions are powered by [expr-lang/expr](https://github.com/expr-lang/expr), a fast and safe expression evaluator for Go. It supports a rich syntax including:
- All Go operators
- String functions
- Array/slice functions
- Type coercion
- And more — see the [expr-lang documentation](https://expr-lang.org/docs/language-definition) for the full reference
Expressions are compiled once and cached, so even templates with thousands of rows evaluate quickly (~5 million evaluations per second).
## What's next?
Expressions fill individual cells with values. But to control **structure** — loops, conditions, grids — you need commands. Let's see how they work.
**[Commands Overview →](/xlfill/guides/commands-overview/)**
---
### Debugging & Troubleshooting
**Description**: Tools and techniques for inspecting templates, validating expressions, and diagnosing issues in XLFill.
**URL**: https://javajack.github.io/xlfill/unknown
XLFill provides built-in tools for catching template issues early and understanding what the engine sees when it processes your template. This page covers every debugging technique available.
## Validate: catch errors without data
`Validate()` checks your template for structural and expression errors **without requiring any data**. Run it in CI, in tests, or during development to catch problems before they reach production.
```go
issues, err := xlfill.Validate("template.xlsx")
if err != nil {
// Template couldn't be opened or parsed at all
log.Fatal(err)
}
for _, issue := range issues {
fmt.Println(issue)
}
```
Output:
```
[ERROR] Sheet1!B2: invalid expression syntax "e.Name +": unexpected token "+"
[ERROR] Sheet1!A3: each command has invalid items expression "employees[": unexpected token "["
```
### What Validate checks
| Check | What it catches |
|-------|----------------|
| Expression syntax | Bad `${...}` in cell values — e.g., `${e.Name +}` |
| Formula expressions | Bad `${...}` inside formulas — e.g., `=SUM(${bad syntax})` |
| Command attributes | Invalid expressions in `items`, `condition`, `select`, `headers`, `data` |
| Bounds | A command's `lastCell` extends beyond its parent `jx:area` |
| Structural errors | Missing `jx:area`, invalid cell references (returned as `error`, not issues) |
### Use in CI / tests
```go
func TestTemplateValid(t *testing.T) {
issues, err := xlfill.Validate("templates/monthly_report.xlsx")
require.NoError(t, err)
assert.Empty(t, issues, "template has validation issues: %v", issues)
}
```
### ValidationIssue type
Each issue includes a severity, cell reference, and message:
```go
type ValidationIssue struct {
Severity Severity // SeverityError or SeverityWarning
CellRef CellRef
Message string
}
```
`issue.String()` formats as `[ERROR] Sheet1!A2: message` or `[WARN] Sheet1!A2: message`.
## Describe: see what the engine sees
When a template doesn't produce the output you expect, `Describe()` shows you exactly what the engine parsed — the area hierarchy, command attributes, and expressions found in each cell.
```go
output, err := xlfill.Describe("template.xlsx")
if err != nil {
log.Fatal(err)
}
fmt.Print(output)
```
Output:
```
Template: template.xlsx
Sheet1!A1:C2 area (3x2)
Commands:
Sheet1!A2 each (3x1) items="employees" var="e"
Sheet1!A2:C2 area (3x1)
Expressions:
A2: ${e.Name}
B2: ${e.Age}
C2: ${e.Salary}
```
### What to look for
- **Missing commands** — if a `jx:each` doesn't appear, the comment text may be malformed
- **Wrong area bounds** — if the area dimensions look off, check your `lastCell` attribute
- **Missing expressions** — if `${...}` cells don't appear, they may be outside the area bounds
- **Unexpected nesting** — commands should nest inside their parent area, not siblings
### Nested template example
For templates with nested loops or conditionals, `Describe` shows the full tree:
```
Template: report.xlsx
Sheet1!A1:C5 area (3x5)
Expressions:
A1: ${title}
Commands:
Sheet1!A2 each (3x4) items="departments" var="dept"
Sheet1!A2:C5 area (3x4)
Expressions:
A2: ${dept.Name}
Commands:
Sheet1!A3 each (3x1) items="dept.Employees" var="e"
Sheet1!A3:C3 area (3x1)
Expressions:
A3: ${e.Name}
B3: ${e.Role}
C3: ${e.Salary}
Sheet1!C5 if (1x1) condition="dept.ShowTotal"
```
## Error messages: reading the error chain
When `Fill()` fails at runtime, the error message includes the full context chain. Here's how to read it:
```
process area at Sheet1!A1: command each (template Sheet1!A2) at target Sheet1!A5:
select filter "e.Active" at item 3: expression evaluation failed: ...
```
Breaking this down:
| Part | Meaning |
|------|---------|
| `process area at Sheet1!A1` | The root area being processed |
| `command each (template Sheet1!A2)` | The command that failed, and which template cell it came from |
| `at target Sheet1!A5` | The output cell where the command was being applied |
| `select filter "e.Active" at item 3` | The specific operation and iteration index |
The **template cell** tells you where to look in your `.xlsx` file. The **target cell** tells you where in the output the failure occurred. The **item index** tells you which data record triggered the error.
## AreaListener: trace every cell transformation
For deep debugging, implement `AreaListener` to log every cell as it's processed:
```go
type DebugListener struct{}
func (l *DebugListener) BeforeTransformCell(
src, target xlfill.CellRef,
ctx *xlfill.Context,
tx xlfill.Transformer,
) bool {
cd := tx.GetCellData(src)
if cd != nil && cd.Value != nil {
log.Printf("CELL %s -> %s value=%v", src, target, cd.Value)
}
return true // proceed with default transformation
}
func (l *DebugListener) AfterTransformCell(
src, targ
[Content truncated - see full documentation at URL above]
---
### Formulas
**Description**: How XLFill automatically expands and parameterizes Excel formulas in your templates.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import formulaTemplate from '../../../assets/images/formulas_template.png';
import formulaOutput from '../../../assets/images/formulas_output.png';
import paramFormulaImg from '../../../assets/images/par.png';
import groupsumTemplate from '../../../assets/images/groupsum-1.png';
import groupsumOutput from '../../../assets/images/groupsum-2.png';
One of the most powerful features of the template-first approach: **formulas just work.** You write them in your template like normal Excel formulas, and XLFill automatically updates cell references when rows or columns are inserted.
## Automatic formula expansion
When a `jx:each` command inserts new rows, any formula that references cells in the expanded area is automatically updated.
**Template:**
**Output:**
A formula like `=SUM(B2:B2)` in the template expands to `=SUM(B2:B6)` when 5 data rows are generated. You don't write any code for this — it happens automatically.
### Why this matters
With hard-coded Excel libraries, you'd have to:
1. Count how many rows were inserted
2. Manually construct the formula string: `fmt.Sprintf("=SUM(B2:B%d)", lastRow)`
3. Write it to the cell
4. Hope you got the reference right
With XLFill, you write the formula once in the template and forget about it.
## Formulas with grouped data
Formulas work beautifully with grouped data. Here's a report with subtotals per group:
**Template:**
**Output:**
Each group's SUM formula correctly references only the rows in that group.
## Parameterized formulas
Sometimes you need dynamic values inside formulas — a tax rate, a discount percentage, a bonus amount. Use `${...}` expressions inside formula cells:
```
=A1*${taxRate} → =A1*0.2
=A1*${rate}+${bonus} → =A1*0.1+500
```
The `${...}` parts are resolved from your data context, and the result is a real Excel formula (not a static value). This means Excel will recalculate it when the file is opened.
```go
data := map[string]any{
"employees": employees,
"taxRate": 0.2,
"bonus": 500,
}
```
## Recalculate on open
By default, Excel caches formula results. If you want Excel to recalculate everything when the file is opened (ensuring fresh values), enable this option:
```go
xlfill.Fill("template.xlsx", "output.xlsx", data,
xlfill.WithRecalculateOnOpen(true),
)
```
This sets a flag in the file that tells Excel: *"recalculate all formulas when you open this."*
## Try it
Download the runnable example: **template** [t08.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t08.xlsx) | **output** [08_formulas.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/08_formulas.xlsx) | [code snippet](/xlfill/reference/examples/#08-formulas)
## What's next?
If you need to extend XLFill with your own template commands:
**[Custom Commands →](/xlfill/guides/custom-commands/)**
Or if you want to hook into cell processing for conditional styling:
**[Area Listeners →](/xlfill/guides/area-listeners/)**
---
### Getting Started
**Description**: Install the XLFill Go library and build your first template-driven Excel report in 5 minutes.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import blankTemplate from '../../../assets/images/each-1-blank.png';
import filledOutput from '../../../assets/images/each-2.png';
This guide takes you from zero to a working Excel report in about 5 minutes.
## Install
```bash
go get github.com/javajack/xlfill
```
Requires Go 1.24+. Only `.xlsx` files are supported.
## Step 1: Create your template
Open Excel, Google Sheets, or LibreOffice Calc. Create a new file and set it up like this:
Here's what to do:
1. **Format the header row** — bold text, background color, borders, whatever you like
2. **In the data row**, put expressions: `${e.Name}` in A2, `${e.Age}` in B2, `${e.Payment}` in C2
3. **Right-click cell A1** and add a cell comment with this text:
```
jx:area(lastCell="C2")
jx:each(items="employees" var="e" lastCell="C2")
```
4. Save as `template.xlsx`
That comment tells XLFill: *"The template region is A1:C2. Repeat row 2 for each item in the `employees` list, using `e` as the loop variable."*
:::tip
The header row (row 1) is part of the area but **above** the `jx:each` row, so it appears once. Only the `jx:each` row (row 2) repeats.
:::
### How to add cell comments in your spreadsheet editor
Cell comments are the mechanism XLFill uses to read commands. Here's how to add them in the most popular tools:
#### Microsoft Excel
1. Right-click the cell (e.g., A1)
2. Select **Insert Comment** (or **New Note** in Excel 365)
3. Type your commands, one per line:
```
jx:area(lastCell="C2")
jx:each(items="employees" var="e" lastCell="C2")
```
4. Click outside the comment to close it
:::note
Excel 365 has two features: **Comments** (threaded, collaborative) and **Notes** (classic yellow sticky notes). XLFill reads **Notes**. If you only see "New Comment" (threaded), go to **Review > Notes > New Note** instead.
:::
#### Google Sheets
1. Right-click the cell
2. Select **Insert note** (not "Insert comment" — that creates a threaded discussion)
3. Type your commands and click outside to save
4. **Important**: When done, download as **Microsoft Excel (.xlsx)** via File > Download. XLFill only reads `.xlsx` files.
:::caution
Google Sheets distinguishes between **Notes** (plain text, stored in the cell) and **Comments** (threaded discussions). XLFill reads **Notes**. Use **Insert > Note**, not **Insert > Comment**.
:::
#### LibreOffice Calc
1. Right-click the cell
2. Select **Insert Comment**
3. Type your commands in the yellow comment box
4. Click outside to close
5. Save as `.xlsx` format (File > Save As > select "Microsoft Excel 2007-365 (.xlsx)")
#### WPS Office Spreadsheet
1. Right-click the cell
2. Select **Insert Comment**
3. Type your commands in the comment box
4. Click outside to close
5. Ensure you save as `.xlsx` format
#### Quick verification
After adding comments, you should see a small **red triangle** in the top-right corner of the cell (in most editors). Hover over the cell to verify your comment text is correct. That's all XLFill needs to find your commands.
## Step 2: Write Go code
```go
package main
import (
"fmt"
"github.com/javajack/xlfill"
)
func main() {
data := map[string]any{
"employees": []map[string]any{
{"Name": "Elsa", "Age": 28, "Payment": 1500},
{"Name": "Oleg", "Age": 32, "Payment": 2300},
{"Name": "Neil", "Age": 34, "Payment": 2500},
{"Name": "Maria", "Age": 25, "Payment": 1700},
{"Name": "John", "Age": 35, "Payment": 2800},
},
}
err := xlfill.Fill("template.xlsx", "output.xlsx", data)
if err != nil {
fmt.Println("Error:", err)
return
}
fmt.Println("Done! Open output.xlsx")
}
```
## Step 3: Run it
```bash
go run main.go
```
Open `output.xlsx`:
Five rows of data, all formatting preserved from the template. The header stayed put, the borders carried over, the number formats are intact. No code for any of that.
## Using Go structs
Maps work, but so do structs — use whatever fits your application:
```go
type Employee struct {
Name string
Age int
Payment float64
}
data := map[string]any{
"employees": []Employee{
{Name: "Elsa", Age: 28, Payment: 1500},
{Name: "Oleg", Age: 32, Payment: 2300},
},
}
xlfill.Fill("template.xlsx", "output.xlsx", data)
```
Field names in expressions (`${e.Name}`) match struct field names.
## Serving from an HTTP handler
For web applications, use `FillReader` or `FillBytes`:
```go
func reportHandler(w http.ResponseWriter, r *http.Request) {
data := fetchReportData()
w.Header().Set("Content-Type",
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
[Content truncated - see full documentation at URL above]
---
### Why XLFill?
**Description**: See why a template-first Go library is fundamentally better than hard-coding spreadsheets cell by cell.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import templateImg from '../../../assets/images/each-1.png';
import outputImg from '../../../assets/images/each-2.png';
import conceptImg from '../../../assets/images/command-and-expression.png';
Let's be honest. If you've ever generated Excel files from Go, you've probably had *that* moment. The one where you're 150 lines deep into setting cell borders, and someone walks over and says, "Hey, can we make the header blue instead of green?"
And you think: *I have to redeploy for this.*
There's a better way. Let's look at both approaches side by side.
## The hard-coded approach (what most of us have been doing)
Here's a real-world Go program that generates a simple 3-column employee report. Just three columns. Nothing fancy:
```go
f := excelize.NewFile()
sheet := "Report"
f.NewSheet(sheet)
// Set column widths manually
f.SetColWidth(sheet, "A", "A", 20)
f.SetColWidth(sheet, "B", "B", 10)
f.SetColWidth(sheet, "C", "C", 25)
// Create header style
headerStyle, _ := f.NewStyle(&excelize.Style{
Font: &excelize.Font{Bold: true, Size: 12, Color: "FFFFFF"},
Fill: excelize.Fill{Type: "pattern", Color: []string{"4472C4"}, Pattern: 1},
Alignment: &excelize.Alignment{Horizontal: "center"},
Border: []excelize.Border{
{Type: "left", Color: "000000", Style: 1},
{Type: "top", Color: "000000", Style: 1},
{Type: "right", Color: "000000", Style: 1},
{Type: "bottom", Color: "000000", Style: 1},
},
})
// Write headers
f.SetCellValue(sheet, "A1", "Name")
f.SetCellValue(sheet, "B1", "Age")
f.SetCellValue(sheet, "C1", "Department")
f.SetCellStyle(sheet, "A1", "C1", headerStyle)
// Create data style
dataStyle, _ := f.NewStyle(&excelize.Style{
Border: []excelize.Border{
{Type: "left", Color: "000000", Style: 1},
{Type: "top", Color: "000000", Style: 1},
{Type: "right", Color: "000000", Style: 1},
{Type: "bottom", Color: "000000", Style: 1},
},
})
// Write rows
for i, emp := range employees {
row := i + 2
f.SetCellValue(sheet, fmt.Sprintf("A%d", row), emp.Name)
f.SetCellValue(sheet, fmt.Sprintf("B%d", row), emp.Age)
f.SetCellValue(sheet, fmt.Sprintf("C%d", row), emp.Department)
f.SetCellStyle(sheet, fmt.Sprintf("A%d", row), fmt.Sprintf("C%d", row), dataStyle)
}
f.SaveAs("output.xlsx")
```
**40+ lines.** For three columns. And honestly? The output still looks mediocre. No alternating row colors. No conditional formatting. No print-ready margins. Now picture a real report — merged headers, subtotals, logos, multiple sections. You can feel the pain already.
### Sound familiar?
- **"Can we change the header color?"** — Sure, let me edit Go code, rebuild, test, deploy...
- **"The finance team designed a new layout in Excel."** — Great, now I get to reverse-engineer every pixel into struct literals.
- **"We need one more column."** — Time to update every cell reference, every style range, every `fmt.Sprintf`.
- **"The numbers should have two decimal places."** — Let me look up the excelize number format codes again...
The fundamental problem? **The layout is trapped inside your code.** And code is the worst possible place to describe how a spreadsheet should look.
---
## What if the spreadsheet editor *was* your design tool?
That's the core insight behind XLFill. It's a Go library that flips the approach: you don't describe how a report looks in code. You design it where it belongs — in a spreadsheet editor — and the library fills it with your data.
### Step 1: Make it look right in Excel
Open Excel, Google Sheets, LibreOffice — whatever you like. Design your report. Make it beautiful. Spend two minutes getting the fonts right instead of twenty minutes writing style structs:
See those `${e.name}` placeholders? Those are expressions — they tell XLFill where to put data. The `jx:each(...)` in the cell comment? That tells XLFill to repeat this row for each item. That's the entire template language.
### Step 2: Fill it from Go
```go
data := map[string]any{
"employees": employees,
}
xlfill.Fill("template.xlsx", "output.xlsx", data)
```
**Three lines.** The same report that took 40+ lines of hard-coded cell manipulation. And it looks *better*, because you designed it in a proper spreadsheet editor instead of guessing at color hex codes in Go structs.
### The result speaks for itself
Becomes:
Every font, every color, every border, every number format — carried over automatically. You didn't write a single line of styling code.
---
## What changes in your workflow
This isn't just a different library. It's a di
[Content truncated - see full documentation at URL above]
---
### How Templates Work
**Description**: Understand how XLFill reads your Excel template and turns it into a filled report.
**URL**: https://javajack.github.io/xlfill/unknown
import { Image } from 'astro:assets';
import conceptImg from '../../../assets/images/command-and-expression.png';
import templateImg from '../../../assets/images/each-1.png';
import outputImg from '../../../assets/images/each-2.png';
Now that you've built your first report, let's understand what XLFill actually does with your template.
## The two building blocks
Every XLFill template uses just two things:
1. **Expressions** — placed in cell values: `${e.Name}`, `${price * qty}`
2. **Commands** — placed in cell comments: `jx:each(...)`, `jx:if(...)`
That's the entire template language. Expressions produce values. Commands control structure (loops, conditions, grids).
## Understanding the work area
The **work area** is the most important concept in XLFill. It answers the question: *"Which part of my spreadsheet is a template, and which part is just static content?"*
### What the work area is
When you write `jx:area(lastCell="C2")` in a comment on cell A1, you're defining a rectangular region — A1 to C2 — as the work area. This is the region XLFill will process. Everything else on the sheet is left completely untouched.
```
A B C D E
┌──────────────────────────────────────┐
1 │ ${e.Name} ${e.Age} ${e.Dept} │ ← Work area (A1:C2)
2 │ (data row) (data row) (data row) │ XLFill processes this
└──────────────────────────────────────┘
3 Notes here Logo ... ← Outside the area
4 Static text Static ... XLFill ignores this
```
### Why the work area matters
- **Cells inside the area** are processed: expressions are evaluated, commands are executed, rows may be inserted or removed
- **Cells outside the area** are preserved exactly as-is — they pass through to the output unchanged
- This lets you have static content (company logos, instructions, disclaimers) on the same sheet as your dynamic template
### The work area grows dynamically
Here's a subtle but powerful point: the work area has a **template size** (what you defined) and an **output size** (what actually gets produced).
If your area is A1:C2 (2 rows) and your `jx:each` loops over 100 employees, the output is 101 rows (1 header + 100 data rows). The area *expands* to accommodate the data. You don't need to predict how large the output will be — XLFill handles it.
```
Template area: A1:C2 (2 rows) → Output: A1:C101 (101 rows)
┌─────────────┐ ┌─────────────┐
A1 │ Header │ A1 │ Header │
A2 │ ${e.Name} │ A2 │ Alice │
└─────────────┘ A3 │ Bob │
... │ ... │
A101 │ Employee100 │
└─────────────┘
```
Conversely, if a `jx:if` condition is false, its area is **removed** and the output shrinks. The work area adapts to the data.
### Every command has its own area
It's not just `jx:area` that has a region — every command defines its own rectangular area via `lastCell`. XLFill builds a hierarchy:
- The root `jx:area` is the outermost boundary
- Each `jx:each`, `jx:if`, etc. defines a sub-area within it
- Commands whose areas are strictly inside another command become **children** of that command
- XLFill detects this nesting automatically from cell positions — you never declare it explicitly
This hierarchy is what makes nested loops, conditional sections, and complex layouts possible without any special syntax.
## How XLFill processes a template
When you call `xlfill.Fill(...)`, here's what happens step by step:
### 1. Parse comments
XLFill scans every cell comment looking for `jx:` commands. It builds a tree of commands based on their areas — which commands contain which other commands.
### 2. Define the working area
The `jx:area(lastCell="...")` command on the top-left cell establishes the processing boundary. Everything outside this area is left untouched.
### 3. Execute commands top-down
Starting from the outermost command, XLFill executes each one:
- **`jx:each`** copies its template rows for each item in a collection, expanding the output
- **`jx:if`** includes or skips its area based on a condition, potentially shrinking the output
- Nested commands are executed within their parent's context, inheriting the parent's loop variables
### 4. Evaluate expressions
For each cell in a command's area, XLFill replaces `${...}` expressions with values from the current data context. Inside a `jx:each` loop, `${e.Name}` resolves to the current item's Name field.
### 5. Preserve everything else
Styles, fonts, borders, number formats, merged cells, row heights, column widths — all copied from the template to the output without any special effort from you.
## A concrete
[Content truncated - see full documentation at URL above]
---
### API Reference
**Description**: Complete API reference for the XLFill Go library — functions, options, and types.
**URL**: https://javajack.github.io/xlfill/unknown
This page covers every public function, option, and type in the XLFill library. For guided walkthroughs, see the [Getting Started](/xlfill/guides/getting-started/) guide.
## Top-level functions
These are the simplest way to use XLFill. One function call, done.
### Fill
```go
func Fill(templatePath, outputPath string, data map[string]any, opts ...Option) error
```
Read a template file, fill it with data, write the output file.
```go
xlfill.Fill("template.xlsx", "report.xlsx", data)
```
### FillBytes
```go
func FillBytes(templatePath string, data map[string]any, opts ...Option) ([]byte, error)
```
Read a template file, fill it, return the result as bytes. Useful when you need the output in memory.
```go
bytes, err := xlfill.FillBytes("template.xlsx", data)
```
### FillReader
```go
func FillReader(template io.Reader, output io.Writer, data map[string]any, opts ...Option) error
```
Fill from an `io.Reader`, write to an `io.Writer`. Perfect for HTTP handlers — no temp files needed:
```go
func handler(w http.ResponseWriter, r *http.Request) {
tmpl, _ := os.Open("template.xlsx")
defer tmpl.Close()
xlfill.FillReader(tmpl, w, data)
}
```
### Validate
```go
func Validate(templatePath string, opts ...Option) ([]ValidationIssue, error)
```
Check a template for structural and expression errors **without requiring data**. Returns a list of issues found. Use this in CI pipelines or during development to catch problems early.
```go
issues, err := xlfill.Validate("template.xlsx")
if err != nil {
log.Fatal(err) // template couldn't be opened or parsed at all
}
for _, issue := range issues {
fmt.Println(issue) // [ERROR] Sheet1!B2: invalid expression syntax "e.Name +": ...
}
```
What it checks:
- **Expression syntax** — validates all `${...}` in cell values and formulas
- **Command attributes** — validates `items`, `condition`, `select`, `headers`, `data` expressions
- **Bounds** — verifies each command's `lastCell` fits within its parent area
### Describe
```go
func Describe(templatePath string, opts ...Option) (string, error)
```
Parse a template and return a human-readable tree showing the area hierarchy, commands with attributes, and expressions found in cells. Useful for understanding what the engine "sees" when it reads your template.
```go
output, err := xlfill.Describe("template.xlsx")
fmt.Print(output)
```
Sample output:
```
Template: template.xlsx
Sheet1!A1:C2 area (3x2)
Commands:
Sheet1!A2 each (3x1) items="employees" var="e"
Sheet1!A2:C2 area (3x1)
Expressions:
A2: ${e.Name}
B2: ${e.Age}
C2: ${e.Salary}
```
## Filler (advanced)
For repeated fills or fine-grained control, create a `Filler`:
```go
filler := xlfill.NewFiller(
xlfill.WithTemplate("template.xlsx"),
xlfill.WithClearTemplateCells(true),
xlfill.WithRecalculateOnOpen(true),
)
err := filler.Fill(data, "output.xlsx")
// Validate and Describe also available on Filler
issues, err := filler.Validate()
description, err := filler.Describe()
```
## Options
All options work with both the top-level functions and `NewFiller`.
### Template source
| Option | Description |
|--------|-------------|
| `WithTemplate(path)` | Set template file path |
| `WithTemplateReader(r io.Reader)` | Set template from a reader |
### Expression configuration
| Option | Description |
|--------|-------------|
| `WithExpressionNotation(begin, end)` | Custom delimiters (default: `${`, `}`) |
### Output control
| Option | Description |
|--------|-------------|
| `WithClearTemplateCells(bool)` | Clear unexpanded `${...}` cells (default: `true`) |
| `WithKeepTemplateSheet(bool)` | Keep template sheet in output (default: `false`) |
| `WithHideTemplateSheet(bool)` | Hide template sheet instead of removing (default: `false`) |
| `WithRecalculateOnOpen(bool)` | Tell Excel to recalculate formulas on open |
### Extensibility
| Option | Description |
|--------|-------------|
| `WithCommand(name, factory)` | Register a [custom command](/xlfill/guides/custom-commands/) |
| `WithAreaListener(listener)` | Add a [cell transform hook](/xlfill/guides/area-listeners/) |
| `WithPreWrite(fn)` | Callback before writing output |
## Types
### CellRef
A reference to a single cell:
```go
type CellRef struct {
SheetName string
Col int // 0-based column index
Row int // 1-based row number
}
```
### AreaRef
A rectangular range of cells:
```go
type AreaRef struct {
SheetName string
FirstCellRef CellRef
LastCellRef CellRef
}
```
### Size
The dimensions of a processed area:
```go
type Size struct {
Width int
Height int
}
```
### Command interface
Implement this to create [custom commands](/xlfill/guides/custom-commands/):
```go
type Command interface {
Name() string
ApplyAt(cellRef CellRef, ctx *Context, transformer Transformer) (Size, error)
Reset()
}
```
### AreaListener interface
Implement this for [cell transform hooks](/x
[Content truncated - see full documentation at URL above]
---
### Performance
**Description**: XLFill benchmarks, scaling characteristics, and optimization tips.
**URL**: https://javajack.github.io/xlfill/unknown
XLFill is designed to be fast enough that performance is never a concern for typical report generation. Here are the numbers.
## Benchmarks
Measured on Intel i5-9300H @ 2.40GHz:
| Scenario | Rows | Time | Memory | Throughput |
|----------|------|------|--------|------------|
| Simple 3-column template | 100 | 5.3ms | 1.8 MB | ~19,000 rows/sec |
| Simple 3-column template | 1,000 | 30ms | 9.4 MB | ~33,000 rows/sec |
| Simple 3-column template | 10,000 | 279ms | 85.6 MB | ~35,800 rows/sec |
| Nested loops (10 groups x 20 items) | 200 | 2.2ms | 872 KB | ~91,000 rows/sec |
| Single expression evaluation | 1 | 199ns | 48 B | ~5M evals/sec |
| Single comment parse | 1 | 4.1us | 1 KB | ~244K parses/sec |
## Key characteristics
**Linear scaling** — processing time grows linearly with the number of rows. 10x more rows = ~10x more time. No surprises.
**~8.7 KB per row** at scale — memory usage is dominated by the Excel file representation in excelize. For 10,000 rows, expect ~87 MB.
**Expression caching** — expressions are compiled once on first encounter and cached. Subsequent evaluations of the same expression hit the cache, giving ~5 million evaluations per second.
**Comment parsing** — template comments are parsed at ~244K per second. Even a template with hundreds of commands parses in under a millisecond.
## What this means in practice
| Report size | Expected time |
|-------------|--------------|
| Small (100 rows) | Instant — under 6ms |
| Medium (1,000 rows) | ~30ms — imperceptible |
| Large (10,000 rows) | ~280ms — still fast |
| Very large (100,000 rows) | ~3 seconds — consider streaming |
For comparison, the time to open a 10,000-row Excel file in the Excel application is typically longer than the time XLFill takes to generate it.
## Tips for large reports
1. **Keep templates simple** — fewer expressions per row means faster evaluation
2. **Filter early with `select`** — `jx:each(... select="e.Active")` is faster than generating rows you'll discard
3. **Avoid deep nesting** — each nesting level multiplies the work. Three levels deep with large collections can add up
4. **Minimize formula references** — formula expansion scans all formulas after each command. Templates with hundreds of formulas in the expanded area may see overhead
## Requirements
- Go 1.24+
- `.xlsx` files only (the `.xls` binary format is not supported)
## What's next?
Having trouble with a template? XLFill ships with built-in tools for inspecting and validating templates:
**[Debugging & Troubleshooting →](/xlfill/guides/debugging/)**
---
### Examples
**Description**: Runnable examples for every XLFill feature, with template inputs and filled outputs.
**URL**: https://javajack.github.io/xlfill/unknown
The [`examples/xlfill-test`](https://github.com/javajack/xlfill/tree/main/examples/xlfill-test) project in the repository exercises every XLFill feature in one Go program. Each test creates a template, fills it, and verifies the output.
You can run it yourself:
```bash
cd examples/xlfill-test
go run .
```
Or just browse the files below. Open any **input** file in Excel or LibreOffice to see the `jx:` comments and `${...}` expressions. Then open the matching **output** file to see the filled result.
## All examples
| # | Feature | Command / API | Template | Output | Source |
|---|---------|---------------|----------|--------|--------|
| 01 | Basic loop | [`jx:each`](/xlfill/commands/each/) | [t01.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t01.xlsx) | [01_basic_each.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/01_basic_each.xlsx) | [code](#01---basic-loop) |
| 02 | Loop index | [`jx:each` varIndex](/xlfill/commands/each/#iteration-index) | [t02.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t02.xlsx) | [02_varindex.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/02_varindex.xlsx) | [code](#02---loop-index) |
| 03 | Expand RIGHT | [`jx:each` direction](/xlfill/commands/each/#expand-right) | [t03.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t03.xlsx) | [03_direction_right.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/03_direction_right.xlsx) | [code](#03---expand-right) |
| 04 | Filter items | [`jx:each` select](/xlfill/commands/each/#filtering) | [t04.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t04.xlsx) | [04_select.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/04_select.xlsx) | [code](#04---filter-items) |
| 05 | Sort items | [`jx:each` orderBy](/xlfill/commands/each/#sorting) | [t05.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t05.xlsx) | [05_orderby.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/05_orderby.xlsx) | [code](#05---sort-items) |
| 06 | Group items | [`jx:each` groupBy](/xlfill/commands/each/#grouping) | [t06.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t06.xlsx) | [06_groupby.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/06_groupby.xlsx) | [code](#06---group-items) |
| 07 | Conditional | [`jx:if`](/xlfill/commands/if/) | [t07.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t07.xlsx) | [07_if_command.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/07_if_command.xlsx) | [code](#07---conditional) |
| 08 | Formulas | [Formula expansion](/xlfill/guides/formulas/) | [t08.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t08.xlsx) | [08_formulas.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/08_formulas.xlsx) | [code](#08---formulas) |
| 09 | Dynamic grid | [`jx:grid`](/xlfill/commands/grid/) | [t09.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t09.xlsx) | [09_grid.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/09_grid.xlsx) | [code](#09---dynamic-grid) |
| 10 | Embed image | [`jx:image`](/xlfill/commands/image/) | [t10.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t10.xlsx) | [10_image.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/10_image.xlsx) | [code](#10---embed-image) |
| 11 | Merge cells | [`jx:mergeCells`](/xlfill/commands/mergecells/) | [t11.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t11.xlsx) | [11_mergecells.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/11_mergecells.xlsx) | [code](#11---merge-cells) |
| 12 | Hyperlinks | [`hyperlink()` expression](/xlfill/guides/expressions/) | [t12.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t12.xlsx) | [12_hyperlinks.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/12_hyperlinks.xlsx) | [code](#12---hyperlinks) |
| 13 | Nested loops | [Nested `jx:each`](/xlfill/commands/each/#nested-loops) | [t13.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t13.xlsx) | [13_nested_each.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/13_nested_each.xlsx) | [code](#13---nested-loops) |
| 14 | Multi-sheet | [`jx:each` multisheet](/xlfill/commands/each/#multi-sheet-output) | [t14.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/input/t14.xlsx) | [14_multisheet.xlsx](https://github.com/javajack/xlfill/raw/main/examples/xlfill-test/output/14_multisheet.xlsx) | [code](#14---multi-sheet) |
| 15 | Custom notation | [`WithExpressionNotation`](/xlfill/refer
[Content truncated - see full documentation at URL above]
---
## Core API Functions
### xlfill.Fill(templatePath, outputPath, data)
Fills an Excel template with data and saves the result.
**Parameters**:
- templatePath: Path to the Excel template (.xlsx)
- outputPath: Path where the output file will be saved
- data: Data structure (map, struct, or any Go value)
### xlfill.Describe(templatePath)
Analyzes a template and returns its structure, including all commands and expressions.
---
## Template Commands Reference
### jx:each - Iterate over collections
Syntax: jx:each(items=data.employees, direction=RIGHT)
Repeats cells for each item in a collection.
### jx:if - Conditional rendering
Syntax: jx:if(test=value > 100, direction=DOWN)
Conditionally includes or removes cells based on expressions.
### jx:grid - 2D data grids
Syntax: jx:grid(data=matrix, headers=true)
Renders two-dimensional data structures efficiently.
### jx:image - Insert images
Syntax: jx:image(src=data.imageUrl)
Inserts images from URLs or file paths.
### jx:mergeCells - Merge cells dynamically
Syntax: jx:mergeCells(cols=2, rows=1)
Merges cells based on data-driven logic.
### jx:updateCell - Modify cell properties
Syntax: jx:updateCell(col=2, row=3, value=newValue)
Updates cell values or formatting programmatically.
### jx:autoRowHeight - Auto-adjust row heights
Syntax: jx:autoRowHeight
Automatically adjusts row heights to fit content.
---
## Project Information
- **GitHub**: https://github.com/javajack/xlfill
- **Documentation**: https://javajack.github.io/xlfill/
- **License**: MIT
- **Author**: Rakesh Waghela (https://x.com/webiyo, https://www.linkedin.com/in/rakeshwaghela)
- **Consultation**: https://topmate.io/rakeshwaghela
This documentation is optimized for LLM consumption. For the interactive version with examples and visual aids, visit the full documentation site.