taking data from excel sheet to the autocad table

[S_S_SS]

Hello every one 
I need to make an autolisp code to import specific data from excel sheet ".xlsx" to an autocad table 
I need the cell "B2" in the sheet to be in the autocad table it's name is " newtblobj" in cell (9, 4) in the autocad table
I've made something but it the excelData gives me nil 
that's not what i need specific but i can edit the code to make what i need but i need to fix this error 

(defun c:updateTableFromXLSX ()
  ; Read the data from the specified Excel file and sheet
  (setq excelData (read-xlsx "G:\\55.xlsx" "11")) ; Update file path and sheet name
  ; Update the table in AutoCAD if the condition is met
  (update-table-if-condition-met newTblObj excelData)
  (princ))

(defun read-xlsx (filePath sheetName)
  (setq excelApp (vlax-get-or-create-object "Excel.Application")) ; Start Excel

  ; Ensure the file exists
  (if (not (findfile filePath))
    (progn
      (princ (strcat "\nFile not found or path is incorrect: " filePath))
      (exit))
  )

  ; Open the workbook
  (setq workbooks (vlax-get excelApp 'Workbooks))
  (setq workbook
        (vl-catch-all-apply
          'vlax-invoke-method
          (list workbooks 'Open filePath))) ; Attempt to open the file

  ; Handle workbook opening errors
  (if (vl-catch-all-error-p workbook)
    (progn
      (princ (strcat "\nFailed to open the Excel file. Check the file path or file integrity: " filePath))
      (vlax-release-object workbooks)
      (vlax-invoke-method excelApp 'Quit)
      (vlax-release-object excelApp)
      (exit))
  )

  ; Get the sheets in the workbook
  (setq sheets (vlax-get workbook 'Sheets))

  ; Retrieve the specific sheet by name
  (setq sheet (vl-catch-all-apply 'vlax-get-property (list sheets 'Item sheetName)))

  ; Handle sheet retrieval errors
  (if (vl-catch-all-error-p sheet)
    (progn
      (princ (strcat "\nSheet named \"" sheetName "\" not found."))
      (vlax-invoke-method workbook 'Close :vlax-false)
      (vlax-release-object workbook)
      (vlax-release-object workbooks)
      (vlax-invoke-method excelApp 'Quit)
      (vlax-release-object excelApp)
      (exit))
  )

  ; Get the cell object at B2 (row 2, column 2)
  (setq cell (vlax-get-property (vlax-get-property sheet 'Cells) 'Item 2 2))

  ; Get the value of the cell, handling both direct values and variants
  (setq cellValue
        (vl-catch-all-apply 'vlax-get-property (list cell 'Value)))

  ; Handle any errors during value retrieval
  (if (vl-catch-all-error-p cellValue)
    (setq cellValue nil)) ; Set to nil if there is an error

  ; Close the workbook and Excel
  (vlax-invoke-method workbook 'Close :vlax-false)
  (vlax-release-object workbook)
  (vlax-release-object workbooks)
  (vlax-invoke-method excelApp 'Quit)
  (vlax-release-object excelApp)

  ; Print the retrieved value for debugging
  (princ (strcat "\nRetrieved value from Excel cell B2: " (if cellValue (vl-princ-to-string cellValue) "nil")))

  ; Return the cell value
  cellValue
)

(defun update-table-if-condition-met (tableObj excelData)
  ; Check if the Excel data was successfully retrieved
  (if (not excelData)
    (princ "\nExcel data is missing or could not be read.")
    ; Ensure that the Excel data is numeric and valid for the table update
    (if (numberp excelData)
      (progn
        ; Update the table at the specified location (adjust indices as necessary)
        ; Example: Setting cell (9, 4) in the AutoCAD table to the value from Excel (B2)
        (vla-settext tableObj 9 4 (rtos excelData 2 2)) ; Convert the number to string for display
        (princ "\nTable updated successfully."))
      (princ "\nExcel data is not a number or is invalid.")))
  (princ))

 and this text to make the newTblObj

(setq newTblObj 
      (vla-AddTable 
        (vla-get-ModelSpace 
          (vla-get-ActiveDocument 
            (vlax-get-acad-object))) 
        (vlax-3D-point insPt) ; Convert list to variant array
        
        20 ; Row count, +1 for headers
        4 ; Column count
        100 ; Row height
        50)) ; Column width

thanks in advance 

[paullimapa]

could you also attach the xls file along with dwg with the table?

[S_S_SS]

Here you are sir 

[paullimapa]

Is this the row 9 column 4 you're referring to in the AutoCAD Table?

[paullimapa · Accepted solution]

Since you haven't had a chance to get back to me yet, I'll go ahead and post the revised code. You can always adjust on your own the row and column for the ACAD Table.

I made the following adjustments:

Use the following code to select the already created AutoCAD Table:

; select Acad Table if already inserted
  (while (not ss)
   (princ "\nSelect Acad Table to Update Cell...")
   (if (setq ss (ssget "_+.:E:S" '((0 . "ACAD_TABLE"))))
    (setq newTblObj (vlax-ename->vla-object (ssname ss 0)))
    (setq ss nil)
   ) 
  ) ; while
;  

Under your read-xlsx function I changed the following to properly get the cell value in Excel:

  ; Get the value of the cell, handling both direct values and variants
;  (setq cellValue
;        (vl-catch-all-apply 'vlax-get-property (list cell 'Value)))
  (setq cellValue
        (vl-catch-all-apply 'vlax-get-property (list (vlax-variant-value cell) 'Value))
  )
  ; Handle any errors during value retrieval
  (if (vl-catch-all-error-p cellValue)
    (setq cellValue nil)
	(setq cellValue (vlax-variant-value cellValue)) ; else return variant
  ) ; Set to nil if there is an error

Then under your update-table-if-condition-met function since you only have 3 columns your reference to 4 would fail in this code and since the Title is row 1 then row 9 is actually row 8:

;       (vla-settext tableObj 9 4 (rtos excelData 2 2)) ; Convert the number to string for display
       (vla-settext tableObj 8 3 (rtos excelData 2 2)) ; Convert the number to string for display

[S_S_SS]

Thanks very much sir
it works correctly 

[paullimapa]

Glad to have helped…cheers!!!

[S_S_SS]

Hello sir 
i need something additional
i need if the AutoCAD table column a  
match with the column a in the excel sheet 
take the item in the column d in the excel 
and put it to the AutoCAD table in column d 
I've tried to make this but it gives me error 
thanks in advance sir 

;*********************************************************************************************************
; updateTableFromXLSX
; imports specific excel file ".xlsx" sheet "11" and cell "B2" into an autocad table's cell 9, 4
; OP:
; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/taking-data-from-excel-sheet-to-the-autocad-table/m-p/13001214#M471251
(defun c:updateTableFromXLSX (/ ss)
  ; Read the data from the specified Excel file and sheet
  (setq excelData (read-xlsx "G:\\55.xlsx" "11")) ; Update file path and sheet name
;
select Acad Table if already inserted
  (while (not ss)
   (princ "\nSelect Acad Table to Update Cell...")
   (if (setq ss (ssget "_+.:E:S" '((0 . "ACAD_TABLE"))))
    (setq newTblObj (vlax-ename->vla-object (ssname ss 0)))
    (setq ss nil)
   ) 
    
    
    
    
    
  ) ; while
;  
  ; Update the table in AutoCAD if the condition is met
  (update-table-if-condition-met newTblObj excelData)
  (princ)
) ; defun 

(defun read-xlsx (filePath sheetName)
  (setq excelApp (vlax-get-or-create-object "Excel.Application")) ; Start Excel

  ; Ensure the file exists
  (if (not (findfile filePath))
    (progn
      (princ (strcat "\nFile not found or path is incorrect: " filePath))
      (exit))
  )

  ; Open the workbook
  (setq workbooks (vlax-get excelApp 'Workbooks))
  (setq workbook
        (vl-catch-all-apply
          'vlax-invoke-method
          (list workbooks 'Open filePath))) ; Attempt to open the file

  ; Handle workbook opening errors
  (if (vl-catch-all-error-p workbook)
    (progn
      (princ (strcat "\nFailed to open the Excel file. Check the file path or file integrity: " filePath))
      (vlax-release-object workbooks)
      (vlax-invoke-method excelApp 'Quit)
      (vlax-release-object excelApp)
      (exit))
  )

  ; Get the sheets in the workbook
  (setq sheets (vlax-get workbook 'Sheets))

  ; Retrieve the specific sheet by name
  (setq sheet (vl-catch-all-apply 'vlax-get-property (list sheets 'Item sheetName)))

  ; Handle sheet retrieval errors
  (if (vl-catch-all-error-p sheet)
    (progn
      (princ (strcat "\nSheet named \"" sheetName "\" not found."))
      (vlax-invoke-method workbook 'Close :vlax-false)
      (vlax-release-object workbook)
      (vlax-release-object workbooks)
      (vlax-invoke-method excelApp 'Quit)
      (vlax-release-object excelApp)
      (exit))
  )

  ; Get the cell object at B2 (row 2, column 2)
  (setq cell (vlax-get-property (vlax-get-property sheet 'Cells) 'Item 2 2))

  ; Get the value of the cell, handling both direct values and variants
;  (setq cellValue
;        (vl-catch-all-apply 'vlax-get-property (list cell 'Value)))
  (setq cellValue
        (vl-catch-all-apply 'vlax-get-property (list (vlax-variant-value cell) 'Value))
  )
  ; Handle any errors during value retrieval
  (if (vl-catch-all-error-p cellValue)
    (setq cellValue nil)
	(setq cellValue (vlax-variant-value cellValue)) ; else return variant
  ) ; Set to nil if there is an error

  ; Close the workbook and Excel
  (vlax-invoke-method workbook 'Close :vlax-false)
  (vlax-release-object workbook)
  (vlax-release-object workbooks)
  (vlax-invoke-method excelApp 'Quit)
  (vlax-release-object excelApp)

  ; Print the retrieved value for debugging
  (princ (strcat "\nRetrieved value from Excel cell B2: " (if cellValue (vl-princ-to-string cellValue) "nil")))

  ; Return the cell value 
  cellValue
)


;**************************************************************************************
(defun update-table-if-condition-met (tableObj excelData)
  ; Initialize the starting row for both the AutoCAD table and Excel sheet
  (setq acadRow 2 excelRow 2) ; Index 2 for A3 in both AutoCAD and Excel

  ; Loop through the AutoCAD table rows, starting from A3
  (while (<= acadRow (- (vla-get-rows tableObj) 1))
    ; Get the value from column A of the AutoCAD table
    (setq acadValue (vla-gettext tableObj acadRow 0))

    ; Get the value from column A and column D of the Excel sheet (using the read-xlsx function)
    (setq excelValueA (vlax-get-property (vlax-get-property excelData 'Cells) 'Item excelRow 1))
    (setq excelValueD (vlax-get-property (vlax-get-property excelData 'Cells) 'Item excelRow 4))

    ; Get the actual value from the Excel cells
    (setq excelValueA (vlax-variant-value (vlax-get-property excelValueA 'Value)))
    (setq excelValueD (vlax-variant-value (vlax-get-property excelValueD 'Value)))

    ; Check if the values in column A match (both are not nil and equal)
    (if (and acadValue excelValueA (eq acadValue excelValueA))
      (progn
        ; Update column D in the AutoCAD table with the value from Excel column D
        (vla-settext tableObj acadRow 3 (vl-princ-to-string excelValueD))
      )
    )

    ; Move to the next row in both the AutoCAD table and Excel sheet
    (setq acadRow (+ acadRow 1))
    (setq excelRow (+ excelRow 1))
  )
  (princ "\nTable updated based on matching column A values.")
)

i've tried to make this but it gives me error 

[paullimapa]

I'm going to give you some ideas and see if you can figure out how to solve your problem.

Since you're trying to access the Excel file to retrieve those cell values for comparison, you'll have to open the Excel file again just like how it's done in entirety in the read-xlsx function. You cannot just include the lines of code to just access those cells without opening Excel.

So one way is to include much of the beginning of the read-xlsx function which locates and opens the Excel file before the while loop of cell comparison and then after the while loop include the end of the read-xlsx function which closes and releases the Excel file. 

Another way would be to create a new function similar to read-xlsx but this time collect into a list the cell values from each row you want to compare and return this list back. Then in your while loop refer to this list to get the cell value information for comparison.

Now another tricky part about your while loop is that it'll only work if the Excel row # is same as the AutoCAD Table row#. Also if Excel has less rows than the AutoCAD Table, then the code will fail.

[paullimapa]

In theory the attached updated lisp should work.

You'll have to share your AutoCAD Table dwg for me to test and not just your Excel spreadsheet.

Again, the way you've written your update-table-if-condition-met function depends on the # rows in AutoCAD Table to be the same as your Excel rows. You may have to go back and revise this section when testing with the actual AutoCAD dwg and Excel file.

Here are the additions I've made:

1. To Open the Excel file again and return the sheet as an object I added function open-xlsx:

(There's no error checking since the same file was opened to read before earlier in the code)

;
; open-xlsx opens excel file and returns sheet in workbook as object
; Arguments:
; filePath = Excel file
; sheetName = name of sheet
(defun open-xlsx (filePath sheetName)
  (setq excelApp (vlax-get-or-create-object "Excel.Application")) ; Start Excel
  ; Open the workbook
  (setq workbooks (vlax-get excelApp 'Workbooks))
  (setq workbook
        (vl-catch-all-apply
          'vlax-invoke-method
          (list workbooks 'Open filePath))) ; Attempt to open the file
  ; Get the sheets in the workbook
  (setq sheets (vlax-get workbook 'Sheets))
  ; Retrieve the specific sheet by name
  (setq sheet (vl-catch-all-apply 'vlax-get-property (list sheets 'Item sheetName)))
  ; Return the sheet as object
  sheet 
) ; defun open-xlsx 

2. To Close & Release the Excel file I added function close-xlsx:

; close-xlsx closes workbook & excel
(defun close-xlsx ()
  ; Close the workbook and Excel
  (vlax-invoke-method workbook 'Close :vlax-false)
  (vlax-release-object workbook)
  (vlax-release-object workbooks)
  (vlax-invoke-method excelApp 'Quit)
  (vlax-release-object excelApp)
) ; close-xlsx

The above works because none of the following variables workbook, workbooks, excelApp have been declared local.

3. To get the value of the cell in a row and column of the worksheet I added function read-cell-value:

; read-cell-value from sheet in excel file
; Arugments:
; sheet = sheet object
; row = row #
; column = column #
(defun read-cell-value (sheet row column)
  ; Get the cell object at B2 (row 2, column 2)
  (setq cell (vlax-get-property (vlax-get-property sheet 'Cells) 'Item row column))

  ; Get the value of the cell, handling both direct values and variants
  (setq cellValue
        (vl-catch-all-apply 'vlax-get-property (list (vlax-variant-value cell) 'Value))
  )
  ; Handle any errors during value retrieval
  (if (vl-catch-all-error-p cellValue)
    (setq cellValue nil)
	  (setq cellValue (vlax-variant-value cellValue)) ; else return variant
  ) ; Set to nil if there is an error
  ; Return cellvalue as real #
  cellValue
) ; defun read-cell-value

4. Using the functions above your update-table-if-condition-met function will contain the following changes:

a. opens the excel file before the while loop

b. reads the cell values within the while loop

c. closes the excel file after the while loop

(defun update-table-if-condition-met (tableObj excelData)
  ; Initialize the starting row for both the AutoCAD table and Excel sheet
  (setq acadRow 2 excelRow 2) ; Index 2 for A3 in both AutoCAD and Excel

  ; open excel file again to get sheet object
  (setq sheet (open-xlsx "G:\\55.xlsx" "11")) 

  ; Loop through the AutoCAD table rows, starting from A3
  (while (<= acadRow (- (vla-get-rows tableObj) 1))
    ; Get the value from column A of the AutoCAD table
    (setq acadValue (vla-gettext tableObj acadRow 0))

    ; Get the value from column A and column D of the Excel sheet (using the read-xlsx function)
    (setq excelValueA (read-cell-value sheet excelRow 1))
    (setq excelValueD (read-cell-value sheet excelRow 4))

    ; Check if the values in column A match (both are not nil and equal)
    (if (and acadValue excelValueA (eq acadValue excelValueA))
      (progn
        ; Update column D in the AutoCAD table with the value from Excel column D
        (vla-settext tableObj acadRow 3 (vl-princ-to-string excelValueD))
      )
    )

    ; Move to the next row in both the AutoCAD table and Excel sheet
    (setq acadRow (+ acadRow 1))
    (setq excelRow (+ excelRow 1))
  ) ; while

  ; close & release excel
  (close-xlsx)

  (princ "\nTable updated based on matching column A values.")
) ; defun update-table-if-condition-met

Hopefully your testing will yield the results you need.

[S_S_SS]

Thanks sir for your interest 
but unfortunately it doesn't work 
kindly see the attached DWG 
Thanks in advance 

[paullimapa]

Could you also attach a more complete excel file for me to test when I get a chance later?

[S_S_SS]

here you are sir 

[paullimapa · Accepted solution]

Attached is the revised updateTableFromXLSX.lsp

Basically what you had in the update-table-if-condition-met function would have worked if you had started the Excel row at 3 instead of 2 because 2 is where you have the column Titles like "ITEM" & "Stock".

So since the identical AutoCAD Table in Excel always starts 1+ row then just change this line:

  ; Initialize the starting row for both the AutoCAD table and Excel sheet
  (setq acadRow 2 excelRow (1+ acadRow)) ; Index 2 for A3 in both AutoCAD and Excel

The following additional cleanup changes I made in the code are:

1. Moved all the functions under c:updateTableFromXLSX

2. Localized all the variables & subfunctions

3. Eliminated the multiple calls to open and read the Excel file. Now there's only one function: open-xlsx

4. Added a custom *error* function to close & release Excel in case it's still opened

5. Included in the beginning of code to set the Excel filename & sheetname

; set file and sheet info
 (setq filename "G:\\100.xlsx"
       sheetname "11"
 )

[S_S_SS]

thanks sir very much it worked correctly 

[paullimapa]

once again you're welcome...cheers!!!