export table with blocks to excel with Python

[aridzv]

@Danielm103

about getting effective *U block name in autocd,

try adding this line to my geteffectivenamebr lisp:

(setq bname (vla-get-effectivename (vlax-ename->vla-object selblk)))

 

[Danielm103]

getEffectiveNameFromBtrId helper works for AutoCAD, however BricsCAD doesn’t have a direct API to do this for parametric blocks, I’m investigating alternatives.

 

vla-get-effectivename, "EffectiveName~Native" are designed to give us the name from a block reference

 

blk = table.blockTableRecordId(cell.row, cell.column)

 

gives us a Block table record id, not a reference

I have a working solution with the help of Bricsys support that should give us what we need, it’s a bit of a kludge though

[aridzv]

Thanks!!!

 

hope bricscad support will help.

for me at least there is no pressure.

 

Right now we know how to build a table in AutoCAD that contains values that Python knows how to work with.

 

and I do belive bricscad will help.

 

aridzv.

[aridzv]

@Danielm103

I need help with converting the code to work with "xlsxwriter" instand of with "openpyxl" Because I think it will solve the problem of the image aligment in the cells.

 

I started with it,

But for starters I think I'm stuck with the "else" statment in row 67.

can you please have a look on the code below?

 

thanks,

aridzv.

 

#https://www.cadtutor.net/forum/topic/97450-export-table-with-blocks-to-excel-with-python/
from pyrx import Rx, Ge, Gi, Gs, Db, Ap, Ed, Ax
import traceback

#from openpyxl.drawing.image import Image as xlImage
#from openpyxl.utils.cell import get_column_letter
#from openpyxl.styles import Alignment
import xlsxwriter
#import openpyxl as xl
import pathlib
import ctypes
#import wx


@Ap.Command()
def Py_tablewithimagetoexcelxlswriter():
    try:

        db = Db.curDb()

        ps, id, _ = Ed.Editor.entSel("\nSelect a table: ", Db.Table.desc())
        if ps != Ed.PromptStatus.eOk:
            raise RuntimeError("Selection Error! {}: ".format(ps))

        # paths
        xlpath = pathlib.Path(db.getFilename()).parent
        xlname = pathlib.Path(db.getFilename()).stem
        fpt = "{}\{}.xlsx".format(xlpath, xlname)
        wb = xlsxwriter.Workbook("hello.xlsx")#create workbook
        ws = workbook.add_worksheet()#create worksheet in workbook
        ws.set_default_row(15)#set default row height to 39.6
        ws.set_column(2, 2, 15.44)#set columnn B width (where the images will be) to 15.44
        cell_format = ws.add_format()# add format 
        cell_format.set_align('center')
        cell_format.set_align('vcenter')
        
        #wb = xl.Workbook()
        #ws = wb.active
        table = Db.Table(id)
        opts = Db.TableIteratorOption.kTableIteratorSkipMerged
        for cell in table.cells(opts):

            # format all cells
            currentCell = ws.cell(cell.row + 1, cell.column + 1)
            currentCell.alignment = Alignment(horizontal="center", vertical="center")

            if table.cellType(cell.row, cell.column) == Db.CellType.kBlockCell:
                blk = table.blockTableRecordId(cell.row, cell.column)
                bname = getEffectiveNameFromBtrId(blk)
                img: wx.Image = Gs.Core.getBlockImage(blk, 495, 495, 1.0, [0, 0, 0])
                img.SetMaskColour(0, 0, 0)
                img.SetMask(True)
                imgpath = "{}/{}.png".format(xlpath, bname)#blk.handle()
                img.SaveFile(imgpath, wx.BITMAP_TYPE_PNG)
                xlimg = xlImage(imgpath)
                xlimg.width = 64
                xlimg.height = 64
                cellref = "{}{}".format(get_column_letter(cell.column + 1), cell.row + 1)
                
                #ws.add_image(xlimg, cellref)
                ws.set_row(cell.row + 1, 39.6)
                ws.insert_image(cellref, xlimg, {'x_offset': 0, 'y_offset': 0, 'x_scale': 0.8, 'y_scale': 0.8})
                #ws.row_dimensions[cell.row + 1].height = 64
                
############################################################################################                
            else:
                ws.cell(
                    row=cell.row + 1,
                    column=cell.column + 1,
                    value=table.textString(cell.row, cell.column),
                )
############################################################################################

        #nested function
        def as_text(value):
            if value is None:
                return ""
            return str(value)

        # make another pass to set the widths + a little extra
        for column_cells in ws.columns:
            length = max(len(as_text(cell.value)) for cell in column_cells)
            ws.column_dimensions[column_cells[0].column_letter].width = length * 1.25

        #wb.save("{}/{}.xlsx".format(xlpath, xlname))
        wb.close()
        ctypes.windll.user32.MessageBoxW(0, fpt , "Files Saved", 1)
    except Exception as err:
        traceback.print_exception(err)



def getEffectiveNameFromBtrId(btrid: Db.ObjectId):
    rec = Db.BlockTableRecord(btrid)
    if rec.isAnonymous() and rec.hasXData("AcDbBlockRepBTag"):
        for dfx, val in rec.xData("AcDbBlockRepBTag"):
            if dfx == 1005:
                hnd = Db.Handle(val)
                dynid = rec.database().tryGetObjectId(False, hnd)
                dynrec = Db.BlockTableRecord(dynid)
                return dynrec.getName()
    return rec.getName()

 

Edited April 20 by aridzv

[Danielm103]

I’ve never used xlsxwriter, here’s my shot but I don’t see that it formats the image better

 

from pyrx import Rx, Ge, Gi, Gs, Db, Ap, Ed, Ax, Brx
import traceback
import pathlib
import xlsxwriter
import wx


@Ap.Command()
def doit():
    try:
        db = Db.curDb()
        ps, id, _ = Ed.Editor.entSel("\nSelect a table: ", Db.Table.desc())
        if ps != Ed.PromptStatus.eOk:
            raise RuntimeError("Selection Error! {}: ".format(ps))

        xlpath = pathlib.Path(db.getFilename()).parent
        xlname = pathlib.Path(db.getFilename()).stem
        fpt = "{}/{}.xlsx".format(xlpath, xlname)
        wb = xlsxwriter.Workbook(fpt)  # create workbook
        ws = wb.add_worksheet()  # create worksheet in workbook
        ws.set_default_row(40)  # set default row height to 39.6
        cell_format = wb.add_format()  # add format
        cell_format.set_align("center")
        cell_format.set_align("vcenter")
    
        table = Db.Table(id)
        opts = Db.TableIteratorOption.kTableIteratorSkipMerged
        for cell in table.cells(opts):
            if table.cellType(cell.row, cell.column) == Db.CellType.kBlockCell:
                
                ws.set_column(cell.column,cell.column, 15.44, cell_format)  
                
                blk = table.blockTableRecordId(cell.row, cell.column)
                bname = getEffectiveNameFromBtrId(blk)
                img: wx.Image = Gs.Core.getBlockImage(blk, 64, 64, 1.0, [0, 0, 0])
                img.SetMaskColour(0, 0, 0)
                img.SetMask(True)
                imgpath = "{}/{}.png".format(xlpath, bname)
                img.SaveFile(imgpath, wx.BITMAP_TYPE_PNG)
                ws.insert_image(
                    cell.row,
                    cell.column,
                    imgpath,
                    {"x_offset": 0, "y_offset": 0, "x_scale": 0.8, "y_scale": 0.8},
                )
            else:
                val = table.textString(cell.row, cell.column)
                #try to get a text width
                w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))
                ws.set_column(cell.column,cell.column, w, cell_format)  
                ws.write(cell.row, cell.column, table.textString(cell.row, cell.column))

        wb.close()

    except Exception as err:
        traceback.print_exception(err)


def getEffectiveNameFromBtrId(btrid: Db.ObjectId):
    rec = Db.BlockTableRecord(btrid)
    if rec.isAnonymous():
        ids = rec.getBlockReferenceIds()
        if len(ids) > 0 and Brx.DbProperties.isValid(ids[0], "EffectiveName~Native"):
            val = Brx.DbProperties.getValue(ids[0], "EffectiveName~Native")
            return val.getString()
    return rec.getName()

 

[aridzv]

37 minutes ago, Danielm103 said:

I’ve never used xlsxwriter, here’s my shot but I don’t see that it formats the image better

 

 

 

yes,

it dosn't...

thanks any way!!

 

aridzv.

[Danielm103]

It does seem to give more control, I would consider renaming the column BLOCK PREVIWE, to something shorter, or less than the size of the icon, maybe just IMAGE, or BLOCK

 

Edited April 20 by Danielm103

[aridzv]

@Danielm103

Can you explain this line to me? I did a search on "Python xlsxwriter table.calcTextExtents" but didn't find anything....

w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))

 

thanks...

[Danielm103]

4 hours ago, aridzv said:

@Danielm103

Can you explain this line to me? I did a search on "Python xlsxwriter table.calcTextExtents" but didn't find anything....

w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))

 

thanks...

It’s a helper method in PyRx to help build tables. The idea is, when you need to build a table inside CAD, but you don’t know what to set the column width to.

 

w, h = table.calcTextExtents(“This is a long text item”, TextStyleId) will compute the actual size of the text in drawing units

 

it may not be applicable in the context since excel has different units

[aridzv]

@Danielm103

 

1. about aligning the image to the center of the cell:

I changed the X offset value (A process of trial and error and depends on the length of the header text):

{"x_offset": 35, "y_offset": 0, "x_scale": 0.8, "y_scale": 0.8}

 

2. the first column was invisible because "val" for those cells was empty after the header's row and those tow lines return empty string after the first row which set the column width to 0:

w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))
ws.set_column(cell.column,cell.column, w, cell_format) 

 

so I did this change with "autofit":

	else:
		#val = table.textString(cell.row, cell.column)
		#try to get a text width
		#w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))
		ws.set_column(cell.column,cell.column, 1, cell_format)#minimum width,autofit wil set at the end
		ws.write(cell.row, cell.column, table.textString(cell.row, cell.column))
ws.autofit()
wb.close()

 

here is the final code I'm using:

#https://www.cadtutor.net/forum/topic/97450-export-table-with-blocks-to-excel-with-python/
from pyrx import Rx, Ge, Gi, Gs, Db, Ap, Ed, Ax, Brx
import traceback
import pathlib
import xlsxwriter
import ctypes
import wx


@Ap.Command()
def Py_tablewithimagetoexcel_xlsxwriter():
    try:
        db = Db.curDb()
        ps, id, _ = Ed.Editor.entSel("\nSelect a table: ", Db.Table.desc())
        if ps != Ed.PromptStatus.eOk:
            raise RuntimeError("Selection Error! {}: ".format(ps))

        xlpath = pathlib.Path(db.getFilename()).parent
        xlname = pathlib.Path(db.getFilename()).stem
        fpt = "{}/{}.xlsx".format(xlpath, xlname)
        wb = xlsxwriter.Workbook(fpt)  # create workbook
        ws = wb.add_worksheet()  # create worksheet in workbook
        ws.set_default_row(40)  # set default row height to 39.6
        cell_format = wb.add_format()  # add format
        cell_format.set_align("center")
        cell_format.set_align("vcenter")
    
        table = Db.Table(id)
        opts = Db.TableIteratorOption.kTableIteratorSkipMerged
        for cell in table.cells(opts):
            if table.cellType(cell.row, cell.column) == Db.CellType.kBlockCell:
                
                ws.set_column(cell.column,cell.column, 15.44, cell_format)  
                
                blk = table.blockTableRecordId(cell.row, cell.column)
                bname = getEffectiveNameFromBtrId(blk)
                img: wx.Image = Gs.Core.getBlockImage(blk, 64, 64, 1.0, [0, 0, 0])
                img.SetMaskColour(0, 0, 0)
                img.SetMask(True)
                imgpath = "{}/{}.png".format(xlpath, bname)
                img.SaveFile(imgpath, wx.BITMAP_TYPE_PNG)
                ws.insert_image(
                    cell.row,
                    cell.column,
                    imgpath,
                    {"x_offset": 35, "y_offset": 0, "x_scale": 0.8, "y_scale": 0.8},
                )
            else:
                #val = table.textString(cell.row, cell.column)
                #try to get a text width
                #w, h =table.calcTextExtents(val,table.textStyle(cell.row, cell.column))
                ws.set_column(cell.column,cell.column, 1, cell_format)#keep format,set minimum width,autofit will set at the end
                ws.write(cell.row, cell.column, table.textString(cell.row, cell.column))
        ws.autofit()
        wb.close()
        ctypes.windll.user32.MessageBoxW(0, fpt , "Files Saved", 1)
        
    except Exception as err:
        traceback.print_exception(err)


def getEffectiveNameFromBtrId(btrid: Db.ObjectId):
    rec = Db.BlockTableRecord(btrid)
    if rec.isAnonymous():
        ids = rec.getBlockReferenceIds()
        if len(ids) > 0 and Brx.DbProperties.isValid(ids[0], "EffectiveName~Native"):
            val = Brx.DbProperties.getValue(ids[0], "EffectiveName~Native")
            return val.getString()
    return rec.getName()

 

 

Thanks for all your help!!

aridzv.

Edited April 21 by aridzv

[Danielm103]

Nice! Autofit to the rescue!

 

Note that PyRx has a full GUI in wxPython (wx), so you can use that instead of ctypes if you want.

Then there’s also alert

 

from pyrx import Rx, Ge, Gi, Gs, Db, Ap, Ed, Ax, Brx
import traceback
import wx

@Ap.Command()
def doit():
    try:
        wx.MessageBox("Now you have done it")
        Ed.Core.alert("Now you have done it")
    except Exception as err:
        traceback.print_exception(err)

 

 

Welcome to Python  

[aridzv]

Thanks

 

question about what needed to improt.

how do I know what to import from PyRx?

what do all of the modules in this line do (except of brx of course)

from pyrx import Rx, Ge, Gi, Gs, Db, Ap, Ed, Ax, Brx

 

thanks,

aridzv.

Edited April 21 by aridzv

[Danielm103]

You can thing of those as name spaces of ObjectARX.

 

Rx = AcRx, the most base classes for all AutoCAD objects

Ge = AcGe, Geometry classes, Points, Vectors, Curves

Gi = AcGi , has the class AcGiDrawable, mostly responsible for drawing entities on your screen

Gs = AcGs, Graphics system, access to drawing devices and of course getBlockImage

Ap = Application level stuff such as the document manager

Db = AcDb, the database (.DWG)

Ed = AcEd, Editor, selection sets, entsel, getPoint

Ax = all of the ActiveX stuff

Br = AcBr, access to 3d geometry

Brx = stuff specific to BricsCAD. there’s also BrxBim and BrxCv

 

They are all in PyRx, and are loaded in the ARX, so its ok to just include them all

 

Also the .NET documentation is relevant, see Unmanaged to Managed Class Mappings

https://help.autodesk.com/view/OARX/2024/ENU/?guid=GUID-390A47DB-77AF-433A-994C-2AFBBE9996AE