[Java] Add, Read and Delete Trendlines in Excel documents

Data Analysis is used to gain an understanding for the development trend of data in the future, which can ensure that plans can be made reasonably at present. The development trend of data can be visually found by adding a trend line to a chart in an Excel document. This article will demonstrate how to add a trend line to a specified chart in an Excel document, and also introduce the methods of reading and deleting the trend line. All actions will be done in Java program by using a free third-party library called FreeSpire.XLS for Java.

Dependency

Before running codes, we need to insert a Jar file into IDEA. You can download it from the website or directly reference it using the following Maven configurations.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

The following screenshot is shown what it looks like finally.

Add Trendline

Here is a screenshot of the Excel file used to test. I’ll add a Trendline for the second data series in the chart below.

The types of Trendline which Free Spire.XLS for Java supports adding to Excel charts includes Linear, Exponential Logarithmic, Moving_Average, Polynomial,Power and so on. In the process of adding a trend line, you can set its type and color, and whether display the equation and R-Squared value. 

import com.spire.xls.*;
import com.spire.xls.core.IChartTrendLine;
import java.awt.*;

public class AddTrendLine {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the first chart in the first worksheet
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);

        //Add a Trendline to the second series of the chart
        IChartTrendLine trendLine = chart.getSeries().get(1).getTrendLines().add(TrendLineType.Linear);

        //Set Trendline name
        trendLine.setName("Linear(Product 2)");
        //Set line type and color
        trendLine.getBorder().setPattern(ChartLinePatternType.DashDot);
        trendLine.getBorder().setColor(Color.blue);
        //Set forward and backward value
        trendLine.setForward(0.5);
        trendLine.setBackward(0.5);
        //Set intercept value
        trendLine.setIntercept(5);

        //Display equation on chart
        trendLine.setDisplayEquation(true);
        //Display R-Squared value on chart
        trendLine.setDisplayRSquared(true);

        //Save the result file
        workbook.saveToFile("output/AddTrendline.xlsx", ExcelVersion.Version2013);
    }
}

Output

Read Trendline
Free Spire.XLS for Java supports reading the type, name and equation of the Trendline.
import com.spire.xls.*;
import com.spire.xls.core.IChartTrendLine;

public class ReadTrendLine {
    public static void main(String[] args) {
        //Load the excel file used to test
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\AddTrendline.xlsx");

        //Get the first chart in the first worksheet
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);

        //Get Trendline in the second data series of the chart
        IChartTrendLine trendLine = chart.getSeries().get(1).getTrendLines().get(0);
        String type = trendLine.getType().toString();
        String name = trendLine.getName();
        String equation = trendLine.getFormula();
        System.out.println("The type of Trendline: "+ type + "\n"
                + "The name of Trendline：" + name + "\n"
                + "The equation of Trendline：" + equation);
    }
}
Output

Delete Trendline
import com.spire.xls.*;

public class DeleteTrendLine {
    public static void main(String[] args) {
        //Load the excel file used to test
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\AddTrendline.xlsx");

        //Get the first chart in the first worksheet
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);

        //Delete the Trendline of the chart
        chart.getSeries().get(1).getTrendLines().removeAt(0);

        //Save the resulting document
        workbook.saveToFile("output/DeleteTrendLine.xlsx",FileFormat.Version2013);
        workbook.dispose();
    }
}