Charts

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)}

int

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

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

bool

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

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

float

Lower bound of the value axis.

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.

str

bool

int

1800 for 18pt.

str

Hex color, such as "0070C0".

str

bool

int

str

bool

"right" | "left" | "top" | "bottom" | "none"

bool

int

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

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},
]

Get started

Guides

Recipes

Reference

About

Types

column

bar

line

pie

scatter

area

Data range

Position

Stacking

Axis scale

Text styling

Chart styles

Several charts

​Types

column

bar

line

pie

scatter

area

​Data range

​Position

​Stacking

​Axis scale

​Text styling

​Chart styles

​Several charts

Types

Data range

Position

Stacking

Axis scale

Text styling

Chart styles

Several charts