Community
@santvn wrote:
As a ACAD Table. I need to automate the PASTE Special functionality using AutoLIST. Is that possible?
I take it you have the excel file opened for you to copy just random bits and parts of the spreadsheet, then you are better off using copy and paste.
If you want the entire table or specific range or tab then yes, certainly doable with Autolisp.
Start command TABLE from ribbon, command line or however you like it.
In table dialog from Insert options select "From a data link", click on "Data Link Manager" icon, in DLM select "Create new data link", give it some name let say "CSV_1", and select your CSV file (change default file extension from .xlsx to .csv).
Pick a point where you want to place your table and that's it.
Cells in table are initially in locked state, but simply select all cells, right click, locking and select Unlock.
To make data in table undependable to data in linked CSV file select table, right click and in Data link sub-menu select "Detach Data link" and answer "Yes" to follow up dialog.
Investigate other options available with table manager.
This can be done with lisp for simple csv files but for particular table case and is actually not usable for various table options you may have in your csv or Excel.
Miljenko Hatlak
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
Regards @santvn
Try this code, to import CSV files to ACAD table...
By default it looks for the file in the Documents folder. If it is not there, you can address it.
(vl-load-com)
(defun c:T_Csv (/ adoc spm tt pt1 ncolumns rowheight colwidth objtable ls lv ln
lt s en tx f fline data
)
(setq adoc (vla-get-activedocument (vlax-get-acad-object))
spm (vla-get-modelspace adoc)
)
(if (and
(setq f (getfiled "Select csv file to import data" "" "csv" 4))
(setq f (open f "r"))
)
(progn
(setq tt "CSV FILE DATA TABLE"
pt1 (vlax-3d-point
(getpoint "\nPick the top left point to insert the table: ")
)
nrows 2
ncolumns 2
rowheight 5
colwidth 20
)
(setq objtable (vla-addtable spm pt1 nrows ncolumns rowheight colwidth))
(setq item 1)
(vla-settext objtable 0 0 tt)
(vla-settext objtable 1 0 "Item")
(vla-settext objtable 1 1 "Value")
(vla-SetTextHeight Objtable (+ acDataRow acHeaderRow acTitleRow) 1.5)
(setq objtable (vlax-ename->vla-object (entlast)))
(while
(setq fline (read-line f))
(if (/= fline "")
(progn
(setq tx item
data fline
)
(vla-InsertRows objtable nrows 3.0 1)
(vla-SetTextHeight Objtable (+ acDataRow) 1.0)
(vla-SetAlignment Objtable acDataRow acMiddleCenter)
(vla-settext objtable nrows 0 tx)
(vla-settext objtable nrows 1 data)
(setq nrows (+ nrows 1))
)
(alert "End Rows...")
) ;if
(setq item (1+ item))
) ;While
) ;if
)
(close file)
(vlax-release-object objtable)
(princ)
)
Carlos Calderon G
>Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
Use your code to import following CSV data, and see what's wrong.
Items count;;
No;Name;Total
1;A;21
2;B;22
3;C;23
4;D;45
5;E;36
;Total sum:;147Code has to be written for general case, where number of rows a columns is unknown, without defining titles and headers.
Miljenko Hatlak
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
"Code has to be written for general case, where number of rows a columns is unknown"
Ok finally got something working. Would appreciate any comments.
Proceedure is have excel open, A1 is title, row 2 is header details, row 3+ is data
; https; //forums.autodesk.com/t5/visual-lisp-autolisp-and-general/need-to-copy-excel-table-to-autocad-drawing-using-autolisp/td-p/11334140;;
; read all cells of excel and make a Acad table
; By AlanH AUG 2022
; Expects a title in A1
; Expects row 2 is header info
; row 3+ is data
(defun AHMaketable (numcolumns / colwidth numcolumns numrows objtable rowheight sp vgad vgao vgms)
(vl-load-com)
(setq sp (vlax-3d-point (getpoint "\nPick point for table")))
(Setq vgms (vla-get-modelspace (vla-get-activedocument (vlax-get-acad-object))))
(setq numrows 3)
(setq rowheight 2.5) get current style height
(setq colwidth 60) width later
(setq objtable (vla-addtable vgms sp numrows numcolumns rowheight colwidth))
(command "_zoom" "e")
(princ)
)
;-------------------------------------------------------------------------------
; Program Name: GetExcel.lsp [GetExcel R7]
; Created By: Terry Miller (Email: terrycadd@yahoo.com)
; (URL: https://autolisp-exchange.com)
; Date Created: 9-20-03
(defun getallcells ( / row# Column# Range$ ExcelRange^ ExcelVariant^ ExcelValue Data@)
(setq CurRegion (vlax-get-property (vlax-get-property
(vlax-get-property myxl "ActiveSheet") "Range" "A1") "CurrentRegion")
)
(setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
(setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))
(setq *ExcelData@ nil)
(setq Row# 1)
(repeat MaxRow#
(setq Data@ nil)
(setq Column# 1)
(repeat MaxColumn#
(setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
(setq ExcelRange^ (vlax-get-property myxl "Range" Range$))
(setq ExcelVariant^ (vlax-get-property ExcelRange^ 'Value))
(setq ExcelValue (vlax-variant-value ExcelVariant^))
(setq ExcelValue
(cond
((= (type ExcelValue) 'INT) (itoa ExcelValue))
((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
((/= (type ExcelValue) 'STR) "")
)
)
(setq Data@ (append Data@ (list ExcelValue)))
(setq Column# (1+ Column#))
)
(setq *ExcelData@ (append *ExcelData@ (list Data@)))
(setq Row# (1+ Row#))
)
(princ)
)
;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
; Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
(if (< Num# 27)
(chr (+ 64 Num#))
(if (= 0 (setq Val# (rem Num# 26)))
(strcat (Number2Alpha (1- (/ Num# 26))) "Z")
(strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
)
)
);defun Number2Alpha
; rtosr - Used to change a real number into a short real number string
; stripping off all trailing 0's.
; Arguments: 1
; RealNum~ = Real number to convert to a short string real number
; Returns: ShortReal$ the short string real number value of the real number.
;********************************************************************************************
(defun rtosr (RealNum~ / DimZin# ShortReal$)
(setq DimZin# (getvar "DIMZIN"))
(setvar "DIMZIN" 8)
(setq ShortReal$ (rtos RealNum~ 2 8))
(setvar "DIMZIN" DimZin#)
ShortReal$
);defun rtosr
;; *******************************************************************************************
; Starts here
(defun ahgetallexcel2table ( / myxl objtable l2 x y val numrows *ExcelData@ Maxcolumn)
(or (setq myxl (vlax-get-object "Excel.Application"))
(setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(getallcells)
(AHMaketable MaxColumn#)
(setq objtable (vlax-ename->vla-object (entlast)))
(vla-settext objtable 0 0 (car (nth 0 *ExcelData@ )))
(setq objtable (vlax-ename->vla-object (entlast)))
(setq l2 (nth 1 *ExcelData@))
(setq y 0)
(foreach val l2
(vla-settext objtable 1 y val)
(setq Y (1+ Y))
)
(setq objtable (vlax-ename->vla-object (entlast)))
(vla-put-regeneratetablesuppressed Objtable :vlax-true) ; speeds up process.
(setq numrows 2)
(repeat (- (length *ExcelData@) 2)
(setq data (nth numrows *ExcelData@))
(setq cols -1 )
(repeat MaxColumn#
(vla-settext objtable numrows (setq cols (1+ cols)) (nth cols data))
)
(setq numrows (1+ numrows))
(vla-InsertRows Objtable numrows (vla-GetRowHeight Objtable 1) 1)
(setq objtable (vlax-ename->vla-object (entlast)))
)
(vla-put-regeneratetablesuppressed Objtable :vlax-false)
(princ)
)
(ahgetallexcel2table)
Forums Links
Can't find what you're looking for? Ask the community or share your knowledge.
Post to forums
