$PBExportHeader$gf_save_dw_as_excel.srf
$PBExportComments$savedwtoexcel
global type gf_save_dw_as_excel from function_object
end type

forward prototypes
global function integer gf_save_dw_as_excel (datawindow p_dw)
end prototypes

global function integer gf_save_dw_as_excel (datawindow p_dw); /*------------------------------------------------------------------------------

 Function:			gf_Save_dw_as_excel

 Returns:         Integer

 Parameters:      DataWindow p_dw to be save as Excel via HTML

 Copyright © 1998-2001 DTI - Michael Nadar 

 Date Created: December 1, 1998 

 Description:	PB default save as excel sheet only saves the column list. This  
					  method of saving to excel involves saving the dw as a html and then
					  saving as excel via ole. This preserves the formatting and also 
					  saves the dw computed columns to the spreadsheet. PB's save as excel
					  does not save computed columns. 
 
--------------------------------------------------------------------------------
 Modifications:
 Date            Author              Comments
 02/23/1999		MXN		1) Move creation of ole below the saving of htm string to a 
								   file code, since this has some RETURN - 1's
									2) Window Appears to be frozen while excel is loading the 
									the .htm file. So put an hourglass around this.
 11/14/2001		P. Salgannik
 
 10/23/2002		P. Salgannik	DO NOT use this function from response windows!!!!
 									(unless it's being called from closing of the response window)
------------------------------------------------------------------------------*/ 


string 	ls_type, ls_name, ls_html_name, ls_html, ls_html_parse
string 	ls_excel_report_name, ls_secured_directory_check, ls_print_ind
integer 	li_filenumber, li_bytes, li_loop, li
integer 	li_rc
long 		ll_stringlen, ll_start, LL_EXCEL_STANDARD_FILE_TYPE
ulong ll_handle, ll_rc 
string 		ls_rc
boolean lb_exists
oleobject 	lole_excel, lole_workbook
//Pointer 		OldPointer
LL_EXCEL_STANDARD_FILE_TYPE = - 4143 		
		
		
// Get The directory in which to save the report
string   ls_dir, ls_file
li_rc = GetFileSaveName('Save As Excel', ls_dir,ls_file, 'XLS','Excel,*.xls')
IF li_rc =  0  THEN RETURN - 1 

IF FileExists(ls_dir) THEN 
	lb_exists = TRUE
	IF f_message_box( "Save As" , ls_dir +  " already exists.~r~nDo you want to replace it?" , &
							Exclamation!, YesNo!,  2 ) =  2  THEN
		RETURN - 1  
	END IF
	
END IF


// Next Two Modifys are not supported in PB  5 . 0  , so comment them
// 11 / 14 / 2001 		P. Salgannik Uncommented - works fine, generates borders
// 06 / 21 / 2002 		P. Salgannik commented again - HTML generation in PB8 is way up higher then
//Excel  97 's support for HTML, works fine in Excel 2000
//ls_rc = p_dw.Modify("datawindow.htmlTable.generateCSS = 'yes'") 
//ls_rc = p_dw.Modify("datawindow.htmlTable.nowrap = 'no'") 
//
ls_html = trim(p_dw.object.datawindow.data.htmltable)
If IsNull(ls_html) THEN RETURN -1
IF Len(ls_html) < 1 THEN RETURN -1


//ll_rc = gnv_app.inv_win32.of_SetCapture(handle(p_dw))
SetPointer(HourGlass!)

IF lb_exists THEN 
	FileDelete(ls_dir)
END IF

// the Saveas htmltable! only worked up until the html table reached 83K. After that
// different "stuff" appears in the stylesheet property.  By doing a filewrite this
// problem was elimanated.

li = Len(ls_dir)
ls_html_name 	= Mid(ls_dir, 1,li - 4) + ".htm"



ll_stringlen 	= len(ls_html) 


If ll_stringlen > 32765 Then
	If Mod(ll_stringlen, 32765) = 0 Then
		li_loop = ll_stringlen / 32765
	Else
		li_loop = (ll_stringlen / 32765) + 1
	End if
Else
	li_loop = 1
End if

li_fileNumber 	= FileOpen(ls_html_name, Streammode!, Write!, LockReadWrite!, Replace!)

IF li_fileNumber >= 0 THEN
	ll_start = 1
	FOR li = 1 to li_loop
	ls_html_parse = Mid(ls_html,ll_start,32765) 
	IF FileWrite(li_filenumber, ls_html_parse) = -1 THEN
		FileClose(li_filenumber)
		//gnv_app.inv_win32.of_ReleaseCapture()
		RETURN -1
	END IF
	ll_start += 32765
	NEXT
ELSE
	//gnv_app.inv_win32.of_ReleaseCapture()
	RETURN -1
END IF
FileClose(li_filenumber)


//save as excel
//for some reason doing a li_rc = lole_excel.application.workbooks.open(ls_html_name) would
//cause the application to hang.  This is true for any command used trying to perform an
//excel function.  (SaveAs, Open or Close)

//!!!! That's because all these methods return ole objects NOT error codes - P.Salgannik  11 / 14 / 2001 

//  lole_excel is a standard user object of type oleobject
lole_excel 	= create oleobject
// li_rc 	= lole_excel.ConnectToNewObject( "excel.application.8 ") SJZ removed . 8  to use current version of Excel
li_rc 	= lole_excel.ConnectToNewObject( "excel.application" )

IF li_rc <>  0  THEN
	destroy lole_excel
	//gnv_app.inv_win32.of_ReleaseCapture()
	RETURN - 1 
END IF

lole_workbook = lole_excel.application.workbooks.open(ls_html_name)
IF NOT ISValid(lole_workbook) THEN
	lole_excel.application.quit
	lole_excel.DisconnectObject()
	destroy lole_excel
	//gnv_app.inv_win32.of_ReleaseCapture()
	RETURN - 1 
END IF

lole_workbook.saveas(ls_dir,LL_EXCEL_STANDARD_FILE_TYPE)
//  6 / 20 / 02  P. Salgannik if you don't close(), the spreadsheet remains in read-only state,
// even after closing this application and excel
lole_workbook.close()

//delete html file !!!!!!!!!
IF fileexists(ls_html_name) THEN
	filedelete(ls_html_name)
END IF

lole_workbook.DisconnectObject()
lole_excel.application.quit
lole_excel.DisconnectObject()
DESTROY lole_workbook
destroy lole_excel		
//gnv_app.inv_win32.of_ReleaseCapture()

RETURN  1 
end function