Sparkline is a tiny chart that can be inserted in cells to represent the trends in a series of values. Spire.XLS enables programmers to select a data cell range and display sparkline in another cell, usually next to data range. This article gives an example in C# and VB.NET to show how this purpose is achieved with a few lines of code.
Code Snippet:
Step 1: Create a new Workbook and load the sample file.
Workbook wb = new Workbook();
wb.LoadFromFile("sample.xlsx");
Step 2: Get the Worksheet from Workbook.
Worksheet ws = wb.Worksheets[0];
Step 3: Set SparklineType as line and apply line sparkline to SparklineCollection.
SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Line); SparklineCollection sparklines = sparklineGroup.Add();
Step 4: Call SparklineCollection.Add(DateRange, ReferenceRange) mothed get data from a range of cells and display sparkline chart inside another cell, e.g., a sparkline in E2 shows the trend of values from A2 to D2.
sparklines.Add(ws["A2:D2"], ws["E2"]); sparklines.Add(ws["A3:D3"], ws["E3"]); sparklines.Add(ws["A4:D4"], ws["E4"]); sparklines.Add(ws["A5:D5"], ws["E5"]);
Step 5: Save the file.
wb.SaveToFile("output.xlsx",ExcelVersion.Version2010);
Output:
Full Code:
using Spire.Xls;
namespace InsertSparkline
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.LoadFromFile("sample.xlsx");
Worksheet ws = wb.Worksheets[0];
SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(ws["A2:D2"], ws["E2"]);
sparklines.Add(ws["A3:D3"], ws["E3"]);
sparklines.Add(ws["A4:D4"], ws["E4"]);
sparklines.Add(ws["A5:D5"], ws["E5"]);
wb.SaveToFile("output.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace InsertSparkline
Class Program
Private Shared Sub Main(args As String())
Dim wb As New Workbook()
wb.LoadFromFile("sample.xlsx")
Dim ws As Worksheet = wb.Worksheets(0)
Dim sparklineGroup As SparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Line)
Dim sparklines As SparklineCollection = sparklineGroup.Add()
sparklines.Add(ws("A2:D2"), ws("E2"))
sparklines.Add(ws("A3:D3"), ws("E3"))
sparklines.Add(ws("A4:D4"), ws("E4"))
sparklines.Add(ws("A5:D5"), ws("E5"))
wb.SaveToFile("output.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
