Anytime I see something that looks like Excel on the web, I cringe.
Unless you are Microsoft or Google and you are putting spreadsheets on a website (SoW), you are almost certainly doing something wrong.
---
Case 1: A co-worker (or yourself) previously wrote an excel-based app and now it's time to put it on the web for wider distribution. You implement the app with a custom gridview/spreadsheet.
Error in Case 1: The UI was copied, but what is almost always needed is an input wizard with validations and workflow along with a reporting backend.
---
Case 2: You have a database that you need to share in a human readable format.
Error in Case 2: Implement export functionality instead, you will never be able to implement all the features of Excel, and at some point someone will need that feature.
---
Case 3: You have a very small and well defined case that actually calls for SoW!
Error in Case 3: Your very small SoW app will grow and grow and strangle you.
---
Case 4: You want to challenge the stranglehold of Google Sheets and MS Office 365.
Error in Case 4: There is no error here, only madness.
Error in Case 4: There is no error here, only madness.
Then give me madness. The problem is that spreadsheets are one of the most natural interfaces for data that we humans have, and even with Microsoft and Google's efforts included, current spreadsheets on a website (SoW) suck.
So let's say the y-axis is importance of function and the x-axis is compelling implementation --> voila! Right there, plotted on the graph you can see that this is something that needs to be worked on.
I understand your reasoning: you've created a spreadsheet-styled labeled grid in no time and you're pumping your fist, only to find out later that it this is way less than 1% of the effort you will eventually have to dole out to make a basic, respectable spreadsheet. The landscape is littered with 1% efforts that look good until you start working with it.
My point, though, is that this area is too important to not try harder, to not see more true attempts.
> spreadsheets are one of the most natural interfaces for data that we humans have
Spreadsheets are natural for data, but not for humans. Spreadsheets are excellent for prototyping and data munging; and spreadsheet prototype apps are even fine for personal or small team use.
When you get medium team size or multiple small teams that spreadsheets break down.
Part of that breakdown is in distribution - which SoW solves.
But another part of that breakdown is in not understanding the implicit rules of the spreadsheet app - another team or person will put some garbage in a cell and break the 'app'.
Spreadsheets are the most intuitive programming environment known to man. Every time you reach for Excel to mung your data, that's opening a repl. The only problem is that Google and Microsoft don't care enough to make them powerful enough to do general purpose computation with, fast enough to build large systems with, versionable enough to enable large teams to use them.
> Spreadsheets are the most intuitive programming environment known to man. Every time you reach for Excel to mung your data, that's opening a repl.
I absolutely agree - REPLs are excellent for prototyping and exploring data and ideas, and spreadsheets are excellent in that role.
Allow me to ask you this then: when was the last time you shipped prototype code? Unless you live dangerously, you don't ship prototype code!
> The only problem is that Google and Microsoft don't care enough to make them powerful enough to do general purpose computation with, fast enough to build large systems with, versionable enough to enable large teams to use them.
I would counter that a more important problem is "productionalizing" spreadsheets - all of the fiddly things you do to ensure inputs are in the right format, etc, etc, along with testing, securing, and deploying code.
We solve the problems of implicit rules by adding UI and UX so that the rules become explicit.
Anything that solves the problem of implicit rules in spreadsheets is a UI or UX solution.
The question is, what is the best UI or UX solution?
My position is that the best UX is not a spreadsheet. To your point, it is possible that there is an acceptable UI or UX based on spreadsheets.
The next question would be, how much more difficult is it to create human driven spreadsheets than to create a classic UX solution (something like an input wizard)?
I agree in general, datagrids usually mean "we have absolutely no idea what our users workflows are", but I'm not sure the credit risk example is a good one. If the credit risk is one of dozens of fields on a generic client edit table then it's awful. If it's a much more specific "adjust credit risk" form with relevant information to that specific workflow then it's fine.
I am working with two separate clients that need excel spreadsheet functionality in a browser. I evaluated all of the existing open source projects (this wasnt available at the time) and ended up going with fix-data-table-2 (fork of facebooks fix-data-table).
fix-data-table is performant and easily extendable. I was able to create an editable grid similar to googlesheets in a months worth of work. working on open sourcing it soon.
We (Metabase) switched from FixedDataTable to React Virtualized and are happier with it. The only major issue with it is doesn't have fixed columns/rows built in, but it's pretty easy to compose a couple Grids with ScrollSync to get that behavior (https://bvaughn.github.io/react-virtualized/#/components/Scr...)
FixedDataTable has performance issues with a larger number of columns because it doesn't "virtualize" columns (https://github.com/facebook/fixed-data-table/issues/49). The DOM it generates is also much heavier (several elements for every cell vs. 1 with React Virtualized)
One of the reasons we decided to maintain this library is that it does virtualize both columns and rows. We use it at Schrodinger for our enterprise platform and have demo'd support of billions of rows. This is possible because we don't use a wrapper DOM element to handle scrolling.
Right now we are migrating all the state to Redux for better maintainability and performance and hope to make extending and optimizing the library more user friendly.
misiti3780 would you be interested in collaborating with us to help us capture your improvements as well?
I've gotta admit I'm weary of the usefulness of a table with billions of rows. Doesn't that just drown the user with noise? Have you explored alternative means of presenting the data? Billions of anything sounds like too much information for any human to make sense of and process. Could you explain a bit about your use-case?
Our application is similar to excel/google spreadsheets. We don't expect the user to scroll through all that data, but we give tools to do sorting, filtering, plotting, etc. over that data.
FYI: there was a new "MultiGrid"[1] high level component that was added in 8.9.0 [2] which helps facilitate fixed columns/rows with less boilerplate.
Though, nearly every time I've used the MultiGrid component, I've found myself eventually dropping back to ScrollSync + Grids/Lists. As the product grows in complexity, adding more complex interactions to MultiGrid is difficult as it doesn't expose all of the Grid API.
I did the same evaluation and ended up going with react-bootstrap-table [1], which comes built-in with some nice features like cell editing [2], keyboard navigation [3], but does _not_ have fixed-data-table-2's column reordering and resizing
As a general pointer, there's a good categorized list of React components at https://github.com/brillout/awesome-react-components , including a number of other table and grid components. Also, https://js.coach is a useful searchable auto-generated catalog of React-related components and libraries.
For anyone interested in learning React, I keep a big list of links to high-quality tutorials and articles on React, Redux, and related topics, at https://github.com/markerikson/react-redux-links . Specifically intended to be a great starting point for anyone trying to learn the ecosystem, as well as a solid source of good info on more advanced topics.
If you can productize this, then you can potentially build a business around this.
The most feature filled spreadsheet component today is Handsontable. My company pays for the enterprise version of it...And we wish there was something better.
It's not free, but IMO it's the most Excel-like Web-based spreadsheet money can buy. You get support for Excel syntax and formulas, hundreds of Excel functions, XLSX import/export, print-to-PDF, virtual scrolling, OS clipboard integration, etc. — and it all runs in the browser.
I'd love it if kendo-ui tool a page from Highcharts and provided a free non-commercial license [0]. I first tried out Highcharts in college, after that I used it for a couple personal projects. Eventually I found myself needing a powerful charting library for my job, and by then I was so familiarized with its capabilities that buying a license was a no-brainer.
If I tried and found myself liking kendo-ui, I'd probably end up even more frustrated from not being able to use it for personal projects.
EDIT: Just wanna add that you guys have done great work. Poking around the component demos is really slick.
So Handsontable had an open source version that I could try before I bought. I had looked at kendo , but saw that it was packaged as part of a larger suite...Which probably I would overpay for and be useless for me.
Second - we use react and kendo had started exploring react only in oct2016 (we have our react HOT app in production since Nov 2015). I'm Not sure what's the status since googling for a react kendo spreadsheet component does not get me much.
But I would switch If you had a clean pricing for the spreadsheet, clearly showed by comparison where it was superior..And most importantly, react.
If you're building a commercial app that needs this kind of functionality then $490/seat is not expensive, it's the cost of a fairly cheap developer day. So instead of messing around for days trying to get various partially implemented open source/free "Excel" type grids to work, you pay your money and get the job done more quickly.
This looks really great, and I've got an immediate use for something like this. I've been using ag-grid for presentation of grid-based data, but this looks ideal for more-interactive views. I look forward to putting it through its paces.
Somewhat off-topic, but for those working in React, have you built large systems using Redux? I'm fairly early in what is likely to ultimately be a large-ish application, and while I've got Redux working, I feel working with it is definitely adding cognitive load to each feature I implement. Just wondering whether it's worth the effort and would appreciate feedback from those who may have more experience weighing the pros and cons of using Redux with their application.
It's important to remember that not all of your state needs to be in Redux. Redux is supposed to supplement local state, not replace it.
Create namespaces in your store, it helps a ton with maintainability in the long run. Name your mutations consistently: describe what you're doing to what kind of data as briefly as you can.
Keeping these few guidelines in mind, building huge applications using any flux-like pattern is quite straight-forward and easy to reason about.
I've built a relatively large React app using Redux for state management. Etheryte's point is valid -- if state is only used within a single component (or maybe shared with its immediate children), you're probably better off leaving it local to that component. And if it turns out you're wrong later and actually do need it somewhere else you can refactor and pull it into Redux at that time.
If you have more than a few bits of state to manage, use multiple reducers and split your state into logical groups. Within each reducer, I use immutable.js `Record`s as the main state container. I also use Flow to strongly-type these reducers and get autocomplete and type checking of my state, but the benefits of that are mostly orthogonal to the benefits of using Redux.
You should almost never try to "denormalize" data (store the same thing in multiple places) or eg. use Redux state to initialize a component's local state. Trying to keep these in sync will kill your mental model. Instead, use selectors to keep components in sync, and if a particular view is costly to compute as a function of your state you can memoize it (I use https://github.com/reactjs/reselect for that). But that's extra complexity that should only be added in the few cases that warrant it.
I found that by organizing my code this way I was able to keep the parts of the app that I needed to make any particular change in my head easily, which did incredible things for developer velocity and happiness. :)
I have made several relatively large systems using Redux, and it has become a necessity in order to handle complexity of new features. Overcoming the initial cognitive load takes a week or two, but as your system grows, your productivity and code organization is greatly improved. I also recommend Immutable for handling the state object and reselect for computing your container state properties. Those technologies together are a pleasure to develop with.
How does https://github.com/kolodny/immutability-helper compare to Immutable in your experience? The latter seems potentially more performant and lighter-weight; curious what its disadvantages might be (the syntax?)
I personally recommend against using Immutable.js, for a number of reasons (which I wrote as a Reddit comment a while ago [0] ). There's some additional info on Immutable.js-related perf in my links list [1]
FYI, there's also a bunch of other immutable update utility libs besides immutability-helper. I have a list of them in my Redux addons catalog [2].
Interesting, thanks for sharing. I took a look at your list of links recently, actually – it was helpful.
I'm considering syntax-level support for `immutability-helper`, which seems like the best all-around solution aside from its syntax. This is for a JS Dialect I'm building called LightScript[0].
One thing i like about immutability-helper is their mongo-style syntax. Yeah, MongoDB might kinda suck for multiple reasons, but the query syntax is quite nice IMO.
Redux does deliberately add some indirection and overhead to things, but that tends to stay constant as the app increases in complexity. In other words, if you're just writing a TodoMVC app, then it's probably going to seem like a pain. As the app scales, the consistency and behavior usually winds up as a net benefit.
The Redux FAQ has an entry on "scaling" [0], and my React/Redux links list has a large section on Redux architecture and best practices [1].
I just refactored our fairly large (15k LoC) redux codebase into MobX for exactly this reason. Result, shaved off 10k LoC and started enjoying dev life again
It's great to see that Nadim started to work on this spreadsheet for React. It's for sure an ambitious project and will require a lot of community support and love;) We at Handsontable benefit from the open source since 2012 and most of ~3400 closed issues to date were reported by users who actually care about our product and/or make a good use of it.
Here are some thoughts which anyone who attempt to create an online spreadsheet from scratch should take into account:
- Separate data logic from the view. Sounds like a cliché but actually it will save you plenty of time when you start adding more and more features to the pile.
- Get to know how people want to use your app. Focus on their workflow not on your (false) assumptions.
- Don't mix up features typical for grids with those present in spreadsheets. Focus on what's important, not on Excel's blows and whistles. Grouping is OK. Parent-child structure not necessarily.
- Don't underestimate the effort connected with building such component. Ask the community for help. Give them a great product in return.
- At some point your "data engine" will work just like a small, specialised database (CRUD + search/filter/sort).
- Think of how you will scale your solution both horizontally (new technologies) and vertically (new features). Our customers have completely remote requirements in terms of using Handsontable with specific databases, back-ends, JS/CSS frameworks, devices (not to mention different formats of numbers, languages etc.)
Offtopic: In the contemporary history we have seen a lot of attempts to make an ultimate online spreadsheet. Most of those projects are now long gone (see https://www.lifewire.com/best-free-online-spreadsheets-34862...). That was before Google acquired 2Web Technologies and created its own, free solution upon it. The trend is to create spreadsheet-based projects for certain use cases just like Quip, Smartsheet, Airtable and more.
Anyway, fingers crossed for your project. I will be happy to share with you our best practices and thoughts from our work with Handsontable (write @ chris [at] handsoncode.net).
If you think it's a constructive comment I'll be writing a bot that scans pages posts on HN, looks on the page for a GitHub link and complains about the license used. Doesn't matter if it's GPL, MIT, Apache, I'll just write some generic comment complaining about each license.
Hi. Certainly, my comment would be applicable to any open source library. But my intent was not to say that OSS software is difficult to get by patent attorneys. Rather I was attempting an inside joke with kindred spirits who have submitted OSS libs to their company's attorneys for patent infringement.
What they seem to do is hire a grunt to do a naive word search match on the patent database. So I was saying, "just imagine the number of results that will come back for 'spreadsheet' or 'Excel'".
There is no complaint to be made about Apache or MIT outside of ideology, while there are some developers who simply aren't aware that they're excluding a large part of their potential user base when selecting a copyleft license
I have spent way too much time building grid components at my company, and I've found that if you have (1) a lot of data, (2) a dense grid, and (3) a grid that occupies most of the screen, THEN the only way to get 30+fps is to use canvas and do all the layout and drawing logic yourself.
I had to verify this... I cant believe it, they doing all the rendering logic for all the cells in canvas. They are not rendering simple cells either, sheets has may features :O.
Testing would be the easy part, in some cases it would be easier than DOM because you can do image diffs... The harder part is BUILDING IT.
They applied some kind of mixed model in which the entire effort connected with rendering the body of the grid (cells) is being drawn in canvas and the rest of it, including headers, selection and editors, are rendered in a regular way. There is no doubt that Google Sheets keeps almost all of its logic on the back-end side.
Have you tried out react-virtualized [0]? The author has invested a ton of time tweaking things left and right in order to get optimal performance. It's by far the best virtualized grid in JS that I've had the pleasure of using. In addition to that, the React Fiber rewrite should eventually introduce a few new APIs which, as I understand it, should enable further performance improvements. If you scroll really fast it'll flicker white and rendering will lag behind a bit, but it won't cause any jank, so the interface remains at 60FPS. It exposes a good number of params which you can tweak to suit most needs. Personally, I think it's not a big deal, as long as the UI runs smoothly. It takes a few extra ms for the view to catch up if I scroll really fast... But I've had similar the same experience with Apple's Numbers and with browsers trying to render huge HTML tables and it hasn't bothered me.
I've gone back on forth on this topic quite a bit. I used to be a strong believer you should virtualize everything. But then you've broken in-browser search, and you find yourself having to write and support even more code. Using canvas? Say goodbye to accessibility. Now, I'm not saying this is the case for your use-case, but I think that in some cases it's worth seriously reconsidering the design and just going with a simple table that leverages built-in browser functionality and traditional pagination. If you have so much data that you struggle to load and display it with JS, it may be a good idea to take a step back and explore alternative strategies for presenting the data to your users. As an extreme example, a 1 million by 1 million table would probably contain too much data for any consumer to really make sense of it. I only say this because I had a couple experiences where having heard that might've helped keep things simpler. I'll still happily reach for react-virtualized when I find a situation that merits its use.
Very slick looking! Currently I'm using Angular 1.x with editablegrid to enable browser edits on server hosted spreadsheets [1]. The UX quality of this makes me think I should go for a rewrite in React.
Any data on how this performs on large datasets?
I've previously found that most web based data grids get extremely slow on larger data sets in BI applications (100k+ rows)
If that is the case, then this would have avoided the real problem that needs solving here. Throwing together some rendering and keyboard handling is not game changing. Getting good performance is the hard problem that needs solving.
Perhaps a future version will focus on making sure it scales?
I might look into it in future revisions as large datasets wasn't part of my initial requirement (having a simple grid component that can handle formulas and copy/pasting).
The best option I had when building this was Handsontable. But that doesn't play nicely with react's virtual dom and immutable data.
One thing I'm wondering about... if this is React based, do you reassign and recalculate the whole grid upon every change? Because the grid is probably a component, and has the table as props. I could imagine that is inefficient... Not to even speak of then rendering the whole component and sending it through DOM reconciliation every time a key is pressed.
Well, OTOH I guess it doesn't matter for small tables. The demo feels really snappy!
Back in the day I did a perl spread sheet. I was only updating the cells that were displayed at the moment. There were also transitive recalcs too if a displayed cell depended on other cells.
Lately I have been looking into javascript grid components and have not found a good solution.
What would be a good javascript grid component that satisfies the following:
1. Integrates easily with Vue
2. Has sorting
3. Has grouping
4. Has inline editing (formulas are not required)?
OTOH I would be happy with a software that is basically a web frontend for Postgres that can be customised in a way that a client can only see and edit specific fields based on assigned rights.
> OTOH I would be happy with a software that is basically a web frontend for Postgres that can be customised in a way that a client can only see and edit specific fields based on assigned rights.
Take a look at Adminer [1] and their Adminer Editor [2] (the later is what you're asking for).
Niall here from ag-Grid - thanks for the nice words about ag-Grid. Yes we support Angular 1, Angular 2+, React, Aurelia, Vue, Web Components, and just plain JavaScript.
Just some basic functionality that is used when making nearly all spreadsheets: dynamically resizing tables, formating cells, setting cell types (currency, floating point, integer, boolean), conditional formatting, cell ranges, SUM, COUNT, IF, AVG, extending cell contents by dragging.
This project is a proof-of-concept. Nothing more than that.
Unless you are Microsoft or Google and you are putting spreadsheets on a website (SoW), you are almost certainly doing something wrong.
---
Case 1: A co-worker (or yourself) previously wrote an excel-based app and now it's time to put it on the web for wider distribution. You implement the app with a custom gridview/spreadsheet.
Error in Case 1: The UI was copied, but what is almost always needed is an input wizard with validations and workflow along with a reporting backend.
---
Case 2: You have a database that you need to share in a human readable format.
Error in Case 2: Implement export functionality instead, you will never be able to implement all the features of Excel, and at some point someone will need that feature.
---
Case 3: You have a very small and well defined case that actually calls for SoW!
Error in Case 3: Your very small SoW app will grow and grow and strangle you.
---
Case 4: You want to challenge the stranglehold of Google Sheets and MS Office 365.
Error in Case 4: There is no error here, only madness.