收藏 分享(赏)

intouch脚本资料.pdf

上传人:精品资料 文档编号:10748383 上传时间:2020-01-07 格式:PDF 页数:22 大小:382.81KB
下载 相关 举报
intouch脚本资料.pdf_第1页
第1页 / 共22页
intouch脚本资料.pdf_第2页
第2页 / 共22页
intouch脚本资料.pdf_第3页
第3页 / 共22页
intouch脚本资料.pdf_第4页
第4页 / 共22页
intouch脚本资料.pdf_第5页
第5页 / 共22页
点击查看更多>>
资源描述

1、Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM Tech Note 1043 Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data All Tech Notes, Tech Alerts and KBCD doc

2、uments and software are provided “as is“ without warranty of any kind. See the Terms of Use for more information. Topic#: #002882 Created: May 2014 Introduction Generally people use a database such as SQL Server, Oracle or Access to store data. However, Excel can also act as a database. Using Excels

3、 rich file types and functionality, you can enhance your Application Server application. This Tech Note introduces the approach of reading and writing Excel data from the IDE via a .NET control. It contains the following sections: Read and write the Excel data using an Application Server Script Expl

4、ain the SQL techniques for Excel data processing in the .NET Control Application Versions Wonderware Application Server 2012 R2 and later InTouch 2012 R2 and later Read and Write the Excel Data Using an Application Server Script This section explains reading and writing Excel data from Application S

5、erver using a custom script function library. 1. Download the ExcelOperations.zip file to your GR node and extract the contents to a local directory. In this example, we use C:TEMPExcel. 2. Create a new Galaxycalled GalaxyExcel and open it in the IDE. 3. Import the ExcelOperations.dll file and confi

6、rm the import succeeds (Figures 1,2 and 3 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 1: SELECT GALAXYIMPORTSCRIPT FUNCTION LIBRARY OPTIONWonderware Application Server Scripti

7、ng Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 2: BROWSE AND SELECT THE DLL FIGURE 3: VERIFY IMPORT SUCCEEDED 4. Import ExcelUDA.aaPKG and $READnWRITE2Excel.aaPKG into the Galaxy (Figures 4, 5, 6 and 7 below).Wonderware Ap

8、plication Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 4: SELECT GALAXY IMPORT OBJECT(S) OPTIONWonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwr

9、oot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 5: SELECT BOTH OBJECTS AND OPENWonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 6: ACCEPT ALL DEFAULT IMPORT PREFERENCESWonderware Appli

10、cation Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 7: CLOSE THE WINDOW AFTER SUCCESSFUL IMPORT 5. Go to the Deployment View, then locate the ExcelUDA object and open it. 6. Click the UDAs tab. The UDAs lis

11、ted will be used to Read and write data to Excel from InTouch (Figure 8 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 8: LIST OF UDAS USED IN READ AND WRITE OPERATION AllCellArr

12、ay: String Data Type: Holds the concatenated values from multiple Excel cells. ExcelCellReadWrite: String Data Type: This contains the Excel cell information from where the data will be read or written to. This example uses A1. ExcelCellValue: String Data Type: Holds the Excel cell value. ExcelPath:

13、 String Data Type: Holds the path of the Excel file. This example uses C:TEMPExcelReadWrite.xlsx. Make sure you have this path with ReadWrite.xlsx file in it on your local machine. ExcelSheetName: String Data Type: Holds the Excel sheet name. This example uses Sheet1. ReadAllCmd: Boolean Data Type:

14、Used to trigger the data read from multiple Excel cells. ReadCmd: Boolean Data Type: Used to trigger the data read from Excel cell. WriteCmd: Boolean Data Type: Used to trigger the data write to Excel cell. Write Value: String Data Type: Holds the value to be written to the Excel cell. 7. Click the

15、Scripts tab.Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM Use the Script ReadCell to read data from an Excel cell (Figure 9 below). FIGURE 9: SCRIPT TO READ DATA FROM EXCEL CELL Use the Scrip

16、t WriteCell to write Data to an Excel cell (Figure 10 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE10: SCRIPT TO WRITE DATA INTO EXCEL CELL Use the Script ReadAllCells to read f

17、rom multiple cells in the Excel (Figure 11 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 11: SCRIPT TO READ ALL DATA FROM EXCEL CELL 8. Close the ExcelUDA object 9. Create a new

18、 instance of the Platform, App Engine, Area, ViewEngine and a READnWRITE2Excel InTouchView object. 10. Assign the objects correctly under the Platform then cascade deploy the Platform (Figure 12 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|

19、/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 12: CREATE OBJECT INSTANCES AND DEPLOY PLATFORM 11. Open the ReadWrite.xlsx file and type the string Hello in cell A1 (Figure 13 below). Then save and close the file. FIGURE13: TYPE A VALUE IN EXCEL CELL A1 12. Open InTouch Application

20、Manager. Find the deployed $READnWRITE2Excel object and open it in WindowViewer (Figure 14 and 15).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 14: SELECT THE DEPLOYED INTOUCHVIEW APP

21、LICATIONWonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 15: OPEN THE DEPLOYED INTOUCHVIEW APPLICATION IN RUNTIME 13. To read the value from Excel, enter the cell # as A1 (this appears by

22、 default on WindowViewer) then click the Read the value from excel button. The string Hello is displayed in the window (Figure 16 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 1

23、6: TYPE A CELL# AND READ THE VALUE FROM EXCEL To Write a Value to Excel 1. Type the value to be written as Wonderware, type the cell # as A1 (this appears by default in WindowViewer) then click Commit the value to excel button (Figure 17 below). Note: Before writing to Excel, make sure the ReadWrite

24、.xlsx file is closed.Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 17: TYPE CELL # AND VALUE TO WRITE VALUE INTO EXCEL The string Wonderware will be written into the Excel sheet1 cell

25、A1 (Figure 18 below).Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AMFIGURE18: VERIFY THE VALUE WRITTEN INTO EXCEL To read values from multiple cells in Excel 1. Open the ReadWrite.xlsx file and

26、 type your values (Figure 19 below). Save and Close the ReadWrite.xlsx file.Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM FIGURE 19: MULTIPLE VALUES IN EXCEL 2. In WindowViewer, click the Rea

27、d the values from excel button. The values are displayed as a concatenated string (Figure 20 below). FIGURE 20: READ MULTIPLE VALUES FROM EXCEL IN INTOUCH WINDOW VIEWERWonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.h

28、tm5/27/2014 8:47:41 AM Explain the SQL Techniques for Excel Data Processing in the .NET Control Excel has many unique features, summarized in the following list. Accessing wide range data types and Updating large amounts of data quickly and easily. Charting and graphing data sets. Perform drill-down

29、 analysis on large data sets. Storing, analyzing, collecting and sharing data amount the Microsoft Office World and much more In this Tech Note, our focus is to introduce a .NET component approach that allows Wonderware Application Server (WAS) to read and write data into Excel. Similar to working w

30、ith SQL Server or Microsoft Access, you need to follow the same concept of Database connection, dataset and SQL query for the Excel database operations. Database connection In our example in this Tech Note, we have the following connection string in C# format: connectionString = string.Format(“Provi

31、der=Microsoft.ACE.OLEDB.12.0; Data source=0; Extended Properties=“Excel 12.0;HDR=NO“;“, excelFilePath); conOleDB = new System.Data.OleDb.OleDbConnection(connectionString); Provider: Microsoft.ACE.OLEDB.12.0 It is the main OLEDB provider used to open the Excel sheet. So far it has been tested with Ex

32、cel 2010 version. The Provider is a Microsoft Shared DLL is located at: -64-bit OS: C:Program Files (x86)Common Filesmicrosoft sharedOFFICE14ACEOLEDB.DLL -32-bit OS: C:Program FilesCommon Filesmicrosoft sharedOFFICE14ACEOLEDB.DLL Data source: The full path of the Excel workbook which can have follow

33、ing extension types. Excel Workbook Type Description XLS 97-2003 Excel Workbook XLSX 2007 or later Excel Workbook XLSB 2007 or later Office Open XML format saved in a binary format XLSM 2007 or later Office Open XML format with macros enabled Note: Refer to the System Platform Readme file (on your S

34、ystem Platform installation disc) under Third-Party Application Prerequisites for list of supported Excel versions.Wonderware Application Server Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM Extended Properties: Extended

35、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 dont have fieldnames in the header of your

36、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

37、 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 fro

38、m 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 HKLMSoftwareMicrosoftJet4.0EnginesExcelTypeGuessRows which is 8 by default. Currently, MaxScanR

39、ows 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 m

40、ixed 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

41、 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

42、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 HKLMSoftwareMicrosoftJet4.0EnginesExcelImportMixedTypes to numeric as well. Read Cell string qu

43、eryLine = 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)

44、string cellValue = row0.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.Wonderware Application Se

45、rver Scripting Implementations Part 2: Read and Write Excel Data file:/C|/inetpub/wwwroot/t002882/t002882.htm5/27/2014 8:47:41 AM The standard .NET approach is to use the OleDbDataAdapter, DataTable and DataRow .NET component. You can click on each component for detailed information. Write Cell stri

46、ng queryLine = String.Format(“UPDATE 0$1:2 Set F1=“3“, sheetName, cellName, cellName, value2Write); oleDBCmd = new System.Data.OleDb.OleDbCommand(queryLine, conOleDB); oleDBCmd.ExecuteNonQuery(); In this code snippet, the UPDATE is a standard SQL statement to change the value of a specific Excel cel

47、l. For example, we can change the value of Sheet1$A1:A1 with the content of value2Write. Note: F1 is the default name for .NET ADO to assign to the first column. For multiple columns, you can use the following format example string queryLine = String.Format(“UPDATE 0$1:2 Set F1=“3“ Set F2=“4“, sheet

48、Name, cellName1, cellName2, value2Write1, value2Write2); Read all cells from an Excel sheet string queryLine = String.Format(“select * from 0$“, sheetName); StringBuilder allContents = new StringBuilder(); cmdOLEDBAdpter = new System.Data.OleDb.OleDbDataAdapter(queryLine, conOleDB);System.Data.DataT

49、able sheetData = new System.Data.DataTable();cmdOLEDBAdpter.Fill(sheetData);System.Data.DataSet excelDataSet = new System.Data.DataSet();excelDataSet.Tables.Add(sheetData);foreach (System.Data.DataTable thisTable in excelDataSet.Tables)foreach (System.Data.DataRow myRow in thisTable.Rows)foreach (System.Data.DataColumn myCol in thisTable.Columns)string value = myRowmyCol.ToString();allContents.Append

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 企业管理 > 管理学资料

本站链接:文库   一言   我酷   合作


客服QQ:2549714901微博号:道客多多官方知乎号:道客多多

经营许可证编号: 粤ICP备2021046453号世界地图

道客多多©版权所有2020-2025营业执照举报