Export Excel and format cells

[p2pnex]

How to change cell formatting in Excel to text via lisp, and change cell color in the first line, before writing the list.

 

 

(defun c:test ( / *error* arq A B C o)

(defun *error* (errmsg) (if (not (wcmatch errmsg "Function cancelled,quit / exit abort,console break,end")) (princ (strcat "\nError: " errmsg))) (if arq (close arq))

(princ))

(setq narq (getfiled "Selecione o Nome e Local" "PLANILHA TESTE" "csv" 1))

 

(setq arq (open narq "w"))

(write-line "Col1;Col2;Col3;Col4;Col5;Col6" arq )

 

(setq A '(("0001" "aa" "01-01" "x001") ("0002" "aa" "002" "x002") )

          B '(("0001" "bb" "c01" "d002"))

          C A)

 

(foreach i B (if (setq o (assoc (car i) A)) (setq C (subst (append o (cdr i)) o C))))

 

(foreach e C (write-line (apply 'strcat (mapcar '(lambda (x) (strcat x ";")) e)) arq))

 

(if arq (close arq)) (princ)))

[kirby]

Setting excel range or cell properties is done using the 'vlax-put-property' command.  MS Excel VBA online help is your friend for finding the names of properties of the Range or Cell object, and of the many enumerations used in Excel.

 

See attached code snippet (part of a larger routine that inserts and formats a list of data into a specified Worksheet at the specified row and column)

		; Data type codes
		; vlax-make-variant constants
		; https://documentation.help/AutoLISP-Functions/WS1a9193826455f5ff1a32d8d10ebc6b7ccc-6806.htm
		; Some codes  Empty = 0, Null = 1, Integer = 2, Long = 3, Single = 4, Double = 5, String = 8, Object = 9, Boolean = 11
		(cond
			((eq DataType "R") (setq TypeCode 5))	; vlax-vbDouble
			((eq DataType "I") (setq TypeCode 3))	; vlax-vbLong
			((eq DataType "S") (setq TypeCode 8))	; vlax-vbString
		)

		; Justification
		;https://docs.microsoft.com/en-us/office/vba/api/excel.xlhalign
		(cond
			((eq DataJust "L") (setq JustCode -4131))	; xlLeft
			((eq DataJust "C") (setq JustCode -4108))	; xlCenter
			((eq DataJust "R") (setq JustCode -4152))	; xlRight
		)	


		(setq xlcell nil)
		(setq xlcell (xlgetcellrange MySheet CellIndex))

		; Format.  Specify this first so that any Text strings do not get covnerted to numbers my mistake (e.g. Handle '20e87', excel will try to converr to real 2.0e88
		(if DataFormat
			(vlax-put-property xlcell "NumberFormat" DataFormat)
		)


		; Data type
		(cond
			((member DataType (list "R" "I" "S"))
				(vlax-put-property xlcell 'value2 (vlax-make-variant DataValue TypeCode))
			)
			((eq DataType "F")
				(vlax-put-property xlcell "Formula" DataValue)
			)
		)



		; Justification
		(if DataJust
			(vlax-put-property xlcell "HorizontalAlignment" JustCode)
		)

		; Font bolding
		(setq MyFont (vlax-get-property xlcell "Font"))
		(cond
			((eq FontBold 1) (vlax-put-property MyFont "Bold" (vlax-make-variant 1 11)))	; boolean 1=true
			((eq FontBold 0) (vlax-put-property MyFont "Bold" (vlax-make-variant 0 11)))	; boolean 0=false
		)

		; Colour
		(if CellColourIndex
			(vlax-put-property (vlax-get-property xlcell "Interior") "Colorindex" (vlax-make-variant CellColourIndex))
		)

 

[BIGAL]

Thought this was answered here new poster name.

 

Exportar Excel e formatar células - Autodesk Community - AutoCAD

[hosneyalaa]

3 hours ago, BIGAL said:

Thought this was answered here new poster name.

 

Exportar Excel e formatar células - Autodesk Community - AutoCAD

 

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/exportar-excel-e-formatar-celulas/td-p/10769819

[BIGAL]

Thanks hosneyalaa i seen the copy paste not work correct, will double check in future that it worked for link.