The speed of cad writing to excel is very slow

[ekko]

Hello friends, I have an lsp program, when I didn't add the code written to excel, the program took 1.8 seconds to run, and when I added the code written to excel, it took 15 seconds to run, obviously this The speed is terrible, I want to improve it, is there a way to fix it?

(defun toexcel ()
  (vlax-put-property cels 'item 1 6 test1) 
  (vlax-put-property cels 'item 2 5 test2) 
  (vlax-put-property cels 'item 3 4 test3)
  (vlax-put-property cels 'item 4 3 test4) 
  (vlax-put-property cels 'item 5 2 test5) 
  (vlax-put-property cels 'item 6 1 test6) 
)

 

Edited April 17, 2022 by ekko

[BIGAL]

Is the speed in opening excel or just the put cell part ?

 

maybe

(defun putcell (cellname val1 / )
(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
(vlax-put-property myRange 'Value2 val1)
)

 

[ekko]

      (setq atvbook  (vlax-get-property excelapp 'ActiveWorkbook)
        atvsheet (vlax-get-property atvbook 'ActiveSheet)
        cels     (vlax-get-property atvsheet 'cells)
      )

Just putting data into cells is too slow. Your code doesn't seem to change, I have set the ActiveX object before calling the subroutine

[exceed]

It can be faster to select by range and vlax-put than to select cells one by one and vlax-put.

 

or 

If .xls or .xlsx are heavy, how about using a .csv file?

http://www.lee-mac.com/writecsv.html

 

It will be a little lighter.

 

Edited April 18, 2022 by exceed

[BIGAL]

That is odd I had a look at multiple puts just pasted to command line and excel values were there. It was instant. The excel Iuse this on has look up tables etc and shows no sign of being slow.

 

(ahputcell "B11" "Yes")
(ahputcell "B12" "no")
(ahputcell "B13" "1")
(ahputcell "B14" "2")
(ahputcell "B15" "3")
(ahputcell "B16" "6")

 

It sounds like something in your excel is slowing it down.

[ekko]

7 hours ago, exceed said:

It can be faster to select by range and vlax-put than to select cells one by one and vlax-put.

 

or 

If .xls or .xlsx are heavy, how about using a .csv file?

http://www.lee-mac.com/writecsv.html

 

It will be a little lighter.

 

Thank you for your suggestion My bro, maybe .csv file is not suitable for me, because I have prepared a .xls file template in advance, and I also hope that the input data can be displayed in real time, and program bugs can be found in real time. Thanks to @BIGAL's tip, I found the reason for slowing down the program, because I established a condition in the .xls file, =($F8<>"")*($F8=0). When I removed this condition, the running speed dropped from 15 seconds to 2.3 seconds.

Edited April 18, 2022 by ekko