Spreadsheet Document API - How to Import Data to a Worksheet from Different Data Sources

This example illustrates how to use the Worksheet.Import method to import data to worksheet cells from different data sources (arrays, lists and data tables), and how to format a range of cells as a table.

Note

Worksheet extensions are defined by the WorksheetExtensions class. To enable them, add a reference to the DevExpress.Docs.vX.Y.dll library. Note that distribution of this assembly requires a license to the Office File API or the DevExpress Universal Subscription.

Files to Review

Form1.cs (VB: Form1.vb)
TestDataValueConverter.cs (VB: TestDataValueConverter.vb)
TestObject.cs (VB: TestObject.vb)

Documentation

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

DataImportExample/Form1.cs(vb)

C#
VB.NET

C#
using DevExpress.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;

namespace DataImportExample
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {
        byte[] imageBytes1 = File.ReadAllBytes("images//img.png");
        byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png");
        public Form1()
        {
            InitializeComponent();
        }

        private void btnImportDataTable_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.BeginUpdate();
            try
            {
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Clear(worksheet.GetUsedRange());
                ImportDataTable(worksheet);
                CreateTable(worksheet);
            }
            finally
            {
                workbook.EndUpdate();
                workbook.SaveDocument("result.xlsx");
                Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            }
            workbook.Dispose();
        }

        private void btnImportArray_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.BeginUpdate();
            try
            {
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Clear(worksheet.GetUsedRange());
                worksheet.Cells["A1"].ColumnWidthInCharacters = 35;
                worksheet.Cells["A1"].Value = "Import an array horizontally:";
                worksheet.Cells["A3"].Value = "Import a two-dimensional array:";                
                ImportArrays(worksheet);
            }
            finally
            {
                workbook.EndUpdate();
                workbook.SaveDocument("result.xlsx");
                Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            }
            workbook.Dispose();
        }

        private void btnImportList_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.BeginUpdate();
            try
            {
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Clear(worksheet.GetUsedRange());
                worksheet.Cells["A1"].ColumnWidthInCharacters = 35;
                worksheet.Cells["A1"].Value = "Import data from List vertically:";
                ImportList(worksheet);
            }
            finally
            {
                workbook.EndUpdate();
                workbook.SaveDocument("result.xlsx");
                Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            }
            workbook.Dispose();
        }

        private void btnArrayList_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.BeginUpdate();
            try
            {
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Clear(worksheet.GetUsedRange());
                ImportArrayList(worksheet);
            }
            finally
            {
                workbook.EndUpdate();
                workbook.SaveDocument("result.xlsx");
                Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });

            }
        }

        #region #ImportDataTable
        void ImportDataTable(Worksheet worksheet)
        {
            // Create a "Products" DataTable object with four columns.
            DataTable sourceTable = new DataTable("Products");
            sourceTable.Columns.Add("Product", typeof(string));
            sourceTable.Columns.Add("Price", typeof(float));
            sourceTable.Columns.Add("Quantity", typeof(Int32));
            sourceTable.Columns.Add("Discount", typeof(float));
            sourceTable.Columns.Add("Image", typeof(byte[]));

            sourceTable.Rows.Add("Chocolade", 5, 15, 0.03, imageBytes1);
            sourceTable.Rows.Add("Konbu", 9, 55, 0.1, imageBytes1);
            sourceTable.Rows.Add("Geitost", 15, 70, 0.07, imageBytes2);

            // Import data from the data table into the worksheet and insert it, starting with cell B2.
            worksheet.Import(sourceTable, true, 1, 1);

        }
        #endregion #ImportDataTable

        #region #CreateTable
        void CreateTable(Worksheet worksheet)
        {
            // Insert a table in the worksheet.
            Table table = worksheet.Tables.Add(worksheet["B2:G5"], true);

            // Format the table by applying a built-in table style.
            table.Style = worksheet.Workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium27];

            // Access table columns.
            TableColumn productColumn = table.Columns[0];
            TableColumn priceColumn = table.Columns[1];
            TableColumn quantityColumn = table.Columns[2];
            TableColumn discountColumn = table.Columns[3];
            TableColumn imageColumn = table.Columns[4];
            TableColumn amountColumn = table.Columns[5];

            // Set the name of the last column. 
            amountColumn.Name = "Amount";

            // Set the formula to calculate the amount per product 
            // and display results in the "Amount" column.
            amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])";

            // Display the total row in the table.
            table.ShowTotals = true;

            // Set the label and function to display the sum of the "Amount" column.
            discountColumn.TotalRowLabel = "Total:";
            amountColumn.TotalRowFunction = TotalRowFunction.Sum;

            // Specify the number format for each column.
            priceColumn.DataRange.NumberFormat = "$#,##0.00";
            discountColumn.DataRange.NumberFormat = "0.0%";
            amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;\"\";@";

            // Specify horizontal alignment for header and total rows of the table.
            table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

            // Specify horizontal alignment to display data in all columns except the first one.
            for (int i = 1; i < table.Columns.Count; i++)
            {
                table.Columns[i].DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            }

            // Set the width of table columns.
            table.Range.ColumnWidthInCharacters = 10;
        }
        #endregion #CreateTable

        #region #ImportArrays
        void ImportArrays(Worksheet worksheet)
        {
            // Create an array containing string values.
            string[] array = new string[] { "AAA", "BBB", "CCC", "DDD" };

            // Import the array into the worksheet and insert it horizontally, starting with cell B1.
            worksheet.Import(array, 0, 1, false);

            // Create a two-dimensional array containing string values.
            String[,] names = new String[2, 4]{
            {"Ann", "Edward", "Angela", "Alex"},
            {"Rachel", "Bruce", "Barbara", "George"}
                 };

            // Import the two-dimensional array into the worksheet and insert it, starting with cell B3.
            worksheet.Import(names, 2, 1);


        }
        #endregion #ImportArrays

        #region #ImportList
        void ImportList(Worksheet worksheet)
        {
            // Create a List object containing string values.
            List<string> cities = new List<string>
            {
                "New York",
                "Rome",
                "Beijing",
                "Delhi"
            };


            List<byte[]> imageList = new List<byte[]>();
            {
                imageList.Add(imageBytes1);
                imageList.Add(imageBytes2);
            };

            // Import the list into the worksheet and insert it vertically, starting with cell B1.
            worksheet.Import(cities, 0, 1, true);

            // Import the image list into the worksheet and insert it vertically
            worksheet.Import(imageList, 0, 2, true, new DataImportOptions());
        }
        #endregion #ImportList

        #region #ImportArrayList
        void ImportArrayList(Worksheet worksheet)
        {
            System.Collections.ArrayList listDataSource = new System.Collections.ArrayList()
            {
                new TestObject(1, "Jane", true, imageBytes1),
                new TestObject(2, "Joe", false, imageBytes2),
                new TestObject(3, "Bill", true, imageBytes1),
                new TestObject(4, "Michael", false, imageBytes2),
            };
            worksheet.Import(listDataSource, 0, 0);
        }
        #endregion #ImportArrayList


        private void btnImportObject_Click(object sender, EventArgs e)
        {
            #region #ImportObject
            Workbook workbook = new Workbook();
            var worksheet = workbook.Worksheets[0];
            worksheet.Clear(worksheet.GetUsedRange());

            worksheet.Import(new TestObject(1, "1", true, imageBytes1), 0, 0);

            workbook.SaveDocument("result.xlsx");
            Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            #endregion #ImportObject
        }


        private void btnUseOptions_Click(object sender, EventArgs e)
        {
            #region #ImportUsingOptions
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Clear(worksheet.GetUsedRange());

            string[] arrayR1C1 = new string[] { "a", "b", "=R1C1&R1C2" };
            worksheet.Import(arrayR1C1, 0, 0, false, new DataImportOptions() { ImportFormulas = true, ReferenceStyle = ReferenceStyle.R1C1 });

            string[] arrayLocalized = new string[] { "a", "=1,2+1" };
            worksheet.Import(arrayLocalized, 1, 0, false,
                new DataImportOptions() { ImportFormulas = true, FormulaCulture = new System.Globalization.CultureInfo("de-DE") });
            workbook.SaveDocument("result.xlsx");
            Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            #endregion #ImportUsingOptions
        }

        private void btnUseFields_Click(object sender, EventArgs e)
        {
            #region #ImportSpecifiedFields
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Clear(worksheet.GetUsedRange());

            List<TestObject> list = new List<TestObject> { new TestObject(1, "1", true, imageBytes1), new TestObject(2, "2", false, imageBytes2) };
            worksheet.Import(list, 0, 0, new DataSourceImportOptions() { PropertyNames = new string[] { "BoolValue", "ImageValue" } });
            workbook.SaveDocument("result.xlsx");
            Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            #endregion #ImportSpecifiedFields
        }

        private void btnUseConverter_Click(object sender, EventArgs e)
        {
            #region #ImportUsingConverter
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Clear(worksheet.GetUsedRange());

            string imageBase64 = Convert.ToBase64String(imageBytes1);

            List<TestObject> list = [new TestObject(1, "1", true, imageBase64), new TestObject(2, "2", false, imageBase64)];
            worksheet.Import(list, 0, 0, new DataSourceImportOptions()
            {
                Converter = new TestDataValueConverter(),
                PropertyNames = new string[] { "IntValue", "Value", "BoolValue", "ImageBase64" }
            });

            workbook.SaveDocument("result.xlsx");
            Process.Start(new ProcessStartInfo("result.xlsx") { UseShellExecute = true });
            #endregion #ImportUsingConverter
        }
    }
}

Visual Basic
Imports System.Data
Imports System.IO
Imports DevExpress.Spreadsheet

Namespace DataImportExample
    Partial Public Class Form1
        Inherits DevExpress.XtraEditors.XtraForm

        Private imageBytes1() As Byte = File.ReadAllBytes("images//img.png")
        Private imageBytes2() As Byte = File.ReadAllBytes("images//x-docserver.png")
        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub btnImportDataTable_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImportDataTable.Click
            Dim workbook As New Workbook()
            workbook.BeginUpdate()
            Try
                Dim worksheet As Worksheet = workbook.Worksheets(0)
                worksheet.Clear(worksheet.GetUsedRange())
                ImportDataTable(worksheet)
                CreateTable(worksheet)
            Finally
                workbook.EndUpdate()
                workbook.SaveDocument("result.xlsx")
                Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            End Try
            workbook.Dispose()
        End Sub

        Private Sub btnImportArray_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImportArray.Click
            Dim workbook As New Workbook()
            workbook.BeginUpdate()
            Try
                Dim worksheet As Worksheet = workbook.Worksheets(0)
                worksheet.Clear(worksheet.GetUsedRange())
                worksheet.Cells("A1").ColumnWidthInCharacters = 35
                worksheet.Cells("A1").Value = "Import an array horizontally:"
                worksheet.Cells("A3").Value = "Import a two-dimensional array:"
                worksheet.Cells("A5").Value = "Import image data:"
                ImportArrays(worksheet)
            Finally
                workbook.EndUpdate()
                workbook.SaveDocument("result.xlsx")
                Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            End Try
            workbook.Dispose()
        End Sub

        Private Sub btnImportList_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImportList.Click
            Dim workbook As New Workbook()
            workbook.BeginUpdate()
            Try
                Dim worksheet As Worksheet = workbook.Worksheets(0)
                worksheet.Clear(worksheet.GetUsedRange())
                worksheet.Cells("A1").ColumnWidthInCharacters = 35
                worksheet.Cells("A1").Value = "Import data from List vertically:"
                ImportList(worksheet)
            Finally
                workbook.EndUpdate()
                workbook.SaveDocument("result.xlsx")
                Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            End Try
            workbook.Dispose()
        End Sub

        Private Sub btnArrayList_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImportArrayList.Click
            Dim workbook As New Workbook()
            workbook.BeginUpdate()
            Try
                Dim worksheet As Worksheet = workbook.Worksheets(0)
                worksheet.Clear(worksheet.GetUsedRange())
                ImportArrayList(worksheet)
            Finally
                workbook.EndUpdate()
                workbook.SaveDocument("result.xlsx")
                Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            End Try
        End Sub

#Region "#ImportDataTable"
        Private Sub ImportDataTable(ByVal worksheet As Worksheet)
            ' Create a "Products" DataTable object with four columns.
            Dim sourceTable As New DataTable("Products")
            sourceTable.Columns.Add("Product", GetType(String))
            sourceTable.Columns.Add("Price", GetType(Single))
            sourceTable.Columns.Add("Quantity", GetType(Int32))
            sourceTable.Columns.Add("Discount", GetType(Single))
            sourceTable.Columns.Add("Image", GetType(Byte()))

            sourceTable.Rows.Add("Chocolade", 5, 15, 0.03, imageBytes1)
            sourceTable.Rows.Add("Konbu", 9, 55, 0.1, imageBytes1)
            sourceTable.Rows.Add("Geitost", 15, 70, 0.07, imageBytes2)

            ' Import data from the data table into the worksheet and insert it, starting with cel0l B2.
            worksheet.Import(sourceTable, True, 1, 1)
        End Sub
#End Region ' #ImportDataTable

#Region "#CreateTable"
        Private Sub CreateTable(ByVal worksheet As Worksheet)
            ' Insert a table in the worksheet.
            Dim table As Table = worksheet.Tables.Add(worksheet("B2:G5"), True)

            ' Format the table by applying a built-in table style.
            table.Style = worksheet.Workbook.TableStyles(BuiltInTableStyleId.TableStyleMedium27)

            ' Access table columns.
            Dim productColumn As TableColumn = table.Columns(0)
            Dim priceColumn As TableColumn = table.Columns(1)
            Dim quantityColumn As TableColumn = table.Columns(2)
            Dim discountColumn As TableColumn = table.Columns(3)
            Dim imageColumn As TableColumn = table.Columns(4)
            Dim amountColumn As TableColumn = table.Columns(5)

            ' Set the name of the last column. 
            amountColumn.Name = "Amount"

            ' Set the formula to calculate the amount per product 
            ' and display results in the "Amount" column.
            amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])"

            ' Display the total row in the table.
            table.ShowTotals = True

            ' Set the label and function to display the sum of the "Amount" column.
            discountColumn.TotalRowLabel = "Total:"
            amountColumn.TotalRowFunction = TotalRowFunction.Sum

            ' Specify the number format for each column.
            priceColumn.DataRange.NumberFormat = "$#,##0.00"
            discountColumn.DataRange.NumberFormat = "0.0%"
            amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;"""";@"

            ' Specify horizontal alignment for header and total rows of the table.
            table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
            table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center

            ' Specify horizontal alignment to display data in all columns except the first one.
            For i As Integer = 1 To table.Columns.Count - 1
                table.Columns(i).DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
            Next i

            ' Set the width of table columns.
            table.Range.ColumnWidthInCharacters = 10
        End Sub
#End Region ' #CreateTable

#Region "#ImportArrays"
        Private Sub ImportArrays(ByVal worksheet As Worksheet)
            ' Create an array containing string values.
            Dim array() As String = {"AAA", "BBB", "CCC", "DDD"}

            ' Import the array into the worksheet and insert it horizontally, starting with cell B1.
            worksheet.Import(array, 0, 1, False)

            ' Create a two-dimensional array containing string values.
            Dim names(,) As String = {
                {"Ann", "Edward", "Angela", "Alex"},
                {"Rachel", "Bruce", "Barbara", "George"}
            }

            ' Import the two-dimensional array into the worksheet and insert it, starting with cell B3.
            worksheet.Import(names, 2, 1)
        End Sub
#End Region ' #ImportArrays

#Region "#ImportList"
        Private Sub ImportList(ByVal worksheet As Worksheet)
            ' Create a List object containing string values.
            Dim cities As New List(Of String) From {"New York", "Rome", "Beijing", "Delhi"}

            Dim images As New List(Of Byte()) From {imageBytes1, imageBytes2}

            ' Import the list into the worksheet and insert it vertically, starting with cell B1.
            worksheet.Import(cities, 0, 1, True)
            worksheet.Import(images, 2, 1)
        End Sub
#End Region ' #ImportList

#Region "#ImportArrayList"
        Private Sub ImportArrayList(ByVal worksheet As Worksheet)
            Dim listDataSource As New ArrayList() From {
    New TestObject(1, "Jane", True, imageBytes1),
    New TestObject(2, "Joe", False, imageBytes2),
    New TestObject(3, "Bill", True, imageBytes1),
    New TestObject(4, "Michael", False, imageBytes2)
}
            worksheet.Import(listDataSource, 0, 0)
        End Sub
#End Region ' #ImportArrayList


        Private Sub btnImportObject_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImportObject.Click
            '            #Region "#ImportObject"
            Dim workbook As New Workbook()
            Dim worksheet = workbook.Worksheets(0)
            worksheet.Clear(worksheet.GetUsedRange())

            worksheet.Import(New TestObject(1, "1", True, imageBytes1), 0, 0)

            workbook.SaveDocument("result.xlsx")
            Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            '            #End Region ' #ImportObject
        End Sub


        Private Sub btnUseOptions_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUseOptions.Click
            '            #Region "#ImportUsingOptions"
            Dim workbook As New Workbook()
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            worksheet.Clear(worksheet.GetUsedRange())

            Dim arrayR1C1() As String = {"a", "b", "=R1C1&R1C2"}
            worksheet.Import(arrayR1C1, 0, 0, False, New DataImportOptions() With {
                .ImportFormulas = True,
                .ReferenceStyle = ReferenceStyle.R1C1
            })

            Dim arrayLocalized() As String = {"a", "=1,2+1"}
            worksheet.Import(arrayLocalized, 1, 0, False, New DataImportOptions() With {
                .ImportFormulas = True,
                .FormulaCulture = New System.Globalization.CultureInfo("de-DE")
            })
            workbook.SaveDocument("result.xlsx")
            Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            '            #End Region ' #ImportUsingOptions
        End Sub

        Private Sub btnUseFields_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUseFields.Click
            '            #Region "#ImportSpecifiedFields"
            Dim workbook As New Workbook()
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            worksheet.Clear(worksheet.GetUsedRange())

            Dim list As List(Of TestObject) = New List(Of TestObject) From {
    New TestObject(1, "1", True, imageBytes1),
    New TestObject(2, "2", False, imageBytes2)
}
            worksheet.Import(list, 0, 0, New DataSourceImportOptions() With {
                .PropertyNames = New String() {"BoolValue", "ImageValue"}
            })
            workbook.SaveDocument("result.xlsx")
            Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
            '            #End Region ' #ImportSpecifiedFields
        End Sub

        Private Sub btnUseConverter_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUseConverter.Click
#Region "#ImportUsingConverter"
            Dim workbook As New Workbook()
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            worksheet.Clear(worksheet.GetUsedRange())

            Dim imageBase64 As String = Convert.ToBase64String(imageBytes1)

            Dim list As List(Of TestObject) = New List(Of TestObject) From {
    New TestObject(1, "1", True, imageBytes1),
    New TestObject(2, "2", False, imageBytes2)
}
            worksheet.Import(list, 0, 0, New DataSourceImportOptions() With {
                .Converter = New TestDataValueConverter(),
                .PropertyNames = New String() {"IntValue", "Value", "BoolValue", "ImageBase64"}
            })

            workbook.SaveDocument("result.xlsx")
            Process.Start(New ProcessStartInfo("result.xlsx") With {.UseShellExecute = True})
#End Region ' #ImportUsingConverter
        End Sub
    End Class
End Namespace

DataImportExample/TestDataValueConverter.cs(vb)

C#
VB.NET

C#
using DevExpress.Drawing;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataImportExample
{
    #region #converter
    class TestDataValueConverter : DevExpress.Spreadsheet.IDataValueConverter
    {
        public bool TryConvert(object value, int columnIndex, out DevExpress.Spreadsheet.CellValue result)
        {
            switch (value)
            {
                case string strValue:
                    try
                    {
                        result = DXImage.FromBase64String(strValue);
                    }
                    catch
                    {
                        int str2int = 0;
                        if (Int32.TryParse(strValue, out str2int))
                        {
                            result = str2int;
                        }
                        else
                        {
                            result = strValue;
                        }
                    }
                    return true;
                case int intValue:
                    result = intValue;
                    return true;
                case bool boolValue:
                    result = boolValue;
                    return true;
                default:
                    result = value == null ? null : value.ToString();
                    return true;
            }
        }
    }
    #endregion #converter
}

Visual Basic
Imports DevExpress.Drawing
Imports DevExpress.Spreadsheet
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Runtime.InteropServices
Imports System.Text
Namespace DataImportExample
#Region "#converter"
    Friend Class TestDataValueConverter
        Implements IDataValueConverter
        Private Function IDataValueConverter_TryConvert(value As Object, index As Integer, ByRef result As CellValue) As Boolean Implements IDataValueConverter.TryConvert
            If TypeOf value Is String Then
                Dim strValue As String = TryCast(value, String)

                Try
                    result = DXImage.FromBase64String(strValue)
                Catch
                    Dim str2int As Integer = 0

                    If Int32.TryParse(strValue, str2int) Then
                        result = str2int
                    Else
                        result = strValue
                    End If
                End Try

                Return True
            End If

            result = DevExpress.Spreadsheet.CellValue.TryCreateFromObject(value)
            Return True
        End Function

    End Class
#End Region ' #converter
End Namespace

DataImportExample/TestObject.cs(vb)

C#
VB.NET

C#
namespace DataImportExample
{
    #region #testObject
    class TestObject
    {
        public TestObject(int intValue, string value, bool boolValue, byte[] imageValue)
        {
            this.intValue = intValue;
            this.Value = value;
            this.BoolValue = boolValue;
            this.ImageValue = imageValue;

        }

        public TestObject(int intValue, string value, bool boolValue, string imageBase64)
        {
            this.intValue = intValue;
            this.Value = value;
            this.BoolValue = boolValue;
            this.ImageBase64 = imageBase64;
        }
        public int intValue;
        private int privateValue { get { return 123; } }
        public int IntValue { get { return intValue + privateValue - 123; } }
        public string Value { get; set; }
        public bool BoolValue { get; set; }
        public byte[] ImageValue { get; set; }
        public int this[int index] { get { return index; } }
        public string ImageBase64 { get; set; }

    }
    #endregion #testObject
}

Visual Basic
Namespace DataImportExample
    #Region "#testObject"
    Friend Class TestObject
        Public Sub New(ByVal intValue As Integer, ByVal value As String, ByVal boolValue As Boolean, ByVal imageValue() As Byte)
            Me.intValue_Renamed = intValue
            Me.Value = value
            Me.BoolValue = boolValue
            Me.ImageValue = imageValue

        End Sub

        Public Sub New(ByVal intValue As Integer, ByVal value As String, ByVal boolValue As Boolean, ByVal imageBase64 As String)
            Me.intValue_Renamed = intValue
            Me.Value = value
            Me.BoolValue = boolValue
            Me.ImageBase64 = imageBase64
        End Sub
'INSTANT VB NOTE: The field intValue was renamed since Visual Basic does not allow fields to have the same name as other class members:
        Public intValue_Renamed As Integer
        Private ReadOnly Property privateValue() As Integer
            Get
                Return 123
            End Get
        End Property
        Public ReadOnly Property IntValue() As Integer
            Get
                Return intValue_Renamed + privateValue - 123
            End Get
        End Property
        Public Property Value() As String
        Public Property BoolValue() As Boolean
        Public Property ImageValue() As Byte()
        Default Public ReadOnly Property Item(ByVal index As Integer) As Integer
            Get
                Return index
            End Get
        End Property
        Public Property ImageBase64() As String

    End Class
    #End Region ' #testObject
End Namespace

Poline Fedorova (DevExpress)

last modified 2 years ago

Modified: April 20, 2023 10:49 AM

Platform: WinForms

Product: XtraSpreadsheet

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Spreadsheet Document API - How to Import Data to a Worksheet from Different Data Sources

Files to Review

Documentation

Does this example address your development requirements/objectives?

Example Code

Recently viewed tickets