How to: Use Worksheet Table as a Data Source

• Mar 01, 2024
• 4 minutes to read

In this example, a worksheet containing a table with data is loaded in a Workbook instance. The Table.GetDataSource method returns a data source which is subsequently used to create a report in a RichEditControl.

View Example: Spreadsheet Document API - Use a Worksheet Table as a Data Source

To modify worksheet data before they are exposed as data source fields, the application utilizes a custom MyPictureProvider converter which implements the IBindingRangeValueConverter interface. The MyPictureProvider converter finds a picture in a worksheet by its name and returns a picture bitmap instead of the name specified in a worksheet column.

MyPictureProvider.cs

public class MyPictureProvider : IBindingRangeValueConverter
{
    Dictionary<string, Bitmap> pictures;

    public MyPictureProvider(Worksheet sheet)
    {
        pictures = GetPictures(sheet);
    }

    public object ConvertToObject(CellValue value, Type requiredType, int columnIndex)
    {
        if (columnIndex == 13)
        {
            Bitmap pic;
            if (pictures.TryGetValue(value.TextValue, out pic))
                return pic;
        }
        return value;
    }

    public CellValue TryConvertFromObject(object value)
    {
        return CellValue.Empty;
    }

    public Dictionary<string, Bitmap> GetPictures(Worksheet sheet)
    {
        Dictionary<string, Bitmap> employeePictures = new Dictionary<string, System.Drawing.Bitmap>();
        foreach (Picture pic in sheet.Pictures)
        {
            employeePictures.Add(pic.Name, new Bitmap(new MemoryStream(pic.Image.GetImageBytes(OfficeImageFormat.Bmp))));
        }
        return employeePictures;
    }
}

MyPictureProvider.vb

Public Class MyPictureProvider
    Implements IBindingRangeValueConverter

    Private pictures As Dictionary(Of String, Bitmap)

    Public Sub New(ByVal sheet As Worksheet)
        pictures = GetPictures(sheet)
    End Sub

    Public Function ConvertToObject(ByVal value As CellValue, ByVal requiredType As Type, ByVal columnIndex As Integer) As Object Implements IBindingRangeValueConverter.ConvertToObject
        If columnIndex = 13 Then
            Dim pic As Bitmap = Nothing
            If pictures.TryGetValue(value.TextValue, pic) Then
                Return pic
            End If
        End If
        Return value
    End Function

    Public Function TryConvertFromObject(ByVal value As Object) As CellValue Implements IBindingRangeValueConverter.TryConvertFromObject
        Return CellValue.Empty
    End Function

    Public Function GetPictures(ByVal sheet As Worksheet) As Dictionary(Of String, Bitmap)
        Dim employeePictures As Dictionary(Of String, Bitmap) = New Dictionary(Of String, System.Drawing.Bitmap)()
        For Each pic As Picture In sheet.Pictures
            employeePictures.Add(pic.Name, New Bitmap(New MemoryStream(pic.Image.GetImageBytes(OfficeImageFormat.Bmp))))
        Next pic
        Return employeePictures
    End Function
End Class

The custom MyColumnDetector object which implements the IDataSourceColumnTypeDetector interface is used to specify column names and types.

MyColumnDetector.cs

class MyColumnDetector : IDataSourceColumnTypeDetector
{
    public string GetColumnName(int index, int offset, CellRange range)
    {
        return range[-1, offset].DisplayText;
    }

    public Type GetColumnType(int index, int offset, CellRange range)
    {
        Type defaultType = typeof(string);

        if (offset == 13) return typeof(System.Drawing.Bitmap);

        CellValue value = range[0, offset].Value;
        if (value.IsText) return typeof(string);
        if (value.IsBoolean) return typeof(bool);
        if (value.IsDateTime) return typeof(DateTime);
        if (value.IsNumeric) return typeof(double);
        return defaultType;
    }
}

MyColumnDetector.vb

Friend Class MyColumnDetector
    Implements IDataSourceColumnTypeDetector

    Public Function GetColumnName(ByVal index As Integer, ByVal offset As Integer, ByVal range As CellRange) As String Implements IDataSourceColumnTypeDetector.GetColumnName
        Return range(-1, offset).DisplayText
    End Function

    Public Function GetColumnType(ByVal index As Integer, ByVal offset As Integer, ByVal range As CellRange) As Type Implements IDataSourceColumnTypeDetector.GetColumnType
        Dim defaultType As Type = GetType(String)

        If offset = 13 Then
            Return GetType(System.Drawing.Bitmap)
        End If

        Dim value As CellValue = range(0, offset).Value
        If value.IsText Then
            Return GetType(String)
        End If
        If value.IsBoolean Then
            Return GetType(Boolean)
        End If
        If value.IsDateTime Then
            Return GetType(Date)
        End If
        If value.IsNumeric Then
            Return GetType(Double)
        End If
        Return defaultType
    End Function
End Class

The converter and column detector are specified using the DataSourceOptionsBase.CellValueConverter and RangeDataSourceOptions.DataSourceColumnTypeDetector properties of the RangeDataSourceOptions instance which is passed as a Table.GetDataSource method parameter.

Form1.cs

}

private void Form1_Load(object sender, EventArgs e)
{
    using (Workbook wb = new Workbook())
    {
        wb.LoadDocument("Employees.xlsx");
        RangeDataSourceOptions options = new RangeDataSourceOptions();
        options.UseFirstRowAsHeader = true;
        options.CellValueConverter = new MyPictureProvider(wb.Worksheets[0]);
        options.DataSourceColumnTypeDetector = new MyColumnDetector();
        string dsName = wb.Worksheets[0].Tables[0].Name;

        var document = richEditControl1.Document;
        RangeDataSource rows = wb.Worksheets[0].Tables[0].GetDataSource(options) as RangeDataSource;
        var columns = rows[0].GetProperties();
        List<PropertyDescriptor> columnsToDisplay = new List<PropertyDescriptor>()
            {
                columns.Find("First Name", false),
                columns.Find("Last Name", false),
                columns.Find("Photo", false)
            };
        document.BeginUpdate();

        var table = InitTable(document, rows.Count, columnsToDisplay.Count);

        FillTable(document, table, rows, columnsToDisplay);

        document.EndUpdate();
    }
}

Form1.vb

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    Using wb As Workbook = New Workbook()
        wb.LoadDocument("Employees.xlsx")
        Dim options As RangeDataSourceOptions = New RangeDataSourceOptions()
        options.UseFirstRowAsHeader = True
        options.CellValueConverter = New MyPictureProvider(wb.Worksheets(0))
        options.DataSourceColumnTypeDetector = New MyColumnDetector()
        Dim dsName As String = wb.Worksheets(0).Tables(0).Name

        Dim document = RichEditControl1.Document
        Dim rows As RangeDataSource = TryCast(wb.Worksheets(0).Tables(0).GetDataSource(options), RangeDataSource)
        Dim columns = rows(0).GetProperties()
        Dim columnsToDisplay As List(Of PropertyDescriptor) = New List(Of PropertyDescriptor)() From {
                columns.Find("First Name", False),
                columns.Find("Last Name", False),
                columns.Find("Photo", False)
        }
        document.BeginUpdate()

        Dim table = InitTable(document, rows.Count, columnsToDisplay.Count)

        FillTable(document, table, rows, columnsToDisplay)

        document.EndUpdate()
    End Using
End Sub