Jump to content

Help!! Decimal place values of Integers in a Lisp, importing values from excel


loveboatcaptain

Recommended Posts

I am using the XL lisp available from http://www.jefferypsanders.com/autolisp_XL.html

 

Its a great free lisp that takes cell values from an excel spreadsheet and imports them into cad by replacing attribute or text values in autocad.

 

the problem I have is that imports numerical values incorrectly. it cant seem to process any excel value over 6 digits, e.g. a cell value of

1234567.123 imports as 123457e+006

1234.123 imports as 1234.12

123.123 import as 123.123

1.123 imports as 1.123

 

Is the lisp causing this problem or prescision variables in cad?????? I've tried all i can with no joy.:(

Link to comment
Share on other sites

Correct me if I am wrong, but you are obtaining the Text Property (the Cell Value) As String from Excel... So, what function are you using to convert from a String to a Real number?

Link to comment
Share on other sites

Correct me if I am wrong, but you are obtaining the Text Property (the Cell Value) As String from Excel... So, what function are you using to convert from a String to a Real number?

 

Im not sure as I didnt write this lisp

Here is the lisp code:

 

(defun GetRangeCells(stRow stCol LsRow LsCol / cellList copyCol)
 ;;;--- Make a copy of the first column
 (setq copyCol stCol)
 ;;;--- Build an empty list to hold the cell's addresses and values
 (setq cellList(list))
 ;;;--- Save the column
 (setq tmpCol stCol)
 ;;;--- Set up a cell counter and a flag
 (setq cellCnt 0 oldstRow nil)
 
 ;;;--- Loop while we are inside the range
 (while (<= stRow LsRow )
   ;;;--- Increment the cell counter
   (setq cellCnt(+ cellCnt 1))
   ;;;--- Add the address and value to the cell list
   (setq cellList
     (append cellList
               
       ;;;--- Build a list containing the cell's row:col and value
       (list
         (list
           ;;;--- Build a string representing the row and column
           (strcat (N2C stCol) (itoa stRow))
                 
           ;;;--- Get the value of the cell
           (vlax-variant-value
             (JXCL-get-value
               (vlax-variant-value
                 (JXCL-get-item
                   (JXCL-get-cells (JXCL-get-ActiveSheet myApp))
                   (vlax-make-variant stRow)
                   (vlax-make-variant stCol)
                 )
               )   
             )
           )
         )
       )
     )        
   )
   ;;;--- Inform the user of progress - Added 3/31/05
   (if(or(> cellCnt 50)(/= oldstRow stRow))
     (progn
       (princ "\n Currently retrieving cells in Row ")
       (princ (strcat (itoa stRow) " of " (itoa LsRow)))
       (setq oldstRow stRow)
       (setq cellCnt 0)
     )
   )
 
   ;;;--- Increment the column
   (setq stCol(+ stCol 1))
   ;;;--- Make sure the column stays in the range
   (if(> stCol LsCol)
     (setq stCol copyCol stRow(+ stRow 1))
   )
 ) 
 (princ "\n Looking for Logical cell values...")
 (princ)
 ;;;--- Check for LOGICAL cell values and replace them with a string
 (setq newList(list) cellCnt 0 tmpCnt 0  cellPercentage -1)
 (foreach a cellList
   ;;;--- Keep the user informed of progress
   (setq cellCnt(+ cellCnt 1) tmpCnt(+ tmpCnt 1))
   (if(< tmpCnt 10)
     (progn
       (setq oldCellPercentage cellPercentage)
       (setq cellPercentage(* 100.0 (/ (float cellCnt) (float(length cellList)))))
       (if(/= (rtos oldCellPercentage 2 0) (rtos cellPercentage 2 0))
         (progn
           (princ "\n Checking for Logical cell values ...")
           (princ (rtos cellPercentage 2 0))
           (princ "% Complete.")
         )
       )
       (setq tmpCnt 0)
     )
   )
   
   ;;;--- If a logical cell value [T or F] is found, replace it with a string ["TRUE" or "FALSE"]
   (cond
     ((equal (list :vlax-true)  (cdr a)) (setq newList(append newList (list(list (car a) "TRUE" )))))
     ((equal (list :vlax-false) (cdr a)) (setq newList(append newList (list(list (car a) "FALSE")))))
     (T (setq newList (append newList (list a))))
   )
 )
 (princ "\n Finished looking for Logical Cell values!")
 (princ)
 ;;;--- Return the list
 newList
)  


;;;--- Function to get the selected worksheet name from the dialog box
(defun saveVars()
 ;;;--- Get the index of the selected sheet
 (setq sheetIndex(atoi(get_tile "sheetlist")))
 ;;;--- Use the index to find the name of the sheet
 (setq sheetName(nth sheetIndex sheetList))
)

(defun C2N(a)
 (if(= 1 (strlen a))
   (setq column (- (ascii a) 64))
 )  
 (if(= (strlen a) 2)
   (progn
     (setq b(substr a 1 1))
     (setq c(substr a 2 1))
     (setq column(- (ascii c) 64))
     (setq column(+ column(* 26 (- (ascii b) 64))))
   )
 )  
 column
)  

(defun N2C(a)
 (if(< a 27)
   (setq column (chr (+ a 64)))
   (setq column
     (strcat
       (if(= 91 (+ 64(fix(/ a 26.001))))
          "Z"
          (chr(+ 64(fix(/ a 26.001))))
       )  
       (if(= 64 (+ 64(- a(* 26(fix(/ a 26))))))
         "Z"
         (chr(+ 64(- a(* 26(fix(/ a 26))))))
       )
     )
   )
 )  
 column
)


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;                                                                ;;;
;;;  888       888          888           8888888      8888   888  ;;;
;;;  8888     8888         88888            888        88888  888  ;;;
;;;  88888   88888        888 888           888        888888 888  ;;;
;;;  888888 888888       888   888          888        888 888888  ;;;
;;;  888 88888 888      88888888888         888        888  88888  ;;;
;;;  888  888  888     888       888      8888888      888   8888  ;;;
;;;                                                                ;;;
;;;                                                                ;;;
;;;           888            888888888        888888888            ;;;
;;;          88888           888   888        888   888            ;;;
;;;         888 888          888   888        888   888            ;;;
;;;        888   888         888888888        888888888            ;;;
;;;       88888888888        888              888                  ;;;
;;;      888       888       888              888                  ;;;
;;;                                                                ;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(defun XL_GET (/ myApp sysDrive fileName dataList myWBooks myWBook sht shtCnt mySheets
                sheetList cnt shtName dcl_id ddiag mySht mySheet myRange myAddress
                myCells 1stRow 1stCol LstRow LstCol dataList)
 ;;;--- Get the system drive
 (setq sysDrive (getenv "systemdrive"))
 ;;;--- If the excel object library is not found...load it
 (if (null Library)
   (progn
     ;;;--- Find out which version we should use
     (setq Library
       (cond
         ((findfile (strcat sysDrive "[url="file:///"]\\Program[/url] Files\\Microsoft Office\\Office12\\Excel.exe"))) 
         ((findfile (strcat sysDrive "[url="file:///"]\\Program[/url] Files\\Microsoft Office\\Office12\\XL5EN32.OLB")))
  ((findfile (strcat sysDrive "[url="file:///"]\\Program[/url] Files (x86)\\Microsoft Office\\Office12\\Excel.exe"))) 
         ((findfile (strcat sysDrive "[url="file:///"]\\Program[/url] Files (x86)\\Microsoft Office\\Office12\\XL5EN32.OLB")))
       )
     )
     ;;;--- If the library was found...
     (if Library
       (progn
         ;;;--- Strip off 
         (setq LibVer (substr (vl-filename-base Library) 6))
         (cond
           ((= LibVer "8")(princ "\n Opening Excel Version 8..."))
           ((= LibVer "9")(princ "\n Opening Excel Version 9..."))
           ((= LibVer "1")(princ "\n Opening Excel Version 10..."))
           ((= LibVer "") (princ "\n Opening Excel Version 2000+..."))
           ((= LibVer "3")(princ "\n Opening Excel Version 2003..."))
         )
         (vlax-import-type-library
           :tlb-filename Library
           :methods-prefix "JXCL-"
           :properties-prefix "JXCL-"
           :constants-prefix "JXCL-"
         )
       )
       (alert "Excel Object Library was not found!\n\nLook inside the XL_Readme.txt file for HELP.")
     )
   )
 )

 ;;;--- If an excel application is not loaded, proceed...
 
 (if (null myApp)
   (progn
     ;;;- Select an Excel workbook
     (setq fileName (getfiled "Excel Spreadsheet File" (if oldFileName oldFileName "") "XLS" )
     ;;;--- Verify the path to the file
     (setq fileName(findfile fileName))
     ;;;--- Set the default file name globally for next use
     (setq oldFileName fileName)
     (princ "\n Opening Excel file...")
     ;;;--- If Excel...
     (if(setq myapp(vlax-get-or-create-object "Excel.Application"))
       (progn
         ;;;--- Open the workbook
         (vlax-invoke-method (vlax-get-property myapp 'WorkBooks) 'Open fileName)
         ;;;--- Set it to invisible mode
         (vla-put-visible myApp 0)
         ;;;--- Get the workbooks object
         (setq myWBooks(vlax-get myApp "Workbooks"))
         ;;;--- Open the excel file
         (setq myWBook(vla-open myWBooks fileName))
         ;;;;--- Get the sheets object
         (setq mySheets(vlax-get myWBook "Sheets"))
 
         ;;;--- Get a list of the sheet names
         (princ "\n Getting Sheet Names from Excel...")
         (setq shtCnt(vla-get-count mySheets))
         (setq sheetList(list))
         (setq cnt 1)
         (while(<= cnt shtCnt)
           (setq sht(JXCL-get-item mySheets cnt))
           (setq shtName(vla-get-name sht))
           (setq sheetList(append sheetList(list shtName)))
           (setq cnt(+ cnt 1))
         )
            
         ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
         ;;;--- Let the user select a sheet  ;;;
         ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
            
         ;;;--- Put up the dialog box
         (setq dcl_id (load_dialog "XL_GET.dcl"))

         ;;;--- See if it is already loaded
         (if (not (new_dialog "XL_GET" dcl_id))
           (progn
             (setq str "The XL_GET.DCL file was not found!")
             (setq str(strcat str "\n\nSee help file on website for more information."))
             (setq str(strcat str "\n\nhttp://www.jefferypsanders.com/autolisp_XL_Help.html"))
             (setq str(strcat str "\n\nSee error: XL03; error: quit / exit abort"))
             (alert str)              
             (exit)
           )
         )
         ;;;--- Add the layer names to the dialog box
         (start_list "sheetlist" 3)
         (mapcar 'add_list sheetList)
         (end_list)
         ;;;--- Set the first sheet as default
         (set_tile "sheetlist" "0")
         ;;;--- If an action event occurs, do this function
         (action_tile "cancel" "(setq ddiag 1)(done_dialog)")
         (action_tile "accept" "(setq ddiag 2)(saveVars)(done_dialog)") 
         ;;;--- Display the dialog box
         (start_dialog)
 
         ;;;--- If the "Okay" button was pressed
         (if (= ddiag 2)
           (progn
             ;;;--- Get the selected worksheet
             (setq mySht(vlax-get-property mySheets 'Item sheetName))
             ;;;--- Make the selected worksheet active
             (vlax-invoke-method mysht "Activate")
             ;;;--- Get the used range of the worksheet
             (setq myRange(vlax-get-property mySht 'UsedRange))
             ;;;--- Get the cells from the range
             (setq myCells(vlax-get-property myRange 'Cells))
             ;;;--- Get the addresses of the top left and bottom right cells
             (setq myAddress(JXCL-get-address myRange myCells 2 3 1))
             ;;;--- Strip off the workbook and worksheet names
             (setq myAddress(substr myAddress (+ 3 (vl-string-search "!" myAddress))))
             ;;;--- Get the starting row number of the range
             (setq 1stRow(fix(atof myAddress)))
             ;;;--- Get the starting column number of the range
             (setq 1stCol(fix(atof (substr myAddress (+ (strlen (itoa 1stRow)) 2)))))
             ;;;--- Strip off the first row and column from the address string
             (setq myAddress(substr myAddress (+ 3 (vl-string-search ":" myAddress))))
             ;;;--- Get the ending row number of the range
             (setq LstRow(fix(atof myAddress)))
             ;;;--- Get the ending column number of the range
             (setq LstCol(fix(atof (substr myAddress (+ (strlen (itoa LstRow)) 2)))))
             ;;;--- Get the data by row into a list
             (princ "\n Getting Excel Data...\n")
             ;;;--- Get each cells value and store it in a list
             (setq dataList (getRangeCells 1stRow 1stCol LstRow LstCol))
             
             (princ "\n Exiting EXCEL...")
             (princ)
             ;;;--- Shut Excel down
             (cond
               (
                 (not(vlax-object-released-p myApp))
                 (vlax-invoke-method myApp 'QUIT)
                 (vlax-release-object myApp)
               )
             )
           )
         )                             
       )
       
       (alert "Could not start Excel application!")
       
     )                                 
   )
   
   (alert "Could not Find the Excel Object Library!")
   
 )
 (princ "\n Returning control to XL.lsp...")
 ;;;--- Return the list of data from Excel
 dataList
) 

(defun XL_PutTxt(dataList)
 ;;;--- Set up a revised text counter
 (setq textCnt 0)
 ;;;--- Update progress
 (princ "\n Gathering text entities...")
 ;;;--- Get a selection set of all text entities
 (if(setq eset(ssget "X" (list (cons 0 "TEXT"))))
   (progn
     ;;;--- Set up a counter
     (setq cntr 0)
     ;;;--- Update progress
     (princ "\n Processing text entities...")
     ;;;--- Cycle through each entity
     (while (< cntr (sslength eset))
       ;;;--- Get the name of the first entity
       (setq en(ssname eset cntr))
       ;;;--- Get the dxf group codes of the entity
       (setq enlist(entget en))
       ;;;--- Get the old text value
       (setq oldVal(cdr(assoc 1 enlist)))
       ;;;--- See if has a match
       (foreach a dataList
         (if(= (strcase oldVal)(strcase (car a)))
           (progn
             (cond
               ((= nil (cadr a))(setq newVal " "))
               ((= (read "REAL") (type (cadr a)))(setq newVal(vl-princ-to-string (cadr a))))
               ((= (read "INT") (type (cadr a))) (setq newVal(itoa (cadr a))))
               (T (setq newVal (cadr a)))
             )  
             (setq enlist(subst (cons 1 newVal)(assoc 1 enlist)enlist))
             (entmod enlist)
             (entupd en)
             (princ (strcat "\n Changed Value of Text From: " oldVal " To: " newVal))
             (setq textCnt(+ textCnt 1))
           )
         )
       )
       (setq cntr(+ cntr 1))
     )  
   )
   (alert "No text found in drawing!")
 )
 (princ (strcat "\n Revised (" (itoa textCnt) ") text entities!"))
 (princ)
)

Edited by fuccaro
Adding CODE tags
Link to comment
Share on other sites

@loveboatcaptain: Your code excerpt is really huge; please wrap it in tags. Also, can you point exactly where did you have that issue? Thank you.

 

Regards,

Mircea

Link to comment
Share on other sites

I don't know if I understand your issue completely, I'd have to re-read the post in more detail. But I do know that Jeffery Sanders is a very cool guy and if you send him an e-mail with the word LISP included somewhere in the subject line, he will usually get back to you with a reply to questions in a reasonable time. He has helped me several times and since it's his code he would probably be the best source for information.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...