;; -*-Lisp-*-
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Description:  Hello, Excel!
;; Author:       Claus Brod  
;; Language:     Lisp
;;
;; (C) Copyright 2006 Claus Brod, all rights reserved
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;
;;================================================================================================
;;=========== STICHWORTE - IDEEN - HILFEN ========================================================
;;================================================================================================

#|
 [NEW()]
 [COPY()]
 [QUIT()]
 [QUIT("FALSE")]												--> was bedeutet "FALSE" ????????????
 [CLOSE("TRUE")]
 [CLOSE("FALSE")]
 [OPEN("c:\mypath\myfile.xls")]
 [BEEP]
 [SAVE.AS("c:\mypath\myfile.xls")]
 [Formula("=TODAY()","R1C2")]						--> zum schreiben von Werten in eine Zelle
 [WINDOW.SIZE(310,255)]
 [run("mymacro")] 
|#

(in-package :clausbrod.de)
(use-package :oli)

(setf si::*enter-break-handler* t)

;;================================================================================================
;;===========  G R U N D E I N S T E L L U N G E N ===============================================
;;================================================================================================

;Pfad zum Testdokument!
	(setq DATEI "D:TEST.xls")
(setq workbook 	"Test"	)
(setq Sheet 		"Tabelle1"				)
(setq DIAMETER 8)


;;================================================================================================
;;=========== F U N K T I O N S D E F I N I T I O N E N  =========================================
;;================================================================================================





(defun create-workbook()
  (setq dde (connect-to-excel))
    (unless dde
      (display "Cannot connect to Excel.")
      (return-from send-objects-to-excel nil)
    );;unless

    ;; open new sheet
    (send-excel-command dde "NEW()")
    
 );; create-workbook  
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun openfile (Datei)
	(setq dde (connect-to-excel))														;dde definieren
    (unless dde																						;wenn dde keinen Wert hat dann
      (display "Cannot connect to Excel.")
    );;unless

    ;; open File   
    (send-excel-command dde (format nil "OPEN(~S)" Datei))

);;openfile
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun closefile (COMMAND)
	(setq dde (connect-to-excel))														;dde definieren
    (unless dde																						;wenn dde keinen Wert hat dann
      (display "Cannot connect to Excel.")
    );;unless

    (disconnect-from-excel dde)														; DDE-Verbindung zu Excel trennen!
    
    ;; close File
    (display (format nil "~A()" COMMAND))
    (send-excel-command dde (format nil "~A" COMMAND))

);;closefile
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun send-diameter-to-excel (DIAMETER)
	(setq dde (connect-to-excel))														;dde definieren
    (unless dde																						;wenn dde keinen Wert hat dann
      (display "Cannot connect to Excel.")
    );;unless
    
    ;; write DIAMETER to Excel
    (set-cell dde "r10c2" DIAMETER)		;r1=Zeile 1  c1=Spalte 1

    
);;send-diameter-to-excel
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun get-value-from-excel ()
;;	(setq Sheethandle (oli:sd-dde-initiate "Excel" "[Mappe1]Tabelle1" ))
;; oder so ???
  (let ((Sheethandle (oli:sd-dde-initiate "Excel" "[Mappe1]Tabelle1" )))
    (if (eq :error Sheethandle) nil dde)
  );;let
	
(display (format nil "Sheethandle : ~A" Sheethandle ))  
  
    ;; get value
  	(print (oli:sd-dde-request Sheethandle sheet) "r10c3")
  	
);;get-result-from-excel
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun connect-to-sheet(workbook sheet)
 	(oli:sd-dde-initiate "Excel" (format nil "[~A]~A" workbook sheet))
 	
);;connect-to-sheet
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun connect-to-excel()
  (let ((dde (oli:sd-dde-initiate "Excel" "System")))
    (if (eq :error dde) nil dde)
  );;let
);;connect-to-excel
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun disconnect-from-excel(dde)
  (oli:sd-dde-close dde)
);;disconnect-from-excel
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun set-cell(dde cell formula) 
  (sd-dde-execute dde (format nil "[Formula(~S, ~S)]" formula cell))
);;set-cell
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
(defun send-excel-command(dde cmd)
	(display (format nil "SEND-EXCEL-COMMAND : [~A]" cmd))
  (oli:sd-dde-execute dde (format nil "[~A]" cmd))
);;send-excel-command
;; ----------------------------------------------------------------------------------------------------------------------------------------------------


;;================================================================================================
;;=========== D I A L O G ========================================================================
;;================================================================================================

;; Exports selected objects into Excel table; assumes that Excel is running
(sd-defdialog 'EXCEL_IN-OUT :dialog-title "EXCEL IN - OUT"
   
   :variables
   '(

;; ----------------------------------------------------------------------------------------------------------------------------------------------------
			(create 		:title "ERSTELLE MAPPE"
									:toggle-type :wide-toggle
									:initial-enable nil      
									:push-action 	(progn
																	    (create-workbook)
			 													);;progn
			);;create																																																	

;; ----------------------------------------------------------------------------------------------------------------------------------------------------
			(fileopen 			:title "LADE EXCELSHEET"
									:toggle-type :wide-toggle
									:push-action 	(progn
																	(openfile DATEI)
			 													);;progn
			);;fileopen																																																	
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
			(WriteCell 	:title "SCHREIBE WERT"
									:toggle-type :wide-toggle									
									:push-action 	(progn
																	(send-diameter-to-excel DIAMETER)
																);;progn
			);;WriteCell		
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
			(ReadCell 	:title "LESE WERT"
									:toggle-type :wide-toggle									
									:push-action 	(progn
																	(get-value-from-excel)
																	(display ERGEBNIS)
																);;progn
		
			);;ReadCell
;; ----------------------------------------------------------------------------------------------------------------------------------------------------
			(close 			:title "SCHLIESSE MAPPE"
									:toggle-type :wide-toggle									
									:push-action 	(progn
																	(closefile "CLOSE()"	)
																);;progn
		
			);;close						
;; ----------------------------------------------------------------------------------------------------------------------------------------------------														
;      (filename :value-type 		:filename 
;       					:direction 			:output 
;       					:initial-value 	'("foo.xls" :overwrite)
;      );;filename
;; ----------------------------------------------------------------------------------------------------------------------------------------------------    

);;variables
    
    :ok-action '(progn    							
    					
    						);;progn

);;sd-defdialog


(trace set-cell)
(trace closefile)
(trace send-excel-command)