Excel behaves strange with XSLX file created manually

Question

Based on knowledge gained through working with the OpenXML SDK, I have implemented an Excel generator in JS (using TypeScript with ReactJS and a custom JSX factory generating plain XML). The files generated open fine in Excel and one can also edit and save them fine in Excel, no errors.

However, if one tries to copy cells (even a single one) from such a generated Excel file to another worksheet in the same Excel instance, it fails with the error "The command cannot be used on multiple selections.". Just saving and reopening the file is enough to fix the problem. Copying to other applications (e.g. Notepad) works fine.

It seems that this particular error is shown by Excel in several edge cases where the data is not exactly meet the expected format, for instance I found reports of that happening when a sheet is hidden when manipulating it via VBA. However, in my case I'm not sure what could be causing the issue.

Just saving the file in Excel unfortunately significantly alters its parts, so that I couldn't get a meaningful diff out of it. I did not see what could be causing the problem. Maybe someone has some experience with the internals of Excel?

To get a sample file, copy the following into your browser address bar and save it as xlsx file:

data:application/vndopenxmlformats-officedocumentspreadsheetmlsheet;base64,UEsDBBQAAAgIAAAAAAA69A4d5wAAAGYBAAAPAAAAeGwvd29ya2Jvb2sueG1sjZA9T8MwEIZ3JP7DyTt1AAmhKEkXBOqCMgC7Y1+SU/0R3bktPx+3ocxM9/k+9+qa7XfwcEQWSrFV95tKAUabHMWpVZ8fr3fPCrbd7U1zSrwfUtpDEURp1ZzzUmstdsZgZJMWjGUyJg4ml5InLQujcTIj5uD1Q1U96WAoqpVQ838YaRzJ4kuyh4AxrxBGb3KxKzMtoro/Zz3rrrmck98Ikk3GVh1JaPCoIJpQyi/CE/SHwZNdQZBGeGOcEpOJoOAi3rnyDwVcU0l45x5Vwesr3+FIEd17AUrpW+Ntz3AOZ1112b0a634AUEsDBBQAAAgIAAAAAAD2SCbhNwEAAMYCAAANAAAAeGwvc3R5bGVzLnhtbJ1STWvDMAy9D/YfjO+rk8DGGEl6KAR22aUd7OokSmvwF7Zbkv36yXFK20EZ7GJJz++9KLLK9agkOYHzwuiK5quMEtCd6YXeV/Rz1zy9UrKuHx9KHyYJ2wNAICjRvqKHEOwbY747gOJ+ZSxovBmMUzxg6fbMWwe891GkJCuy7IUpLjStS31UjQqedOaoQ0UzyupyMPqCFDQBSOUKyInLim64FK0TkevF9wLmeaxZ4qazvQBz8HghpLz1RqAuLQ8BnG6wIEu+myxUVBsNyGJXhGg2hz+ke8envHi+p54DdtQa1+PYzz3l9Awh5VeC7A6k3MYX+BpuJONA0ijf+zhFEn/3nOJ3ljTZpCLO5toteV/ZFv+yJdxaOX0cVQuumVcgNcjG4b5dftduEbKlt7rsx+F2W9hlIesfUEsDBBQAAAgIAAAAAABh+IC4iAEAAGIDAAAYAAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1shZNNT8MwDIbvSPyHKCc4sGzdxsfUFsEQEhJCSOPjnKXeFtEkVWLY4NfjtKUaaBqXyLXzPn7jpOnlxpTsA3zQzmZ80OtzBla5Qttlxp+fbk/OObvMDw/StfNvYQWAjBQ2ZHyFWE2ECGoFRoaeq8BSZeG8kUiffilC5UEWtciUIun3T4WR2vI8rXMvGtZhK2Yo5zMoQSEUZIWz2HLu3Fss3lGqT8pKWmCbWVVqjAn22Ya0HV11DwucQllm/CrhTCrUH/BIiozPHaIzsc5ZQImUWnj3BZYLslB3pQmw6u9mqorO4XZMzpUrm5UZbWsLRm4a57rAFUXD3tlglJyPu5Uz9R6I/dpuiPwOkLSApAOMkz+A8V7AsAUMtwFb3Wnd72DUAkYd4CLZJRDN0eth3EiUeerdmvn67IFmSM9jMBnRdamYvIrZ5ooyrm2pLczQU1UTA3MCADt6fpoepwKJHbNCtdrr/dqpK2CHarpf9SDNLtXNPz4hKK+r+E5+iwUd/udpNNMQ3d+SfwNQSwMEFAAACAgAAAAAAI86L6y8AAAAmQEAABoAAAB4bC9fcmVscy93b3JrYm9vay54bWwucmVsc7WQSwrCMBBA94J3CLO3qQoiYupGBLdSDxDSaRvaJiETP729KYJacOHG1TC/N4/Z7u5dy67oSVsjYJ6kwNAoW2hTCTjnh9ka2C6bTrYnbGWIQ1RrRyxuGRJQh+A2nJOqsZOUWIcmdkrrOxli6ivupGpkhXyRpivuPxmQjZgs7x3+QrRlqRXurbp0aMIXMKfQt0jAcukrDAKeeRI5wI6FAH8sFsD/dv5mfUM1YngbvEpRbgjzkcxykOGjB2cPUEsDBBQAAAgIAAAAAABja/EoqQAAABkBAAALAAAAX3JlbHMvLnJlbHONz7EKwjAQBuBd8B3C7Tatg4g07SJCV6kPENNrGtrmQhK1vr0ZVRwcf+6/D/6yXuaJ3dEHQ1ZAkeXA0CrqjNUCLu1pswdWV+tVecZJxlQKg3GBpS8bBAwxugPnQQ04y5CRQ5suPflZxhS95k6qUWrk2zzfcf9uQPVhsvbp8B+R+t4oPJK6zWjjD/irAayVXmMUsEz8QX68Eo1ZQoE1nQDfdAXwquQfA6sXUEsDBBQAAAgIAAAAAAAUVUFPBQEAAJkCAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbK2Sv07DMBDGdyTewfJaxU4ZEEJJOkA7AkN5AONcEiv+J59b0rfHcQsDKmXpdLLv+77fneVqNRlN9hBQOVvTJSspAStdq2xf0/ftpnigZNXc3lTbgwckSW2xpkOM/pFzlAMYgcx5sKnTuWBETMfQcy/kKHrgd2V5z6WzEWws4pxBm+oZOrHTkayndH0kB9BIydNROLNqKrzXSoqY+nxv21+U4kRgyZk1OCiPiySg/Cxh7vwNOPle01ME1QJ5EyG+CJNUfNL804Xxw7mRXQ45M6XrOiWhdXJnkoWhDyBaHACi0SxXZoSyi8t8jAcNeG16Dv2HPG+eDchzWV55iJ/87zl4/mjNF1BLAQIUABQAAAgIAAAAAAA69A4d5wAAAGYBAAAPAAAAAAAAAAAAAAAAAAAAAAB4bC93b3JrYm9vay54bWxQSwECFAAUAAAICAAAAAAA9kgm4TcBAADGAgAADQAAAAAAAAAAAAAAAAAUAQAAeGwvc3R5bGVzLnhtbFBLAQIUABQAAAgIAAAAAABh+IC4iAEAAGIDAAAYAAAAAAAAAAAAAAAAAHYCAAB4bC93b3Jrc2hlZXRzL3NoZWV0MS54bWxQSwECFAAUAAAICAAAAAAAjzovrLwAAACZAQAAGgAAAAAAAAAAAAAAAAA0BAAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHNQSwECFAAUAAAICAAAAAAAY2vxKKkAAAAZAQAACwAAAAAAAAAAAAAAAAAoBQAAX3JlbHMvLnJlbHNQSwECFAAUAAAICAAAAAAAFFVBTwUBAACZAgAAEwAAAAAAAAAAAAAAAAD6BQAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLBQYAAAAABgAGAIABAAAwBwAAAAA=

Answer 1

Well, I don't know the particulars of how you are generating the xml file, but I can tell you how to edit the underlying xml files so that it will work, and then perhaps you can figure out how to use your implementation to change the property that's gunking things up.

First, an xlsx is a set of xml files. I'm sure you know that, but I'm just starting at the beginning. You can change the extension to zip and then extract the files, and then rezip them and change the extension back to xlsx.

So do this:

take the generated xlsx
change the extension to .zip
extract the files
find xl\worksheets\sheet1.xml
open it and find this property: worksheet>sheetViews>sheetView:tabSelected

set it to 0
save the file
go back to the unzipped folder

select all files and send to zip
change the extension on the new zip file to .xlsx

You should now be able to open the newly created xlsx, add a new sheet, and copy freely.

If this works for you, then you have diagnosed the problem, one property set to true when it shouldn't be, and it should be relatively simple for you to modify your export procedure.