How to write XML Spreadsheet format to clipboard to paste in Excel (javascript)

Question

I'm trying to put some data in clipboard (by clicking a button on a website), which will be then possible to paste in MS Excel (by Ctrl+V). The data needs to have some specific Excel-formatting, that's why I need to use "XML Spreadsheet" clipboard format, not HTML for example (although I know excel uses many other formats too).

I've tried Clipboard API (navigator.clipboard.writeText and navigator.clipboard.write) and document.execCommand('copy'), but it worked only for HTML, not for XML. However, not sure which mime type should be used. For "application/xml" Clipboard API throws: "DOMException: Type application/xml not supported on write."

Would prefer to avoid external libraries. document.execCommand('copy') is deprecated, so it's probably not a good idea to use it too.

Example XML to put in clipboard:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1"
   ss:DefaultRowHeight="14.4">
   <Row>
    <Cell><Data ss:Type="Number">100</Data></Cell>
    <Cell><Data ss:Type="Number">200</Data></Cell>
    <Cell><Data ss:Type="Number">300</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

This is the way I'm trying to achieve this (the code that throws an error for "application/xml" type, but works with HTML):

<html>
<body>
    <button onclick="myFunction()" >copy data to clipboard</button>

<script >
function myFunction() {
var str = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <Worksheet ss:Name="Sheet1">  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1"   ss:DefaultRowHeight="14.4">   <Row>    <Cell><Data ss:Type="Number">100</Data></Cell>    <Cell><Data ss:Type="Number">200</Data></Cell>    <Cell><Data ss:Type="Number">300</Data></Cell>   </Row>  </Table> </Worksheet></Workbook>';

var data = [new ClipboardItem({ "application/xml": new Blob([str], { type: "application/xml" }) })];
navigator.clipboard.write(data)
    }
</script>   

</body>
</html>

When I use "text/plain" type or navigator.clipboard.writeText() clipboard see data as a string and paste text in Excel ("<?xml version..."). Type: "text/html" seems to ignore XML formatting and paste all numbers in one cell.

I have found similar question, but it's not in javascript: How do I get OpenXML onto the clipboard so that it will paste into Excel? Maybe it would help.

How to write XML Spreadsheet format to clipboard to paste in Excel (javascript)

0 Answers0