Jump to content

Export Excel and format cells


Recommended Posts

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

Link to post
Share on other sites

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

 

  • Thanks 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...