Solved: Re: Export data to Excel

edsaqueteAG5Q7 · ‎12-17-2022

I'm trying to develop a command to extract data from the drawing and save it to a standard spreadsheet.

I need to define a directory to leave the default worksheet and after the open and update command save in the directory folder of the dwg file.

Another point is after getting the necessary data from the blocks, I need to define which column and line I will write in the worksheet.

In the spreadsheet there are more than one tab, is it possible to define which one I will use?

-didier- · ‎12-18-2022

Bonjour

The easiest way is to create a CSV file.
Do you need help to create that file with an LSP program ?

Amicalement

Éternel débutant.. my site for learning : Programmer dans AutoCAD
Didier Aveline

_gile · ‎12-18-2022

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)))
   )
 )
)

Gilles Chanteau
Programmation AutoCAD LISP/.NET
GileCAD
GitHub

Sea-Haven · ‎12-18-2022

Like you writing direct to excel is not that hard once you get the hang of it, I would like to say thanks for the couple of routines that you wrote Alpha2number and Number2alpha my excel would not work without them.

I started with getexcel.lsp and expanded on it and now have my own version of a excel program. I just use,

(if (not AH:getcell)(load "my excel library"))

this way it has all the defuns you want, with out putting all the excel code into the current lisp.

Like you I have

Getcell

PutCell

Getrange with smart input thanks to fixo

getallcells

openexist

openexcel-sheetname

addsheet "thanks to fixo"

closeexcel

isopenexcel

chkexcel-filename "is filename open"

newexcelname

setsheet

Sheetorder

I have not posted the code as you do need some experience programming in lisp and needs to be customised to suit the users data export requirements. But happy to discuss request.

There are a couple of others putting together excel Library functions. Such as denon@alxfunctions.com

edsaqueteAG5Q7 · ‎12-19-2022

_gile thanks for sending your code, I'll look into it to use the necessary commands for what I'm trying to do.

As for extracting in .csv that I already use, I want to automate in addition to .csv and extract directly to the standard execel worksheet in the columns and rows defined in lisp.

edsaqueteAG5Q7 · ‎12-19-2022

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.

Sea-Haven · ‎12-22-2022

Can be done a question why save it twice ? Is it just so you have a backup ?

Make sheet 1 copyclip then paste to sheet2 would be easiest way to have 2 sheets the same writing 2 would be like a double handle.

1-Open the model spreadsheet in a specific folder yes need 2 things is it auto name like dwgname.xls, and location where to start search.

Final question how much about lisp do you know ? Did you give the code by Gile a go?

edsaqueteAG5Q7 · ‎12-22-2022

_gile

Thanks for sharing your code, I'm studying it to make my code along with other functions.

edsaqueteAG5Q7 · ‎12-22-2022

Sea Haven

I just posted models, I'm coding more complex spreadsheets.

My knowledge of lisp doesn't compare to yours, I make some codes to help with my work, but I still have a lot to learn.

Thank's for your time.

Sea-Haven · ‎12-22-2022

Ok ask questions if you get stuck.

Export data to Excel

Export data to Excel

Export data to Excel

Forums Links

Post to forums