Help with Creating a Lisp that will change the Cell Styles of a Table

[logan_ownbey5UKQZ]

I am seeking a Lisp routine that can modify the cell styles of a table that is linked from Excel. Although I have established a custom table style, the cells in the linked table do not reflect these styles, necessitating manual adjustments. My goal is to create a Lisp routine that will automatically apply the appropriate styles to these cells.

I am uncertain whether this is feasible or if there might be a more straightforward solution. I have attempted to prevent the linked data from adopting Excel's formatting; however, the presence of numerous merged cells complicates the process. This requires unlocking the cells, merging them, and then re-merging them after any updates to the link.

[logan_ownbey5UKQZ]

I'm still on the hunt for a solution to this. If anyone has any ideas, I'm all ears!

[Sea-Haven]

You can change say column widths, row height, text height, merge cells and so on. So what do you want to do ?

Post a dwg with a before and after showing how you want it changed.

If you just want a copy of the Excel and not auto update that can be done much easier. Otherwise will need to read the excel to get info like merge cells. 

[logan_ownbey5UKQZ]

Basically, when the table is linked, it does not apply the cell styles of the table, it puts them on "By row/column". I would like the Title to be on Title, Header on Header and Data on Data. There is also 2 cells that need the top line of the boarder removed, they are hatched solid for reference.

As for the width of the cells and height, I have applied some cell sizes to some of the cells but if the cell sizes can also be applied to a lisp, i can go back and modify the number to fit what we are needing.

[Sea-Haven]

You can set the background color of a cell. You used a hatch.

This should work but something about the table stops it working. Tested on a new table works. You can set column 0 but not any other. This worked for the Merged cell as its row 1 column 0.

(setq colObj (vlax-create-object (strcat "AutoCAD.AcCmColor." (substr (getvar "ACADVER") 1 2))))
(vla-setRGB colObj 55 55  55)
(Vla-SetCellBackgroundColor obj 1 0 colobj) ; works in sample
(Vla-SetCellBackgroundColor obj 1 2 colobj) ; does not work in sample but ok new table.

Hopefully some one else may find an answer, will keep looking. 

[logan_ownbey5UKQZ]

Yeah i had to do it in a hurry, the 2 hatched cells are the ones that need the top line of the border turned off, i hatched it to show which cells need the border changed. the rest of the cells need to be put on the correct cell style.

I appreciate any help as i am looking also, if it is an issue with the table its self or the excel table i can try to resolve those issues as well but i am not sure what i would be looking for yet.

[Sea-Haven]

Have a look at this gives lots of clues. Maybe Vla-SetCellGridVisibility not tested.

[logan_ownbey5UKQZ]

Thank you for the list, could you write a lisp that uses some of this to set the cell style to the appreciate style and may be resize the cells? I'm very new to writing a lisp and not exactly sure how to go about it. It would be nice if a window opened allowing the user to use buttons to select which cells is what style and what the width of the cell should be.

[logan_ownbey5UKQZ]

Attached is what i have comeup with using the help of Grok. However, i cant seem to get it to select a group of cells or rows to apply a specific style to them. It reads all the table styles but not cell styles. I need it to also when selecting the cell that it opens the table cell ribbon.

[Sea-Haven]

Can you post your Excel file don't need data just a few lines ie Title and header plus some data. Need it as no connection to sample dwg "data link not found". For data there is one method can read all the strings and get the length of the strings use that with a fuzz factor for width, it may though be better to read the width in Excel of the columns. 

[logan_ownbey5UKQZ]

Please see attached. 

[Sea-Haven]

Thank you for the Excel, what I have found is a couple of things, one copy and paste your relevant cells to a new Excel and save as the one provided takes a long time to be read as it is looking at all those empty cells. 

More important I tried a couple of settings when making the datalink and it some how locks the Table object so you can not change the cells. A new table works correctly can merge cells etc. (vla-mergecells objtable rowmin rowmax colmin colmax).

So unfortunately not sure how to get around this problem. Maybe some one else will know how. 

[logan_ownbey5UKQZ]

Alright, i appreciate the help, hopefully someone else might know a solution to the problem.