3.7.5.103 Wbk.DC
The wbk.DC object has properties and methods related to the Data Connector attached to the workbook. The workbook object can be referred to using either "wbook" or "wbk".
Contents
Properties
| Property | Access | Description |
|---|---|---|
| wbk.DC.mfiles |
Read Only |
Check if the workbook allows connections to multiple data sources.
Returns 1 for yes, and 0 for no |
| wbk.DC.nav |
Read/Write numeric |
Create or remove data navigator: 0 = remove, 1 = create.
Note, to show/hide data navigator, please use page.cp.show. |
| wbk.DC.nConns |
Read Only |
Returns the number of connected sheets |
| wbk.DC.OCFiles$ |
Read Only |
Returns the relative path of the connector cpp file to the Apps root folder. To construct the full OC file path, use
"%@A"+wbk.dc.OCFile$. |
| wbk.DC.optn$ |
Read/Write |
Set partial import and sccript. |
| wbk.DC.source$ |
Read/Write string |
Get and set the data source. For example,
newbook; wbook.dc.add("HTML"); wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993"; wks.dc.sel$="Tables/_2"; wks.dc.import(); See also wks.DC.source$. |
| wbk.DC.type$ |
Read Only |
Returns the connector type, CSV_Connector or JSON_Connector. This string can be used in wbk.DC.Add. |
| wbk.DC.URL$ |
Read Only |
Returns the data source URL if connecting data from web. |
Methods
| Method | Description |
|---|---|
| wbk.DC.add(file_type[,1]) | Add Data Connector of file_type... ... where file_type is the name of the Connector (generally, just the name of the Connector, enclosed by double-quotes if there is a space in the name -- e.g. Note that in the case of some complex file structures (NetCDF, HDF), import times may be significantly reduced by not showing the Data Navigator pane. To prevent the Navigator pane from being added on import, add "1" as an argument: wbk.dc.add("HDF",1); |
| wbk.DC.Compatible(file) | check if given file is compatible with the current DC if book has one:
0 = not compatible int bValid = wbook.dc.Compatible(myfile$); |
| wbk.DC.import(nn) | Import data:
nn = 0 or not specify: import all sheets in the workbook without opening the select... menu,
|
| wbk.DC.newsheet(sheet$[,1]) | Connect to another sheet or variable Some data connectors connect to a file with multi-sheets or multi-variables within. E.g. multi-sheet Excel file, multi-varible Matlab file, etc. Origin only imports one sheet/variable. Use this method to connect to another sheet or variable. The second argument 1 is new in Origin 2024b. If it's included, new sheet will be connected by duplicating active sheet's structure and analysis, etc. E.g. Connect to Oil sheet of an Excel file with other sheets, e.g. Coal, etc. Some calculation are done on Oil sheet. With Oil sheet active: wbk.DC.newsheet("Coal") //connect to Coal sheet as a clean new sheet
wbk.DC.newsheet("Coal", 1) //connect to Coal sheet with same calculation on Oil sheet
Note: For Excel Connector, only sheet name is need as sheet$. For other other connectors, e.g. Origin Project connector, Book+sheet range string should be used as sheet$: wbk.DC.newsheet("[Book1]Sensor02", 1)
|
| wbk.DC.remove(nn) | Remove data connector.
nn = 0: Default value when nn is not specified. Remove data connector object from the current workbook, and remove edit protection from all destination sheets in this workbook. |
Examples
EX1
Below codes import a HTML table on a wiki webpage to Origin worksheet.
newbook; wbook.dc.add("HTML"); urllink$="https://en.wikipedia.org/wiki/List_of_metropolitan_statistical_areas"; wks.dc.source$={"isAdv":0,"bas":{"url":"%(urllink$)"},"adv":{"urlParts":["",""],"cmdTimeout":"","hdrParams":[]}}; wks.DC.Sel$=Tables/_1; wks.dc.import();
EX2
The following example shows how to import an Excel file by Data Connector.
newbook; wbook.dc.add("Excel"); wks.dc.source$=System.path.program$+"Samples\Import and Export\United States Energy (1980-2013).xls"; wks.dc.sel$="Natural Gas"; wks.dc.import(); //set column label rows worksheet -s 0 1 0 1; wks.setaslabel(C, -1, 0, 0); wks.setaslabel(L, -1, 0, 0); wks.setaslabel(U, -1, 0, 0);
EX3
This example uses the Origin Connector to import data from another Origin project by running a query (information on the SQL-like query language supported by the Origin Connector can be found here).
// Origin project file location: string path$ = "C:\temp\mydata.opju"; // Create new workbook in current project newbook; // Connect to the desired Origin project using the Origin Data Connector wbook.dc.ADD("Origin"); wks.dc.source$ = path$; // Specify query string to pull all X columns that have long name of "alpha" wks.dc.sel$=Select Column from Project where (Column_LName like "alpha" AND Column_Type = X); wks.dc.import();
EX4
This example uses the NetCDF Connector to do partial import of web-stored multi-dimensional data in a .nc file.
newbook mat:=1; wbk.dc.add("NetCDF"); wks.dc.source$="https://psl.noaa.gov/thredds/fileServer/Datasets/cpc_us_precip/precip.V1.0.mon.mean.nc"; // import z=1 to end, read 1 skip 11; lon shift, lat flip, formula v*0.0393701 wks.dc.sel$=NetCDF/precip[1:0|1-11][y#][x/2]v*0.0393701; wks.dc.import();
EX5
The following example shows how to specify an import filter in the same folder as your data file.
wbook.dc.add("Import Filter"); wks.dc.source$="C:\2016\Samples\Import and Export\S15-125-03.dat"; // connect to .dat file wks.dc.sel$="test1.oif"; //use import filter "test1.oif" in the data folder - see Note wks.dc.import();
Note: You can refer to an import filter saved to UFF\Filters using the %Y string register as in, for example, wks.dc.sel$="%YFilters\test1.oif";
EX6
The following 2 examples show you how to import files with multiple sheets
// import Excel file with two sheets with same headings string fname$=system.path.program$ + "Samples\Import and Export\United States Energy (1980-2013).xls"; wbook.dc.add("Excel"); wks.dc.source$ = fname$; Tree tr1 = wks.dc.Optn$; tr1.Settings.mainheader = 2; tr1.Settings.labels.SetAttribute("Use",1); tr1.Settings.labels.longname=1; tr1.Settings.labels.unit=2; tr1.tostring(wks.dc.optn$); wks.dc.sel$ = "Oil"; wks.dc.import(); //now connect to another sheet named "Coal" wbook.dc.Newsheet("Coal"); //import one sheet in an OPJU file, normalize 4th column //Then import a new sheet by duplicating the current sheet so normalization is done as well string fname$=system.path.program$ + "Samples\tutorial data.opju"; wbook.dc.add("Origin"); wks.dc.source$ = fname$; wks.dc.sel$ = "[book6]pre-Electrical Open"; wks.dc.import(); rnormalize -r 1 irng:=4 method:=range100; //normalize 4th column //Import "post-Electrical Open" sheet in OPJU by duplicating same operation on current "pre-Electrical Open" sheet //2nd argument 1 is new in Origin 2024b wbook.dc.Newsheet("[book6]post-Electrical Open",1);
EX7
The following 3 examples show you how to import tables from web.
//import ''table _2'' and then append ''table _9'' as new columns in the same worksheet newbook; wbook.dc.add("HTML"); wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993"; wks.dc.sel$="Tables/_2"; wks.dc.import(); wks.DC.flags=0; //0: append new columns, 256: append new rows. wks.dc.add("Tables/_9"); //import multiple tables in same sheet, for each table only import 2:3 columns. //table name only from 2nd char on and show as calendar 3 char month format newbook; wbook.dc.add("HTML"); wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993"; wks.dc.sel$="Tables/_2|Tables/_3|Tables/_4"; Tree tr = wks.dc.optn$; tr.partial_col$="2 3"; tr.nameoptn$="2:0";//Keep table name from 2nd char on. Use "2" to show 2nd char only ToString(wks.dc.optn$); wks.dc.import(); wlabel ty:="table" fmt:="CM0"; //show calendar month Feb, Mar, Apr in Table row //import multiple tables into same sheet by appending row after row newbook; wbook.dc.add("HTML"); wbook.dc.source$="https://www.extremeweatherwatch.com/cities/boston/year-1993"; wks.dc.sel$="Tables/_2|Tables/_3|Tables/_4"; wks.dc.flags=256; Tree tr = wks.dc.optn$; tr.partial_col$="2 3"; tr.ToString(wks.dc.optn$); wks.dc.import();