Copying and Pasting Formulas Between SpreadJS and Excel

In this article, we will go over how copying and pasting generally works when doing so between SpreadJS and Excel. We will use the SpreadJS Designer demo as our SpreadJS application for this topic.

To start, assume we have a basic formula on a new sheet in Excel. Version of Excel should not matter, but for reference I am using the Office 365 version of Excel:

We will have a similar basic formula on a new sheet in SpreadJS as well:

First, let’s try copying the cell with the formula in Excel to our SpreadJS sheet using the Excel context menu (right-click):

When we try pasting this into SpreadJS with either the SpreadJS context menu or Ctrl-V, we get nothing. We will try using Ctrl+C and Ctrl+V to copy/paste instead and see what happens:

You will see that we only copied over the cell value, when we wanted the cell formula. This is because Excel and SpreadJS work differently when copying and pasting. SpreadJS is hosted via a web environment; this poses a limitation. While SpreadJS has its own context menu and internal clipboard, it (and similar webapps) can otherwise only access the system clipboard.

In the most simple terms, they each have a clipboard that is exclusive to one another, but they can both access the system clipboard. While you can copy and paste cells with formulas fine within each respectively, the system clipboard is all they can share.

When copying a cell in Excel, the system clipboard gets the value as it appears in the cell; not the formula. However, there is an easy trick to get around this. Under the “Formulas” tab in Excel, you should see “Show Formulas” under “Formula Auditing”:

This will switch cell values to be their formula counterparts, if applicable. Now try copying the value of the cell to the system clipboard with Ctrl+C and then pasting into SpreadJS with Ctrl+V:

You will see that the cell value pasted is the formula from Excel. This also works in batches, and to demonstrate we will try the other way around, from SpreadJS to Excel. SpreadJS has a similar function to show formulas. You can either do so via the Formulas tab in the Designer ribbon:

Or you can toggle this programmatically using showFormulas similarly like this:

// set showFormulas to true to enable users copy the formulas into other application

activeSheet.options.showFormulas = true;

This way, you can copy/paste the other way around, from SpreadJS to Excel, without losing formulas. 

If you have a worksheet/workbook with mass quantities of formulas (we’re talking hundreds upon thousands of different formulas), we would recommend importing directly into SpreadJS as well as reading our Best Practices documentation to help optimize your sheet and data in SpreadJS. But sometimes, you just want a quick, straightforward copy and paste, and this is the easiest way to get that while circumventing browser limitations.