Jump to content
Mickey the Gr8

adjusting excel columns from autolisp

Recommended Posts

Mickey the Gr8

I am creating an excel worksheet from autolisp to populate with all of my fuse information from autocad electrical 2011. Everything works fine except that I cannot figure out how to change the width of the columns in the excel spreadsheet from autolisp. Any assistance would be greatly appreciated.

This is what I have on the spreadsheet:

 (setq xl (vlax-create-object "excel.application"))
 (setq wb-collection (vlax-get xl "workbooks"))
 (setq workbook (vlax-invoke-method wb-collection "add"))
 (setq sheets (vlax-get workbook "sheets"))
 (setq sheet1 (vlax-get-property sheets "item" 1))
 (setq mywb(vlax-get-property xl "ActiveWorkbook"))
 (setq *excel-cells* (vlax-get sheet1 "cells"))
 (vlax-put xl "visible" 1)

 

;;****************insert header info into spreadsheet**********************
 (setq fuse_cell (strcat "A" (itoa in)))
 (setq cat_cell (strcat "B" (itoa in)))
 (setq mfg_cell (strcat "C" (itoa in)))
 (setq rating1_cell (strcat "D" (itoa in)))
 (setq class_cell (strcat "E" (itoa in)))
 (setq a (vlax-get-property xl 'Range fuse_cell))
 (setq b (vlax-get-property xl 'Range cat_cell))
 (setq c (vlax-get-property xl 'Range mfg_Cell))
 (setq d (vlax-get-property xl 'Range rating1_cell))
 (setq e (vlax-get-property xl 'Range class_cell))
 (vlax-put-property a 'Value2 "FUSE")
 (vlax-put-property b 'Value2 "PART NUMBER")
 (vlax-put-property c 'Value2 "MANUFACTURER")
 (vlax-put-property d 'Value2 "RATING")
 (vlax-put-property E 'Value2 "CLASS")
 (setq in 2); sets row in spreadsheet
 
 ;*****************************populate excel sheet****************************************
 (repeat (length fuse_name)
  (setq fuse_cell (strcat "A" (itoa in)))
  (setq cat_cell (strcat "B" (itoa in)))
  (setq mfg_cell (strcat "C" (itoa in)))
  (setq rating1_cell (strcat "D" (itoa in)))
  (setq class_cell (strcat "E" (itoa in)))
  (setq a (vlax-get-property xl 'Range fuse_cell))
  (setq b (vlax-get-property xl 'Range cat_cell))
  (setq c (vlax-get-property xl 'Range mfg_Cell))
  (setq d (vlax-get-property xl 'Range rating1_cell))
  (setq e (vlax-get-property xl 'Range class_cell))
  (vlax-put-property a 'Value2 (nth in_lst fuse_name))
  (vlax-put-property b 'Value2 (nth in_lst fuse_Cat))
  (vlax-put-property c 'Value2 (nth in_lst fuse_mfg))
  (vlax-put-property d 'Value2 (nth in_lst fuse_rating1))
  (vlax-put-property e 'Value2 (nth in_lst fuse_class))
  (setq in_lst (1+ in_lst))(princ)
  (setq in (1+ in))(princ)
 )

Share this post


Link to post
Share on other sites
Grrr

Heres a simple example to change the width of the third column:

(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(vlax-put-property xlapp 'Visible :vlax-true)
(setq xlwbks (vlax-get-property xlapp 'WorkBooks))
(setq xlwbk (vlax-invoke-method xlwbks 'Add))
(setq xlshts (vlax-get-property xlwbk 'Sheets))
(setq xlsht (vlax-get-property xlshts 'Item 1))
(setq xlcols (vlax-get-property xlsht 'Columns))
(setq ThirdColumn (vlax-get-property (vlax-variant-value (vlax-get-property xlcols 'Item 3)) 'EntireColumn))
(vlax-put-property ThirdColumn 'ColumnWidth 16)

FWIW To change the width of all columns in that worksheet:

(vlax-put-property xlcols 'ColumnWidth 16)

But perhaps I assume you'd want to invoke the AutoFit method:

(vlax-invoke-method xlcols 'AutoFit)

 


And to release the excel objects, and reset to nil the assigned symbols from the above manual test:

(foreach x '(xlapp xlwbks xlwbk xlshts xlsht xlcols ThirdColumn)
  (
    (lambda (x)
      (and (eq 'VLA-OBJECT (type x)) (vl-catch-all-apply (function vlax-release-object) (list x)))
    )
    (eval x)
  )
  (set x nil)
)

 

Edited by Grrr

Share this post


Link to post
Share on other sites
Mickey the Gr8

Thank you very much.

There is not a lot of info out there on manipulating excel through autolisp.

Your help is appreciated.

Thanks a lot.

Share this post


Link to post
Share on other sites
Lee Mac
11 minutes ago, Mickey the Gr8 said:

There is not a lot of info out there on manipulating excel through autolisp.

 

If you're interested in manipulating MS Excel using Visual LISP, this should be your bible.

 

The reference is obviously VBA, but the translation from VBA to Visual LISP is relatively straightforward.

 

Lee

Share this post


Link to post
Share on other sites
Grrr

No problem,

The concept is the same as in any other language - you investigate the COM hierarchy of the excel application to become familiar.

And then you just access the available objects/properties/methods required for your work.

Just google for excel+lisp to get some examples (Lee Mac's are the best ones)

 


One thing though - with LISP you cannot assign events, while they are contained in the VB/.NET languages.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...