3. Python environment setup

A CAE report automation pipeline is only as reliable as the interpreter and package set beneath it. Before a single workbook is written, you need a Python installation that is isolated from other projects, a pinned set of libraries that can read solver output and write formatted Excel files, and a repeatable way to verify that every dependency imports correctly. This chapter walks through that setup concretely: choosing an interpreter, creating a virtual environment, installing the core stack, and validating it against a small but realistic CAE data flow.

Choosing and Installing the Interpreter

Use a 64-bit CPython build in the 3.10–3.12 range. These versions have mature wheels for the scientific stack and are well supported by Excel-writing libraries. On Windows, install from python.org and tick Add python.exe to PATH during setup; on Linux, prefer your distribution's python3 plus the venv module, or use pyenv if you need multiple versions side by side. Avoid the Microsoft Store build for engineering work because its file-system virtualization can confuse tools that scan directories for solver result files.

Confirm the interpreter before doing anything else:

python --version
python -c "import sys; print(sys.executable, sys.maxsize > 2**32)"

The second command should print a path and True, confirming a 64-bit build. A 32-bit interpreter will fail to allocate memory for large result arrays and is a common cause of cryptic MemoryError exceptions later.

Creating an Isolated Virtual Environment

Never install the CAE stack into the system interpreter. Create a project-local environment so that package upgrades for one report pipeline cannot break another:

mkdir cae_reports
cd cae_reports
python -m venv .venv
# Windows
.venv\Scripts\activate
# Linux / macOS
source .venv/bin/activate
python -m pip install --upgrade pip

After activation, python -c "import sys; print(sys.prefix)" should point inside .venv. If it still points at the system prefix, activation failed silently — a frequent issue when shell execution policy blocks the Windows activation script. In that case run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned in PowerShell, or invoke the interpreter directly via .venv\Scripts\python.exe.

The Core Package Set

The stack divides into four functional layers, each with a distinct role in the report pipeline. The diagram below shows how data moves from solver output through parsing and aggregation into the Excel writer.

Install the stack with a single pinned command so the environment is reproducible:

pip install "numpy>=1.26,<2.0" "pandas>=2.1,<3.0" \
            "openpyxl>=3.1,<4.0" "xlsxwriter>=3.2,<4.0"

Pin upper bounds deliberately. NumPy 2.x changed several dtype-promotion rules that can silently alter how stress values are cast when read from text files, and pandas 3.x introduces copy-on-write defaults that change in-place assignment behavior. For a report pipeline that must produce identical numbers run after run, bounded ranges are safer than open-ended installs.

Reading Solver Output: pandas and numpy

Most CAE post-processing begins with a text or CSV export. A typical nodal result file has a header block followed by columns of node ID, coordinates, and result quantities. The pattern below reads such a file, skips the header, and coerces types explicitly:

import pandas as pd
import numpy as np

df = pd.read_csv(
    "results/nodal_stress.csv",
    skiprows=4,
    names=["node_id", "x", "y", "z", "von_mises", "max_principal"],
    dtype={"node_id": "int64"},
)
df[["x", "y", "z", "von_mises", "max_principal"]] = \
    df[["x", "y", "z", "von_mises", "max_principal"]].apply(pd.to_numeric, errors="coerce")

print(df.shape, df["von_mises"].max())

Two details matter here. First, errors="coerce" turns malformed tokens into NaN rather than raising, which lets you count and report bad rows instead of aborting the whole run. Second, checking df.shape against the expected node count is the cheapest early warning that the parser misread the header offset — a mismatch of exactly the header line count is the classic symptom of a wrong skiprows value.

Writing the Workbook: openpyxl versus xlsxwriter

Both libraries write .xlsx files, but they serve different purposes and should not be used interchangeably within one workbook. openpyxl can both read and modify existing workbooks, which makes it the right choice when you must open a template, fill cells, and preserve existing formatting. xlsxwriter is write-only but produces richer native charts, conditional formats, and data validation, and it is faster for large sheets built from scratch.

A practical split: use xlsxwriter to generate the dashboard workbook from a blank file, and use openpyxl only when a stakeholder-supplied template must be populated. Do not attempt to open an xlsxwriter-produced file with openpyxl and re-save it in the same run — the two libraries maintain separate internal models and the round trip can drop chart definitions.

import xlsxwriter

wb = xlsxwriter.Workbook("reports/summary.xlsx")
ws = wb.add_worksheet("Summary")
bold = wb.add_format({"bold": True, "bg_color": "#DCE9F5", "border": 1})

ws.write_row(0, 0, ["Load Case", "Peak von Mises (MPa)", "Allowable (MPa)", "Margin"], bold)
ws.write_row(1, 0, ["LC01", 182.4, 235.0, 235.0 / 182.4 - 1.0])
ws.set_column(0, 3, 22)
wb.close()

The margin column uses the standard definition allowable / applied − 1, so a positive value indicates a passing case. Keep this convention consistent across every sheet and chart in the report; mixing ratio definitions is one of the most common sources of contradictory dashboard results.

Verifying the Environment End to End

Installation is not complete until a small synthetic dataset flows through all four layers. Create a three-row CSV, run the read-aggregate-write sequence, and confirm the output file opens with the expected values. A useful check is to assert the computed peak against a hand-calculated value:

peak = df["von_mises"].max()
assert abs(peak - 182.4) < 1e-6, f"unexpected peak: {peak}"
print("environment OK, peak =", peak)

If this assertion fails, the problem is almost always upstream of the Excel writer: a wrong skiprows, a locale-dependent decimal separator, or a column-order mismatch. If the assertion passes but the workbook is empty, check that wb.close() was actually reached — an unclosed xlsxwriter workbook produces a zero-byte or truncated file with no error message.

With the interpreter isolated, the four package layers pinned, and the end-to-end assertion passing, the environment is ready to accept real solver output. The next step is structuring that output into the tabular forms the dashboard will consume.