{"id":2180,"date":"2015-07-21T16:34:28","date_gmt":"2015-07-21T23:34:28","guid":{"rendered":"https:\/\/www.microsoft.com\/reallifecode\/index.php\/2015\/07\/21\/building-a-reactjs-spreadsheet-component\/"},"modified":"2020-03-19T09:42:51","modified_gmt":"2020-03-19T16:42:51","slug":"building-a-reactjs-spreadsheet-component","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/ise\/building-a-reactjs-spreadsheet-component\/","title":{"rendered":"Building a ReactJS Spreadsheet Component"},"content":{"rendered":"

During the Microsoft Ventures hackathon in May 2015 it became obvious that one of the startups (CreativeWorx) required a standalone Excel-like spreadsheet component for the web<\/a>. This post describes the resulting React component, how it was built, and how it can be used today.<\/p>\n

CreativeWorx is the company behind the CreativeWorx time tracker, which enables users to document time spent on a project in a calendar view. While this feature is the unique value proposition for the app, many users have requested an Excel-like input method to augment the information captured by the CreativeWorx calendar. Building such an input is surprisingly difficult: Users have grown fond of Excel\u2019s keyboard navigation, allowing them to quickly switch cells and edit their inputs without mouse clicks. They wanted the same experience in CreativeWorx time tracker.<\/p>\n

\"Screenshot:<\/p>\n

Overview of Solution<\/h2>\n

The CreativeWorx team was working on a significant rearchitecture of their application, and wanted to not add any additional dependencies. The Spreadsheet component was required to be entirely independent from the rest of the application.<\/p>\n

To solve that need, we used Facebook\u2019s ReactJS<\/a>, which is optimized for standalone components. The developed Spreadsheet Component<\/a> is a self-contained spreadsheet component that can be run in a heavily styled mode, but also allows other developers to use it in a more Excel-like spread sheeting mode.<\/p>\n

\"Screenshot\"<\/p>\n

Implementation<\/h2>\n

The component is made up by three React Component Classes \u2013 the overarching spreadsheet component implements \u201crow components\u201d that in return implement \u201ccell components\u201d. This enables the spreadsheet to implement as many rows and columns as needed, including the dynamic addition of rows and columns.<\/p>\n

Usage<\/h3>\n

The component is initialized with a configuration object. If desired, initial data for the spreadsheet can be passed in as an array of rows. In addition, one can pass in a second array filled with class names for each cell, enabling the developer to style each cell differently.<\/p>\n

\n
var SpreadsheetComponent = require('.\/src\/spreadsheet');\r\n\r\nReact.render(<TableComponent initialData={initialData} config={config} spreadsheetId=\"1\" cellClasses={classes} \/>, document.getElementsByTagName('body'));\r\n<\/code><\/pre>\n<\/div>\n
Configuration<\/h4>\n
To ensure that the component could also be used by other developers and by CreativeWorx in different contexts, the configuration allows for a number of settings:<\/p>\n
\n
var config = {\r\n    \/\/ Initial number of rows\r\n    rows: 5,\r\n    \/\/ Initial number of columns\r\n    columns: 8,\r\n    \/\/ True if the first column in each row is a header (th)\r\n    headColumn: true,\r\n    \/\/ True if the data for the first column is just a string.\r\n    \/\/ Set to false if you want to pass custom DOM elements.\r\n    headColumnIsString: true,\r\n    \/\/ True if the first row is a header (th)\r\n    headRow: true,\r\n    \/\/ True if the data for the cells in the first row contains strings.\r\n    \/\/ Set to false if you want to pass custom DOM elements.\r\n    headRowIsString: true,\r\n    \/\/ True if the user can add rows (by navigating down from the last row)\r\n    canAddRow: true,\r\n    \/\/ True if the user can add columns (by navigating right from the last column)\r\n    canAddColumn: true,\r\n    \/\/ Override the display value for an empty cell\r\n    emptyValueSymbol: '-',\r\n    \/\/ Fills the first column with index numbers (1...n) and the first row with index letters (A...ZZZ)\r\n    letterNumberHeads: true\r\n};\r\n<\/code><\/pre>\n<\/div>\n
Initial Data Object<\/h4>\n
The initial data object contains an array of rows, which itself contains an array for every single row. Each index in the array represents a cell. It is used by the component to pre-populate the cells with data.<\/p>\n
\n
var initialData = {\r\n    rows: [\r\n        ['Key', 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG'],\r\n        ['COM', '0,0', '0,1', '0,2', '0,3', '0,4', '0,5', '0,6'],\r\n        ['DIV', '1,0', '1,1', '1,2', '1,3', '1,4', '1,5', '1,6'],\r\n        ['DEV', '2,0', '2,1', '2,2', '2,3', '2,4', '2,5', '2,6'],\r\n        ['ACC', '3,0', '3,1', '3,2', '3,3', '3,4', '3,5', '3,6']\r\n    ]\r\n};\r\n<\/code><\/pre>\n<\/div>\n
Cell Classes Object<\/h4>\n
You can assign custom classes to individual cells. Follow the same schema as for the initial data object.<\/p>\n
\n
var classes = {\r\n    rows: [\r\n        ['', 'specialHead', '', '', '', '', '', ''],\r\n        ['', '', '', '', '', '', '', ''],\r\n        ['', 'error', '', '', '', '', '', ''],\r\n        ['', 'error changed', '', '', '', '', '', ''],\r\n        ['', '', '', '', '', '', '', '']\r\n    ]\r\n};\r\n<\/code><\/pre>\n<\/div>\n
Data Lifecycle<\/h4>\n
The initialData object is only used in initialization to populate the state, so user input is not written to it. To capture user input, one can subscribe callbacks to the cellValueChanged and dataChanged events on the dispatcher. This enables the spreadsheet component to be used in more complex scenarios, like external data validation.<\/p>\n
Get the full data state after each change<\/strong><\/h6>\n
\n
var Dispatcher = require('.\/src\/dispatcher');\r\n\r\nDispatcher.subscribe('dataChanged', function (data) {\r\n    \/\/ data: The entire new data state\r\n}, \"spreadsheet-1\")\r\n<\/code><\/pre>\n<\/div>\n
Get the data change before state change<\/strong><\/h6>\n
\n
var Dispatcher = require('.\/src\/dispatcher');\r\n\r\nDispatcher.subscribe('cellValueChanged', function (cell, newValue, oldValue) {\r\n    \/\/ cell: An array indicating the cell position by row\/column, ie: [1,1]\r\n    \/\/ newValue: The new value for that cell\r\n}, \"spreadsheet-1\")\r\n<\/code><\/pre>\n<\/div>\n
Other events are available for even more complex scenarios<\/a>. The selection\/deselection of cells, click on head cells, creation of new rows or columns, or even the beginning or ending of a cell edit process can be captured by external listeners.<\/p>\n
Challenges<\/h2>\n
Cross-browser support was challenging. A lot of progress has been made recently on user input, but the Spreadsheet component could not make full use of the new DOM and JavaScript APIs and still be usable by older browsers.<\/p>\n
Another challenge was interaction with the clipboard. CreativeWorx deemed it unnecessary, but if the popularity of the component continues to rise, we may consider revisiting it. Being able to copy and paste values without the current hiccups is likely to be a popular request. It\u2019s important to point out that browsers heavily restrict the interaction with the clipboard for security reasons, which is why this feature is unavailable in Excel Online.<\/p>\n
Opportunities for Reuse<\/h2>\n
The component is currently listed on Facebook\u2019s own \u2018Complementary Tools\u2019 page<\/a> and became Felix Rieseberg\u2019s most popular GitHub repository within two days<\/a>, so it\u2019s clear that many developers are already considering reusing this component for their own apps.<\/p>\n
Since the component is entirely standalone, it can be reused in any number of websites and web apps that require user table input.<\/p>\n
How to run the Example<\/h2>\n
    \n
  • Clone the repository from GitHub and open the created folder:<\/li>\n<\/ul>\n
    \n
    git clone https:\/\/github.com\/felixrieseberg\/React-Spreadsheet-Component.git\r\ncd React-Spreadsheet-Component\r\n<\/code><\/pre>\n<\/div>\n
      \n
    • Install npm packages and compile JSX<\/li>\n<\/ul>\n
      \n
      npm install\r\ngrunt\r\n<\/code><\/pre>\n<\/div>\n
      If you are using Windows, run the following commands instead:<\/p>\n
      \n
      npm install --msvs_version=2013\r\ngrunt\r\n<\/code><\/pre>\n<\/div>\n
        \n
      • Open the example in example\/index.html<\/code>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
        During the Microsoft Ventures hackathon in May 2015 it became obvious that one of the startups required a standalone Excel-like spreadsheet component for the web. This post describes the resulting React component, how it was built, and how it can be used today.<\/p>\n","protected":false},"author":21345,"featured_media":12689,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[11],"tags":[137,307],"class_list":["post-2180","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-data","tag-creativeworx","tag-reactjs"],"acf":[],"blog_post_summary":"
        During the Microsoft Ventures hackathon in May 2015 it became obvious that one of the startups required a standalone Excel-like spreadsheet component for the web. This post describes the resulting React component, how it was built, and how it can be used today.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/2180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/users\/21345"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/comments?post=2180"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/2180\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media\/12689"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media?parent=2180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/categories?post=2180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/tags?post=2180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}