Hello,
I have a program that generates a master list of lists.
I'm looking to export the list to an excel file named CODE ANALYSIS on sheet DATA line 20
the list looks like this
((1_2 50.0 10.0 10.0) (2_3 50.0 20.0 20.0) (3_4 50.0 20.0 20.0) (4_5 50.0 10.0 10.0))
something like this:
I've seen others but they are way more complex than this and I'm having a hard time "reverse engineering" the code found. As you all know my lisp skills are limited.
Thanks.
Hi,
Here's a quite old one (but I hope still relevant) which seems to reply to your request.
;;-------------------------------------------------------------------------------
;; gc:WriteExcel
;; Writes in an Excel file
;;
;; Arguments : 4
;; filename : complete path of the file
;; sheet : name of the sheet (or nil for the current sheet)
;; startRange : name of the start cell (or nil for "A1")
;; dataList : list of sub-lists containing the data (one sub-list per row)
;;-------------------------------------------------------------------------------
(defun gc:WriteExcel (filename sheet startRange dataList / *error* xlApp
wBook save sheets active start row col cells
n cell
)
(vl-load-com)
(defun *error* (msg)
(and msg
(/= msg "Function cancelled")
(princ (strcat "\nError: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(and reg (vlax-release-object reg))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list cell cells wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(if (findfile filename)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Open filename)
save T
)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Add))
)
(if sheet
(progn
(setq sheets (vlax-get-property xlApp 'Sheets))
(vlax-for s sheets
(if (= (strcase (vlax-get-property s 'Name)) (strcase sheet))
(progn
(vlax-invoke-method s 'Activate)
(setq active T)
)
)
)
(or active
(vlax-put-property (vlax-invoke-method sheets 'Add) 'Name sheet)
)
)
)
(if startRange
(setq start (gc:ColumnRow startRange)
col (car start)
row (cadr start)
)
(setq col 1
row 1
)
)
(setq cells (vlax-get-property xlApp 'Cells))
(or startRange (vlax-invoke-method cells 'Clear))
(foreach sub dataList
(setq n col)
(foreach data sub
(setq cell (vlax-variant-value (vlax-get-property cells 'Item row n)))
(if (= (type data) 'STR)
(vlax-put-property cell 'NumberFormat "@")
)
(vlax-put-property cell 'Value2 data)
(setq n (1+ n))
)
(setq row (1+ row))
)
(vlax-invoke-method
(vlax-get-property
(vlax-get-property xlApp 'ActiveSheet)
'Columns
)
'AutoFit
)
(if save
(vlax-invoke-method wBook 'Save)
(if (and
(< "11.0" (vlax-get-property xlapp "Version"))
(= (strcase (vl-filename-extension filename) T) ".xlsx")
)
(vlax-invoke-method wBook 'SaveAs filename 51 "" "" :vlax-false :vlax-false 1 1)
(vlax-invoke-method wBook 'SaveAs filename -4143 "" "" :vlax-false :vlax-false 1 1)
)
)
(*error* nil)
)
;;-------------------------------------------------------------------------------
;; gc:ReadExcel
;; Returns a list of sub-lists containing the data of an Excel file (one sub-list per row)
;;
;; Arguments : 4
;; filename : complete path of the file
;; sheet : name of the sheet (or nil for the current sheet)
;; startRange : name of the start cell (or nil for "A1")
;; maxRange : name of the cell where the reading have to stop,
;; or "*" for the whole sheet, or nil or "" for the current range
;;-------------------------------------------------------------------------------
(defun gc:ReadExcel (filename sheet startRange maxRange / *error*
xlApp wBook wSheet startCell startCol startRow maxCell maxCol
maxRow reg cells col row data sub
lst
)
(defun *error* (msg)
(and msg
(/= msg "Fonction annulée")
(princ (strcat "\nErreur: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list reg cells wSheet wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application")
wBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) 'Open filename)
)
(if sheet
(vlax-for ws (vlax-get-property xlapp 'Sheets)
(if (= (vlax-get-property ws 'Name) sheet)
(vlax-invoke-method (setq wSheet ws) 'Activate)
)
)
(setq wSheet (vlax-get-property wBook 'ActiveSheet))
)
(if startRange
(setq startCell (gc:ColumnRow startRange)
startCol (car startCell)
startRow (cadr startCell)
)
(setq startRange
"A1"
startCol 1
startRow 1
)
)
(if (and maxRange (setq maxCell (gc:ColumnRow maxRange)))
(setq maxCol (1+ (car MaxCell))
maxRow (1+ (cadr MaxCell))
)
(setq reg (if (= maxRange "*")
(vlax-get-property wSheet 'UsedRange)
(vlax-get-property
(vlax-get-property wSheet 'Range startRange)
'CurrentRegion
)
)
maxRow (+ (vlax-get-property reg 'Row)
(vlax-get-property (vlax-get-property reg 'Rows) 'Count)
)
maxCol (+ (vlax-get-property reg 'Column)
(vlax-get-property (vlax-get-property reg 'Columns) 'Count)
)
)
)
(setq cells (vlax-get-property xlApp 'Cells)
row maxRow
)
(while (< startRow row)
(setq sub nil
col maxCol
row (1- row)
)
(while (< startCol col)
(setq col (1- col)
sub (cons
(vlax-variant-value
(vlax-get-Property
(vlax-variant-value (vlax-get-property cells 'Item row col))
'Value2
)
)
sub
)
)
)
(setq lst (cons sub lst))
)
(*error* nil)
lst
)
;;-------------------------------------------------------------------------------
;; gc:ColumnRow
;; Returns a list of the column row indices
;; Argument: 1
;; cell = name of the cell
;; Using example : (gc:ColumnRow "IV987") -> (256 987)
;;-------------------------------------------------------------------------------
(defun gc:ColumnRow (cell / col char row)
(setq col "")
(while (< 64 (ascii (setq char (strcase (substr cell 1 1)))) 91)
(setq col (strcat col char)
cell (substr cell 2)
)
)
(if (and (/= col "") (numberp (setq row (read Cell))))
(list (gc:Alpha2Number col) row)
)
)
;;-------------------------------------------------------------------------------
;; gc:Alpha2Number
;; Converts a string into an integer
;; Arguments: 1
;; str = string to convert
;; Using example : (gc:Alpha2Number "BU") = 73
;;-------------------------------------------------------------------------------
(defun gc:Alpha2Number (str / num)
(if (= 0 (setq num (strlen str)))
0
(+ (* (- (ascii (strcase (substr str 1 1))) 64)
(expt 26 (1- num))
)
(gc:Alpha2Number (substr str 2))
)
)
)
;;-------------------------------------------------------------------------------
;; gc:Number2Alpha - Convertit un nombre entier en chaîne alphabétique
;; Converts an integer into a string
;; Arguments: 1
;; num = integer to convert
;; Using example : (gc:Number2Alpha 73) = "BU"
;;-------------------------------------------------------------------------------
(defun gc:Number2Alpha (num / val)
(if (< num 27)
(chr (+ 64 num))
(if (= 0 (setq val (rem num 26)))
(strcat (gc:Number2Alpha (1- (/ num 26))) "Z")
(strcat (gc:Number2Alpha (/ num 26)) (chr (+ 64 val)))
)
)
)
In your case, simply do:
(gc:WriteExcel
(findfile "CODE ANALYSIS.xlsx")
"DATA"
"A20"
(apply
'append
'((1_2 50.0 10.0 10.0) (2_3 50.0 20.0 20.0) (3_4 50.0 20.0 20.0) (4_5 50.0 10.0 10.0))
)
)
Oops!... my mistake. the last argument have to be a list of lists.
And also, 1_2, 2_3, ... should be strings ("1_2", "2_3", ...) instead of symbols.
Try like this:
(gc:WriteExcel
(findfile "CODE ANALYSIS.xlsx")
"DATA"
"A20"
(list
(apply
'append
'(("1_2" 50.0 10.0 10.0)
("2_3" 50.0 20.0 20.0)
("3_4" 50.0 20.0 20.0)
("4_5" 50.0 10.0 10.0)
)
)
)
)
or simply like this (you only need a single sub-lists if you want to write on a single line).
(gc:WriteExcel
(findfile "CODE ANALYSIS.xlsx")
"DATA"
"A20"
'(("1_2" 50.0 10.0 10.0 "2_3" 50.0 20.0 20.0 "3_4" 50.0 20.0 20.0 "4_5" 50.0 10.0 10.0))
)
@DC-MWA wrote:If I have a variable that contains this
((1_2 50.0 10.0 10.0)(2_3 50.0 20.0 20.0)(3_4 50.0 20.0 20.0)(4_5 50.0 10.0 20.0))
1. How did you get it?
@DC-MWA wrote:
how do i get it to read like this?
(("1_2" 50.0 10.0 10.0 "2_3" 50.0 20.0 20.0 "3_4" 50.0 20.0 20.0 "4_5" 50.0 10.0 20.0))
2. Here's a way:
(list
(apply
'append
(mapcar '(lambda (l) (cons (vl-symbol-name (car l)) (cdr l)))
'((1_2 150.0 30.0 25.0)
(2_3 150.0 30.0 30.0)
(3_4 50.0 15.0 15.0)
(4_5 80.0 160.0 160.0)
(5_6 100.0 95.0 95.0)
(6_7 70.0 60.0 60.0)
)
)
)
)but it would be much more simple to directly build the correct list format (see 1.)
@DC-MWA wrote:Also, after the above, how do i make the program use the variable instead of an actual list as shown above?
3. Which variable?
