2.42.10 GetData

Description

Get a block of data, within the specified row and column range, from the Worksheet.

Syntax

VB: Function GetData([ nRowStart As ByVal Object ], [ nColStart As ByVal Object ], [ nRowEnd As ByVal Object ], [ nColEnd As ByVal Object ], [ format As ByVal Object ], [ lowbound As ByVal Object ] ) As Object

C++: _variant_t GetData(_variant_t nRowStart, _variant_t nColStart, _variant_t nRowEnd, _variant_t nColEnd, _variant_t format, _variant_t lowbound )

C#: var GetData(var nRowStart, var nColStart, var nRowEnd, var nColEnd, var format, var lowbound )

Parameters

nRowStart

Starting (zero based) row index to get data from. This is optional argument, defaults to 0.

nColStart

Starting (zero based) column index to get data from. This is optional argument, defaults to 0.

nRowEnd

Ending (zero based) row index to get data from. This is optional argument, defaults to -1 (to the rest of worksheet).

nColEnd

Ending (zero based) column index to get data from. This is optional argument, defaults to -1 (to the rest of worksheet).

format

one of the ARRAYDATAFORMAT enumeration, please use only the 2D ones. For example ARRAY2D_NUMERIC.

lowbound

Optional array offset index. If not supplied, lowbound = 1 will be assumed.

Return

Array as a variant. Actual array type is dependent on the format argument.

Remark

Examples

VBA

The following examples will connect to an existing instance of Origin and load a relevant project file, which was saved by the Worksheet.SetData examples.

The first example will get numeric data in ARRAY2D_NUMERIC format from Origin and then search for some data in the data we got. If found then the index of the data will be printed to the Excel sheet.

Public Sub getNumericData()
    Dim app As Origin.ApplicationSI
    Dim Wks As Origin.Worksheet
    Dim Data As Variant
    Dim ii As Integer
 
    Set app = New Origin.ApplicationSI     'Connect to the running Origin
    app.Load (app.Path(APPPATH_USER) + "COMServerExamples_WN.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Get data as Numeric.
    Data = Wks.getData(0, 0, -1, -1, ARRAY2D_NUMERIC)
    'Search for data
    For ii = 1 To UBound(Data)
        If Data(ii, 1) = (63 * 0.01 - 1.23) Then Range("A1") = ii
        If Data(ii, 2) = (29 / 12.8 - 0.75) Then Range("A2") = ii
        If Data(ii, 3) = (41 + 0.123) Then Range("A3") = ii
    Next
End Sub

The next example will get text data in ARRAY2D_TEXT format from Origin and then search for some data in the data we got. If found then the index of the data will be printed to the Excel sheet.

Public Sub getTextData()
    Dim app As Origin.ApplicationSI
    Dim Wks As Origin.Worksheet
    Dim Data As Variant
    Dim ii As Integer
 
    Set app = New Origin.ApplicationSI     'Connect to the running Origin
    app.Load (app.Path(APPPATH_USER) + "COMServerExamples_WT.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Get data as Text.
    Data = Wks.getData(0, 0, -1, -1, ARRAY2D_TEXT)
    'Search for data
    For ii = 1 To UBound(Data)
        If Data(ii, 1) = "abc 11" Then Range("A1") = ii
        If Data(ii, 2) = "def 89" Then Range("A2") = ii
    Next
 
End Sub

C#

Using Origin;
static void GetNumericData()
{
    // Connect to the running Origin
    Origin.ApplicationSI app = new Origin.ApplicationSI();

    // Load the opj file saved by relevant SetData example
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_WN.opj", 0);
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    // Get data as Numeric.
    object Data = wks.GetData(0, 0, -1, -1, ARRAYDATAFORMAT.ARRAY2D_NUMERIC);

    // Cast object to double type array. Cast to other type if needed.
    double[,] result = Data as double[,];
    for (int ii = 1; ii <= result.GetUpperBound(0); ii++)
    {
        if (result[ii, 1] == (double)(63 * 0.01 - 1.23))
        {
            Console.WriteLine(ii + "\n");
        }
        if (result[ii, 2] == (double)(29 / 12.8 - 0.75))
        {
            Console.WriteLine(ii + "\n");
        }
        if (result[ii, 3] == (double)(41 + 0.123))
        {
            Console.WriteLine(ii + "\n");
        }
    }

    Console.ReadLine();
}

Using Origin;
static void GetTextData()
{
    //Connect to the running Origin
    Origin.ApplicationSI app = new Origin.ApplicationSI();

    //Load the opj file saved by relevant SetData example
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_WT.opj", 0);
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Get data as Numeric.
    object Data = wks.GetData(0, 0, -1, -1, ARRAYDATAFORMAT.ARRAY2D_TEXT);

    //Cast object to object type array. Cast to other type if needed.
    object[,] result = Data as object[,];
    for (int ii = 1; ii <= result.GetUpperBound(0); ii++)
    {
        if (string.Equals(result[ii, 1],"abc 11"))
        {
            Console.WriteLine(ii + "\n");
        }
        if (string.Equals(result[ii, 2], "def 89"))
        {
            Console.WriteLine(ii + "\n");
        }
    }

    Console.ReadLine();
}

Version Information

8.0SR2

See Also

SetData | Application.PutWorksheet | Application.GetWorksheet | Column.SetData | Column.GetData