When the categories in a chart are themselves layered -- a region that contains months, or a year that contains quarters -- squeezing both columns into a single row of labels leaves the reader guessing which region or which month a data point belongs to. Layered data brings a second problem with it: sales run into the millions while a growth rate sits in the low teens, and on one shared value axis the growth rate flattens into a line pinned to the baseline. Multi-level category labels and the secondary axis solve these two problems respectively. Spire.XLS for JavaScript performs both directly in the browser through WebAssembly, managing input and output files with a virtual file system (VFS) and requiring no backend service.
This article covers two key features:
For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.
Create a Chart with Multi-Level Category Labels
Multi-level category labels are drawn by the category axis, and how many levels that axis has depends on how many columns the series' category labels point at. In the test data the outer labels are already merged by region, so the code only has to make the category labels span both the outer and the inner column. The steps are:
- Load the font and the test data file into the VFS.
- Load the workbook and get the worksheet.
- Add a column chart and add a named sales series.
- Point the category labels at both the region and the month column.
- Turn on multi-level labels for the category axis and save the workbook.
The complete code example below creates a chart with multi-level category labels in React:
function App() {
const createMultiLevelChart = async () => {
// Get the Spire.XLS WASM module
const xlsModule = window.wasmModule?.spirexls;
// Check if the module is ready
if (!xlsModule) {
alert('Spire.Xls is not ready yet');
return;
}
// Load the font and the test data file into the VFS
await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
const inputFileName = 'MultiLevelChartData.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
// Load the workbook and get the first worksheet
const workbook = new xlsModule.Workbook();
workbook.LoadFromFile({ fileName: inputFileName });
const sheet = workbook.Worksheets.get(0);
// Add a column chart
const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
chart.ChartTitle = "Sales";
chart.Legend.Delete();
// Add the sales series and give it a name
const serie = chart.Series.Add({ name: "Sales", serieType: xlsModule.ExcelChartType.ColumnClustered });
serie.Values = sheet.Range.get("C2:C7");
// Point the category labels at both the region and the month column
serie.CategoryLabels = sheet.Range.get("A2:B7");
// Turn on multi-level category labels so each level gets its own row
chart.PrimaryCategoryAxis.MultiLevelLable = true;
// Place the chart on the worksheet
chart.LeftColumn = 5;
chart.TopRow = 1;
chart.RightColumn = 14;
// Save the workbook
const outputFileName = "MultiLevelLabels.xlsx";
workbook.SaveToFile({ fileName: outputFileName });
// Dispose of the workbook object to free resources
workbook.Dispose();
// Read the result file from the VFS and trigger the download
const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
a.click();
URL.revokeObjectURL(url);
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Multi-Level Labels</h1>
<button onClick={createMultiLevelChart}>Start</button>
</div>
);
}
export default App;
How many levels the category axis has is decided by how many columns CategoryLabels points at, so binding a single-column range still yields a single level of labels; MultiLevelLable controls whether those levels are laid out as multiple rows.
After running, the effect of creating a chart with multi-level category labels:
Add a Secondary Axis to the Chart
When two series in the same chart differ sharply in magnitude, sharing one value axis squashes the smaller of the two into a line pinned to the baseline, and its movement can no longer be read. A secondary axis is meant for exactly that case: it gives the series a value axis of its own, so each scale spreads across its own range without interfering with the other. The way to do it is to move the series off the primary axis and draw it as a line -- a line takes up no bar width, so it reads clearly against the column series sharing the same categories. The steps are:
- Load the font and the test data file into the VFS.
- Load the workbook and get the worksheet.
- Add a column chart and add a named sales series.
- Add the growth series as a line.
- Move the growth series to the secondary axis and save the workbook.
The complete code example below adds a secondary axis to the chart in React:
function App() {
const addSecondaryAxis = async () => {
// Get the Spire.XLS WASM module
const xlsModule = window.wasmModule?.spirexls;
// Check if the module is ready
if (!xlsModule) {
alert('Spire.Xls is not ready yet');
return;
}
// Load the font and the test data file into the VFS
await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
const inputFileName = 'MultiLevelChartData.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
// Load the workbook and get the first worksheet
const workbook = new xlsModule.Workbook();
workbook.LoadFromFile({ fileName: inputFileName });
const sheet = workbook.Worksheets.get(0);
// Add a column chart
const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
chart.ChartTitle = "Sales and YoY Growth";
// Add the sales series, which stays on the primary axis
const salesSerie = chart.Series.Add({ name: "Sales", serieType: xlsModule.ExcelChartType.ColumnClustered });
salesSerie.Values = sheet.Range.get("C2:C7");
// Point the category labels at both the region and the month column
salesSerie.CategoryLabels = sheet.Range.get("A2:B7");
// Add the growth series as a line
const growthSerie = chart.Series.Add({ name: "YoY Growth", serieType: xlsModule.ExcelChartType.Line });
growthSerie.Values = sheet.Range.get("D2:D7");
// Move the growth series to the secondary axis so it plots on its own percentage scale
growthSerie.UsePrimaryAxis = false;
// Turn on multi-level category labels
chart.PrimaryCategoryAxis.MultiLevelLable = true;
// Place the chart on the worksheet
chart.LeftColumn = 5;
chart.TopRow = 1;
chart.RightColumn = 14;
// Save the workbook
const outputFileName = "SecondaryAxis.xlsx";
workbook.SaveToFile({ fileName: outputFileName });
// Dispose of the workbook object to free resources
workbook.Dispose();
// Read the result file from the VFS and trigger the download
const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
a.click();
URL.revokeObjectURL(url);
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Secondary Axis</h1>
<button onClick={addSecondaryAxis}>Start</button>
</div>
);
}
export default App;
UsePrimaryAxis = false affects only the series it is set on; the other series stay on the primary axis. The chart gains a second pair of value and category axes as a result, giving it two separate scale ranges. Series.Add takes the series name at the same time, so the legend shows the name passed in rather than an auto-generated "Series 1".
After running, the effect of adding a secondary axis to the chart:
Frequently Asked Questions
Why does the category axis show only one level of labels?
Cause: How many levels the category axis has is decided by the range CategoryLabels points at. If that is a single-column range such as B2:B7, the range holds only one level of category information, and setting PrimaryCategoryAxis.MultiLevelLable to true will still give you one level of labels -- the property controls whether multiple levels are expanded, not whether a level of data is created.
Solution: Point CategoryLabels at a multi-column range that includes the outer labels; the cells the outer labels occupy then need to be merged in the data:
// Cover both columns with the category labels; the outer label cells need merging in the data
serie.CategoryLabels = sheet.Range.get("A2:B7");
Why do the two value axes have different scales?
Cause: The primary and secondary value axes work out their scales independently of each other, and MinValue, MaxValue and MajorUnit on PrimaryValueAxis apply to the primary axis only -- changing them leaves the secondary axis untouched. When the two series differ sharply in magnitude, the range the secondary axis picks for itself is often not a good fit.
Solution: Give the secondary axis its own scale through chart.SecondaryValueAxis:
// Give the secondary axis a 0-20 scale with a major unit of 5
chart.SecondaryValueAxis.MinValue = 0;
chart.SecondaryValueAxis.MaxValue = 20;
chart.SecondaryValueAxis.MajorUnit = 5;
Set the scale after the series has been moved to the secondary axis: while no series uses the secondary axis, the assignment is accepted but never written to the file.
Get a Free License
Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.
