Excel to List of Lists

[ab103]

Hi, I am transferring data from an excel file to a list of lists where the format is as follows....list of columns in row1, list of columns in row 2...etc.

 

The code (which I got from the internet), works fine except for with numbers in excel. I have the entire excel sheet formatted to Text only, however, the numbers get formatted with a .0 at the end of it...for example....2 becomes 2.0

 

If you have any suggestions on how to fix this, please let me know! Also, I am very knew to lisp, so full explanations would be helpful. Thank you!

 

Code:

(defun EXD  (/ ExcelApp ExcData FilePath Sht ShtNum UsdRange Wbk)
 (setq	FilePath (getfiled "Select Excel file to read :" "C:\\Users\\Public\\Documents\\" "xlsx" 16))
 (setq ShtNum 1)
 (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
 (vla-put-visible ExcelApp :vlax-true)
 (setq	Wbk (vl-catch-all-apply 'vla-open (list (vlax-get-property ExcelApp "WorkBooks") FilePath)))
 (setq	Sht (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Wbk "Sheets") "Item" ShtNum)))
 (vlax-invoke-method Sht "Activate")
 (setq	UsdRange (vlax-get-property Sht 'UsedRange) ExcData	(vlax-safearray->list (vlax-variant-value (vlax-get-property UsdRange 'Value))))
 (setq ExcData (mapcar(function (lambda (x) (mapcar 'vlax-variant-value x))) ExcData))
 (vl-catch-all-apply 'vlax-invoke-method (list Wbk "Close"))
 (vl-catch-all-apply 'vlax-invoke-method(list ExcelApp "Quit"))
 (mapcar(function(lambda (x) (vl-catch-all-apply (function (lambda ()(progn(if (not (vlax-object-released-p x))(progn (vlax-release-object x)(setq x nil)))))))))(list UsdRange Sht Wbk ExcelApp))
 (gc)
 (gc)
 ExcData
) 

[Lee Mac]

Try changing:

(vlax-get-property UsdRange 'Value)

To:

(vlax-get-property UsdRange 'Text)

[ab103]

Try changing:

(vlax-get-property UsdRange 'Value)

To:

(vlax-get-property UsdRange 'Text)

 

No Luck. I get the following error: ; error: bad argument type: safearrayp nil

[ronjonp]

I can't replicate your problem?

_$

(#

# # # # # #

)

_$

 

Nevermind, when you have the cells formatted as numbers and 0 precision I can replicate it.

(2.0 3.0 4.0 5.0 6.0 7.0 8.0)

If they are formatted as text, they keep their 'value'.

Edited September 12, 2017 by ronjonp

[ab103]

I can't replicate your problem?

 

Create an excel sheet with rows and columns as shown here:

1 2 3 4

5 6 7 8

 

If you store access the lists it will come out as this: ([1.0,2.0,3.0,4.0], [5.0,6.0,7.0,8.0])

[ab103]

Create an excel sheet with rows and columns as shown here:

1 2 3 4

5 6 7 8

 

If you store access the lists it will come out as this: ([1.0,2.0,3.0,4.0], [5.0,6.0,7.0,8.0])

 

Maybe this might also help....when you run the program, a dialog box will pop up. Select the saved Excel file, and then have the function return to a variable and return the variable to view the lists. I'm sure you already know all of this, but just wanted to be clear, thank you for taking the time to look at my code!

[ronjonp]

See above .. try changing the actual cell format in your workbook to text and run it again.

[ab103]

As mentioned in my first post, I had it formatted as text, thinking that would resolve issue, but it did not change the result. It still added .0 at the end

[ronjonp]

THIS might solve your problem .. looks like you need to check the data type for each cell.

 

To use:

 

(getexcel
 (getfiled "Select Excel file to read :" (strcat (getenv "userprofile") "\\Desktop\\") "xlsx" 16)
 nil
 nil
)

[ab103]

That did it!!! Thank you so much!

[ronjonp]

Glad to help

[Grrr]

THIS might solve your problem .. looks like you need to check the data type for each cell.

 

To use:

 

(getexcel
 (getfiled "Select Excel file to read :" [color="red"](strcat (getenv "userprofile") "\\Desktop\\")[/color] "xlsx" 16)
 nil
 nil
)

 

This is very clever, thanks for posting!

Noted in my stuff.