need expert help on my write to Excel function

[Tomislav]

Hello everyone, i'm in need of help cause my function 'WriteRelativeToXls' for writing passed values behaves randomly and unexpectedly 

The passed arguments and some 'outside' data you can see in submitted log.txt and the result in Book.xlsx where you can see marked in yellow the wrong values it produces and i can't figure out why

 

;;;********************************************


   (defun setcelltext (cells row column value)
       (vl-catch-all-apply
	   'vlax-put-property
	   (list cells
		 'Item
		 row
		 column
		 (vlax-make-variant
		     (vl-princ-to-string value)
		     8
		 ) ;_vlax-make-variant
	   ) ;_list
       ) ;_vl-catch-all-apply
   ) ;_defun

;;;***************************************************************************


(defun WriteRelativeToXls (station hdistances z_values / i col)
    (tv:StationIntoText station)
    (vla-put-name xlsheet (strcat (rtos pocetna_stac 2 0) "-" (rtos zavrsna_stac 2 0)))
    (setcelltext xlcells stat_row 1 stattxt);;funkcija upisuje u excel
    (setq col 1
	  i 0
	  xlrow_txt (strcat (itoa stat_row) ":" (itoa stat_row))
	  xlrownext_txt (strcat (itoa (1+ stat_row)) ":" (itoa (1+ stat_row)))
	  )
    (setq xlrow  (vlax-get-property xlsheet 'Range xlrow_txt)
	  xlrownext  (vlax-get-property xlsheet 'Range xlrownext_txt)
	  )
    (vlax-put-property xlrow 'NumberFormat "0.00")
    (vlax-put-property xlrownext 'NumberFormat "0.000")
    (repeat(length hdistances)
	(setq col(1+ col))
	(setcelltext xlcells stat_row col (rtos(nth i hdistances)2 2))
	(setcelltext xlcells (1+ stat_row) col (rtos(nth i z_values)2 3))
	(setq i(1+ i))
	)
    (vlax-invoke-method(vlax-get-property xlsheet 'Columns)'AutoFit) ;_ vlax-invoke-method    
    (setq stat_row(+ 3 stat_row))    
) ;_  defun

Book1.xlsx log.txt

[BIGAL]

Rather than try to fix code have a look at some of the functions inside this like Alan Excel library.lsp"xlsetcelltext"

[Tomislav]

4 hours ago, BIGAL said:

Rather than try to fix code have a look at some of the functions inside this like Alan Excel library.lsp"xlsetcelltext"

i have that library too, but haven't found an answer in it to this, even when i incorporated some of it like 'rtosr' function i get even weirder results...the problem is my lisp works on most of numbers but some seem to produce bad result and i want to understand why is it so..

[Tomislav]

u know what i just found, that by changing the number of decimals in this row :

(setcelltext xlcells stat_row col (rtos(nth i hdistances)2 3))

 

from 2 to 3, everything works fine(only thing is i have an extra decimal in cells), but why???   why can't i set the number of decimals to 2?

Edited June 17 by Tomislav

[Tomislav]

and another thing just found...if i omit first two lines and leave the third one to 2 decimals, excel fills in those cells, that i've said are wrong, a date!!

(vlax-put-property xlrow 'NumberFormat "0.00")
(vlax-put-property xlrownext 'NumberFormat "0.000")


(setcelltext xlcells stat_row col (rtos(nth i hdistances)2 2))

 

[exceed]

Try adding a ' in front of all text.

[Tomislav]

8 minutes ago, exceed said:

Try adding a ' in front of all text.

well, that did the trick as with the look of the result, but now i have a bunch of those corner triangles in excel for almost every cell that tell you the format of data in cell is not correct, any idea how to avoid that?

these are lines changed for everyone that follows

 

	(setcelltext xlcells stat_row col (strcat "'"(rtos(nth i hdistances)2 2)))
	(setcelltext xlcells (1+ stat_row) col (strcat "'"(rtos(nth i z_values)2 3)))

 

[Tomislav]

well, must apologize to you all for being a dork, just realized that my decimal separator in excel isn't set to match system settings and now that i've corrected that, my lisp works normally so now my question would be is there a way to check if excel decimal separator is a dot (system) or comma? 

[Tomislav]

found it in this post for anyone interested

 

https://www.cadtutor.net/forum/topic/65290-identify-decimal-separator-via-autolisp/