2.5.11 GetData


Contents

Description

Get a range of values or a single value from a worksheet column.

Syntax

VB: Function GetData(format As ByVal ARRAYDATAFORMAT, [ nStart As ByVal Object ], [ nEnd As ByVal Object ], [ lowbound As ByVal Object ] ) As Object

C++: _variant_t GetData(ARRAYDATAFORMAT format, _variant_t nStart, _variant_t nEnd, _variant_t lowbound )

C#: var GetData(ARRAYDATAFORMAT format, var nStart, var nEnd, var lowbound )

Parameters

format
one of the ARRAYDATAFORMAT enumeration. For example ARRAY1D_NUMERIC
nStart
Starting index to get array, default is 0
nEnd
Inclusive ending index to get array, use -1(default) to get all
lowbound
Optional array offset index. This is typically 0, but for VB, user can DIM array with any integer offset, and often it is 1 in VB.

Return

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

Remark

Examples

Please refer to DataFormat for sending and getting different types of data to and from an Origin column.

VB

Get Integer Data from Origin The example will connect to a running Origin and load a specified project file, which was saved by the Column.SetData example. Once connected it will get the Integer data in ARRAY1D_NUMERIC format, search for a data value, and if found it will put the row index into the Excel worksheet.

Note: VB does not support the Origin data types CHAR nor USHORT so we can not get data from the first column nor the fourth column of the project file. 

Public Sub getData()
    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_CI.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Data in the second Column are LONG data. Get them as Numeric.
    Data = Wks.Columns(1).getData(ARRAY1D_NUMERIC, 0, -1)
    For ii = 1 To UBound(Data)
        If Data(ii) = 2700 Then Range("A1") = ii
    Next
 
    'Data in the third Column are BYTE data. Get them as Numeric.
    Data = Wks.Columns(2).getData(ARRAY1D_NUMERIC, 0, -1)
    For ii = 1 To UBound(Data)
        If Data(ii) = 55 Then Range("A2") = ii
    Next
 
End Sub

Get Floating-point Data from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Floating-point data in ARRAY1D_NUMERIC format from Origin, and then search for a float or double data in the data array we got. If found, we'll print the index of the data to Excel sheet. 

Public Sub getData()
    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_CF.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Data in the first Column are FLOAT data. Get them as Numeric.
    Data = Wks.Columns(0).getData(ARRAY1D_NUMERIC, 0, -1)
    For ii = 1 To UBound(Data)
        If Data(ii) = (17 * 0.01 - 1.23) Then Range("A1") = ii
    Next
 
    'Data in the second Column are DOUBLE data. Get them as Numeric.
    Data = Wks.Columns(1).getData(ARRAY1D_NUMERIC, 0, -1)
    For ii = 1 To UBound(Data)
        If Data(ii) = (53 / 12.8 - 0.75) Then Range("A2") = ii
    Next
 
End Sub

Get Complex Data(as 1D array) from Origin Run Origin first. We will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Complex data in ARRAY1D_NUMERIC format from Origin as 1D data array, and then print the data to Excel sheet. 

Public Sub getData()
    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_CC1.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Data in the first Column are COMPLEX data. Get them as 1D Numeric.
    'Result array is twice the size since complex is 16 byte each while array is double and 8 byte each
    Data = Wks.Columns(0).GetData(ARRAY1D_NUMERIC, 0, -1)
    'UBound(Data) / 2 is the actual number of complex values
    For ii = 1 To UBound(Data) / 2
        Range("A1").Cells(ii) = Data(2 * ii - 1)
        Range("B1").Cells(ii) = Data(2 * ii)
    Next
End Sub

Get Complex Data(as 2D array) from Origin Run Origin first. We will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Complex data in ARRAY1D_NUMERIC format from Origin as 2D data array, and then print the data to Excel sheet. 

Public Sub getData()
    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_CC2.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Data in the first Column are COMPLEX data. Get them as Numeric.
 
    Data = Wks.Columns(1).GetData(ARRAY2D_NUMERIC, 0, -1)
    For ii = 1 To UBound(Data)
        Range("A1").Cells(ii) = Data(ii, 1)
        Range("B1").Cells(ii) = Data(ii, 2)
    Next 
End Sub

Get Text from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Text in ARRAY1D_TEXT format from Origin, and then search for a string in the data array we got. If found, we'll print the index of the data to Excel sheet. 

Public Sub getData()
    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_CT.opj")     'Load the opj file saved by relevant SetData example
    Set Wks = app.FindWorksheet("[Book1]Sheet1")
 
    'Data in the first Column are TEXT. Get them as Text.
    Data = Wks.Columns(0).getData(ARRAY1D_TEXT, 0, -1)
    For ii = 1 To UBound(Data)
        If Data(ii) = "abc 11" Then Range("A1") = ii
    Next

End Sub

Get Data from Linked Cells When getting data from a linked cell in a column, there are additional complications. A linked cell holds a text which is a reference string to the actual data. To get this link string, you should use ARRAY1D_TEXT. To get the actual text value as you see them in Origin, use ARRAY1D_VARIANT and then convert it to a string. To get the actual numeric value, use ARRAY1D_NUMERIC.

The following example shows how to use a sample Analysis Template to get values from linked cells. This template (ogw) computes basic statistics on data in the first sheet, and update the results in the 2nd sheet as Descriptive Statistics tables. There is a third sheet which pick out a few of the values and display them in a much simpler form, using linked cells. 

Const NUMPTS = 100
    Dim app As Origin.ApplicationSI
    Dim wbk As Origin.WorksheetPage
    Dim Wks As Origin.Worksheet
    Dim Col As Origin.Column
    Dim ii As Integer
    Dim data(NUMPTS, 1) As Variant

Private Sub LoadOPJ_Click()
'Connect to a running Origin and load a sample Opj file
    Set app = New Origin.ApplicationSI
    app.Load (app.Path(APPPATH_PROGRAM) + "\Samples\COM Server and Client\Basic Stats on Data.opj")     'Load a sample Opj file from the folder under the Origin install folder
    app.Visible = MAINWND_SHOW     'Show the main window of Origin
        
End Sub

Private Sub SetAndGetData_Click()
'Set some data to Origin, and get back the processed data from Origin
    Set Wks = app.FindWorksheet("[RawData]Data")   'Select this Worksheet to send data
    'Set data array
    For ii = 0 To NUMPTS
        data(ii, 0) = ii
        data(ii, 1) = Rnd(100)
    Next ii
    'Send data to Origin
    Wks.SetData (data)

    app.Run     'Wait Origin to process the data
    Set Wks = app.FindWorksheet("[RawData]Basic Stats")     'In this sample Opj file, Origin will output the processed data to this Worksheet
    
    'Get data by different format
    Range("A1") = Wks.Columns(0).GetData(ARRAY1D_TEXT, 1, 1)     'Get the Link of the data, which is the real content in the Origin column
    Range("B1") = Wks.Columns(0).GetData(ARRAY1D_VARIANT, 1, 1)  'Get the content as is shown in the Origin column
    
    Range("C1") = Wks.Columns(1).GetData(ARRAY1D_TEXT, 1, 1)     'Get the Link of the data, which is the real content in the Origin column
    Range("D1") = Wks.Columns(1).GetData(ARRAY1D_NUMERIC, 1, 1)  'Get the content as is shown in the Origin column
    
    Range("E1") = Wks.Columns(2).GetData(ARRAY1D_TEXT, 1, 1)
    Range("F1") = Wks.Columns(2).GetData(ARRAY1D_NUMERIC, 1, 1)
    
End Sub

C#

Get Integer Data from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Integer data in ARRAY1D_NUMERIC format from Origin, and then search for a data in the data array we got. If found, we'll output the index of the data by console. 

Using Origin;
static void GetData()
{
    Origin.ApplicationSI app = new Origin.ApplicationSI();     //Connect to the running Origin
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_CI.opj", 0);     //Load the opj file saved by relevant SetData example
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Data in the first Column are Origin CHAR data. Get them as Numeric.
    object Data = wks.Columns[0].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    sbyte[] result1 = Data as sbyte[];                    //Cast object to sbyte type array
    for (int ii = 0; ii < result1.GetUpperBound(0); ii++)
    {
        if (result1[ii] == 17)
        {
            Console.WriteLine(ii + "\n");
        }
    }

    //Data in the second Column are Origin LONG data. Get them as Numeric.
    Data = wks.Columns[1].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    int[] result2 = Data as int[];                    //Cast object to int type array
    for (int ii = 0; ii < result2.GetUpperBound(0); ii++)
    {
        if (result2[ii] == 6700)
        {
            Console.WriteLine(ii + "\n");
        }
    }

    //Data in the third Column are Origin BYTE data. Get them as Numeric.
    Data = wks.Columns[2].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    byte[] result3 = Data as byte[];                    //Cast object to byte type array
    for (int ii = 0; ii < result3.GetUpperBound(0); ii++)
    {
        if (result3[ii] == 39)
        {
            Console.WriteLine(ii + "\n");
        }
    }

    //Data in the forth Column are Origin USHORT data. Get them as Numeric.
    Data = wks.Columns[3].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    ushort[] result4 = Data as ushort[];                    //Cast object to ushort type array
    for (int ii = 0; ii < result4.GetUpperBound(0); ii++)
    {
        if (result4[ii] == 210)
        {
            Console.WriteLine(ii + "\n");
        }
    }

    Console.ReadLine();
}

Get Floating-point Data from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Floating-point data in ARRAY1D_NUMERIC format from Origin, and then search for a data in the data array we got. If found, we'll output the index of the data by console. 

Using Origin;
static void GetData()
{
    Origin.ApplicationSI app = new Origin.ApplicationSI();     //Connect to the running Origin
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_CF.opj", 0);     //Load the opj file saved by relevant SetData example
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Data in the first Column are Origin FLOAT data. Get them as Numeric.
    object Data = wks.Columns[0].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    float[] result1 = Data as float[];                    //Cast object to float type array
    for (int ii = 0; ii < result1.GetUpperBound(0); ii++)
    {
        if (result1[ii] == (float)(17 * 0.01 - 1.23))
        {
            Console.WriteLine(ii + "\n");
        }
    }

    //Data in the second Column are Origin DOUBLE data. Get them as Numeric.
    Data = wks.Columns[1].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    double[] result2 = Data as double[];                    //Cast object to double type array
    for (int ii = 0; ii < result2.GetUpperBound(0); ii++)
    {
        if (result2[ii] == (double)(53 / 12.8 - 0.75))
        {
            Console.WriteLine(ii + "\n");
        }
    }

    Console.ReadLine();
}

Get Complex Data(as 1D array) from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Complex data in ARRAY1D_NUMERIC format from Origin as 1D data array, and then output the data by console. 

Using Origin;
static void GetData()
{
    Origin.ApplicationSI app = new Origin.ApplicationSI();     //Connect to the running Origin
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_CC1.opj", 0);     //Load the opj file saved by relevant SetData example
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Data in the first Column are COMPLEX data. Get them as 1D Numeric.
    //Result array is twice the size since complex is 16 byte each while array is double and 8 byte each
    object Data = wks.Columns[0].GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, -1, 0);
    double[] result = Data as double[];                    //Cast object to 1D double array
    //result.GetUpperBound(0) / 2 is the actual number of complex values
    for (int ii = 0; ii < (result.GetUpperBound(0) / 2); ii++)
    {
        Console.WriteLine(ii + ": " + result[2 * ii] + ", " + result[2 * ii + 1]);
    }

    Console.ReadLine();
}

Get Complex Data(as 2D array) from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Complex data in ARRAY1D_NUMERIC format from Origin as 2D data array, and then output the data by console. 

Using Origin;
static void GetData()
{
    Origin.ApplicationSI app = new Origin.ApplicationSI();     //Connect to the running Origin
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_CC2.opj", 0);     //Load the opj file saved by relevant SetData example
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Data in the first Column are Origin COMPLEX data. Get them as 2D Numeric.
    object Data = wks.Columns[0].GetData(ARRAYDATAFORMAT.ARRAY2D_NUMERIC, 0, -1, 0);
    double[,] result = Data as double[,];                    //Cast object to 2D double array
    for (int ii = 0; ii < result.GetUpperBound(0); ii++)
    {
        Console.WriteLine(ii + ": " + result[ii, 0] + ", " + result[ii, 1]);
    }

    Console.ReadLine();
}

Get Text from Origin Run Origin first. The example will connect to Origin and load a relevant opj file, which was saved by Column.SetData Examples. We'll get these Text in ARRAY1D_TEXT format from Origin, and then search for a data in the data array we got. If found, we'll output the index of the data by console. 

Using Origin;
static void GetData()
{
    Origin.ApplicationSI app = new Origin.ApplicationSI();     //Connect to the running Origin
    app.Load(app.path(Origin.APPPATH_TYPES.APPPATH_USER) + "COMServerExamples_CT.opj", 0);     //Load the opj file saved by relevant SetData example
    Worksheet wks = app.FindWorksheet("[Book1]Sheet1");

    //Data in the first Column are Origin TEXT. Get them as Text.
    object Data = wks.Columns[0].GetData(ARRAYDATAFORMAT.ARRAY1D_TEXT, 0, -1, 0);
    object[] result = Data as object[];                    //Cast object to object type array
    for (int ii = 0; ii < result.GetUpperBound(0); ii++)
    {
        if (string.Equals(result[ii],"abc 11"))
        {
            Console.WriteLine(ii + "\n");
        }
    }

    Console.ReadLine();
}

Get Data from Linked Cells 

using Origin;

static void Main(string[] args)
{
    GetData();
    Console.ReadLine();
}

static void GetData()
{
    object Default = System.Type.Missing;
    double[,] send = new double[100,2];
    for (int ii = 0; ii < 100; ii++)
    {
        send[ii, 0] = ii;
        send[ii, 1] = ii * 0.3;
    }

    //Connect to a running Origin
    Origin.ApplicationSI app = new Origin.ApplicationSI();
    //Load a sample Opj file from the folder under the Origin install folder
    app.Load((app.path(APPPATH_TYPES.APPPATH_PROGRAM) + "\\Samples\\COM Server and Client\\Basic Stats on Data.opj"), Default);     
    app.Visible = Origin.MAINWND_VISIBLE.MAINWND_SHOW;     //Show the main window of Origin

    Worksheet wks = app.FindWorksheet("[RawData]Data");    //Select this Worksheet to send data
    //Send data to Origin
    wks.SetData(send, 0, 0);
    app.Run();     //Wait Origin to process the data

    wks = app.FindWorksheet("[RawData]Basic Stats");     //In this sample Opj file, Origin will output the processed data to this Worksheet

    //Get data by different format
    Column col = wks.Columns[0];
    object get= col.GetData(ARRAYDATAFORMAT.ARRAY1D_TEXT, 0, 4, 0);     //Get the Link of the data, which is the real content in the Origin column
    object[] data = get as object[];
    Console.WriteLine(data[1] + "\n");
    get = col.GetData(ARRAYDATAFORMAT.ARRAY1D_VARIANT, 0, 4, 0);        //Get the content as is shown in the Origin column
    data = get as object[];
    Console.WriteLine(data[1] + "\n\n");

    col = wks.Columns[1];
    get = col.GetData(ARRAYDATAFORMAT.ARRAY1D_TEXT, 0, 4, 0);           //Get the Link of the data, which is the real content in the Origin column
    data = get as object[];
    Console.WriteLine(data[1] + "\n");
    get = col.GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, 4, 0);        //Get the content as is shown in the Origin column
    double[] dataN = get as double[];
    Console.WriteLine(dataN[1] + "\n\n");

    col = wks.Columns[2];
    get = col.GetData(ARRAYDATAFORMAT.ARRAY1D_TEXT, 0, 4, 0);
    data = get as object[];
    Console.WriteLine(data[1] + "\n");
    get = col.GetData(ARRAYDATAFORMAT.ARRAY1D_NUMERIC, 0, 4, 0);
    dataN = get as double[];
    Console.WriteLine(dataN[1] + "\n\n");

    Console.ReadLine();

}

Version Information

8.0SR2

See Also

SetData | Application.PutWorksheet | Application.GetWorksheet | Worksheet.SetData | Worksheet.GetData