[Solved] Export datagridview to openoffice in VB.net

[Makmilan]

Hello
I created an application under VB.net
which is connected to a database under SQL Server
I would like to export data from a datagridview to a table in a Writer file
I would also like to add an image before this table
I've done this before with MS Office.
but I would like to be able to do it with Open Office Or Libre Office

i will accept any help
thanks a lot

[Villeroy]

menu:File>New>Database...
[X] Connect to existing
Type: ADO
A Windows wizard pops up where you specify the details.
[X] Register the data source.
Save the connection as a database document. So far it is a configuration file actually.
Add queries as needed.
Copy the icon of a query or table.
Paste into Writer. A wizard pops up.

While in Writer without database window:
Open the data source window (menu:View>Data sources)
Copy the table or query icon and paste.

[Villeroy]

Since you have set up your database connection by now, here you find a ready-made solution: [Writer] Stand-Alone Database Reports
You need a 2-row table for header and data, the embedded Python macro and then specify the connection details in the custom document properties That is the name of registered data source, connection type (table, query or SQL), the name of the table/query or the SQL string respectively, name of the target table in the Writer document and optionally the log-in credentials.

[Villeroy]

And http://www.openoffice.org/api/docs/comm ... eData.html is the interface that can be used to fill a strictly rectangular text table (no merged nor split cells).
For instance the 2 rows in the "Report_Table" of my template:

Code: Select all

REM  *****  BASIC  *****

Sub Main
h=Array("Identifier","Type","Author","Publisher","Title","Pages","Year","ISBN")
d = Array("A2","B2","C2","D2","E2","F2","G2","H2")
A = Array(h(), d())
t = ThisComponent.TextTables.getByName("Report_Table")
t.setDataArray(A())
End Sub

[Makmilan]

I would like to thank those who helped me

I modified a code that I found on Libre Office under VB.net
and I manage to export the data from the datagridview to a Libre Office Document Writer

I will post the VB.net code that I modified it may help another person

I now have to Add an image before this table from a BMP or Jpeg file
I don't know how to do that yet

and save the file and then print it

Code: Select all

Imports System.Data.SqlClient

 
Imports System
Imports System.Collections
Imports Microsoft.VisualBasic
Imports unoidl.com.sun.star.lang
Imports unoidl.com.sun.star.uno
Imports unoidl.com.sun.star.bridge
Imports uno.util

Public Class FormTestLO

    Sub ExportetoLibreOffice()


        Dim xContext As XComponentContext

        xContext = Bootstrap.bootstrap()


        Dim xFactory As XMultiServiceFactory
        xFactory = DirectCast(xContext.getServiceManager(),  _
            XMultiServiceFactory)

        'Create the Desktop
        Dim xDesktop As unoidl.com.sun.star.frame.XDesktop
        xDesktop = DirectCast(xFactory.createInstance("com.sun.star.frame.Desktop"),  _
            unoidl.com.sun.star.frame.XDesktop)

        'Open a new empty writer document
        Dim xComponentLoader As unoidl.com.sun.star.frame.XComponentLoader
        xComponentLoader = DirectCast(xDesktop, unoidl.com.sun.star.frame.XComponentLoader)
        Dim arProps() As unoidl.com.sun.star.beans.PropertyValue = _
            New unoidl.com.sun.star.beans.PropertyValue() {}
        Dim xComponent As unoidl.com.sun.star.lang.XComponent
        xComponent = xComponentLoader.loadComponentFromURL( _
       "private:factory/swriter", "_blank", 0, arProps)
        Dim xTextDocument As unoidl.com.sun.star.text.XTextDocument
        xTextDocument = DirectCast(xComponent, unoidl.com.sun.star.text.XTextDocument)

        'Create a text object
        Dim xText As unoidl.com.sun.star.text.XText
        xText = xTextDocument.getText()

        Dim xSimpleText As unoidl.com.sun.star.text.XSimpleText
        xSimpleText = DirectCast(xText, unoidl.com.sun.star.text.XSimpleText)

        'Create a cursor object
        Dim xCursor As unoidl.com.sun.star.text.XTextCursor
        xCursor = xSimpleText.createTextCursor()

        'Inserting some Text
        xText.insertString(xCursor, "Table des actes :" _
            & vbLf, False)


        '________________________________________________________________
        '   'Create a text object
        'Create instance of a text table with dgv columns and dgv rows

        Dim objTextTable As Object
        objTextTable = DirectCast(xTextDocument, unoidl.com.sun.star.lang.XMultiServiceFactory). _
            createInstance("com.sun.star.text.TextTable")
        Dim xTextTable As unoidl.com.sun.star.text.XTextTable
        xTextTable = DirectCast(objTextTable, unoidl.com.sun.star.text.XTextTable)
        '******************************************************
        Dim RowCount0 As Integer = dgv.Rows.Count
        Dim ColumnCount0 As Integer = dgv.Columns.Count

        xTextTable.initialize(RowCount0 + 1, ColumnCount0)
        '**********************************************************************
        xText.insertTextContent(xCursor, xTextTable, False)

        'Set the table background color
        Dim xPropertySetTable As unoidl.com.sun.star.beans.XPropertySet
        xPropertySetTable = DirectCast(objTextTable, unoidl.com.sun.star.beans.XPropertySet)
        xPropertySetTable.setPropertyValue("BackTransparent", New uno.Any(False))
        xPropertySetTable.setPropertyValue("BackColor", New uno.Any(&HFFFFFF))
        ' blanc &HFFFFFF
        'Bleu clair &HCCCCFF
        'Bleu foncé &H6666AA
        'Get first row
        Dim xTableRows As unoidl.com.sun.star.table.XTableRows
        xTableRows = xTextTable.getRows()
        Dim anyRow As uno.Any
        anyRow = DirectCast(xTableRows, unoidl.com.sun.star.container.XIndexAccess).getByIndex(0)

        'Set a different background color for the first row
        Dim xPropertySetFirstRow As unoidl.com.sun.star.beans.XPropertySet
        xPropertySetFirstRow = DirectCast(anyRow.Value, unoidl.com.sun.star.beans.XPropertySet)
        xPropertySetFirstRow.setPropertyValue("BackTransparent", New uno.Any(False))
        xPropertySetFirstRow.setPropertyValue("BackColor", New uno.Any(&HCCCCFF))

        'Fill the first table row
        insertIntoCell("A1", "Acte", xTextTable)
        insertIntoCell("B1", "Type", xTextTable)
        insertIntoCell("C1", "Prix", xTextTable)
        insertIntoCell("D1", "Cod", xTextTable)



        For Each row As DataGridViewRow In dgv.Rows
            Dim Acte As String = row.Cells(0).Value
            Dim Prix As String = row.Cells(1).Value
            Dim Type As String = row.Cells(2).Value
            Dim Cod As String = row.Cells(3).Value

            Dim R As Integer = row.Index


          
                R = R + 2

                Dim RStr As String = R

                Dim AIndex As String
                Dim Bindex As String
                Dim CIndex As String
                Dim DIndex As String

                AIndex = "A" + RStr
                Bindex = "B" + RStr
                CIndex = "C" + RStr
                DIndex = "D" + RStr


                insertIntoCell(AIndex, Acte, xTextTable)
                insertIntoCell(Bindex, Prix, xTextTable)
                insertIntoCell(CIndex, Type, xTextTable)
                insertIntoCell(DIndex, Cod, xTextTable)

             


        Next




    End Sub

    Sub insertIntoCell(ByVal sCellName As String, ByVal sText As String, _
         ByVal xTable As unoidl.com.sun.star.text.XTextTable)
        Dim xCell As unoidl.com.sun.star.table.XCell
        xCell = xTable.getCellByName(sCellName)

        Dim xSimpleTextCell As unoidl.com.sun.star.text.XSimpleText
        xSimpleTextCell = DirectCast(xCell, unoidl.com.sun.star.text.XSimpleText)

        Dim xCursor As unoidl.com.sun.star.text.XTextCursor
        xCursor = xSimpleTextCell.createTextCursor()

        Dim xPropertySetCursor As unoidl.com.sun.star.beans.XPropertySet
        xPropertySetCursor = DirectCast(xCursor, unoidl.com.sun.star.beans.XPropertySet)

        xPropertySetCursor.setPropertyValue("CharColor", New uno.Any(65536))
        xSimpleTextCell.insertString(xCursor, sText, False)
    End Sub

[Makmilan]

if you have an error
change that in app.config

<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>

[Makmilan]

hello
after several attempts to add an image before the DGV table that I had filled in
I failed using unoidl.com.sun.star DLLs

so I started again from the beginning
and i found this file
http://www.openoffice.org/fr/Documentat ... _v10fr.zip
I added to my project:
OOoConstants.vb
OOoTools.vb
OOoMessages.vb

Code: Select all

  

Sub Aoo()

        ConnectOpenOffice()



        Dim myDoc As Object, myText As Object, myCursor As Object
        Dim sUrl As String
        sUrl = "file:///D|/LO/Dv.odt"
        Dim sUrlImage

        sUrlImage = "file:///D|/Devis1.bmp"
        '

        myDoc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, dummyArray)
        myText = myDoc.Text
        myCursor = myText.createTextCursor




 
        Dim oGraph

        oGraph = myDoc.createInstance("com.sun.star.text.GraphicObject")
        With oGraph
            .GraphicURL = sUrlImage
            .Width = 16000
            .Height = 6250
        End With


        myText.insertTextContent(myCursor, oGraph, False)

        myText.insertControlCharacter(myCursor, OOOtextControlCharacterPARAGRAPH_BREAK, False)

        Dim Otable
        Otable = myDoc.createInstance("com.sun.star.text.TextTable")

        Dim RowCount0 As Integer = dgv.Rows.Count + 1
        Dim ColumnCount0 As Integer = dgv.Columns.Count



        Otable.initialize(RowCount0, ColumnCount0)
        '.insertTextContent(, Otable, False)

        myText.insertTextContent(myCursor, Otable, False)


        Otable.getCellByName("A1").String = "Colum1"
        Otable.getCellByName("B1").String = "Colum2"
        Otable.getCellByName("C1").String = "Colum3"
        Otable.getCellByName("D1").String = "Colum4"



        For Each row As DataGridViewRow In dgv.Rows

            Dim ValColum1 As String = row.Cells(0).Value
            Dim ValColum2 As String = row.Cells(1).Value
            Dim ValColum3 As String = row.Cells(2).Value
            Dim ValColum4 As String = row.Cells(3).Value

            Dim R As Integer = row.Index

            R = R + 2

            Dim RStr As String = R

            Dim AIndex As String
            Dim Bindex As String
            Dim CIndex As String
            Dim DIndex As String

            AIndex = "A" + RStr
            Bindex = "B" + RStr
            CIndex = "C" + RStr
            DIndex = "D" + RStr

            Otable.getCellByName(AIndex).String = ValColum1
            Otable.getCellByName(Bindex).String = ValColum2
            Otable.getCellByName(CIndex).String = ValColum3
            Otable.getCellByName(DIndex).String = ValColum4

 



        Next

       

 
    End Sub

now it works
but I still have to add text with a Large font: 16 in the middle
by using these OOoTools.vb file

I shared the code that works maybe it can help someone else
thank you to whoever can help me with the personalization of the text to add
and also if there is a way to position the table and the image in the created file