Text replace and formatting

April 28, 2015

Hi all, fairly new here, and fairly noob to LISP. I've done 'some' since AutoCAD 2000, but only here and there, so I'm very rough at this.

I've written a small routine to translate (read swap) languages, that scours a drawing for all text and mtext on layer 0 and puts it in a SS. Then I've used the Excel routines and bits from Gilles Chanteau to read in an excel file to a list. The text will be compared to that list and swapped.

The problem is, the special fonts(Greek, Russian, etc) or the replace method I think. IF i copy the Excel text and paste it into TEXT or MTEXT it displays properly. The routine however displays ?s for many characters, and setting text styles/fonts seems to have no effect. For example the English word 'SOFA' swapped becomes '??????S' but copy/paste shows 'ΚΑΝΑΠΕΣ'.

More or less i'm stepping through the SS comparing text strings to a list from excel. It swaps perfect every time, finding the matches for 18 languages. Excel Column 1 is English, 2 Greek and so on. It will swap to greek and display ?'s and it will recognize that as a match to a cell in the Greek column and change to another language or back to English so the recognition is there. I just can't figure out why it won't display. I was thinking the text string is raw or there is some unicode formatting issue, since copy/paste works. Strangely enough after copy paste that text is NOT recognized for a swap. Is there some formatting switch I need to include in my ENTMOD?

(setq ED (entget ENT))
(setq iTXT (cdr (assoc 1 ED)))
(setq curCELL (nth excelCOL (nth excelROW mylist))) ;original language
(setq retCELL (nth newCOL (nth excelROW mylist)))   ;new language
(if (= curCELL iTXT)
(setq ED (subst (cons 1 retCELL) (assoc 1 ED) ED))
(entmod ED)
); (if (= curCELL iTXT)

EDIT: i have a feeling mylist is the problem, if the font can't display there, it never gets to the text. How can i match a string character for character with an Excel cell's contents?

Edited April 28, 2015 by howiez69

May 1, 2015

It might be a limitation from the excel routines, i would have to take a look at them.

My feeling is that it is using (read-char) or (read-line)... and from what i see seems incompatible with unicode chars.. heres a test i made using a chinese symbol.

(ASCII "R")

82

(WRITE-CHAR 82)(PRINC)

R

(ASCII "你")

92

(WRITE-CHAR 92)(PRINC)

\

*edit: not obvious due to italic of quote, but it returned a back slash*

also i tried making a unicode text file chinease symbols, and made a try also writing "ΚΑΝΑΠΕΣ" in it. Reading back the file containing "ΚΑΝΑΠΕΣ" using a function that uses read-line it returned that:

(readthis "testtt.txt")
(ŸÞ`O}Y)

and entmoding an existing text with that gave that as a result: "ŸÞŠ"

Im not the most expert in unicode and symbols handling, but I am not sure there is a workaround..

May 4, 2015

I never thought about the Excel macro, The list is populated with odd characters so that would make sense. I have also discovered that if i copy/paste from Excel into D or MText, there are formatting characters locked in with the contents via properties palette.

I have attached the Excel snippet...

;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
; Arguments: 3
;   ExcelFile$ = Path and filename
;   SheetName$ = Sheet name or nil for not specified
;   MaxRange$ = Maximum cell ID range to include or nil to get the current region from cell A1
; Syntax examples:
; (GetExcel "C:\\Temp\\Temp.xls" "Sheet1" "E19") = Open C:\Temp\Temp.xls on Sheet1 and read up to cell E19
; (GetExcel "C:\\Temp\\Temp.xls" nil "XYZ123") = Open C:\Temp\Temp.xls on current sheet and read up to cell XYZ123
;-------------------------------------------------------------------------------
(defun GetExcel (ExcelFile$ SheetName$ MaxRange$ / Column# ColumnRow@ Data@ ExcelRange^
 ExcelValue ExcelValue ExcelVariant^ MaxColumn# MaxRow# Range$ Row# Worksheet)
 (if (= (type ExcelFile$) 'STR)
   (if (not (findfile ExcelFile$))
     (progn
       (alert (strcat "Excel file " ExcelFile$ " not found."))
       (exit)
     );progn
   );if
   (progn
     (alert "Excel file not specified.")
     (exit)
   );progn
 );if
 (gc)
 (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
   (progn
     (alert "Close all Excel spreadsheets to continue!")
     (vlax-release-object *ExcelApp%)(gc)
   );progn
 );if
 (setq ExcelFile$ (findfile ExcelFile$))
 (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
 (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
 (if SheetName$
   (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
     (if (= (vlax-get-property Worksheet "Name") SheetName$)
       (vlax-invoke-method Worksheet "Activate")
     );if
   );vlax-for
 );if
 (if MaxRange$
   (progn
     (setq ColumnRow@ (ColumnRow MaxRange$))
     (setq MaxColumn# (nth 0 ColumnRow@))
     (setq MaxRow# (nth 1 ColumnRow@))
   );progn
   (progn
     (setq CurRegion (vlax-get-property (vlax-get-property
       (vlax-get-property *ExcelApp% "ActiveSheet") "Range" "A1") "CurrentRegion")
     );setq
     (setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
     (setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))
   );progn
 );if
 (setq *ExcelData@ nil)
 (setq Row# 1);start at row 1 main function will find empty cells and flag row 1 as header
 (repeat MaxRow#
   (setq Data@ nil)
   (setq Column# 1)
   (while (<= Column# MaxColumn#)
     (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
     (setq ExcelRange^ (vlax-get-property *ExcelApp% "Range" Range$))
     (setq ExcelVariant^ (vlax-get-property ExcelRange^ 'Value))
     (setq ExcelValue (vlax-variant-value ExcelVariant^))
     (setq ExcelValue
       (cond
         ((= (type ExcelValue) 'INT) (itoa ExcelValue))
         ((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
         ((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
         ((/= (type ExcelValue) 'STR) "")
       );cond
     );setq
     (if (and (= ExcelValue "") (= Column# 1))
   (progn
     (Setq Column# (1+ MaxColumn#))
     )
   (progn
     (setq Data@ (append Data@ (list ExcelValue)))
     (setq Column# (1+ Column#))
     )
   )
   );repeat
   (if (/= Data@ nil)
     (setq *ExcelData@ (append *ExcelData@ (list Data@)))
     )
   (setq Row# (1+ Row#))
 );repeat
 ;(setq *ExcelData@ (remove-i 0 *ExcelData@))
 (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
 (vlax-invoke-method *ExcelApp% 'Quit)
 (vlax-release-object *ExcelApp%)(gc)
 (setq *ExcelApp% nil)
 *ExcelData@
);defun GetExcel
;-------------------------------------------------------------------------------
; GetCell - Returns the cell value from the *ExcelData@ list
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (GetCell "E19") = value of cell E19
;-------------------------------------------------------------------------------
(defun GetCell (Cell$ / Column# ColumnRow@ Return Row#)
 (setq ColumnRow@ (ColumnRow Cell$))
 (setq Column# (1- (nth 0 ColumnRow@)))
 (setq Row# (1- (nth 1 ColumnRow@)))
 (setq Return "")
 (if (< Row# (length *ExcelData@)) ; error trap for empty row
   (if *ExcelData@
     (if (and (>= (length *ExcelData@) Row#)(>= (length (nth 0 *ExcelData@)) Column#))
   (setq Return (nth Column# (nth Row# *ExcelData@)))
     );if
   );if
 ); (if (< Row# (length *ExcelData@)
 Return
);defun GetCell
;-------------------------------------------------------------------------------
; 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)
;-------------------------------------------------------------------------------
(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)
   );setq
 );while
 (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
   (list (Alpha2Number Column$) Row#)
   '(1 1);default to "A1" if there's a problem
 );if
);defun ColumnRow
;-------------------------------------------------------------------------------
; 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))
   );+
 );if
);defun Alpha2Number
;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
 (if (< Num# 27)
   (chr (+ 64 Num#))
   (if (= 0 (setq Val# (rem Num# 26)))
     (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
     (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
   );if
 );if
);defun Number2Alpha
;-------------------------------------------------------------------------------
; Cell-p - Evaluates if the argument Cell$ is a valid cell ID
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = String of the cell ID to evaluate
; Syntax examples: (Cell-p "B12") = t, (Cell-p "BT") = nil
;-------------------------------------------------------------------------------
(defun Cell-p (Cell$)
 (and (= (type Cell$) 'STR)
   (or (= (strcase Cell$) "A1")
     (not (equal (ColumnRow Cell$) '(1 1)))
   );or
 );and
);defun Cell-p
;-------------------------------------------------------------------------------
; Row+n - Returns the cell ID located a number of rows from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of rows from cell
; Syntax examples: (Row+n "B12" 3) = "B15", (Row+n "B12" -3) = "B9"
;-------------------------------------------------------------------------------
(defun Row+n (Cell$ Num#)
 (setq Cell$ (ColumnRow Cell$))
 (strcat (Number2Alpha (car Cell$)) (itoa (max 1 (+ (cadr Cell$) Num#))))
);defun Row+n
;-------------------------------------------------------------------------------
; Column+n - Returns the cell ID located a number of columns from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of columns from cell
; Syntax examples: (Column+n "B12" 3) = "E12", (Column+n "B12" -1) = "A12"
;-------------------------------------------------------------------------------
(defun Column+n (Cell$ Num#)
 (setq Cell$ (ColumnRow Cell$))
 (strcat (Number2Alpha (max 1 (+ (car Cell$) Num#))) (itoa (cadr Cell$)))
);defun Column+n
;-------------------------------------------------------------------------------
; rtosr - Used to change a real number into a short real number string
; stripping off all trailing 0's.
; Arguments: 1
;   RealNum~ = Real number to convert to a short string real number
; Returns: ShortReal$ the short string real number value of the real number.
;-------------------------------------------------------------------------------
(defun rtosr (RealNum~ / DimZin# ShortReal$)
 (setq DimZin# (getvar "DIMZIN"))
 (setvar "DIMZIN" 
 (setq ShortReal$ (rtos RealNum~ 2 )
 (setvar "DIMZIN" DimZin#)
 ShortReal$
);defun rtosr
;-------------------------------------------------------------------------------

Sign In

Text replace and formatting

Recommended Posts

howiez69

Jef!

howiez69

Join the conversation

Tutorials

Forums

Activity