DMelt:Plots/Spreadsheets

From HandWiki
Member


Showing data in spreadsheets

A spreadsheet is an interactive computer application for organization, analysis and storage of data in tabular form. All data containers (including histograms and functions) can be shown as a table (the class class jhplot.HTable jhplot.HTable or inside a spreadsheet, see the class jhplot.SPsheet jhplot.SPsheet. To show data in table, use the method "toTable()". To show data in a spreadsheet, initialize the class jhplot.SPsheet jhplot.SPsheet with an data object as an argument.

Showing spreadsheet

You can call a spreadsheet from the menu bar [Tool]->[Spreadsheet]. You will see the table. Another way to start is to use Jython prompt and type:

a=SPsheet()

You will see the spreadsheet again, but now you can "talk" to it since you have a created a "spreadsheet" object "a". Type "a." + [Ctrl]+[Space]. You will see many methods associated with the class "SPsheet". You can insert data, remove, while watching how the data are changed in the spreadsheet.

The spreadsheet is based on SharpTools package and have:

  • A Friendly Interface
  • Compatibility with Microsoft Excel
  • Configurable Preferences
  • Formulas and References, File Operations
  • Clipboard Operations
  • Undo and Redo, insertion and Deletion of Rows and Columns
  • Sorting Operations, Find and Find Next, Histograms
  • Password Security

The spreadsheet configuration file is located in ".jehep/" directory (linux) and called "sharptools.ini".

Programming with spreadsheet

You can program a spreadsheet in the usual manner, Create any object and insert it to the spreadsheet:

from jhplot import *
p=P1D()
p.add(1,2)
p.add(2,5)
SPsheet(p)

This will show s spreadsheet with a numbers of the P1D object.


Showing comma-separated values (CSV) files

Comma-separated values (CSV) files can be shown either by loading the file from the menu of the spreadsheet, or inserting the data as a object in a programmatic manner.

Here is a small code which read csv file and shows the spreadsheet:

from jhplot import *
from jhplot.io.csv import *
c=CSVReader('mytable.csv',',') # assume comma separation
SPsheet(c)

Read more about the methods of the jhplot.SPsheet jhplot.SPsheet class.


Spreadsheet manual

Input

Data is input to cells in the workspace. When a cell is clicked on, or highlighted, it is said to be selected. To make a cell editable, double-click on it. To select a range, select a cell and drag the mouse over the desired region. The range will be highlighted.

Sharp Tools Spreadsheet allows for user-defined formula input, such as:

=1+54 =B10^(5-2) =AVERAGE(C1:C10) =SUM(C2:C7)/2

Sharp Tools Spreadsheet allows nearly arbitrary variable-length parameter input to most formulas using

literals: -1, 0, 100
    relative addressing: A1, B4, E76
    absolute addressing: $A$2, $B$5, $E$7
    range specification: C1:C10, A2:B2

Functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structure.

Arguments can be numbers or cell references. A cell reference can be a single cell or a range of cells. A single cell can use relative addressing (A1) or absolute addressing ($B$5). A range of cells is specified by a pair of diagonal cells (A1:C5 or $A$1:$C:$5). Different functions have difference requirements on the number or type of parameters.

The structure of a function begins with the an equal sign (=), the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.

=FUNCTION(ARGUMENT1,ARGUMENT2,...)

Sharp Tools Spreadsheet implements various symbolic formula operations:

   ABS
   usage: =ABS(argument)
   Returns the absolute value of is argument.
   Example: =ABS(-1) returns 1.
   AVERAGE
   usage: =AVERAGE(argument1,argument2,...)
   Returns the average (arithmetic mean) of its arguments.
   COUNT
   usage: =COUNT(argument1,argument2,...)
   Returns the number of its arguments.
   Example: =COUNT(A1:A25,B1) returns 26.0.
   E
   usage: =E()
   Returns an approximation of the golden mean. Takes no arguments.
   Example: =E() returns 2.7182817
   INT
   usage: =INT(argument)
   Returns the integer portion of its argument.
   Example: =INT(94.99) returns 94.0.
   LOG
   usage: =LOG(argument)
   Returns the logarithm base e of its argument.
   Example: =LOG(E()*E()) returns 2.0.
   MAX
   usage: =MAX(argument1,argument2,...)
   Returns the largest number among its arguments.
   Example:=MAX(99.99,100,99.9999) returns 100.0.
   MEANDEV
   usage: =MEANDEV(argument1,argument2,...)
   Returns the largest average absolute deviation of its arguments.
   MEDIAN
   usage: =MEANDEV(argument1,argument2,...)
   Returns the largest average absolute deviation of its arguments.
   MIN
   usage: =MIN(argument1,argument2,...)
   Returns the largest average absolute deviation of its arguments.
   PI
   usage: =PI()
   Returns an approximation of pi. Takes no arguments.
   RANGE
   usage: =RANGE(argument1,argument2,...)
   Returns the difference between MAX and MIN of its arguments.
   ROUND
   usage: =ROUND(argument)
   Returns the nearest integer value of its argument.
   SQRT
   usage: =SQRT(argument)
   Returns the square root of its argument.
   SUM
   usage: =SUM(argument1,argument2,...)
   Adds up the specified arguments.
   STDDEV
   usage: =STDDEV(argument1,argument2,...)
   Returns the standard deviation of its arguments.
   TRIG
   TRIG can be any one of the trigonometric functions SIN, COS, TAN, ASIN, ACOS, or ATAN. 
   All trigonometric functions take only one parameter input, which is specified in radians.
   usage: =TRIG(argument)
   Example: =COS(2*PI()) returns 1.0. 
   
   

Errors

Errors are of the form #ERROR?, where ERROR is the type of error. There are several errors that are possible when entering formulas:

   #PAREN: Missing a parenthesis.
   Example: =SUM(A1:A25
   #NUM?: Improper number format.
   Example: 2.3.4
   #OP?: Improper use of an operator.
   Example: =2++3
   #NAME?: An unknown syntax.
   Example: =AD
   #REFS?: A cell that does not exist has been referenced.
   #ADDR?: An invalid cell has been referenced.
   Example: =1A
   #EVAL?: The input cannot be evaluated properly.
   Example: =A4:B4+1
   #FUNC?: An unknown function has been used
   Example: =NOP()
   #PARAM?: Too many or too few arguments specified.
   Example: =MIN()