visicalc_engine 1.1.0

A clone of the visicalc spreadsheet engine in dart library for education use and UI foundation

VisiCalc Engine Package

This package implements a clone of the VisiCalc spreadsheet engine in a dart library that works by parsing the strings of cells (Map<A1,String>) and calculating the result considering references to other cells.

The screenshot below shows the original user interface of Visicalc made by Dan Bricklin and Bob Frankston.

From wikipedia

VisiCalc ("visible calculator") is the first spreadsheet computer program for personal computers, originally released for Apple II by VisiCorp on October 17, 1979.It is considered the killer application for the Apple II, turning the microcomputer from a hobby for computer enthusiasts into a serious business tool, and then prompting IBM to introduce the IBM PC two years later.More than 700,000 copies were sold in six years, and up to 1 million copies over its history

Features

• Supports the calculation and reference language from VisiCalc explained in the reference card

Getting started

Simple usage examples below:

import 'package:a1/a1.dart';
import 'package:visicalc_engine/visicalc_engine.dart';

void main(List<String> arguments) {
   final sheet = {
    'A1'.a1: '/FR-12.2',
    'A2'.a1: '(a5+45)',
    'A3'.a1: '/F*13',
    'A4'.a1: '+A2+A5-A6',
    'A5'.a1: '-A3/2+2',
    'A6'.a1: '/F\$.23*2',
    'B1'.a1: '+A1+A3*3',
    'B2'.a1: '(A1+A3)*3',
    'B3'.a1: '12.23e-12',
    'B4'.a1: '.23e12',
    'B5'.a1: '/FRb4',
    'B6'.a1: '+b2',
    'B7'.a1: '@sum(a1...b6)',
    'D13'.a1: '+b2',
  };
  final worksheet = Engine.fromMap(sheet, parseErrorThrows: true);
  print(worksheet);

  // Change cell
  var b5 = worksheet["B5".a1];
  print('B5 formula was ${b5?.formulaType?.asFormula} = $b5');
  print('Now setting B5 to formula a1');
  worksheet['B5'.a1] = '+a1';

  b5 = worksheet["B5".a1];
  print('Now B5 formula is ${b5?.formulaType?.asFormula} = $b5');
  print(worksheet);
  print('Output to a .vc file\n: ${worksheet.toFileContents()}');

  // .vc file example
  final fileContents = '''\
>A1:/FL"TRUE\r
>A2:+B5*10\r
>A3:+B3+1\r
>A4:"tes\r
>A5:@SUM(B2...B5)\r
>B1:/FR"label\r
>B2:123\r
>B3:@PI\r
>B4:+B3\r
>B5:.23*2\r
\u0000''';
  print('Parsing an example of a VisiCalc .vc file format');
  final engine = Engine.fromFileContents(fileContents);
  print(engine);
}

copied to clipboard

The result looks as follows:

         A fx           |          A           |       B fx           |          B           |       C fx           |          C           |       D fx           |          D           | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  -12.2                |                -12.2 | +A1+A3*3             | 26.8                 |                      |                      |                      |                      | 
 2  (A5+45)              | 40.5                 | (A1+A3)*3            | 2.4                  |                      |                      |                      |                      | 
 3  13                   | ********             | 1.223e-11            | 1.223e-11            |                      |                      |                      |                      | 
 4  +A2+A5-A6            | 35.54                | 230000000000         | 230000000000         |                      |                      |                      |                      | 
 5  -A3/2+2              | -4.5                 | B4                   |         230000000000 |                      |                      |                      |                      | 
 6  0.23*2               | 0.46                 | +B2                  | 2.4                  |                      |                      |                      |                      | 
 7                       |                      | @SUM(A1...B6)        | 460000000104.4000244 |                      |                      |                      |                      | 
 8                       |                      |                      |                      |                      |                      |                      |                      | 
 9                       |                      |                      |                      |                      |                      |                      |                      | 
10                       |                      |                      |                      |                      |                      |                      |                      | 
11                       |                      |                      |                      |                      |                      |                      |                      | 
12                       |                      |                      |                      |                      |                      |                      |                      | 
13                       |                      |                      |                      |                      |                      | +B2                  | 2.4                  | 

B5 formula was B4 = 230000000000
Now setting B5 to formula a1
Now B5 formula is +A1 = -12.2
          A fx           |          A           |       B fx           |          B           |       C fx           |          C           |       D fx           |          D           | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  -12.2                |                -12.2 | +A1+A3*3             | 26.8                 |                      |                      |                      |                      | 
 2  (A5+45)              | 40.5                 | (A1+A3)*3            | 2.4                  |                      |                      |                      |                      | 
 3  13                   | ********             | 1.223e-11            | 1.223e-11            |                      |                      |                      |                      | 
 4  +A2+A5-A6            | 35.54                | 230000000000         | 230000000000         |                      |                      |                      |                      | 
 5  -A3/2+2              | -4.5                 | +A1                  | -12.2                |                      |                      |                      |                      | 
 6  0.23*2               | 0.46                 | +B2                  | 2.4                  |                      |                      |                      |                      | 
 7                       |                      | @SUM(A1...B6)        | 230000000092.1999816 |                      |                      |                      |                      | 
 8                       |                      |                      |                      |                      |                      |                      |                      | 
 9                       |                      |                      |                      |                      |                      |                      |                      | 
10                       |                      |                      |                      |                      |                      |                      |                      | 
11                       |                      |                      |                      |                      |                      |                      |                      | 
12                       |                      |                      |                      |                      |                      |                      |                      | 
13                       |                      |                      |                      |                      |                      | +B2                  | 2.4                  | 

Output to a .vc file
: >D13:+B2
>B7:@SUM(A1...B6)
>B6:+B2
>A6:/F$0.23*2
>B5:+A1
>A5:-A3/2+2
>B4:230000000000
>A4:+A2+A5-A6
>B3:1.223e-11
>A3:/F*13
>B2:(A1+A3)*3
>A2:(A5+45)
>B1:+A1+A3*3
>A1:/FR-12.2

Parsing an example of a VisiCalc .vc file format
          A fx           |          A           |       B fx           |          B           | 
-----------------------------------------------------------------------------------------------
 1  "TRUE                | TRUE                 | "label               |                label | 
 2  +B5*10               | 4.6                  | 123                  | 123                  | 
 3  +B3+1                | 4.1415926536         | @PI                  | 3.1415926536         | 
 4  "tes                 | tes                  | +B3                  | 3.1415926536         | 
 5  @SUM(B2...B5)        | 129.7431853072       | 0.23*2               | 0.46                 | 

copied to clipboard

The test/ directory explores other use cases for the visicalc_engine types and library.

Usage

The code above in getting started is also available in the example/example.dart

Reference

• The Visicalc user interface and logo are referenced from wikipeida.
• The parsing depends on the great library petitparser by Lukas Renggli.
• For A1 Notation we leverage the a1 library a1.