Lisp to read excel file to insert text values at coordinates

Hi, Guys

I've been off the forum for a long time, I know.... sorry about it... sometimes I answer threads or new questions from old threads, but now I got a simple new one, I may have a similar lisp around, but while I find it, I wanted to post my new little trouble for you guys.

I got a new work to help and get it done in the next 2 days, but first I need to do this... from a text file (or excel file, I got both) I got 3 columns, 1 one for node number, and 2 other for X and Y coordinates for each node.

So from that file I need to insert into autocad the node numbers as text (using the current style and height) at the point specified by the other two columns, that are the X,Y coordinates which will be the insertion point of text....

like this..

2619 406,359.91 1,866,448.12

Does anyone have a lisp to do this?

This is what I need too, I will check Civil3d tomorrow for a possible way.

http://forums.autodesk.com/t5/AutoCAD-2007/Help-with-Inserting-text-with-a-lisp-routine/td-p/2633793

The easiest solution will be to create a script from that file using Excel and its formating features (fill only the first row and clone to the remaining ones):

_TEXT _J _MC [color=darkgreen]406359.91,1866448.12[/color] 5.0 0.0 [color=darkgreen]2619[/color]

Ok, It seems I got what I need in this one... great!

https://sites.google.com/site/cadlispandtips/autocad-lisp/import-points

The easiest solution will be to create a script from that file using Excel and its formating features (fill only the first row and clone to the remaining ones):

_TEXT _J _MC [color=darkgreen]406359.91,1866448.12[/color] 5.0 0.0 [color=darkgreen]2619[/color]

You know, I didn´t think about it, looks easy and may work, I'll test it out tomorrow morning, thanks a lot.

You may want to try this oldies as well

 
(vl-load-com); load ActiveX dynamic library

;;local defuns:
;; subfunction: read diapazone of cells by given file name, sheet number and address of range
;; usage: (setq data_list (read_excel_range "C:\\Programming\\FORUMS\\Cadtutor\\excel_test2007.xlsx" 1 "A1:C10000"))
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(vl-load-com)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property 
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     UsdRange (vlax-get-property
  UsdRange
  "CurrentRegion"
  )
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close" :vlax-false)
 )
 (gc)
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )
 (gc)
 ExcData
)

;; main program
(defun C:CVIMP (/ adoc acsp address data_list fname mtext_obj pname pt pt_str sheetnum)
 (setq adoc (vla-get-activedocument
       (vlax-get-acad-object)
     )
 )
 (if (= 1 (vlax-get-property adoc 'activespace))
   (setq acsp (vla-get-modelspace adoc))
   (setq acsp (vla-get-paperspace adoc))
 )
 ;; points range address
 (setq address "A1:C100") ;<--3 columns (if tab has headers the you have to starts from A2 to avoid first row with headers)
 ;; sheet number
 (setq sheetnum 1);<-- you can use instead a sheet name as well: (setq sheetnum "Sheet1")


(if 
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLSX;XLS" 4))

;; Or use hard coded path for your test (change path before):  (setq fname "C:\\Temp\\MyExcel2007File.xlsx")   
 (progn
(setq data_list (read_excel_range fname sheetnum address))
;; display result (for debug only)
;;(alert (apply 'strcat (mapcar '(lambda (a)(strcat a "\n"))  (mapcar 'vl-princ-to-string data_list))))
(foreach line data_list
 ;; assume the first row is for point name 
 (setq pname (car line))
 (setq pt (trans (list (cadr line)(last line))1 0))
 ;; build text string to your suit, e.g.: (setq pt_str (strcat pname "[url="file://px/"]\\PX[/url] = " (rtos (cadr line))"[url="file://py/"]\\PY[/url] = " (rtos (last line))))
 (setq pt_str (strcat pname "[url="file://p/"]\\P[/url]" (rtos (cadr line))"[url="file://p/"]\\P[/url]" (rtos (last line))))
 (setq mtext_obj (vl-catch-all-apply 'vlax-invoke-method (list acsp 'addmtext (vlax-3d-point pt) 0.0 pt_str)))
 (vla-put-attachmentpoint mtext_obj acAttachmentPointBottomCenter)
 (vla-put-insertionpoint mtext_obj (vlax-3d-point pt))
 (vla-update mtext_obj)
)
)
  )
 (princ)
 )
(prompt "\n\t\t>>>\tType \"CVIMP\" or \"cvimp\" to execute\t<<<")
(prin1)
;;===============================================================;;