Java create / edit / delete Excel Mini chart

Summary

A sparkline is a miniature chart that represents data in Excel worksheet cells. Using the sparkline can display the trend of data change intuitively, highlight the maximum value and the minimum value, and put them in the data table can play a good data analysis effect. This article introduces how to create Mini chart in Excel, edit and delete Mini chart in table through Java code example.
Program running environment: Java, IDEA, jdk1.8.0, no need to install Microsoft Excel
Use class library: free fire.xls for Java (free version)
Class library acquisition and import: Official Website Download jar package , and unzip, and import the jar file under the lib folder into the java program. Or through maven warehouse download and import To Maven project. The import effect is as follows:

Java sample code

1. Create a mini chart

import com.spire.xls.*;

import java.awt.*;

public class CreateSparklineChart {
    public static void main(String[] args) {
        //Create a Workbook class object and load an Excel document
        Workbook workbook = new Workbook();
        workbook.loadFromFile(  "test.xlsx");

        //Get first sheet 
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add line sparkline, format line sparkline
        SparklineGroup sparklineGroup1 = sheet.getSparklineGroups().addGroup();
        sparklineGroup1.setSparklineType(SparklineType.Line);
        sparklineGroup1.setSparklineColor(new Color(153,50,204));
        sparklineGroup1.setShowHighPoint(true);
        //Set cells for adding discount Mini chart and data range generated by chart
        SparklineCollection sparklines1 = sparklineGroup1.add();
        sparklines1.add(sheet.getCellRange("B3:F3"), sheet.getCellRange("G3"));

        //Add column sparkline and set chart color
        SparklineGroup sparklineGroup2 = sheet.getSparklineGroups().addGroup();
        sparklineGroup2.setSparklineType(SparklineType.Column);
        sparklineGroup2.setSparklineColor(new Color(244,164,96));
        sparklineGroup2.setShowHighPoint(true);
        //Set the cells for adding the column Mini chart and the data range generated by the chart
        SparklineCollection sparklines2 = sparklineGroup2.add();
        sparklines2.add(sheet.getCellRange("B4:F4"), sheet.getCellRange("G4"));

        //Add profit and loss sparkline and set color
        SparklineGroup sparklineGroup3 = sheet.getSparklineGroups().addGroup();
        sparklineGroup3.setSparklineType(SparklineType.Stacked);
        sparklineGroup3.setSparklineColor(new Color(255,20,147));
        sparklineGroup3.setShowHighPoint(true);
        //Set the cells of the profit and loss Mini chart and the data range generated by the chart
        SparklineCollection sparklines3 = sparklineGroup3.add();
        sparklines3.add(sheet.getCellRange("B5:F5"), sheet.getCellRange("G5"));

        //Save document
        workbook.saveToFile("AddSparkline.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Mini chart creation effect:

2. Edit the mini chart

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.ISparklineGroup;
import com.spire.xls.core.spreadsheet.ISparklines;

public class ModifySparkline {
    public static void main(String[] args) {
        //Create instance, load document
        Workbook wb = new Workbook();
        wb.loadFromFile("AddSparkline.xlsx");

        //Get worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Modify the sparkline type and data range in the second sparkline group
        ISparklineGroup sparklineGroup = sheet.getSparklineGroups().get(1);
        sparklineGroup.setSparklineType(SparklineType.Line);
        ISparklines sparklines = sparklineGroup.get(1);
        sparklines.refreshRanges(sheet.getCellRange("C4:F4"), sheet.getCellRange("G4"));

        //Save document
        wb.saveToFile("ModifySparkline.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Mini chart modification result:

3. Delete the mini chart

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.ISparklineGroup;

public class ClearSparkline {
    public static void main(String[] args) {
        //Create instance, load document
        Workbook wb = new Workbook();
        wb.loadFromFile("AddSparkline.xlsx");

        //Get worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Modify the sparkline type and data range in the third sparkline group
        ISparklineGroup sparklineGroup = sheet.getSparklineGroups().get(2);

        //Remove chart from sheet
        sheet.getSparklineGroups().clear((SparklineGroup) sparklineGroup);

        wb.saveToFile("ClearSparkline.xlsx",ExcelVersion.Version2013);
        wb.dispose();
    }
}

Mini chart delete effect:

(end of this paper)

Tags: Programming Java Excel Maven

Posted on Thu, 26 Mar 2020 11:11:15 -0400 by dank