loveboatcaptain Posted February 8, 2012 Share Posted February 8, 2012 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. Quote Link to comment Share on other sites More sharing options...
BlackBox Posted February 8, 2012 Share Posted February 8, 2012 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? Quote Link to comment Share on other sites More sharing options...
loveboatcaptain Posted February 8, 2012 Author Share Posted February 8, 2012 (edited) 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 February 9, 2012 by fuccaro Adding CODE tags Quote Link to comment Share on other sites More sharing options...
MSasu Posted February 8, 2012 Share Posted February 8, 2012 @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 Quote Link to comment Share on other sites More sharing options...
Bill Tillman Posted February 8, 2012 Share Posted February 8, 2012 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.