Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data
file:///C|/inetpub/wwwroot/t002882/t002882.htm[5/27/2014 8:47:41 AM]
• Extended Properties: Extended properties can be applied to Excel workbooks which may change the overall activity of the
excel workbook from your program. The available properties are:
Property Description
HDR
It represents Header of the fields in the excel table. Default is YES.
If you don’t have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as
f1,f2 etc.
ReadOnly
You can also open excel workbook in read-only mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify
data within your workbook.
MaxScanRows
Excel does not provide the detailed schema definition of the tables. Excel needs to scan the rows before deciding the data types of the fields.
MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column.
By default, the value of this is 8. You can specify any value from 1 – 16 for 1 to 16 rows. You can also make the value to 0 so that it searches
all existing rows before deciding the data type. You can change the default behavior of this property by changing the value of
[HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.
Currently, MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hopefully, Microsoft fixes this issue to its
later versions
IMEX
As mentioned MaxScanRows, Excel has to guess a number of rows to select the most appropriate data type of the column, a serious problem
may occur of you have mixed data in one column. Say, you have data of both integer and text on a single column, in that case excel will choose
its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority
data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
For e.g., In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4)
numbers and four (4) null values, but you really want is text values.
To work around this problem for data, set “IMEX=1″ in the Extended Properties section of the connection string. This enforces the
ImportMixedTypes=Text registry setting. You can change the enforcement of type by changing
[HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well.
Read Cell
string queryLine = String.Format("select * from [{0}${1}:{2}]", sheetName, cellName, cellName);
cmdOLEDBAdpter = new System.Data.OleDb.OleDbDataAdapter(queryLine, conOleDB);
System.Data.DataTable cellData = new System.Data.DataTable();
cmdOLEDBAdpter.Fill(cellData);
foreach (System.Data.DataRow row in cellData.Rows)
{
string cellValue = row[0].ToString();
}
In the above code snippet, we compose a SQL statement that tries to query a single Excel cell content. For example,
Sheet1$A1:A1.
The remaining code in the snippet is the standard .NET approach to retrieve the data from the Excel Workbook.