1# Spreadsheet
  2TypeScript/javascript spreadsheet parser, with formulas. For a full list of formulas, see [DOCS.md](DOCS.md).
  3
  4## Usage
  5
  6### Install
  7```
  8npm install js-spreadsheet
  9```
 10
 11### Examples
 12
 13**Using a Sheet**
 14```javascript
 15var Sheet = require("js-spreadsheet").Sheet;
 16var sheet = new Sheet();
 17sheet.setCell("A1", "10");
 18sheet.setCell("A2", "14");
 19sheet.setCell("A4", "10e2");
 20sheet.setCell("A5", "99.1");
 21sheet.setCell("B1", "=SUM(A1:A5)");
 22sheet.getCell("B1").getValue(); // returns: 1123.1
 23```
 24
 25**Using Formulas Directly**
 26```javascript
 27var Formulas = require("js-spreadsheet").AllFormulas;
 28Formulas.SUM(1, 2, 3, [4, 5, 6], "7"); // returns: 28
 29```
 30
 31For a full list of formulas, see [DOCS.md](DOCS.md)
 32
 33
 34**Nested Formulas**
 35```javascript
 36sheet.setCell('A1', '=SIN(PI() / 2)')
 37sheet.getCell("A1").getValue(); // returns: 1
 38```
 39
 40**Date Conversion**
 41```javascript
 42sheet.setCell('A1', '=DATEDIF("1992-6-19", "1996-6-19", "Y")')
 43sheet.getCell("A1").getValue(); // returns: 4
 44```
 45
 46**Number Parsing**
 47```javascript
 48sheet.setCell('A1', '="10e1" + 44');
 49sheet.getCell("A1").getValue(); // returns: 144
 50
 51sheet.setCell('A2', '="1,000,000" + 1');
 52sheet.getCell("A2").getValue(); // returns: 1000001
 53
 54sheet.setCell('A3', '="-$10.00" + 0');
 55sheet.getCell("A3").getValue(); // returns: -10
 56
 57sheet.setCell('A4', '=10% + 1');
 58sheet.getCell("A4").getValue(); // returns: 1.1
 59
 60sheet.setCell('A5', '= 2 ^ 10');
 61sheet.getCell("A5").getValue(); // returns: 1024
 62```
 63
 64
 65## Ranges
 66
 67In MS Excel, and Google Spreadsheets, literal ranges are denoted with opening and closing curly-brackets. E.g. "{1, 2, 3}". In this implementation however, literal ranges are denoted with opening and closing brackets. E.g. "[1, 2, 3]".
 68
 69```javascript
 70// OK
 71sheet.setCell('A1', '=SUM([1, 2, 3])');
 72// NOT OK
 73sheet.setCell('A1', '=SUM({1, 2, 3})');
 74```
 75
 76
 77## Docs
 78See [DOCS.md](DOCS.md) for full list and documentation of all formulas available.
 79
 80
 81## Contributing
 82When adding a formula, or fixing a bug please follow the commit message format:
 83```
 84[BUG_FEATURE_FILE_OR_COMPONENT] short description here of issue and fix
 85```
 86If you're adding a new formula, before you submit a pull request or push ensure that:
 871) The formula is tested inside the proper category file in `tests/Formulas`.
 882) Make sure the formula is exported, and imported/exported in `AllFormulas.ts`.
 893) The formula tests for reference errors, N/A errors, value errors for each input.
 904) That the formula is tested for parsing inside `SheetFormulaTest.ts`.
 915) Run tests with `npm run test`.
 926) Build with `npm run build`.
 937) Build DOCS.md with `npm run docs`.
 94
 95
 96## Why?
 97Near the end of 2016 I began to ask myself why I didn't know more about MS Excel and Google Spreadsheets. Why didn't I know more about the most popular programing language in the world? I began to reverse engineer Google Spreadsheets in particular, gaining a better understanding along the way.
 98
 99I chose TypeScript because, coming from Java, it is really nice to be able to see type errors, and catch them. I also just enjoy getting specific with my return types, even if the specifications for a spreadsheet treat type flexibly.
100
101For the formula documentation, I tried to be at least -- if not more -- thorough as Google Spreadsheets.
102
103
104## License
105
106For this repository's code license, and related licenses, see LICENSES directory.
107
108
109## Acknowledgements
110This is largely a re-write of [Handsontable](https://github.com/handsontable)'s [https://github.com/handsontable/ruleJS](https://github.com/handsontable/ruleJS), and [https://github.com/sutoiku/formula.js/](https://github.com/sutoiku/formula.js/). The parser was derived from Handsontable's, and many of the formulas were created with FormulaJS's formulas as a reference point.