The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.

How to: Use Worksheet Table as a Data Source

  • 4 min 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 SnapControl.

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.

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

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.

Note that a Snap report template, which is required to visualize the data in the Snap™ application, is created in code at runtime. However, you can readily modify it using the Snap™ application UI.

Use Worksheet Table as a Data Source

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;
    }
}