When a workbook holds several worksheets, default names and look-alike tabs make it hard to tell one sheet from another. Renaming “Sheet1” to “Quarterly Sales” tells a colleague at a glance what the sheet contains; marking the sales, inventory and summary sheets with red, green and light blue tabs removes the need to open each one while switching. Both the name and the tab color live inside the workbook itself, so they survive distribution and archiving without any external notes. Spire.XLS for JavaScript performs these operations 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 features:

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


Rename Excel Worksheets

A worksheet name is the first identifier a reader sees after opening the file. With default names such as “Sheet1” and “Sheet2”, even a well-filled workbook has to be opened sheet by sheet before the right table turns up; with a business name such as “Quarterly Sales”, the tab strip itself becomes a table of contents, and the label is preserved when the file is printed or exported. The sample workbook contains sales, inventory and summary data in that order. This section renames the first worksheet.

How to rename a worksheet and what it produces:

Call Result
workbook.Worksheets.get(0) Takes the first worksheet by index
sheet.Name Reads back the current worksheet name
sheet.Name = 'Quarterly Sales' Assigns the new worksheet name
workbook.SaveToFile Saves the renamed workbook to the VFS

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook() and load the input file with workbook.LoadFromFile.
  3. Take the first worksheet with workbook.Worksheets.get(0).
  4. Assign the new name to sheet.Name.
  5. Save the workbook with workbook.SaveToFile, then release the workbook object with workbook.Dispose.

The complete example below renames an Excel worksheet in React:

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

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

    // Assign the new worksheet name
    sheet.Name = 'Quarterly Sales';

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

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back 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>Rename Excel Worksheets</h1>
      <button id="rename-worksheet" onClick={renameWorksheet}>Rename Worksheet</button>
    </div>
  );
}

export default App;

The workbook after renaming the first worksheet:

The workbook after renaming the first worksheet

Set Excel Worksheet Tab Colors

Even with descriptive names, the tab strip remains a row of similarly sized text, which has to be scanned sheet by sheet. Tab colors add a second identifier: red for sales, green for inventory and light blue for summary. Color is perceived before the text is read, so a reader can locate a group directly, and both the color and the name stay in the workbook when the file is shared. The three worksheets in the sample correspond to the three groups, so this section assigns each one a different tab color.

How to set worksheet tab colors and what it produces:

Call Result
workbook.Worksheets.get(0) Takes the worksheet whose tab will be colored
sheet.TabColor = xlsModule.Color.get_Red() Turns the worksheet tab red
sheet.TabColor = xlsModule.Color.get_Green() Turns the worksheet tab green
sheet.TabColor = xlsModule.Color.get_LightBlue() Turns the worksheet tab light blue
xlsModule.Color.FromArgb(255, 255, 193, 7) Builds a custom RGB color for TabColor

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook() and load the input file with workbook.LoadFromFile.
  3. Take the three worksheets in order with workbook.Worksheets.get(0), get(1) and get(2).
  4. Assign xlsModule.Color.get_Red(), get_Green() and get_LightBlue() to each worksheet's TabColor.
  5. Save the workbook with workbook.SaveToFile, then release the workbook object with workbook.Dispose.

The complete example below sets worksheet tab colors in React:

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

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Set the tab colours to red, green and light blue in worksheet order
    workbook.Worksheets.get(0).TabColor = xlsModule.Color.get_Red();
    workbook.Worksheets.get(1).TabColor = xlsModule.Color.get_Green();
    workbook.Worksheets.get(2).TabColor = xlsModule.Color.get_LightBlue();

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

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back 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>Set Excel Worksheet Tab Colors</h1>
      <button id="set-tab-color" onClick={setTabColor}>Set Tab Colors</button>
    </div>
  );
}

export default App;

The workbook after setting tab colors for the three worksheets:

The workbook after setting tab colors for the three worksheets

Set a Custom Tab Color

When the built-in colors do not fit a report's palette, Color.FromArgb builds a custom color. Its four arguments are the alpha, red, green and blue channels, each from 0 to 255:

// Set the first worksheet tab to amber
workbook.Worksheets.get(0).TabColor = xlsModule.Color.FromArgb(255, 255, 193, 7);

FAQ

Worksheet rename reports a duplicate name

Cause: Worksheet names in a workbook must be unique, and the comparison ignores case. Assigning a name already used by another worksheet, such as “Sales” and “sales”, triggers the limit.

Solution: Before renaming, iterate over workbook.Worksheets, read each Name, and confirm the target name is free before assigning it to sheet.Name.

A sheet name containing a slash throws an error

Cause: Excel does not allow worksheet names to contain :, \, /, ?, *, [ or ]; Spire.XLS throws when such a name is assigned.

Solution: Replace the illegal characters with a hyphen or underscore before assigning the value to sheet.Name.

What happens when a worksheet name exceeds 31 characters

Cause: Excel limits worksheet names to 31 characters. Spire.XLS does not report an error; it truncates the name to 31 characters before writing it.

Solution: Check name.length before assigning and shorten the name when it is longer than 31 characters. The truncation happens during assignment, so comparing the saved name with the intended one will reveal the difference.

Getting a worksheet by name throws Arg_NullReferenceException

Cause: workbook.Worksheets.get('name') returns a worksheet only when the name matches exactly. A missing name, a trailing space, or a worksheet that has already been renamed all throw this exception.

Solution: Iterate over workbook.Worksheets and print each Name to confirm the current name before calling get. When the name comes from external input, trim leading and trailing spaces first.


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.

UOS is the spreadsheet format of the UOF specification, saved with the .uos extension, and office suites that implement the specification can open it directly. When an Excel report is handed to such an environment and .xlsx is not supported, the workbook has to be converted to UOS first; a UOS file received in return has to be converted back to Excel before it can be edited in the usual tools. Conversion only changes the saved format: formulas, merged areas, number formats and text formatting travel with the file. Spire.XLS for JavaScript performs the conversion in the browser through WebAssembly, and the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

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.


Convert Excel to UOS

Saving an Excel workbook as UOS lets an office suite that only recognizes the UOF format open the report directly. Only the saved format changes; worksheet names, merged areas and number formats stay as they are. Formula cells need one extra step: they have to be calculated before saving so that both the formula and its result are written.

How to convert Excel to UOS and what it produces:

Call Result
window.spire.FetchFileToVFS Loads the font file and the Excel file into the virtual file system
workbook.LoadFromFile({ fileName }) Loads the Excel workbook
workbook.CalculateAllValue() Calculates every formula in the workbook
xlsModule.FileFormat.UOS Sets the target format to UOS
workbook.SaveToFile({ fileName, fileFormat }) Writes the workbook to the virtual file system in UOS format

The steps are:

  1. Load the font file and the Excel test file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook() and load the test file with workbook.LoadFromFile.
  3. Calculate the formulas with workbook.CalculateAllValue().
  4. Call workbook.SaveToFile with fileFormat set to xlsModule.FileFormat.UOS, writing ExcelToUos.uos.
  5. Release the workbook with workbook.Dispose, then read the result out of the VFS and trigger the download.

The following is a complete code example that shows how to convert an Excel workbook to UOS in React:

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

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Calculate the formulas so the formula cells carry their results
    workbook.CalculateAllValue();

    // Save as UOS, with the format given by fileFormat
    const outputFileName = 'ExcelToUos.uos';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.UOS });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/octet-stream' });
    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>Convert Between Excel and UOS in React</h1>
      <button id="excel-to-uos" onClick={excelToUos}>Convert Excel to UOS</button>
    </div>
  );
}

export default App;

The UOS file produced from the Excel workbook:

The UOS file produced from the Excel workbook


Convert UOS to Excel

Converting a received UOS file back to Excel is the reverse of the same process. UOS and Excel store the same workbook content; loading restores worksheet names, cell data and styles, and formulas come back together with their cached results, so nothing has to be recalculated before saving. The converted file can then be opened in Excel or an online spreadsheet tool for further editing, summarizing or printing.

How to convert a UOS file to Excel and what it produces:

Call Result
window.spire.FetchFileToVFS Loads the font file and the UOS file into the virtual file system
workbook.LoadFromFile({ fileName }) Reads the UOS file and recognizes it as a workbook
xlsModule.FileFormat.Version2010 Sets the target format to Excel 2010 or later
workbook.SaveToFile({ fileName, fileFormat }) Writes the workbook to the virtual file system in Excel format
window.dotnetRuntime.Module.FS.readFile Reads the result out of the VFS and triggers the download

The steps are:

  1. Load the font file and the UOS test file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook() and load the UOS file with workbook.LoadFromFile.
  3. Call workbook.SaveToFile with fileFormat set to xlsModule.FileFormat.Version2010, writing UosToExcel.xlsx.
  4. Release the workbook with workbook.Dispose, then read the result out of the VFS and trigger the download.

The following is a complete code example that shows how to convert a UOS file back to Excel in React:

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

    // Load the UOS file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Save as Excel, with the version given by fileFormat
    const outputFileName = 'UosToExcel.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.Version2010 });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back 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>Convert Between Excel and UOS in React</h1>
      <button id="uos-to-excel" onClick={uosToExcel}>Convert UOS to Excel</button>
    </div>
  );
}

export default App;

The workbook produced from the UOS file:

The workbook produced from the UOS file


FAQ

Why are the formula cells empty in the UOS file after conversion

Cause: During export, the formula and the value of each cell are taken from the workbook's current calculation results. If CalculateAllValue has not been called after loading, the formula cells are still uncalculated and are written as empty cells, so neither the formula nor the value survives.

Solution: Call workbook.CalculateAllValue() after workbook.LoadFromFile and before workbook.SaveToFile, then save with FileFormat.UOS; the formula cells will carry both the formula and the calculated result.

Can I save as UOS by using only the .uos extension without fileFormat

Cause: fileFormat is an optional parameter of SaveToFile. When it is omitted, the save format is inferred from the extension of the output file name, and a recognizable extension selects that format.

Solution: Yes. Name the output file ExcelToUos.uos and call workbook.SaveToFile({ fileName: outputFileName }); the file is still written as UOS. Setting fileFormat explicitly is safer, because a missing or misspelled extension will not silently produce another format.


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 chart makes a trend visible at a glance, yet it does not give the exact numbers: the height of a column has to be read against the value axis, and the estimate drifts as more series crowd in. A data table lists the category names, series names and values directly below the chart, so readers do not have to go back to the source cells or add a data label to every point. As part of the chart, the table scales and moves with it and stays in place when the chart is exported as an image or printed. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

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.


Add a data table to the chart

Adding a data table to a chart solves the problem of numbers living apart from the picture. A default column chart only draws the columns; the values stay in the source worksheet and the reader has to move between the two. With several series, a data table sits directly under the category axis, one row per series, its columns aligned with the categories and its rows matching the colours in the legend, so the trend and the figures appear in one place. The sample workbook already holds a quarterly sales table and a column chart; what it lacks is the table below the chart.

Turning the data table on:

Syntax Result
workbook.Worksheets.get(0) Takes the first worksheet of the workbook
sheet.Charts.get(0) Takes the first chart on the worksheet
chart.HasDataTable = true Shows a data table below the chart with the categories, series names and values
workbook.SaveToFile Saves the workbook with its data table into the VFS

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
  3. Take the first chart on the worksheet with sheet.Charts.get(0).
  4. Set chart.HasDataTable to true, which adds the data table below the chart.
  5. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to add a data table to an Excel chart in React:

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

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

    // Turn on the chart's data table, which lists the categories and series values below the plot
    chart.HasDataTable = true;

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

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back 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>Add a Data Table to an Excel Chart</h1>
      <button id="add-data-table" onClick={addDataTable}>Add a Data Table to the Chart</button>
    </div>
  );
}

export default App;

The sales report after turning on the chart data table:

The sales report after turning on the chart data table


Adjust the data table borders, legend keys and text formatting

Adjusting the data table borders, legend keys and text formatting keeps the table readable when there are many series and lets it blend into the layout of a report. A newly opened data table carries an outer frame and both horizontal and vertical separators; with three rows of values stacked together it looks like a dense grid. Dropping the outer frame and the vertical lines leaves only the horizontal ones, so the eye follows a row straight to its numbers without being cut off by vertical rules. Turning on the legend keys then puts a colour swatch of the matching series in front of every series name, so readers no longer have to compare each row with the legend. By default the text draws on the chart's own settings -- ten points, black, not bold -- which does not stand out against dark columns and gridlines; dropping the size to nine points fits more numbers on a line, while bolding the text and switching it to a deeper blue ties it to the title and the header row. These settings apply to the data table alone; the chart title, axis labels and legend keep their own fonts.

Borders, legend keys and text formatting:

Syntax Result
chart.DataTable Takes the data table object of the chart
table.HasBorders = false Removes the outer frame of the data table
table.HasVertBorder = false Removes the vertical separators between columns
table.HasHorzBorder = true Keeps the horizontal separators between rows
table.ShowSeriesKeys = true Shows a legend key in front of every series name
table.FontName = 'Arial' Sets the data table text to Arial
table.Size = 9 Sets the data table text to 9 points
table.IsBold = true Bolds the data table text
table.Color = xlsModule.Color.FromArgb(255, 28, 68, 118) Sets the data table text to a deep blue

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS, then load the workbook with workbook.LoadFromFile.
  2. Take the first worksheet with workbook.Worksheets.get(0) and the first chart with sheet.Charts.get(0).
  3. Set chart.HasDataTable to true to open the data table, then take the object with chart.DataTable.
  4. Set table.HasBorders and table.HasVertBorder to false and table.HasHorzBorder to true, so only the horizontal lines remain.
  5. Set table.ShowSeriesKeys to true to show the legend key before each series name.
  6. Set table.FontName, table.Size and table.IsBold to choose the typeface, the size and the bold weight.
  7. Build a deep blue with xlsModule.Color.FromArgb and assign it to table.Color.
  8. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to adjust the data table borders, legend keys and text formatting in React:

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

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

    // Turn on the data table and take its object
    chart.HasDataTable = true;
    const table = chart.DataTable;

    // Drop the outer frame and the vertical lines, keeping only the horizontal ones
    table.HasBorders = false;
    table.HasVertBorder = false;
    table.HasHorzBorder = true;

    // Show the legend key in front of each series name
    table.ShowSeriesKeys = true;

    // Set the data table font, size, bold weight and text colour
    table.FontName = 'Arial';
    table.Size = 9;
    table.IsBold = true;
    table.Color = xlsModule.Color.FromArgb(255, 28, 68, 118);

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

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back 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>Add a Data Table to an Excel Chart</h1>
      <button id="data-table-formatting" onClick={customizeDataTable}>Adjust the Borders, Legend Keys and Text</button>
    </div>
  );
}

export default App;

The data table after adjusting the borders, legend keys and text formatting:

The data table after adjusting the borders, legend keys and text formatting


FAQ

What is the difference between a data table and data labels?

Cause: Data labels sit next to each data point and show only that point's value. A data table sits below the chart, one row per series, and gives the category names and all the values together.

Solution: Use data labels when each point needs a callout without a table taking up space; use a data table when readers should take in a whole set of values at once. Both can be present on the same chart and turned on or off as needed.

How do I remove the data table from a chart?

Cause: The data table is controlled by a single switch on the chart; once it is on, it does not disappear with a change of source data or chart type.

Solution: Set chart.HasDataTable to false and save the workbook; the data table below the chart is removed.

How can the data table use a different font from the rest of the chart?

Cause: The data table carries its own text formatting. Until it is set, it follows the rest of the chart's text; once set, only the data table changes.

Solution: Set FontName, Size, IsBold and Color on chart.DataTable. These properties act on the data table only and leave the chart title, axis labels and legend untouched.

What is the legend key in a data table?

Cause: A legend key is the small colour swatch shown in front of a series name, matching the series' marker in the legend. It is off by default, so the data table shows plain text.

Solution: Set table.ShowSeriesKeys to true; a swatch then appears in front of every series name, so a reader can match a row of the data table with the legend directly.


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.

Text boxes in a worksheet carry notices, reminders or a single conclusion, yet their default look is plain: small black text on white, framed by a thin border that blends into the grid. Enlarging and bolding the font and laying down a light fill make the text rise above the table; inner margins and text wrapping keep long sentences off the border and inside the box; removing the border lets a text box read as a quiet annotation. These changes can be applied in code in one pass instead of one box at a time. Spire.XLS for JavaScript runs in the browser on WebAssembly and handles the input and output files through a virtual file system (VFS), with no backend service required.

This article covers two core feature points:

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.


Adjust the Font, Background and Border of Text Boxes

The default 11-point black text on a white background lacks contrast against the sheet. A larger size, a bold weight, another color and a pale fill lift a notice above the grid; clearing a border that competes with the cell lines makes the text read as if written straight on the page, which suits footnotes and corner notes. The same two kinds of change can be applied to one text box or split across different boxes, and this feature point adjusts the font, background and border of text boxes.

Syntax Result
workbook.CreateFont() Creates a font object for the text of a text box
font.FontName, font.Size, font.IsBold, font.Color Set the font name, size, bold weight and text color
xlsModule.RichTextShape.Convert(textBox.RichText) Returns the rich text object of the text box
richText.SetFont(0, textBox.Text.length - 1, font) Applies the font to the whole text of the text box
textBox.Fill.FillType = xlsModule.ShapeFillType.SolidColor Switches the text box background to a solid fill
textBox.Fill.ForeColor Sets the color of the solid fill
textBox.Line.Weight = 0 Sets the border weight to zero, so no outline is drawn

The steps are:

  1. Take the first text box with sheet.TextBoxes.get(0).
  2. Create a font object with workbook.CreateFont() and set its name, size, bold weight and color through FontName, Size, IsBold and Color.
  3. Get the rich text object with xlsModule.RichTextShape.Convert(textBox.RichText), then call SetFont(0, textBox.Text.length - 1, font) to apply the font to the whole text.
  4. Set textBox.Fill.FillType to xlsModule.ShapeFillType.SolidColor, then name the fill color with textBox.Fill.ForeColor.
  5. Take the fourth text box with sheet.TextBoxes.get(3) and set textBox.Line.Weight to 0 to remove its border.
  6. Save the workbook with workbook.SaveToFile.

The code below is the complete example of adjusting the font, background and border of a text box:

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

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

    // take the first text box and create a font with its name, size, bold weight and color
    const textBox = sheet.TextBoxes.get(0);
    const font = workbook.CreateFont();
    font.FontName = 'Arial';
    font.Size = 14;
    font.IsBold = true;
    font.Color = xlsModule.Color.FromArgb(255, 28, 68, 118);

    // apply the font to all the text in the text box
    const richText = xlsModule.RichTextShape.Convert(textBox.RichText);
    richText.SetFont(0, textBox.Text.length - 1, font);

    // fill the text box background with a solid color
    textBox.Fill.FillType = xlsModule.ShapeFillType.SolidColor;
    textBox.Fill.ForeColor = xlsModule.Color.FromArgb(255, 232, 240, 250);

    // take the fourth text box and set the border weight to 0, which removes the border
    const borderBox = sheet.TextBoxes.get(3);
    borderBox.Line.Weight = 0;

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

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back 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>Format Excel Text Boxes</h1>
      <button onClick={adjustTextAppearance}>Adjust the Font, Background and Border of a Text Box</button>
    </div>
  );
}

export default App;

The notice board after adjusting the font, background and border:

The notice board after adjusting the font, background and border


Set the Inner Margins and Text Wrapping of Text Boxes

Text pressed against the border is hard to read, especially on a dark fill. Inner margins hold the text away from each edge and can be set separately for the four sides. A long sentence in a narrow box stays on one line by default and runs past the border, while text wrapping folds it onto the next line. The sample leaves about 1 cm on the left and right and 0.4 cm at the top and bottom, and aligns the text to the top left so the shift caused by the margins is visible. This feature point sets the inner margins and text wrapping of text boxes.

Syntax Result
textBox.InnerLeftMargin, InnerRightMargin, InnerTopMargin, InnerBottomMargin Set the distance between the text and the left, right, top and bottom border
xlsModule.CommentHAlignType.Left Aligns the text to the left inside the box
xlsModule.CommentVAlignType.Top Aligns the text to the top inside the box
textBox.IsWrapText = true Folds the text onto the next line when it reaches the box width

The steps are:

  1. Take the second text box with sheet.TextBoxes.get(1).
  2. Set InnerLeftMargin, InnerRightMargin, InnerTopMargin and InnerBottomMargin in turn to leave room on all four sides.
  3. Set HAlignment to xlsModule.CommentHAlignType.Left and VAlignment to xlsModule.CommentVAlignType.Top, so the text starts from the top-left corner.
  4. Take the third text box with sheet.TextBoxes.get(2) and set IsWrapText to true to turn on text wrapping.
  5. Save the workbook with workbook.SaveToFile.

The code below is the complete example of setting the inner margins and text wrapping of a text box:

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

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

    // take the second text box and set the space between the text and its four borders
    const textBox = sheet.TextBoxes.get(1);
    textBox.InnerLeftMargin = 1;
    textBox.InnerRightMargin = 1;
    textBox.InnerTopMargin = 0.4;
    textBox.InnerBottomMargin = 0.4;

    // align the text to the top left so the effect of the margins is visible
    textBox.HAlignment = xlsModule.CommentHAlignType.Left;
    textBox.VAlignment = xlsModule.CommentVAlignType.Top;

    // take the third text box and turn on wrapping, so text folds at the box width
    const wrapBox = sheet.TextBoxes.get(2);
    wrapBox.IsWrapText = true;

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

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back 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>Format Excel Text Boxes</h1>
      <button onClick={setTextLayout}>Set the Inner Margins and Text Wrapping of a Text Box</button>
    </div>
  );
}

export default App;

The notice board after setting the inner margins and text wrapping:

The notice board after setting the inner margins and text wrapping


FAQ

How do I get a text box by name?

Cause: Getting text boxes by index means remembering their order in the collection, and inserting or removing a text box changes that order.

Solution: Use sheet.TextBoxes.get("TextBox 2") to get a text box by name.

How do I format only part of the text in a text box?

Cause: The text of a text box is stored as rich text, and SetFont applies to a character range; anything outside the range keeps its original format.

Solution: Get the rich text object with xlsModule.RichTextShape.Convert(textBox.RichText), then call richText.SetFont(startIndex, endIndex, font) with the range you want. For example, SetFont(0, 3, font) applies to the first four characters only.

Can the border of a text box have another color or a dashed style?

Cause: Besides its weight, the Line object also provides color and dash style settings.

Solution: Set the border color with textBox.Line.ForeColor, the weight with textBox.Line.Weight, and the dash style with textBox.Line.DashStyle, whose value is a member of xlsModule.ShapeDashLineStyleType, such as Dashed for a dashed line.

What if the text does not wrap as expected?

Cause: Text only wraps when IsWrapText is true, and when the text box is wide enough for the whole sentence no line break appears either.

Solution: Confirm that textBox.IsWrapText = true has been set, and check that the width of the text box is smaller than the length of the sentence. When several lines are needed, leave enough height in the text box as well.


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 cell whose formatting is already right often has to be reused elsewhere: the unit price column carries a money format, a light blue fill and thin borders, while the amount column on the same row is still plain. Copying a cell format hands the look the source cell has already settled on to the target cell -- number format, font, fill, border and alignment in one go -- and leaves the data in the target cell untouched. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

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.


Copy the whole cell format

Every part of a cell's look is kept in a style object of its own. Handing the source cell's style object straight to the target cell carries the number format, font, fill, border and alignment across in one go, while the data in the target cell stays where it is. The sample workbook's unit price column already carries a money format and a light blue fill, and the amount column is still plain, so copying the former onto the latter row by row shows the difference.

Assigning the source cell's whole Style to the target cell's Style completes the copy:

Syntax Result
sheet.Range.get('C2').Style Takes the whole style object of the source cell (the unit price column)
sheet.Range.get('E2').Style = sheet.Range.get('C2').Style Hands that whole style to the target cell (the amount column): number format, font, fill, border and alignment follow
sheet.Range.get({ row, column: 5 }).Text The value already in the target cell stays where it is, unaffected by the style assignment

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
  3. Take the source column's cell and the target column's cell row by row with sheet.Range.get.
  4. Assign the source cell's Style to the target cell's Style; the whole cell format follows, and the target cell's value is left untouched.
  5. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to copy the whole cell format in React:

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

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

    // row by row, put the unit price column's format onto the amount column; the amounts stay put
    for (let row = 2; row <= 7; row++) {
      sheet.Range.get(`E${row}`).Style = sheet.Range.get(`C${row}`).Style;
    }

    // save the workbook
    const outputFileName = 'CopyCellStyle-output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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>Copy Cell Format</h1>
      <button id="copy-cell-style" onClick={copyCellStyle}>Copy the Whole Cell Format</button>
    </div>
  );
}

export default App;

The worksheet after copying the whole cell format:

The whole cell format copied


Clone and reuse a named style

When one set of formatting is needed in several places, registering it as a named style lets it be applied to any range by name, and a colour change has to be made in one place only. A named style lives on the workbook: create it with workbook.Styles.Add, set its properties, then apply it by name through Range.CellStyleName. The example builds a style of white text on a steel blue fill for the header row, then clones it and changes only the colours for the total line -- the change lands on the clone alone, and the original style is unaffected.

A named style is registered on the workbook and applied to a range by name:

Syntax Result
workbook.Styles.Add('OrderHeader') Registers a named style called OrderHeader on the workbook
headerStyle.Font.Color, headerStyle.Color, headerStyle.FillPattern Sets the text colour, the fill colour and the fill pattern one by one
headerStyle.clone() Copies out a style of its own, with a number appended to the name
sheet.Range.get('A1:E1').CellStyleName = headerStyle.Name Applies the named style to that range by name

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
  3. Create a named style with workbook.Styles.Add and set its font name, size, bold, text colour, fill colour, fill pattern and horizontal alignment.
  4. Apply the style to the header range by name with sheet.Range.get('A1:E1').CellStyleName.
  5. Clone it with the style's clone(), change only the text colour and the fill colour, and get the variant the total line needs.
  6. Apply the variant to the total line with sheet.Range.get('A8:E8').CellStyleName.
  7. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to clone a named style and reuse it for different ranges in React:

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

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

    // create a named style: white text on a steel blue fill, centred
    const headerStyle = workbook.Styles.Add('OrderHeader');
    headerStyle.Font.FontName = 'Arial';
    headerStyle.Font.Size = 11;
    headerStyle.Font.IsBold = true;
    headerStyle.Font.Color = xlsModule.Color.get_White();
    headerStyle.Color = xlsModule.Color.get_SteelBlue();
    headerStyle.FillPattern = xlsModule.ExcelPatternType.Solid;
    headerStyle.HorizontalAlignment = xlsModule.HorizontalAlignType.Center;

    // apply the named style to the header row
    sheet.Range.get('A1:E1').CellStyleName = headerStyle.Name;

    // clone it and change only the colours, giving the variant the total line needs
    const totalStyle = headerStyle.clone();
    totalStyle.Font.Color = xlsModule.Color.get_DarkBlue();
    totalStyle.Color = xlsModule.Color.get_LightYellow();

    // apply the cloned style to the total line
    sheet.Range.get('A8:E8').CellStyleName = totalStyle.Name;

    // save the workbook
    const outputFileName = 'CloneNamedStyle.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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>Clone and Reuse a Named Style</h1>
      <button id="clone-named-style" onClick={cloneNamedStyle}>Clone and Reuse a Named Style</button>
    </div>
  );
}

export default App;

The worksheet after cloning a named style and reusing it:

A named style cloned and reused


FAQ

Copying the number format alone, without the fill or the border

Reason: A whole-cell copy takes the fill, font and border along with the number format, which is more than a change of display calls for.

Solution: Take NumberFormat from the source cell's style and assign that single property to the target cell's style, leaving the rest untouched:

// take the number format of the total line's quantity cell as the format to apply
const sourceStyle = sheet.Range.get('D8').Style;

// put only the number format onto the quantity column; the bold and the top border stay untouched
for (let row = 2; row <= 7; row++) {
  sheet.Range.get(`D${row}`).Style.NumberFormat = sourceStyle.NumberFormat;
}

Why a whole font object cannot be assigned to the target cell

Reason: Style.Font is a read-only property; it returns the font object the cell currently carries, so it cannot be replaced wholesale -- only the members hanging off it can be changed.

Solution: Set FontName, Size, Color, IsBold and the like one at a time. To bring the fill and the border across as well, assign the whole Style; to reuse a group of settings, register them as a named style and apply that style by name.

Does re-colouring a clone change the original style as well

Cause: clone() produces a copy that stands on its own, and once saved it takes a separate entry among the workbook's named styles; the name has a number appended to keep the two apart, so OrderHeader clones to OrderHeader1. Changing the text colour and the fill colour on the copy leaves the original where it was, still carrying the colours it had before the clone.

Solution: When several colour variants are needed, clone them all from the same base style and adjust each one separately; the definition of the base style never moves.

A named style or a plain Style assignment -- which one to use

Cause: Assigning a Style copies the source cell's current look onto the target cell in one go, which suits a one-off move; Range.CellStyleName applies a named style registered on the workbook, so a format used over and over is referred to by a single name in the code.

Solution: For a one-off move of the look, assign the whole Style; for a format reused in several places, build a named style with workbook.Styles.Add and apply it by name.


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 a workbook has to be handed to a front-end program, JSON is the shape that fits APIs, caches and components alike: one array per worksheet, one object per row, and the column headers as the keys. Once the workbook is converted, the table data joins the data flow of the page without a backend parsing it first. Spire.XLS for JavaScript performs the conversion in the browser on WebAssembly, handling input and output files through a virtual file system (VFS) with no backend service.

This article covers four core feature points:

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.


Convert the Entire Workbook to JSON

Every worksheet is a set of records that share a shape: the first row names the fields, and each row below it is one record. Handing over the whole workbook writes all of them at once, with the worksheet name as the key and the records as an array under it, so there is no need to call the conversion once per worksheet or to pick a range in advance. The sample workbook's three worksheets, the sales ledger, the customers and the notes, are written together, one top-level key each.

The whole-workbook conversion only has to name the target format when saving:

Syntax Result
fileFormat: xlsModule.FileFormat.Json Writes the workbook as JSON, one top-level key per worksheet

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), then load the input file with workbook.LoadFromFile.
  3. Save the workbook with workbook.SaveToFile, naming xlsModule.FileFormat.Json in fileFormat.
  4. Dispose of the workbook with workbook.Dispose to free resources.

The code below is the complete example of converting the entire workbook to JSON:

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

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // save the whole workbook as JSON
    const outputFileName = 'WorkbookToJson.json';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.Json });

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/json' });
    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>Convert Excel to JSON</h1>
      <button id="workbook-to-json" onClick={workbookToJson}>Convert the Entire Workbook to JSON</button>
    </div>
  );
}

export default App;

The JSON file after converting the entire workbook:

The JSON file after converting the entire workbook


Convert a Specific Worksheet to JSON

Most of the time only one worksheet is meant for the front end, while the rest are internal ledgers or instructions. Writing them all out lengthens the response and leaves the caller to pick the right one. The way around it is to copy the worksheet into a new workbook that holds nothing else and save that, so the result carries this one worksheet only. In the example the third worksheet, the notes, is exported, and the sales ledger and customers worksheets stay out of the result.

Isolating one worksheet comes down to three operations on the Worksheets collection:

Syntax Result
workbook.Worksheets.get(2) Takes the third worksheet by index, counting from 0
workbook.Worksheets.Clear() Clears the worksheets the workbook already holds
workbook.Worksheets.AddCopy({ sheet }) Copies the whole source worksheet into the cleared workbook

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create the source workbook with new Workbook() and load the input file with workbook.LoadFromFile, then take the worksheet to export with Worksheets.get(2).
  3. Create a second workbook with new Workbook() and clear the three blank worksheets it starts with using Worksheets.Clear().
  4. Copy the target worksheet in with Worksheets.AddCopy({ sheet }), so that it is the only one left.
  5. Save the new workbook with SaveToFile, naming xlsModule.FileFormat.Json in fileFormat.
  6. Dispose of both workbook objects with Dispose.

The code below is the complete example of converting a specific worksheet to JSON:

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

    // load the source workbook and take the third worksheet
    const sourceWorkbook = new xlsModule.Workbook();
    sourceWorkbook.LoadFromFile({ fileName: inputFileName });
    const targetSheet = sourceWorkbook.Worksheets.get(2);

    // create a new workbook and clear the three worksheets it starts with
    const targetWorkbook = new xlsModule.Workbook();
    targetWorkbook.Worksheets.Clear();

    // copy the target worksheet in, so that it is the only one left
    targetWorkbook.Worksheets.AddCopy({ sheet: targetSheet });

    // save that worksheet alone
    const outputFileName = 'WorksheetToJson.json';
    targetWorkbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.Json });

    // dispose of the workbook to release its resources
    sourceWorkbook.Dispose();
    targetWorkbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/json' });
    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>Convert Excel to JSON</h1>
      <button id="worksheet-to-json" onClick={worksheetToJson}>Convert a Specific Worksheet to JSON</button>
    </div>
  );
}

export default App;

The JSON file after converting a specific worksheet:

The JSON file after converting a specific worksheet


Convert a Specific Cell Range to JSON

A worksheet usually carries more than the records themselves: title rows, totals formulas, helper columns pasted in from somewhere else. Exporting the whole sheet takes all of that along, when the caller only wants the tidy block in the middle. Naming the range first keeps everything outside it out of the result. The example takes A1:D6 of the sales ledger, six rows by four columns, leaving the price, quantity and amount columns behind in the workbook.

A range export follows the same path as a worksheet export, with a block of cells standing in for the whole sheet:

Syntax Result
sourceSheet.Range.get('A1:D6') Takes the source cell range by address
workbook.Worksheets.Add('RangeData') Adds a worksheet called RangeData to the cleared workbook
targetSheet.Range.get('A1:D6') Takes a destination range of the same size as the source
sourceSheet.Copy({ sourceRange, destRange, copyStyle: true }) Copies the values and styles into the destination range; copyStyle set to true brings the styles along

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create the source workbook with new Workbook() and load the input file with workbook.LoadFromFile, then take the source worksheet with Worksheets.get(0).
  3. Mark out the cell range to export with sourceSheet.Range.get('A1:D6').
  4. Create a target workbook and clear its default worksheets with Worksheets.Clear(), then add a worksheet to receive the range with Worksheets.Add('RangeData').
  5. Take a destination range of the same size with targetSheet.Range.get('A1:D6') and copy the values and styles across with sourceSheet.Copy({ sourceRange, destRange, copyStyle: true }).
  6. Save the new workbook with SaveToFile, naming xlsModule.FileFormat.Json in fileFormat, and dispose of both workbook objects.

The code below is the complete example of converting a specific cell range to JSON:

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

    // load the source workbook and take the first worksheet
    const sourceWorkbook = new xlsModule.Workbook();
    sourceWorkbook.LoadFromFile({ fileName: inputFileName });
    const sourceSheet = sourceWorkbook.Worksheets.get(0);

    // name the cell range to export
    const sourceRange = sourceSheet.Range.get('A1:D6');

    // create a new workbook, clear its default worksheets and add one to receive the range
    const targetWorkbook = new xlsModule.Workbook();
    targetWorkbook.Worksheets.Clear();
    const targetSheet = targetWorkbook.Worksheets.Add('RangeData');

    // copy the values and styles across; the destination range must match the source size
    const destRange = targetSheet.Range.get('A1:D6');
    sourceSheet.Copy({ sourceRange, destRange, copyStyle: true });

    // save the new workbook
    const outputFileName = 'RangeToJson.json';
    targetWorkbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.Json });

    // dispose of the workbook to release its resources
    sourceWorkbook.Dispose();
    targetWorkbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/json' });
    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>Convert Excel to JSON</h1>
      <button id="range-to-json" onClick={rangeToJson}>Convert a Specific Cell Range to JSON</button>
    </div>
  );
}

export default App;

The JSON file after converting a specific cell range:

The JSON file after converting a specific cell range


Customize the JSON Output Format

The three conversions above all produce a fixed shape: the field names are taken from the header row as they stand, every value is written as a string, an amount comes out as "186400", a boolean as "TRUE", a date in the runtime's own format rather than as yyyy-MM-dd, and a blank cell leaves its field out altogether. A result like that still has to be cleaned up before an API or a component binding can use it. Reading the cells one by one instead lets you decide the field names, the type of every value, and how a blank is represented.

Reading the cells yourself calls for the getters that return real types:

Syntax Result
sheet.get(row, column) Takes a cell by row and column number, both counting from 1
sheet.LastRow / sheet.LastColumn The last row and column of the used range
cell.Text The text in the cell; a blank cell returns an empty string
cell.NumberValue The number in a numeric column, as a number; a blank cell returns null
cell.BooleanValue The true or false of a boolean column, as a boolean
cell.DateTimeValue The Date object of a date column, ready to be formatted
JSON.stringify(records, null, 2) Serialises the records into JSON text indented by two spaces

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS, load the workbook with new Workbook() and workbook.LoadFromFile, and take the customers worksheet with Worksheets.get(1).
  2. List the fields to keep in an array, each entry naming its column header, its output field name and how its value is read.
  3. Read the header row with sheet.get(1, column) and, using sheet.LastColumn, map each column header to its column number.
  4. Walk the rows from row 2 to sheet.LastRow, reading each value with cell.Text, cell.NumberValue, cell.BooleanValue or cell.DateTimeValue according to its field, and recording a blank cell as null.
  5. Build a yyyy-MM-dd date from the year, month and day of the Date object.
  6. Serialise the records with JSON.stringify and a two-space indent, then write the text into the VFS with FS.writeFile.
  7. Dispose of the workbook with workbook.Dispose to free resources.

The code below is the complete example of customizing the JSON output format:

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

    // load the workbook and take the customers worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(1);

    // the fields to keep: column header, output field name, how to read the value
    const fields = [
      { header: 'Customer ID', key: 'customerId', type: 'text' },
      { header: 'Customer Name', key: 'customerName', type: 'text' },
      { header: 'City', key: 'city', type: 'text' },
      { header: 'First Order Date', key: 'firstOrderDate', type: 'date' },
      { header: 'Renewed', key: 'renewed', type: 'boolean' },
      { header: 'Total Amount (CNY)', key: 'totalAmount', type: 'number' },
      { header: 'Remark', key: 'remark', type: 'text' },
    ];

    // read the header row and map each column header to its column number
    const columnOf = {};
    for (let column = 1; column <= sheet.LastColumn; column++) {
      columnOf[sheet.get(1, column).Text] = column;
    }

    // dates all go out as yyyy-MM-dd
    const pad = (value) => String(value).padStart(2, '0');
    const formatDate = (date) =>
      `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())}`;

    // walk the rows; a blank cell becomes null
    const records = [];
    for (let row = 2; row <= sheet.LastRow; row++) {
      const record = {};
      for (const field of fields) {
        const cell = sheet.get(row, columnOf[field.header]);
        if (field.type === 'number') {
          record[field.key] = cell.NumberValue;
        } else if (field.type === 'boolean') {
          record[field.key] = cell.BooleanValue;
        } else if (field.type === 'date') {
          record[field.key] = formatDate(cell.DateTimeValue);
        } else {
          const text = cell.Text;
          record[field.key] = text === '' ? null : text;
        }
      }
      records.push(record);
    }

    // write with a two-space indent so the result stays readable
    const json = JSON.stringify(records, null, 2);

    // write the JSON string into the VFS
    const outputFileName = 'CustomJsonFormat.json';
    window.dotnetRuntime.Module.FS.writeFile(outputFileName, new TextEncoder().encode(json));

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/json' });
    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>Convert Excel to JSON</h1>
      <button id="custom-json-format" onClick={customJsonFormat}>Customize the JSON Output Format</button>
    </div>
  );
}

export default App;

The JSON file after applying the custom output format:

The JSON file after applying the custom output format


FAQ

Why are numbers, booleans and dates all strings in the exported JSON

Cause: SaveToFile writes the stored value of each cell. It neither applies the cell's number format nor infers a type, so an amount is written as "186400", a boolean as "TRUE", and a date in the runtime's own format instead of yyyy-MM-dd.

Solution: To get real types, read the cells yourself and take the value with cell.NumberValue, cell.BooleanValue or cell.DateTimeValue, then let JSON.stringify serialise the result.

Why do blank cells not appear in the JSON

Cause: Both the workbook and the worksheet conversion use the header row as the field names, and a blank cell produces no field at all, so that object ends up with fewer keys than the others.

Solution: If the caller needs every object to carry the same keys, read the cells yourself and write null whenever cell.Text comes back as an empty string.

Why is there an extra Evaluation Warning key at the top level of the JSON

Cause: The trial build adds an evaluation warning worksheet to the workbook, and in a whole-workbook conversion it becomes one more top-level key alongside the real worksheets.

Solution: Convert a specific worksheet or a specific cell range instead, and only the data you asked for is written; if the whole workbook has to be exported as it stands, contact sales for a temporary license that removes the evaluation message.

How do I export only a few columns

Cause: The workbook and worksheet conversions both write every column in the worksheet, while a range export can only take a consecutive block, so columns such as A, C and E cannot be expressed as a range.

Solution: Read the cells yourself the way the custom output format does and keep the fields you need; when the columns happen to be consecutive, a range export narrows the result just as well.


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 single column often packs several pieces of information into one cell: a contact's name, phone number and city written on one line, an order code joining the country code, the date and a serial number into one string. Such data can neither be sorted nor filtered by any one of those parts. Splitting text into columns takes a column of text apart by a given rule and drops the pieces into the columns to its right, leaving the original data where it is. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

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.


Split on a separator

When every entry on a line is separated from the next by the same character, splitting on that character moves them into the columns to the right. Each kind of information then has a column of its own, ready for sorting, checking or grouping, while the original data stays where it is.

The split reads the text out of the first column, cuts it apart, and writes the pieces back into the columns on the right:

Syntax Result
sheet.Range.get({ row, column: 1 }).Text Reads the raw text of the first column on that row
.split(' ') Cuts the text apart on the space
sheet.Range.get({ row, column: index + 2 }).Text = piece Writes the piece at index into the index + 1 column to its right
sheet.AllocatedRange.AutoFitColumns() Sizes every column to its content

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the target worksheet with workbook.Worksheets.get(0).
  3. Take the last data row with sheet.LastRow and work row by row from row 2, skipping the header.
  4. Read the raw text of the first column with sheet.Range.get({ row, column: 1 }).Text and split it on the separator with split(' ').
  5. Walk the pieces and write them back from the second column on with sheet.Range.get({ row, column: index + 2 }).Text.
  6. Size every column to its content with sheet.AllocatedRange.AutoFitColumns().
  7. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to split an Excel column on a separator in React:

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

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

    // row by row, take the text in column A, split it on the space and write the pieces into the columns on the right
    const lastRow = sheet.LastRow;
    for (let row = 2; row <= lastRow; row++) {
      const pieces = sheet.Range.get({ row, column: 1 }).Text.split(' ');
      pieces.forEach((piece, index) => {
        sheet.Range.get({ row, column: index + 2 }).Text = piece;
      });
    }

    // size every column to its content
    sheet.AllocatedRange.AutoFitColumns();

    // save the workbook
    const outputFileName = 'SplitByDelimiter.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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>Split Excel Data into Columns</h1>
      <button id="split-by-delimiter" onClick={splitByDelimiter}>Split by a Separator</button>
    </div>
  );
}

export default App;

The worksheet after splitting on a separator:

Splitting on a separator


Split by fixed width

Other text is not divided by any character at all: every entry has a fixed length and they run end to end, as a code assembled from fields of known width. Such text is cut by the number of characters in each entry, carrying on from where the previous one ended.

The cut advances through the source text by the length of each entry and writes the pieces back into the columns on the right:

Syntax Result
sheet.Range.get({ row, column: 1 }).Text Reads the raw code of the first column on that row
code.substring(start, start + width) Cuts out width characters starting at start
start += width Moves the cutting position past that entry, ready for the next
sheet.Range.get({ row, column: index + 2 }).Text = piece Writes the piece at index into the index + 1 column to its right
sheet.AllocatedRange.AutoFitColumns() Sizes every column to its content

The steps are:

  1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the worksheet holding the text with workbook.Worksheets.get(2).
  3. Note down the number of characters in each entry in an array; the lengths should add up to that of the source text.
  4. Take the last data row with sheet.LastRow and work row by row from row 2, skipping the header.
  5. Cut one entry out with code.substring(start, start + width), move the starting position on by that length, and write it back from the second column on with sheet.Range.get({ row, column: index + 2 }).Text.
  6. Size every column to its content with sheet.AllocatedRange.AutoFitColumns().
  7. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to split an Excel column by fixed width in React:

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

    // load the workbook and take the third worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(2);

    // row by row, take the code in column A and cut it at the fixed lengths into the columns on the right
    const widths = [2, 4, 2, 2, 3];
    const lastRow = sheet.LastRow;
    for (let row = 2; row <= lastRow; row++) {
      const code = sheet.Range.get({ row, column: 1 }).Text;
      let start = 0;
      widths.forEach((width, index) => {
        sheet.Range.get({ row, column: index + 2 }).Text = code.substring(start, start + width);
        start += width;
      });
    }

    // size every column to its content
    sheet.AllocatedRange.AutoFitColumns();

    // save the workbook
    const outputFileName = 'SplitByFixedWidth.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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>Split Excel Data into Columns</h1>
      <button id="split-by-fixed-width" onClick={splitByFixedWidth}>Split by Fixed Width</button>
    </div>
  );
}

export default App;

The worksheet after splitting by fixed width:

Splitting by fixed width


FAQ

The column uses more than one kind of separator, how can it be split in one pass

Reason: split(' ') cuts on a single character at a time; when a text mixes commas, slashes and the like, each separator has to be handled on its own, so the code keeps piling up and one of them is easily left out.

Solution: Put the separators you expect into a character class, and a single split covers them all -- the characters inside the brackets are alternatives, so any one of them counts as a split point. The sample workbook's address sheet separates the levels of an address with a comma or a slash, and this form splits it into three columns at once:

// a comma and a slash both count as split points, taken apart in one pass
const pieces = sheet.Range.get({ row, column: 1 }).Text.split(/[,/]/);

An empty column turns up in the middle of the split result

Reason: split(' ') cuts strictly on a single space, so two adjacent spaces in the raw text cut out an empty string between them, and that empty string lands on the sheet as a blank column.

Solution: Strip the leading and trailing spaces with trim() first, then split on /\s+/ -- a run of consecutive spaces counts as one split point, and no empty string is cut out in between:

// strip the surrounding spaces, then split on a run of whitespace so no empty piece is cut out
const pieces = sheet.Range.get({ row, column: 1 }).Text.trim().split(/\s+/);

Does splitting into columns overwrite what the columns on the right already hold

Cause: The pieces are written cell by cell with sheet.Range.get({ row, column: index + 2 }).Text = piece, so whatever the target column held on that row is replaced by the entry written into it. The text in the first column is untouched; on the right, the columns the pieces land in are overwritten, and any column beyond them keeps its old data.

Solution: To keep what is already on the right, write the result further out into empty columns, or into a separate worksheet instead; sheet.LastColumn tells you whether the right-hand side already holds anything before you write.

What happens when the entry lengths do not add up to the length of the source text

Cause: substring(start, start + width) only takes what the source text actually holds. When the lengths add up to less than the source text, the characters left at the end of it go into no column at all; when they add up to more, the overrun is cut to an empty string and leaves a blank column.

Solution: Check that the lengths add up to the length of the source text before cutting, and every entry lands in a column of its own.


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 workbook is made for editing, which does not always make it the right thing to hand over for archiving or printing. XPS, OFD and PostScript are all fixed-layout documents: the page size, the position of every piece of text and the fonts are pinned when the file is written, so the document looks the same on any machine that opens it. Spire.XLS for JavaScript performs all three conversions in the browser on WebAssembly, handling input and output files through a virtual file system (VFS) with no backend service.

This article covers three core feature points:

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.


Convert Excel to XPS

XPS is a fixed-layout document format that ships with Windows, and saving a workbook as XPS gives it an archived copy that no longer rearranges itself to suit the program that opens it, which is what makes it fit for a report that has to be read on a machine without Excel installed. The conversion changes only the format the file is saved in: the page setup, the cell formatting and the fonts of the worksheet are all written out with it, and a fileFormat argument names the target.

The fileFormat argument of workbook.SaveToFile decides which of the formats is written:

Syntax Result
workbook.LoadFromFile({ fileName: inputFileName }) Loads the workbook to convert from the VFS
workbook.SaveToFile({ fileName: 'ExcelToXPS.xps', fileFormat: xlsModule.FileFormat.XPS }) Writes the workbook as XPS, pinning the page size, the text position and the fonts
workbook.Dispose() Releases the resources held by the workbook object

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), then load the Excel file with workbook.LoadFromFile.
  3. Save the workbook with workbook.SaveToFile, naming xlsModule.FileFormat.XPS in fileFormat.
  4. Dispose of the workbook with workbook.Dispose to free resources.

The code below is the complete example of converting a workbook to XPS in React:

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

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // save as XPS, naming the format with fileFormat
    const outputFileName = 'ExcelToXPS.xps';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.XPS });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.ms-xpsdocument' });
    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>Convert Excel to XPS, OFD or PostScript</h1>
      <button id="excel-to-xps" onClick={excelToXps}>Convert Excel to XPS</button>
    </div>
  );
}

export default App;

The document after converting Excel to XPS:

Convert an Excel workbook to an XPS document


Convert Excel to OFD

OFD is the fixed-layout document format used by Chinese electronic official documents and e-invoices, describing a page in a national standard so that it can be kept for the long term. Saving a sales report as OFD hands other systems a page whose layout is already fixed, and it no longer matters whether that system has the fonts or applies the same row heights. The conversion runs exactly like the previous feature point, with OFD named as the format.

Syntax Result
workbook.LoadFromFile({ fileName: inputFileName }) Loads the workbook to convert from the VFS
workbook.SaveToFile({ fileName: 'ExcelToOFD.ofd', fileFormat: xlsModule.FileFormat.OFD }) Writes the workbook as OFD, describing the page in the national standard
workbook.Dispose() Releases the resources held by the workbook object

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), then load the Excel file with workbook.LoadFromFile.
  3. Save the workbook with workbook.SaveToFile, naming xlsModule.FileFormat.OFD in fileFormat.
  4. Dispose of the workbook with workbook.Dispose to free resources.

The code below is the complete example of converting a workbook to OFD in React:

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

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // save as OFD, naming the format with fileFormat
    const outputFileName = 'ExcelToOFD.ofd';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.OFD });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/ofd' });
    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>Convert Excel to XPS, OFD or PostScript</h1>
      <button id="excel-to-ofd" onClick={excelToOfd}>Convert Excel to OFD</button>
    </div>
  );
}

export default App;

The document after converting Excel to OFD:

Convert an Excel workbook to an OFD document


Convert Excel to PostScript

PostScript is the page description language used by printing devices and print workflows. The file is readable text that carries both the page description and the fonts embedded in it, so it can go straight to a PostScript printer or to typesetting software. When a report is headed for a print run, handing over PostScript is safer than handing over the original workbook. The usage matches the first two feature points, with PostScript named as the format.

Syntax Result
workbook.LoadFromFile({ fileName: inputFileName }) Loads the workbook to convert from the VFS
workbook.SaveToFile({ fileName: 'ExcelToPostScript.ps', fileFormat: xlsModule.FileFormat.PostScript }) Writes the workbook as PostScript, a readable page description
workbook.Dispose() Releases the resources held by the workbook object

The steps are:

  1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS.
  2. Create a workbook with new Workbook(), then load the Excel file with workbook.LoadFromFile.
  3. Save the workbook with workbook.SaveToFile, naming xlsModule.FileFormat.PostScript in fileFormat.
  4. Dispose of the workbook with workbook.Dispose to free resources.

The code below is the complete example of converting a workbook to PostScript in React:

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

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // save as PostScript, naming the format with fileFormat
    const outputFileName = 'ExcelToPostScript.ps';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.PostScript });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/postscript' });
    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>Convert Excel to XPS, OFD or PostScript</h1>
      <button id="excel-to-ps" onClick={excelToPostScript}>Convert Excel to PostScript</button>
    </div>
  );
}

export default App;

The document after converting Excel to PostScript:

Convert an Excel workbook to a PostScript document


FAQ

Why does the conversion fail with Cannot found font?

Cause: Writing XPS, OFD and PostScript means embedding every font the document uses, so a workbook whose text is not in the default Latin range needs the font file for it. If the font was never loaded into the VFS before the conversion, the module cannot find it and SaveToFile stops with a Cannot found font(...) installed on the system. error instead of writing a document.

Solution: Put the font file in public/font and load it into the VFS before loading the workbook, using the same file name the code refers to:

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

Does the worksheet's page setup affect the converted document?

Cause: XPS, OFD and PostScript are all written page by page, and the size and the orientation of that page come from the worksheet's own page setup rather than from a decision the conversion makes on its own.

Solution: They carry over as they are. Taking the report in this example, the XPS page box is 794 x 1122 (portrait) while the report keeps its default orientation; set the first worksheet's sheet.PageSetup.Orientation to xlsModule.PageOrientationType.Landscape before converting, and the page box becomes 1122 x 794 (landscape). Paper size, margins and print area take effect on the way out in the same manner.

Why is the converted file much larger than the original workbook

Cause: Writing XPS, OFD and PostScript embeds every font the workbook uses, so the result comes out several times the size of a workbook that only holds cell data. That embedding is what lets a fixed-layout document be opened away from the machine it was made on.

Solution: This is normal and nothing needs fixing. To bring the size down, use a smaller font file, or check first that the report does not rely on several font files at once.


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.

Formatting a cell range cell by cell means repeating the same group of properties down every column, and with enough columns one of them is easily missed; these formats also tend to be spread across fonts, borders and alignment, so each has to be set and then checked again on its own. Spire.XLS stores that group of formats as a named style, uses a flag object to state which of those formats should land this time, and then applies it to the target range: properties that were not ticked are not applied, and every other format on the range is left unchanged. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the output file is managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

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 named style to a cell range

Setting formats cell by cell means repeating the same block of code in every column, and with enough columns a cell is easily missed; fonts, borders and centring set separately would also mean working over the same block three times over. A named style stores a group of formats as an object that can be taken up repeatedly, and the flag object states which of those formats should land this time; together they apply the wanted parts to the whole range in a single call, leaving the range's other formatting unchanged. The steps are:

  1. Load the font file into the VFS, create a workbook, take the first worksheet and write the header and two data rows.
  2. Create a named style with workbook.Styles.Add and give it the font colour, size and italic the header will use.
  3. Set the top and bottom borders on that same style.
  4. Set horizontal and vertical centring on that same style.
  5. Create the flag object with new xlsModule.CellStyleFlag() and tick the properties to land this time; the items left unticked are not written into the target cells.
  6. Apply the style to the header range with sheet.Range.get('A1:E1').ApplyStyle(tableStyle, flag); set the column widths so the header text displays in full, then save the workbook and dispose of it to free resources.

The full code example below shows how to apply cell styles to an Excel cell range in React:

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

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

    // write the header and two data rows
    const columns = ['A', 'B', 'C', 'D', 'E'];
    const headers = ['Product', 'Spec', 'Quantity', 'Unit price', 'Amount'];
    headers.forEach((text, index) => {
      sheet.Range.get(`${columns[index]}1`).Value = text;
    });

    const rows = [
      ['Blu-ray mini projector', '1080P / 8G', '6', '1299', '7794'],
      ['Portable Bluetooth speaker', '20W / IPX7', '12', '399', '4788'],
    ];
    rows.forEach((row, rowIndex) => {
      columns.forEach((column, columnIndex) => {
        sheet.Range.get(`${column}${rowIndex + 2}`).Value = row[columnIndex];
      });
    });

    // create a named style
    const tableStyle = workbook.Styles.Add('tableStyle');

    // give the header its font colour, size and italic
    tableStyle.Font.Color = xlsModule.Color.get_CornflowerBlue();
    tableStyle.Font.Size = 16;
    tableStyle.Font.IsItalic = true;

    // set the top and bottom borders on the same style
    tableStyle.Borders.get(xlsModule.BordersLineType.EdgeTop).LineStyle = xlsModule.LineStyleType.Thin;
    tableStyle.Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Thin;

    // and set horizontal and vertical centring on the same style
    tableStyle.HorizontalAlignment = xlsModule.HorizontalAlignType.Center;
    tableStyle.VerticalAlignment = xlsModule.VerticalAlignType.Center;

    // create the flag object and tick the properties to land this time; unticked ones are not written to the target cells
    const flag = new xlsModule.CellStyleFlag();
    flag.FontColor = true;
    flag.FontSize = true;
    flag.FontItalic = true;
    flag.Borders = true;
    flag.HorizontalAlignment = true;
    flag.VerticalAlignment = true;

    // apply the style to the header range
    sheet.Range.get('A1:E1').ApplyStyle(tableStyle, flag);

    // set the column widths
    sheet.Columns.get(0).ColumnWidth = 22;
    sheet.Columns.get(1).ColumnWidth = 14;

    // save the workbook as an Excel file
    const outputFileName = 'ApplyStyleToRange.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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 cell styles to a cell range</h1>
      <button id="apply-style-to-range" onClick={applyStyleToRange}>Apply cell styles to the cell range</button>
    </div>
  );
}

export default App;

After running, the effect of applying the named style to the cell range:

Apply cell styles to a cell range


Read and adjust the named style

Once a style has been saved, later changes concern the values inside it rather than the whole set of formats, and no workbook has to be rebuilt: load the result file produced by feature point 1, read the named style by name, take the size already set in it, increase it, and apply the style to another range. The flag decides the scope here too: this time only the size, borders and centring are ticked, so the style's colour and italic are not written into the data range. Note that applying a style writes its properties into the cells as they stand at that moment, and the cells keep no reference to the style, so the range has to be applied again for the change to take effect. The steps are:

  1. Run the code of feature point 1 first, so that its result file ApplyStyleToRange.xlsx stays in the VFS as this run's input file.
  2. Load that input file with workbook.LoadFromFile and take the first worksheet.
  3. Read the named style feature point 1 created with workbook.Styles.get('tableStyle') and take its Font.Size as the baseline.
  4. Raise the baseline size and write it back to the style, as the size the data range will use.
  5. Create the flag object and tick only the size, borders and centring; apply the adjusted style to the data range with sheet.Range.get('A2:E3').ApplyStyle(readBack, flag), set the column widths so the larger font size still fits, then save the workbook and dispose of it to free resources.

The full code example below shows how to load a result file, read the named style and apply it after adjusting the size in React:

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

    // load the result file produced by feature point 1 as this run's input
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: 'ApplyStyleToRange.xlsx' });
    const sheet = workbook.Worksheets.get(0);

    // read the named style feature point 1 created and take its current font size as the baseline
    const readBack = workbook.Styles.get('tableStyle');
    const baseSize = readBack.Font.Size;

    // raise the baseline size to get the size the data range will use
    readBack.Font.Size = baseSize + 4;

    // create the flag object: this time tick only the font size, borders and centring, so the style's colour and italic stay off the data range
    const flag = new xlsModule.CellStyleFlag();
    flag.FontSize = true;
    flag.Borders = true;
    flag.HorizontalAlignment = true;
    flag.VerticalAlignment = true;

    // apply the adjusted style to the data range
    sheet.Range.get('A2:E3').ApplyStyle(readBack, flag);

    // set the column widths so the larger font size still fits
    sheet.Columns.get(0).ColumnWidth = 30;
    sheet.Columns.get(1).ColumnWidth = 20;

    // save the workbook as an Excel file
    const outputFileName = 'ReadAndAdjustFontStyle.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start 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>Read the style and adjust its size</h1>
      <button id="read-and-adjust-font-style" onClick={readAndAdjustFontStyle}>Read the named style, adjust it, then apply it</button>
    </div>
  );
}

export default App;

After running, the effect of reading the named style and adjusting its font size:

Read the named style and adjust its size


FAQ

Do the number formats already on the range change when I apply a style

Reason: No. Only the items ticked in the flag object are written into the target cells when the style is applied; the items left unticked take no part, so the number formats, borders and alignment already on the cells remain as they were.

Solution: Conversely, to apply number formats along with it, set the number format on the style as well and tick the matching item in the flag:

// give the style the number format first
tableStyle.NumberFormat = '0.00';

// tick the matching item in the flag
flag.NumberFormat = true;
sheet.Range.get('A1:E1').ApplyStyle(tableStyle, flag);

How do I apply a style to the whole worksheet

Reason: The examples above apply the style to one range at a time; a whole worksheet treated the same way would mean listing every range to cover. The worksheet object carries a method of the same name that covers the whole sheet in a single call.

Solution: Call ApplyStyle on the worksheet object to apply the whole style to the whole sheet:

sheet.ApplyStyle(tableStyle);

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 PDF that runs to dozens or hundreds of pages — a product manual, a technical handbook, a project report, a conference proceedings — loses most of its usability when it has no bookmarks and no table of contents. To get from "Chapter 3" to "5.2 Interface Description", a reader has to scroll page by page or guess the page number from memory. Reviewers and archivists cannot jump to a chapter either.

Bookmarks and a table of contents exist to solve exactly this problem of navigation: bookmarks are the hierarchical structure in the PDF reader's side panel, where one click jumps to the matching chapter; a table of contents page is a single page of text placed before the body, listing chapter titles and page numbers so readers can grasp the structure at a glance or look things up in print. Yet these two things are usually the first to be lost: bookmarks generated when converting Word to PDF can drop out, scanned or reassembled documents have none at all, and adding them by hand means cross-checking titles against page numbers until a long document inevitably goes out of sync.

The PDF AI capability of Spire.Agent.Office lets you describe the bookmark hierarchy and the table of contents requirements in natural language. The AI agent understands the request and completes two kinds of work automatically: generating hierarchical bookmarks for a PDF, and generating a front-of-document contents page whose page numbers match the body and whose entries jump when clicked.

Comparison with Traditional SDK API

Traditional Spire.Office for .NET API Spire.Agent.Office
Driving style Write code that walks every page, matches headings by font size or numbering patterns, builds the bookmark tree and assigns each entry a page, controlling every step Describe the bookmark hierarchy and TOC requirements in natural language; the AI understands them and orchestrates the execution path
Code size Bookmark and TOC generation usually needs 150-300 lines of C# (page iteration, heading detection, hierarchy decisions, bookmark tree construction, page number calculation, TOC page layout and formatting, etc.) About 10 lines of calling code + 1 natural-language instruction
Heading detection You must define what text counts as a heading, and any change to font size or numbering format means changing the rules The AI judges heading levels from numbering, font size and layout context; the rules are stated in one sentence in the instruction
Page number mapping TOC page numbers are counted by hand and must be recounted whenever the body changes The AI reads the actual page numbers from the body and fills them into the TOC
Requirement change Change the bookmark hierarchy / TOC style → change code → compile → redeploy Modify the instruction and it takes effect immediately

This article shows how to use the Spire.Agent.Office PDF AI capability to generate bookmarks and a table of contents for PDF documents. Two cases demonstrate the two typical uses — "generating hierarchical bookmarks" and "generating a table of contents page":

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: Generate hierarchical bookmarks for a PDF

Documents such as product manuals and technical handbooks usually follow a fixed structure: the body is divided into large parts labelled "Chapter 1", "Chapter 2", and each chapter is broken down further into sections such as "1.1" and "1.2". The layout makes the hierarchy obvious at a glance through numbering and font size, but the PDF reader's navigation panel stays empty — jumping to one specific section means paging through the document yourself.

For that panel to be genuinely useful, the bookmarks must mirror the heading hierarchy exactly: top-level headings become top-level bookmarks, second-level headings become child bookmarks nested under their chapter, and clicking a bookmark jumps straight to the page where the heading appears. Adding them by hand means finding each heading page by page, creating an entry in the bookmark panel, then pointing that entry at the right destination — hundreds of headings in, mistakes are close to inevitable.

The example below uses the Spire.Agent.Office agent to read the chapter structure from the PDF body through a natural-language instruction and generate multi-level bookmarks by heading hierarchy:

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

// PDF-related configuration
string inputPath = @"E:\manual.pdf";  // path to the PDF that needs bookmarks
string savePath = @"E:\output\manual-with-bookmarks.pdf";  // path of the result file
string key = "**************************";  // SpireToken key
string instruction =
    "Read the body of this PDF product manual and generate hierarchical bookmarks for it:" +
    "1. Detect the chapter headings in the body; headings starting with 'Chapter X' become top-level bookmarks, and headings starting with 'X.Y' become child bookmarks under the matching chapter;" +
    "2. List the bookmarks in the order they appear in the body, keep the hierarchy consistent with the heading structure, and keep each bookmark title identical to the heading text;" +
    "3. Each bookmark jumps to the page where its heading appears, so one click locates that chapter;" +
    "4. Ignore figure captions and headers/footers and do not create bookmarks for them;" +
    "5. Keep the original layout, fonts and page setup of the body unchanged;" +
    "Save the final result as a PDF file";

// Call the PDF document processing function
AIResult result = ExecuteDemoPDF(instruction, inputPath, savePath, key);


// Execute PDF AI processing
static AIResult ExecuteDemoPDF(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 PDF document using a PdfDocument object
    using (PdfDocument pdf = new PdfDocument())
    {
        // Load the PDF document from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            pdf.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = pdf.AI(options);

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

The PDF after hierarchical bookmarks were generated The PDF after hierarchical bookmarks were generated

The generated bookmarks reproduce the chapter structure of the body completely: top-level chapters sit at the top of the panel, each section is nested beneath its chapter, and clicking any bookmark jumps to the page where that heading appears. The body layout, fonts and page setup stay exactly as they were — the navigation structure is simply added on top.


Case 2: Generate a table of contents page for a PDF

When a technical proposal, project report or collection of papers is delivered to reviewers or readers, it usually needs a table of contents on the very first page: chapter titles listed line by line, each with the page number where the chapter begins, so readers can see the whole structure before deciding where to start. A contents page is also part of the required style of a formal document, and documents of this kind normally carry a cover page, with the contents sitting after the cover and before the body.

Two things make a contents page awkward to build. The first is the page numbers: they must match the body exactly, and any added or removed chapter, or any change in pagination, means every number has to be recounted. The second is the jumping: a line reading "Section 5.2, page 27" still leaves the reader to find page 27 by hand; only when the entry itself is clickable does the contents page become genuinely useful.

The example below uses the Spire.Agent.Office agent to extract each chapter title with its page number through a natural-language instruction and insert a laid-out, clickable contents page after the cover page:

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

// PDF-related configuration
string inputPath = @"E:\report.pdf";  // path to the PDF that needs a contents page
string savePath = @"E:\output\report-with-toc.pdf";  // path of the result file
string key = "**************************";  // SpireToken key
string instruction =
    "Read the body of this PDF and generate a table of contents page after the cover page:" +
    "1. Extract the title and page number of every chapter in the body, including both top-level and second-level headings;" +
    "2. Insert a 'Contents' page after the cover page and before the body, listing chapter titles with their page numbers line by line, titles left-aligned and page numbers right-aligned with dot leaders in between;" +
    "3. Make every entry in the contents a clickable link that jumps straight to the page where that chapter begins;" +
    "4. The page numbers shown in the contents must match the actual body pages after the contents page has been inserted; no number may be off by a page;" +
    "5. Centre the word 'Contents' as the title of the page, using the same font size as the top-level headings in the body;" +
    "6. Keep the original layout, fonts and page setup of the body unchanged;" +
    "Save the final result as a PDF file";

// Call the PDF document processing function
AIResult result = ExecuteDemoPDF(instruction, inputPath, savePath, key);


// Execute PDF AI processing
static AIResult ExecuteDemoPDF(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 PDF document using a PdfDocument object
    using (PdfDocument pdf = new PdfDocument())
    {
        // Load the PDF document from file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            pdf.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = pdf.AI(options);

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

The PDF with a contents page generated after the cover The PDF with a contents page generated after the cover

The contents page lists chapters by level, with dot leaders running from each title to its page number, matching the format of a published book. Every entry carries a jump link, so one click lands on the matching chapter — the page works as a reading aid and as a navigation tool at the same time. The body content and its original layout are untouched; the contents page is inserted as a page of its own after the cover.


FAQ

The generated bookmarks have no hierarchy and all headings sit on one level

Cause: The hierarchy in the body is conveyed by numbering format ("Chapter 1" versus "1.1") and font size. If the instruction does not state the rule, the AI may treat every heading it detects as a top-level bookmark.

Solution: State the hierarchy rule in the instruction, for example "headings starting with 'Chapter X' become top-level bookmarks and headings starting with 'X.Y' become child bookmarks under the matching chapter". When the numbering is unusual (such as "Part I" or "Appendix A"), include that rule in the instruction as well.

The page numbers in the contents do not match the actual body pages

Cause: Inserting the contents page in front of the body shifts every following page by one. If the page numbers are collected before the contents page is inserted, every number in the contents comes out one page too low.

Solution: State which numbering basis to use, for example "the page numbers in the contents must match the actual body pages after the contents page has been inserted". If the body already has its own numbering scheme (for instance a cover page and preface that are not numbered), describe that rule in the instruction so the AI counts by the same convention.


Getting a SpireToken Key

Configure it in your code:

AIOptions options = new AIOptions();
options.SpireToken = key;
Page 4 of 9