EXCEL and AutoLISP

[daniel_gh]

I have a Lisp program which draw objects acc. to different standards (self made).
The user indicates the parameters (size,type.. etc.) and the program search in a "database" and draw the object. This "database" is now a simple .txt file and I would like to make it an .xls file in order to become more easy to add, change, delete etc. informations.
I have no idea if with Lisp it's possible to open, read, search, extract and use data from a .xls file.
First step, first question - it's possible to do this with Lisp ?

[miff]

Yes, it's possible. look in the Sample folder for some examples (mostly in VBA but those can be converted to lisp). Also, search on the Autodesk autocad.customization newsgroup, there are a lot of Q's & A's on this over there.

[fixo]

I have a Lisp program which draw objects acc. to different standards (self made).
The user indicates the parameters (size,type.. etc.) and the program search in a "database" and draw the object. This "database" is now a simple .txt file and I would like to make it an .xls file in order to become more easy to add, change, delete etc. informations.
I have no idea if with Lisp it's possible to open, read, search, extract and use data from a .xls file.
First step, first question - it's possible to do this with Lisp ?
Hi daniel_gh

Can you send the part of your text file in order to actually help to you more effectively

Thank you

f.

[daniel_gh]

Fixo,
attached is one of the files I am using.
Please let me know if you need more informations.
Thank you,
Daniel

[truevis]

Very possible...

;;;Visual LISP ActiveX API for Excel 97, 2000 and XP
;;;Originally from http://www.dsxcad.com/dsx/indexnew.html (http://www.dsxcad.com/dsx/indexnew.html)
;;;Modified by truevis


;;;*************************************************************************
;;; MODULE: DSX-TypeLib-Excel
;;; DESCRIPTION: Returns typelib (olb) file for either Excel 97, 2000, or XP
;;; ARGS: none
;;; EXAMPLE: (DSX-TypeLib-Excel)
;;;*************************************************************************

(defun dsx-typelib-excel (/ sysdrv tlb)
(setq sysdrv (getenv "systemdrive"))
(cond ((setq tlb
(findfile
(strcat sysdrv
"Program FilesMicrosoft OfficeOfficeExcel8.olb")))
tlb)
((setq tlb
(findfile
(strcat sysdrv
"Program FilesMicrosoft OfficeOfficeExcel9.olb")))
tlb)
((setq tlb
(findfile
(strcat sysdrv
"Program FilesMicrosoft OfficeOfficeExcel10.olb")))
tlb)
((setq tlb
(findfile
(strcat sysdrv
"Program FilesMicrosoft OfficeOfficeExcel.exe")))
tlb)
((setq tlb
(findfile
(strcat sysdrv
"Program FilesMicrosoft OfficeOffice10Excel.exe")))
tlb)))

;;;*************************************************************************
;;; MODULE: DSX-Load-TypeLib-Excel
;;; DESCRIPTION: Loads typelib for Excel 97, 2000 or XP (whichever is found)
;;; ARGS: none
;;; EXAMPLE: (DSX-Load-TypeLib-Excel)
;;;*************************************************************************

(defun dsx-load-typelib-excel (/ tlbfile tlbver out)
(dsx-princ "n(DSX-Load-TypeLib-Excel)")
(cond
((null msxl-xl24hourclock)
(if (setq tlbfile (dsx-typelib-excel))
(progn
(setq tlbver (substr (vl-filename-base tlbfile) 6))
(cond
((= tlbver "9") (princ "nInitializing Microsoft Excel 2000..."))
((= tlbver "8") (princ "nInitializing Microsoft Excel 97..."))
((= (vl-filename-base tlbfile) "Excel.exe")
(princ "nInitializing Microsoft Excel XP...")))
(vlax-import-type-library
:tlb-filename tlbfile :methods-prefix "msxl-" :properties-prefix
"msxl-" :constants-prefix "msxl-")
(if msxl-xl24hourclock
(setq out t)))))
(t (setq out t)))
out)

;;;*************************************************************************
;;; MODULE: DSX-Open-Excel-New
;;; DESCRIPTION: Opens a new session of Excel 97, 2000 or XP
;;; ARGS: display-mode ("SHOW" or "HIDE")
;;; EXAMPLE: (setq xlapp (DSX-Open-Excel-New "SHOW"))
;;;*************************************************************************

(defun dsx-open-excel-new (dmode / appsession)
(dsx-princ "n(DSX-Open-Excel-New)")
(princ "nCreating new Excel Spreadsheet file...")
(cond ((setq appsession (vlax-create-object "Excel.Application"))
(vlax-invoke-method (vlax-get-property appsession 'workbooks) 'add)
(if (= (strcase dmode) "SHOW")
(vla-put-visible appsession 1)
(vla-put-visible appsession 0))))
appsession)

;;;*************************************************************************
;;; MODULE: DSX-Open-Excel-Exist
;;; DESCRIPTION: Gets handle to existing (running) session of Excel 97, 2000, XP
;;; ARGS: xls-filename, display-mode ("SHOW" or "HIDE")
;;; EXAMPLE: (setq xlapp (DSX-Open-Excel-Exist "myfile.xls" "SHOW"))
;;;*************************************************************************

(defun dsx-open-excel-exist (xfile dmode / appsession)
(dsx-princ "n(DSX-Open-Excel-Exist)")
(princ "nOpening Excel Spreadsheet file...")
(cond
((setq fn (findfile xfile))
(cond
((setq appsession (vlax-get-or-create-object "Excel.Application"))
(vlax-invoke-method (vlax-get-property appsession 'workbooks) 'open fn)
(if (= (strcase dmode) "SHOW")
(vla-put-visible appsession 1)
(vla-put-visible appsession 0)))))
(t (alert (strcat "nCannot locate source file: " xfile))))
appsession)

;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-ColumnList
;;; DESCRIPTION: Write each list member to a column (startcol) starting at row (startrow)
;;; ARGS: list, startrow, startcol
;;; EXAMPLE: (DSX-Excel-Put-ColumnList '("A" "B" "C") 1 2) puts members into cells (1,B) (2,B) (3,B) respectively
;;;*************************************************************************

(defun dsx-excel-put-columnlist (lst startrow startcol)
(dsx-princ "n(DSX-Excel-Put-ColumnList)")
(foreach itm lst
(msxl-put-value (dsx-excel-get-cell range startrow startcol) itm)
(setq startrow (1+ startrow))) ; repeat
)

;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-RowList
;;; DESCRIPTION: Write each list member to row (startrow) starting at column (startcol)
;;; ARGS: list, startrow, startcol
;;; EXAMPLE: (DSX-Excel-Put-RowList '("A" "B" "C") 2 1) puts members into cells (1,B) (1,C) (1,D) respectively
;;;*************************************************************************
;;;
;;;(defun DSX-Excel-Put-RowList (lst startrow startcol)
;;; (dsx-princ "n(DSX-Excel-Put-RowList)")
;;; (foreach itm lst
;;; (msxl-put-value
;;; (DSX-Excel-Get-Cell range startrow startcol)
;;; itm
;;; )
;;; (setq startcol (1+ startcol))
;;; ); repeat


;;new by truevis

(defun dsx-excel-put-rowlist (lst startrow startcol)
(dsx-princ "n(DSX-Excel-Put-RowList)")
(setq range (msxl-get-activecell xlapp))
;;; (setq range (msxl-get-activesheet xlapp))
(foreach itm lst
;;; (msxl-put-value (dsx-excel-get-cell range startrow startcol) itm)
(msxl-put-value2 (dsx-excel-get-cell range startrow startcol) itm) ;; works with Excel XP
(setq startcol (1+ startcol))) ; repeat
)

;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-CellColor
;;; DESCRIPTION: Applies fill-color to specified cell
;;; ARGS: row, column, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-CellColor 1 1 14) apply color #14 to cell (1,A)
;;;*************************************************************************

(defun dsx-excel-put-cellcolor (row col intcol / rng)
(setq rng (dsx-excel-get-cell (msxl-get-activesheet xlapp) row col))
(msxl-put-colorindex (msxl-get-interior rng) intcol))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-RowCellsColor
;;; DESCRIPTION: Applies fill-color to a row of cells
;;; ARGS: startrow, startcol, num-cols, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-RowCellsColor 1 1 5 14) Start at row=1 col=1 repeat for 5 columns using color #14
;;;*************************************************************************

(defun dsx-excel-put-rowcellscolor (startrow startcol cols intcol / next)
(dsx-princ "n(DSX-Excel-Put-RowCellsColor)")
(setq next startcol)
(repeat cols
(dsx-excel-put-cellcolor startrow next intcol)
(setq next (1+ next))))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-ColumnCellsColor
;;; DESCRIPTION: Change fill color in a column of cells
;;; ARGS: startrow, startcol, num-rows, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-ColumnCellsColor 1 1 5 14) Start at row=1 col=1 repeat for 5 rows using color #14
;;;*************************************************************************

(defun dsx-excel-put-columncellscolor (startrow startcol rows intcol / next)
(dsx-princ "n(DSX-Excel-Put-ColumnCellsColor)")
(setq next startrow)
(repeat rows
(dsx-excel-put-cellcolor next startcol intcol)
(setq next (1+ next))))

[truevis]

Part 2:

;;;***********
;;; MODULE: DSX-Excel-Get-Cell
;;; DESCRIPTION: Get cell object relative to range using (relrow) and (relcol) offsets
;;; ARGS: range-object, relative-row, relative-col
;;; EXAMPLE: (DSX-Excel-Get-Cell rng1 2 2)
;;;**************

(defun dsx-excel-get-cell (rng relrow relcol)
(dsx-princ "n(DSX-Excel-Get-Cell)")
(vlax-variant-value
(msxl-get-item
(msxl-get-cells rng)
(vlax-make-variant relrow)
(vlax-make-variant relcol))))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-CellValue
;;; DESCRIPTION: Return value in given cell (row, column) of active session object (xlapp)
;;; ARGS: row(int), column(int)
;;; EXAMPLE: (DSX-Excel-Get-CellValue 1 2)
;;;*************************************************************************

(defun dsx-excel-get-cellvalue (row col)
(dsx-princ "n(DSX-Excel-Get-CellValue)")
(vlax-variant-value
(msxl-get-value (dsx-excel-get-cell (msxl-get-activesheet xlapp) row col))))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-RowValues
;;; DESCRIPTION: Returns a list of cell values within a given row
;;; ARGS: row-number(int), startcol, num-cells
;;; EXAMPLE: (DSX-Excel-Get-RowValues 3 1 20) get first 20 values in row 3
;;;*************************************************************************

(defun dsx-excel-get-rowvalues (row startcol numcells / next out)
(dsx-princ "n(DSX-Excel-Get-RowValues)")
(setq next startcol)
(repeat numcells
(setq out (if out
(append out (list (dsx-excel-get-cellvalue row next)))
; row x col
(list (dsx-excel-get-cellvalue row next)) ; row x col
)
next (1+ next))) ; repeat
out)

;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-ColumnValues
;;; DESCRIPTION: Returns a list of cell values within a given column
;;; ARGS: column-number(int), startrow, num-cells
;;; EXAMPLE: (DSX-Excel-Get-ColumnValues 2 1 20) get top-20 entries in column 2 ("B")
;;;*************************************************************************

(defun dsx-excel-get-columnvalues (col startrow numcells / next out)
(dsx-princ "n(DSX-Excel-Get-ColumnValues)")
(setq next startrow)
(repeat numcells
(setq out (if out
(append out (list (dsx-excel-get-cellvalue next col)))
(list (dsx-excel-get-cellvalue next col)))
next (1+ next))) ; repeat
out)

;;;*************************************************************************
;;; MODULE: DSX-Excel-GetRangeValues-ByRows
;;; DESCRIPTION: Get range values in row order and return as nested lists
;;; ARGS: startrow, startcol, num-rows, num-cols
;;; EXAMPLE: (DSX-Excel-GetRangeValues-ByRows 1 1 5 10) get range values from 1A to 5J where each sublist is one row
;;;*************************************************************************

(defun dsx-excel-getrangevalues-byrows
(startrow startcol numrows numcols / nextrow rowlst outlst)
(dsx-princ "n(DSX-Excel-GetRangeValues-ByRows)")
(setq nextrow startrow)
(repeat numrows
(setq rowlst (dsx-excel-get-rowvalues nextrow startcol numcols)
outlst (if outlst
(append outlst (list rowlst))
(list rowlst))
nextrow (1+ nextrow)))
outlst)

;;;*************************************************************************
;;; MODULE: DSX-Excel-GetRangeValues-ByCols
;;; DESCRIPTION: Get range values in column order and return as nested lists
;;; ARGS: startrow, startcol, num-rows, num-cols
;;; EXAMPLE: (DSX-Excel-GetRangeValues-ByCols 1 1 5 10) get range values from 1A to 5J where each sublist is one column
;;;*************************************************************************

(defun dsx-excel-getrangevalues-bycols
(startrow startcol numrows numcols / nextrow nextcol collst outlst)
(dsx-princ "n(DSX-Excel-GetRangeValues-ByCols)")
(setq nextcol startcol)
(repeat numcols
(setq collst (dsx-excel-get-columnvalues nextcol startrow numrows)
outlst (if outlst
(append outlst (list collst))
(list collst))
nextcol (1+ nextcol)))
outlst)

;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-ActiveWorkSheet
;;; DESCRIPTION: Returns object of active worksheet in active Excel session
;;; ARGS: app (session object)
;;; EXAMPLE: (DSX-Excel-Get-ActiveWorkSheet xlapp)
;;;*************************************************************************

(defun dsx-excel-get-activeworksheet (xlapp)
(dsx-princ "n(DSX-Excel-Get-ActiveWorkSheet)")
(msxl-get-activesheet xlapp))

;;;*************************************************************************
;;; MODULE: DSX-Excel-RangeAutoFit
;;; DESCRIPTION: Applies Auto-Fit to columns within active range
;;; ARGS: active-sheet (object)
;;; EXAMPLE: (DSX-Excel-RangeAutoFit myxlws)
;;;*************************************************************************

(defun dsx-excel-rangeautofit (active-sheet)
(dsx-princ "n(DSX-Excel-RangeAutoFit)")
(vlax-invoke-method
(vlax-get-property
(vlax-get-property (vlax-get-property active-sheet 'usedrange) 'cells)
'columns)
'autofit))

(defun dsx-excel-rangedataformat (active-sheet)
(dsx-princ "n(DSX-Excel-RangeDataFormat)")
(vlax-put-property
(vlax-get-property active-sheet "Cells")
'numberformat
"@"))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Quit
;;; DESCRIPTION: Quit and close Excel session (app)
;;; ARGS: app (session object)
;;; EXAMPLE: (DSX-Excel-Quit xlapp)
;;;*************************************************************************

(defun dsx-excel-quit (appsession)
(dsx-princ "n(DSX-Excel-Quit)")
(cond ((not (vlax-object-released-p appsession))
(vlax-invoke-method appsession 'quit)
(vlax-release-object appsession))))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Kill
;;; DESCRIPTION: Forces any open Excel sessions to be closed
;;; ARGS: none
;;; EXAMPLE: (DSX-Excel-Kill)
;;;*************************************************************************

(defun dsx-excel-kill (/ eo)
(while (setq eo (vlax-get-object "Excel.Application"))
(dsx-excel-quit eo)
(vlax-release-object eo)
(setq eo nil)
(gc)
(gc)
;; even this doesn't always kill the damn thing!
))

;;;*************************************************************************
;;; MODULE:
;;; DESCRIPTION:
;;; ARGS:
;;; EXAMPLE:
;;;*************************************************************************
;;; Remove trailing 'nil' members from a given list

(defun dsx-trimlist (lst)
(cond ((/= nil (last lst)) lst)
(t (dsx-trimlist (reverse (cdr (reverse lst)))))))

;;;*************************************************************************
;;; MODULE:
;;; DESCRIPTION:
;;; ARGS:
;;; EXAMPLE:
;;;*************************************************************************
;;; Convert a list of values into a list of string equivalents

(defun dsx-liststr (lst / mbr out)
(setq out '())
(foreach mbr lst
(cond ((= mbr nil) (setq out (cons "" out)))
((= (type mbr) 'str)
(if (member mbr '(" " " " " "))
(setq out (cons "" out))
(setq out (cons mbr out))))
((= (type mbr) 'int) (setq out (cons (itoa mbr) out)))
((= (type mbr) 'real) (setq out (cons (rtos mbr 2 6) out)))))
(reverse out))

;;;*************************************************************************
;;; MODULE: DSX-Excel-Sheets
;;; DESCRIPTION: Returns SHEETS collection from active workbook
;;; ARGS: Excel-application
;;; EXAMPLE: (setq sheets (DSX-Excel-Sheets xlApp))
;;;*************************************************************************

(defun dsx-excel-sheets (xlapp)
(setq xlsheets (vlax-get-property xlapp "sheets")))

;;;*************************************************************************
;;; MODULE:DSX-Excel-SheetDelete
;;; DESCRIPTION: Delete sheet (tab) from active workbook sheets collection
;;; ARG: sheet-name, sheets-collection
;;; EXAMPLE: (DSX-Excel-SheetDelete "Sheet3" xlSheets)
;;;*************************************************************************

(defun dsx-excel-sheetdelete (name xlsheets)
(vlax-for sh xlsheets
(if (= (vlax-get-property sh "Name") name)
(vlax-invoke-method sh "Delete"))))

;;;*************************************************************************
;;; MODULE: DSX-Excel-SheetAdd
;;; DESCRIPTION: Add new sheet (tab) to sheets collection in workbook, returns sheet object
;;; ARG: sheet-name, sheets-collection
;;; EXAMPLE: (setq newsheet (DSX-Excel-SheetAdd "SheetX" xlSheets))
;;;*************************************************************************

(defun dsx-excel-sheetadd (name xlsheets)
(setq newsheet (vlax-invoke-method xlsheets "Add"))
(vlax-put-property newsheet "Name" name)
newsheet)

;;;*************************************************************************
;;; MODULE: DSX-Excel-WorkbookSave


;;; DESCRIPTION: Saves active workbook to specified filename, if file exists, it is overwritten if user accepts prompt
;;; ARG: workbook-object, filename
;;; EXAMPLE: (DSX-Excel-WorkbookSave objWB "myfile.xls")
;;;*************************************************************************

(defun dsx-excel-workbooksave (workbook filename)
(if (findfile filename)
(vlax-invoke-method awb "Save")
(vlax-invoke-method
awb "SaveAs" filename msxl-xlnormal "" "" :vlax-false :vlax-false nil)))

;;;*************************************************************************
;;; MODULE: DSX-Excel-ActiveWorkbook
;;; DESCRIPTION: Returns active workbook object from given Excel application session
;;; ARG: Excel-application
;;; EXAMPLE: (setq objWB (DSX-Excel-ActiveWorkbook xlApp))
;;;*************************************************************************

(defun dsx-excel-activeworkbook (xlapp)
(vlax-get-property xlapp "ActiveWorkbook"))


(princ)

[fixo]

Fixo,
attached is one of the files I am using.
Please let me know if you need more informations.
Thank you,
Daniel
Hi Daniel

I have some questions

1. For the best conversion of a text file to file Excel
it is necessary to replace all empty columns in a text file
on any letter, for example on "-"
2. Here there is not clear algorithm - what is designate variable S0, S10, S30 etc etc
3. Figure is necessary, for example, what are you draw according to the first line
text file
4. If it does not represent interest for other users that
it will be logical to bear this problem out of a forum, if so,
you can send all files to me personally fixo@yandex.ru
5. Try these routine you shall see result I saw

Thank you

f.



(defun read-txt (/ fname ftxt rd_line rd_list)
(setq fname (getfiled "Select TXT file to read :"
(getvar "dwgprefix")
"txt"
16
)
)
(setq ftxt (open fname "r"))
(while
(setq rd_line (read-line ftxt))
(setq rd_list (cons (read (strcat "(" rd_line ")")) rd_list)))
(close ftxt)

(reverse rd_list)
)


;;=====================================================;;


(defun conv-to-csv (lst / )
(setq exc_list (vl-remove-if (function not)
(mapcar (function (lambda (x)
(mapcar (function (lambda (y)
(strcat (vl-princ-to-string y) "\t"))) x))) lst)))

(setq last_list (mapcar 'last exc_list)
last_list (mapcar (function (lambda (x)
(strcat (vl-string-trim "\t" x) "\n"))) last_list))

(setq exc_list (mapcar (function (lambda (x y)
(append (reverse (cdr (reverse x))) (list y)))) exc_list last_list))
)
(conv-to-csv data_list);ok
;;=======================================================;;

(defun txt->excel (/ data_line data_list fd fname)
(setq data_list (conv-to-csv (read-txt)))
(setq fname (getfiled "Enter file name to write data" "" "xls" 1))
(setq fd (open fname "w"))
(foreach ln data_list (foreach ix ln (princ ix fd)))
(close fd)
(princ)
)
(txt->excel)

[fixo]

Oops...

I forgot to put routine to read data from Excel file:



(defun read-xcl (/ fname ftxt rd_line rd_list)
(setq fname (getfiled "Select EXCEL file to read :"
(getvar "dwgprefix")
"xls"
16
)
)
(setq ftxt (open fname "r"))
(while
(setq rd_line (read-line ftxt))
(setq rd_list (cons (read (strcat "(" rd_line ")")) rd_list)))
(close ftxt)
(reverse rd_list)
)

;Test: (setq xls_data (read-xcl))

[daniel_gh]

Truevis and Fixo,
thank you for your answers & interest on subject.
I'll check your codes to see what can I use .

Regarding the questions of Fixo, the file I showed you is only one of my "databases"- I am not building my drawing directly from this file..
Imagine I have another one with about 300 - 350 lines and ~ 20 columns.
One wrong space(character) in a line will change all data I need to read from that line.


I have to go now....
Daniel

[fixo]

Truevis and Fixo,
thank you for your answers & interest on subject.
I'll check your codes to see what can I use .

Regarding the questions of Fixo, the file I showed you is only one of my "databases"- I am not building my drawing directly from this file..
Imagine I have another one with about 300 - 350 lines and ~ 20 columns.
One wrong space(character) in a line will change all data I need to read from that line.


I have to go now....
Daniel

Do not worry
I will help to you

f.

[daniel_gh]

Fixo has found a solution for my problem.
Who is interested and need the code please feel free to contact Fixo or me.

[PellaCAD]

How would this be converted to VBA?? I'm stuck!!

Set xlbook = xlapp.Workbooks.Open(path & "\" & dwgname & "-" & "KeyNotes Data Main.xls", UpdateLinks:=3)

Yes, it's possible. look in the Sample folder for some examples (mostly in VBA but those can be converted to lisp). Also, search on the Autodesk autocad.customization newsgroup, there are a lot of Q's & A's on this over there.

[Opie]

How would this be converted to VBA?? I'm stuck!!

Set xlbook = xlapp.Workbooks.Open(path & "\" & dwgname & "-" & "KeyNotes Data Main.xls", UpdateLinks:=3)
Are you looking for a VBA solution?