[Solved] Copy Calc Table content & paste into Writer Table

[bvneck]

Hello,
I can't figure out how to do a very simple and basic operation. I have a table in Writer document, and I have some data in a Calc document. I want to simply copy the content of certain Calc cells into my Writer table cells: I go to Calc, mark let's say 3 table cells, "Strg+Copy", the I go to Writer, mark in my table 3 empty cells and "Strg+V". But the table data is not pasted correctly. I tried basically all the "copy content" options - I found no way to solve this. Can someone help me here please?!
Thanks, bvneck

[RoryOF]

You may need to copy on a cell by cell basis. Writer may not understand how to paste three cells into its table.

[bvneck]

Thank you very much. Since there are about 100-150 cells to copy (in groups) this is not really a workign solution for my problem. I'm a bit surprised, writer isn't able to manage this task?

[RoryOF]

I don't know - I offer it only as a possibility. Why not set up a sheet of your calc file to display only the information you need, then paste that into Writer to serve as the table? Or insert it as an OLE object?

[bvneck]

I will try your ideas, thank you!

[MrProgrammer]

Hi, and welcome to the forum.

I want to simply copy the content of certain Calc cells into my Writer table cells:

Let's say you have a Writer table TableW 100 rows by four columns (100×4) and a Calc range of 100×3. The goal is to copy the first Calc column to the first Writer column and the third Calc column to the fourth Writer column.

Select the 100×3 range in Calc. Edit → Copy. Open a new Writer document. Edit → Paste Special → Formatted Text. You now have a 100×3 Writer table which we'll call Table1. Select the cells in Table1's first column. Edit → Copy. Select the first cell in TableW. Edit → Paste. Select the cells in Table1's third column. Edit → Copy. Select the uppermost right cell in TableW. Edit → Paste.

This isn't optimal, since it requires a tempory Writer table, but it's better then performing the work on a cell by cell basis.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[bvneck]

Thank you so much - this is a very nice little work-around! I'll do it his way!!

[Villeroy]

Paste-special as unformatted text into a new, blank text document.
Then select all (Ctrl+A) and menu:Table>>Convert>Text to Table with Tab as column separator.
------------
The following Basic module reads the selected sheet cell range, generates a blank new text table and dumps everything including in-cell formatting, hyperlinks and line breaks within cells.

Code: Select all

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

[bvneck]

This seems to work very well, too - thank you very much!

[zdanman4u]

Villeroy posted the best/easiest solution

simply paste as UNFORMATTED TEXT

then go to Table -> Convert -> Text to Table and use Tabs as separators.

Its really quite pathetic that Writer does not have this capability built in.