I’m trying to import a spreadsheet from Calc into Writer. No matter what I do, text color and strikethrough doesn’t get pasted into Writer. Also, the headers won’t repeat when the table is broken between pages.
If the spreadsheet is smaller than a page, it is probably easiest to Edit > Paste Special > Paste Special (Ctrl+Alt+Shift+V) as LibreOffice x.x. Spreadsheet
If it will exceed one page then you could just paste normally (Ctrl+V) and size table to page width, then format the contents by hand. The table can be set to repeat headings by right-clicking on table, selecting Table Properties and in the dialogue that opens, select the tab Text Flow and set the number of rows to repeat.
Another option would be to export the spreadsheet to pdf and combine it with the Writer pdf on completion (would require another tool to combine pdfs, note that there is possible loss of functionality such as hyperlinks).
I can confirm that LibO V 7.1.2 doesn’t paste the strikethrough attribute (and also some additional attributes, including cell borders) if content/values/cellrange copied from a spreadsheet are pasted into a Writer document automatically creating a TextTable.
This is a bug, imo, and should be reported to bugs.documentfoundation.org.
That headers aren’t repeated for tables spanning pages simply is the default setting for text tables, and Writer can’t know whether or not the current user wants a repetition. It leaves it to you to edit this property of the table after pasting (as @ EarnestAl already described). Spreadsheets don’t have “header rows”, and I don’t know if it’s even possible technically to transfer the related setting (“Rows to Repeat”) for printranges to the target text by copy/paste. A feature request in the above linked bug tracker might at least find an answer to this question.
Anyway: Breaking tables with the general textflow is a doubtable layout, and I personally prefer to insert a sheet as an OLE object instead as a text table wherever possible…
This 15 year old macro may serve as a work-around until things are fixed in LO. It reads a selected range of Calc cells, generates an equally sized text table in a new text document and transfers all content and text formatting. The resulting text table can be copied into another text document without problems.
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
end if
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())
end if
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)
end if
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)
end if
next
end functionIt seems that the reason for the incorrect formatting of text in Basic has been clarified (as in the example above). The reason is in EndIf (can be changed to End If) and Wend (corrected only by replacing the loop type While / Wend with Do / Loop). 