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