Question

wincc6.0中如何将excel导入到变量中

Answer 1

这是我写的WinCC读写Excel的C脚本和VB脚本 应该有用

读：
#include "apdefap.h"
void OnLButtonDown(char* lpszPictureName, char* lpszObjectName, char* lpszPropertyName, UINT nFlags, int x, int y)
{
#pragma code("Shell32.dll")//调用动态链接库
char tag1[10],tag2[10];
__object* pExcel = NULL; //建立 pExcel 指针 用来对 EXCEL 进行操作
HWND handle=NULL;
handle=FindWindow(NULL,"WinCC-运行系统 - ");
if (MessageBox(handle,"Start","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel = __object_create("Excel.Application");
if (MessageBox(handle,"After Creat","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->Visible = 0;//控制excel文件是否可见,当1时,excel文件可见.
pExcel->Workbooks ->Open ("d:\\SetPointToPLC.xls");
if (MessageBox(handle,"After Open","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
if (MessageBox(handle,pExcel->Worksheets("sheet1")->Range("B1")->Value,"Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
strcpy(tag1,pExcel->Worksheets("sheet1")->Range("B1")->Value);
strcpy(tag2,pExcel->Worksheets("sheet1")->Range("B2")->Value);
SetTagChar("NewTag1",pExcel->Worksheets("sheet1")->Range("B1")->Value);//将excel文件B2里面的内容写到外部变量NewTag1
SetTagChar("NewTag2",pExcel->Worksheets("sheet1")->Range("B2")->Value);//将excel文件B3里面的内容写到外部变量NewTag2
if (MessageBox(handle,"After Read","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->Workbooks->Close();//关闭文件
pExcel->Quit();//退出Excel
__object_delete(pExcel);

}

写：
#include "apdefap.h"
void OnLButtonDown(char* lpszPictureName, char* lpszObjectName, char* lpszPropertyName, UINT nFlags, int x, int y)
{
#pragma code("Shell32.dll")//调用动态链接库
char tag1[10],tag2[10];
__object* pExcel = NULL; //建立 pExcel 指针 用来对 EXCEL 进行操作
HWND handle=NULL;
handle=FindWindow(NULL,"WinCC-运行系统 - ");
if (MessageBox(handle,"Start","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel = __object_create("Excel.Application");
if (MessageBox(handle,"After Creat","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->Visible = 0;//控制excel文件是否可见,当1时,excel文件可见.
pExcel->Workbooks ->Open ("d:\\SetPointToPLC.xls");
if (MessageBox(handle,"After Open","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
if (MessageBox(handle,pExcel->Worksheets("sheet1")->Range("B1")->Value,"Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->Worksheets("sheet1")->Range("B3")->Value = GetTagChar("NewTag3"); //Return-Type: char*
pExcel->Worksheets("sheet1")->Range("B4")->Value = GetTagChar("NewTag4"); //Return-Type: char*
if (MessageBox(handle,"After Write","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->ActiveWorkbook->Save();
if (MessageBox(handle,"After Save","Test", MB_YESNO|MB_ICONQUESTION|MB_SETFOREGROUND|MB_SYSTEMMODAL)==IDYES) { }
pExcel->DisplayAlerts = 0;
pExcel->Workbooks->Close();//关闭文件
pExcel->Quit();//退出Excel
__object_delete(pExcel);
}

Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
Dim iBlankLine
iBlankLine = oWorkBook.ActiveSheet.Columns(1).Find("").Row '查找第一列的空行
'MsgBox iBlankLine
objExcelApp.Cells(iBlankLine, 1).Value = ScreenItems("tag1").OutputValue
objExcelApp.Cells(iBlankLine, 2).Value = ScreenItems("tag2").OutputValue
objExcelApp.Cells(iBlankLine, 3).Value = ScreenItems("tag3").OutputValue
oWorkBook.Save
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
Set oWorkBook =objExcelApp.Workbooks.Add
oWorkBook.SaveAs "D:\ExcelExample.xls"

写：
Sub OnOpen()

Dim objComboBox

Set objComboBox = ScreenItems("控件1")
objComboBox.Clear()

Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")

objComboBox.AddItem objExcelApp.Cells(1, 1).Value
objComboBox.AddItem objExcelApp.Cells(1, 2).Value
objComboBox.AddItem objExcelApp.Cells(1, 3).Value

objComboBox.SelText = "选项1"

objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
End Sub

读：
Sub OnLButtonDown(Byval Item, Byval Flags, Byval x, Byval y)

Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
Dim iBlankLine
iBlankLine = oWorkBook.ActiveSheet.Columns(1).Find("").Row '查找第一列的空行
'MsgBox iBlankLine
objExcelApp.Cells(iBlankLine, 1).Value = "tag1"
objExcelApp.Cells(iBlankLine, 2).Value = "tag2"
objExcelApp.Cells(iBlankLine, 3).Value = "tag3"
oWorkBook.Save
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
'Set oWorkBook =objExcelApp.Workbooks.Add
'oWorkBook.SaveAs "D:\ExcelExample.xls"

End Sub