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, I 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:

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

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

When we try pasting this into SpreadJS with either the context menu or Crtl+V, we get… nothing? Ok, we will try using Ctrl+C to copy 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. It can be explained like a Venn diagram:

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:

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;

Again copying with Crtl+C:

And pasting the formulas to Excel:

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.