Topic: Excel Importation

[jvillarreal]

Just another slow day..

(exceldcl <filename> <Properties Flag>) ;;Used Lee Mac's list manipulation functions

Arguments
 filename
  Full path to csv or xls file.
  If filename is nil, getfiled is used.

 Properties Flag
  T - Will compose an associated list of properties for each cell.
     -including but not limited to

Code: [Select]

(setq CellProps
  '("COLUMN" "COLUMNWIDTH" "HASFORMULA" "HEIGHT" "HORIZONTALALIGNMENT" "LEFT" "MERGEAREA" "MERGECELLS"
   "ROW" "ROWHEIGHT" "TEXT" "TOP" "VALUE2" "VERTICALALIGNMENT" "WIDTH"))
nil - Will compose a list of string values for each cell.

Return Values
(List <filename> (<Headers Selected>) (<Order Selected>) (<Excel Data Lists>))

Excel Data List will be in order by column or row according to selection.
I've updated the dcl to include the edit box as suggested by Ketxu but have yet to update the list functions.

Side Note:
After obtaining the properties from each cell, excel didn't close as before. A secondary call to close in the error function or the end of the program does the trick.

[Ketxu]

Thanks jvillarreal for sharing. I think we should catch the error when user not choose Header to sort Up - Down - Remove and make cycle Preview and Next Header Location (or textbox/listbox/popup list to choose Where Location to get Data - when your expect data is 1001 th row, you have click 1000 times  :ugly:)

[Lee Mac]

Hi jvillarreal,

That looks like a nice program idea, quite similar to an Excel Data Link Table, but perhaps for developers since it returns the raw data?

Thanks for the acknowledgement, much appreciated. Just to let you know, I updated those List_Box Functions a little while back (new ones here), it may shorten your code a little 

Lee

[jvillarreal]

Thanks jvillarreal for sharing. I think we should catch the error when user not choose Header to sort Up - Down - Remove and make cycle Preview and Next Header Location (or textbox/listbox/popup list to choose Where Location to get Data - when your expect data is 1001 th row, you have click 1000 times  :ugly:)

Ketxu,

My intention was to obtain a compare list to sort all data according to the header order selected by the user. If you prefer to obtain a specific line of data, you could add an edit box to the dcl and update the indx variable accordingly in the function. Feel free to ask for help if you have trouble modifying it.

Hi jvillarreal,

That looks like a nice program idea, quite similar to an Excel Data Link Table, but perhaps for developers since it returns the raw data?

Thanks for the acknowledgement, much appreciated. Just to let you know, I updated those List_Box Functions a little while back (new ones here), it may shorten your code a little 

Lee

Lee,

I was planning on using a more evolved version of the function in some existing programs. I hadn't played with importing excel data before jhonnying's request at cadtutor and am still experimenting.
The acknowledgement is well deserved and thanks for the update on the List_Box functions.

As an example of exceldcl function use:

After loading MakeTable.lsp, test1 will create an autocad table with the sorted data.

*Edit* - I've included a csv file as well. Row 2 contains the headers.

Code: [Select]

(defun c:test1 ( / ActDoc xldata)
(vl-load-com)
(setq ActDoc (vla-get-ActiveDocument (vlax-get-Acad-Object)))
(vla-EndUndoMark ActDoc)
(vla-StartUndoMark ActDoc)
(setq xldata (exceldcl nil nil))
(MakeTable
 T
  "Excel Data"
  (nth 1 xldata)
  (mapcar
   '(lambda (x)
    (mapcar
     '(lambda (y)
       (nth y x))
      (nth 2 xldata)))
     (nth 3 xldata))
)
(vla-EndUndoMark ActDoc)
(princ)
);defun


[jvillarreal]

[OOCG]I had a little more time to do some experimenting. [/OOCG]
The function now includes an option to list properties or string values for each cell.
I've also included the edit box as suggested by Ketxu but have yet to update the list functions.

[OOCG]I'll continue experimenting when i'm able.[/OOCG]