Visual LISP与Excel电子表格

VisualLISP与Excel电子格MicrosoftExcel在当今世界上的电子表格应用软件产品中占有绝对的统治地位。我们经常可以看到财会部门或人事部门利用它来处理日常的一些数据。虽然说Access在数据处理方面会比Excel好些，但它在一些简单的日常事务中象执行一些快速输入或排序时却不那么方便简单。它是一个常用的事务处理工具。这10年来，有许多AutoCAD程序是用于将提取数据并将其输出为。不论什么原因，它是很必要的。近10年来所见到的除了极少部分外(如TerryDotson的http://www.dotsoft.com/),输出的机制均为输出为ASCII文本文件，或有些是带格式文件。这是因为10年来，我们没有VisualLISP或VBA使程序与其它应用软件相连通并在相互之间传递信息。现在可以了。如果你还一直在使用逗号或TAB分界符的文本来区分你的数据，你应该好好地看看这篇文章了。VisualLISP(VBA也可以)提供了必要的函数来接触和与任何提供了类型库(内部暴露API[应用程序接口程序]的应用程序相联系。通常它是VB或VBA。这一类应用程序包括MicrosoftOffice(Access,Excel,Word,还有象Outlook和PowerPoint),以及象MathCAD,Visio、Actrix等产品。在你的代码中的第一件事就是必需在VisualLISP中加载ActiveX/COM。它是通过(vl-load-com)函数来完成的。第一件事尝试与Excel连接上就是搜索Excel的类型库文件。这里使用了Excel2000(9.0)作为例子。示例1演示了怎样通过Excel不同版本来取得与其类型库相关联。注意Excel2002用的是可执行文件来身来代替在以往版本中所使用的分离的TLB或OLB文件。Figure1:加载Excel97类型库(vl-load-com)(defunDSX-TypeLib-Excel(/sysdrvtlb)(setqsysdrv(getenv"systemdrive"))(cond((setqtlb(findfile(strcatsysdrv"\\ProgramFiles\\MicrosoftOffice\\Office\\Excel8.olb")))tlb)((setqtlb(findfile(strcatsysdrv"\\ProgramFiles\\MicrosoftOffice\\Office\\Excel9.olb")))tlb)((setqtlb(findfile(strcatsysdrv"\\ProgramFiles\\MicrosoftOffice\\Office\\Excel10.olb")))tlb)((setqtlb(findfile(strcatsysdrv"\\ProgramFiles\\MicrosoftOffice\\Office\\Excel.exe")))tlb)((setqtlb(findfile(strcatsysdrv"\\ProgramFiles\\MicrosoftOffice\\Office10\\Excel.exe")))tlb)))下一步是加载类型库并定义内部接口的属性、方法和恒量。这些都可以使用任意名称的前缀，该前缀只是用于对外部应用程序的快速及合理地调用。以下的文章内容将把类型库的前缀均置为斜体以方便区分。示例2演示了一个简单的函数来加载类型库并判断是否成功返回T或nil：Figure2:定义类型库接口(defunDSX-Load-TypeLib-Excel(/tlbfiletlbverout)(cond((nullmsxl-xl24HourClock)(if(setqtlbfile(DSX-TypeLib-Excel))(progn(setqtlbver(substr(vl-filename-basetlbfile)6))(cond((=tlbver"9")(princ"\n初始化MicrosoftExcel2000..."))((=tlbver"8")(princ"\n初始化MicrosoftExcel97..."))((=(vl-filename-basetlbfile)"Excel.exe")(princ"\n初始化MicrosoftExcelXP...")))(vlax-import-type-library:tlb-filenametlbfile:methods-prefix"msxl-":properties-prefix"msxl-":constants-prefix"msxl-")(ifmsxl-xl24HourClock(setqoutT)))))(T(setqoutT)))out)现在你已经在敲Excel的门并且得到“我在家!哪位，有什么事？”这样的回答。你必须温和地回答，象“你好!我这里有一个新的文件想存些数据进来!”示例3给出了一个简单的函数来打开Excel并利用缺省的工作簿(一般为3个工作表)来创建一个新的空白工作簿。工作表1将作为缺省的活动工作表。该函数返回vla-object指向新的Excel进程对象。Figure3:打开带有新的工作簿的Excel;;;注意:<dmode>可以设为"SHOW"(显示)或"HIDE"(隐藏)，它取决于你希望;;;Excel进程是否可以让用户直接操作访问。(defunDSX-Open-Excel-New(dmode/appsession)(princ"\n创建一个新的Excel电子表格文件...")(cond((setqappsession(vlax-create-object"Excel.Application"))(vlax-invoke-method(vlax-get-propertyappsession'WorkBooks)'Add)(if(=(strcasedmode)"SHOW")(vla-put-visibleappsession1)(vla-put-visibleappsession0))))appsession)经常用到的除了新建文件外还有打开现有的工作簿。示例4演示了怎样作为函数来调用实现该功能。注意文件名参数必须在调用该函数前检查过是存在的。你可能也同时注意到了在这个示例中使用了(vlax-get-or-create-object)来代替在示例3中使用的(vlax-create-object)。这是一个历史学家独特的函数，在VisualLISP程序中可能会经常不太注意到它。它会尝试获取现有的进程对象，如果找不到或失败，则会尝试新建一个。它虽然价值不高，但它却能节省你键入好多的代码。如果你只想新建一个进程或只想取得现有的进程，你可以只将其替换为(vlax-get-object)或(vlax-create-object)。Figure4:打开Excel并在其中打开现有的文档文件;;;注意:<xfile>必须为全路径文件名,;;;<dmode>可以设为"SHOW"(显示)或"HIDE"(隐藏)，它取决于你希望;;;Excel进程是否可以让用户直接操作访问。(defunDSX-Open-Excel-Exist(xfiledmode/appsession)(princ"\n打开Excel电子表格文件...")(cond((setqfn(findfilexfile))(cond((setqappsession(vlax-get-or-create-object"Excel.Application"))(vlax-invoke-method(vlax-get-propertyappsession'WorkBooks)'Openfn)(if(=(strcasedmode)"SHOW")(vla-put-visibleappsession1)(vla-put-visibleappsession0)))))(T(alert(strcat"\n不能找到指定的文件:"xfile))))appsession)现在你已经打开了工作簿并进入活动的工作表中，你肯定还想进一步取在表中取点数据。示例5:在活动的工作表中的单个单元格中获取数据;;;获取行<relrow>和列<relcol>范围内的单元格对象(defunDSX-Excel-Get-Cell(rngrelrowrelcol)(vlax-variant-value(msxl-get-item(msxl-get-cellsrng)(vlax-make-variantrelrow)(vlax-make-variantrelcol))));;;返回单元格(row,col)内容的值(defunDSX-Excel-Get-CellValue(rowcol)(vlax-variant-value(msxl-get-value(DSX-Excel-Get-Cell(msxl-get-ActiveSheetxlapp)rowcol))))尽管获取单个单元格的值有时是足够的，但经常情况下你会一次性获取一个指定行列范围内的值。这里有一些函数用于从多行、多列或单元格数组获取值。Figure6:在活动的工作表中获取一定行列范围中的数据;;;*************************************************************************;;;模块:DSX-Excel-Get-RowValues;;;描述:返回给定行的单元格值列表;;;参数:行号(整数),起始列,单元格数量;;;样例:(DSX-Excel-Get-RowValues3120)取得行3的前20个单元格的值;;;*************************************************************************(defunDSX-Excel-Get-RowValues(rowstartcolnumcells/nextout)(setqnextstartcol)(repeatnumcells(setqout(ifout(appendout(list(DSX-Excel-Get-CellValuerownext)));rowxcol(list(DSX-Excel-Get-CellValuerownext));rowxcol)next(1+next)));repeatout);;;*************************************************************************;;;模块:DSX-Excel-Get-ColumnValues;;;描述:返回给定列的单元格值列表;;;参数:列号(整数),起始行,单元格数量;;;样例:(DSX-Excel-Get-ColumnValues2120)取得列2(“B”)的前20个单元格的值;;;*************************************************************************(defunDSX-Excel-Get-ColumnValues(colstartrownumcells/nextout)(setqnextstartrow)(repeatnumcells(setqout(ifout(appendout(list(DSX-Excel-Get-CellValuenextcol)))(list(DSX-Excel-Get-CellValuenextcol)))next(1+next)));repeatout);;;*************************************************************************;;;模块:DSX-Excel-GetRangeValues-ByRows;;;描述:按行顺序获得某一区域的值并返回嵌套的列表;;;参数:起始行,起始列,行数,列数;;;样例:(DSX-Excel-GetRangeValues-ByRows11510)获取从1A到5J区域的值，每一子列表为一行;;;*************************************************************************(defunDSX-Excel-GetRangeValues-ByRows(startrowstartcolnumrowsnumcols/nextrowrowlstoutlst)(setqnextrowstartrow)(repeatnumrows(setqrowlst(DSX-Excel-Get-RowValuesnextrowstartcolnumcols)outlst(ifoutlst(appendoutlst(listrowlst))(listrowlst))nextrow(1+nextrow)))outlst);;;*************************************************************************;;;模块:DSX-Excel-GetRangeValues-ByCols;;;描述:按列顺序获得某一区域的值并返回嵌套的列表;;;参数:起始行,起始列,行数,列数;;;样例:(DSX-Excel-GetRangeValues-ByCols11510)获取从1A到5J区域的值，每一子列表为一列;;;*************************************************************************(defunDSX-Excel-GetRangeValues-ByCols(startrowstartcolnumrowsnumcols/nextrownextcolcollstoutlst)(setqnextcolstartcol)(repeatnumcols(setqcollst(DSX-Excel-Get-ColumnValuesnextcolstartrownumrows)outlst(ifoutlst(appendoutlst(listcollst))(listcollst))nextcol(1+nextcol)))outlst)好了!现在可以从Excel中攫取数据了，但既然可以取数据，也应该可以写入数据才对，下面就介绍怎样将值填入到单元格中。示例7演示了怎样将列表值输入到一行或一列的单元格中。这可以很方便地让你将图形中的数据转到Excel中。这样可让你轻松地将图形中的内容输出到报告中让你的老板感受你惊人的天才。示例7:将数据输入到工作表中;;;*************************************************************************;;;模块:DSX-Excel-Put-ColumnList;;;描述:将列表写到工作表指定列(startcol)中的指定起始行(startrow);;;参数:list,startrow,startcol;;;样例:(DSX-Excel-Put-ColumnList'("A""B""C")12)将数据分别输出到单元格(1,B)(2,B)(3,B)中;;;*************************************************************************(defunDSX-Excel-Put-ColumnList(lststartrowstartcol)(foreachitmlst(msxl-put-value(DSX-Excel-Get-Cellrangestartrowstartcol)itm)(setqstartrow(1+startrow)));repeat);;;*************************************************************************;;;模块：DSX-Excel-Put-RowList;;;描述：将列表写到工作表指定行(startrow)中的指定起始列(startcol);;;参数：list,startrow,startcol;;;示例：(DSX-Excel-Put-RowList'("A""B""C")21)将数据分别输出到单元格(1,B)(1,C)(1,D)中;;;*************************************************************************(defunDSX-Excel-Put-RowList(lststartrowstartcol)(foreachitmlst(msxl-put-value(DSX-Excel-Get-Cellrangestartrowstartcol)itm)(setqstartcol(1+startcol)));repeat)单独的数据将不会给你留下多深的印象。就是你输入“一个老板”也是一样的。你必须增加一些颜色给它，还有就是加些格式。让它漂亮起来!示例8给出了一些怎样一次性添加颜色给单元格或整行或整列。注意EXCEL的颜色调色板和AutoCAD是不一样的。你必须无能为力钻研Excel的在线帮助并找到你需要的颜色索引。示例8:更改Excel单元格的属性(颜色);;;*************************************************************************;;;模块：DSX-Excel-Put-CellColor;;;描述：为指定单元格填入颜色;;;参数：row,column,color(integer);;;示例：(DSX-Excel-Put-CellColor1114)将颜色#14填入到单元格(1,A);;;*************************************************************************(defunDSX-Excel-Put-CellColor(rowcolintcol/rng)(setqrng(DSX-Excel-Get-Cell(msxl-get-ActiveSheetxlapp)rowcol))(msxl-put-colorindex(msxl-get-interiorrng)intcol));;;*************************************************************************;;;模块：DSX-Excel-Put-RowCellsColor;;;描述：为一行单元格填入颜色;;;参数：startrow,startcol,num-cols,color(integer);;;示例：(DSX-Excel-Put-RowCellsColor11514)从行=1、列=1开始连接5列使用颜色#14;;;*************************************************************************(defunDSX-Excel-Put-RowCellsColor(startrowstartcolcolsintcol/next)(setqnextstartcol)(repeatcols(DSX-Excel-Put-CellColorstartrownextintcol)(setqnext(1+next))));;;*************************************************************************;;;模块：DSX-Excel-Put-ColumnCellsColor;;;描述：为一列单元格填入颜色;;;参数：startrow,startcol,num-rows,color(integer);;;示例：(DSX-Excel-Put-ColumnCellsColor11514)从行=1、列=1连接5行使用颜色#14;;;*************************************************************************(defunDSX-Excel-Put-ColumnCellsColor(startrowstartcolrowsintcol/next)(setqnextstartrow)(repeatrows(DSX-Excel-Put-CellColornextstartcolintcol)(setqnext(1+next))));;;*************************************************************************;;;模块：DSX-Excel-RangeAutoFit;;;描述：为选中的范围的实行自动调整宽度;;;参数：active-sheet(object);;;示例：(DSX-Excel-RangeAutoFitmyxlws);;;*************************************************************************(defunDSX-Excel-RangeAutoFit(active-sheet)(vlax-invoke-method(vlax-get-property(vlax-get-property(vlax-get-propertyactive-sheet'UsedRange)'Cells)'Columns)'AutoFit))示例9:将它们合在一起让我们将以上代码片段合在一起看看它们是怎样在AutoCAD中发挥作用的。该函数将提示你选择一个Excel.XLS文件打开并获取指定范围的行和列。它将通过列表形式返回数据，每一行为一个列表，而主列表是将每行列表合在一起。就象这样的表达式：((行列表)(行列表)(行列表)...)(defunC:GETXLREGION(/xlappxlfilereadytlbfileashrangexlist)(cond((DSX-Load-TypeLib-Excel)(cond((setqxlfile(getfiled"Excel电子表格文?quot;(ifG$XFILEG$XFILE"")"XLS"8))(setqG$XFILExlfile)(cond((setqxlapp(DSX-Open-Excel-Existxlfile"HIDE"))(setqash(msxl-Get-ActiveSheetxlapp))(setqrange(msxl-Get-ActiveCellxlapp));;;从行2列1开始取68行6列的数据出来(setqxlist(DSX-Excel-GetRangeValues-ByRows21686));;;将每一子列表显示出来看看你拿到了什么样的数据...(foreachmbrxlist(princmbr)(terpri))(setqxlistnil)(DSX-Excel-Quitxlapp)(gc);在关闭Excel后把所有的资源回收!)(T(princ"\n开始应用程序进程失败."))))))(T(alert"初始化Excel97类型库失败...")))(princ))--------------------------------------------------------------------------------;;;*************************************************************************;;;模块：DSX-Excel-Quit;;;描述：退出并关闭Excel进程;;;参数：app(进程对象);;;示例：(DSX-Excel-Quitxlapp);;;*************************************************************************(defunDSX-Excel-Quit(appsession)(cond((not(vlax-object-released-pappsession))(vlax-invoke-methodappsession'QUIT)(vlax-release-objectappsession))));;;*************************************************************************;;;模块：DSX-Excel-Kill;;;描述：强迫任何打开的Excel进程关闭;;;参数：none;;;示例：(DSX-Excel-Kill);;;*************************************************************************(defunDSX-Excel-Kill(/eo)(while(setqeo(vlax-get-object"Excel.Application"))(DSX-Excel-Quiteo)(vlax-release-objecteo)(setqeonil)(gc)(gc);;这样做有时还是不能完全杀除!)