SUPPORTED FOR: AUTOLISP ADD TEXT BELOW TEXT AVAILABLE FROM FILE EXCEL

[vanconghoaviet]

- I have an excel file with the content of column A being Vietnamese, column B being English.
- In the Autocad file, there are contents of Vietnamese or English text
- Using Autolisp code (TTA to add English content, TTV to add Vietnamese content), scan to select the object, select the Excel file containing the content, Autolisp will add the corresponding content under the sample text with appropriate spacing.
Thank you for your interest and support for my request!

I have cad and excel file attached

[devitg]

Hi , will it be the only text to handle , or you will add more , as differents topics. 

As I see , it will better the file be a CSV , as it is easy to handle by lisp . 

You can SAVEAS.csv from xlsx 

[dbhunia · Accepted solution]

check this....... (the reading of Excel file I got from FORUM years ago, which I used).

But your Excel containing "Uni code Character" ....... those are creating problem to read using LISP. So in that place you will get "?" mark. if you can remove those then every thing will good.......

(defun Read_Excel ( Opt / adoc acsp address data_list fname mtext_obj pname pt pt_str sheetnum Txt_write)
	(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))
	)
	(setq address "A1:B500")
	(setq sheetnum 1)
	(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS;XLSX" 4))
	(setq data_list (read_excel_range fname sheetnum address))
	(foreach data data_list
		(setq data (if (= Opt "TTA") data (reverse data)))
		(foreach txt data (setq Txt_write (cons txt Txt_write)))
	)
	(setvar 'cmdecho 0)(vla-StartUndoMark adoc)
	(wt Txt_write)
	(setvar 'cmdecho 1)(vla-EndUndoMark adoc)
	(princ)
)
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
	(vl-load-com)
	(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
	(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
)
(defun WT (lst / N)
(setq N (length lst))
(command "text" "_none" (getpoint "\nPick Insertion Point of text...") "" "" (nth (setq N (- N 1)) lst)
		 "_.chprop" (entlast) "" "_LA" "Text VN- text tiếng việt" "")
   (while (> N 0)
	(command "text" "" (nth (setq N (- N 1)) lst)
			 "_.chprop" (entlast) "" "_LA" (if (= (rem N 2) 0) "Text EN- text tiếng anh" "Text VN- text tiếng việt") "")
   )
)
(defun C:TTA nil (Read_Excel "TTA"))
(defun C:TTV nil (Read_Excel " "))

[devitg]

As I can see , the  "dictionary"  shall be at the dwg ,  because  as per

(Cdr (assoc 1 (entget ( car (entsel)))))

You get 

(setq text-1 "DÀN L\U+1EA0NH CÓ QU\U+1EA0T") ; one at the left 
(Setq text-2 "DÀN L\U+1EA0NH CÓ QU\U+1EA0T") ; the other at right 

and for it , 

(wcmatch text-1 text-2)

T is given.

So the dictionary shall be at the DWG , in 2 columns . 

and the do a Wcmatch , and lok at the other column 

Hope my way to do is clear .

[vanconghoaviet]

Thank you
I can scan the region for the content to be processed in cad.
Keep text 1 in place and add text 2 directly below

I have an autolisp but not perfect (I do not have the form .lsp)
- but it replaces all the content itself in cad (I need it processed in my chosen area)
- it does not retain the original text

[vanconghoaviet]

Thank you
I can do library right on cad like you.
Attachment below

And me
I have an autolisp but not perfect (I do not have the form .lsp)
- but it replaces all the content itself in cad (I need it processed in my chosen area)
- it does not retain the original text

[vanconghoaviet]

Thank you
Attachment below

[devitg]

as you wrote

I have an autolisp but not perfect (I do not have the form .lsp)

Have or have not , such .lsp

Please upload it 

[devitg]

text shall be at the same line , side by side , and with a fixed distances between it's insert point . So LIsp could find it 

[vanconghoaviet]

- From the dictionary file is Excel (the content is in sheet1, column A: Vietnamese, column B: other languages, the conversion command is: TTA => into other languages, the opposite is TTV => to Vietnamese ). - If column A is in another language, B is Vietnamese, then TTV and TTA orders will be swapped. - Once you have an Excel file, use the TAOTUDIEN command to create a dictionary for CAD, just create 1 until the change or addition ... This is the file to run: https://drive.google.com/file/d/0B2LetfHDljPGc3BmaDlJX29lYTA/view?usp=sharing   P / s: - Code table must correspond (Meaning: Cad code UNICODE, then Excel also UNICODE ...)        - Converting by String by String (so: CAD & Excel => Uppercase is the same uppercase, often lower case, underlined is the same underline).

[vanconghoaviet]

I have a command structure, but I don't know anything about autolisp.
Thanks for you tuning it to work.
Thanks

(defun main
FasStringtables 0
FasStringtables 1
(defun main
nil
(setq TRANSLATE_STR <Func> TRANSLATE_STR)
TRANSLATE_STR
(setq C:TTA C:TTA)
(vl-ACAD-defun C:TTA)
C:TTA
(setq C:TTV C:TTV)
(vl-ACAD-defun C:TTV)
C:TTV
(VL-LOAD-COM )
(setq C:TAOTUDIEN C:TAOTUDIEN)
(vl-ACAD-defun C:TAOTUDIEN)
C:TAOTUDIEN
(PRINC ":Lisp Create 2018:")
(PRINC "\nLenh: TAOTUDIEN; TTA; TTV")
(defun TRANSLATE_STR
(VA)
(_al-bind-alist '(ASOC DIC DOC ELS ENT FILE I LST SS STR))
(setq DOC (vla-get-ActiveDocument (vlax-get-acad-object )))
(vla-StartUndoMark (setq DOC (vla-get-ActiveDocument (vlax-get-acad-object ))))
(SETVAR NOMUTT 1)
(SETVAR CMDECHO 0)
(setq FILE (FINDFILE "QMDIC2017.dwg"))
(setq TBL (ENTLAST ))
(setq SS (SSGET "_X" '((cons 0 "TEXT"))))
(setq LST (MAPCAR CDR (VL-REMOVE-IF-NOT '(LAMBDA '(X) '(EQ '(CAR X) 302)) (ENTGET TBL))))
(setq DIC (CONS (CONS (CAR LST) (CADR LST)) DIC))
(setq DIC Then OR Else)
(setq LST (CDDR LST))
(setq I (SSLENGTH SS))
(setq I (1- I))
(setq ENT (SSNAME SS (setq I (1- I))))
(setq ELS (ENTGET ENT))
(setq STR (CDR (ASSOC 1 ELS)))
(setq ASOC (ASSOC STR DIC))
T
(ENTDEL TBL)
(SETVAR Then OR Else 0)
(vla-EndUndoMark DOC)
(defun C:TTA
(TRANSLATE_STR T)
(PRINC ":Lisp Create 2018:")
(defun C:TTV
(TRANSLATE_STR nil)
(PRINC ":Lisp Create 2018:")
(defun C:TAOTUDIEN
(_al-bind-alist '(ADDDATALINK DC EXCELDATA))
(defun EXCELDATA
(_al-bind-alist '(ROW COL LST VERTXLDATA CELLPROPS CELLITEM))
(cond T (
(setq FILEPATH (GETFILED "Select File:" (GETVAR DWGPREFIX) "xlsx;xls" 2))
(cond (GETFILED "Select File:" (GETVAR DWGPREFIX) "xlsx;xls" 2) (
it's OR skip next 6 bytes -> 709
it's OR skip next 6 bytes -> 709
(setq EXCEL-APP (vlax-get-or-create-object "excel.application"))
(setq WB-COLLECTION (vlax-get EXCEL-APP "workbooks"))
(setq ARQ (vlax-invoke-method WB-COLLECTION "Open" FILEPATH))
(setq SHEETS (vlax-get ARQ "sheets"))
(setq SHEET1 (vlax-get-property SHEETS "item" 1))
(setq SHEETNAME (vlax-get-property SHEET1 "Name"))
(vlax-invoke-method WB-COLLECTION CLOSE)
(VL-CATCH-ALL-APPLY vlax-invoke-method (LIST EXCEL-APP QUIT))
(MAPCAR '(LAMBDA '(X) '(vlax-release-object X)) (LIST SHEET1 SHEETS ARQ WB-COLLECTION EXCEL-APP))
(MAPCAR '(LAMBDA '(X) '(SETQ X nil) '(GC)) (LIST SHEET1 SHEETS ARQ WB-COLLECTION EXCEL-APP))
(GC )
(setq EXCELDATA <Func> EXCELDATA)
(defun DC
(STR LEN)
(setq DC <Func> DC)
(defun ADDDATALINK
(DATALINKNAME FILEPATH)
(_al-bind-alist '(ACTDOC DATDICT DLDATE DATDICTENAME DATALINKLIST DLEM TEMPTC SHEETNAME TABLECONTENT))
(EXCELDATA )
(setq ACTDOC (vla-get-ActiveDocument (vlax-get-acad-object )))
(setq DATDICT (vla-Add (vla-get-Dictionaries (vla-get-Database ACTDOC)) "ACAD_DATALINK"))
(setq DATALINK (LIST (cons 0 "DATALINK") (cons 100 "AcDbDataLink")))
(setq DLEM (ENTMAKEX DATALINK))
(setq EDL (ENTGET DLEM))
(setq TEMPTC (ENTMAKEX (LIST (CONS 0 "TABLECONTENT") (CONS 100 "AcDbLinkedData") (CONS 100 "AcDbLinkedTableData") (CONS 92 0) (CONS 100 "AcDbFormattedTableData") (CONS 300 "TABLEFORMAT") (CONS 1 "TABLEFORMAT_BEGIN") (CONS 90 4) (CONS 170 0) (CONS 309 "TABLEFORMAT_END") (CONS 90 0) (CONS 100 "AcDbTableContent"))))
(setq DLDATE (GETVAR CDATE))
(setq DATDICTENAME (vlax-vla-object->ename DATDICT))
(ENTMOD (SUBST (CONS 330 DATDICTENAME) (ASSOC 330 (ENTGET TEMPTC)) (ENTGET TEMPTC)))
(setq DATALINKLIST (LIST (ASSOC -1 EDL) (CONS 0 "DATALINK") (CONS 102 "{ACAD_REACTORS") (CONS 330 DATDICTENAME) (CONS 102 "}") (CONS 330 DATDICTENAME) (CONS 100 "AcDbDataLink") (CONS 1 "AcExcel") (CONS 300 "") (CONS 301 (STRCAT "Data Link\n" DATALINKNAME "\n" FILEPATH "\nLink details: Entire sheet: " SHEETNAME)) (CONS 302 (STRCAT FILEPATH "!" SHEETNAME)) (CONS 90 2) (CONS 91 1179649) (CONS 92 1) (CONS 170 (DC 1 4)) (CONS 171 (DC 5 2)) (CONS 172 (DC 7 2)) (CONS 173 (+ (DC 10 2) 6)) (CONS 174 (DC 12 2)) (CONS 175 (DC 14 2)) (CONS 176 (DC 16 2)) (CONS 177 3) (CONS 93 0) (CONS 304 "") (CONS 94 0) (CONS 360 TEMPTC) (CONS 305 "CUSTOMDATA") (CONS 1 "DATAMAP_BEGIN") (CONS 90 3) (CONS 300 "ACEXCEL_UPDATEOPTIONS") (CONS 301 "DATAMAP_VALUE") (CONS 93 2) (CONS 90 1) (CONS 91 1179649) (CONS 94 0) (CONS 300 "") (CONS 302 "") (CONS 304 "ACVALUE_END") (CONS 300 "ACEXCEL_CONNECTION_STRING") (CONS 301 "DATAMAP_VALUE") (CONS 93 2) (CONS 90 4) (CONS 1 (STRCAT FILEPATH "!" SHEETNAME)) (CONS 94 0) (CONS 300 "") (CONS 302 "") (CONS 304 "ACVALUE_END") (CONS 300 "ACEXCEL_SOURCEDATE") (CONS 301 "DATAMAP_VALUE") (CONS 93 2) (CONS 90 😎 (CONS 92 16) (CONS 94 0) (CONS 300 "") (CONS 302 "") (CONS 304 "ACVALUE_END") (CONS 309 "DATAMAP_END")))
(ENTMOD DATALINKLIST)
(ENTMOD (APPEND (ENTGET DATDICTENAME) (LIST (CONS 3 DATALINKNAME) (CONS 360 DLEM))))
(setq ADDDATALINK <Func> ADDDATALINK)
(defun -lambda-
(_al-bind-alist '(DLNAME ADOC OBS))
(SETVAR CMDECHO 0)
(SETVAR NOMUTT 1)
(setq ADOC (vla-get-ActiveDocument (vlax-get-acad-object )))
(setq DLNAME "")
(ADDDATALINK DLNAME nil)
(VL-CMDF "-table" "L" DLNAME '(0 0 0))
(setq TBL (ENTLAST ))
(setq OBS (vla-get-PickfirstSelectionSet ADOC))
(vlax-invoke OBS ADDITEMS (LIST (vlax-ename->vla-object TBL)))
(vlax-invoke ADOC WBLOCK (STRCAT (VL-FILENAME-DIRECTORY (FINDFILE "acad.dcl")) "\QMDIC2017.dwg") OBS)
(vla-Delete OBS)
(vlax-release-object OBS)
(ENTDEL TBL)
<Func> -lambda-
(PRINC "\n")
(PRINC "\nUse TTA, TTV commands to translate...!")