KozMos VLXLS Project

 

Copyright(C) 1994-2009 by KozMos Inc.

 

Permission to use, copy, modify, and distribute this software for any purpose and without fee is hereby granted, provided that the above copyright notice appears in all copies and that both that copyright notice and the limited warranty and restricted rights notice below appear in all supporting documentation.

 

KOZMOS PROVIDES THIS PROGRAM "AS IS" AND WITH ALL FAULTS. KOZMOS SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTY OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR USE. KOZMOS INC. DOES NOT WARRANT THAT THE OPERATION OF THE PROGRAM WILL BE UNINTERRUPTED OR ERROR FREE.

 

You can directly save this HTML file or download the source LSP file at download center.

 

Public Function

Name

(vlxls-variant->list VariantValue)

Usage

Convert a variant into normal Visual LISP LIST data, nested Variant and safearray will also be converted.

Input

VARIANT

Input Variant

RetVal

True

LIST

Valid Visual LISP variable value

Fail

STR

“”

(Defun vlxls-variant->list (VarX / Run Item Rtn)

  (setq Run T)

  (while

    Run

     (cond ((= (type VarX) 'SAFEARRAY)

           (setq VarX (vlax-safearray->list VarX))

          )

          ((= (type VarX) 'VARIANT)

           (if    (member (vlax-variant-type VarX) (list 5 4 3 2))

             (setq VarX (vlax-variant-change-type Varx vlax-vbString))

           )

           (setq VarX (vlax-variant-value VarX))

          )

          (t (setq Run nil))

     )

  )

  (cond  ((= (type VarX) 'LIST)

        (foreach Item VarX

          (setq Item (vlxls-variant->list Item)

               Rtn  (append Rtn (list Item))

          )

        )

       )

       ((= VarX nil) (setq Rtn ""))

       (t (setq Rtn VarX))

  )

  Rtn

)

Examples:

NONE

 

Color Transfer Function

Name

(vlxls-color-eci->truecolor ExcelColorIndexNumber)

Usage

Convert Excel ColorIndex number into most matched AutoCAD2004+ truecolor number (stored by DXF420).

Input

INT

Excel ColorIndex integer (0 to 56)

RetVal

True

INT

Valid AutoCAD 2004+ truecolor number

Fail

INT

16711935 for None

(Defun vlxls-color-ECI->truecolor (Color / Rtn)

  (if (setq Rtn (cdr (assoc Color *xls-color*)))

    (setq Rtn (nth 1 Rtn))

  )

  (if (null Rtn)

    (setq Rtn 16711935)

  )

  Rtn

)

Examples:

(vlxls-color-eci->truecolor 0)  ==> 16711935

(vlxls-color-eci->truecolor 1) ==>  0

(vlxls-color-eci->truecolor 12) ==>  8355584

(vlxls-color-eci->truecolor 120)  ==> 16711935

 

Color Transfer Function

Name

(vlxls-color-eci->aci ExcelColorIndexNumber)

Usage

Convert Excel ColorIndex number into most matched AutoCAD ACI Integer number.

Input

INT

Excel ColorIndex integer (0 to 56)

RetVal

True

INT

Valid AutoCAD ACI Integer number (0 to 256)

Fail

INT

256 for BYLAYER

(Defun vlxls-color-eci->aci (Color / Rtn)

(if (null (setq Rtn (cdr (assoc Color *xls-color*))))

  (setq Rtn 256)

    (setq Rtn (nth 0 Rtn))

  )

  Rtn

)

Examples:

(vlxls-color-eci->aci 0)  ==> 256

(vlxls-color-eci->aci 1) ==>  18

(vlxls-color-eci->aci 12) ==>  56

(vlxls-color-eci->aci 120)  ==> 256

 

Color Transfer Function

Name

(vlxls-color-aci->eci AutoCADColorIndexNumber)

Usage

Convert AutoCAD ColorIndex number into Excel ColorIndex .

Input

INT

AutoCAD ColorIndex integer (0 to 256)

RetVal

True

INT

Valid Excel ColorIndex number (from 1 to 56)

Fail

INT

0 for NONE

(Defun vlxls-color-aci->eci (Color / Item Rtn)

  (foreach Item    *xls-color*

    (if    (= (nth 1 Item) Color)

      (setq Rtn (car Item))

    )

  )

  (if (null Rtn)

    (setq Rtn 0)

  )

  Rtn

)

Examples:

(vlxls-color-aci->eci 0)  ==> 0

(vlxls-color-aci->eci 1) ==>  3

(vlxls-color-aci->eci 12) ==>  0

(vlxls-color-aci->eci 120)  ==> 0

 

Color Transfer Function

Name

(vlxls-color-aci->truecolor AutoCADColorIndexNumber)

Usage

Convert AutoCAD ColorIndex number into most matched AutoCAD2004+ true color number (using Excel ColorIndex as intermediary, provided for use in AutoCAD2002. In AutoCAD2004+, this can be done directly by AutoCAD.

Input

INT

AutoCAD ColorIndex integer (0 to 256)

RetVal

True

INT

Valid AutoCAD2004+ truecolor number

Fail

INT

16711935 for None

(Defun vlxls-color-aci->truecolor (aci)

  (vlxls-color-eci->truecolor (vlxls-color-aci->eci aci))

)

Examples:

(vlxls-color-aci-> truecolor 0)  ==>  16711935

(vlxls-color-aci->truecolor 1) ==>  16711680

(vlxls-color-aci-> truecolor 12) ==>  16711935

(vlxls-color-aci-> truecolor 120)  ==>  16711935

 

Excel Application Session Progress Function

Name

(vlxls-app-init)

Usage

Import Microsoft Excel Type Library, set prefix of "msxl-" for all of the :methods-prefix; :properties-prefix & :constants-prefix. This function can detect Excel’s installation path automatically from Windows registry so that it can run smoothly on any language platform of Windows and Office.

Input

NONE

No Arguments

RetVal

True

BOOLEAN

msxl-xl24HourClock

Fail

BOOLEAN

NIL

(Defun vlxls-app-Init

       (/ OSVar GGG Olb8 Olb9 Olb10 TLB Out msg msg1 msg2)

  (if *Chinese*

    (setq msg  "\n 初始化微软Excel "

         msg1 "\042初始化Excel错误\042"

         msg2 (strcat

               "\042 警告"

               "\n ===="

               "\n 无法在您的计算机上检测到微软Excel软件"

               "\n 如果您确认已经安装Excel, 请发送电子邮"

               "\n 件到kozmosovia@hotmail.com获取更多的解决方案\042"

              )

    )

    (setq msg  "\n Initializing Microsoft Excel "

         msg1 "\042Initialization Error\042"

         msg2 (strcat

               "\042 WARNING"

     "\n ======="

               "\n Can NOT detect Excel97/200X/XP in your computer"

               "\n If you already have Excel installed, please email"

               "\n us to get more solution via kozmosovia@hotmail.com\042")

    )

  )

  (if (null msxl-xl24HourClock)

    (progn

      (if (and (setq GGG

                    (vl-registry-read

                     "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\App Paths\\Excel.EXE"

                     "Path"

                    )

              )

              (setq GGG (strcase (strcat GGG "Excel.EXE")))

         )

       (progn

         (foreach OSVar (list "SYSTEMROOT" "WINDIR"

                            "WINBOOTDIR"     "SYSTEMDRIVE"

                            "USERNAME" "COMPUTERNAME"

                            "HOMEDRIVE"      "HOMEPATH"

                            "PROGRAMFILES"

                           )

           (if    (vl-string-search (strcat "%" OSVar "%") GGG)

             (setq GGG       (vl-string-subst

                       (strcase (getenv OSVar))

                       (strcat "%" OSVar "%")

                       GGG

                     )

             )

           )

         )

         (setq   Olb8  (findfile (vl-string-subst "EXCEL8.OLB" "EXCEL.EXE" GGG))

              Olb9  (findfile (vl-string-subst "EXCEL9.OLB" "EXCEL.EXE" GGG))

              Olb10 (findfile (vl-string-subst "EXCEL10.OLB" "EXCEL.EXE" GGG))

         )

         (cond  ((= (vl-filename-base (vl-filename-directory GGG))

                  "OFFICE12"

               )

               (setq TLB GGG

                     Out "2007"

               )

              )

((= (vl-filename-base (vl-filename-directory GGG))

                  "OFFICE11"

               )

               (setq TLB GGG

                     Out "2003"

               )

              )

              ((= (vl-filename-base (vl-filename-directory GGG))

                  "OFFICE10"

               )

               (setq TLB GGG

                     Out "XP"

               )

              )

              (Olb9

               (setq TLB Olb9

                     Out "2000"

               )

              )

              (Olb8

               (setq TLB Olb8

                     Out "97"

               )

              )

              (t (setq Out "Version Unknown"))

         )

         (if TLB

           (progn

             (princ (strcat MSG Out "..."))

             (vlax-import-type-library

              :tlb-filename    TLB               :methods-prefix

              "msxl-"            :properties-prefix

              "msxl-"            :constants-prefix "msxl-"

              )

           )

         )

       )

       (progn

         (if vldcl-msgbox

(vldcl-msgbox "x" msg1 msg2)

(alert (read msg2))

)

         (exit)

       )

      )

    )

  )

  msxl-xl24HourClock

)

Examples:

(vlxls-app-init) ==>  33

 

Excel Application Session Progress Function

Name

(vlxls-app-new ShowExcelFlag)

Usage

Open a new Excel session and start a new workbook.

Input

BOOLEAN

T for display, nil for hide

RetVal

True

VLOBJ

Excel Session vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-app-New (UnHide / Rtn)

  (if (vlxls-app-init)

    (progn

      (if *Chinese*

           (princ "\n 新建微软Excel工作表...")

       (princ "\n Creating new Excel Spreadsheet file...")

      )

      (if (setq Rtn (vlax-get-or-create-object "Excel.Application"))

       (progn

         (vlax-invoke-method

           (vlax-get-property Rtn 'WorkBooks)

           'Add

         )

         (if UnHide

           (vla-put-visible Rtn 1)

           (vla-put-visible Rtn 0)

         )

       )

      )

    )

  )

  Rtn

)

Examples:

(setq *xlapp* (vlxls-app-new T))  ==>  #<VLA-OBJECT _Application 001db27c>

 

Excel Application Session Progress Function

Name

(vlxls-app-open XLSfilename ShowExcelFlag)

Usage

Open a new Excel session to start existing XLS file.

Input

STR

XLS file name with full path, ".XLS" not needed.

BOOLEAN

T for display, nil for hide

RetVal

True

VLOBJ

Excel Session vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-app-open

       (XLSFile UnHide / ExcelApp WorkSheet Sheets ActiveSheet Rtn)

  (setq XLSFile (strcase XLSFile))

  (if (null (wcmatch XLSFile "*.XLS"))

    (setq XLSFile (strcat XLSFile ".XLS"))

  )

  (if (and (findfile XLSFile)

          (setq Rtn (vlax-get-or-create-object "Excel.Application"))

      )

    (progn

      (vlax-invoke-method

       (vlax-get-property Rtn 'WorkBooks)

       'Open

       XLSFile

      )

      (if UnHide

       (vla-put-visible Rtn 1)

       (vla-put-visible Rtn 0)

      )

    )

  )

  Rtn

)

Examples:

(setq *xlapp* (vlxls-app-open “C:/test.XLS” T))  ==>  #<VLA-OBJECT _Application 001efd2c>

 

Excel Application Session Progress Function

Name

(vlxls-app-save ExcelSessionVLA-OBJECT)

Usage

Perform save operation in Excel.

Input

VLOBJ

Excel session vla-object

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-app-save (xlapp)

  (equal (vlax-invoke-method

          (vlax-get-property Xlapp "ActiveWorkbook")

          "Save"

        )

        :vlax-true

  )

)

Examples:

(vlxls-app-save *xlapp*)  ==>  T

 

Excel Application Session Progress Function

Name

(vlxls-app-saveas ExcelSessionVLA-OBJECT SavedFileName)

Usage

Perform saveas operation in Excel.

Input

VLOBJ

Excel session vla-object

STR

Saved XLS file name with full path

NIL for a temporary “XLS.XLS” file in current drawing path.

RetVal

True

STRING

XLS file name with full path

Fail

BOOLEAN

NIL

(Defun vlxls-app-saveas    (xlapp Filename / Rtn)

  (if (null filename)

    (setq filename (strcat (getvar "dwgprefix") "XLS.XLS"))

  )

  (if (null (wcmatch (setq filename (strcase Filename)) "*`.XLS"))

    (setq filename (strcat filename ".XLS"))

  )

  (if (findfile Filename)

    (vl-file-delete (findfile Filename))

  )

  (vlax-invoke-method

    (vlax-get-property Xlapp "ActiveWorkbook")

    "SaveAs"

    Filename

    msxl-xlNormal

    ""

    ""

    :vlax-False

    :vlax-False

    nil

  )

  (findfile Filename)

)

Examples:

(vlxls-app-saveas *xlapp* nil)  ==>  “C:/Temp-Folder/XLS.XLS”

(vlxls-app-saveas *xlapp* “C:/Temp-Folder/XLS.XLS”)  ==>  “C:/Temp-Folder/XLS.XLS”

(vlxls-app-saveas *xlapp* nil)  ==>  NIL

 

Excel Application Session Progress Function

Name

(vlxls-app-quit ExcelSessionVLA-OBJECT SavedFlag)

Usage

Quit active workbook of Excel session and release Excel application.

Input

VLOBJ

Excel session vla-object

BOOLEAN

Save Excel active workwook flag, T for save, NIL for unsave

RetVal

True

BOOLEAN

NIL

Fail

BOOLEAN

NIL

(Defun vlxls-app-quit (ExlObj SaveYN)

  (if SaveYN

    (vlax-invoke-method

      (vlax-get-property ExlObj "ActiveWorkbook")

      'Close

    )

    (vlax-invoke-method

      (vlax-get-property ExlObj "ActiveWorkbook")

      'Close

      :vlax-False

    )

  )

  (vlax-invoke-method ExlObj 'QUIT)

  (vlax-release-object ExlObj)

  (setq ExlObj nil)

  (gc)

)

Examples:

(vlxls-app-quit *xlapp* nil)  ==>  nil

 

Excel Application Session Progress Function

Name

(vlxls-app-kill)

Usage

Close all active Excel workbooks.

Input

NONE

No Arguments

RetVal

True

BOOLEAN

NIL

Fail

BOOLEAN

NIL

(Defun vlxls-app-kill (SaveYN / ExlObj)

  (while (setq ExlObj (vlax-get-object "Excel.Application"))

    (vlxls-app-quit ExlObj SaveYN)

  )

)

Examples:

(vlxls-app-kill T)  ==>  nil

 

Excel Application Session Progress Function

Name

(vlxls-app-autofit ExcelSessionVLA-OBJECT)

Usage

Autofit the column width of all Excel session used ranges.

Input

VLOBJ

Excel session vla-object

RetVal

True

Variant

T

Fail

BOOLEAN

NIL

(Defun vlxls-app-autofit (xlapp / sh act Rtn)

  (setq act (vlxls-Sheet-Get-Active xlapp))

  (foreach sh (append (vl-remove act (vlxls-sheet-get-all Xlapp))

                    (list act)

             )

    (setq Rtn (variant-value

              (msxl-autofit

                (msxl-get-columns

                  (msxl-get-Cells

                    (vlxls-sheet-get-usedrange xlapp sh)

                  )

                )

              )

             )

    )

  )

  (equal Rtn :vlax-true)

)

Examples:

(vlxls-app-autofit *xlapp*)  ==>  T

(vlxls-app-autofit *xlapp*)  ==>  NIL

 

Excel Sheet Progress Function

Name

(vlxls-sheet-get-all ExcelSessionVLA-OBJECT)

Usage

Get name list of all sheets.

Input

VLOBJ

Excel session vla-object

RetVal

True

LIST

List contain all sheets’ name

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-get-all (xlapp / SH Rtn)

  (vlax-for SH (vlax-get-property Xlapp "sheets")

    (setq Rtn (cons (vlax-get-property sh "Name") Rtn))

  )

  (reverse Rtn)

)

Examples:

(vlxls-sheet-get-all *xlapp*)  ==>  ("Sheet1" "Sheet2" "Sheet3")

 

Excel Sheet Progress Function

Name

(vlxls-sheet-get-active ExcelSessionVLA-OBJECT)

Usage

Get active sheet name.

Input

VLOBJ

Excel session vla-object

RetVal

True

STRING

Active sheet's name string

Fail

BOOLEAN

NIL

(Defun vlxls-Sheet-Get-Active (xlapp)

  (vlax-get-property (msxl-get-ActiveSheet Xlapp) 'name)

)

Examples:

(vlxls-sheet-get-active *xlapp*)  ==>  "Sheet2"

 

Excel Sheet Progress Function

Name

(vlxls-sheet-delete ExcelSessionVLA-OBJECT DeleteSheetName)

Usage

Delete certain sheet by name.

Input

VLOBJ

Excel session vla-object

 

STRING

Sheet name to delete

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-delete (xlapp Name / sh Rtn)

  (setq Rtn (vlxls-sheet-get-all Xlapp))

  (vlax-for sh (vlax-get-property Xlapp "sheets")

    (if    (= (vlax-get-property sh "Name") Name)

      (vlax-invoke-method sh "Delete")

    )

  )

  (not (equal Rtn (vlxls-sheet-get-all Xlapp)))

)

Examples:

(vlxls-sheet-delete *xlapp* “Sheet1”)  ==>  T

(vlxls-sheet-delete *xlapp* “UnExistingSheet”)  ==>  NIL

 

Excel Sheet Progress Function

Name

(vlxls-sheet-rename NewSheetName OldSheetName ExcelSessionVLA-OBJECT)

Usage

Rename certain sheet by name.

Input

STRING

New sheet name string

STRING

Old sheet name string

VLOBJ

Excel session vla-object

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-rename (New Old Xlapp / sh Rtn)

  (if (null old)

    (setq old (msxl-get-name (msxl-get-activesheet Xlapp)))

  )

  (if (member New (vlxls-sheet-get-all Xlapp))

    (setq Rtn nil)

    (progn

      (vlax-for     sh (vlax-get-property Xlapp "sheets")

       (if (= (msxl-get-name sh) Old)

         (msxl-put-name sh New)

       )

      )

      (setq Rtn

            (equal New

                  (vlax-get-property (msxl-get-ActiveSheet Xlapp) 'name)

            )

      )

    )

  )

  Rtn

)

Examples:

(vlxls-sheet-rename “New” “Sheet1” *xlapp*)  ==>  T

(vlxls-sheet-rename “New” NIL *xlapp*)  ==>  T

(vlxls-sheet-rename “Sheet3” NIL *xlapp*)  ==>  NIL

(vlxls-sheet-rename “Sheet2” “Sheet1” *xlapp*)  ==>  NIL

(vlxls-sheet-rename “Sheet2” “UnExistSheet” *xlapp*)  ==>  NIL

 

Excel Sheet Progress Function

Name

(vlxls-sheet-add ExcelSessionVLA-OBJECT NewSheetName)

Usage

New sheet name. If sheet name exist, return NIL

Input

VLOBJ

Excel session vla-object

STRING

New added sheet name string

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-add (xlapp Name / Rtn)

  (if (member name (vlxls-sheet-get-all xlapp))

    (setq Rtn nil)

    (progn

      (vlax-put-property

       (vlax-invoke-method

         (vlax-get-property Xlapp "sheets")

         "Add"

       )

       "name"

       Name

      )

      (setq Rtn (equal (vlxls-sheet-get-active xlapp) name))

    )

  )

  Rtn

)

Examples:

(vlxls-sheet-add *xlapp* “Sheet1”)  ==> T

(vlxls-sheet-add *xlapp* NIL)  ==> T

(vlxls-sheet-add *xlapp* “NewSheet”)  ==>  NIL

 

Excel Sheet Progress Function

Name

(vlxls-sheet-put-active ExcelSessionVLA-OBJECT ActiveSheetName)

Usage

Put certain sheet as active sheet. If sheet name not exist, create automatically.

Input

VLOBJ

Excel session vla-object

STRING

New active sheet name string

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-put-active (xlapp Name / sh)

  (if (null (vlxls-sheet-add xlapp name))

    (vlax-for sh   (vlax-get-property Xlapp "sheets")

      (if (= (vlax-get-property sh "Name") Name)

       (vlax-invoke-method sh "Activate")

      )

    )

  )

  (equal (vlxls-sheet-get-active xlapp) name)

)

Examples:

(vlxls-sheet-put-active *xlapp* “Sheet1”)  ==>  T

(vlxls-sheet-put-active *xlapp* “NewSheet”)  ==>  T

 

Excel Sheet Progress Function

Name

(vlxls-sheet-get-usedrange ExcelSessionVLA-OBJECT SheetName)

Usage

Get all used range of certain Excel sheet. If sheet name not exist, return NIL.

Input

VLOBJ

Excel session vla-object

STRING

Excel sheet name string, NIL for current active sheet.

RetVal

True

VLOBJ

Excel Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-sheet-get-UsedRange (xlapp Name / sh Rtn)

  (if (null Name)

    (setq Name (vlax-get-property (msxl-get-ActiveSheet Xlapp) 'Name))

  )

  (vlax-for sh (vlax-get-property Xlapp "sheets")

    (if    (= (vlax-get-property sh "Name") Name)

      (setq Rtn (vlax-get-property sh "UsedRange"))

    )

  )

  Rtn

)

Examples:

(vlxls-sheet-get-usedrange *xlapp* “Sheet1”)  ==>  T

(vlxls-sheet- get-usedrange *xlapp* “NewSheet”)  ==>  T

 

Excel Cell and Range Progress Function

Name

(vlxls-cellid CellIDStringOrList)

Usage

Divide complex Excel Cell ID into a two-string-item list, contain the Left-Upper and Right-Lower Cell ID.
If only one Cell ID is provided, set the Right-Lower Cell ID to “”.

Input

STR/LIST

Complex Excel Cell ID string or simple Cell ID string/list.

RetVal

True

LIST

List of Left-Upper and Right-Lower Cell ID

Fail

BOOLEAN

NIL

(Defun vlxls-cellid (id / xx id1 id2 Rtn)

  (if (= (type id) 'list)

    (setq id (vlxls-rangeid id))

  )

  (setq id (strcase id))

  (if (null (setq xx (vl-string-search ":" id)))

    (setq Rtn (list id ""))

    (setq id1 (substr id 1 xx)

         id2 (substr id (+ xx 2))

         id1 (vlxls-rangeid id1)

         id2 (vlxls-rangeid id2)

         Rtn (list (vlxls-rangeid

                    (list (min (car id1) (car id2))

                         (min (cadr id1) (cadr id2))

                    )

                  )

                  (vlxls-rangeid

                    (list (max (car id1) (car id2))

                         (max (cadr id1) (cadr id2))

                    )

                  )

             )

    )

  )

  Rtn

)

Examples:

(vlxls-cellid ‘(3 14))  ==>  ("C14" "")

(vlxls-cellid “D23”)  ==>  ("D23" "")

(vlxls-cellid “C12:F3”)  ==>  ("C3" "F12")

(vlxls-cellid “F15:G22”)  ==>  ("F15" "G22")

 

Excel Cell and Range Progress Function

Name

(vlxls-rangeid CellIDStringOrList)

Usage

VLXLS treats Excel Cell ID in two types: AutoCAD LIST and Excel simple Cell ID String. This function is used to convert Cell ID between the two types.

Input

STR/LIST

The Cell ID list or string

RetVal

True

STR/LIST

Cell ID value in another VLXLS ID type

Fail

BOOLEAN

NIL

(Defun vlxls-rangeid (id / str->list list->str xid->str Rtn)

  (Defun str->list (str / ii xk xv rr pos x y)

    (setq rr (strlen str))

    (foreach ii     '("0" "1" "2" "3" "4" "5" "6" "7" "8" "9")

      (if (setq pos (vl-string-search ii str))

       (setq rr (min pos rr))

      )

    )

    (setq x (substr str 1 rr)

         y (substr str (1+ rr))

    )

    (if    (= (strlen x) 2)

      (setq xk (- (ascii (substr x 1 1)) 64)

           xv (- (ascii (substr x 2)) 64)

      )

      (setq xk 0

           xv (- (ascii x) 64)

      )

    )

    (list (+ (* xk 26) xv) (read y))

  )

  (Defun xid->str (IntNum / PosNum Nm-One)

    (setq Nm-One (1- IntNum)

         PosNum (/ Nm-One 26)

    )

    (if    (= PosNum 0)

      (chr (+ 65 (rem Nm-One 26)))

      (strcat (chr (+ 64 PosNum)) (chr (+ 65 (rem Nm-One 26))))

    )

  )

  (Defun list->str (idr / x y)

    (setq x (car idr)

         y (cadr idr)

         x (xid->str x)

         y (itoa y)

    )

    (strcat x y)

  )

  (cond  ((= (type id) 'str) (setq Rtn (str->list id)))

       ((= (type id) 'list) (setq Rtn (list->str id)))

  )

  Rtn

)

Examples:

(vlxls-rangeid ‘(3 14))  ==>  "C14"

(vlxls-rangeid “D23”)  ==>  (4 23)

(vlxls-rangeid “DD23”)  ==>  (108 23)

 

Excel Cell and Range Progress Function

Name

(vlxls-range-autofit RangeVLA_OBJECT)

Usage

Autofit the column width of a certain range object.

Input

VLOBJ

The Excel Range vla-object

RetVal

True

BOOLEAN

T

Fail

BOOLEAN

NIL

(Defun vlxls-range-autofit (range)

  (equal (vlax-variant-value

          (msxl-autofit

            (msxl-get-columns (msxl-get-Cells range))

          )

        )

        :vlax-true

  )

)

Examples:

(vlxls-range-autofit (msxl-get-range *xlapp* “C12:F15”))  ==>  T

(vlxls-range-autofit RangeObject)  ==>  NIL

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-put-active ExcelSessionVLA-OBJECT CellIDStringOrList)

Usage

Select to certain Cell ID and activate it.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

RetVal

True

VLOBJ

Active Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-cell-put-active (xl id / Rtn)

  (if (= (type id) 'list)

    (setq id (vlxls-rangeid id))

  )

  (msxl-activate (setq Rtn (msxl-get-range xl id)))

  Rtn

)

Examples:

(vlxls-cell-put-active *xlapp* “C12:F15”)  ==>  #<VLA-OBJECT Range 09d1998c>

(vlxls-cell-put-active *xlapp* “F12”)  ==>  #<VLA-OBJECT Range 06c389a2>

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-get-value ExcelSessionVLA-OBJECT CellIDStringOrList)

Usage

Get value of certain Cell ID.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

RetVal

True

STR/LIST

String for one cell, a 2 dimension list for multiple cells or merged cell

Fail

BOOLEAN

NIL

(Defun vlxls-cell-get-value (xl id)

  (if (= (type id) 'list)

    (setq id (vlxls-rangeid id))

  )

  (vlxls-variant->list

    (msxl-get-value2 (msxl-get-range xl id))

  )

)

Examples:

(vlxls-cell-get-value *xlapp* “C12”)  ==>  “g”

(vlxls-cell-get-value *xlapp* “C12:C12”)  ==>  “g”

(vlxls-cell-get-value *xlapp* “C12:C15”)  ==>  (("g") ("") ("") (""))

(vlxls-cell-get-value *xlapp* “C12:F12”)  ==>  (("g" "ds" "" ""))

(vlxls-cell-get-value *xlapp* “C12:F15”)  ==>  (("g" "ds" "" "") ("" "" "g" "") ("" "" "" "") ("" "" "" ""))

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-put-value ExcelSessionVLA-OBJECT CellIDStringOrList DataList)

Usage

Pass a 1 dimension or a 2 dimension string list into Excel, started at certain Cell ID.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The start Cell ID [Left-Upper] list or string

STR/LIST

If this argument is a string, VLXLS will fill same string to all cells.

Or the argument should be a 1 dimension list or a 2 dimension list to fill in Excel. If the data list can NOT match the given cell ID, VLXLS will only fill first cell, fill to other cells will be ignored.

RetVal

True

VLOBJ

All Excel Range vla-object that just be filled in by given data list

Fail

BOOLEAN

NIL

(Defun vlxls-cell-put-value

       (xl id Data / vllist-explode idx xx yy ary Rtn)

  (Defun vllist-explode      (lst)

    (cond

      ((not lst) nil)

      ((atom lst) (list lst))

      ((append (vllist-explode (car lst))

              (vllist-explode (cdr lst))

       )

      )

    )

  )

  (if (null id)

    (setq id "A1")

  )

  (if (= (type id) 'list)

    (setq id (vlxls-rangeid id))

  )

  (if (= (type (car Data)) 'LIST)

    (setq ARY (vlax-make-safearray

              vlax-vbstring

              (cons 0 (1- (length Data)))

              (cons 1 (length (car Data)))

             )

         XX  (1- (length (car Data)))

         YY  (1- (length Data))

    )

    (setq

      ARY (vlax-make-safearray

           vlax-vbstring

           (cons 0 1)

           (cons 1 (length Data))

         )

      XX  (1- (length Data))

      YY  0

    )

  )

  (if (= xx yy 0)

    (msxl-put-value2

      (setq Rtn (msxl-get-range xl id))

      (car (vllist-explode data))

    )

    (progn

      (setq id (vlxls-cellid-calc id xx yy))

      (msxl-put-value2

       (setq Rtn (msxl-get-range xl id))

       (vlax-safearray-fill ary data)

      )

    )

  )

  Rtn

)

Examples:

(vlxls-cell-put-value *xlapp* “C12” “xx”)  ==>  #<VLA-OBJECT Range 093a7764>

(vlxls-cell-put-value *xlapp* “C12:F3” “xx”)  ==>  #<VLA-OBJECT Range 43c5ac64>

(vlxls-cell-put-value *xlapp* “C12:D13” ‘((“zz” “xx”)(“xx” “zz”)))  ==>  #<VLA-OBJECT Range 1b8f2a64>

 

Excel Cell and Range Progress Function

Name

(vlxls-cellid-calc BaseCellId XOffset YOffset)

Usage

Calculate a new Cell ID for given delta X and Y from base Cell ID.

Input

STR/LIST

Base Cell ID string or list

INT

X offset integer of Cell ID

INT

Y offset integer of Cell ID

RetVal

True

STRING

An Excel Complex Cell ID format contain the base Cell ID and target Cell ID.

Fail

BOOLEAN

NIL

(Defun vlxls-cellid-calc (id x y / idx)

  (setq   id  (car (vlxls-cellid id))

       idx (vlxls-rangeid id)

       x   (+ x (car idx))

       x   (if    (< x 1)

             1

             x

           )

       y   (+ y (cadr idx))

       y   (if    (< y 1)

             1

             y

           )

       idx (vlxls-rangeid (list x y))

       id  (vlxls-cellid (strcat id ":" idx))

       id  (strcat (car id) ":" (cadr id))

  )

  id

)

Examples:

(vlxls-cellid-calc “C12” 2 20)  ==>  "C12:E32"

(vlxls-cellid-calc ‘(2 23) 2 -120)  ==>  "B1:D23"

 

Excel Cell and Range Progress Function

Name

(vlxls-get-row-value ExcelSessionVLA-OBJECT StartCellIDStringOrList RowCellNumber)

Usage

Get values of certain row.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Start Cell ID list or string

INT

Number of cells in row to read.

RetVal

True

LIST

A list contain cells' value in row

Fail

BOOLEAN

NIL

(Defun vlxls-get-row-value (xl id len / vllist-explode Rtn)

  (Defun vllist-explode      (lst)

    (cond

      ((not lst) nil)

      ((atom lst) (list lst))

      ((append (vllist-explode (car lst))

              (vllist-explode (cdr lst))

       )

      )

    )

  )

  (if (> len 0)

    (setq id (vlxls-cellid-calc id (1- len) 0))

    (setq id (vlxls-cellid-calc id (1+ len) 0))

  )

  (setq Rtn (vllist-explode (vlxls-cell-get-value xl id)))

  Rtn

)

Examples:

(vlxls-get-row-value *xlapp* “C12” 2)  ==>  ("zz" "xxx")

(vlxls-get-row-value *xlapp* “C12” -20)  ==>  ("" "" "zz")

 

Excel Cell and Range Progress Function

Name

(vlxls-put-row-value ExcelSessionVLA-OBJECT StartCellIDStringOrList StringList)

Usage

Put a string list into Excel row started by certain cell.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Start Cell ID list or string

STR/LIST

A string to fill in one cell or a 1 dimension string list to fill in row cells.

RetVal

True

VLOBJ

Filled Excel Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-put-row-value (xl id data / Rtn)

  (if (= (type data) 'str)

    (setq data (list data))

  )

  (setq   id (car (vlxls-cellid id))

       id (vlxls-cellid-calc id (1- (length data)) 0)

  )

  (vlxls-range-autofit

    (setq Rtn (vlxls-cell-put-value xl id (list data)))

  )

  Rtn

)

Examples:

(vlxls-put-row-value *xlapp* “C12” “abc”)  ==> #<VLA-OBJECT Range 2a621cac>

(vlxls-put-row-value *xlapp* ‘(12 3) “abc”)  ==> #<VLA-OBJECT Range 7a36c491>

(vlxls-put-row-value *xlapp* “C12” ‘("zz" "xxx"))  ==> #<VLA-OBJECT Range 09d1da1c>

(vlxls-put-row-value *xlapp* ‘(12 3) ‘("zz" "xxx"))  ==> #<VLA-OBJECT Range 0a26c4f3>

 

Excel Cell and Range Progress Function

Name

(vlxls-get-column-value ExcelSessionVLA-OBJECT StartCellIDStringOrList ColumnCellNumber)

Usage

Get values of certain column.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Start Cell ID list or string

INT

Number of cells in column to read.

RetVal

True

LIST

A list contain cells' value in column

Fail

BOOLEAN

NIL

(Defun vlxls-get-column-value (xl id len / vllist-explode Rtn)

  (Defun vllist-explode      (lst)

    (cond

      ((not lst) nil)

      ((atom lst) (list lst))

      ((append (vllist-explode (car lst))

              (vllist-explode (cdr lst))

       )

      )

    )

  )

  (setq id (car (vlxls-cellid id)))

  (if (> len 0)

    (setq id (vlxls-cellid-calc id 0 (1- len)))

    (setq id (vlxls-cellid-calc id 0 (1+ len)))

  )

  (setq Rtn (vllist-explode (vlxls-cell-get-value xl id)))

  Rtn

)

Examples:

(vlxls-get-column-value *xlapp* “C12” 2)  ==>  ("zz" "sdfsdf")

(vlxls-get-column-value *xlapp* “C12” -20)  ==>  ("" "" "xxx" "xxx" "xxx" "xxx" "xxx" "xxx" "xxx" "xxx" "xxx" "zz")

 

Excel Cell and Range Progress Function

Name

(vlxls-put-column-value ExcelSessionVLA-OBJECT StartCellIDStringOrList StringList)

Usage

Put a string list into Excel column started by certain cell.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Start Cell ID list or string

STR/LIST

A string to fill in one cell or a 1 dimension string list to fill in column cells.

RetVal

True

VLOBJ

Filled Excel Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-put-column-value (xl id data / item Rtn)

  (if (= (type data) 'str)

    (setq data (list data))

  )

(setq   id (car (vlxls-cellid id))

       id (vlxls-cellid-calc id 0 (1- (length data)))

  )

  (foreach item    data

    (setq Rtn (cons (list item) Rtn))

  )

  (vlxls-range-autofit

    (setq Rtn (vlxls-cell-put-value xl id (reverse Rtn)))

  )

  Rtn

)

Examples:

(vlxls-put-column-value *xlapp* “C12” “abc”)  ==> #<VLA-OBJECT Range 049c521b>

(vlxls-put-column-value *xlapp* ‘(12 3) “abc”)  ==> #<VLA-OBJECT Range 0235cba1>

(vlxls-put-column-value *xlapp* “C12” ‘("zz" "xxx"))  ==> #<VLA-OBJECT Range 09d1da1c>

(vlxls-put-column-value *xlapp* ‘(12 3) ‘("zz" "xxx"))  ==> #<VLA-OBJECT Range 0a26c4f3>

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-get-aci ExcelSessionVLA-OBJECT CellIDStringOrList)

Usage

Get the background color (In AutoCAD ColorIndex mode) of certain Excel cell, Multiple color will return 256.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

RetVal

True

INT

Valid ACI Integer number (0 to 256)

Fail

BOOLEAN

NIL

(Defun vlxls-cell-get-aci (xl id)

  (vlxls-color-eci->aci

    (vlax-variant-value

      (msxl-get-colorindex

       (msxl-get-interior (msxl-get-range xl id))

      )

    )

  )

)

Examples:

(vlxls-cell-get-aci *xlapp* “C12”)  ==> 256

(vlxls-cell-get-aci *xlapp* ‘(12 3))  ==> 15

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-put-aci ExcelSessionVLA-OBJECT CellIDStringOrList ACINumber)

Usage

Put or clear the background color (In AutoCAD ColorIndex mode) of certain Excel cells.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

INT

ACI Integer number, NIL for remove background color

RetVal

True

VLOBJ

Modified Excel Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-cell-put-aci (xl id aci / Rtn)

  (if (null aci)

    (msxl-put-colorindex

      (msxl-get-interior (setq Rtn (msxl-get-range xl id)))

      (vlax-make-variant -4142)

    )

    (msxl-put-colorindex

      (msxl-get-interior (setq Rtn (msxl-get-range xl id)))

      (vlxls-color-aci->eci aci)

    )

  )

  Rtn

)

Examples:

(vlxls-cell-put-aci *xlapp* “C12” 6)  ==> #<VLA-OBJECT Range 09d1369c>

(vlxls-cell-put-aci *xlapp* “C12” nil)  ==> #<VLA-OBJECT Range 09d1369c>

 

Excel Cell and Range Progress Function

Name

(vlxls-text-get-aci ExcelSessionVLA-OBJECT CellIDStringOrList)

Usage

Get the text color (In AutoCAD ColorIndex mode) of certain Excel cells.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

RetVal

True

INT

Valid ACI Integer number (0 to 256)

Fail

BOOLEAN

NIL

(Defun vlxls-text-get-aci (xl id)

  (vlxls-color-eci->aci

    (vlax-variant-value

      (msxl-get-colorindex

       (msxl-get-font (msxl-get-range xl id))

      )

    )

)

Rtn

)

Examples:

(vlxls-text-get-aci *xlapp* “C12”)  ==> 256

(vlxls-text-get-aci *xlapp* ‘(12 3))  ==> 15

 

Excel Cell and Range Progress Function

Name

(vlxls-text-put-aci ExcelSessionVLA-OBJECT CellIDStringOrList ACINumber)

Usage

Put or clear the content color (In AutoCAD ColorIndex mode) of certain Excel cells.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

INT

ACI Integer number, NIL for remove background color

RetVal

True

VLOBJ

Modified Excel Range vla-object

Fail

BOOLEAN

NIL

(Defun vlxls-text-put-aci (xl id aci / Rtn)

  (if (null aci)

    (msxl-put-colorindex

      (msxl-get-font (setq Rtn (msxl-get-range xl id)))

      (vlax-make-variant -4105)

    )

    (msxl-put-colorindex

      (msxl-get-font (setq Rtn (msxl-get-range xl id)))

      (vlxls-color-aci->eci aci)

    )

  )

  Rtn

)

Examples:

(vlxls-text-put-aci *xlapp* “C12” 6)  ==> #<VLA-OBJECT Range 09d1369c>

(vlxls-text-put-aci *xlapp* “C12” nil)  ==> #<VLA-OBJECT Range 09d1369c>

 

Excel Cell and Range Progress Function

Name

(vlxls-text-get-prop ExcelSessionVLA-OBJECT CellIDStringOrList)

Usage

Get the properties of content of certain Excel cells. Multiple cells will only record the Left-Upper cell.

Input

VLOBJ

The Excel Session vla-object

STR/LIST

The Cell ID list or string

RetVal

True

LIST

A dot-paired list contain text properties. Syntax is as following:

((0 . LeftUpperCellID)(7 . FontStyle)(40 . FontSize)(62 . TextACIColor)(72 . TextAlignment)(420 . TextTrueColor))

FontStyle will be recorded as Windows TTF font name displayed in Excel

VLXLS only support horizontal for TextAlignment: 9=Left, 10=Center, 11=Right

Fail

BOOLEAN

NIL

(Defun vlxls-text-get-prop

       (xl id / Cell Font DXF1 DXF7 DXF40 DXF72 DXF62 DXF420 Rtn)

  (setq   id     (car (vlxls-cellid id))

       cell   (msxl-get-range xl id)

       font   (msxl-get-font cell)

       DXF7   (vlax-variant-value (msxl-get-name Font))

       DXF40  (vlax-variant-value (msxl-get-size Font))

       DXF72  (vlax-variant-value

               (msxl-get-HorizontalAlignment Cell)

              )

       DXF72  (cond ((= DXF72 -4152) 11)

                   ((= DXF72 -4108) 10)

                   (t 9)

              )

       DXF62  (vlxls-color-eci->aci

               (vlax-variant-value (msxl-get-colorIndex Font))

              )

       DXF420 (vlxls-color-eci->truecolor

               (vlax-variant-value (msxl-get-colorIndex Font))

              )

       Rtn    (list (cons 0 (strcase id))

                   (cons 7 DXF7)

                   (cons 40 DXF40)

                   (cons 62 DXF62)

                   (cons 72 DXF72)

                   (cons 420 DXF420)

              )

  )

  Rtn

)

Examples:

(vlxls-text-get-prop *xlapp* “C12”)  ==> ((0 . "C12") (7 . "Arial") (40 . 12.0) (62 . 256) (72 . 9) (420 . 16711935))

(vlxls-text-get-prop *xlapp* ‘(2 10))  ==> ((0 . "B10") (7 . "Arial") (40 . 12.0) (62 . 256) (72 . 11) (420 . 16711935))

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-get-prop ExcelSessionVLA-OBJECT CellIDString)

Usage

Get the properties of certain Excel cells.

Input

VLOBJ

The Excel Session vla-object

STR

The Cell ID string

RetVal

True

LIST

A dot-paired list contain cell properties. Syntax is as following:

((0 . CellIDString)(1 . CellValueList) (10 . LeftUpperLocation_of_LeftUpperCell) (41 . TotalColumnWidth) (42 . TotalRowHeight) (-1 . ReturnValue_of_vlxls-text-get-prop))

If only one cell, CellValueList can be a string, or it will be a 2 dimension list.

LeftUpperLocation_of_LeftUpperCell is in Excel units and Cell “A1” will be original.

TotalRowHeight and TotalColumnWidth are both in Excel units

Fail

BOOLEAN

NIL

(Defun vlxls-cell-get-prop

       (xl id / range left top width height dxf10 Rtn)

  (if (vlxls-cell-merge-p xl id)

    (setq id (vlxls-cell-get-mergeid xl id))

  )

  (setq   range  (msxl-get-range xl id)

       left   (vlax-variant-value (msxl-get-left Range))

       top    (vlax-variant-value (msxl-get-top Range))

       width  (vlax-variant-value (msxl-get-width Range))

       height (vlax-variant-value (msxl-get-height Range))

       dxf10  (list left top)

       Rtn    (list (cons 0 (strcase id))

                   (cons 1 (vlxls-cell-get-value xl id))

                   (cons 10 dxf10)

                   (cons 41 width)

                   (cons 42 height)

                   (cons -1 (vlxls-text-get-prop xl id))

              )

  )

  Rtn

)

Examples:

(vlxls-cell-get-prop *xlapp* “C12:F14”)  ==> ((0 . "C12:F14") (1 ("zz" "xxx" "xxx" "xxx") ("sdfsdf" "sdfsdf" "sdfsdf" "sdfsdf") ("sdfsdf" "sdfsdf" "sdfsdf" "sdfsdf")) (10 108.0 156.75) (41 . 156.0) (42 . 42.75) (-1 (0 . "C12") (7 . "Arial") (40 . 12.0) (62 . 256) (72 . 9) (420 . 16711935)))

(vlxls-cell-get-prop *xlapp* “B8”)  ==> ((0 . "B8") (1 . "sdg") (10 54.0 99.75) (41 . 54.0) (42 . 14.25) (-1 (0 . "B8") (7 . "Arial") (40 . 12.0) (62 . 256) (72 . 10) (420 . 16711935)))

 

Excel Cell and Range Progress Function

Name

(vlxls-cell-border ExcelSessionVLA-OBJECT CellIDString)

Usage

Force to draw or hide 4 slim border to certain Excel cells.