Hello Andrew,
Thanks for your reply. I'll take a look at your examples and will
let you know if I succeeded.
Just for your info I include my complete macro so you can take a
look at what I'm doing.
It's not completely working but most parts work, I can generate my
complete document with TOC and save it.
The database is a Postgresql-database that I registered in OO-base.
REM ***** BASIC *****
dim sale_order_name as String
dim offerte as Object
dim DataBaseContext as Object
dim DataSource as Object
dim DBcon as Object
dim DBcon2 as Object
dim DBcon3 as Object
dim DBcon4 as Object
dim DBsql as Object
dim DBsql2 as Object
dim DBsql3 as Object
dim DBsql4 as Object
dim DBsql5 as Object
dim sale_order as Object
dim sale_order_id as Integer
Dim chapters as Object
Dim checklist as Object
Dim products as Object
Dim sale_order_type as Object
Dim sale_order_lines as Object
Dim Dummy()
Dim url as String
Dim Cursor as Object
Dim dispatcher as Object
Dim level as Integer
Dim mDoc as Object
Dim mCurs as Object
Dim oIndex as Object
Sub Main
x=open_db()
sale_order_name = inputbox("Geef offertenr aub","Offerte")
DBsql=DBcon.createStatement()
DBsql2=DBcon.createStatement()
DBsql3=DBcon.createStatement()
DBsql4=DBcon.createStatement()
DBsql5=DBcon.createStatement()
sale_order=DBsql.executeQuery("SELECT id, type_id,
project_description, checklist_id FROM sale_order WHERE name='" +
sale_order_name + "'"
If Not Isnull(sale_order) Then
While sale_order.next
x=aanmaken_offerte()
sale_order_id=sale_order.getInt(1)
type_id=sale_order.getInt(2)
chapters=DBsql2.executeQuery("SELECT title, image_url,
description, level1_seq, chp_level, lines, checklist, products FROM
sale_order_chapter WHERE " + _
"type_id = " + type_id + "
ORDER BY seq_nbr")
sale_order_type=DBsql3.executeQuery("SELECT image_url
FROM sale_order_type WHERE " + _
"id = " + type_id)
document = ThisComponent.CurrentController.Frame
dispatcher =
createUnoService("com.sun.star.frame.DispatchHelper")
mDoc = ThisComponent
mCurs = mDoc.currentController.getViewCursor()
dim args4(3) as new com.sun.star.beans.PropertyValue
While sale_order_type.next
args4(0).Name = "FileName"
args4(0).Value = sale_order_type.getString(1)
args4(1).Name = "FilterName"
args4(1).Value = "<Alle indelingen>"
args4(2).Name = "AsLink"
args4(2).Value = false
args4(3).Name = "Style"
args4(3).Value = "Afbeeldingen"
dispatcher.executeDispatch(document,
".uno:InsertGraphic", "", 0, args4())
dispatcher.executeDispatch(document,
".uno:SetAnchorToPara", "", 0, Array())
dispatcher.executeDispatch(document,
".uno:InsertLinebreak", "", 0, Array())
dispatcher.executeDispatch(document,
".uno:InsertPagebreak", "", 0, Array())
Wend
mCurs = mDoc.currentController.getViewCursor()
oIndex =
ThisComponent.CreateInstance("com.sun.star.text.ContentIndex")
oIndex.CreateFromOutline = True
ThisComponent.getText().insertTextContent(mCurs,
oIndex, False)
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(1) as new com.sun.star.beans.PropertyValue
dim args3(2) as new com.sun.star.beans.PropertyValue
level=0
While chapters.next
if chapters.getInt(4) > level then
dispatcher.executeDispatch(document,
".uno:InsertPagebreak", "", 0, Array())
end if
level=chapters.getInt(4)
if chapters.getInt(5) = 1 then
args2(0).Name = "Template"
args2(0).Value = "Kop 1"
args2(1).Name = "Family"
args2(1).Value = 2
dispatcher.executeDispatch(document,
".uno:StyleApply", "", 0, args2())
else
args2(0).Name = "Template"
args2(0).Value = "Kop 3"
args2(1).Name = "Family"
args2(1).Value = 2
dispatcher.executeDispatch(document,
".uno:StyleApply", "", 0, args2())
end if
args1(0).Name = "Text"
args1(0).Value = chapters.getString(1)
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
dispatcher.executeDispatch(document,
".uno:InsertPara", "", 0, Array())
args2(0).Name = "Template"
args2(0).Value = "Tekstblok"
args2(1).Name = "Family"
args2(1).Value = 2
if chapters.getBoolean(6) then
REM tabel invoegen
args4(0).Name = "TableName"
args4(0).Value = "Tabel1"
args4(1).Name = "Columns"
args4(1).Value = 3
args4(2).Name = "Rows"
args4(2).Value = 2
args4(3).Name = "Flags"
args4(3).Value = 11
dispatcher.executeDispatch(document,
".uno:InsertTable", "", 0, args4())
args1(0).Name = "Text"
args1(0).Value = "Product"
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
dispatcher.executeDispatch(document,
".uno:JumpToNextCell", "", 0, Array())
args1(0).Name = "Text"
args1(0).Value = "Hoev."
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
dispatcher.executeDispatch(document,
".uno:JumpToNextCell", "", 0, Array())
args1(0).Name = "Text"
args1(0).Value = "Prijs"
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
sale_order_lines=DBsql4.executeQuery("SELECT name, product_uom_qty,
" + _
"(product_uom_qty *
price_unit) AS amount, " + _
"(SELECT seq_document from
product_product where product_product.id =
sale_order_line.product_id) AS seq " + _
" FROM sale_order_line
WHERE " + _
"order_id = " +
sale_order_id + " ORDER BY seq")
While sale_order_lines.next
naam=sale_order_lines.getString(1)
qty=sale_order_lines.getString(2)
amount=sale_order_lines.getString(3)
args1(0).Name = "Text"
args1(0).Value = naam
dispatcher.executeDispatch(document,
".uno:JumpToNextCell", "", 0, Array())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
args1(0).Name = "Text"
args1(0).Value = qty
dispatcher.executeDispatch(document,
".uno:JumpToNextCell", "", 0, Array())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
args1(0).Name = "Text"
args1(0).Value = amount
dispatcher.executeDispatch(document,
".uno:JumpToNextCell", "", 0, Array())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
Wend
mCurs.gotoEnd(False)
end if
rem if chapters.getBoolean(7) then
rem REM tabel invoegen
rem args4(0).Name = "TableName"
rem args4(0).Value = "Tabel2"
rem args4(1).Name = "Columns"
rem args4(1).Value = 2
rem args4(2).Name = "Rows"
rem args4(2).Value = 2
rem args4(3).Name = "Flags"
rem args4(3).Value = 9
rem dispatcher.executeDispatch(document,
".uno:InsertTable", "", 0, args4())
rem checklist=DBsql4.executeQuery("SELECT
question, answer, " + _
rem "(SELECT answer FROM
survey_response_answer, survey_response_line " + _
rem "WHERE
survey_response_answer.answer_id = survey_answer.id " + _
rem "AND
survey_response_answer.response_id = survey_response_line.id " + _
rem "AND
survey_response_line.question_id = survey_question.id " + _
rem "AND
survey_response_line.response_id = survey_response.id) AS
description " + _
rem "FROM survey_response,
survey, survey_page, survey_question, survey_answer WHERE " + _
rem "survey_response.id = " + sale_order.getInt(4) + " AND " + _
rem "survey.id =
survey_response.survey_id AND " + _
rem "survey_page.survey_id = survey.id AND " + _
rem "survey_question.page_id = survey_page.id AND " + _
rem "survey_answer.question_id = survey_question.id ORDER BY
survey_question.sequence")
rem
rem while checklist.next
rem question=checklist.getString(1)
rem answer=checklist.getString(2)
rem description=checklist.getString(3)
rem args1(0).Name = "Text"
rem args1(0).Value = question
rem dispatcher.executeDispatch(document, ".uno:InsertText", "", 0,
args1())
rem args1(0).Name = "Text"
rem args1(0).Value = answer
rem dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "",
0, Array())
rem dispatcher.executeDispatch(document, ".uno:InsertText", "", 0,
args1())
rem args1(0).Name = "Text"
rem args1(0).Value = description
REM dispatcher.executeDispatch(document, ".uno:InsertPara", "", 0,
Array())
rem dispatcher.executeDispatch(document, ".uno:InsertLinebreak",
"", 0, Array())
rem dispatcher.executeDispatch(document, ".uno:InsertText", "", 0,
args1())
rem if not checklist.islast() then
rem dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "",
0, Array())
rem end if
rem Wend
rem mCurs.gotoEnd(False)
rem end if
if chapters.getBoolean(8) then
products=DBsql5.executeQuery("SELECT
sale_order_line.name, product_template.description_sale,
product_product.image, seq_document " + _
"FROM sale_order_line,
product_product, product_template WHERE " + _
"sale_order_line.order_id = " + sale_order_id + " AND " + _
"product_product.id =
sale_order_line.product_id AND " + _
"product_template.id =
product_product.product_tmpl_id AND " +_
"product_template.description <> '' " + _
"ORDER BY seq_document")
While products.next
naam=products.getString(1)
desc=products.getString(2)
args1(0).Name = "Text"
args1(0).Value = naam
args2(0).Name = "Template"
args2(0).Value = "Kop 3"
args2(1).Name = "Family"
args2(1).Value = 2
dispatcher.executeDispatch(document,
".uno:StyleApply", "", 0, args2())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
args1(0).Name = "Text"
args1(0).Value = desc
args2(0).Name = "Template"
args2(0).Value = "Tekstblok"
args2(1).Name = "Family"
args2(1).Value = 2
dispatcher.executeDispatch(document,
".uno:StyleApply", "", 0, args2())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
Wend
end if
if not isnull(chapters.getString(3)) then
args1(0).Name = "Text"
args1(0).Value = chapters.getString(3)
dispatcher.executeDispatch(document,
".uno:StyleApply", "", 0, args2())
dispatcher.executeDispatch(document,
".uno:InsertText", "", 0, args1())
dispatcher.executeDispatch(document,
".uno:InsertPara", "", 0, Array())
rem dispatcher.executeDispatch(document,
".uno:InsertLinebreak", "", 0, Array())
end if
Wend
oIndex.update()
x=opslaan_offerte(sale_order_name)
Wend
Else
MsgBox "Offerte bestaat niet, probeer opnieuw aub"
End If
sale_order.Close
DBsql.Close
DBcon.Close
DBcon.Dispose
End Sub
Sub open_db()
DataBaseContext=createUNOService("com.sun.star.sdb.DatabaseContext")
DataSource=DatabaseContext.getByName("indiegroup")
DBcon=DataSource.GetConnection("","")
End Sub
Sub aanmaken_offerte()
url = "private:factory/swriter"
offerte = StarDesktop.loadComponentFromURL(url, "_blank", 0,
Dummy())
End Sub
Sub opslaan_offerte(naam)
url = "file:///home/alain/Data/Indiegroup/" + naam + ".odt"
offerte.storeAsUrl(url, Dummy())
offerte.dispose
End Sub
Greetings,
Alain
On 15-08-13 22:58, Andrew Douglas Pitonyak wrote:
I believe that there are examples of inserting an image
AndrewMacro.odt and probably also OOME (also available from my web
site).
Does this at least get you started?
You stated that you were pulling data from a DB, how do you get
your data from your database?
On 08/15/2013 05:48 AM, Alain Van Utterbeeck wrote:
Hello all,
I'm writing a macro to create a document entirely based on
database-contents.
I have an image (page-sized) that should come on the first page,
and then I should put the title on top of it and the jump to the
next page. The part that follows (TOC + further contents of the
document) is about finished and working but I have trouble with
my title-page.
I tried to record the inserting of the image, then add text on
top of it using the drawing toolbar, then set the properties of
that text to centered (horizontal and vertical), size it to the
text and then jump to the next page.
This resulted in this:
sub afbeelding_en_titel
rem
----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem
----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem
----------------------------------------------------------------------
dim args2(3) as new com.sun.star.beans.PropertyValue
args2(0).Name = "FileName"
args2(0).Value =
"file:///home/alain/Data/Indiegroup/Cover%20Offerte%20Site%20Builder.jpg"
args2(1).Name = "FilterName"
args2(1).Value = "<Alle indelingen>"
args2(2).Name = "AsLink"
args2(2).Value = false
args2(3).Name = "Style"
args2(3).Value = "Afbeeldingen"
dispatcher.executeDispatch(document, ".uno:InsertGraphic", "", 0,
args2())
rem
----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:InsertDraw", "",
0, Array())
rem
----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:TransformDialog",
"", 0, Array())
rem
----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:TextAttributes", "",
0, Array())
rem
----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertLinebreak", "",
0, Array())
As you can see the recording didn't work after the inserting of
the image and I find no documentation that accurately helps me
figure it out the InsertDraw, TransformDialog and TextAttributes
myself.
Could anybody help me on this one?
Any suggestion is welcome...
Thanks in advance,
Alain