; 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)
  ; 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
       (vla-settext tableObj 8 3 (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))