[Solved] Copying formatted cell contents to a text document

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
StephJohnson
Posts: 4
Joined: Mon Sep 01, 2014 11:00 am

[Solved] Copying formatted cell contents to a text document

Post by StephJohnson »

I have a Calc spreadsheet cell that contains text. Some of the words are highlighted as bold or underline (e.g. Note: I mean some words not the whole cell).

I want to use a Basic macro to copy the cell's text to a Text document and retain this internal formatting.

Doing it manually, copy and paste creates a Calc object cell in the Text document (I can replicate this with Basic) but what I really want is the equivalent of Paste Special using RTF because this doesn't create the Calc object and puts the text neatly into the document. Unfortunately PasteSpecial doesn't work in Basic - it always pops up a dialogue asking for a format selection - I've seen this discussed in the forums but no one has found a solution to bypass this dialogue.

I can copy the text directly using (oDoc points to an empty Text document; oCell points to a single cell in the spreadsheet):

Code: Select all

oCursor = oDoc.Text.createTextCursor()
oCursor.String = oCell.String
But this doesn't keep the internal formatting.

Any help gratefully received - thanks.
Last edited by StephJohnson on Mon Mar 07, 2016 5:34 pm, edited 1 time in total.
Steph
OpenOffice: 4.1.2
OS: Windows 10 Home (version 1511)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying formatted cell contents to a text document

Post by Villeroy »

Get the spreadsheet range and a new text document.
Insert a text table with the same amount of rows and columns as the sheet range.
Loop through the sheet range cell by cell fetching the text cursor of the cell's text and its differently formatted text snippets.
Get the corresponding cell in the text table, transfer the text and all property values of the respective text snippet.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying formatted cell contents to a text document

Post by Villeroy »

My code of 2009 or so was lost because the old oooforum.org is lost but I found an old backup medium which included an OpenOffice profile of that time.
Select the cell range in question and run Sub CalcCellsFormattedToWriterCells()

Code: Select all

REM  *****  BASIC  *****
REM Copy cells of selected SheetCellRange into a new writer table, including formatting in cells.
REM Sheet-cells may have portions of formatted text, hyperlink-fields and line breaks.
Sub CalcCellsFormattedToWriterCells()
	sel = thisComponent.getCurrentController.getSelection()
	if sel.supportsService("com.sun.star.sheet.SheetCellRange") then
    	doc = StarDesktop.loadComponentFromURL("private:factory/swriter","_default",0,Array())
    	doc.lockControllers()
    	on error goto errExit
    	tbl = getNewWriterTable(doc,sel.getColumns.getCount,sel.getRows.getCount)
    	transferCellsContents sel, tbl
	else
		Msgbox "Current selection is not a rectangle of spreadsheet cells", 16, "macro:CalcCellsFormattedToWriterCells"
    	exit sub
   endif
errExit:
	doc.unlockControllers()
End Sub

Sub transferCellsContents(range,tbl)
   addr = range.getRangeAddress
   addr.EndColumn = addr.EndColumn - addr.StartColumn
   addr.EndRow = addr.EndRow - addr.StartRow
   addr.StartColumn = 0
   addr.StartRow = 0
'   myxray range.getCellByPosition(c,r).getText
'   exit sub
   for r = 0 to addr.EndRow
      for c = 0 to addr.EndColumn
         sheetcell = range.getCellByPosition(c,r)
         sheetcelltext = sheetcell.getText()
         cell = tbl.getCellByPosition(c,r)
         cursor = cell.createTextCursor()
         celltext = cursor.getEnd()
'         print "celltext.implementationname: "& celltext.implementationname
         eLines = sheetcelltext.createEnumeration()
         while eLines.hasmoreelements()
            para = eLines.nextElement()
'            print "para.implementationname: "& para.implementationname
'            celltext.insertTextContent(cursor.getEnd,para,false)
            eRanges = para.createEnumeration()
            while eRanges.hasMoreElements()
               rg = eRanges.nextElement()
'               print "rg.implementationname: "& rg.implementationname
               sType = rg.TextPortionType
'               myxray rg
'               prps = rg.getPropertySetInfo.getProperties()
               if sType = "Text" then
                  celltext.setString(rg.getString())
                  cloneProperties rg,celltext
               elseif sType = "TextField" then
'                  myxray rg
                  cursor.HyperlinkURL = rg.TextField.URL
                  cursor.HyperlinkName = rg.TextField.Representation
                  cursor.HyperlinkTarget = rg.TextField.TargetFrame
                  celltext.setString(rg.getString())
               endif
               cursor.collapseToEnd()
'               print "cursor.implementationname: "& cursor.implementationname
               celltext = cursor.getEnd(true)
'               print "celltext.implementationname: "& celltext.implementationname
            wend
            if eLines.hasMoreElements() then
               cell.insertControlCharacter(cursor,com.sun.star.text.ControlCharacter.LINE_BREAK,False)
               cursor.collapseToEnd()
               celltext = cursor.getEnd(true)
            endif
         wend
      next
   next
End Sub
function getNewWriterTable(doc,cols, rows)
   tbl = doc.createInstance("com.sun.star.text.TextTable")
   tbl.initialize(rows,cols)
   xrange = doc.getText()
   xrange.insertTextContent(xrange,tbl,true)
   getNewWriterTable = tbl
end function

REM (C) Stephan Wunderlich
REM Archived-At: <http://permalink.gmane.org/gmane.comp.openoffice.devel.api/14674>
function cloneProperties(original, clone)
On Error resume next
properties = original.getPropertySetInfo.getProperties
for i=0 to UBound(properties)
    aName = properties(i).Name
    aValue = original.getPropertyValue(aName)
    if (NOT isNull(aValue)) AND (NOT isEmpty(aValue)) then
        clone.setPropertyValue(aName,aValue)
    endif
next
end function 
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
StephJohnson
Posts: 4
Joined: Mon Sep 01, 2014 11:00 am

Re: [Solved] Copying formatted cell contents to a text docum

Post by StephJohnson »

Thank you Villeroy, very helpful.

It wasn't quite what I wanted since it creates a writer table and I just wanted a simple paragraph. However, inspecting your code gave me the information I was missing in regard to cloning the properties of the text element from the spreadsheet cell to the writer paragraph.

I've got it working now.
Steph
OpenOffice: 4.1.2
OS: Windows 10 Home (version 1511)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Copying formatted cell contents to a text docum

Post by Villeroy »

And as you can see in the comment lines, I used a tool named "xray" to find all properties and methods of an object.
You can find "Xray" and a more advanced tool "MRI" in the extensions repository.
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
Post Reply