[Solved] Copy Calc Table content & paste into Writer Table
[Solved] Copy Calc Table content & paste into Writer Table
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
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
Last edited by Hagar Delest on Mon Mar 12, 2018 10:50 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Apache OpenOffice 4.1.3
Windows 8.1
Windows 8.1
Re: Simply Copy Calc Table content and paste into Writer Tab
You may need to copy on a cell by cell basis. Writer may not understand how to paste three cells into its table.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Simply Copy Calc Table content and paste into Writer Tab
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?
Apache OpenOffice 4.1.3
Windows 8.1
Windows 8.1
Re: Simply Copy Calc Table content and paste into Writer Tab
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?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Simply Copy Calc Table content and paste into Writer Tab
I will try your ideas, thank you!
Apache OpenOffice 4.1.3
Windows 8.1
Windows 8.1
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Simply Copy Calc Table content and paste into Writer Tab
Hi, and welcome to the forum.
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.
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.bvneck wrote:I want to simply copy the content of certain Calc cells into my Writer table cells:
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Simply Copy Calc Table content and paste into Writer Tab
Thank you so much - this is a very nice little work-around! I'll do it his way!!
Apache OpenOffice 4.1.3
Windows 8.1
Windows 8.1
Re: Simply Copy Calc Table content and paste into Writer Tab
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.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Simply Copy Calc Table content and paste into Writer Tab
This seems to work very well, too - thank you very much!
Apache OpenOffice 4.1.3
Windows 8.1
Windows 8.1
Re: [Solved] Copy Calc Table content & paste into Writer Tab
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.
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.
OpenOffice 4.1.6 on Windows 10