Using GetExcel to read data from multiple sheets

[teebro]

Hi Everyone,

I'm new to AutoCAD lisp and have a couple of questions regarding GetExcel from the GetExcel.lsp functions. I am generating several tables using data from an excel spreadsheet. The spreadsheet contains several "Sheets" and a new table will be generated from each sheet. It will be unknown how many "Sheets" the excel spreadsheet will have. I do not see any easy way to find out how many sheets are in the spreadsheet so I wrote a function that will call GetExcel and compare cell "A1" with a known value. This value should be in cell "A1" for all used sheets. My problem is that if a blank sheet is not added as the last sheet my function will fail. I do not know a lot about the GetExcel code but it appears that if a particular sheet is requested and is not in the spreadsheet the function returns the sheet that corresponds to the looped sheet number. For example if a spreadsheet has 3 sheets and you request "Sheet4" it returns the data from "Sheet1". The idea was for the LISP routine to call GetExcel with successive "Sheet" numbers, test "A1" for a particular value, if the value was expected then save the *exceldata@ array to a new variable to get the next sheet. I was also under the impression that the "GetExcel" function opened the excel file, grabbed the data and closed it. When GetExcel is called multiple times a popup is presented prompting the user to "Close all excel spreadsheets to continue". When "OK" is pressed the function works as expected. I'm probably not using the GetExcel code as it was intended but maybe someone can help me out.

I have posted some code that I was initially testing with to get the number of sheets in the spreadsheet. TIA

 

(defun GetSheetCount ( excelfile / count continue )
  (setq count 1)
  (setq continue 1)
  (while (= continue 1)
     (progn
        (getexcel excelfile (strcat "Sheet" (rtos count 2 0)) "A1")
        (if (= (car (car *exceldata@)) "Section Title")
           (progn
              (setq count (1+ count))
           )
           (setq continue (1- continue))
        )   
     ) 
  )
  (1- count)
) 

[ZakiKaz]

Hi,

 

I was also surfing for the same, and I came across this code, don't know who developed. But it served my purpose. Still if you are in need or any other developers need can be benefited from this. This is Autolisp/Visual lisp code

(defun Getsheetsname (ExcelFile$ / Column# ColumnRow@ Data@ ExcelRange^
    ExcelValue ExcelValue ExcelVariant^ MaxColumn# MaxRow# Range$ Row# workbook Worksheet)
    (if (= (type ExcelFile$) 'STR)
        (if (not (findfile ExcelFile$))
            (progn
                (alert (strcat "Excel file " ExcelFile$ " not found."))
                (exit)
            )
        )
        (progn 
            (alert "Excel file not specified.")
            (exit)
        )
    )
    (gc)
    (if (not (setq *ExcelApp% (vlax-get-object "Excel.Application")))
        (progn
            (alert "Close all Excel spreadsheets to continue!")
            (vlax-release-object *ExcelApp%)(gc)
        )
    )

    (setq ExcelFile$ (findfile ExcelFile$)
                *ExcelApp% (vlax-get-or-create-object "Excel.Application")
                workbook (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
                    Sheets (vlax-get-property Workbook "Sheets")
                    sheet_list '()
        )
        (vlax-for sh (vlax-get-property Workbook "Sheets")
            (setq sheet_list (append sheet_list (list (vlax-get-property sh "Name"))))
  )
    (print sheet_list)
    (princ)
)

 

Zaki Kaz

Edited November 7 by SLW210
Added Code Tags!!

[SLW210]

Please use Code Tags in the future. (<> in the editor toolbar)

[BIGAL]

I started with getexcel and found a few functions were missing so added some more, in the program attached there are various defuns that you can use, I do acknowledge that I got some from FIXO who did a lot of Excel lisps, unfortunately he is no longer with us, others are mentioned in the code for their help. A great one is getrange, select or type a range. Another very handy is get the current sheet range returns the 1st and last cell in the current sheet, handy for row column use.

 

There is a lot of code out there just have to spend a little time looking say via Google. Add Fixo in search string.

 

Just a comment when you get the property sheets it has a property count. 

 

(setq x 1)
(setq num (vlax-get-property xlSheets "Count"))
(repeat num
(setq curSheet (vlax-get-property xlSheets "Item" x))
; do something with that sheet
(setq x (1+ x))
)

 

You may already do this in some situations need the user to change cell values then read the excel, so I use an (alert "Press ok when finished editing excel ") this holds CAD till your ready.

 One thing I was trying to do was via windows to jump to Excel as current windows program I got close using powershell but could not get it to work, at moment must click in Excel to make current. Then click back into CAD.

Alan Excel library.lsp

Edited November 7 by BIGAL