Charts

Cached charts

XLSX.Chart — Type

Chart

Metadata for one chart part, plus its series.

Fields

path - package path, e.g. "xl/charts/chart1.xml".
name - part name without extension, e.g. "chart1".
rId - relationship id of the chart within its drawing part, if resolved.
sheet - name of the sheet the chart is anchored to, if resolved.
from, to - anchor cell references as strings, following getImages.
title - chart title text; nothing if auto-generated or deleted.
charttypes - e.g. [:barChart], or several for a combo chart.
series - Vector{ChartSeries} in document order.

source

XLSX.ChartSeries — Type

ChartSeries

A single c:ser element.

categories holds c:cat for category charts and c:xVal for scatter and bubble charts; values holds c:val or c:yVal correspondingly, so the two fields mean the same thing whatever the chart type.

source

XLSX.ChartRef — Type

ChartRef

One cached reference from a chart series: the formula it came from, the number format Excel recorded for it, and the cached values themselves.

Fields

kind::Symbol - one of :num, :str, :multiLvlStr, :numLit, :strLit.
ref::Union{Nothing,String} - the c:f formula (Sheet1!$B$2:$B$9). nothing for literal (c:numLit / c:strLit) series, which have no source range.
format_code::Union{Nothing,String} - number format recorded in the cache.
ptCount::Int - number of points Excel declared, whether or not the cache was read.
data::Vector - cached values, length ptCount, gaps as missing. Empty when the chart was read with cache=false.
errors::Dict{Int,UInt64} - index => error code for cached error values.

Excel only caches the error values #N/A in the chart data cache. Others are written a 0 and become indistinguishable from real zero in the chart cache.

Note

For kind == :multiLvlStr each element of data is itself a level vector, in document order (Excel writes the innermost/leaf level first).

source

XLSX.getCharts — Function

getCharts(xf::XLSXFile; cache=true, get_external_refs=false) -> Vector{Chart}
getCharts(ws::Worksheet; cache=true, get_external_refs=false) -> Vector{Chart}

Return every chart in the file, or every chart anchored to ws, together with the data Excel cached inside each chart part.

Pass cache=false to read metadata only - title, chart types, series names, source formulas, format codes and point counts - and skip the cached values, which is the expensive part for a large chart.

A chart may reference an external workbook, in which case its source formula takes the form [1]Sheet1!$A$1:$A$10, where [1] indexes the workbook's external references. Use get_external_refs=true to substitute the workbook path, as XLSX.getFormula does.

Examples

julia> f = XLSX.readxlsx("sales.xlsx");

julia> c = XLSX.getCharts(f["Summary"])[1];

julia> c.title
"Revenue by region"

julia> c.series[1].values.ref
"Summary!$B$2:$B$5"

julia> XLSX.getChartData(c)

Note

The values returned are Excel's cache, written when the file was last saved by Excel. They may be stale relative to the source, and a file written by a tool that does not populate the cache will return empty series.

Note

Charts using the newer chartEx schema (waterfall, funnel, treemap, sunburst, histogram, box & whisker) are stored under a different namespace and are not read.

source

XLSX.getChart — Function

getChart(ws::Worksheet, name; cache=true, get_external_refs=false) -> Chart
getChart(xf::XLSXFile, name; cache=true, get_external_refs=false) -> Chart

Return a single chart. name may be the part name ("chart1" or "chart1.xml"), the full package path, or the chart's relationship id within its drawing part ("rId1").

See also XLSX.getCharts.

source

XLSX.getChartData — Function

getChartData(c::Chart) -> DataTable
getChartData(ws::Worksheet, name) -> DataTable
getChartData(xf::XLSXFile, name) -> DataTable

Return the cached data of a chart as a DataTable, ready for DataFrame(...) or any other Tables.jl sink.

Categories become the leading column(s). Where every series shares one category reference a single categories column is emitted; otherwise each series contributes its own <series>_x column, which is the usual layout for scatter and bubble charts. Multi-level categories give one column per level, and bubble charts add a <series>_size column. Series of unequal length are padded with missing.

Series with no name in the file - Excel shows these as "Series1", "Series2" in the legend - are labelled by position. No category column is produced when the chart has no c:cat at all: Excel is plotting against an implicit index in that case, and nothing is cached for it.

Examples

julia> using DataFrames

julia> DataFrame(XLSX.getChartData(f["Summary"], "chart1"))
4×3 DataFrame
 Row │ categories  2024      2025
     │ String      Float64   Float64

source

XLSX.getChartRanges — Function

getChartRanges(c::Chart) -> Vector{ChartRanges}
getChartRanges(ws::Worksheet, name) -> Vector{ChartRanges}
getChartRanges(xf::XLSXFile, name) -> Vector{ChartRanges}
getChartRanges(ws::Worksheet) -> Vector{@NamedTuple{chart::String, ranges::Vector{ChartRanges}}}
getChartRanges(xf::XLSXFile) -> Vector{@NamedTuple{chart::String, ranges::Vector{ChartRanges}}}

The worksheet ranges of the source data a chart plots from.

Given a Chart, or a chart name in any of the forms XLSX.getChart accepts, return one entry per series in document order, parallel to c.series. Each entry carries the series idx and name alongside its categories, values and bubble_sizes ranges.

Given no name, return the ranges of every chart on the worksheet or in the workbook, each paired with its chart name, following XLSX.getCharts.

categories holds c:cat or c:xVal and values holds c:val or c:yVal, so the two mean the same thing whatever the chart type, as in XLSX.ChartSeries. bubble_sizes is nothing for every chart type but bubble.

A range is nothing wherever the series has no addressable source: a literal series (c:numLit/c:strLit), a reference to an external workbook, a defined name.

Examples

julia> f = XLSX.readxlsx("sales.xlsx");

julia> r = XLSX.getChartRanges(f["Summary"], "chart1");

julia> r[1].name, r[1].values
("2024", Summary!B2:B5)

julia> XLSX.getdata(f, r[1].values)      # read the live source cells, not the cache
4-element Vector{Any}:
 1250.0
 1310.0
 ⋮

julia> [(x.chart, length(x.ranges)) for x in XLSX.getChartRanges(f)]
2-element Vector{Tuple{String, Int64}}:
 ("chart1", 3)
 ("chart2", 1)

Note

A range records where the chart says its source data came from, which is not necessarily where the values in XLSX.getChartData came from: the cache is a snapshot from the last save, and the cells may have changed since, or the source sheet may have been deleted entirely.

source

XLSX.iserror — Method

iserror(r::ChartRef) -> Vector{Bool}
iserror(r::ChartRef, i::Integer) -> Bool

Report which cached chart values are Excel error values. When Excel writes source data to a chart cache, all error values are written as simple zeros except for #N/A. Therefore, when operating on a chart cache, only #N/A values will return true. All other error values will return false, and are indistinguisable from genuine zero values.