Pasting a Calc spreadsheet into Writer loses formatting

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

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("") then
       doc = StarDesktop.loadComponentFromURL("private:factory/swriter","_default",0,Array())
       on error goto errExit
       tbl = getNewWriterTable(doc,sel.getColumns.getCount,sel.getRows.getCount)
       transferCellsContents sel, tbl
      Msgbox "Current selection is not a rectangle of spreadsheet cells", 16, "macro:CalcCellsFormattedToWriterCells"
       exit sub
   end if
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
                  cloneProperties rg,celltext
               elseif sType = "TextField" then
'                  myxray rg
                  cursor.HyperlinkURL = rg.TextField.URL
                  cursor.HyperlinkName = rg.TextField.Representation
                  cursor.HyperlinkTarget = rg.TextField.TargetFrame
               end if
'               print "cursor.implementationname: "& cursor.implementationname
               celltext = cursor.getEnd(true)
'               print "celltext.implementationname: "& celltext.implementationname
            if eLines.hasMoreElements() then
               celltext = cursor.getEnd(true)
            end if
End Sub

function getNewWriterTable(doc,cols, rows)
   tbl = doc.createInstance("")
   xrange = doc.getText()
   getNewWriterTable = tbl
end function

REM (C) Stephan Wunderlich
REM Archived-At: <>
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
    end if
end function

It 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). :slightly_smiling_face: