Charts

Six chart types, positioned on the sheet and styled

A chart needs three things: which cells hold the data, where the chart sits, and what type it is. Everything else is refinement.

jet.write_sheet_arrow(
    df.to_arrow(),
    "chart.xlsx",
    charts=[{
        "chart_type": "column",
        "start_row": 1, "start_col": 0,     # data range
        "end_row": 5,   "end_col": 2,
        "from_col": 4,  "from_row": 1,      # where it sits
        "to_col": 12,   "to_row": 15,
        "title": "Monthly Sales",
        "category_col": 0,                  # x-axis labels
    }],
)

Types

column

Vertical bars. Comparing categories.

bar

Horizontal bars. Long category names.

line

Trends over time.

pie

Parts of a whole.

scatter

Relationship between two numbers.

area

A line chart with volume.

Data range

Two equivalent spellings. Use either four separate keys or one tuple:

# Separate
{"start_row": 1, "start_col": 0, "end_row": 10, "end_col": 3}

# Tuple: (start_row, start_col, end_row, end_col)
{"data_range": (0, 0, 9, 3)}

category_col · int

0-based column supplying the x-axis labels, usually the first column.

series_names · list[str]

Overrides the names shown in the legend.

Position

from_col, from_row, to_col and to_row give the rectangle of cells the chart floats over. All four are 0-based.

Place charts to the right of your data, starting a column or two clear of the last one. A chart anchored over occupied cells hides them.

Stacking

stacked · bool

Series sit on top of one another rather than side by side. Works with column, bar, line and area.

percent_stacked · bool

The same, normalized so every category fills the axis. Shows composition rather than magnitude.

With percent_stacked, values run 0.0 to 1.0 rather than 0 to 100. Set axis_min to 0.0 and axis_max to 1.0, not 100.0.

Axis scale

axis_min · float

Lower bound of the value axis.

axis_max · float

Upper bound.

Leave both out and Excel scales automatically.

Start bar and column charts at zero. A truncated axis exaggerates differences, and on bars, where the eye reads length as magnitude, it misleads. Line charts showing a trend are the reasonable exception.

Text styling

Font sizes are in hundredths of a point, so 12pt is 1200.

title · str

title_bold · bool

title_font_size · int

1800 for 18pt.

title_color · str

Hex color, such as "0070C0".

x_axis_title · str

y_axis_title · str

axis_title_bold · bool

axis_title_font_size · int

axis_title_color · str

show_legend · bool

legend_position · "right" | "left" | "top" | "bottom" | "none"

legend_bold · bool

legend_font_size · int

show_data_labels · bool

Prints values on the bars or points.

Chart text colors accept six or eight hex digits. If you give eight, Jetxl drops the alpha, because the chart format Excel uses accepts only six. See Conventions.

Chart styles

chart_style · int

Excel’s built-in style number. 1 to 48 are the legacy set. 101 to 148 are the Office 2013 and later set, written with a legacy fallback of value - 100, so 104 renders modern in current Excel and degrades to style 4 in older versions.

Rough groupings within the legacy range: 1 to 10 colorful, 11 to 16 monochrome, 17 to 32 outlined, 33 to 40 soft, 41 to 48 gradient and flat.

Several charts

charts is a list, so add as many as fit:

charts = [
    {"chart_type": "column", "data_range": (0, 0, 3, 2),
     "from_col": 5, "from_row": 0,  "to_col": 13, "to_row": 15,
     "title": "Revenue & Expenses", "category_col": 0},

    {"chart_type": "line",   "data_range": (0, 0, 3, 3),
     "from_col": 5, "from_row": 17, "to_col": 13, "to_row": 32,
     "title": "Profit Trend", "category_col": 0, "chart_style": 26},
]