* Open or Create a document from it's URL.
* New documents are created by URL's such as:
* private:factory/sdraw
* private:factory/swriter
* private:factory/scalc
* private:factory/simpress
FUNCTION OOoOpenURL( cURL )
LOCAL ARRAY aNoArgs[1]
aNoArgs[1] = OOoMakePropertyValue("Hidden",.F.)

LOCAL oDesktop
oDesktop = OOoGetDesktop()

LOCAL oDoc
oDoc = oDesktop.LoadComponentFromUrl(cURL,"_blank",0,@aNoargs)

* Make sure that arrays passed to this document are passed zero based.
COMARRAY(oDoc,10)

RETURN oDoc
ENDFUNC

* Create a com.sun.star.beans.PropertyValue struct and return it.
FUNCTION OOoMakePropertyValue( cName, uValue, nHandle, nState )
LOCAL oPropertyValue
oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )

oPropertyValue.Name = cName
oPropertyValue.Value = uValue

IF TYPE([nHandle])="N"
oPropertyValue.Handle = nHandle
ENDIF
IF TYPE([nState])="N"
oPropertyValue.State = nState
ENDIF

RETURN oPropertyValue
ENDFUNC

* Sugar coated routine to create any UNO struct.
* Use the Bridge_GetStruct() feature of the OLE-UNO bridge.
FUNCTION OOoCreateStruct( cTypeName )
LOCAL oServiceManager
oServiceManager = OOoGetServiceManager()

LOCAL oStruct
oStruct = .NULL.

LOCAL cOldErrHandler
cOldErrHandler = ON( "ERROR" )
ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
ON ERROR &cOldErrHandler

IF ISNULL( oStruct )
=__OOoReleaseCachedVars()
oServiceManager = OOoGetServiceManager()
oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
ENDIF

RETURN oStruct
ENDFUNC

* Return the OpenOffice.org desktop object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetDesktop()
IF (TYPE([goOOoDesktop])!="O") OR ISNULL( goOOoDesktop )
PUBLIC goOOoDesktop
goOOoDesktop = OOoServiceManager_CreateInstance( "com.sun.star.frame.Desktop" )
COMARRAY( goOOoDesktop, 10 )
ENDIF
RETURN goOOoDesktop
ENDFUNC

* Return the OpenOffice.org dispatcher object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetDispatcher()
IF (TYPE([goOOoDispatcher])!="O") OR ISNULL(goOOoDispatcher)
PUBLIC goOOoDispatcher
goOOoDispatcher = OOoServiceManager_CreateInstance("com.sun.star.frame.DispatchHelper")
COMARRAY(goOOoDispatcher,10)
ENDIF
RETURN goOOoDispatcher
ENDFUNC

* Return the OpenOffice.org service manager object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetServiceManager()
IF (TYPE([goOOoServiceManager])!="O") OR ISNULL( goOOoServiceManager )
PUBLIC goOOoServiceManager
goOOoServiceManager = CREATEOBJECT( "com.sun.star.ServiceManager" )
ENDIF
RETURN goOOoServiceManager
ENDFUNC

* Sugar coated routine to ask the service manager to
* create you an instance of some other OpenOffice.org UNO object.
FUNCTION OOoServiceManager_CreateInstance( cServiceName )
LOCAL oServiceManager
oServiceManager = OOoGetServiceManager()

LOCAL oInstance
oInstance = .NULL.

LOCAL cOldErrHandler
cOldErrHandler = ON( "ERROR" )
ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
oInstance = oServiceManager.createInstance( cServiceName )
ON ERROR &cOldErrHandler

IF ISNULL( oInstance )
=__OOoReleaseCachedVars()
oServiceManager = OOoGetServiceManager()
oInstance = oServiceManager.createInstance( cServiceName )
ENDIF

RETURN oInstance
ENDFUNC

PROCEDURE DoNothing__ErrorHandler( pnError, pcErrMessage, pnLineNo, pcProgramFileSys16, pcProgram, pcErrorParamSys2018 )
ENDPROC

PROCEDURE __OOoReleaseCachedVars()
RELEASE goOOoServiceManager, goOOoDesktop, goOOoCoreReflection
ENDPROC

* Convert a local filename to an OOo URL.
FUNCTION OOoConvertToURL( cFilename )
* Ensure leading slash.
IF LEFT( cFilename, 1 ) != "/"
cFileName = "/" + cFileName
ENDIF

LOCAL cURL
cURL = CHRTRAN( cFilename, "\", "/" ) && change backslashes to forward slashes.
cURL = "file://" + cURL
RETURN cURL
ENDFUNC

If AT("oo_proc", SET("PROCEDURE")) = 0
SET PROCEDURE TO oo_proc ADDITIVE
EndIf


LOCAL i, lcAlias, lcField
LOCAL loDoc, loSheet, loBorderLine
LOCAL ARRAY laArgs[1]

IF RECCOUNT(ThisForm.pageframe1.page1.grid1.RecordSource)>0

* Откроем OO Calc с "пустым" документом
loDoc = OOoOpenURL("private:factory/scalc")

* Прячем окно Calc'а чтобы пользователь не видел процесс заполнения
loDoc.GetCurrentController.GetFrame.GetContainerWindow.SetVisible(.T.)

* Выбираем первый лист
loSheet = loDoc.getSheets().getByIndex( 0 )

WITH loSheet

* Название отчета
* Объединим ячейки под заголовок
.getCellRangeByPosition(0, 0, ThisForm.pageframe1.page1.grid1.ColumnCount-1, 0).Merge(.T.)

* Размер шрифта 11
.getCellByPosition( 0, 0).charHeight=11

* "Жирный" шрифт (обычный - 100)
.getCellByPosition( 0, 0).charWeight=200

* Выравнивание "по горинтали" - по центру
.getCellByPosition( 0, 0).HoriJustify=2 && по центру

* Выравнивание "по вертикали" - по центру
.getCellByPosition( 0, 0).VertJustify=2 && по центру

* Пишем текст в ячейку
.getCellByPosition( 0, 0).SetString(ThisForm.Caption)

* Формируем "шапку" таблицы
FOR i=1 TO ThisForm.pageframe1.page1.grid1.ColumnCount
.getCellByPosition( i-1, 2).SetString(ThisForm.pageframe1.page1.grid1.Columns(i).Header1.Caption)
ENDFOR

* Форматируем "шапку" таблицы
* Работаем с диапазоном ячеек

* Размер шрифта 11
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, 2).charHeight=11

* "Жирный" шрифт (обычный - 100)
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, 2).charWeight=200

* Выравнивание "по горинтали" - по центру
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, 2).HoriJustify=2 && по центру

* Выравнивание "по вертикали" - по центру
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, 2).VertJustify=2 && по центру

.getRows.getByIndex(2).OptimalHeight(.T.) && автоподбор высоты

lnRowNum=3

WAIT 'Идет формирование отчета...' WINDOW NOWAIT NOCLEAR

lcAlias=ThisForm.pageframe1.page1.grid1.RecordSource
SELECT (lcAlias)

* Перебираем строки грида
SCAN
* Перебираем столбцы грида
FOR i=1 TO ThisForm.pageframe1.page1.grid1.ColumnCount
lcField=ThisForm.pageframe1.page1.grid1.Columns(i).ControlSource

* Заполняем данные
.getCellByPosition( i-1, lnRowNum).SetString(&lcField)
ENDFOR
lnRowNum=lnRowNum+1
ENDSCAN

FOR i=1 TO ThisForm.pageframe1.page1.grid1.ColumnCount

* автоподбор ширины столбца
.getColumns.getByIndex(i-1).OptimalWidth(.T.)
ENDFOR

* Рисование таблицы
loBorderLine = OOoCreateStruct("com.sun.star.table.BorderLine")
loBorderLine.color=RGB(0,0,0)
loBorderLine.lineDistance=0
loBorderLine.innerLineWidth=0
loBorderLine.outerLineWidth=1
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, lnRowNum-1).leftBorder=loBorderLine
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, lnRowNum-1).rightBorder=loBorderLine
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, lnRowNum-1).topBorder=loBorderLine
.getCellRangeByPosition( 0, 2, ThisForm.pageframe1.page1.grid1.ColumnCount-1, lnRowNum-1).bottomBorder=loBorderLine

WAIT CLEAR

* Делаем сформированный отчет видимым
loDoc.getCurrentController.getFrame.getContainerWindow.SetVisible(.T.)

ENDWITH

ELSE
=MESSAGEBOX("Нет данных!",0+48+0,"Внимание")
ENDIF

OLE IDsdpatch exeption code 1001 from[automation bridge]:
InterfaceOleWrapper_Impl::Invoke:
[automation bridge]UnoConversionUtilites<T>:variantToAny
The provided VARIANT of type"7" is unappropriate for....