[Solved] Export to CSV with hyperlink in subsequent column

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
dacha
Posts: 10
Joined: Fri Jul 15, 2011 10:18 am

[Solved] Export to CSV with hyperlink in subsequent column

Post by dacha »

Hi

On bug 123928, the user wanted a way to export cells with hyperlinks to CSV, in such a way that the hyper links appears after the cell. Eg. if the document had:

1,Click here to visit Amazon,something else
(where clicking on "Click here" would open http://www.amazon.com),
then the CSV would have:
1,Click here to visit Amazon,https://www.amazon.com,something else

We could patch OpenOffice to offer this feature in the save dialog, but it seems like something very few users would want, and there are potentially other ways they might want to export hyperlinks (eg. by embedding the link after the text, like "Click here (http://www.amazon.com) to visit Amazon"). A macro seems like a better approach.

And here is a macro that creates a new spreadsheet, copies the currently selected cells to it, exporting links into subsequent columns as required. That new spreadsheet can then be saved.

Code: Select all

Sub ExportSelectedCellsWithHyperlinks
  spreadSheet = ThisComponent
  selection = spreadSheet.getCurrentController().getSelection()

  Dim dummy()
  newDoc = StarDesktop.LoadComponentFromURL("private:factory/scalc", "_default", 0, dummy)
  newSheet = newDoc.getCurrentController().ActiveSheet

  Dim currentRow As Integer
  Dim currentCol As Integer
  Dim newRow As Integer
  Dim newCol As Integer
  newRow = 0
  For currentRow = selection.RangeAddress.StartRow To selection.RangeAddress.EndRow
    newCol = 0
    For currentCol = selection.RangeAddress.StartColumn To Selection.RangeAddress.EndColumn
      cell = newSheet.getCellByPosition(newCol, newRow)
      cell.String = selection.getCellByPosition(currentCol, currentRow).String
      newCol = newCol + 1
      
      ' If that cell had hyperlinks, add them into succeeding columns:
      text = selection.getCellByPosition(currentCol, currentRow).Text
      If Not IsNull(text) Then
        Dim field As Integer
        For field = 0 To (text.TextFields.Count - 1)
          textField = text.TextFields.getByIndex(field)
          If Not IsNull(textField.URL) Then
            newSheet.getCellByPosition(newCol, newRow).String = textField.URL
            newCol = newCol + 1
          End If
        Next field
      End If
      
    Next currentCol
    newRow = newRow + 1
  Next currentRow
End Sub
To add this macro, go to "Tools" -> "Macros" -> "Organize Macros" -> "OpenOffice Basic". Expand "My Macros" -> "Standard" -> "Module 1". Select "Module 1", and click "Edit". Copy the above code and paste it at the end of the that file, then "Save" and exit that dialog.

To run the macro, open the document with hyperlinks you want to save as CSV. Select all cells with the data you want to save (eg. Ctrl+Shift+Down, then Ctrl+Shift+Left). Then go to "Tools" -> "Macros" -> "Run Macro", select "My Macros" -> "Standard" -> "Module 1", then select "ExportSelectedCellsWithHyperlinks" and click "Run". It will create a new spreadsheet and copy the selected cells to it, with any hyperlinks in cells to their right. This new spreadsheet can then be saved as CSV.

Hope you find it useful.

Many thanks to Villeroy whose excellent sample code from https://forum.openoffice.org/en/forum/v ... hp?t=84821 helped a lot.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Export to CSV with hyperlink in subsequent column

Post by Villeroy »

Alternatively, you can use my function CELL_URL from [Calc, Basic] Introspective cell functions. It extracts the URL(s) from the hyperlin(s) -- possibly more than one per cell. The resulting values can be exported as csv or whatever.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
alsOO
Posts: 7
Joined: Wed Jun 17, 2009 6:38 am

Re: [Solved] Export to CSV with hyperlink in subsequent column

Post by alsOO »

If I correctly understand your code, the key sequence is:
text = selection.....).Text
where Text is an array of fields which can be either text or hyperlinks. The only way to tell which is which is to parse for http.

If so, running this macro with a modification to detect the hyperlinks will work for my purpose.
Thank you, Dacha, for going to the effort to write this macro. I am aware of how poor the Basic macro documentation is...

Villeroy's code is great in that it provides a template for access to multiple aspects of a cell. I will be keeping a copy of this code. Thank you, Villeroy!

It still leaves OO output inconsistent for XML, HTML and CSV. XML and HTML output the hyperlinks, CSV doesn't.

At least now there is a work around for those who prefer CSV output with hyperlinks included. CSV is a heck of a lot easier to use for follow-on processing...

I will go to the original bug report and list it as resolved.
OOo 4.0.1 on Ms Windows 8
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: [Solved] Export to CSV with hyperlink in subsequent column

Post by Mountaineer »

CSV simply can not represent 2 values (text and link) in the same cell, so this code is actually a conversion to another table.
.
Another point: At a quick glance I could see no precautions for quoting/special characters. So the result can be broken, if the link include special characters - especially , is quite often seen in parameters.
As the csv is not directly writen to disk the qouting by AOO/LOveill handke this
.
I would prefer Villeroys CELL_URL, to create my table, then use the save-as of AOO/LO to get separators and quoting right as it gives me more freedom to arrange cells...

PS: csv is no standard and comes "as is" without any precautions for safety. So if one hates python3 for insisting on encodings for strings - welcome to write anything in a csv...
OpenOffice 3.1 on Windows Vista
Post Reply