LabWindowsCVI中调用Excel

LabWindows/CVI中调用Excel设计要求:1.启动Microsoft Excel并打开xls文件；2.向xls文件中写入数据；3.根据所填入的数据完成画图功能；4.Xls具有存盘，关闭等功能；5.关闭Microsoft Excel。

利用ActiveX技术进行Excel访问。

ActiveX技术以微软的COM技术为基础，也称之为ActiveX自动化。

ActiveX是一种软件组件，它可以插入到不同的应用程序中，并被当作程序的一部分来使用。

利用ActiveX可以把两个或两个以上的应用程序组合在一起，使其像应用程序一样工作，或者可以在同一应用程序外壳中进行不同应用程序间的切换。

生成自动化仪器驱动器在LabWindows／CVI的工程主窗口中选择Tool/Create ActiveXController，弹出ActiveX Controller Wizard对话框，点击Next进入自动化服务器选择对话框，如图1所示。

选择Microsoft Excel 12.0 Object Library，点击Next进入服务器配置对话框，如图所示。

ActiveX Server 表示已选中的ActiveX服务器名称，Instnmmnt Prefix表示生成驱动器的前缀，可以设置为空或键人有效的C语言标识符，若不输入任何字符，系统会默认产生一个有效的文件名，一般情况下不用修改此属性。

Target．fp File表示CVI产生的．fp文件在磁盘中的存储路径，点击Browse可以查找或定位一个．fp文件。

将Add．fp File To Project选中表示允许把所产生的．fp文件加入当前工程中。

点击Compatibility Options进入兼容选项对话框，通过该对话框选项可以保持所产生的代码源文件与已经存在的ActiveX仪器驱动的向后兼容性，系统默认为Per Property。

点击Next进入高级选项对话框，在高级选项对话框中，点击Advanced Option 将显示高级函数配置对话框，可以通过此对话框来增减ActiveX对象及修改对象的名称和标识符，高级函数一般采用默认配置即可。

配置好高级函数之后，点击OK，返回到高级选项对话框，再点击Next按钮，系统会自动产生ActiveX所有驱动器文件，可以看到在左侧导航栏Instrument files中已经加入了驱动文件。

图l 自动化服务器选择对话框自动化服务器配置对话框自动化服务器面板附程序://---------------------------------------------------------------------------- #include <cviauto.h>#include <utility.h>#include <ansi_c.h>#include <userint.h>#include "toolbox.h"#include "exceldem.h"#include "excel97.h"#define caErrChk errChk#define APP_AUTOMATION_ERR "Error: Microsoft Excel Automation"#define APP_WARNING "Warning"#define EXCEL_ARRAY_OF_CELLS "A2:H11"#define ROWS 10#define COLUMNS 8#define LAUNCHERR "\An error occurred trying to launch Excel 97 through its automation interface.\n\n\ Ensure that Excel is installed and that you can launch it manually. If errors\n\ persist, try to launch Excel manually and use the CONNECT button instead."//---------------------------------------------------------------------------- // Variables//---------------------------------------------------------------------------- static int panelHandle = 0;static int excelLaunched = 0;static int appVisible = 1;static HRESULT status;static ExcelObj_App ExcelAppHandle = 0;static ExcelObj_Workbooks ExcelWorkbooksHandle = 0;static ExcelObj_Workbook ExcelWorkbookHandle = 0;static ExcelObj_Sheets ExcelSheetsHandle = 0;static ExcelObj_Worksheet ExcelWorksheetHandle = 0;static ExcelObj_Range ExcelRangeHandle = 0;static ExcelObj_ChartObject ExcelChartObjHandle = 0;static ExcelObj_Chart ExcelChartHandle = 0;static ExcelObj_ChartGroup ExcelChartsHandle = 0;static ERRORINFO ErrorInfo;static VARIANT MyVariant;static LPDISPATCH MyDispatch;static VARIANT MyCellRangeV;//---------------------------------------------------------------------------- // Prototypes//---------------------------------------------------------------------------- HRESULT ClearObjHandle(CAObjHandle *objHandle);static int ShutdownExcel(void);static void ReportAppAutomationError (HRESULT hr);static void InitVariables(void);static int UpdateUIRDimming(int panel);//---------------------------------------------------------------------------- // Main//---------------------------------------------------------------------------- int main (int argc, char *argv[]){if (InitCVIRTE (0, argv, 0) == 0)return -1;CA_InitActiveXThreadStyleForCurrentThread (0, COINIT_APARTMENTTHREADED);SetSleepPolicy (VAL_SLEEP_MORE);if ((panelHandle = LoadPanel (0, "exceldem.uir", PANEL)) < 0)return -1;// SetupUpdateUIRDimming(panelHandle);InitVariables();DisplayPanel (panelHandle);RunUserInterface ();// CleanupShutdownExcel();DiscardPanel (panelHandle);return 0;}//---------------------------------------------------------------------------- // InitVariables//---------------------------------------------------------------------------- static void InitVariables(void){// Demo path and filenameGetCtrlVal (panelHandle, PANEL_VISIBILITY, &appVisible);return;}//---------------------------------------------------------------------------- // UIR Callbacks//---------------------------------------------------------------------------- // LaunchApp//---------------------------------------------------------------------------- int CVICALLBACK LaunchApp (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;switch (event) {case EVENT_COMMIT:// Launch AppSetWaitCursor (1);error = Excel_NewApp (NULL, 1, LOCALE_NEUTRAL, 0, &ExcelAppHandle);SetWaitCursor (0);if (error<0){MessagePopup (APP_AUTOMATION_ERR, LAUNCHERR);error = 0;goto Error;}// Make App Visibleerror = Excel_SetProperty (ExcelAppHandle, NULL, Excel_AppVisible, CAVT_BOOL, appVisible?VTRUE:VFALSE);if (error<0)goto Error;UpdateUIRDimming(panelHandle);MakeApplicationActive ();excelLaunched = 1;break;}Error:if (error < 0)ReportAppAutomationError (error);return 0;}//---------------------------------------------------------------------------- // ShutdownApp//---------------------------------------------------------------------------- int CVICALLBACK ShutdownApp (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){switch (event) {case EVENT_COMMIT:SetWaitCursor (1);ShutdownExcel();SetWaitCursor (0);UpdateUIRDimming(panel);break;}return 0;}//---------------------------------------------------------------------------- // ShutdownExcel//---------------------------------------------------------------------------- static int ShutdownExcel(void){HRESULT error = 0;ClearObjHandle (&ExcelRangeHandle);ClearObjHandle (&ExcelWorksheetHandle);ClearObjHandle (&ExcelSheetsHandle);if (ExcelWorkbookHandle){// Close workbook without savingerror = Excel_WorkbookClose (ExcelWorkbookHandle, NULL, CA_VariantBool (VFALSE),CA_DEFAULT_VAL, CA_VariantBool (VFALSE));if (error < 0)goto Error;ClearObjHandle (&ExcelWorkbookHandle);}ClearObjHandle (&ExcelWorkbooksHandle);if (ExcelAppHandle){if (excelLaunched){// Quit the Applicationerror = Excel_AppQuit (ExcelAppHandle, &ErrorInfo);if (error < 0) goto Error;}ClearObjHandle (&ExcelAppHandle);}return 0;Error:if (error < 0)ReportAppAutomationError (error);return error;}//----------------------------------------------------------------------------// ChangeVisibility//----------------------------------------------------------------------------int CVICALLBACK ChangeVisibility (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;switch (event) {case EVENT_COMMIT:GetCtrlVal(panel, control, &appVisible);if (ExcelAppHandle){error = Excel_SetProperty (ExcelAppHandle, NULL, Excel_AppVisible,CAVT_BOOL, appVisible?VTRUE:VFALSE);if (error < 0)ReportAppAutomationError (error);}break;}return 0;}//---------------------------------------------------------------------------- // OpenAppFile//----------------------------------------------------------------------------int CVICALLBACK OpenAppFile (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;char fileName[MAX_PATHNAME_LEN];switch (event) {case EVENT_COMMIT:if (!ExcelWorkbooksHandle){// Get Workbookserror = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppWorkbooks,CAVT_OBJHANDLE, &ExcelWorkbooksHandle);if (error<0)goto Error;// Open existing WorkbookGetProjectDir (fileName);strcat(fileName, "\\exceldem.xls");error = Excel_WorkbooksOpen (ExcelWorkbooksHandle, NULL, fileName, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL,&ExcelWorkbookHandle);if (error<0)goto Error;// Get Active Workbook Sheetserror = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, CAVT_OBJHANDLE, &ExcelSheetsHandle);if (error<0)goto Error;// Get First Sheeterror = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(1),&ExcelWorksheetHandle);if (error<0)goto Error;// Make First Sheet Active - should already be activeerror = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);if (error<0)goto Error;// Update UIRUpdateUIRDimming(panel);}elseMessagePopup(APP_WARNING, "Document already open");break;}return 0;Error:ClearObjHandle (&ExcelWorksheetHandle);ClearObjHandle (&ExcelSheetsHandle);ClearObjHandle (&ExcelWorkbookHandle);ClearObjHandle (&ExcelWorkbooksHandle);if (error < 0)ReportAppAutomationError (error);return 0;}//---------------------------------------------------------------------------- // SaveAppFile//---------------------------------------------------------------------------- int CVICALLBACK SaveAppFile (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;char fileName[MAX_PATHNAME_LEN];switch (event) {case EVENT_COMMIT:if (ExcelWorkbookHandle){GetProjectDir (fileName);if (FileSelectPopup (fileName, "*.xls", "*.xls","Save file as...", VAL_SAVE_BUTTON, 0, 1, 1, 1, fileName)>0){SetWaitCursor (1);error = CA_VariantSetCString(&MyVariant, fileName);error = Excel_WorkbookSaveAs (ExcelWorkbookHandle, NULL, MyVariant,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL,ExcelConst_xlNoChange,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL);CA_VariantClear(&MyVariant);SetWaitCursor (0);if (error < 0)goto Error;}}break;}return 0;Error:if (error < 0)ReportAppAutomationError (error);return 0;}//---------------------------------------------------------------------------- // CloseAppFile//---------------------------------------------------------------------------- int CVICALLBACK CloseAppFile (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;switch (event) {case EVENT_COMMIT:if (ExcelWorkbookHandle){ClearObjHandle (&ExcelRangeHandle);ClearObjHandle (&ExcelSheetsHandle);ClearObjHandle (&ExcelWorksheetHandle);if (ExcelWorkbookHandle){// Close workbook without savingerror = Excel_WorkbookClose (ExcelWorkbookHandle, NULL, CA_VariantBool (VFALSE),CA_DEFAULT_VAL, CA_VariantBool (VFALSE));if (error < 0)goto Error;ClearObjHandle (&ExcelWorkbookHandle);}ClearObjHandle (&ExcelWorkbooksHandle);ExcelWorksheetHandle = 0;UpdateUIRDimming(panel);}break;}return 0;Error:if (error < 0)ReportAppAutomationError (error);return 0;}//---------------------------------------------------------------------------- // WriteDataToExcel//---------------------------------------------------------------------------- // This function illustrates 3 different ways of writing data to the Excel cells. //---------------------------------------------------------------------------- HRESULT WriteDataToExcel(void){VARIANT *vArray = NULL;LPSAFEARRAY MySafeArray = NULL;HRESULT error = 0;int i, j;SetWaitCursor (1);// Open new Range for Worksheeterror = CA_VariantSetCString (&MyCellRangeV, EXCEL_ARRAY_OF_CELLS);error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);if (error<0) goto Error;// Make range Activeerror = Excel_RangeActivate (ExcelRangeHandle, &ErrorInfo);if (error<0) goto Error;//----------------------------------------------------------------// 1) Set all cells in Range to a single value of zero//----------------------------------------------------------------error = Excel_SetProperty (ExcelRangeHandle, &ErrorInfo, Excel_RangeValue2, CAVT_VARIANT, CA_VariantFloat (0.0));if (error<0) goto Error;//----------------------------------------------------------------// 2) Set each cell in Range one at a time using an offset from// range's top left cell//----------------------------------------------------------------for (i=0;i<ROWS;i++){for (j=0;j<COLUMNS;j++){error = Excel_RangeSetItem (ExcelRangeHandle, &ErrorInfo, CA_VariantInt (i+1), CA_VariantInt (j+1), CA_VariantFloat ( sin(3.14*(i+1)/(ROWS+1)) * sin(3.14*(j+1)/(COLUMNS+1))) );if (error<0) goto Error;}}//----------------------------------------------------------------// 3) Set all cells at once using a SAFEARRAY in a VARIANT// NOTE: The arrays must be 2-dimensional even if setting only// one row or one column.//----------------------------------------------------------------// Create a Variant Array and set each valuevArray = (VARIANT *) calloc (ROWS*COLUMNS, sizeof(VARIANT));if (!vArray)goto Error;for (i=0;i<ROWS;i++){for (j=0;j<COLUMNS;j++){error = CA_VariantSetDouble (&vArray[i*COLUMNS+j], sin(3.14*(i+1)/(ROWS+1)) * sin(3.14*(j+1)/(COLUMNS+1)));if (error<0) goto Error;}}// Create a SAFEARRAYerror = CA_Array2DToSafeArray (vArray, CAVT_VARIANT, ROWS, COLUMNS, &MySafeArray);if (error<0) goto Error;// Set SafeArray into a Variant to send to Excelerror = CA_VariantSetSafeArray (&MyVariant, CAVT_VARIANT, MySafeArray);if (error<0) goto Error;// Set Range with one call passing SAFEARRAY as Varianterror = Excel_SetProperty (ExcelRangeHandle, &ErrorInfo, Excel_RangeValue2, CAVT_VARIANT, MyVariant);if (error<0) goto Error;Error:SetWaitCursor (0);// Free array of VARIANTif (vArray){for (i=0;i<ROWS;i++){for (j=0;j<COLUMNS;j++){CA_VariantClear (&vArray[i*COLUMNS+j]);}}free(vArray);}// Free SAFEARRAY in VARIANTCA_VariantClear(&MyVariant);CA_VariantClear(&MyCellRangeV);// Clear Range HandleClearObjHandle (&ExcelRangeHandle);if (error < 0)ReportAppAutomationError (error);return error;}int CVICALLBACK WriteData (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){switch (event){case EVENT_COMMIT:WriteDataToExcel();break;}return 0;}//---------------------------------------------------------------------------- // MakeChartInExcel//---------------------------------------------------------------------------- HRESULT MakeChartInExcel(void){HRESULT error = 0;SetWaitCursor (1);// Open new Range for Worksheeterror = CA_VariantSetCString (&MyCellRangeV, EXCEL_ARRAY_OF_CELLS);error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);CA_VariantClear(&MyCellRangeV);if (error<0) goto Error;// Open new Chart Collection for Worksheetstatus = Excel_WorksheetChartObjects (ExcelWorksheetHandle, NULL, CA_DEFAULT_VAL, &ExcelChartsHandle);if (status<0) goto Error;// Create new chartstatus = Excel_ChartObjectsAdd (ExcelChartsHandle, NULL, 175.0, 175.0,300.0, 200.0, &ExcelChartObjHandle);if (status<0) goto Error;status = Excel_GetProperty (ExcelChartObjHandle, NULL, Excel_ChartObjectChart, CAVT_OBJHANDLE, &ExcelChartHandle);if (status<0) goto Error;// Use Chart Wizard to setup Chartstatus = CA_VariantSetCString (&MyVariant, "Chart #1");status = CA_GetDispatchFromObjHandle (ExcelRangeHandle, &MyDispatch); // Get dispatch for rangestatus = Excel_ChartChartWizard (ExcelChartHandle, &ErrorInfo,CA_VariantDispatch (MyDispatch),CA_VariantLong(ExcelConst_xl3DSurface),CA_DEFAULT_VAL,CA_VariantInt(ExcelConst_xlRows),CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL,MyVariant,CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL);CA_VariantClear(&MyVariant);if (status<0) goto Error;// Lets get the current rotation valuestatus = Excel_GetProperty (ExcelChartHandle, NULL, Excel_ChartRotation, CAVT_VARIANT, &MyVariant);if (status<0) goto Error;CA_VariantClear(&MyVariant);Error:SetWaitCursor (0);CA_VariantClear(&MyCellRangeV);CA_VariantClear(&MyVariant);ClearObjHandle (&ExcelRangeHandle);ClearObjHandle (&ExcelChartHandle);ClearObjHandle (&ExcelChartObjHandle);ClearObjHandle (&ExcelChartsHandle);if (error < 0)ReportAppAutomationError (error);return error;}int CVICALLBACK MakeChart (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){switch (event){case EVENT_COMMIT:MakeChartInExcel();break;}return 0;}//---------------------------------------------------------------------------- // RunMacro//---------------------------------------------------------------------------- int CVICALLBACK RunMacro (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){HRESULT error = 0;switch (event){case EVENT_COMMIT:SetWaitCursor (1);// Application.Run "exceldem.xls!NewDataFromMacro"status = CA_VariantSetCString (&MyVariant, "exceldem.xls!NewDataFromMacro");error = Excel_AppRun (ExcelAppHandle, NULL, MyVariant, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,CA_DEFAULT_VAL, CA_DEFAULT_VAL, NULL);CA_VariantClear(&MyVariant);if (status<0) goto Error;SetWaitCursor (0);break;}Error:SetWaitCursor (0);if (error < 0)ReportAppAutomationError (error);return 0;}//---------------------------------------------------------------------------- // Quit//---------------------------------------------------------------------------- int CVICALLBACK Quit (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){switch (event) {case EVENT_COMMIT:QuitUserInterface (0);break;}return 0;}//----------------------------------------------------------------------------// UpdateUIRDimming//---------------------------------------------------------------------------- static int UpdateUIRDimming(int panel){SetCtrlAttribute (panel, PANEL_LAUNCHAPP, ATTR_DIMMED, (int)ExcelAppHandle);SetCtrlAttribute (panel, PANEL_SHUTDOWNAPP, ATTR_DIMMED, !(int)ExcelAppHandle);SetCtrlAttribute (panel, PANEL_OPENFILE, ATTR_DIMMED, ((int)ExcelWorksheetHandle || !(int)ExcelAppHandle));SetCtrlAttribute (panel, PANEL_SAVEFILE, ATTR_DIMMED, !(int)ExcelWorksheetHandle);SetCtrlAttribute (panel, PANEL_CLOSEFILE, ATTR_DIMMED, !(int)ExcelWorksheetHandle);SetCtrlAttribute (panel, PANEL_WRITEDATA, ATTR_DIMMED, !(int)ExcelWorksheetHandle);SetCtrlAttribute (panel, PANEL_MAKECHART, ATTR_DIMMED, !(int)ExcelWorksheetHandle);SetCtrlAttribute (panel, PANEL_RUNMACRO, ATTR_DIMMED, !(int)ExcelWorksheetHandle);return 0;}//----------------------------------------------------------------------------// ClearObjHandle//---------------------------------------------------------------------------- HRESULT ClearObjHandle(CAObjHandle *objHandle){HRESULT error = 0;if ((objHandle) && (*objHandle)){error = CA_DiscardObjHandle (*objHandle);*objHandle = 0;}return error;}//---------------------------------------------------------------------------- // ReportWordAutomationError//---------------------------------------------------------------------------- static void ReportAppAutomationError (HRESULT hr){char errorBuf[256];if (hr < 0) {CA_GetAutomationErrorString (hr, errorBuf, sizeof (errorBuf));MessagePopup (APP_AUTOMATION_ERR, errorBuf);}return;}。