Author Topic: Read excel (intact open files)  (Read 5538 times)

0 Members and 1 Guest are viewing this topic.

kruuger

  • Swamp Rat
  • Posts: 625
Read excel (intact open files)
« on: November 17, 2014, 07:40:44 AM »
hi,

is there any way to read excel file and leave intact open files.
most of lisp always close excel when done.

thanks
kruuger

Lee Mac

  • Seagull
  • Posts: 12906
  • London, England
Re: Read excel (intact open files)
« Reply #1 on: November 17, 2014, 08:36:16 AM »
hi,

is there any way to read excel file and leave intact open files.
most of lisp always close excel when done.

thanks
kruuger

Yes, but my code is unfortunately not freeware  :-(

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #2 on: November 17, 2014, 08:47:56 AM »
hi,

is there any way to read excel file and leave intact open files.
most of lisp always close excel when done.

thanks
kruuger

Yes, but my code is unfortunately not freeware  :(
thanks Lee. that's also helps. i can try to dig it more.
any tips ? are you save somehow opened files and restore or solution is elsewhere ?
pure lisp ?

thanks
kruuger


VovKa

  • Water Moccasin
  • Posts: 1626
  • Ukraine
Re: Read excel (intact open files)
« Reply #3 on: November 17, 2014, 09:03:42 AM »
Code: [Select]
(defun vk_ExcelNumToAbc (Num)
  (if (< Num 27)
    (chr (+ Num 64))
    (strcat (vk_ExcelNumToAbc (/ (1- Num) 26))
    (chr (+ (if (zerop (rem Num 26))
      26
      (rem Num 26)
    )
    64
)
    )
    )
  )
)
(defun vk_GetXls (FileName
  SheetName
  Range
  /
  ExcelObj
  IsExcelRunning
  IsFileOpenned
  BooksObj
  BookObj
  SheetsObj
  SheetObj
  CellsObj
  LastCellObj
  OutList
  *error*
)
  (if (and FileName
   (findfile FileName)
   (or (and (setq ExcelObj (vlax-get-object "Excel.Application"))
    (setq IsExcelRunning t)
       )
       (setq ExcelObj (vlax-create-object "Excel.Application"))
   )
   (or IsExcelRunning
       (not (vl-catch-all-error-p
      (vl-catch-all-apply
'vlax-put-property
(list ExcelObj "Visible" :vlax-false)
      )
    )
       )
   )
   (not
     (vl-catch-all-error-p
       (setq BooksObj (vl-catch-all-apply 'vlax-get-property (list ExcelObj "WorkBooks")))
     )
   )
   (or (and (not
      (vl-catch-all-error-p
(setq BookObj (vl-catch-all-apply
'vlax-get-property
(list BooksObj
      "Item"
      (strcat (vl-filename-base FileName) (vl-filename-extension FileName))
)
      )
)
      )
    )
    (setq IsFileOpenned t)
       )
       (not (vl-catch-all-error-p
      (setq BookObj (vl-catch-all-apply
      'vlax-invoke-method
      (list BooksObj "Open" FileName)
    )
      )
    )
       )
   )
   (not
     (vl-catch-all-error-p
       (setq SheetsObj (vl-catch-all-apply 'vlax-get-property (list BookObj "Sheets")))
     )
   )
   (not (vl-catch-all-error-p
  (setq SheetObj (vl-catch-all-apply
   'vlax-get-property
   (if SheetName
     (list SheetsObj "Item" SheetName)
     (list BookObj "ActiveSheet")
   )
)
  )
)
   )
   (or Range
       (not
(vl-catch-all-error-p
   (setq CellsObj (vl-catch-all-apply 'vlax-get-property (list SheetObj "UsedRange")))
)
       )
   )
      )
    (setq OutList (mapcar (function
    (lambda (row)
      (mapcar (function
(lambda (cell)
  (vlax-variant-value (vlax-variant-change-type cell vlax-vbString))
)
      )
      row
      )
    )
  )
  (vlax-safearray->list
    (vlax-variant-value
      (vlax-get-property
(vlax-get-property
  SheetObj
  "Range"
  (cond (Range)
((strcat "A1:"
(vk_ExcelNumToAbc
   (vlax-get-property (vlax-get-property CellsObj "Columns") "Count")
)
(itoa (vlax-get-property (vlax-get-property CellsObj "Rows") "Count"))
)
)
  )
)
"Value"
      )
    )
  )
  )
    )
  )
  (foreach Obj (list (list LastCellObj)
     (list CellsObj)
     (list SheetObj)
     (list SheetsObj)
     (list BookObj
   (lambda ()
     (if (not IsFileOpenned)
       (vl-catch-all-apply 'vlax-invoke-method (list BookObj "Close" :vlax-false))
     )
   )
     )
     (list BooksObj)
     (list ExcelObj
   (lambda ()
     (if (not IsExcelRunning)
       (vl-catch-all-apply 'vlax-invoke-method (list ExcelObj "Quit"))
     )
   )
     )
       )
    (if (and (car Obj) (not (vl-catch-all-error-p (car Obj))))
      (progn (eval (cdr Obj)) (vlax-release-object (car Obj)))
    )
  )
  (gc)
  OutList
)
;;;(vk_GetXls "C:\\Temp\\Book1.xls" "Sheet1" nil)
;;;(vk_GetXls (getfiled "" "" "xls" 0) nil nil)
« Last Edit: November 19, 2014, 05:46:40 AM by VovKa »

Patrick_35

  • Guest
Re: Read excel (intact open files)
« Reply #4 on: November 17, 2014, 09:36:28 AM »
Hi

Try this

Work with excel, openoffice (not tested with libreoffice)

@+

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #5 on: November 17, 2014, 03:36:13 PM »
VovKa thanks for sample. you open xls file in another window. maybe not perfect but it is some kind of workaround.
but i noticed one thing. you not "kill" excel - by quit or close - not sure which one is correct. excel is still present in window task manager. if we start routine one more time autocad freeze
1. close all excel
2. run vk_GetXls
3. we get data
4. look task window - excel still running
5. try run vk_GetXls one more time - autocad freeze

Patrick_35 also thanks for file. lot of stuff :) need to go thru all of this.

kruuger
« Last Edit: November 17, 2014, 03:45:46 PM by kruuger »

VovKa

  • Water Moccasin
  • Posts: 1626
  • Ukraine
Re: Read excel (intact open files)
« Reply #6 on: November 17, 2014, 04:11:07 PM »
VovKa thanks for sample. you open xls file in another window. maybe not perfect but it is some kind of workaround.
but i noticed one thing. you not "kill" excel - by quit or close - not sure which one is correct. excel is still present in window task manager. if we start routine one more time autocad freeze
what version of Excel do use?
try to add one more (gc) call in the end.

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #7 on: November 17, 2014, 04:45:35 PM »
VovKa thanks for sample. you open xls file in another window. maybe not perfect but it is some kind of workaround.
but i noticed one thing. you not "kill" excel - by quit or close - not sure which one is correct. excel is still present in window task manager. if we start routine one more time autocad freeze
what version of Excel do use?
try to add one more (gc) call in the end.
2010. one more (gc) but nothing.
Code: [Select]
...
    (if (and Obj (not (vl-catch-all-error-p Obj)))
      (vlax-release-object Obj)
    )
  )
  (gc)
  (gc)
  OutList
)

VovKa

  • Water Moccasin
  • Posts: 1626
  • Ukraine
Re: Read excel (intact open files)
« Reply #8 on: November 17, 2014, 05:07:02 PM »
2010. one more (gc) but nothing.
i use 2003 :(
i'll try to find 2010 to test

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #9 on: November 17, 2014, 06:23:06 PM »
Patrick_35 just finished review your file. was a little scary because lots of subroutine but everything is write very clean and clear.

use this to read file:
Code: [Select]
(setq Mon_Appli (lancer_excel))
(setq lst (liste_fichiers_ouverts Mon_Appli))
(setq Mon_Classeur (ouvrir_fichier Mon_Appli "d:\\test.xls"))
(setq lst (liste_feuilles Mon_Classeur))
(lire_cellule Mon_Classeur "Arkusz1" "A1")
;(sauver_fichier Mon_Classeur) ; comm1
(vl-catch-all-error-p (vl-catch-all-apply 'vlax-invoke (list Mon_Classeur 'store))) ; comm2
(fermer_fichier Mon_Classeur) ; comm3
(fermer_appli (list Mon_Appli Mon_Classeur))
what is bother my is why file is always saved (comm #1) with (sauver_fichier Mon_Classeur) even if we just read file ?
if i replace with store (comm #2) all is ok.

if file which we try to read is open can we skip line #1, #2, #3 (do not save, close) and after read file release all objects ?
dozen of reads will not "blow up" something ?

thanks
kruuger

VovKa

  • Water Moccasin
  • Posts: 1626
  • Ukraine
Re: Read excel (intact open files)
« Reply #10 on: November 18, 2014, 08:58:24 AM »
i use 2003 :(
i'll try to find 2010 to test
i've found excel2010 and (with the help of google, of course) found the solution
code updated

Patrick_35

  • Guest
Re: Read excel (intact open files)
« Reply #11 on: November 18, 2014, 10:10:34 AM »
What I have given is a library of several functions. (it is true that, read permiere times, it's been scary, but it's pretty simple definitive)
One can very well read and close the file without saving.

For example with Excel
Code: [Select]
(setq Mon_Appli (lancer_excel)) ; --> #<VLA-OBJECT _Application 00000000301e8f58>
(setq Mon_Classeur (ouvrir_fichier  Mon_Appli "c:\\Test\\Classeur1.xls")) ; --> #<VLA-OBJECT Sheets 00000000301e8fc8>
(setq Ma_Feuille (feuille_active Mon_Classeur)) --> "Feuil1"

If it's not "Feuil1" and i want activate "Feuil1"
Code: [Select]
(activer_feuille Mon_classeur "Feuil1") ; --> T , it's OK
To read cell "A1" in "Feuil1"
Code: [Select]
(lire_cellule Mon_Classeur "Feuil1" "A1") ; --> Return value
Close file
Code: [Select]
(fermer_fichier Mon_Classeur)
Close Excel
Code: [Select]
(fermer_appli (list Mon_Appli Mon_Classeur))
You have the same functions to work with Openoffice / Libreoffice.
There is just the first function to determine choose to work with.

@+

Patrick_35

  • Guest
Re: Read excel (intact open files)
« Reply #12 on: November 18, 2014, 10:26:29 AM »
(sauver_fichier Mon_Classeur) even if we just read file ?
With this function, you save your file and it's not want you want.

@+

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #13 on: November 18, 2014, 10:37:26 AM »
(sauver_fichier Mon_Classeur) even if we just read file ?
With this function, you save your file and it's not want you want.

@+
great thanks. all works well.
kruuger

kruuger

  • Swamp Rat
  • Posts: 625
Re: Read excel (intact open files)
« Reply #14 on: November 18, 2014, 04:08:43 PM »
i use 2003 :(
i'll try to find 2010 to test
i've found excel2010 and (with the help of google, of course) found the solution
code updated
almost. when we read file but from other sheet than file was saved we need to confirm exit - save or cancel.
Patrick_35 solved close here:
Code: [Select]
;-------------------------------------------------------------------
;
; Fermer le fichier ouvert
; Attention, si le fichier a été modifié, il ne sera pas sauvegardé
; Sauf pour une liaison SQL
; Close sheets
;
; Entrée --> Classeur
;
; Retourne T si tout s'est bien passée ou nil.
;
; Exemple
; (fermer_fichier Mon_Classeur) --> T
;
;-------------------------------------------------------------------
(defun fermer_fichier(classeur / val)
  (setq val T)
  (cond
    ( (not (vl-catch-all-error-p (vl-catch-all-apply 'vlax-get (list classeur 'creator))))
      (setq val (vl-catch-all-error-p (vl-catch-all-apply 'vlax-invoke (list (vlax-get classeur 'parent) 'close :vlax-false))))
    )
    ( (not (vl-catch-all-error-p (vl-catch-all-apply 'vlax-invoke (list classeur 'close :vlax-false))))
      (setq val nil)
    )
    ( (and (vlax-property-available-p classeur 'provider)
   (wcmatch (strcase (vlax-get classeur 'provider)) "MICROSOFT*")
      )
      (setq val (vl-catch-all-error-p (vl-catch-all-apply 'vlax-invoke (list classeur 'close :vlax-true))))
    )
  )
  (not val)
)