Help: Export length of vertical lines to Excel file (csv)

[maikhanhmst]

Hi there,

I want to measure length of vertical lines between lines and curves and export to csv file as same as the following picture. It starts writing at B4.

The vertical lines I want to measure and export.

Could someone please help me to do it?

I also attached dwg file. 

Khanh.

[pbejse]

---

@maikhanhmst wrote:

Hi there,

I want to measure length of vertical lines between lines and curves and export to csv file as same as the following picture. It starts writing at B4.

Could someone please help me to do it?

I also attached dwg file. 

Khanh.

---

Is that write to an existing CSV file? or from scratch? Do you really need all those borders? will a commad delimited be enough for your needs?

[maikhanhmst]

Hi pbejse,

I just programmed a  routine to create vertical lines between a line and a curve at common interval and measure their length with your previous help. But it's not enough, the length must be exported to a new csv file. All things the routine will do is to prompt users to pick a line and a curve, then it will create automatically lines between them, measuring length of the lines and exporting to csv file.

Thanks for taking your time to reply.

Khanh. 

[pbejse]

---

@maikhanhmst wrote:

Hi pbejse,

I just programmed a  routine to create vertical lines between a line and a curve at common interval and measure their length with your previous help. But it's not enough, the length must be exported to a new csv file. All things the routine will do is to prompt users to pick a line and a curve, then it will create automatically lines between them, measuring length of the lines and exporting to csv file.

Thanks for taking your time to reply.

Khanh. 

---

Post your routine then we'll pick from there, we can add the option to export after all the lines are drawn. This task is pretty easy, CSV is simple but you will have to forego the borders.

I just dont like dealing with Excel application that much. 

[maikhanhmst]

My routine

(defun c:test()
(while
  (setq line (car(entsel "Select line: ")))
  (setq curve (car(entsel "Select curve: ")))
  (setq n 101)
  (setq p1 (vlax-curve-getStartPoint line))
  (setq p2 (vlax-curve-getEndPoint line))
  (setq l (/ (distance p1 p2) n))
  (entmake (list '(0 . "LINE") (cons 10 (trans p1 1 0))
	   (cons 11 (vlax-curve-getClosestPointToProjection curve p1 '(0.0 1.0 0.0)))
		 );list
	   );entmake
  (entmake (list '(0 . "LINE") (cons 10 (trans p2 1 0))
	   (cons 11 (vlax-curve-getClosestPointToProjection curve p2 '(0.0 1.0 0.0)))
		 );list
	   );entmake
  (repeat (- n 1)
    (setq pt (polar p1 (angle p1 p2) l))
    (entmake (list '(0 . "LINE") (cons 10 (trans pt 1 0))
	     (cons 11 (vlax-curve-getClosestPointToProjection curve pt '(0.0 1.0 0.0)))
	     );list
	     );entmake
    (setq p1 pt)
    );repeat
  );while
  );defun
  
  

[pbejse]

---

@maikhanhmst wrote:

My routine

---

How do you want to deal with the sub-header name like Region 1,Region 2... select a Text Entity or Type in the name?

[maikhanhmst]

When users pick a line of the first region, it will automatically write "Region 1" to csv file. Then if they continue to pick a line of the second one, it will write "Region 2", etc. Because the number of the regions is variable. 

Thanks.

[pbejse · Accepted solution]

---

@maikhanhmst wrote:

When users pick a line of the first region, it will automatically write "Region 1" to csv file. Then if they continue to pick a line of the second one, it will write "Region 2", etc. Because the number of the regions is variable. 

Thanks.

---

I tried as much as possible not to deviate from your orignal code:

(defun c:test ( / data _line LoopData line curve n p1 p2 pt file_ revData)

;;;	this code is written as a part of Khan learning AutoLisp,	;;;
;;			pBe 21Sep2014					;;;
  
(setq Data nil i 1)
  
;;	Sub Routine by pBe/Khan		;;;
(defun _line (pt / l)
(strcat   
  (if (setq l (entmakex (list '(0 . "LINE") (cons 10 (trans pt 1 0))
	   (cons 11 (vlax-curve-getClosestPointToProjection curve pt '(0.0 1.0 0.0)))
		 );list
	   )
            l (vlax-curve-getdistatpoint l (vlax-curve-getendpoint l))     
        )
  (Rtos l 2 2)  "0.00") ","
    )
  )
;;					;;;
  
(while (and
  	(setq LoopData nil 
              line (car(entsel "Select line: ")))
  	(setq curve (car(entsel "Select curve: ")))
        )
  
  (setq n 101)
  (setq p1 (vlax-curve-getStartPoint line))
  (setq p2 (vlax-curve-getEndPoint line))
  (setq l (/ (distance p1 p2) n))
  
;;		Modified by pBe		;;;
  (setq LoopData (list (strcat "Region " (itoa i) ","))
    	Loopdata (cons (_line p1) Loopdata))
;;					;;;
        
  (repeat (- n 1)
    (setq pt (polar p1 (angle p1 p2) l))

;;		Modified by pBe		;;;
    (setq Loopdata (cons (_line pt) Loopdata))

    (setq p1 pt)
    );repeat
  (setq Data (cons (cons (_line p2)  LoopData) Data) i (1+ i))
;;					;;;
  
  );while

;;		Options by pBe		;;;
  (initget 1 "Y N")
  (if (And
        Data
 	(Eq "Y" (getkword "\nSave Length Data to file[Yes/No]: "))
        (setq revData nil
              file_ (getfiled "\nLength (mm)" (getvar 'Dwgname) "CSV" 1))
        )
    (progn
      	(setq Data (reverse data))
	(repeat 103
          (setq revData (cons (apply 'strcat (cons "," (mapcar 'car data))) revData)
            )
          (setq data (mapcar 'cdr data))
          )
	(setq CSVFile (open  file_ "w"))
      	(foreach itm (append '("," "," ",") Revdata)
          	(print (write-line itm CSVFile))
          )
	
      (princ (Strcat "\n<<Length data file Saved at " file_" >>"))
      (close CSVFile)
      )
    (princ "\n<<Skipped Creation of Length data file>>")
    )
  (princ)
  )

 Please NOTE

;;;	this code is writtenAs a part of Khan learning AutoLisp,	;;;
;;			pBe 21Sep2014					;;;

HTH

[maikhanhmst]

Wow, it works so great  . Many thanks to you for all of your help. I'm just an Autolisp beginner. I'm learning by trying to decipher existing routines and write some simple routine for my learning. This routine has a lot of amazing things to learn. Thank you once again .   

[pbejse]

---

@maikhanhmst wrote:

Wow, it works so great  . Many thanks to you for all of your help. I'm just an Autolisp beginner. I'm learning by trying to decipher existing routines and write some simple routine for my learning. This routine has a lot of amazing things to learn. Thank you once again .   

---

You are Welcome and  good for you, Glad i could help, hope you will learn from it. 

Cheers

pBe

||| Keep on coding |||

[maikhanhmst]

Hi pBe,

Is it possible for me to modify the routine and export to .xlsx? Although I know Excel can save a csv file as a .xlsx file. But it's pretty much annoying to do that. 

Khanh  

[maikhanhmst]

I exported to .xlsx file successfully, but I couldn't open the file by Excel 2013. Could you please tell me why?

[pbejse]

---

@maikhanhmst wrote:

I exported to .xlsx file successfully, but I couldn't open the file by Excel 2013. Could you please tell me why?

---

What exactly did you do? just hange CSV extension to XLS on the code? Did it work on other versions of Excel? I dont have excel with me now to test. 

We can definitely modify the code to write directly to Exccl format, If thats what yopu really need, I have an idea though, what if instead of saving the result on an external file like CSV, we an use ACAD_TABLE ?  I think yu can export thte table via DATA extraction directly to an XLS file, you just need to save a DXE format one and used that as template.

What do you think?

[maikhanhmst]

Hi,

Yes. I converted CSV extension to XLSX extension. But I get an error message from Excel "Excel cannot open this file the file format or file extension is not valid". I don't know whether it can work on other Excel versions. Your idea is so great. I have to keep my output simple, so I would rather directly export to .xlsx file than do use DATA extraction.

Many thanks.

Khanh.

[maikhanhmst]

(if (And
        Data
 	(Eq "Y" (getkword "\nSave Length Data to file[Yes/No]: "))
        (setq revData nil
              file_ (getfiled "\nLength (mm)" (getvar 'Dwgname) "CSV" 1))
        );and
    (progn...)
);if

 The (if) statement works when Data is not nil and, the answer is "Y" and finish choosing where the out file is located.

Is that right? This (and) logical operator is too complicated.