AutoCAD, Python, Pandas and excel

 This is a revision from my last post on pandas. This revised code will write the contents to Excel instead of of the command line. The data could be then imported as a datalink

 

import PyRx as Rx

import PyGe as Ge

import PyGi as Gi

import PyDb as Db

import PyAp as Ap

import PyEd as Ed

import traceback

 

import pandas as pd

#openpyxl or xlsxwriter

 

#get the attribues and return them as a list

def getAttValues(ref: Db.BlockReference)->list[str]:

    v = [ref.getBlockName()]

    for attrefid in ref.attributeIds():

        attref = Db.AttributeReference(attrefid)

        match attref.tag():

            case 'PART#':

                v.append(attref.textString())

            case 'DESCRIPTION':

                v.append(attref.textString())

            case _:

                pass

    return v

 

def PyRxCmd_doit():

    try:

        db = Db.curDb()

 

        #define our columns

        data = {'Name': [],

                'PART#': [],

                'DESCRIPTION': [],

                'QTY': []}

 

        #search for blocks that start with 'SPK'

        bt = Db.BlockTable(db.blockTableId())

        spkIds = [id for (n, id) in bt.toDict().items() if n.startswith('SPK')]

 

        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['Name'].append(values[0].rstrip('.dwg'))

                data['PART#'].append(values[1])

                data['DESCRIPTION'].append(values[2])

                data['QTY'].append(1) #we know we have one

 

        #create the dataframe, then group by

        df = pd.DataFrame(data)

        dfgr = df.groupby(['Name', 'PART#', 'DESCRIPTION'],

                         sort=False, as_index=False).agg({'QTY': 'sum'})

       

        with pd.ExcelWriter('e:\\pandas_to_excel.xlsx') as writer:

            dfgr.to_excel(writer, sheet_name='sheet1')

 

    except Exception as err:

        traceback.print_exception(err)