Skip to content

API Reference

Complete documentation for xltemplate classes and methods.

Workbook

xltemplate.Workbook

A wrapper around an openpyxl Workbook for template population.

Provides a clean, stateful interface for loading Excel templates, writing DataFrames to sheets, and saving the result.

Example

wb = Workbook("template.xlsx") wb.sheet("Data").write_df(df, row=5, col=2) wb.save("output.xlsx")

Source code in xltemplate/workbook.py 
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
class Workbook:
    """
    A wrapper around an openpyxl Workbook for template population.

    Provides a clean, stateful interface for loading Excel templates,
    writing DataFrames to sheets, and saving the result.

    Example:
        >>> wb = Workbook("template.xlsx")
        >>> wb.sheet("Data").write_df(df, row=5, col=2)
        >>> wb.save("output.xlsx")
    """

    def __init__(self, path: Union[str, Path]) -> None:
        """
        Load an existing Excel workbook from disk.

        Args:
            path: Path to the .xlsx file to load

        Raises:
            FileNotFoundError: If the file does not exist
            openpyxl.utils.exceptions.InvalidFileException: If not a valid xlsx
        """
        self._path = Path(path)
        if not self._path.exists():
            raise FileNotFoundError(f"Workbook not found: {self._path}")

        self._wb: OpenpyxlWorkbook = openpyxl_load_workbook(
            self._path,
            data_only=False,  # Preserve formulas
        )
        self._sheets: dict[str, Sheet] = {}

    @property
    def sheet_names(self) -> list[str]:
        """List of all sheet names in the workbook."""
        return self._wb.sheetnames

    def sheet(self, name: str) -> Sheet:
        """
        Get a Sheet object for the named worksheet.

        Args:
            name: The name of the worksheet

        Returns:
            A Sheet object for the worksheet

        Raises:
            KeyError: If no sheet with that name exists
        """
        if name not in self._wb.sheetnames:
            raise KeyError(f"Sheet '{name}' not found. Available: {self.sheet_names}")

        # Cache Sheet objects for reuse
        if name not in self._sheets:
            self._sheets[name] = Sheet(self._wb[name], self)

        return self._sheets[name]

    def save(self, path: Union[str, Path]) -> None:
        """
        Save the workbook to disk.

        Args:
            path: Destination path for the saved workbook
        """
        self._wb.save(Path(path))

    def close(self) -> None:
        """Close the workbook and release resources."""
        self._wb.close()

    def __enter__(self) -> Workbook:
        """Context manager entry."""
        return self

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """Context manager exit - closes the workbook."""
        self.close()

sheet_names property

List of all sheet names in the workbook.

__init__(path)

Load an existing Excel workbook from disk.

Parameters:

Name Type Description Default
path Union[str, Path]

Path to the .xlsx file to load

 required

Raises:

Type Description
FileNotFoundError

If the file does not exist
InvalidFileException

If not a valid xlsx
Source code in xltemplate/workbook.py 
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
def __init__(self, path: Union[str, Path]) -> None:
    """
    Load an existing Excel workbook from disk.

    Args:
        path: Path to the .xlsx file to load

    Raises:
        FileNotFoundError: If the file does not exist
        openpyxl.utils.exceptions.InvalidFileException: If not a valid xlsx
    """
    self._path = Path(path)
    if not self._path.exists():
        raise FileNotFoundError(f"Workbook not found: {self._path}")

    self._wb: OpenpyxlWorkbook = openpyxl_load_workbook(
        self._path,
        data_only=False,  # Preserve formulas
    )
    self._sheets: dict[str, Sheet] = {}

sheet(name)

Get a Sheet object for the named worksheet.

Parameters:

Name Type Description Default
name str

The name of the worksheet

 required

Returns:

Type Description
Sheet

A Sheet object for the worksheet

Raises:

Type Description
KeyError

If no sheet with that name exists
Source code in xltemplate/workbook.py 
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
def sheet(self, name: str) -> Sheet:
    """
    Get a Sheet object for the named worksheet.

    Args:
        name: The name of the worksheet

    Returns:
        A Sheet object for the worksheet

    Raises:
        KeyError: If no sheet with that name exists
    """
    if name not in self._wb.sheetnames:
        raise KeyError(f"Sheet '{name}' not found. Available: {self.sheet_names}")

    # Cache Sheet objects for reuse
    if name not in self._sheets:
        self._sheets[name] = Sheet(self._wb[name], self)

    return self._sheets[name]

save(path)

Save the workbook to disk.

Parameters:

Name Type Description Default
path Union[str, Path]

Destination path for the saved workbook

 required
Source code in xltemplate/workbook.py 
75
76
77
78
79
80
81
82
def save(self, path: Union[str, Path]) -> None:
    """
    Save the workbook to disk.

    Args:
        path: Destination path for the saved workbook
    """
    self._wb.save(Path(path))

close()

Close the workbook and release resources.

Source code in xltemplate/workbook.py 
84
85
86
def close(self) -> None:
    """Close the workbook and release resources."""
    self._wb.close()

__enter__()

Context manager entry.

Source code in xltemplate/workbook.py 
88
89
90
def __enter__(self) -> Workbook:
    """Context manager entry."""
    return self

__exit__(exc_type, exc_val, exc_tb)

Context manager exit - closes the workbook.

Source code in xltemplate/workbook.py 
92
93
94
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
    """Context manager exit - closes the workbook."""
    self.close()

Sheet

xltemplate.Sheet

Represents a worksheet within a Workbook.

Provides methods for writing DataFrames and values while preserving existing formatting and formulas.

Attributes:

Name Type Description
name str

The name of the worksheet
Source code in xltemplate/sheet.py 
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
class Sheet:
    """
    Represents a worksheet within a Workbook.

    Provides methods for writing DataFrames and values while preserving
    existing formatting and formulas.

    Attributes:
        name: The name of the worksheet
    """

    def __init__(self, worksheet: Worksheet, workbook: Workbook) -> None:
        """
        Initialize a Sheet wrapper.

        Args:
            worksheet: The underlying openpyxl Worksheet object
            workbook: Reference to the parent Workbook
        """
        self._ws = worksheet
        self._wb = workbook

    @property
    def name(self) -> str:
        """The name of this worksheet."""
        return self._ws.title

    def write_df(
        self,
        df: Any,
        row: int,
        col: int,
        *,
        headers: bool = True,
        preserve_format: bool = True,
        preserve_formulas: bool = True,
    ) -> Sheet:
        """
        Write a DataFrame to the worksheet starting at the specified position.

        Args:
            df: A Pandas or Polars DataFrame
            row: Starting row (1-indexed)
            col: Starting column (1-indexed)
            headers: Include column headers as the first row (default: True)
            preserve_format: Keep existing cell formatting (default: True)
            preserve_formulas: Skip cells containing formulas (default: True)

        Returns:
            Self for method chaining

        Raises:
            TypeError: If df is not a supported DataFrame type
            ValueError: If row or col is less than 1
        """
        if row < 1 or col < 1:
            raise ValueError("row and col must be >= 1 (1-indexed)")

        for r_idx, data_row in enumerate(iter_dataframe_rows(df, headers), start=row):
            for c_idx, value in enumerate(data_row, start=col):
                self._write_cell(
                    r_idx, c_idx, value,
                    preserve_format=preserve_format,
                    preserve_formulas=preserve_formulas,
                )

        return self

    def write_value(
        self,
        value: Any,
        row: int,
        col: int,
        *,
        preserve_format: bool = True,
    ) -> Sheet:
        """
        Write a single value to a cell.

        Args:
            value: The value to write
            row: Row number (1-indexed)
            col: Column number (1-indexed)
            preserve_format: Keep existing cell formatting (default: True)

        Returns:
            Self for method chaining

        Raises:
            ValueError: If row or col is less than 1
        """
        if row < 1 or col < 1:
            raise ValueError("row and col must be >= 1 (1-indexed)")

        self._write_cell(row, col, value, preserve_format=preserve_format)
        return self

    def _write_cell(
        self,
        row: int,
        col: int,
        value: Any,
        *,
        preserve_format: bool = True,
        preserve_formulas: bool = False,
    ) -> None:
        """
        Internal method to write a value to a cell with optional preservation.

        Args:
            row: Row number (1-indexed)
            col: Column number (1-indexed)
            value: The value to write
            preserve_format: Keep existing cell formatting
            preserve_formulas: Skip if cell contains a formula
        """
        cell = self._ws.cell(row=row, column=col)

        # Skip cells with formulas if preserve_formulas is True
        if preserve_formulas:
            current_value = cell.value
            if isinstance(current_value, str) and current_value.startswith("="):
                return

        if preserve_format:
            # Store the existing style ID before writing
            existing_style = cell._style
            cell.value = value
            # Restore the style after writing
            cell._style = existing_style
        else:
            cell.value = value

    def extract_header_schema(
        self,
        row: int,
        col: int,
        *,
        n_cols: int | None = None,
        n_header_rows: int = 1,
    ) -> TableSchema:
        """
        Extract column structure from template header cells.

        Reads the header row(s) at the specified location and returns a
        TableSchema that can be used to create matching DataFrames.

        For multi-row headers (e.g., grouped columns), the bottom row is used
        for column names and upper rows are captured as groups.

        Args:
            row: Starting row of the header (1-indexed)
            col: Starting column (1-indexed)
            n_cols: Number of columns to extract. If None, auto-detects by
                    reading until an empty cell is encountered.
            n_header_rows: Number of header rows (default: 1). For multi-level
                          headers, set this to the number of rows in the header.

        Returns:
            TableSchema with column names and optional group information

        Raises:
            ValueError: If row or col is less than 1

        Example:
            >>> schema = sheet.extract_header_schema(row=3, col=2, n_cols=6)
            >>> df = schema.empty_df()
            >>> schema.column_names
            ['N', '% of Total', 'N', '% of Total', 'N', '% of Total']
        """
        if row < 1 or col < 1:
            raise ValueError("row and col must be >= 1 (1-indexed)")

        # Determine the row containing leaf-level column names
        leaf_row = row + n_header_rows - 1

        # Extract column names
        column_names: list[str] = []
        c_idx = col

        while True:
            cell_value = self._ws.cell(row=leaf_row, column=c_idx).value

            # Stop conditions
            if n_cols is not None:
                if c_idx - col >= n_cols:
                    break
            else:
                # Auto-detect: stop at empty cell
                if cell_value is None or (isinstance(cell_value, str) and not cell_value.strip()):
                    break

            column_names.append(str(cell_value) if cell_value is not None else "")
            c_idx += 1

        # Extract all header rows above the leaf row (from top to bottom)
        header_rows: list[list[tuple[str, int]]] = []
        if n_header_rows > 1:
            for header_row_idx in range(row, leaf_row):
                header_row = self._extract_header_row(header_row_idx, col, len(column_names))
                header_rows.append(header_row)

        return TableSchema(column_names=column_names, header_rows=header_rows)

    def _extract_header_row(
        self,
        row: int,
        col: int,
        n_cols: int,
    ) -> list[tuple[str, int]]:
        """
        Extract labels and spans from a single header row.

        For merged cells, determines the span from the merge range and
        retrieves the value from the top-left cell of the merge.
        For non-merged cells, the span is 1.

        Args:
            row: The row to extract
            col: Starting column
            n_cols: Number of columns to process

        Returns:
            List of (label, span) tuples
        """
        result: list[tuple[str, int]] = []
        c_idx = col

        while c_idx < col + n_cols:
            cell = self._ws.cell(row=row, column=c_idx)
            cell_value = cell.value

            # Check if this cell is part of a merged range
            span = 1
            for merged_range in self._ws.merged_cells.ranges:
                if cell.coordinate in merged_range:
                    # Get the value from the top-left cell of the merged range
                    top_left_cell = self._ws.cell(
                        row=merged_range.min_row,
                        column=merged_range.min_col
                    )
                    cell_value = top_left_cell.value

                    # Get the span within our column range
                    merge_start_col = merged_range.min_col
                    merge_end_col = merged_range.max_col
                    span = min(merge_end_col, col + n_cols - 1) - max(merge_start_col, col) + 1
                    break

            label = str(cell_value) if cell_value is not None else ""
            result.append((label, span))
            c_idx += span

        return result

name property

The name of this worksheet.

write_df(df, row, col, *, headers=True, preserve_format=True, preserve_formulas=True)

Write a DataFrame to the worksheet starting at the specified position.

Parameters:

Name Type Description Default
df Any

A Pandas or Polars DataFrame

 required
row int

Starting row (1-indexed)

 required
col int

Starting column (1-indexed)

 required
headers bool

Include column headers as the first row (default: True)

 True
preserve_format bool

Keep existing cell formatting (default: True)

 True
preserve_formulas bool

Skip cells containing formulas (default: True)

 True

Returns:

Type Description
Sheet

Self for method chaining

Raises:

Type Description
TypeError

If df is not a supported DataFrame type
ValueError

If row or col is less than 1
Source code in xltemplate/sheet.py 
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
def write_df(
    self,
    df: Any,
    row: int,
    col: int,
    *,
    headers: bool = True,
    preserve_format: bool = True,
    preserve_formulas: bool = True,
) -> Sheet:
    """
    Write a DataFrame to the worksheet starting at the specified position.

    Args:
        df: A Pandas or Polars DataFrame
        row: Starting row (1-indexed)
        col: Starting column (1-indexed)
        headers: Include column headers as the first row (default: True)
        preserve_format: Keep existing cell formatting (default: True)
        preserve_formulas: Skip cells containing formulas (default: True)

    Returns:
        Self for method chaining

    Raises:
        TypeError: If df is not a supported DataFrame type
        ValueError: If row or col is less than 1
    """
    if row < 1 or col < 1:
        raise ValueError("row and col must be >= 1 (1-indexed)")

    for r_idx, data_row in enumerate(iter_dataframe_rows(df, headers), start=row):
        for c_idx, value in enumerate(data_row, start=col):
            self._write_cell(
                r_idx, c_idx, value,
                preserve_format=preserve_format,
                preserve_formulas=preserve_formulas,
            )

    return self

write_value(value, row, col, *, preserve_format=True)

Write a single value to a cell.

Parameters:

Name Type Description Default
value Any

The value to write

 required
row int

Row number (1-indexed)

 required
col int

Column number (1-indexed)

 required
preserve_format bool

Keep existing cell formatting (default: True)

 True

Returns:

Type Description
Sheet

Self for method chaining

Raises:

Type Description
ValueError

If row or col is less than 1
Source code in xltemplate/sheet.py 
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
def write_value(
    self,
    value: Any,
    row: int,
    col: int,
    *,
    preserve_format: bool = True,
) -> Sheet:
    """
    Write a single value to a cell.

    Args:
        value: The value to write
        row: Row number (1-indexed)
        col: Column number (1-indexed)
        preserve_format: Keep existing cell formatting (default: True)

    Returns:
        Self for method chaining

    Raises:
        ValueError: If row or col is less than 1
    """
    if row < 1 or col < 1:
        raise ValueError("row and col must be >= 1 (1-indexed)")

    self._write_cell(row, col, value, preserve_format=preserve_format)
    return self

TableSchema

xltemplate.TableSchema dataclass

Represents the column structure extracted from a template header.

Use this to create DataFrames that match a template's expected structure, or to validate that existing DataFrames conform to the template.

The schema captures hierarchical column headers (e.g., grouped columns) and creates DataFrames with pandas MultiIndex columns so that each cell can be addressed by all levels of the hierarchy.

Attributes:

Name Type Description
column_names list[str]

List of column names in order (leaf-level for multi-row headers)
header_rows list[list[tuple[str, int]]]

List of header rows above the leaf row, from top to bottom. Each row is a list of (label, span) tuples.
Example

schema = sheet.extract_header_schema(row=6, col=2, n_cols=16, n_header_rows=3) df = schema.empty_df() df[("Prevalence by Domain", "Domain: XXX", "N")] # Access by hierarchy

Source code in xltemplate/schema.py 
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
@dataclass
class TableSchema:
    """
    Represents the column structure extracted from a template header.

    Use this to create DataFrames that match a template's expected structure,
    or to validate that existing DataFrames conform to the template.

    The schema captures hierarchical column headers (e.g., grouped columns)
    and creates DataFrames with pandas MultiIndex columns so that each cell
    can be addressed by all levels of the hierarchy.

    Attributes:
        column_names: List of column names in order (leaf-level for multi-row headers)
        header_rows: List of header rows above the leaf row, from top to bottom.
                     Each row is a list of (label, span) tuples.

    Example:
        >>> schema = sheet.extract_header_schema(row=6, col=2, n_cols=16, n_header_rows=3)
        >>> df = schema.empty_df()
        >>> df[("Prevalence by Domain", "Domain: XXX", "N")]  # Access by hierarchy
    """

    column_names: list[str]
    header_rows: list[list[tuple[str, int]]] = field(default_factory=list)

    @property
    def groups(self) -> list[tuple[str, int]] | None:
        """Backward-compatible alias for the first header row."""
        return self.header_rows[0] if self.header_rows else None

    @property
    def n_levels(self) -> int:
        """Number of header levels (header_rows + leaf column_names)."""
        return len(self.header_rows) + 1

    def _expand_header_row(self, row: list[tuple[str, int]]) -> list[str]:
        """
        Expand (label, span) tuples into per-column labels.

        Example:
            [('A', 2), ('B', 3)] -> ['A', 'A', 'B', 'B', 'B']
        """
        result = []
        for label, span in row:
            result.extend([label] * span)
        return result

    def to_multiindex(self) -> Any:
        """
        Build a pandas MultiIndex from the header structure.

        Each level of the MultiIndex corresponds to a header row,
        with the leaf column_names as the final level.

        Returns:
            pandas.MultiIndex with one level per header row + leaf columns

        Raises:
            ImportError: If pandas is not installed
        """
        try:
            import pandas as pd
        except ImportError as e:
            raise ImportError(
                "pandas is required to use to_multiindex(). "
                "Install it with: pip install pandas"
            ) from e

        if not self.header_rows:
            # Single-level header: just return Index
            return pd.Index(self.column_names)

        # Build tuples for each column position
        n_cols = len(self.column_names)
        tuples = []

        for col_idx in range(n_cols):
            col_tuple = []
            # Add label from each header row
            for header_row in self.header_rows:
                expanded = self._expand_header_row(header_row)
                col_tuple.append(expanded[col_idx])
            # Add the leaf column name
            col_tuple.append(self.column_names[col_idx])
            tuples.append(tuple(col_tuple))

        return pd.MultiIndex.from_tuples(tuples)

    def empty_df(self, n_rows: int = 0) -> Any:
        """
        Create an empty DataFrame with MultiIndex columns matching this schema.

        For multi-row headers, the DataFrame will have hierarchical columns
        that can be accessed by the full path, e.g.:
            df[("Prevalence by Domain", "Domain: XXX", "N")]

        Args:
            n_rows: Number of rows to pre-allocate (default: 0)

        Returns:
            A pandas DataFrame with MultiIndex columns matching the schema.

        Raises:
            ImportError: If pandas is not installed
        """
        try:
            import pandas as pd
        except ImportError as e:
            raise ImportError(
                "pandas is required to use empty_df(). "
                "Install it with: pip install pandas"
            ) from e

        columns = self.to_multiindex()

        if n_rows > 0:
            return pd.DataFrame(index=range(n_rows), columns=columns)
        return pd.DataFrame(columns=columns)

    def validate_df(self, df: Any) -> bool:
        """
        Check if a DataFrame's columns match this schema's MultiIndex structure.

        Args:
            df: A pandas DataFrame to validate

        Returns:
            True if columns match exactly (all levels, names, and order)
        """
        if not hasattr(df, "columns"):
            return False

        expected = self.to_multiindex()
        actual = df.columns

        # Compare the column structures
        if len(expected) != len(actual):
            return False

        return list(expected) == list(actual)

    def __len__(self) -> int:
        """Return the number of columns in the schema."""
        return len(self.column_names)

column_names instance-attribute

header_rows = field(default_factory=list) class-attribute instance-attribute

groups property

Backward-compatible alias for the first header row.

n_levels property

Number of header levels (header_rows + leaf column_names).

to_multiindex()

Build a pandas MultiIndex from the header structure.

Each level of the MultiIndex corresponds to a header row, with the leaf column_names as the final level.

Returns:

Type Description
Any

pandas.MultiIndex with one level per header row + leaf columns

Raises:

Type Description
ImportError

If pandas is not installed
Source code in xltemplate/schema.py 
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
def to_multiindex(self) -> Any:
    """
    Build a pandas MultiIndex from the header structure.

    Each level of the MultiIndex corresponds to a header row,
    with the leaf column_names as the final level.

    Returns:
        pandas.MultiIndex with one level per header row + leaf columns

    Raises:
        ImportError: If pandas is not installed
    """
    try:
        import pandas as pd
    except ImportError as e:
        raise ImportError(
            "pandas is required to use to_multiindex(). "
            "Install it with: pip install pandas"
        ) from e

    if not self.header_rows:
        # Single-level header: just return Index
        return pd.Index(self.column_names)

    # Build tuples for each column position
    n_cols = len(self.column_names)
    tuples = []

    for col_idx in range(n_cols):
        col_tuple = []
        # Add label from each header row
        for header_row in self.header_rows:
            expanded = self._expand_header_row(header_row)
            col_tuple.append(expanded[col_idx])
        # Add the leaf column name
        col_tuple.append(self.column_names[col_idx])
        tuples.append(tuple(col_tuple))

    return pd.MultiIndex.from_tuples(tuples)

empty_df(n_rows=0)

Create an empty DataFrame with MultiIndex columns matching this schema.

For multi-row headers, the DataFrame will have hierarchical columns that can be accessed by the full path, e.g.: df[("Prevalence by Domain", "Domain: XXX", "N")]

Parameters:

Name Type Description Default
n_rows int

Number of rows to pre-allocate (default: 0)

 0

Returns:

Type Description
Any

A pandas DataFrame with MultiIndex columns matching the schema.

Raises:

Type Description
ImportError

If pandas is not installed
Source code in xltemplate/schema.py 
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
def empty_df(self, n_rows: int = 0) -> Any:
    """
    Create an empty DataFrame with MultiIndex columns matching this schema.

    For multi-row headers, the DataFrame will have hierarchical columns
    that can be accessed by the full path, e.g.:
        df[("Prevalence by Domain", "Domain: XXX", "N")]

    Args:
        n_rows: Number of rows to pre-allocate (default: 0)

    Returns:
        A pandas DataFrame with MultiIndex columns matching the schema.

    Raises:
        ImportError: If pandas is not installed
    """
    try:
        import pandas as pd
    except ImportError as e:
        raise ImportError(
            "pandas is required to use empty_df(). "
            "Install it with: pip install pandas"
        ) from e

    columns = self.to_multiindex()

    if n_rows > 0:
        return pd.DataFrame(index=range(n_rows), columns=columns)
    return pd.DataFrame(columns=columns)

validate_df(df)

Check if a DataFrame's columns match this schema's MultiIndex structure.

Parameters:

Name Type Description Default
df Any

A pandas DataFrame to validate

 required

Returns:

Type Description
bool

True if columns match exactly (all levels, names, and order)
Source code in xltemplate/schema.py 
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
def validate_df(self, df: Any) -> bool:
    """
    Check if a DataFrame's columns match this schema's MultiIndex structure.

    Args:
        df: A pandas DataFrame to validate

    Returns:
        True if columns match exactly (all levels, names, and order)
    """
    if not hasattr(df, "columns"):
        return False

    expected = self.to_multiindex()
    actual = df.columns

    # Compare the column structures
    if len(expected) != len(actual):
        return False

    return list(expected) == list(actual)

Preserving Formulas

By default, write_df() will skip cells containing formulas. This prevents accidental overwriting of calculated fields.

# Formula at C5 will NOT be overwritten
wb.sheet("Data").write_df(df, row=1, col=1, preserve_formulas=True)

# Force overwrite formulas
wb.sheet("Data").write_df(df, row=1, col=1, preserve_formulas=False)

Preserving Formatting

Cell formatting (fonts, colors, borders, fills) is preserved by default when writing values:

# Formatting preserved (default)
wb.sheet("Data").write_value("New Text", row=1, col=1, preserve_format=True)

# Clear formatting
wb.sheet("Data").write_value("New Text", row=1, col=1, preserve_format=False)