Search code examples
javascriptexport-to-excelcopy-paste

Confused about copy / paste behavior into excel from my React JS site when tabs are involved?

I'm working on a site and one of the features is to copy some text to the clipboard so that the users can paste this text into their excel spreadsheets. What they'd be copying to the clipboard is actually multiple values delimited by tabs so that when they copy into excel (assuming they kept the default text to columns functionality) it would split the text into multiple cells.

For example, I have this:

getCopyText() {
   return `a \t b \t b \t ${FAKE_FORECAST_VALUE} \t d \t e`
}

render() {
   return (
       ....
            <CopyToClipboard text={this.getCopyText()}>
              <Button">
                Copy to Clipboard
              </Button>
            </CopyToClipboard>
       ...
   )
}

When I click the button to copy the text to the clipboard and paste it into Excel though, it doesn't seem to respond to the tabs and it just looks like spaces (also doesn't separate values into columns).

To sanity check, I tried pasting the original clipboarded string into Word and turned showing tab characters on. It showed the tab characters. enter image description here

I then copied the pasted string from Word into Excel. I was confused to see that if I copied the string from Word (which was just pasted from the clipboard) into Excel, it would respond to the tab characters and split the text into multiple columns. enter image description here

So in summary:

  1. Copying to clipboard -> Pasting into Excel = doesn't seem to preserve tabs, doesn't split values into columns, doesn't work
  2. Copy to clipboard -> Pasting into Word (preserves tabs) -> Copying the pasted string from Word into Excel = preserves tabs, splits values into columns

Of course, I don't want the users to have to paste the string somewhere else and recopy it before being pasted into Excel but I'm pretty stumped about what's going on here, especially since it looks like the tab characters are in the string (can be seen when pasting from clipboard into Word or from clipboard into vim / notepad etc). Is there something I could do from the JS side of things or something to put in the string to help with this so the user doesn't have to deal with it?

Solution

  • A day or two after I posted this question, I actually found this person's answer which helped explain what was actually going on: here. I'm still not totally sure if / how it explains how pasting to Word and then copying from there into Excel works though.

    However, I did want to point out that for my specific use case, I got the copy to clipboard -> paste to excel functionality working by making sure that plain text was copied to the clipboard instead of it being detected as HTML.

    Since I was using the CopyToClipboard dependency (React variety), this was just a matter of adding in an option that lets you specify that text should be copied as text/plain.

    CopyToClipboard options: The description of the option.format: String. Optional. Set the MIME type of what you want to copy as. Use text/html to copy as HTML, text/plain to avoid inherited styles showing when pasted into rich text editor.