Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

How to: Use Worksheet Table as a Data Source

  • 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

Use 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.

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

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

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

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.

}

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