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 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.