Jump to content

dwg to xls routine?


edgar_hj1

Recommended Posts

Hello everybody,

I am looking for a routine that helps me exporting a table drawing to excel. I have a lot of those drawings...

 

Thanks!!!

Link to comment
Share on other sites

  • Replies 27
  • Created
  • Last Reply

Top Posters In This Topic

  • ReMark

    7

  • edgar_hj1

    5

  • Lee Mac

    3

  • The Buzzard

    2

Could you post what you have written?

 

Or, if you are requesting this routine, send me an email, and we can see about coming to some sort of arrangement. :)

 

Lee

Link to comment
Share on other sites

Could you post what you have written?

 

Or, if you are requesting this routine, send me an email, and we can see about coming to some sort of arrangement. :)

 

Lee

 

 

Thanks for answering Lee... My email is edgar_hj1@hotmail.com

Link to comment
Share on other sites

Edgar, I would advise that you remove your email address from the post, as you may receive spam emails if it is on the forum.

 

In future, use the PM service available from clicking on a user name :)

Link to comment
Share on other sites

I just posted this in another forum, but meant to post it here.

 

Try this!

 

Crusty, I should have explained better what I need. Your lisp routine is very useful but it is not exactly what I need. I am going to try a better explanation.

Someone sent me a drawing in wich there are tables. They are not objects with links to excel elements, they are only lines and text. What I need is a routine that sends text in this drawings to excel in columns and rows in the exact position they are drawn in the CAD drawing.

Is that posible?...

Can someone help me with that?

Link to comment
Share on other sites

Crusty, I should have explained better what I need. Your lisp routine is very useful but it is not exactly what I need. I am going to try a better explanation.

Someone sent me a drawing in wich there are tables. They are not objects with links to excel elements, they are only lines and text. What I need is a routine that sends text in this drawings to excel in columns and rows in the exact position they are drawn in the CAD drawing.

Is that posible?...

Can someone help me with that?

 

Try the command TABLEEXPORT

 

Exports data from a table object in CSV file format

Shortcut menu: With a table selected, right-click and click Export.

 

Command entry: tableexport

A standard file selection dialog box is displayed. Table data is exported in the comma-separated (CSV) file format. All formatting of the table and its text is lost.

Link to comment
Share on other sites

Buzzard, tableexport command works only for table elements (with table properties), what I have is just lines and text in specific positions.

Thanks anyway!

Link to comment
Share on other sites

Buzzard, tableexport command works only for table elements (with table properties), what I have is just lines and text in specific positions.

Thanks anyway!

 

So it just looks like a table, But it really is not.

I only went on the information you supplied.

 

Sorry

Link to comment
Share on other sites

Buzzard, tableexport command works only for table elements (with table properties), what I have is just lines and text in specific positions.

Thanks anyway!

 

I also thought your problem involved an ACAD_Table entity, if it is just a collections of lines and text, then the difficulty of the problem is increased somewhat...

Link to comment
Share on other sites

  • 1 year later...
Crusty, I should have explained better what I need. Your lisp routine is very useful but it is not exactly what I need. I am going to try a better explanation.

Someone sent me a drawing in wich there are tables. They are not objects with links to excel elements, they are only lines and text. What I need is a routine that sends text in this drawings to excel in columns and rows in the exact position they are drawn in the CAD drawing.

Is that posible?...

Can someone help me with that?

Try this one from my code library

;; Import plain table without complex title row  ;;
;; into the newly created Excel file ;;
;; written by Fatty T.O.H. (c) 2007 * all rights released ;;
;; edited 8/20/08
;; edited 8/26/10

(vl-load-com)

(defun rem-dups (mylist / newlst)
 ;; remove duplicates
 ;; published by hutch
 (foreach item mylist
   (and (null (member item newlst))
 (setq newlst (cons item newlst))
   )
 )
 newlst
)
(defun get-contents (/ data en i ip output p1 p2 rowlist ss tmp txt ylist)
 (setq data nil);debug only
 (alert "Select table by window selection\n\twithout title")
 (setq p1 (getpoint "\nSpecify the first corner point: >> ")
p2 (getcorner p1 "\nSpecify the opposite corner point: >> ")
)
 (setq ss (ssget "W" (list (car p1)(cadr p1)) (list (car p2)(cadr p2)) (list (cons 0 "TEXT")))
i  -1
 )
 (repeat (sslength ss)
   (setq en   (ssname ss (setq i (1+ i)))
  ip   (cdr (assoc 10 (entget en)))
  txt  (cdr (assoc 1 (entget en)))
  tmp  (cons txt ip)
  data (cons tmp data)
   )
 )
 (setq ylist (mapcar 'caddr data)
ylist (rem-dups ylist)
ylist (vl-sort ylist (function (lambda (a b) (> a b))))
 )
 (repeat (length ylist)
   (setq rowlist (vl-remove-if-not
     (function (lambda (x)
   (equal (caddr x) (car ylist) 0.1)
        )
     )
     data
   )
  rowlist (vl-sort rowlist
     (function (lambda (a b) (< (cadr a) (cadr b))))
   )
   )
   (setq output (append output (list rowlist)))
   (setq ylist (cdr ylist))
 )
 (setq output (mapcar (function (lambda (x)
      (mapcar 'car x)
    )
        )
        output
       )
 )
 (reverse (rem-dups  output))
)
;; *** main part *** ;;
(defun C:ETT (/       adoc    aexc    awb     axss    cll     col
      colm    csht    rowdata excel_list      nwb     osm
      rang    row     sht
     )
 (vl-load-com)
 (setq excel_list (get-contents))
 (setq fname (getstring T "\nEnter an Excel file name without XLS extension: "))
 (setq aexc (vlax-get-or-create-object "Excel.Application")
awb  (vlax-get-property aexc "Workbooks")
nwb  (vlax-invoke-method awb "Add")
sht  (vlax-get-property nwb "Sheets")
csht (vlax-get-property sht "Item" 1)
cll  (vlax-get-property csht "Cells")
 )
 (vlax-put-property csht 'Name "Table")
 (vla-put-visible aexc :vlax-false)
 (setq n   1
num (length (car excel_list))
 )
 (repeat num
   (setq row 1
  colm n
   )
   (setq rowdata (mapcar 'car excel_list))
   (repeat (length rowdata)
     (vlax-put-property
cll
"Item"
row
colm
(vl-princ-to-string (car rowdata))
     )
     (setq row (1+ row))
     (setq rowdata (cdr rowdata))
   )
   (setq excel_list (mapcar 'cdr excel_list))
   (setq row (1+ row)
  n   (1+ n)
   )
 )
 (setq rang (vlax-get-property csht "UsedRange"))
 ;; Horizontal alignment Left(Indent) :
 (vlax-put-property
   rang
   "Horizontalalignment"
   (vlax-make-variant -4131 3)
 )
 ;; Vertical alignment Center :
 (vlax-put-property
   rang
   "VerticalAlignment"
   (vlax-make-variant -4108 3)
 )
 ;; Set text format :
 (vlax-put-property
   rang
   "NumberFormat"
   (vlax-make-variant "@" ; <-- text format (use "0,000" for number format)
 )
 (vlax-invoke-method (vlax-get-property csht "Columns") "AutoFit")

 (vlax-invoke-method
   nwb
   'SaveAs
   (strcat (getvar "dwgprefix")
    fname 
   )
   -4143
   nil
   nil
   :vlax-false
   :vlax-false
   1
   2
 )
 (vlax-invoke-method aexc "Quit")
 (mapcar (function (lambda (x)
       (vl-catch-all-apply
  (function (lambda ()
       (progn
         (vlax-release-object x)
         (setq x nil)
       )
     )
  )
       )
     )
  )
  (list cll csht rang sht nwb awb aexc)
 )
 (gc)
 (alert (strcat "File saved as: " (strcat (getvar "dwgprefix")
    fname ".xls"
   )))
 (princ)
)

 

~'J'~

Link to comment
Share on other sites

Guest balajibth84

Hai i have tried this lisp with this attached dwg...But some error is coming..Can u see????i am using cad 2007 version...This is for Dwg table export to Excel format????

 

 

Here *Excel Application Not Found* ; error: no function definition:

XLP-GET-RANGE this error is coming....PLease can you clarify......

 

 

Thanks in Advance...

Link to comment
Share on other sites

In the header of the code it mentions importing into a newly created Excel spreadsheet. Does that mean the spreadsheet has to exist in some form even if it is empty?

Link to comment
Share on other sites

Guest balajibth84

Hai hw to use this lisp??????This is for import from excel????Please clarify how to use this lisp...What is the input and output.....

 

 

Try this one from my code library

;; Import plain table without complex title row  ;;
;; into the newly created Excel file ;;
;; written by Fatty T.O.H. (c) 2007 * all rights released ;;
;; edited 8/20/08
;; edited 8/26/10

(vl-load-com)

(defun rem-dups (mylist / newlst)
 ;; remove duplicates
 ;; published by hutch
 (foreach item mylist
   (and (null (member item newlst))
 (setq newlst (cons item newlst))
   )
 )
 newlst
)
(defun get-contents (/ data en i ip output p1 p2 rowlist ss tmp txt ylist)
 (setq data nil);debug only
 (alert "Select table by window selection\n\twithout title")
 (setq p1 (getpoint "\nSpecify the first corner point: >> ")
p2 (getcorner p1 "\nSpecify the opposite corner point: >> ")
)
 (setq ss (ssget "W" (list (car p1)(cadr p1)) (list (car p2)(cadr p2)) (list (cons 0 "TEXT")))
i  -1
 )
 (repeat (sslength ss)
   (setq en   (ssname ss (setq i (1+ i)))
  ip   (cdr (assoc 10 (entget en)))
  txt  (cdr (assoc 1 (entget en)))
  tmp  (cons txt ip)
  data (cons tmp data)
   )
 )
 (setq ylist (mapcar 'caddr data)
ylist (rem-dups ylist)
ylist (vl-sort ylist (function (lambda (a b) (> a b))))
 )
 (repeat (length ylist)
   (setq rowlist (vl-remove-if-not
     (function (lambda (x)
   (equal (caddr x) (car ylist) 0.1)
        )
     )
     data
   )
  rowlist (vl-sort rowlist
     (function (lambda (a b) (< (cadr a) (cadr b))))
   )
   )
   (setq output (append output (list rowlist)))
   (setq ylist (cdr ylist))
 )
 (setq output (mapcar (function (lambda (x)
      (mapcar 'car x)
    )
        )
        output
       )
 )
 (reverse (rem-dups  output))
)
;; *** main part *** ;;
(defun C:ETT (/       adoc    aexc    awb     axss    cll     col
      colm    csht    rowdata excel_list      nwb     osm
      rang    row     sht
     )
 (vl-load-com)
 (setq excel_list (get-contents))
 (setq fname (getstring T "\nEnter an Excel file name without XLS extension: "))
 (setq aexc (vlax-get-or-create-object "Excel.Application")
awb  (vlax-get-property aexc "Workbooks")
nwb  (vlax-invoke-method awb "Add")
sht  (vlax-get-property nwb "Sheets")
csht (vlax-get-property sht "Item" 1)
cll  (vlax-get-property csht "Cells")
 )
 (vlax-put-property csht 'Name "Table")
 (vla-put-visible aexc :vlax-false)
 (setq n   1
num (length (car excel_list))
 )
 (repeat num
   (setq row 1
  colm n
   )
   (setq rowdata (mapcar 'car excel_list))
   (repeat (length rowdata)
     (vlax-put-property
cll
"Item"
row
colm
(vl-princ-to-string (car rowdata))
     )
     (setq row (1+ row))
     (setq rowdata (cdr rowdata))
   )
   (setq excel_list (mapcar 'cdr excel_list))
   (setq row (1+ row)
  n   (1+ n)
   )
 )
 (setq rang (vlax-get-property csht "UsedRange"))
 ;; Horizontal alignment Left(Indent) :
 (vlax-put-property
   rang
   "Horizontalalignment"
   (vlax-make-variant -4131 3)
 )
 ;; Vertical alignment Center :
 (vlax-put-property
   rang
   "VerticalAlignment"
   (vlax-make-variant -4108 3)
 )
 ;; Set text format :
 (vlax-put-property
   rang
   "NumberFormat"
   (vlax-make-variant "@" ; <-- text format (use "0,000" for number format)
 )
 (vlax-invoke-method (vlax-get-property csht "Columns") "AutoFit")

 (vlax-invoke-method
   nwb
   'SaveAs
   (strcat (getvar "dwgprefix")
    fname 
   )
   -4143
   nil
   nil
   :vlax-false
   :vlax-false
   1
   2
 )
 (vlax-invoke-method aexc "Quit")
 (mapcar (function (lambda (x)
       (vl-catch-all-apply
  (function (lambda ()
       (progn
         (vlax-release-object x)
         (setq x nil)
       )
     )
  )
       )
     )
  )
  (list cll csht rang sht nwb awb aexc)
 )
 (gc)
 (alert (strcat "File saved as: " (strcat (getvar "dwgprefix")
    fname ".xls"
   )))
 (princ)
)

 

~'J'~

Link to comment
Share on other sites

Guest balajibth84

Can you give the codes for table import from excels to dwg ----or----- table export from cad to excel..Because we are using tables for details.....can you help me......I am using cad 2007.......i am expect the lisp....

Link to comment
Share on other sites

I see you are using Mech 2007. I just did a test using plain vanilla 2007 whereby I created a table using the TABLE command in AutoCAD consisting of 5 columns and 14 rows. I populated each column heading and put whole numbers in each cell. I used the TABLEEXPORT command to export the data in a comma-separated (CSV) file format. I double clicked on the .CSV file I created and Microsoft Excel (2003) opened up and there was my main header (title) for the table, the five columns and all the values I had placed in each cell. So, as you see IF you have a true AutoCAD TABLE in your drawing the data can be exported and easily used in Excel. This is all done without the use of any lisp code.

Edited by ReMark
Link to comment
Share on other sites

Regarding the lisp routine you keep asking about that was posted here.

 

Copy the contents and paste it into a Notepad .txt file and call it Export Table.lsp .

 

In your AutoCAD drawing that has the table-like objects (lines and text) type APPLOAD and press Enter.

 

When the Load/Unload Applications window appears Find and Load the lisp file mentioned previously.

 

AutoCAD will tell you it successfully loaded. Close the window.

 

At the command line type the letters ETT . Note: these letters appeared in the code. How did you miss them?

 

Follow the onscreen prompts for selecting the objects (use a window) and for naming your new Excel spreadsheet file (do not use the XLS file type in the name).

 

The XLS file will be created and AutoCAD will tell you where it can be found. Go to that folder and open the file with Excel. Your data should be there.

 

I tested this using Auto2010 and MS Office 2003. It should work in AutoCAD 2007 too.

Link to comment
Share on other sites

Guest balajibth84
I see you are using Mech 2007. I just did a test using plain vanilla 2007 whereby I created a table using the TABLE command in AutoCAD consisting of 5 columns and 14 rows. I populated each column heading and put whole numbers in each cell. I used the TABLEEXPORT command to export the data in a comma-separated (CSV) file format. I double clicked on the .CSV file I created and Microsoft Excel (2003) opened up and there was my main header (title) for the table, the five columns and all the values I had placed in each cell. So, as you see IF you have a true AutoCAD TABLE in your drawing the data can be exported and easily used in Excel. This is all done without the use of any lisp code.

 

Dear Remark....i am understood what you are said ......Actually this is also good..And one thing we want present the table with attraction....Like top heading column text is some what height with one colur...And table remaining Text is different color with different font size....So as per this need i want to explode the table first..Suppose i am exploded the table means i cannt able to export to excel format......So hw can i create the table in cad.....with different font size and colour..So only i am asked the lisp for this....For export to excel.....Or we can ready to import also from excel..So any one i am expect from you..Can you clarify??????????

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...