Bonjour
The easiest way is to create a CSV file.
Do you need help to create that file with an LSP program ?
Amicalement
Accepted solution
Hi,
I agree with @-didier- an in addition to what he said using a CSV is also much faster.
Anyway, if you absolutely want to follow the Excel file route, you can use these routines.
;;-------------------------------------------------------------------------------
;; gc:WriteExcel
;; Writes in an Excel file
;;
;; Arguments
;; filename : Excel file full path
;; sheet : name of the sheet (or nil for the current sheet)
;; startRange : name of the start cell (or nil for "A1")
;; dataList : list of sublists containing the data (one sublist per row)
;;-------------------------------------------------------------------------------
(defun gc:WriteExcel (filename sheet startRange dataList / *error* xlApp
wBook save sheets active start row col cells
n cell
)
(vl-load-com)
(defun *error* (msg)
(and msg
(/= msg "Function cancelled")
(princ (strcat "\nError: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(and reg (vlax-release-object reg))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list cell cells wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(if (findfile filename)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Open filename)
save T
)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Add))
)
(if sheet
(progn
(setq sheets (vlax-get-property xlApp 'Sheets))
(vlax-for s sheets
(if (= (strcase (vlax-get-property s 'Name)) (strcase sheet))
(progn
(vlax-invoke-method s 'Activate)
(setq active T)
)
)
)
(or active
(vlax-put-property (vlax-invoke-method sheets 'Add) 'Name sheet)
)
)
)
(if startRange
(setq start (gc:ColumnRow startRange)
col (car start)
row (cadr start)
)
(setq col 1
row 1
)
)
(setq cells (vlax-get-property xlApp 'Cells))
(or startRange (vlax-invoke-method cells 'Clear))
(foreach sub dataList
(setq n col)
(foreach data sub
(setq cell (vlax-variant-value (vlax-get-property cells 'Item row n)))
(if (= (type data) 'STR)
(vlax-put-property cell 'NumberFormat "@")
)
(vlax-put-property cell 'Value2 data)
(setq n (1+ n))
)
(setq row (1+ row))
)
(vlax-invoke-method
(vlax-get-property
(vlax-get-property xlApp 'ActiveSheet)
'Columns
)
'AutoFit
)
(if save
(vlax-invoke-method wBook 'Save)
(if (and
(< "11.0" (vlax-get-property xlapp "Version"))
(= (strcase (vl-filename-extension filename) T) ".xlsx")
)
(vlax-invoke-method wBook 'SaveAs filename 51 "" "" :vlax-false :vlax-false 1 1)
(vlax-invoke-method wBook 'SaveAs filename -4143 "" "" :vlax-false :vlax-false 1 1)
)
)
(*error* nil)
)
;;-------------------------------------------------------------------------------
;; gc:ReadExcel
;; Returns a list of sublists containing the data (one sublist per row) from an Excel file
;; Retourne une liste de sous-listes contenant les données (une sous liste par rangée)
;; d'un fichier Excel
;;
;; Arguments
;; filename : Excel file full path
;; sheet : name of the sheet (or nil for the current sheet)
;; startRange : name of the start cell (or nil for "A1")
;; maxRange : name of the cell where to stop reading,
;; or "*" for the whole sheet, or nil or "" for the current range
;;-------------------------------------------------------------------------------
(defun gc:ReadExcel (filename sheet startRange maxRange / *error*
xlApp wBook wSheet startCell startCol startRow maxCell maxCol
maxRow reg cells col row data sub
lst
)
(defun *error* (msg)
(and msg
(/= msg "Fonction annulée")
(princ (strcat "\nErreur: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list reg cells wSheet wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application")
wBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) 'Open filename)
)
(if sheet
(vlax-for ws (vlax-get-property xlapp 'Sheets)
(if (= (vlax-get-property ws 'Name) sheet)
(vlax-invoke-method (setq wSheet ws) 'Activate)
)
)
(setq wSheet (vlax-get-property wBook 'ActiveSheet))
)
(if startRange
(setq startCell (gc:ColumnRow startRange)
startCol (car startCell)
startRow (cadr startCell)
)
(setq startRange
"A1"
startCol 1
startRow 1
)
)
(if (and maxRange (setq maxCell (gc:ColumnRow maxRange)))
(setq maxCol (1+ (car MaxCell))
maxRow (1+ (cadr MaxCell))
)
(setq reg (if (= maxRange "*")
(vlax-get-property wSheet 'UsedRange)
(vlax-get-property
(vlax-get-property wSheet 'Range startRange)
'CurrentRegion
)
)
maxRow (+ (vlax-get-property reg 'Row)
(vlax-get-property (vlax-get-property reg 'Rows) 'Count)
)
maxCol (+ (vlax-get-property reg 'Column)
(vlax-get-property (vlax-get-property reg 'Columns) 'Count)
)
)
)
(setq cells (vlax-get-property xlApp 'Cells)
row maxRow
)
(while (< startRow row)
(setq sub nil
col maxCol
row (1- row)
)
(while (< startCol col)
(setq col (1- col)
sub (cons
(vlax-variant-value
(vlax-get-Property
(vlax-variant-value (vlax-get-property cells 'Item row col))
'Value2
)
)
sub
)
)
)
(setq lst (cons sub lst))
)
(*error* nil)
lst
)
;;-------------------------------------------------------------------------------
;; gc:ColumnRow -
;; Return a list containg the column and row indices
;; Arguments:
;; Cell = Reference of the cell
;; Example: (gc:ColumnRow "IV987") -> (256 987)
;;-------------------------------------------------------------------------------
(defun gc:ColumnRow (cell / col char row)
(setq col "")
(while (< 64 (ascii (setq char (strcase (substr cell 1 1)))) 91)
(setq col (strcat col char)
cell (substr cell 2)
)
)
(if (and (/= col "") (numberp (setq row (read Cell))))
(list (gc:Alpha2Number col) row)
)
)
;;-------------------------------------------------------------------------------
;; gc:Alpha2Number
;; Converts an alphabetic string into an integer
;; Function By: Gilles Chanteau from Marseille, France
;; Arguments
;; str = string to convert
;; Example: (gc:Alpha2Number "BU") = 73
;;-------------------------------------------------------------------------------
(defun gc:Alpha2Number (str / num)
(if (= 0 (setq num (strlen str)))
0
(+ (* (- (ascii (strcase (substr str 1 1))) 64)
(expt 26 (1- num))
)
(gc:Alpha2Number (substr str 2))
)
)
)
;;-------------------------------------------------------------------------------
;; gc:Number2Alpha
;; Converts an integer into an alphabetic string
;; Function By: Gilles Chanteau from Marseille, France
;; Arguments: 1
;; num = Nombre à convertir
;; Example: (gc:Number2Alpha 73) = "BU"
;;-------------------------------------------------------------------------------
(defun gc:Number2Alpha (num / val)
(if (< num 27)
(chr (+ 64 num))
(if (= 0 (setq val (rem num 26)))
(strcat (gc:Number2Alpha (1- (/ num 26))) "Z")
(strcat (gc:Number2Alpha (/ num 26)) (chr (+ 64 val)))
)
)
)
Sea-Haven and _gile
Follow step by step what I'm trying to do.
1-Open the model spreadsheet in a specific folder
2-Collect the block information (I already do this and save it in the .csv)
3-Identify which tab in the worksheet "Sheet1" or "Sheet2"
4-"Sheet1" fill in column "B" line "2" and "C" line "2" (repeat the same in "Sheet2'" depending on the information I'm going to extract from the blocks, this I can do)
5-Save the worksheet in the same folder where the dwg is saved (it will not be the same source folder.
