In data analysis scenarios, trendlines help you visually identify data trends and predict directions from Excel charts. Spire.XLS for JavaScript provides rich trendline APIs that allow you to add various types of trendlines to chart series directly in the browser via WebAssembly, without requiring a backend service.

This article covers two core features:

• Add Trendline to Chart
• Extract Trendline Formula

For installation and project configuration, please refer to Integrating Spire.XLS for JavaScript in a React Project. The following examples assume Spire.XLS is installed and the WebAssembly module has been initialized.

Add Trendline to Chart

You can add trendlines to any series in a chart through the chart.Series.get(i).TrendLines.Add() method. Spire.XLS for JavaScript supports 4 types of trendlines defined in the TrendLineType enumeration:

• Linear — Linear trendline, suitable for data showing steady increase or decrease
• Exponential — Exponential trendline, suitable for scenarios where the growth or decline rate accelerates
• Logarithmic — Logarithmic trendline, suitable for data that changes rapidly then stabilizes
• Moving_Average — Moving average trendline, suitable for smoothing data fluctuations

Specific steps:

1. Create a Workbook object and get the first worksheet.
2. Get the chart that needs a trendline through Worksheet.Charts.get(i).
3. Call chart.Series.get(0).TrendLines.Add() with the type parameter specifying the trendline type.
4. Save the workbook through Workbook.SaveToFile().

Below is a complete code example showing how to add four different types of trendlines to an Excel chart in React:

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

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

    // Load the Excel file into VFS
    const inputFileName = 'ChartTrendline_en.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Get the first chart from the first worksheet
    let chart = workbook.Worksheets.get(0).Charts.get(0);

    // Add linear trendline
    chart.ChartTitle = "Linear Trendline";
    chart.Series.get(0).TrendLines.Add({ type: xlsModule.TrendLineType.Linear });

    // Add exponential trendline
    chart = workbook.Worksheets.get(0).Charts.get(0);
    chart.ChartTitle = "Exponential Trendline";
    chart.Series.get(0).TrendLines.Add({ type: xlsModule.TrendLineType.Exponential });

    // Add logarithmic trendline
    chart.ChartTitle = "Logarithmic Trendline";
    chart.Series.get(0).TrendLines.Add({ type: xlsModule.TrendLineType.Logarithmic });

    // Add moving average trendline
    chart.ChartTitle = "Moving Average Trendline";
    chart.Series.get(0).TrendLines.Add({ type: xlsModule.TrendLineType.Moving_Average });

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

    // Release resources
    workbook.Dispose();

    // Read the converted file from VFS and trigger 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 Trendline to Chart</h1>
      <button onClick={addTrendline}>Start</button>
    </div>
  );
}

export default App;

After running the code, you get the effect of adding four different types of trendlines to a chart:

Extract Trendline Formula

You can obtain the mathematical formula of a trendline through the trendLine.Formula property, making it easy to display the trendline's analytical expression in reports.

Specific steps:

1. Load the AddTrendline.xlsx file generated in Step 1, which contains four charts.
2. Use Worksheet.Charts.get(i) to iterate through all four charts.
3. Read the trendLine.Formula property from each chart to obtain the formula string, then save all formulas to a text file.

Below is a complete code example showing how to extract trendline formulas in React:

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

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

    // Load the AddTrendline.xlsx file generated in Step 1 into VFS
    const inputFileName = 'AddTrendline.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Get the first worksheet
    let sheet = workbook.Worksheets.get(0);
    let result = "Extracted trendline formulas from four charts:\n\n";

    // Iterate through all four charts and extract trendline formulas
    for (let i = 0; i < 4; i++) {
      let chart = sheet.Charts.get(i);
      let trendLine = chart.Series.get(0).TrendLines.get(0);
      // Moving average trendline has no mathematical formula
      if (trendLine.Type === xlsModule.TrendLineType.Moving_Average) {
        result += `Chart ${i + 1} (${chart.ChartTitle}): N/A (Moving Average)\n`;
      } else {
        let formula = trendLine.Formula;
        result += `Chart ${i + 1} (${chart.ChartTitle}): ${formula}\n`;
      }
    }

    // Release resources
    workbook.Dispose();

    // Save the formulas to a text file and trigger download
    const outputFileName = 'ExtractTrendline.txt';
    const blob = new Blob([result], { type: "text/plain;charset=utf-8" });
    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>Extract Trendline Formula</h1>
      <button onClick={extractTrendlineFormula}>Start</button>
    </div>
  );
}

export default App;

After running the code, you get the effect of extracting trendline formulas:

FAQ

How to delete an added trendline from a chart?

Cause: A trendline has already been added to the chart, but it needs to be removed or replaced.

Solution: Use the TrendLines.RemoveAt(index) method to remove a trendline at a specific index. Indexing starts from 0. For example, chart.Series.get(0).TrendLines.RemoveAt(0) removes the first trendline from the first series.

How to set forward/backward prediction periods for a trendline?

Cause: A trendline can not only fit existing data, but also predict future or past values based on the trend.

Solution: Use the trendLine.Forward and trendLine.Backward properties to set the number of prediction periods forward and backward respectively. For example, trendLine.Forward = 2 predicts two periods beyond the current data, while trendLine.Backward = 1 extrapolates one period before the data.

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