Well, I've finally looked into using Tables as I have 2 subdivisions with large numbers of tables that need to be shown. I've previously done this with Excel and just cut-paste the sheet into my drawing, but these tables are too big for the OLE object. So what I did was write a lisp that accesses my Excel file and reads the data I need into a new table object that I create.
Works great, albeit a bit slow....is this a function of the Table object?... except I cannot get the new table to use the the style that I wish to use. I create the style in code, that part works, but when I AddTable it uses the style that the TableStyle dialog shows as current. I can't find anywhere to set that current Style in code. Changing the StyleName property of the Table does set the correct style, but it does not reset the rows/columns to use the new style's settings.
I'm open to any suggestions as to how I can remedy this problem, preferably in code.
For anyone who got this far and is saying "So post the code, dood.", here it is. Note that it uses any excel workbook's active sheet.....and it will only use the first 3 or 4 columns by any number of rows.
;|Fill a table with data from Excel for Lot-House Matrix
Will only use the first 3 or 4 columns: Lot, Plan, Permit, Phase(opt.)
First row must be column header.
[color=RED]NOTE - Has a file dependency for MsxUtl01.lsp which are Excel Utilities posted
to the Autodesk newsgroups by Marc'Antonio Alessi....some prompts are in Italian[/color]
Jan. 11, 2007 by Jeff Mishler
|;
(defun c:LotTable (/ COLUMNS DATA DATATYPE EXCELFILE INSPT LOTCOUNT ROW TABLE TBLSTYL
TBLSTYLS TS080 TS100 TXTSTYLES XCELUTILS)
(defun get_space ()
(if (= 1 (vla-get-activespace *doc*))
(vla-get-modelspace *doc*) ;we're in modelspace
(if (= (vla-get-mspace *doc*) :vlax-true)
(vla-get-modelspace *doc*) ;we're in modelspace
;thru paperspace VPort
(vla-get-paperspace *doc*) ;we're in paperspace
)
)
)
(if (and (setq xcelutils (findfile "MsxUtl01.lsp"))
(setq excelfile (getfiled "SELECT EXCEL FILE" "House-Lot Matrix.xls" "xls" 8))
(setq LotCount (getint "\nNumber of lots to import: "))
(< 0 LotCount)
(setq *doc* (vla-get-activedocument (vlax-get-acad-object)))
(load xcelutils)
)
(progn
(MSX_LoadTypeLib);;load the TLB file for easy access
(MSX_OpenExist excelfile nil);;open the desired XLS file
(if (MSX_getcellvalue 1 4);;are there to be 3 or 4 columns?
(setq columns 4)
(setq columns 3)
)
(setq row 1)
;;should check this, do all new drawings have a tablestyle dict?
(setq tblstyls (vla-item (vla-get-dictionaries *doc*) "acad_tablestyle"))
(if (vl-catch-all-error-p ;;try to get the existing Style
(vl-catch-all-apply '(lambda ()
(setq tblstyl (vla-item tblstyls "LotMatrix"))
)))
(progn ;;it doesn't exist, let's create it!
(setq txtstyles (vla-get-textstyles *doc*))
;;make sure the desired textstyles are available and the correct size
(setq TS100 (vla-add txtstyles "TS100")
TS080 (vla-add txtstyles "TS080")
)
(vla-put-fontfile ts100 (strcat (getenv "WINDIR") "\\FONTS\\TAHOMA.TTF"))
(vla-put-fontfile ts080 "SIMPLEX.SHX")
(vla-put-height ts100 0.10)
(vla-put-height ts080 0.08)
;;create the tablestyle
(setq tblstyl (vla-addobject tblstyls "LotMatrix" "AcDbTableStyle"))
(vlax-put tblstyl 'Description "LotMatrix")
(vlax-put tblstyl 'FlowDirection 0);;top to bottom or bottom to top
(vlax-put tblstyl 'HeaderSuppressed 0);;header on = 0 off = -1
(vlax-put tblstyl 'HorzCellMargin 0.02)
(vlax-put tblstyl 'TitleSuppressed 0);;Title on = 0 off = -1
(vlax-put tblstyl 'VertCellMargin 0.02)
(vlax-invoke tblstyl 'SetTextHeight acTitleRow 0.10)
(vlax-invoke tblstyl 'SetTextHeight acHeaderRow 0.10)
(vlax-invoke tblstyl 'SetTextHeight acDataRow 0.08)
(vlax-invoke tblstyl 'SetTextStyle acTitleRow "TS100")
(vlax-invoke tblstyl 'SetTextStyle acHeaderRow "TS080")
(vlax-invoke tblstyl 'SetTextStyle acDataRow "TS080")
(vlax-invoke tblstyl 'SetAlignment acTitleRow acMiddleCenter)
(vlax-invoke tblstyl 'SetAlignment acHeaderRow acMiddleCenter)
(vlax-invoke tblstyl 'SetAlignment acDataRow acMiddleCenter)
)
);;done getting/creating table style for Matrix
(setq inspt (getpoint "\nSelect insertion point for Matrix: "))
;;for the # of rows, we need to allow 1 for the title & another for the headers
(setq table (vlax-invoke (get_space) 'addtable inspt (+ 2 LotCount) columns 0.04 0.6))
(vla-put-RegenerateTableSuppressed table :vlax-true);;don't regen table with every addition/change
(vla-put-stylename table "LotMatrix");;this should change the style, which it does,
;;but it does NOT change how the durn thing looks
(vla-settext table 0 0 "LOT-HOUSE MATRIX");;This is the title
;;row 1 of the Excel file should be the Header data, which works well here since row 1 of the table
;; is actually the second row...it's 0 base
(repeat (1+ LotCount);;get each row of data, one cell at a time
(vla-settext table row 0 (MSX_getcellvalue row 1))
(vla-settext table row 1 (MSX_getcellvalue row 2))
(vla-settext table row 2 (MSX_getcellvalue row 3))
(vla-settext table row 3 (MSX_getcellvalue row 4))
(setq row (1+ row))
)
(MSX_Quit *ExcelApp* nil);;terminate the ExcelApp
(vla-put-RegenerateTableSuppressed table :vlax-false);;Now we can regen the table
;;the following is to add Xdata to the table so I can write another app that will update the
;; table from the same excel file....it won't be automatic, but good enough for this job.
(regapp "TDG-Tables")
(setq datatype '(1001 1000)
data (list "TDG-Tables" excelfile)
)
;;function borrowed from Tim Willey, Thanks Tim!
(defun MySetXData (Obj CodeList DataList /)
;; Sets XData to an object. First number in code list must be 1001
(vla-SetXData
Obj
(vlax-make-variant
(vlax-safearray-fill
(vlax-make-safearray
vlax-vbInteger
(cons 0 (1- (length CodeList)))
)
CodeList
)
)
(vlax-make-variant
(vlax-safearray-fill
(vlax-make-safearray
vlax-vbVariant
(cons 0 (1- (length Datalist)))
)
DataList
)
)
)
)
(mysetxdata table datatype data)
;;(vlax-invoke table 'setxdata datatype data);this doesn't work, hence the above.....
)
(princ "\nTable not created due to errors or cancellation.....")
)
(princ);;we're outta here, all done!
)
