How to use excel file .xls file instead of .csv file

[amol1234]

Hello,

I am able to use .csv file for attribute entries and do update into AutoCAD file using update attribute of MacLee. Is there any way I can use excel to update attributes instead of csv?

UpdateTitleblockV1-9.lsp

Edited May 30 by amol1234

[exceed]

 

I recommend continuing to use CSV for input purposes.

or create xlsx by csv file without moving cells, and save it as csv using save as when inputting. You may be able to use column widths, colors, and formulas.

 

CSV file is simply separated by commas, without any additional code, so it is easy and fast to read.

 

you can compare the difficulty levels by right-clicking the xlsx file and csv file and selecting notepad from open with.

 

 

Nevertheless, if you want to do it, look for "getexcel.lsp"

 

 

[Steven P]

Was going to also suggest getexcel, but as exceed, I find this slower than reading a csv file directly

[amol1234]

@exceed is there any already made program using getexcel? Cause I am new to this autolisp. I understand instruction set and I can read program but I am looking for reusable program like Mac Lee's program

[BIGAL]

When reading direct from Excel it nearly always needs to be a custom program, reading certain cells then putting into say a attribute. The issues can be like read column 1 then 4 then 2 then 3 etc. 

 

There is a lot of code out there, I started with getexcel and added more functions, you need to have a good understanding of lisp but its fairly easy to do.

 

The other way is to use a Excel macro and create CAD objects. 

 

Speed not a problem as I use different method than getexcel to read cells.

 

 There are a few of us here with excel code do a google.

 

If you post a dwg and excel can see what I can do. Make it clear what is being changed.

 

Alan Excel library.lsp

Last comment working on similar for Librecalc.

[amol1234]

@BIGAL I have prepared one sample dwg template and excel to try your program on. In lisp program you have given, I am not able to perfectly map program to changes according to my need. If you can provide some instruction where I can change accordingly, I will do it. Thanks a lot for your efforts. I want to update attributes in drawing.

Metadata.xlsx Trial.dwg

Edited June 3 by amol1234

[amol1234]

Can anybody else help me with this?

 

[BIGAL]

Added to my do list.

 

Ok try this but note it matches the dwg I have posted. As your dwg is to vague about what goes where.

 

Just open your Excel 1st as more code is required to open a certain file.

 

; https://www.cadtutor.net/forum/topic/86140-how-to-use-excel-file-xls-file-instead-of-csv-file/

(defun c:amol1234 ( / ss cells cell myxl myrange range start end ss atts att obj str rows col)
; thanks to Lee-mac for this defun
; 58 is Colon

(defun csv->lst58 ( str / pos )
(if (setq pos (vl-string-position 58 str))
    (cons (substr str 1 pos) (csv->lst58 (substr str (+ pos 2))))
    (list str)
)
)

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)

; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

;;	Thanks to fixo			;;
(defun getcell2 (row column / )
  (setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
  (setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)

(setq myrange (vlax-get-property (vlax-get-property (vlax-get-property  myxl "ActiveSheet") 'UsedRange) 'address))
(setq range (csv->lst58 myrange))

(setq start (vl-string-subst "" "$" (car range)))
(setq start (vl-string-subst "" "$" start))
(setq start (columnrow start))

(setq end (vl-string-subst "" "$" (cadr range)))
(setq end (vl-string-subst "" "$" end))
(setq end (columnrow end))

(setq lays (layoutlist))

(if (/= (length lays)(- (cadr end) 1))
(progn
  (alert "Insufficient details compared to number of layouts \n\nWill now exit")
  (exit)
)
)

(setq rows 2)
(setq x -1)
(repeat (- (cadr end) 1)
  (setq col 1)
  (setvar 'ctab (nth (setq x (1+ x)) lays))
  (setq ss (ssget "X" (list (cons 0 "INSERT")(cons 2 "Title_Block_trial")(cons 410 (getvar 'ctab)))))
  (setq obj (vlax-ename->vla-object (ssname ss 0)))
  (setq atts (vlax-invoke obj 'Getattributes))
  (foreach att atts
    (setq str (getcell2 rows col))
    (vla-put-Textstring att str)
    (setq col (1+ col))
  )
  (setq rows (1+ rows))
)

(princ)
)
(c:amol1234)

 

 

 

Trial (1).dwg

Edited June 7 by BIGAL