Preservation of Sparklines and Slicers in Excel files
GemBox.Spreadsheet supports many Microsoft Excel and LibreOffice (or Open Office) features, but not all. You can preserve unsupported features when reading a workbook so that they are not lost when writing to a workbook of the same format.
You can read more about GemBox.Spreadsheet's preservation feature on the Preservation help page.
The following example shows how to preserve sparklines and slicers in an Excel file from input to output using C# and VB.NET.
using System;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
// Load Excel file with preservation feature enabled.
var loadOptions = new XlsxLoadOptions() { PreserveUnsupportedFeatures = true };
var workbook = ExcelFile.Load("%#SparklinesAndSlicers.xlsx%", loadOptions);
// Modify all values in column C, set them to some random value.
var readEnumerator = workbook.Worksheets[0].Columns["C"].Cells.GetReadEnumerator();
var random = new Random();
while (readEnumerator.MoveNext())
{
var cell = readEnumerator.Current;
if (cell.ValueType == CellValueType.Int)
cell.SetValue(random.Next(-10, 10));
}
workbook.Save("Preserved Output.xlsx");
}
}Imports System
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
' Load Excel file with preservation feature enabled.
Dim loadOptions As New XlsxLoadOptions() With {.PreserveUnsupportedFeatures = True}
Dim workbook = ExcelFile.Load("%#SparklinesAndSlicers.xlsx%", loadOptions)
' Modify all values in column C, set them to some random value.
Dim readEnumerator = workbook.Worksheets(0).Columns("C").Cells.GetReadEnumerator()
Dim random As New Random()
While readEnumerator.MoveNext()
Dim cell = readEnumerator.Current
If cell.ValueType = CellValueType.Int Then cell.SetValue(random.Next(-10, 10))
End While
workbook.Save("Preserved Output.xlsx")
End Sub
End ModuleSee also
Next steps
Published: February 10, 2020 | Modified: May 15, 2023 | Author: Mario Zorica