When a report is generated in the browser, an order detail sheet often runs to several printed pages. With the default print settings, the header and the key columns disappear from the second page onwards, which makes the columns hard to identify and leaves the page numbers out of step with the rest of the report. Spire.XLS for JavaScript handles the page setup directly in the browser through WebAssembly, managing input and output files in a virtual file system (VFS) with no backend service required.

Using an order detail sheet of 18 columns by 60 rows as the sample, this article covers two core feature points:

• Set the print title rows and columns
• Set the print order

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 initialized.

Set the print title rows and columns

Print titles are what Excel calls the "Rows to repeat at top" and "Columns to repeat at left" options in the Page Setup dialog. Once set, the chosen rows or columns are repeated in the same position on every printed page, so the header is still visible once the table runs onto the second page. Spire.XLS for JavaScript sets them through the PrintTitleRows and PrintTitleColumns properties of PageSetup, which take a row or column reference string. Here is a complete code example showing how to set the print title rows and columns in React:

function App() {
  const setPrintTitles = 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 input file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'OrderDetails.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);

    // Get the page setup of the worksheet
    const pageSetup = sheet.PageSetup;

    // Use rows 1 and 2 as the print title rows, so they repeat at the top of every page
    pageSetup.PrintTitleRows = '$1:$2';

    // Use columns A and B as the print title columns, so they repeat at the left of every page
    pageSetup.PrintTitleColumns = '$A:$B';

    // Save the workbook
    const outputFileName = 'SetPrintTitles.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // 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>Set Print Title Rows and Columns</h1>
      <button onClick={setPrintTitles}>Set Print Title Rows and Columns</button>
    </div>
  );
}

export default App;

Print result of the original file

After running, the effect of setting the print title rows and columns:

Set the print order

When a worksheet is wider than one page and longer than one page at the same time, Excel has to decide which direction the pages advance in. The default, "down, then over", fills a page vertically before moving to the next block of columns on the right; "over, then down" fills a page horizontally first and then moves down. Spire.XLS for JavaScript switches between the two with the Order property of PageSetup. Here is a complete code example showing how to set the print order of a worksheet in React:

function App() {
  const setPrintOrder = 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 input file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'OrderDetails.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);

    // Get the page setup of the worksheet
    const pageSetup = sheet.PageSetup;

    // Set the page order to over-then-down: print a column top to bottom, then move right
    pageSetup.Order = xlsModule.OrderType.OverThenDown;

    // Save the workbook
    const outputFileName = 'SetPrintOrder.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // 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>Set Print Order</h1>
      <button onClick={setPrintOrder}>Set Print Order</button>
    </div>
  );
}

export default App;

After running, the effect of setting the print order to over-then-down:

FAQ

Can I set only the print title rows and not the title columns

Cause: PrintTitleRows and PrintTitleColumns are two independent properties — one controls the rows repeated at the top of each page, the other the columns repeated at the left. Setting only one leaves the other unset: the call is not rejected for the missing half, and no default is filled in.

Solution: Set whichever one you need. To repeat the header rows only, use pageSetup.PrintTitleRows = '$1:$2'.

Does setting the print titles change the data in the worksheet

Cause: Print titles are part of the page setup. They only decide which rows and columns are repeated in the printout; they do not touch cell contents, and they add or remove no rows and columns.

Solution: The data is unaffected. The file measures 62 rows by 18 columns both before and after the setting, with cell-by-cell identical text — the only difference is the print title definition added to the page setup. The worksheet looks exactly the same on screen.

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.

When an order detail sheet or a statement is exported for printing, the page setup decides whether the printed copy is actually readable: whether the table comes out with gridlines and row and column headings, how fine the output is, and whether comments travel with it. These switches are scattered across several tabs of the Excel Page Setup dialog, which makes them tedious to tick one by one.Spire.XLS for JavaScript handles the page setup in the browser through WebAssembly, using a virtual file system (VFS) for the input and the output, with no backend service involved.

This article takes an 18-column by 60-row order detail sheet and covers three feature points:

• Set the print quality and draft quality
• Print gridlines and row and column headings
• Set black and white printing, comments and error values

For installation and project configuration, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has finished initializing.

Set the print quality and draft quality

Print quality sets the number of ink dots per inch in the printed output and takes a plain integer in dpi; draft quality lets the printer run faster with less toner, which suits a proof that only circulates internally. Spire.XLS for JavaScript exposes both through the PrintQuality and Draft members of PageSetup:

• PrintQuality = 72 prints at 72 dpi, a low setting that lets the page come out faster with less consumable
• Draft = true turns on draft quality, trading fine detail for speed

The complete example code is as follows:

function App() {
  const setPrintQuality = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Make sure the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the input file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'OrderDetails.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and take its first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Get the page setup of the worksheet
    const pageSetup = sheet.PageSetup;

    // Set the print quality to 72 dpi
    pageSetup.PrintQuality = 72;

    // Turn on draft quality: speed matters more than fine detail
    pageSetup.Draft = true;

    // Save the workbook
    const outputFileName = 'SetPrintQuality.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release the workbook to free its resources
    workbook.Dispose();

    // Read the result file back from the VFS to 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>Set print quality and draft quality</h1>
      <button onClick={setPrintQuality}>Set print quality and draft quality</button>
    </div>
  );
}

export default App;

The original file as printed:

The effect of the print quality and draft quality settings:

Print gridlines and row and column headings

The gridlines on screen do not travel with the data when the sheet is printed, so a sheet without borders of its own reaches paper as a field of floating values that is hard to check cell by cell. Row and column headings have the same problem: printing them is what lets a printed copy be discussed in the same C5, D7 terms used in Excel. The two boolean members IsPrintGridlines and IsPrintHeadings control them. The complete example code is as follows:

function App() {
  const setGridlinesAndHeadings = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Make sure the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the input file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'OrderDetails.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and take its first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Get the page setup of the worksheet
    const pageSetup = sheet.PageSetup;

    // Print the gridlines along with the data
    pageSetup.IsPrintGridlines = true;

    // Print the row and column headings along with the data
    pageSetup.IsPrintHeadings = true;

    // Save the workbook
    const outputFileName = 'SetGridlinesAndHeadings.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release the workbook to free its resources
    workbook.Dispose();

    // Read the result file back from the VFS to 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>Print gridlines and headings</h1>
      <button onClick={setGridlinesAndHeadings}>Print gridlines and headings</button>
    </div>
  );
}

export default App;

The effect of printing the gridlines and the row and column headings:

Set black and white printing, comments and error values

The remaining three output switches also live on PageSetup, and they decide what colour the printed copy comes out in, whether comments travel with the sheet, and how errors are shown. The three members and their values:

• BlackAndWhite set to true prints in black and white, turning colour content into greyscale, which is especially useful with a mono laser printer
• PrintComments set to InPlace prints each comment box where it sits on the sheet; the comment has to be shown first — one that is not displayed is not printed
• PrintErrors set to NA shows every error value such as #DIV/0! as #N/A on paper, keeping internal formula errors out of sight

The complete example code is as follows:

function App() {
  const setOtherPrintOptions = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Make sure the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the input file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'OrderDetails.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and take its first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Get the page setup of the worksheet
    const pageSetup = sheet.PageSetup;

    // The sample carries one comment, on cell A18
    // Show it first: a comment that is not displayed is not printed
    sheet.Range.get('A18').Comment.Visible = true;

    // Print the worksheet in black and white
    pageSetup.BlackAndWhite = true;

    // Print comments where they appear on the worksheet
    pageSetup.PrintComments = xlsModule.PrintCommentType.InPlace;

    // Print every cell error as #N/A
    pageSetup.PrintErrors = xlsModule.PrintErrorsType.NA;

    // Save the workbook
    const outputFileName = 'SetOtherPrintOptions.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release the workbook to free its resources
    workbook.Dispose();

    // Read the result file back from the VFS to 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>Set black and white, comments and errors</h1>
      <button onClick={setOtherPrintOptions}>Set black and white, comments and errors</button>
    </div>
  );
}

export default App;

The effect of the black and white, comment and error value settings:

FAQ

The sheet still runs to several pages, and lowering the print quality did not help

Cause: print quality and draft quality only decide how fine the output is and how much ink it uses; they do not change the layout of the content, so no setting of theirs moves a page break. Fitting a whole sheet onto one page is a matter of page scaling, which has nothing to do with print quality. Setting both at once is not a conflict — each one simply does its own job.

Solution: use FitToPagesWide and FitToPagesTall to squeeze the worksheet into a given number of pages, where 1 means one page wide and one page tall:

// Scale the worksheet onto one page wide and one page tall
pageSetup.FitToPagesWide = 1;
pageSetup.FitToPagesTall = 1;

Set together with the print quality, both survive:

<pageSetup fitToHeight="1" fitToWidth="1" horizontalDpi="72" verticalDpi="72" orientation="portrait" paperSize="9" />

The worksheet looks exactly the same after setting PrintQuality and Draft

Cause: both act on the printer output only and leave the worksheet's own content and display untouched, so the document looks no different once they are set. That is expected, and does not mean the setting failed to apply.

Solution: the print quality and the draft quality are written into the result file. To confirm they took effect, unzip the result file and look at xl/worksheets/sheet1.xml: the print quality lands on the horizontalDpi and verticalDpi of <pageSetup>, and the draft quality shows up as draft="1" on the same element. This example sets both, giving:

<pageSetup draft="1" horizontalDpi="72" verticalDpi="72" orientation="portrait" paperSize="9" />

Note that what Draft actually does depends on the printer driver — some drivers ignore the switch, and the attribute is written into the file either way.

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.

When you build a presentation, the part that really costs time is rarely the layout — it is deciding what to say. A slide only has room for the essentials: a few short lines, a set of numbers, one chart. The words that actually need to be spoken live in the author's head. The night before the talk, the author reads the whole deck again, slide by slide, trying to remember what each page was meant to say, and then writes it up.

It gets worse when a team is involved. For a product launch, one person writes the content, another builds the slides, and a third delivers the talk. Whoever steps on stage inherits a deck of bullet points with no idea which numbers matter most — so they improvise. Speaker notes exist precisely for this: they never appear on the projector, only in Presenter View, as the author's message to the speaker. In practice, almost nobody has the patience to fill them in page by page.

The PowerPoint AI capability in Spire.Agent.Office lets you describe in plain language what the notes should say and what tone the script should take. The AI agent reads the title and bullet points of every slide and then handles two jobs: writing speaker notes for each slide, and expanding the whole deck into a Word script that can be read aloud directly.

Compared with the Traditional SDK API

This article shows how to use Spire.Agent.Office to generate speaker notes and a script for a PowerPoint deck. Two cases cover the two common patterns — adding notes to an existing deck and turning a report into a Word script:

• Case 1: Generate per-slide speaker notes for a deck
• Case 2: Turn a business review into a ready-to-read Word script

For product installation and SpireToken configuration, please refer to Integrating Spire.Agent.Office in a .NET Project. The examples below assume Spire.Agent.Office is installed and SpireToken is configured.

Case 1: Generate per-slide speaker notes for a deck

The most common situation is a deck that is already final: layout and content stay exactly as they are, and the only thing missing is the prompt for whoever has to speak. A projected slide is built to be read at a glance, so it carries a title and a few bullet points — where the emphasis falls, what is behind each number, and how to move from one page to the next all stay in the author's head. Speaker notes are what carry that across. They are the author's handover to the presenter: what this page is really getting at, what to stress, and how to lead naturally into the next one. This product launch deck has 8 slides, each with a title and a few bullet points, and an empty notes pane from start to finish. The presenter needs one short paragraph per slide — not an essay, but enough to present the deck rather than read it out. Writing those notes by hand is expensive because every slide needs its own thought. Read the page, rebuild the sentence, and calibrate length — too short and it is no help, too long and it turns into reading from a script. Eight slides is tolerable; a fifty-page annual review rarely gets the treatment it deserves.

The example below uses the Spire.Agent.Office agent to read every slide and write speaker notes into the notes pane, ready to be read aloud:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Presentation;

// PowerPoint processing configuration
string inputPath = @"E:\product-launch.pptx";  // Deck to add speaker notes to
string savePath = @"E:\product_result.pptx";  // Result document path

string key = "**************************";  // SpireToken key
string instruction = "Generate speaker notes for each slide of the input PPT file and write them into the notes pane of that slide: " +
"1. Read each slide's title and bullet points and understand what the page is meant to convey; " +
"2. Write one paragraph of speaker notes per slide, in the presenter's voice, ready to be read aloud; " +
"3. Each note should make clear what this page emphasises and lead naturally into the next slide; keep the notes varied in depth — explain the important content in detail and keep transition slides brief; " +
"4. Keep the original slide layout, fonts, colours and page order completely unchanged, and do not add or remove any slides; " +
"Finally, save the output as a PowerPoint file";

// Call the PowerPoint processing function
AIResult result = ExecuteDemoPpt(instruction, inputPath, savePath, key);

// Run the PowerPoint AI processing
static AIResult ExecuteDemoPpt(string instruction, string inputPath, string savePath, string key)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.SpireToken = key;  // Set the SpireToken key

    // Process the PowerPoint document with a Presentation object
    using (Presentation ppt = new Presentation())
    {
        // Load the PowerPoint document from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            ppt.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = ppt.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(ppt, instruction, savePath);
    }
}

The original deck with an empty notes pane The deck after speaker notes were written slide by slide

The generated deck looks identical to the original on the projector; the notes appear only in Presenter View. The speaker opens Presenter View and sees the prompt for each slide below it, ready to deliver a complete product launch in order.

Case 2: Turn a business review into a ready-to-read Word script

The notes pane is good for prompting, but not for reading aloud. Notes are short fragments in a small font at the edge of the screen; looking down at them on stage hurts delivery and invites missed lines. When the occasion is more formal — reporting quarterly results to management, for instance, where every figure has to be explained — the speaker needs a complete, spoken-word script. The medium for that script deserves a word of its own. A presentation is made to be looked at; a script is made to be read. A script needs control over font size, line spacing and sectioning, and it is usually printed and carried into the room. Packing several hundred words of body text onto each slide reads badly — and projecting it hands the audience every line. So this case takes a PPT as input and produces a Word script document as output.

Handling that format change is exactly what Spire.Agent.Office does: the deck is still loaded with Presentation, the AI reads each slide, and it writes the script as a Word file following the instruction. So this case has the AI read the figures page by page and then write the script as sections in the original order: each slide of the original becomes one section of the script document, keeping the original title, with the body holding the full script text.

The example below uses the Spire.Agent.Office agent to interpret the figures page by page and write the script, producing a Word document:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Presentation;

// PowerPoint processing configuration
string inputPath = @"E:\quarterly-review.pptx";  // Input presentation path
string savePath = null;  // Result document path (null here, the output folder below is used instead)
string OutDir = @"E:\output-script";  // Output directory
string key = "**************************";  // SpireToken key
string instruction =
    "Read the input PPT file and write a complete presentation script for the speaker, outputting it as a Word script document: " +
    "1. Read the figures and bullet points on each slide and understand the core conclusion of that page; " +
    "2. Write a complete, conversational script per slide that can be read aloud directly; keep it varied in depth, quote the specific figures on that page and explain what they mean for the business; " +
    "3. Lay the script out as sections in the original slide order, each headed 'Slide N + the original title', with the full script for that page as the body; " +
    "4. Format the body for reading aloud: a clear typeface, no smaller than 14 pt, 1.5 line spacing, comfortable paragraph spacing;";

// Call the PowerPoint processing function
AIResult result = ExecuteDemoPpt(instruction, inputPath, savePath, key, OutDir);

// Run the PowerPoint AI processing
static AIResult ExecuteDemoPpt(string instruction, string inputPath, string savePath, string key, string output)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.WorkDir = output;  // Set the working directory to the output directory
    options.SpireToken = key;  // Set the SpireToken key

    // Process the PowerPoint document with a Presentation object
    using (Presentation ppt = new Presentation())
    {
        // Load the PowerPoint document from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            ppt.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = ppt.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(ppt, instruction, savePath);
    }
}

The generated Word script document

FAQ

Can I use my own model or a private deployment?

Note: By default the AI requests go to Spire's model service. AIOptions also exposes Model, BaseUrl and ApiKey for the model name, the service address and the access key — set these three to route the requests to your own or a third-party inference service.

AIOptions options = new AIOptions();
options.BaseUrl = "baseUrl";
options.Model = "modelName";
options.SpireToken = "*************";
options.ApiKey = "*************";

A long deck is slow to process — and may time out

Cause: Notes and scripts are generated page by page, so the AI reads and writes the document repeatedly and sends the existing content back to the model each time. The more slides there are, and the denser the text on each, the faster token consumption grows and the longer the whole run takes — the default timeout is often not enough.

Solution: Set TimeoutMs explicitly on the AIOptions instance (the unit is milliseconds) so a long deck has enough time. For a 50-slide PPT file, TimeoutMs can be set to 10 minutes (600000).

Getting a SpireToken Key

• Contact sales@e-iceblue.com or visit https://www.e-iceblue.com/TemLicense.html for a trial or commercial API key

Configure it in code:

AIOptions options = new AIOptions();
options.SpireToken = key;

A product list or an order detail table rarely keeps one format per cell. A description note has to be split over several lines, only a few words of a promotion line deserve to stand out, and a stock warning needs to be picked out in red. Doing that by hand means double-clicking every cell, selecting the fragment and adjusting the font one by one, which does not scale past a handful of rows. An HTML string describes exactly this kind of content — one piece of text whose parts are styled differently — and Spire.XLS for JavaScript exposes the HtmlString property, which renders a piece of HTML straight into a cell: line breaks, bold, italic, underline, color and font size all follow the tags, so a batch is one assignment inside a loop. It runs entirely in the browser on WebAssembly, managing input and output files with a virtual file system (VFS) and requiring no backend service.

This article covers two feature points:

• Write HTML text with line breaks into a cell
• Format part of the text in a cell

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 initialized.

Write HTML text with line breaks into a cell

Breaking a line inside a cell normally means pressing Alt+Enter while editing it. When the data comes from a database or a form, that manual step cannot be batched, and the <br> tag in HTML means precisely "break the line here". HtmlString parses <br>, <div> and <p> into line breaks inside the cell, so a single string written into one cell becomes several lines. The steps are as follows:

1. Load the font into the VFS.
2. Create a workbook, take its first worksheet and widen column A.
3. Build an HTML string for each entry, splitting the parts with <br>.
4. Write them into the cells of column A with HtmlString.
5. Save the workbook.

The complete code example below shows how to write HTML text with line breaks into an Excel cell in React:

function App() {
  const setMultilineText = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a workbook, take the first worksheet and widen column A so a single line stays on one line
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);
    sheet.Range.get('A1:A5').ColumnWidth = 30;

    // Each description has three parts, separated by <br> inside the same cell
    const notes = [
      'Bluetooth 5.3 dual pairing<br>30-hour battery<br>USB-C fast charge',
      'Silent micro switches<br>Built-in 800 mAh battery<br>2.4G wireless',
      'Dual-mic noise cancelling<br>6 hours per charge<br>Magnetic charging case',
      '1080P resolution<br>780 g lightweight<br>Single USB-C cable',
      'Wooden enclosure<br>Bluetooth 5.0<br>Remote control included',
    ];

    // Write the HTML strings into column A, three lines inside one cell
    notes.forEach((html, index) => {
      sheet.Range.get(`A${index + 1}`).HtmlString = html;
    });

    // Save the workbook
    const outputFileName = 'MultilineHtmlText.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Release the resources
    workbook.Dispose();

    // Read the result file out of 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>Excel HTML rich text</h1>
      <button onClick={setMultilineText}>Multiline HTML text</button>
    </div>
  );
}

export default App;

After running, the effect of writing HTML text with line breaks into a cell:

Format part of the text in a cell

Text inside one cell does not have to share one format. Within a single run of text, only part of it usually needs to stand out, while the rest can stay at the regular weight. HtmlString supports the <b>, <i> and <u> tags, and takes color and font size in a style attribute on a <span>. Every fragment wrapped in a tag becomes its own run of formatting without affecting the others, and the tags themselves never appear in the cell. The steps are as follows:

1. As in the previous section, load the font into the VFS, create a workbook, take the first worksheet and widen column A.
2. Mark the text to emphasize with <b>, <i> and <u>, and wrap the text to highlight in <span style="color:#C00000;font-size:14pt">, giving the color as a hexadecimal value and the size in points.
3. Write the strings into column A; the rest of the text in the same cell keeps its original format.
4. Save the workbook.

The complete code example below shows how to format part of the text in an Excel cell in React:

function App() {
  const setPartialFormatting = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a workbook, take the first worksheet and widen column A so a single line stays on one line
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);
    sheet.Range.get('A1:A5').ColumnWidth = 30;

    // Each part of a promotion line gets its own <b>, <i>, <u> or styled <span>;
    // bold, italic, underline, color and font size apply only to the wrapped text
    const promotions = [
      '<b>Save 50 over 300</b>, <i>three days only</i>, <u>limit 2 per customer</u>, <span style="color:#C00000;font-size:14pt">only 3 left</span>',
      '<b>Second one half price</b>, <i>this week only</i>, <u>not combinable</u>',
      '<b>100 off instantly</b>, <i>ends at midnight</i>, <u>free carry case</u>, <span style="color:#C00000;font-size:14pt">low stock</span>',
      '<b>Trade-in bonus 200</b>, <i>until month end</i>, <u>old device required</u>',
      '<b>Buy one get one</b>, <i>500 sets only</i>, <u>gift chosen at random</u>, <span style="color:#E36C0A;font-size:14pt">restocking</span>',
    ];

    // Write the HTML strings into column A
    promotions.forEach((html, index) => {
      sheet.Range.get(`A${index + 1}`).HtmlString = html;
    });

    // Save the workbook
    const outputFileName = 'PartialFormattingHtml.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Release the resources
    workbook.Dispose();

    // Read the result file out of 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>Excel HTML rich text</h1>
      <button onClick={setPartialFormatting}>Partial formatting</button>
    </div>
  );
}

export default App;

After running, the effect of formatting part of the text in a cell as bold, italic, underlined, colored or larger:

FAQ

Does the HTML parser swallow runs of spaces?

Cause: A browser collapses consecutive spaces into one when it renders HTML, which makes it easy to assume Spire does the same and to reach for   to build any padding.

Solution: It does not. A B is still five spaces when read back out of the cell, and leading spaces survive as well.   works too, but ordinary alignment does not need it.

Does writing rich text wipe out the alignment the cell already had?

Cause: HtmlString does change the cell's font properties, so it is fair to wonder whether it resets the alignment set earlier along with them.

Solution: The alignment is untouched. Set Style.HorizontalAlignment first and write the HtmlString afterwards, and the alignment reads back unchanged — setting the format before the content is safe.

const cell = sheet.Range.get('A1');
cell.Style.HorizontalAlignment = xlsModule.HorizontalAlignType.Center;
cell.HtmlString = '<b>Centered</b>';

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.

A report whose header and total rows carry nothing but data and borders is hard to scan. Tinting those cells is the usual answer, but a flat color only goes so far: sometimes a light-to-dark sweep is what marks the important row, and sometimes a light dot or stripe texture sets the totals apart without burying the numbers underneath it. Spire.XLS for JavaScript supports both gradient and pattern fills through the Interior object, performing these operations 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 core feature points:

• Apply a gradient fill to cells
• Apply a pattern fill to cells

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 initialized.

Apply a gradient fill to cells

A gradient fill takes three steps: name the fill type, give the two ends their colors, and say how the transition runs. All three land on the same Interior object -- FillPattern picks the fill type, Gradient.ForeColor and Gradient.BackColor give the two ends their colors, and Gradient.TwoColorGradient takes two parameters: the direction comes from GradientStyleType, which covers horizontal, vertical, both diagonals and the spreads that radiate out from the centre or the corner, while the shading variant comes from GradientVariantsType and runs from ShadingVariants1 to ShadingVariants4. The same pair of colors reads completely differently once the direction and variant change. The steps are:

1. Load the font and the sample data into the VFS.
2. Create a Workbook, load it with LoadFromFile, then get the first worksheet with Worksheets.get(0).
3. Take the header range with Range.get and set Style.Interior.FillPattern to ExcelPatternType.Gradient.
4. Give the two ends of the gradient their colors with Gradient.ForeColor and Gradient.BackColor.
5. Set the direction and the shading variant with Gradient.TwoColorGradient.
6. Save the workbook with SaveToFile.

The complete code example below shows how to apply a gradient fill to Excel cells in React:

function App() {
  const applyGradient = 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 sample data into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FillSample_EN.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);

    // Take the header row, A1:E1
    const header = sheet.Range.get('A1:E1');

    // Set the fill type to a gradient
    header.Style.Interior.FillPattern = xlsModule.ExcelPatternType.Gradient;

    // Give the two ends of the gradient their colors
    header.Style.Interior.Gradient.ForeColor = xlsModule.Color.FromArgb(255, 255, 255);
    header.Style.Interior.Gradient.BackColor = xlsModule.Color.FromArgb(79, 129, 189);

    // Two-color gradient: the first parameter sets the direction, the second the shading variant
    header.Style.Interior.Gradient.TwoColorGradient(
      xlsModule.GradientStyleType.Horizontal,
      xlsModule.GradientVariantsType.ShadingVariants1,
    );

    // Save the workbook
    const outputFileName = 'GradientFill.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Apply Gradient Fill</h1>
      <button onClick={applyGradient}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of applying a gradient fill to the header row:

Apply a pattern fill to cells

A pattern fill is not one effect but a family of built-in styles: dots, horizontal and vertical stripes, diagonals, chequerboards and more all live under ExcelPatternType, several dozen values in all, with densities running from 5% to 75%. Compared with a gradient, a pattern carries far less visual weight, so it can be laid across a whole row without getting in the way of reading, which makes it a better fit for marking a total row or setting a block of data apart.

Setting one works the same way as a gradient: Interior.FillPattern picks the style, and then two colors are needed. There is one easy trap here -- Interior.Color sets the color of the pattern itself, while Interior.PatternColor sets the backdrop underneath it, so the two property names mean the opposite of what they suggest. Give the dark color to Interior.Color and the light one to Interior.PatternColor, and the dots float on a pale ground. The steps are:

1. Load the font and the sample data into the VFS.
2. Create a Workbook, load it with LoadFromFile, then get the first worksheet with Worksheets.get(0).
3. Take the total row with Range.get and set Style.Interior.FillPattern to the wanted ExcelPatternType value.
4. Give the pattern its color with Interior.Color and the backdrop its color with Interior.PatternColor.
5. Save the workbook with SaveToFile.

The complete code example below shows how to apply a pattern fill to Excel cells in React:

function App() {
  const applyPattern = 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 sample data into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FillSample_EN.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);

    // Take the total row, A5:E5
    const total = sheet.Range.get('A5:E5');

    // Pick a built-in pattern, here a 12.5% gray dot pattern
    total.Style.Interior.FillPattern = xlsModule.ExcelPatternType.Percent125Gray;

    // Interior.Color sets the pattern itself, Interior.PatternColor the backdrop behind it
    total.Style.Interior.Color = xlsModule.Color.FromArgb(192, 80, 77);
    total.Style.Interior.PatternColor = xlsModule.Color.FromArgb(255, 242, 204);

    // Save the workbook
    const outputFileName = 'PatternFill.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Apply Pattern Fill</h1>
      <button onClick={applyPattern}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of applying a pattern fill to the total row:

FAQ

The fill is set, so why did the cell's font style stay the same?

Cause: Style.Interior only handles the fill. Fill, font and borders are three independent members of Style, and touching one leaves the other two alone -- darken the background and the text stays the same black, which turns the cell into a smudge; recolor Style.Font and the background does not follow either.

Solution: Set the fill and the font separately, and usually change both together when contrast matters:

// Fill
sheet.Range.get('A1:E1').Style.Interior.Color = xlsModule.Color.FromArgb(79, 129, 189);

// Font
sheet.Range.get('A1:E1').Style.Font.Color = xlsModule.Color.FromArgb(255, 255, 255);
sheet.Range.get('A1:E1').Style.Font.IsBold = true;

Style.Font also carries FontName, Size, IsItalic and Underline, none of which interfere with the fill.

After filling a cell, how do I take the color off again?

Cause: Changing the color only swaps one fill for another; it does not remove the fill. The fill type is still a value other than None, so the pattern or the background color is still written into the cell's style.

Solution: Set the fill type back to ExcelPatternType.None, and the pattern and colors stop applying along with it:

sheet.Range.get('A5:E5').Style.Interior.FillPattern = xlsModule.ExcelPatternType.None;

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.

The most direct way to get data into Excel is to assign one cell at a time, which means ten rows take ten loop iterations. But data usually arrives in JavaScript already in blocks -- a list from an API, a table rendered on the page, a computed result set -- and there is no reason to break an array apart just to feed it back cell by cell. The InsertArray method of Spire.XLS for JavaScript takes a whole array at once; combined with a starting row and column and a write direction, a single call drops an entire column or row of data into the worksheet. Spire.XLS for JavaScript performs these operations 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 core feature points:

• Import a one-dimensional array into a worksheet
• Import a two-dimensional data table into a worksheet

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 initialized.

Import a one-dimensional array into a worksheet

InsertArray is split into several overloads by data type: stringArray for text, intArray for integers and doubleArray for decimals. That split is not needless fussiness -- Excel treats cells according to their data type. Numbers sit right-aligned and can take part in sums and charts directly, while a number written as text sits left-aligned and a formula referencing it only yields 0. Scores, amounts and quantities -- anything that will be used in a calculation -- belong in a numeric overload.

The remaining parameters decide where the data lands: firstRow and firstColumn give the starting position, with rows and columns both counted from 1, and isVertical picks the direction the array spreads -- false lays it along a row, true down a column. The same ['Jan', 'Feb', 'Mar'] becomes either a header row spanning A1 to C1 or a column of data running down A1 to A3. The steps are:

1. Load the font into the VFS.
2. Create a Workbook and get the first worksheet with Worksheets.get(0).
3. Write a header row horizontally with the stringArray overload of InsertArray.
4. Write the name column vertically with stringArray and the score column with intArray.
5. Auto-fit the columns with AllocatedRange.AutoFitColumns, then save the workbook with SaveToFile.

The complete code example below shows how to import a one-dimensional array into a worksheet in React:

function App() {
  const importArray = 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 into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a new workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);

    // Write a header row horizontally: isVertical false spreads the array across A1:B1
    sheet.InsertArray({
      stringArray: ['Name', 'Score'],
      firstRow: 1,
      firstColumn: 1,
      isVertical: false,
    });

    // Write the name column vertically: isVertical true spreads the array down A2:A4
    sheet.InsertArray({
      stringArray: ['Alice', 'Bob', 'Carol'],
      firstRow: 2,
      firstColumn: 1,
      isVertical: true,
    });

    // Write the score column: numbers go through the intArray overload, so the cells hold
    // numbers rather than text
    sheet.InsertArray({
      intArray: [92, 85, 78],
      firstRow: 2,
      firstColumn: 2,
      isVertical: true,
    });

    // Auto-fit the columns to their content
    sheet.AllocatedRange.AutoFitColumns();

    // Save the workbook
    const outputFileName = 'ImportArray.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Import 1-D Array</h1>
      <button onClick={importArray}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of importing a one-dimensional array into a worksheet:

Import a two-dimensional data table into a worksheet

A data table is naturally a two-dimensional array -- a header row first, then one record per row, as in [['Name', 'Subject', 'Score'], ['Alice', 'Math', 92]]. Importing a two-dimensional data table into Excel means splitting it by column and writing each column as a one-dimensional array on its own: write the header across one row, then write the body column by column, text columns with stringArray and numeric ones with intArray. A column holds a single data type throughout, so each call only has to pick one form. When the data arrives in batches, read LastRow to find the last row currently in use and write the next batch from the row after it. The steps are:

1. Load the font into the VFS.
2. Create a Workbook and get the first worksheet with Worksheets.get(0).
3. Write the header across the first row with the stringArray overload of InsertArray.
4. Split the body by column and write each one vertically with stringArray or intArray.
5. When a second batch arrives, locate its starting row with LastRow and write the columns the same way.
6. Auto-fit the columns with AllocatedRange.AutoFitColumns, then save the workbook with SaveToFile.

The complete code example below shows how to import a two-dimensional data table into a worksheet in React:

function App() {
  const importDataTable = 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 into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // The two-dimensional data to import, with the header as its first row
    const rows = [
      ['Name', 'Subject', 'Score'],
      ['Alice', 'Math', 92],
      ['Bob', 'Chinese', 85],
    ];

    // Create a new workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);

    // Write one column: text columns go through stringArray, numeric ones through intArray
    const writeColumn = (values, firstRow, firstColumn) => {
      const isNumeric = values.every((value) => typeof value === 'number');
      if (isNumeric) {
        sheet.InsertArray({ intArray: values, firstRow, firstColumn, isVertical: true });
      } else {
        sheet.InsertArray({ stringArray: values, firstRow, firstColumn, isVertical: true });
      }
    };

    // Write the header across the first row
    sheet.InsertArray({
      stringArray: rows[0],
      firstRow: 1,
      firstColumn: 1,
      isVertical: false,
    });

    // Split the body by column and write each one vertically, starting at row 2
    const body = rows.slice(1);
    for (let column = 0; column < rows[0].length; column++) {
      writeColumn(body.map((row) => row[column]), 2, column + 1);
    }

    // A second batch arrives: use LastRow to find where the existing data ends
    const nextBatch = [
      ['Carol', 'English', 78],
      ['Dave', 'Physics', 91],
    ];
    const startRow = sheet.LastRow + 1;
    for (let column = 0; column < rows[0].length; column++) {
      writeColumn(nextBatch.map((row) => row[column]), startRow, column + 1);
    }

    // Auto-fit the columns to their content
    sheet.AllocatedRange.AutoFitColumns();

    // Save the workbook
    const outputFileName = 'ImportDataTable.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Import 2-D Data Table</h1>
      <button onClick={importDataTable}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of importing a two-dimensional data table into a worksheet:

FAQ

How do I put a date into a cell?

Cause: dateTimeArray cannot be used to write a JavaScript Date -- it lands as 0001/1/1. Assigning cell by cell through Range.DateTimeValue does work, but it only accepts a Date object; hand it a string and it reports Value is not a Date.

Solution: For a whole column, the least fuss is to write the date as a yyyy-mm-dd string and pass it to stringArray. The saved cell holds a real date value -- reading NumberValue back gives the date serial number, such as 46037 -- and no number format has to be set:

sheet.InsertArray({
  stringArray: ['2026-01-15', '2026-02-20'],
  firstRow: 1,
  firstColumn: 1,
  isVertical: true,
});

When only one cell needs setting, assign it directly instead, taking care to pass a Date object rather than a string:

sheet.Range.get('A1').DateTimeValue = new Date(Date.UTC(2026, 0, 15));

Do empty values in the array break the write?

Cause: No. null, undefined and the empty string all write an empty cell. InsertArray places each value by its index, so what the value happens to be makes no difference: later elements do not shift, and nothing throws.

Solution: Nothing extra is needed; pass the array as it is. The line below fills A1:D1, with D still landing in the fourth column:

sheet.InsertArray({
  stringArray: ['A', null, '', 'D'],
  firstRow: 1,
  firstColumn: 1,
  isVertical: false,
});

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.

In HR and finance departments, distributing payslips is a monthly task that is small in substance yet remarkably tedious. A payroll sheet is usually an Excel table arranged by rows — each row is one employee, while the columns hold base salary, performance bonus, overtime pay, social insurance deduction, housing fund, individual income tax and net pay. But when the sheet reaches employees, each person should only see their own row. So "one sheet" must first be "split into N copies", and then each copy sent out one by one.

The traditional approach is copy-and-paste row by row inside the spreadsheet: create a new file, paste the header once, paste one employee's row into it, save as PDF, rename it, then loop to the next person. A team of a few dozen people means a few dozen repetitions; at a hundred people it is not only slow but highly prone to missed sends, wrong sends, or sending one person's salary to someone else — and payroll is exactly the information that must never be wrong.

The Excel AI capability of Spire.Agent.Office lets you describe the splitting rules in natural language. The AI agent understands the request and completes two kinds of work automatically: splitting one payroll sheet into individual payslip PDFs, one per employee, and applying a unified layout template to each payslip and encrypting it per person, producing files that are ready to distribute — along with a distribution ledger you can consult at any time.

Comparison with Traditional SDK API

This article shows how to use Spire.Agent.Office to split an Excel payroll sheet into individual payslip PDFs. Two cases demonstrate the two typical uses — "splitting the payroll sheet directly" and "applying a template, encrypting and distributing":

• Case 1: Batch-split a payroll sheet into per-employee payslip PDFs
• Case 2: Apply a payslip template and encrypt to produce distributable files

For product installation and SpireToken configuration, please refer to Integrating Spire.Agent.Office in a .NET Project. The examples below assume that Spire.Agent.Office has been installed and SpireToken has been configured.

Case 1: Batch-split a payroll sheet into per-employee payslip PDFs

This is the most direct scenario: all you have is a raw payroll sheet, with no extra template file. The first row is the header and each following row holds one employee's pay data for the month. The task is to split the sheet by row, generate a payslip for each employee that contains only their own information, and save it named "employeeID_name" for later distribution or archiving.

The pain of doing this by hand is the repetition: the more employees there are, the more copy-and-paste cycles are needed, and the further you get the easier it is for rows to slip — leaving the previous person's net pay in the next person's file. That is a class of error that is hard to notice and serious in its consequences.

The example below uses the Spire.Agent.Office agent to read every employee record in the payroll sheet through a natural-language instruction, split it row by row into individual payslips and export them as PDFs:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// Excel-related configuration
string inputPath = @"E:\payroll.xlsx";  // path to the payroll sheet to split
string savePath = null;  // result path (null here: the output folder below will be used)
string OutDir = @"E:\output";  // output directory (the split payslip PDFs are saved here)
string key = "**************************";  // SpireToken key
string instruction =
"Read the employee payroll data in the payroll sheet and generate an individual payslip for each employee:" +
  "1. The first row is the header and each following row is one employee; extract the employee ID, name, department and each pay item row by row;" +
  "2. At the top of each payslip show the title 'Payslip' and the pay month, and list that employee's ID, name and department, then list each pay item in the source column order: base salary, performance bonus, overtime pay, social insurance deduction, housing fund, individual income tax and net pay;" +
  "3. Generate a separate PDF file for each employee, named 'employeeID_name' and saved to the output directory;" +
  "4. Keep the borders and column widths of the original header, right-align the amount columns and keep two decimal places;" +
  "5. Make the page size fit the payslip content, with tight margins and a height that adapts to the content, leaving no large blank areas;" +
  "Save the final result as a PDF file";

// Call the Excel document processing function
AIResult result = ExecuteDemoExcel(instruction, inputPath, savePath, key, OutDir);


// Execute Excel AI processing
static AIResult ExecuteDemoExcel(string instruction, string inputPath, string savePath, string key, string output)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.WorkDir = output;  // set the working directory to the output directory
    options.SpireToken = key;  // set the SpireToken key

    // Process the Excel document using a Workbook object
    using (Workbook workbook = new Workbook())
    {
        // Load the Excel payroll sheet from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            workbook.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = workbook.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(workbook, instruction, savePath);
    }
}

The Excel payroll sheet to be split The individual payslip PDFs produced by the split

Case 2: Apply a payslip template and encrypt to produce distributable files

When a company distributes payslips, two more requirements usually come up: a unified layout and confidentiality. Companies normally have a fixed payslip template — with a company letterhead, the pay month, the order of pay items and explanatory text at the bottom. At the same time, salary is sensitive information: sending a plain-text PDF directly means that once it is forwarded or misdelivered, the employee's pay is exposed.

The safer approach is to apply the same template to every payslip and then set an individual open password on each employee's PDF (for example, generated from their own employee ID), so that only that person can open and view their payslip.

But once the passwords "all differ", a new management problem appears: who records them, and where do you look them up afterwards. Each password is built from "employee ID + 4 random digits", and those random digits cannot be reconstructed from the file name or any other information. After dozens or hundreds of files have gone out, if an employee reports that a file will not open, the only fallback is to dig out the original payroll sheet and try one by one. So the complete loop of encrypted distribution should also produce a distribution ledger — recording file names, employee details and open passwords in one-to-one correspondence, for later distribution, reconciliation and archive lookups.

This case takes the payslip template as the input document, while the employee payroll data is passed in as an attachment. The AI needs to fill the attachment data into the corresponding positions of the template and output that ledger alongside the encrypted PDFs.

The example below uses the Spire.Agent.Office agent to read the employee data in the attached payroll sheet through a natural-language instruction, fill it into the payslip template row by row, generate a separate PDF for each employee, encrypt it with "employee ID + 4 random digits", and additionally produce an Excel-format "Payslip Distribution Ledger":

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// Path to the employee payroll data file (passed in as an attachment)
string[] attachmentPaths = new string[]
{
    @"E:\payroll.xlsx"
};

// Excel-related configuration
string inputPath = @"E:\template-payslip.xlsx";  // path to the payslip template file
string savePath = null;  // result path (null here: the output folder below will be used)
string OutDir = @"E:\output-encrypted";  // output directory (encrypted payslip PDFs and the distribution ledger are saved here)
string key = "**************************";  // SpireToken key
string instruction =
    "Read the employee payroll data from the attached 'payroll.xlsx' and process it as follows:" +
    "1. The first row is the header and each following row is one employee; read the employee ID, name, department and each pay item row by row;" +
    "2. Fill each row of data into the corresponding positions of the payslip template, keeping the template's company letterhead, notes and item order unchanged;" +
    "3. Generate a separate PDF file for each employee, named 'employee ID + employee name' and saved to the output directory;" +
    "4. Set an open password on each PDF as 'employee ID + 4 random digits'; it only restricts opening the document and does not affect printing or copying;" +
    "5. Also generate an Excel-format 'Payslip Distribution Ledger' in the output directory, listing the pay month, employee ID, name, department, payslip file name and the corresponding open password in each row, for later distribution and archive lookups;" +
    "6. Keep the layout, fonts and column widths of the template;" +
    "Save the final result as a PDF file";

// Call the Excel document processing function
AIResult result = ExecuteDemoExcel(instruction, inputPath, savePath, key, OutDir, attachmentPaths);


// Execute Excel AI processing
static AIResult ExecuteDemoExcel(string instruction, string inputPath, string savePath, string key, string output, string[] attachmentPaths)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.WorkDir = output;  // set the working directory to the output directory
    options.SpireToken = key;  // set the SpireToken key

    // Process the Excel document using a Workbook object
    using (Workbook workbook = new Workbook())
    {
        // Load the payslip template from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            workbook.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = workbook.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(workbook, instruction, savePath, attachmentPaths);
    }
}

The payslip template file Template-applied, encrypted payslip PDFs and the payslip distribution ledger

FAQ

The split payslips have slipped rows or misaligned data

Cause: The payroll sheet contains blank rows, merged cells or subtotal rows, so when the AI reads by row it counts non-employee rows as records and everything after them shifts.

Solution: Make sure the first row of the payroll sheet is the header and every row after it corresponds to exactly one employee, with no blank or subtotal rows in between. If the table structure is complex, state the rule in the instruction, for example "treat a row as an employee record only when the 'Employee ID' column is not empty".

An encrypted payslip will not open even for me

Cause: Each payslip's password is generated as "employee ID + 4 random digits", so it differs per person, and the random digits cannot be worked out on your own.

Solution: State the password rule explicitly in the instruction (for example, "the password is the employee ID plus 4 random digits") and make sure the generated passwords are recorded in the distribution ledger — once a random number is lost, the file can no longer be opened. Before distributing, try opening one payslip using a record from the ledger to verify.

Getting a SpireToken Key

• Contact sales@e-iceblue.com or visit https://www.e-iceblue.com/TemLicense.html to obtain a trial/commercial API key

Configure it in your code:

AIOptions options = new AIOptions();
options.SpireToken = key;

A chart plots averages, and an average says nothing about how steady the underlying readings were. The same rising line can sit on top of six near-identical measurements or six wildly scattered ones -- the curve alone will not tell you which. Error bars supply that missing layer: a short segment drawn at each data point marks the spread or uncertainty around it, so the reader can see how much confidence each turn of the line deserves. Percentage error bars derive their range from a fixed proportion of the value, while standard error bars derive theirs from the data itself, and the two suit different situations. 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:

• Add percentage error bars to a line chart
• Add standard error bars to a column chart

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.

Add percentage error bars to a line chart

A percentage error bar takes the value of each data point as its base and works out the range from the given percentage. Set it to 10, and a point valued at 4.0 gets an error amount of 0.4; which side of the data point that length is drawn on is decided by a separate parameter. This kind of error bar expresses a tolerance -- a production target allowed to drift by a tenth, or an instrument reading with a fixed relative precision. Error bars belong to a series, so the series object has to be fetched first and its ErrorBar method called on it. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the first worksheet.
3. Add a line chart whose data range is the planned output column.
4. Use the month column as the category labels.
5. Add a percentage error bar to the series, setting its direction and amount, then save the workbook.

The complete code example below shows how to add percentage error bars to a line chart in React:

function App() {
  const addPercentageErrorBar = 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 = 'ErrorBarChartData.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 line chart whose data range is the planned output column
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.Line });
    chart.ChartTitle = "Planned Output";
    chart.ChartTitleArea.IsBold = true;
    chart.ChartTitleArea.Size = 12;
    chart.DataRange = sheet.Range.get("B1:B7");
    chart.SeriesDataFromRange = false;

    // Set the chart position on the worksheet
    chart.TopRow = 9;
    chart.BottomRow = 26;
    chart.LeftColumn = 1;
    chart.RightColumn = 9;

    // Get the series and use the month column as its category labels
    const serie = chart.Series.get(0);
    serie.CategoryLabels = sheet.Range.get("A2:A7");

    // Add a percentage error bar to the series: plus direction, 10% of the value
    serie.ErrorBar({
      bIsY: true,
      include: xlsModule.ErrorBarIncludeType.Plus,
      type: xlsModule.ErrorBarType.Percentage,
      numberValue: 10.0,
    });

    // Save the workbook
    const outputFileName = "PercentageErrorBar.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>Percentage Error Bars</h1>
      <button onClick={addPercentageErrorBar}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of adding percentage error bars to a line chart:

Add standard error bars to a column chart

A standard error bar takes no percentage from you; it computes the standard error of the series itself, so a series with scattered values automatically gets longer error bars. That is the division of labour between the two kinds: the standard error bar describes how steady the data is, the percentage one describes how far you are willing to let it drift. Column charts are usually there to compare several groups side by side, and hanging a standard error bar on each group shows at a glance which one's readings cluster more tightly. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the first worksheet.
3. Add a column chart covering both the planned and the actual output columns.
4. Add a standard error bar to each of the two series.
5. Save the workbook.

The complete code example below shows how to add standard error bars to a column chart in React:

function App() {
  const addStandardErrorBar = 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 = 'ErrorBarChartData.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 covering both the planned and the actual output columns
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
    chart.ChartTitle = "Planned vs. Actual Output";
    chart.ChartTitleArea.IsBold = true;
    chart.ChartTitleArea.Size = 12;
    chart.DataRange = sheet.Range.get("B1:C7");
    chart.SeriesDataFromRange = false;

    // Set the chart position on the worksheet
    chart.TopRow = 9;
    chart.BottomRow = 26;
    chart.LeftColumn = 1;
    chart.RightColumn = 9;

    // Get the first series and use the month column as its category labels
    const serie = chart.Series.get(0);
    serie.CategoryLabels = sheet.Range.get("A2:A7");

    // Draw a standard error bar on the first series, minus direction
    serie.ErrorBar({
      bIsY: true,
      include: xlsModule.ErrorBarIncludeType.Minus,
      type: xlsModule.ErrorBarType.StandardError,
      numberValue: 0.3,
    });

    // Draw a standard error bar on the second series as well, both directions
    const serie2 = chart.Series.get(1);
    serie2.ErrorBar({
      bIsY: true,
      include: xlsModule.ErrorBarIncludeType.Both,
      type: xlsModule.ErrorBarType.StandardError,
      numberValue: 0.5,
    });

    // Save the workbook
    const outputFileName = "StandardErrorBar.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>Standard Error Bars</h1>
      <button onClick={addStandardErrorBar}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of adding standard error bars to a column chart:

FAQ

Which directions do error bars support?

The direction comes from the include parameter of ErrorBar, which takes three values from ErrorBarIncludeType:

// Draw it in both directions
serie.ErrorBar({
  bIsY: true,
  include: xlsModule.ErrorBarIncludeType.Both,
  type: xlsModule.ErrorBarType.Percentage,
  numberValue: 10.0,
});

Direction only decides which side of the data point the error bar is drawn on, and does not change its length. The same parameters give the same error amount in all three directions.

Which error amount types are supported?

The error amount type comes from the type parameter of ErrorBar, which takes five values from ErrorBarType:

The first four take their amount in numberValue:

serie.ErrorBar({
  bIsY: true,
  include: xlsModule.ErrorBarIncludeType.Both,
  type: xlsModule.ErrorBarType.StandardDeviation,
  numberValue: 2,
});

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.

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:

• Create a chart with multi-level category labels
• Add a secondary axis to the chart

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:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Add a column chart and add a named sales series.
4. Point the category labels at both the region and the month column.
5. 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:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Add a column chart and add a named sales series.
4. Add the growth series as a line.
5. 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.

In a sales report, a grade sheet, or a metrics dashboard, how values compare matters more than the values themselves. Inserting a chart for every column makes the worksheet crowded, while data bars, color scales, and icon sets show magnitude right inside the cells—through bar length, color intensity, and icon shape—without consuming extra rows or columns. All three belong to Excel conditional formatting, found under Home → Conditional Formatting in the Excel UI.Spire.XLS for JavaScript performs this work 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 three key features:

• Apply Data Bars to a Cell Range
• Apply Color Scales to a Cell Range
• Apply Icon Sets to a Cell Range

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is already installed and the WebAssembly module has been initialized.

Apply Data Bars to a Cell Range

Data bars draw a horizontal colored band inside each cell, and the length of the band is proportional to how large the value is compared with the rest of the selected range. With Spire.XLS for JavaScript, ConditionalFormats.Add creates a conditional format collection, AddRange binds it to a range, and AddCondition returns the condition object; setting FormatType to ConditionalFormatType.DataBar produces data bars, whose fill color is controlled by DataBar.BarColor. The steps are as follows:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Call ConditionalFormats.Add to create a conditional format, and bind the data range with AddRange.
4. Call AddCondition to add a condition, set FormatType to DataBar, and set the bar color.
5. Save the workbook.

The complete code example below applies data bars to a sales figures table in React:

function App() {
  const applyDataBars = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether 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 = 'SalesData.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);

    // Select the data range that receives the data bars
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a data bar condition and set the bar color
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.DataBar;
    format.DataBar.BarColor = xlsModule.Color.get_CadetBlue();

    // Save the workbook
    const outputFileName = "ApplyDataBars.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // 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>Apply Data Bars</h1>
      <button onClick={applyDataBars}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of applying data bars to a cell range:

Apply Color Scales to a Cell Range

A color scale uses shading to express magnitude: the larger a cell's value is within the range, the closer its color sits to the high end of the scale. Color scales are added through the same ConditionalFormats API as data bars—the only difference is setting FormatType to ConditionalFormatType.ColorScale. No color arguments are required; when none are specified, the result is a two-color scale that takes orange at the range minimum and pale yellow at the maximum, with intermediate values shaded proportionally between the two. The steps are as follows:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Call ConditionalFormats.Add to create a conditional format, and bind the data range with AddRange.
4. Call AddCondition to add a condition, and set FormatType to ColorScale.
5. Save the workbook.

The complete code example below applies color scales to a sales figures table in React:

function App() {
  const applyColorScales = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether 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 = 'SalesData.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);

    // Select the data range that receives the color scales
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a color scale condition; colors transition with the values
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.ColorScale;

    // Save the workbook
    const outputFileName = "ApplyColorScales.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // 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>Apply Color Scales</h1>
      <button onClick={applyColorScales}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of applying color scales to a cell range:

Apply Icon Sets to a Cell Range

An icon set places a different icon in each cell according to the band its value falls into—for example, red, yellow, and green traffic lights for low, medium, and high. It uses the same API: set FormatType to ConditionalFormatType.IconSet and pick an icon style with IconSet.IconSetType; the example uses IconSetType.ThreeTrafficLights1. The steps are as follows:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Call ConditionalFormats.Add to create a conditional format, and bind the data range with AddRange.
4. Call AddCondition to add a condition, set FormatType to IconSet, and specify the icon set type.
5. Save the workbook.

The complete code example below applies icon sets to a sales figures table in React:

function App() {
  const applyIconSets = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether 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 = 'SalesData.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);

    // Select the data range that receives the icon sets
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add an icon set condition and set the icon style to three traffic lights
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.IconSet;
    format.IconSet.IconSetType = xlsModule.IconSetType.ThreeTrafficLights1;

    // Save the workbook
    const outputFileName = "ApplyIconSets.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // 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>Apply Icon Sets</h1>
      <button onClick={applyIconSets}>Start</button>
    </div>
  );
}

export default App;

An icon set divides the range into bands, so the same icon covers a different span of values in different ranges.

After running, the effect of applying icon sets to a cell range:

FAQ

Why do the text cells in the target range get no data bars?

Cause: A data bar expresses how large a value is relative to the rest of the range, so only numeric cells are shaded and text cells inside the range are skipped. Even when the range covers the product-name column or the header row, those cells show no bars—the result still covers the numeric area alone.

Solution: This is the expected behavior and needs no workaround; just keep the range limited to the numeric area. If the numeric area itself shows no bars either, check that the range passed to AddRange matches where the data actually is.

Can the color and border of a data bar be customized?

Cause: A data bar's appearance is controlled by the DataBar property of the condition object. The fill color comes from DataBar.BarColor; setting only FormatType without BarColor yields the default blue bars. Data bars have no border by default, so assigning BarBorder.Color on its own has no effect.

Solution: Set the border type through DataBar.BarBorder.Type first, then set the border color—the two go together:

// Set the border type first so that the border color takes effect
format.DataBar.BarBorder.Type = xlsModule.DataBarBorderType.DataBarBorderSolid;
format.DataBar.BarBorder.Color = xlsModule.Color.get_Red();

// Fill color of the bar
format.DataBar.BarColor = xlsModule.Color.get_GreenYellow();

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.