Need a way to join an array into a string that excel 2010 will see as separate rows. I've tried \n,\r,\r\n,\v, String.fromCharCode(10), String.fromCharCode(13). Currently it outputs columns just fine but rows won't work. Any help you can give would be greatly appreciated. The closest my googleing has gotten my is a 4 year old question with no answer.

javascript:(function(){
//values for row 1
var row1 = ['a','b','c'];
//values for row 2
var row2 = ['d','e','f'];
//array of rows
var all_rows = [];

//join with horizontal tab to create columns 
all_rows.push(row1.join('\t'));
all_rows.push(row2.join('\t'));

//**not working**, join with * to create row
var d = all_rows.join(String.fromCharCode(10));

//chicanery to put it on the clipboard
var textField = document.createElement('textarea');
textField.innerText = d;
document.body.appendChild(textField);
textField.select();
document.execCommand('copy');
textField.remove();
})();

CodePudding user response：

Use reverse engineering ;p

Filling manually rows in excel with that data, and then copy pasting it into the google chrome dev console will yield you the string required for it to happen. For 2022 version of excel is:

a\tb\tc\nd\te\tf

The textfield method with document.execCommand('copy') is actually malforming your selection, getting rid of the newlines, hence why it is not working. Maybe there is some workaround by using that approach although I suggest below:

To achieve this we have two options:

1. copy('foo') - which is only available in console in chrome dev tools
2. navigator.clipboard.writeText('foo') - which requires document to be focused , meaning there needs to be any action performed by an user on website ex. button click

First option

It will be as simple as opening the dev tools and pasting this into console:

let row1 = ['a', 'b', 'c'];
let row2 = ['d', 'e', 'f'];
let excelString = row1.join('\t')   '\n'   row2.join('\t');
copy(excelString)

Second option

The navigator.clipboard.writeText(foo) for security reasons requires any action performed by an user, meaning we cannot just run the code and use it for copying. We need to trigger any action - so simple example is onclick event on button.

<button onclick="copyExcelString()">copy</button>
<script>
    let row1 = ['a', 'b', 'c'];
    let row2 = ['d', 'e', 'f'];
    let excelString = row1.join('\t')   '\n'   row2.join('\t');

    function copyExcelString() {
        navigator.clipboard.writeText(excelString).then(
            () => {
                /* clipboard successfully set */
                console.log('copied');
            },
            () => {
                /* clipboard write failed */
                console.log('failed copying');
            }
        );
    }

</script>