LibreOffice Basic Macro command converting Calc cellRange to RTF/HTML

Question

My goal is to fill a LibreOffice calc sheet, and silently send a cell range by email when the user clicks the send-off button (and once more to confirm).

So there is three part to this.

• A push button with a request to confirm. (Easy and done.)
• Select Cell Range and turn it into rich text format (Haven't yet found)
• Send rich text email from within the sheet. (Will tackle the "silent" part later)

I tried copying the range to the clipboard with unoService but it seemed over-complicated and full of errors.

Here's what I have:

''''Send by e-mail enriched text

Sub Main
    Dim Doc, Sheet, Range, Rtf, Exec as Object
End Sub

'Confirm it
Sub SendTableApproval
    If MsgBox ("Ready to email?", MB_YESNO + MB_DEFBUTTON2) = IDYES Then
        CopyTable()
    End If
End Sub

'Copy it
Sub CopyTable
    Doc = ThisComponent
    View = Doc.CurrentController
    Frame = View.Frame
    Sheet = Doc.Sheets.getByIndex(0)
    Range = Sheet.getCellrangeByName("a1:f45")
    Exec = createUnoService("com.sun.star.frame.DispatchHelper")

    View.Select(Range)
    Cells = View.getTransferable()
    Exec.executeDispatch(Frame, ".uno:Deselect", "", 0, array())   
    'SimpleMailTo(Cells)
End Sub

'Mail it
Sub SimpleMailTo(body)
    Dim launcher as object
    Dim eAddress, eSubject, eBody, aHTMLanchor as string
    launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
    eAddress = "tu@domo.eg"
    eSubject = "Cotidie agenda futuendane"
    eBody = body
    aHTMLanchor = "mailto:" & eAddress & "?subject=" & eSubject & "&&body=" & eBody
    launcher.execute(aHTMLanchor, "", 0)
End Sub

I still do not know after three days of research over methods, properties, uno.

My question is, simply put, How can I convert a transferable content to HTML/RTF?

Answer 1

Simply copying and pasting into an email produces the result you are asking for. The code on the LibreOffice side should look like this.

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

It sounds like you already tried this, but something didn't work. Perhaps you could elaborate on what went wrong.

Another approach would be to write the spreadsheet to a temporary HTML or XHTML file. Then parse the temporary file to grab the part needed for the email.

AFAIK there is no such command to turn a cell range into rich text format with UNO. To do it that way, you would need to loop through each text range of each cell, read its formatting properties and then generate the HTML yourself.

EDIT:

Good idea about XTransferable. The following Java code adapted from the DevGuide gets an HTML string and then prints it. I believe this would be a good solution for your needs.

public void displayHTMLFromClipboard()
{
    try
    {
        Object oClipboard = xMCF.createInstanceWithContext(
            "com.sun.star.datatransfer.clipboard.SystemClipboard", xContext);
        XClipboard xClipboard = (XClipboard)
            UnoRuntime.queryInterface(XClipboard.class, oClipboard);
        XTransferable xTransferable = xClipboard.getContents();
        DataFlavor[] aDflvArr = xTransferable.getTransferDataFlavors();
        System.out.println("Available clipboard formats:");
        DataFlavor aChosenFlv = null;
        for (int i=0;i<aDflvArr.length;i++)
        {
            System.out.println(
                "MimeType: " + aDflvArr[i].MimeType +
                " HumanPresentableName: " + aDflvArr[i].HumanPresentableName );
            if (aDflvArr[i].MimeType.equals("text/html"))
            {
                aChosenFlv = aDflvArr[i];
            }
        }
        System.out.println("");
        try
        {
            if (aChosenFlv != null)
            {
                System.out.println("HTML text on the clipboard...");
                Object aData = xTransferable.getTransferData(aChosenFlv);
                String s = new String((byte[])aData, Charset.forName("ISO-8859-1"));
                System.out.println(s);
            }
        }
        catch (UnsupportedFlavorException exc)
        {
            exc.printStackTrace();
        }
    }
    catch(com.sun.star.uno.Exception exc)
    {
        exc.printStackTrace();
    }
}

If you plan to use Basic, it might be a good idea to do some research into the proper way to convert bytes. The code I have below seems to work but is probably unreliable and unsafe, and will not work for many languages. A few of my initial attempts crashed before this finally worked.

Sub DisplayClipboardData
    oClipboard = createUnoService("com.sun.star.datatransfer.clipboard.SystemClipboard")
    xTransferable = oClipboard.getContents()
    aDflvArr = xTransferable.getTransferDataFlavors()
    For i = LBound(aDflvArr) To UBound(aDflvArr)
        If aDflvArr(i).MimeType = "text/html" Then
            Dim aData() As Byte
            aData = xTransferable.getTransferData(aDflvArr(i))
            Dim s As String
            For j = LBound(aData) to UBound(aData)
                s = s & Chr(aData(j))  'XXX: Probably a bad way to do this!
            Next j
            Print(s)
        End If
    Next
End Sub

One more suggestion: Python might be a better language choice here. In many ways, using Python with LibreOffice is easier than Java. And unlike Basic, Python is powerful enough to comfortably handle byte strings.