Can lisp export attribute datas to a specific cell of excel?

[smallƑish]

The attached DWG has has Attribute Block with 3 Tags

1. Sl NO

2. LOAD

3. QUANTITY 

Can lisp export 

TAG 2. LOAD value to Column B

TAG.3. QUANTITY value to Column C 

By referring to TAG.1 Sl NO number order Excel Raw

Sample files are attached.

[Sea-Haven]

Yes. Been done many times, only thing is it will be a custom output as putting correct attribute into correct cell.

Ok the how to, select all blocks, and make a list of attributes, sort on, SL NO, then just fill in cells in a loop reading the list. Hint Putcell.

This has all the functions you need for the excel part just pull out the relevant defuns.

Do you want table creation as well ?

I will look into how to add formula to a cell so total works rather than just put a text value.

[smallƑish]

can you please share any drawings and lisp that work with excel, any simple and small? hence I can study how it works.

Thank you 

[Sea-Haven]

I would need to search more but here is a couple.

Solved: list the length of all segments of a polyline - Page 3 - Autodesk Community - AutoCAD

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/update-layer-that-named-blocks-are-o...

[smallƑish]

Atleast can we do something to do attout, but with the order of serial number? Or a AutoCAD table.

[daniel_cadext]

Sorry for the intrusion

IMHO, this is a job for Python, Openpyxl and Pandas

MS Office is not required, just create the xlsx directly.

import traceback
from pyrx_imp import Rx
from pyrx_imp import Ge
from pyrx_imp import Gi
from pyrx_imp import Db
from pyrx_imp import Ap
from pyrx_imp import Ed

import pandas as pd

#get the attribues and return them as a list
def getAttValues(ref: Db.BlockReference)->list[str]:
    v = []
    for attrefid in ref.attributeIds():
        attref = Db.AttributeReference(attrefid)
        match attref.tag():
            case 'SL':
                v.append(attref.textString())
            case 'LOAD':
                v.append(attref.textString())
            case 'QTY':
                v.append(attref.textString())
            case _:
                pass
    return v

def PyRxCmd_doit():
    try:
        db = Db.curDb()
 
        #define our columns
        data = {'SL#': [],
                'Unit Weight': [],
                'QTY': [],
                'Total KG': [],}
 
        #search for blocks 'MACHINE WEIGHT'
        bt = Db.BlockTable(db.blockTableId())
        spkIds = [id for (n, id) in bt.toDict().items() if n == 'MACHINE WEIGHT']
        
        for id in spkIds:
            btr = Db.BlockTableRecord(id)
            for refid in btr.getBlockReferenceIds():
                ref = Db.BlockReference(refid)
                values = getAttValues(ref)
                #pandas wants equal length lists
                if len(values) != 3:
                    continue
                
                data['SL#'].append(values[0])
                data['Unit Weight'].append(values[1])
                data['QTY'].append(values[2])
                total = float( float(values[1]) * float(values[2])) 
                data['Total KG'].append(total)
 
        df = pd.DataFrame(data)
        df.loc['total']= df.sum(numeric_only=True)

        #write to excel
        with pd.ExcelWriter('e:\\pandas_to_excel.xlsx') as writer:
            df.to_excel(writer, sheet_name='sheet1',index=False)
 
    except Exception as err:
        traceback.print_exception(err)

 

[Sea-Haven]

Being multi posted need to look at dwg BLOCKS also that has more common blocks in it.

Just a comment changing the attribute order would be a good idea so they match the drawn view would be my 1st step. 

Using Bricscad  Bedit Battman move SL NO with up arrow Apply & Sync.

[Sea-Haven · Accepted solution]

(defun c:wow ( / xlsetcelltext ss lst lst2 myxl str row)
;;	Thanks to fixo			;;
;;   = Set Excel cell text =    ;;
;;				;;
(defun xlsetcelltext ( row column text)
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
  (vl-catch-all-apply
    'vlax-put-property
    (list cells 'Item row column
	(vlax-make-variant (vl-princ-to-string text) vlax-vbstring)))
)

(setq ss (ssget '((0 . "INSERT"))))
(if (= ss nil)
(progn (alert "No blocks selected will exit")(exit))
)
(setq lst '())
(repeat (setq x (sslength ss))
(setq obj (vlax-ename->vla-object (ssname ss (setq x (- x 1)))))
(setq atts (vlax-invoke obj 'Getattributes))
(setq lst2 '())
(foreach att atts
(setq str (vlax-get att 'textstring))
(setq lst2 (cons str lst2))
)
(setq lst (cons (reverse lst2) lst))
)

(setq lst (vl-sort lst
	 '(lambda (a b)
	    (cond
	      ((< (car a) (car b)))
	      ((= (car a) (car b)) (< (cadr a) (cadr b)))
	    )
	  )
     )
)

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vlax-invoke-method (vlax-get-property myXL 'WorkBooks) 'Add)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)


(setq row 1)
(xlsetcelltext 1 1 "Room")
(xlsetcelltext 1 2 "SL")
(xlsetcelltext 1 3 "Table")
(xlsetcelltext 1 4 "CHAIR")

(setq row 2)
(foreach val lst
(xlsetcelltext row 1 (nth 0 val))
(xlsetcelltext row 2 (nth 1 val))
(xlsetcelltext row 3 (nth 2 val))
(xlsetcelltext row 4 (nth 3 val))
(setq row (1+ row))
)

(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))

(princ)
)
(c:wow)

[smallƑish]

@Sea-Haven 

Thank you so much for the code, Looks like much more complicated stuff is included in it. Hope many users can take the code as a reference to develop code as per their requirements referring to it. Again many thanks.