Parameter Class
Parameter Properties.When the object is serialized out as xml, its qualified name is x:parameter.
Inheritance Hierarchy
System.Object
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlLeafElement
DocumentFormat.OpenXml.Spreadsheet.Parameter
Namespace: DocumentFormat.OpenXml.Spreadsheet
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
Syntax
'Declaration
Public Class Parameter _
Inherits OpenXmlLeafElement
'Usage
Dim instance As Parameter
public class Parameter : OpenXmlLeafElement
Remarks
[ISO/IEC 29500-1 1st Edition]
18.13.6 parameter (Parameter Properties)
This element stores properties about any parameters used with external data connections. Parameters are used to change the query executed externally and cause different data to be retrieved into the workbook. The type of parameter used – see ST_parameterType (§18.18.54) – determines whether the user is prompted for a value before data is refreshed, or the value is pulled from a cell in the workbook, or whether the same value should be used until explicitly changed in the data connection. Parameters are permitted for ODBC and web queries.
[Example:
Data connectivity can use a number of different technologies. The following is an example of XML defining a connection to a Microsoft Access database, with a parameter based on the value in cell C1 on the first sheet.
<connection id="1" name="Connection" type="1" refreshedVersion="2" background="1" saveData="1"> <dbPr connection="DSN=MS Access Database;DBQ=C:\Desktop\db1.mdb;DefaultDir=C:\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" command="SELECT Table1.Field1, Table1.Field2_x000d__x000a_FROM `C:\Desktop\db1`.Table1 Table1_x000d__x000a_WHERE (Table1.Field2=?)"/> <parameters count="1"> <parameter name="user specified value" sqlType="4" parameterType="cell" cell="Sheet1!$C$1"/> </parameters> </connection>
end example]
Note that the command string in the dbPr element contains a "?" character. This character serves as a parameter marker.
[Note: Data connectivity can use a number of different technologies. One example of potential values stored in this attribute can be found at: https://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcstatement\_parameters.asp end note]
Parent Elements |
|---|
parameters (§18.13.7) |
Attributes |
Description |
|---|---|
boolean (Boolean) |
Boolean value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
cell (Cell Reference) |
Cell reference indicating which cell's value to use for the query parameter. Used only when parameterType = cell. [Example: <Parameter parameterType="cell" cell="Sheet1!$C$1"> end example] The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19). |
double (Double) |
Non-integer numeric value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the W3C XML Schema double datatype. |
integer (Integer) |
Integer value to use as the query parameter. Used when parameterType = value. The possible values for this attribute are defined by the W3C XML Schema int datatype. |
name (Parameter Name) |
The name of the parameter. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19). |
parameterType (Parameter Type) |
Type of parameter used. If the parameterType=value, then the value from boolean, double, integer, or string are used. In this case, it is expected that only one of {boolean, double, integer, or string} is specified. The possible values for this attribute are defined by the ST_ParameterType simple type (§18.18.54). |
prompt (Parameter Prompt String) |
Prompt string for the parameter. Presented to the spreadsheet user along with input UI to collect the parameter value before refreshing the external data. Used only when parameterType = prompt. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19). |
refreshOnChange (Refresh on Change) |
Flag indicating whether the query should automatically refresh when the contents of a cell that provides the parameter value changes. If true, then external data is refreshed using the new parameter value every time there's a change. If false, then external data is only refreshed when requested by the user, or some other event triggers refresh (e.g., workbook opened). The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
sqlType (SQL Data Type) |
SQL data type of the parameter. Only supported for ODBC sources. Supported values include: -22 SQL_UNSIGNED_OFFSET -20 SQL_SIGNED_OFFSET -11 SQL_GUID -10 SQL_WLONGVARCHAR -9 SQL_WVARCHAR -8 SQL_WCHAR -7 SQL_BIT -6 SQL_TINYINT -5 SQL_BIGINT -4 SQL_LONGVARBINARY -3 SQL_VARBINARY -2 SQL_BINARY -1 SQL_LONGVARCHAR 0 SQL_UNKNOWN_TYPE 1 SQL_CHAR 2 SQL_NUMERIC 3 SQL_DECIMAL 4 SQL_INTEGER 5 SQL_SMALLINT 6 SQL_FLOAT 7 SQL_REAL 8 SQL_DOUBLE 9 SQL_TYPE_DATE or SQL_DATE 10 SQL_TYPE_TIME or SQL_TIME 11 SQL_TYPE_TIMESTAMP or SQL_TIMESTAMP 12 SQL_VARCHAR 101 SQL_INTERVAL_YEAR 102 SQL_INTERVAL_MONTH 103 SQL_INTERVAL_DAY 104 SQL_INTERVAL_HOUR 105 SQL_INTERVAL_MINUTE 106 SQL_INTERVAL_SECOND 107 SQL_INTERVAL_YEAR_TO_MONTH 108 SQL_INTERVAL_DAY_TO_HOUR 109 SQL_INTERVAL_DAY_TO_MINUTE 110 SQL_INTERVAL_DAY_TO_SECOND 111 SQL_INTERVAL_HOUR_TO_MINUTE 112 SQL_INTERVAL_HOUR_TO_SECOND 113 SQL_INTERVAL_MINUTE_TO_SECOND The possible values for this attribute are defined by the W3C XML Schema int datatype. |
string (String) |
String value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19). |
[Note: The W3C XML Schema definition of this element’s content model (CT_Parameter) is located in §A.2. end note]
© ISO/IEC29500: 2008.
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.