C. how to add, modify and delete Excel Mini chart

The mini chart in Excel table can show us the trend of data change intuitively. This paper introduces how C ා can generate Mini chart for table data, and how to modify and delete Mini chart. It will be described in detail below.

Component tools used: Spire.XLS for .NET

Original Excel chart:

 

 

1, Add Mini chart (line chart, column chart, profit and loss chart)

1. Add namespace

using System;
using Spire.Xls;
using System.Drawing;

2. Main code

//Create a Workbook Class object and load Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Sample.xlsx"); 

//Get the first sheet, add text to a specific cell and format it
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["H2"].Text = "Foreign trade import/Trend of export volume";
sheet.Range["H2"].Style.Font.FontName = "Arial Narrow";
sheet.Range["H2"].Style.Font.Color = Color.Black;
sheet.Range["A1:H5"].Columns[7].ColumnWidth = 15F;
sheet.Range["H2"].Style.Font.IsBold = true;
sheet.Range["H2:H5"].BorderInside(LineStyleType.Thin);
sheet.Range["H2:H5"].BorderAround(LineStyleType.Thin);

//Add line sparkline
SparklineGroup sparklineGroup1 = sheet.SparklineGroups.AddGroup();
sparklineGroup1.SparklineType = SparklineType.Line;
//Format polyline sparkline
sparklineGroup1.SparklineColor = Color.Tomato;
sparklineGroup1.HighPointColor = Color.Red;
//Set cells for adding discount Mini chart and data range generated by chart
SparklineCollection sparklines1 = sparklineGroup1.Add();
sparklines1.Add(sheet["B3:G3"], sheet["H3"]);

//Add column sparkline and set chart color
SparklineGroup sparklineGroup2 = sheet.SparklineGroups.AddGroup();
sparklineGroup2.SparklineType = SparklineType.Column;
sparklineGroup2.SparklineColor = Color.PaleGreen;
sparklineGroup2.HighPointColor = Color.SeaGreen;
//Set the cells for adding the column Mini chart and the data range generated by the chart
SparklineCollection sparklines2 = sparklineGroup2.Add();
sparklines2.Add(sheet["B4:G4"], sheet["H4"]);
//Add profit and loss sparkline and set color SparklineGroup sparklineGroup3 = sheet.SparklineGroups.AddGroup(); sparklineGroup3.SparklineType = SparklineType.Stacked; sparklineGroup3.SparklineColor = Color.SkyBlue; sparklineGroup3.HighPointColor = Color.Blue; //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["B5:G5"], sheet["H5"]); //Save document workbook.SaveToFile("Add sparkline.xlsx", ExcelVersion.Version2010);

Debugging and running project program, generating files

 

Two. Modify the sparkline (Chart Type / data range)

1. Add namespace

using System;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;

2. Main code

//item base  Workbook Class, loading Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Add sparkline.xlsx");

//Get first sheet
Worksheet sheet = workbook.Worksheets[0];
//Modify the sparkline type and data range in the first sparkline group ISparklineGroup sparklineGroup = sheet.SparklineGroups[0]; sparklineGroup.SparklineType = SparklineType.Column; ISparklines sparklines = sparklineGroup[0]; sparklines.RefreshRanges(sheet.Range["C3:G3"], sheet.Range["H3"]); //Save document workbook.SaveToFile("Modify sparkline.xlsx", ExcelVersion.Version2010);

 

 

3, Delete Mini chart

1. Add namespace

using System;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;

2. Main code

//item base  Workbook Class, loading Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Add sparkline.xlsx");

//Get first sheet
Worksheet sheet = workbook.Worksheets[0];

//Get the second sparkline
ISparklineGroup sparklineGroup = sheet.SparklineGroups[1];

//Remove chart from sheet
sheet.SparklineGroups.Remove(sparklineGroup);

//Save file
workbook.SaveToFile("Delete sparkline.xlsx", ExcelVersion.Version2010);

 

 

All of the above is about the generation, modification and deletion of Excel Mini chart. I hope it can help you. If you think it's good, you are welcome to reprint it.

Thanks for browsing.

Tags: C# Excel

Posted on Wed, 27 May 2020 11:31:16 -0400 by bubazoo