Vim Spreadsheets

[mascip]

Hi there, 

I'm thinking using Vim for spreadsheets, for a few reasons:

 - you can diff it ! (For me, that's the main reason)

 - vim key mappings

 - vimscript, so I can easily program it myself

 - easy to add my own key mappings

 - faster to open and save than Libreoffice

I've done a bit of research and found these so far:

- csv.vim:      

      http://www.256bit.org/~chrisbra/csv.gif

      https://github.com/chrisbra/csv.vim

inspired by this advice for CSV files:

       http://vim.wikia.com/wiki/Working_with_CSV_files

seems to be maybe the best one at the moment... I haven't had the time to try it out yet

 - spreadsheet.vim: 

      http://www.vim.org/scripts/script.php?script_id=558

seems dead (not updated for 11 years now). But might do the job anyway?

 - vim-orgmode (vim plugin) => todo list, project planning 

 - table-mode:    

      https://www.youtube.com/watch?v=qA-ZT2r5-t0

      http://www.vim.org/scripts/script.php?script_id=4501

not really a spreadsheet... it seems like it's for displaying something as a table.

 - Tcalc:      

      http://tcalc.berlios.de/

not really a spreadsheet: "allows you to to do all your accounting with plain text files using the full power of your favorite text editor". "It is a command line tool that operates as a filter. It processes your accounting files as it's input, performs all the calculations, and outputs a report in the format of the original file[s]."

 - sc or slsc: 

      ftp://space.mit.edu/pub/davis/slsc/

a spreadsheet with vim-like keybindings, so not strictly Vim 

Has anyone used any of these? How was it?

Cheers =)

-- Pierre Masci

[lessthanideal]

On Tuesday, 25 March 2014 11:47:42 UTC, Pierre Masci wrote:
> Hi there, 
>
>
> I'm thinking using Vim for spreadsheets, for a few reasons:
>
>
>  - you can diff it ! (For me, that's the main reason)
>
>  - vim key mappings
>  - vimscript, so I can easily program it myself
>
>
>
>  - easy to add my own key mappings
>  - faster to open and save than Libreoffice
>
>
> I've done a bit of research and found these so far:
>
>
>
> - csv.vim:      
>
>
>       http://www.256bit.org/~chrisbra/csv.gif
>       https://github.com/chrisbra/csv.vim
>
> inspired by this advice for CSV files:
>        http://vim.wikia.com/wiki/Working_with_CSV_files
>
>
> seems to be maybe the best one at the moment... I haven't had the time to try it out yet

I just use the code from the "Working_with_CSV_files" page (although I think there were a couple of updates to it recently I haven't tried) and it works well for my simple CSV needs - highlight a given column, copy or delete or search it.

Out of curiosity some time ago I tried replacing it with csv.vim, I didn't really need the extra functionality and for fairly trivial reasons I went back to the previous code. (I couldn't get the cosmetics of the colourscheme to match what I already had, and didn't have too much time to tweak it properly to work as I wanted.)

If I was starting from scratch or starting to do more complicated work on CSV files I would go for csv.vim and learn how to use it.

> - spreadsheet.vim:
> http://www.vim.org/scripts/script.php?script_id=558
>seems dead (not updated for 11 years now). But might do the job anyway?

Don't use Libreoffice myself but from Wikipedia it "is capable of opening and saving most spreadsheets in Microsoft Excel file format". If that includes the newer XML file format, and that's what you might use - presumably this plugin was developed long before that and wouldn't suit.

<snip other options>

>
> Has anyone used any of these? How was it?
>
> Cheers =)
>
> -- Pierre Masci

regards,
Geoff

[Tim Chase]

On 2014-03-25 11:47, mascip wrote:
> - *sc* or *slsc*:

> ftp://space.mit.edu/pub/davis/slsc/
> a spreadsheet with vim-like keybindings, so not strictly Vim
>
> Has anyone used any of these? How was it?

Of your list, I've only toyed with "sc" which was a fun experience,
but certainly not where I'd reach for actual spreadsheet
functionality. Then again, I last toyed with it 5-7yrs ago.
Apparently Debian has been faithfully upgrading it since then, and I
just re-read the man pages, and it appears to have grown a LOT of new
features and functionality. I'll have to revisit this.

However, most of the time, I just shell out to a Python REPL and
enter my data there (which can be reformatted easily with vim before
copy/pasting it into the Python shell)

-tim

[lith]

> I'm thinking using Vim for spreadsheets, for a few reasons:

You didn't say what you are trying to achieve. Why do you think a spreadsheet is the right approach to solve that problem? Why not keep the data in csv files and then manipulate the data from a script (e.g. written in R) you evaluate interactivly (somehow)?

[piemas25]

Thanks a lot for your input, all :-)

Very good point lith: I use spreadsheets for many different purposes, and I need different solutions for different problems. That's probably why my question was vague: "Has anyone used any of these? How was it?"

So far I think I could replace some of my spreadsheets with two types of tools:

1. a custom app (it's your proposal lith: "keep the data in csv files and then manipulate the data from a script"). 

      => I have a farily compex accounting spreadsheet, which I plan to turn into an app.

      I will be much easier to interact with it than using a spraedsheet. Also, it is more lightweight, and thus easier to input some data without having to wait for Liberoffice to load and then save the file.

2. a spreadsheet (Vim solution)

      - csv.vim seems good for simple tasks but doesn't have full-blown spreadsheet capabilities. It doesn't enable me to program formulas in cells; but maybe I can create custom vimscripts for that? It' sounds annoying to have to re-invent the formula-wheel for each spreadsheet - has anyone gone that way?

            => I might use this solution for simple spreadsheets, like a list of interesting modules to look at later, and a small description for each.

      - scls seems like a promising solution, I'd have to look how reliable the community is - any feedback on that?

      - spreadsheet.vim seems to have spraedsheet capabilities: cells are easily programmable; but the community seems even smaller - or inexistant.

2bis. keep on using Libreoffice

      - it's heavy and slow, and doesn't have key mappings, but it works out of the box (user friendly display).

      It could be diff-able, if I save files as CSV, and thus don't use formulas, which makes it lose a lot of its appeal. There might be a trick to use formulas in CSV documents, maybe by programming plugins or using macros; but I think I'd rather spend energy exploring Vim-like solutions. 

Any more thoughts?

[Marc Weber]

Excerpts from mascip's message of Tue Mar 25 11:47:42 +0000 2014:
> I'm thinking *using Vim for spreadsheets*, for a few *reasons*:
libreoffice like tools can also export to html and import it again.

[..]
<TABLE CELLSPACING="0" COLS="2" BORDER="0">
<COLGROUP SPAN="2" WIDTH="85"></COLGROUP>
<TR>
<TD HEIGHT="16" ALIGN="LEFT">a</TD>
<TD ALIGN="LEFT">b</TD>
</TR>
</TABLE>
[..]

Its diffable

In the end it depends on which features you need.
openoffice like tools support
- formulars
- zooming (easily)
- printing
- collapsing colums (eventually this could be implemented by conceal
feature now)

I don't know more tools than you've already found yourself.

About sc/slc: I tried it once, but in the end I didn't use it often
enough to learn everything from scratch such as "how to export to csv,
how to ..."

Don't forget that in calculation apps you often can use
ctrl-<up/down/right/left> to jump to next border and tab/cr to move
right/bottom. Not as nice as Vim but often good enough.

Don't forget about additional csv tools such as ruby/python/... which
all can read/write such files easily. VimL is nice, but date
manipulating eg could be harder for instance.

Marc Weber

[tooth pik]

On Tue, Mar 25, 2014 at 11:47:42AM +0000, mascip wrote:
> Hi there,

> I'm thinking *using Vim for spreadsheets*, for a few *reasons*:

> - you can *diff* it ! (For me, that's the main reason)

> - vim key mappings
> - vimscript, so I can easily program it myself
> - easy to add my own key mappings
> - faster to open and save than Libreoffice

> I've done a bit of research and found these so far:

> - *csv.vim*:
> - *spreadsheet**.**vim*:
> - *vim**-orgmode* (vim plugin) => todo list, project planning
> - *table-mode*:
> - *Tcalc*:
> - *sc* or *slsc*:

> Has anyone used any of these?

sorry, no, I had my own needs and decided to address them head-on

several years ago, as I was transitioning from Windows to Linux, I had
an ugly several months as I transitioned from Excel to oocalc, and from
Visual Basic for Applications to the most arcane and ridiculous language
ever developed that was so bad I have since repressed its name

at the same time I was becoming a vim fan, and it occurred to me why not
build my own, I know what I want from it, and there can be no medium
that will give the level of "always available" as ascii text -- the
tools we use to access and manipulate it may evolve, but if your data is
stored in ascii it will never be rendered inaccessible due to upgrades

I wanted a budget spreadsheet to track, reconcile, and project into the
future my checking account -- the first iteration of supporting scripts
I wrote in TCL, as that was what I was learning at the time -- I've
since re-written them in python, as vim/python scripts, because ZyX was
doing so much work on the python interface I figured the more people
using it the quicker problems will be caught

I'm sure as it stands my small application won't suit your needs, but
may stand as inspiration for you to create your own, with just the
features you want and no more -- have a look at

http://toothpik.github.io

and scroll to konsole 4 where I start describing it -- if this is any
use to you, well, yea

--
_|_ _ __|_|_ ._ o|
|_(_)(_)|_| ||_)||<
|

[Christian Brabandt]

Am 2014-03-25 12:47, schrieb mascip:

> I'm thinking USING VIM FOR SPREADSHEETS, for a few REASONS:
> - you can DIFF it ! (For me, that's the main reason)

> - vim key mappings
> - vimscript, so I can easily program it myself
> - easy to add my own key mappings
> - faster to open and save than Libreoffice
>
> I've done a bit of research and found these so far:
>

> - CSV.VIM:

> http://www.256bit.org/~chrisbra/csv.gif
> https://github.com/chrisbra/csv.vim
>
> inspired by this advice for CSV files:

> http://vim.wikia.com/wiki/Working_with_CSV_files [3]

> seems to be maybe the best one at the moment... I haven't had the time
> to try it out yet

It was made to make handling of CSV files easier and not for
manipulating data or even a
spreadsheet tool. However for a long time I have been thinking if it is
possible to add formular
support to it, but I don't have any idea how to do it right. You would
need 2 views on a buffer
one for editing formulas and one for seeing the result. And this would
get even more complicated
if those formulas can be nested.

Therefore, csv.vim does not currently support this. I might add it
later, if support is requested and
can be reasonably well implemented.

It has never before requested however ;) Nevertheless csv.vim contains
some functions, that could be used
for example at evaluating the Sum of a column or to get the Cell at a
specified index.

Best,
Christian (csv.vim creator)

[piemas25]

​Thank you for your input Christian :-) I have to give csv.vim a try soon.

Thanks for your code Tooth: I'll check it out when I have a bit more time.

I think I might end up doing something similar. Or follow Marc's idea of using Python/Perl/Ruby.

So many possibilities, it's hard to choose. I'll have to try a few. And I'll have to sit down for an hour or two and look at my various spreadsheets, with a short list of possible tools written on a paper, and choose which tool would suit which spreadsheet.

Ideally I would like to use only 2 tools rather than too many.

When that is done (possibly not this most busy coming month) I will share my findings with the VimLondon Meetup group; and with this mailing list.

[glphvgacs]

On Tue, Mar 25, 2014 at 11:47:42AM +0000, mascip wrote:

> - *sc* or *slsc*:

> ftp://space.mit.edu/pub/davis/slsc/
> a spreadsheet with vim-like keybindings, so not strictly Vim

IMHO, if you're using vim to do handle your spreadsheet needs you're not
doing it right. i mean what happened to "do one thing but do it well"
philosophy of unix?

i would say stay with sc if you want a real spreadsheet calculator. it's
written by the inventor of java so it's like Rod Stewart's work before
he became acunt.

here is the best updated (read cleanest) version i have found
git://github.com/msharov/sc.git

and watch that space for new features. two thingw i would personally
love to see, and filled a bugs for, are the visual mode and cursor line
indicator.

[glphvgacs]

On Tue, Mar 25, 2014 at 11:47:42AM +0000, mascip wrote:

> - *Tcalc*:

> http://tcalc.berlios.de/
> not really a spreadsheet: "allows you to to do all your accounting with
> plain text files using the full power of your favorite text editor". "It is
> a command line tool that operates as a filter. It processes your accounting
> files as it's input, performs all the calculations, and outputs a report in
> the format of the original file[s]."

for your double-entery accounting needs try ledger:
http://github.com/ledger/ledger.git

[Eike Rathke]

Hi piemas25,

On Tuesday, 2014-03-25 15:41:53 +0000, piemas25 wrote:

> 2bis. keep on using Libreoffice

> It could be diff-able, if I save files as CSV, and thus don't use
> formulas, which makes it lose a lot of its appeal. There might be a trick
> to use formulas in CSV documents, maybe by programming plugins or using
> macros; but I think I'd rather spend energy exploring Vim-like solutions.

Check the "Edit filter settings" box when saving as Text CSV and in the
following dialog tick "Save cell formulas instead of calculated values".

> Any more thoughts?

If you wanted to preserve both, values and formulas, plus some of the
number formatting, you may give the SYLK format a try.

Eike

--
OpenPGP/GnuPG encrypted mail preferred in all private communication.
Key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A
Support the FSFE, care about Free Software! https://fsfe.org/support/?erack
Use LibreOffice! https://www.libreoffice.org/

[piemas25]

> "do one thing but do it well"

> stay with sc if you want a real spreadsheet calculator.

Very good point there.

The borders between spreadsheets,  Vim, and text editors are pretty blurry I find. Vim is a tool to manipulate textual and numerical data, in any form. A spreadsheet is specialised in "data formatted as a table", and also sometimes "where some cells contain formulas". It's also good at pretty formatting.

So far I identified three use cases which require different tools:

1. When I only need a LIST of items, with 2 or 3 elements per line, 

    and i want  the columns and rows to be clearly visible when I open the file. 

    I might also want a CSV file so it can easily be interacted with programmatically in the future, if I ever needed to. 

    In this case I could either use sc, or csv.vim. I have to try both.

2. For a user-friendly interface (but slower, and the convenience of formulas in cells 

    => Full fledged spreadsheet, probably with sc.

3. When I need a more lightweight and flexible way to interact with the data

    Then I need an app. I can either store the data in a database or in CSV files, depending on my needs. 

    - A CSV file is easier to setup, to diff, to look into and to modify manually. 

    - A database represents relationships between data, and thus will grow better as new types of data get added to the mix.

    CSV files for simple needs, databases for complexity.

One drawback of using one specialized too for each use case, is that you lose the benefit of "muscle memory" for key mappings. I'm guessing that sc's key mappings are similar enough to Vim's, so it won't be too much of a problem. 

Another drawback is the redundancy of configuration: I will have to declare the same key mappings for each tool; for instance I have exchanged the keys ; and : in vim. I will have to do the same in sc. And possibly, the lack of some Vim plugins that i'm used to. 

The only way to know, is to try. sc is installed, I will follow a tutorial soon.

CSV files can save formulas? Ace!

ledger does seem good indeed, cheers.

[glphvgacs]

On Wed, Mar 26, 2014 at 12:15:13PM +0000, piemas25 wrote:
>
> I might also want a CSV file so it can easily be interacted with
> programmatically in the future, if I ever needed to.
>
> In this case I could either use sc, or csv.vim. I have to try both.
>
>
> 2. For a user-friendly interface (but slower, and the convenience of
> formulas in cells
>
> => Full fledged spreadsheet, probably with sc.

only on vim's mailing list you would call sc's interface
"user-friendly"; great stuff.

>
>
> 3. When I need a more lightweight and flexible way to interact with the data
>
> Then I need an app. I can either store the data in a database or in CSV
> files, depending on my needs.

not sure what you mean by "lightweight" and "flexible".

> - A CSV file is easier to setup, to diff, to look into and to modify
> manually.

sc does io everything in text (another unix feature btw), though i've
had problem with diff'ing in-line. also with using vcs (git in my case)
for keeping track of stuff and laying out different scenarios... but in
both cases my knowledge of the tools (diff and git) may have been
lacking so your mileage may well vary.

> - A database represents relationships between data, and thus will grow
> better as new types of data get added to the mix.
>
> CSV files for simple needs, databases for complexity.
>
>
> One drawback of using one specialized too for each use case, is that you
> lose the benefit of "muscle memory" for key mappings. I'm guessing that
> sc's key mappings are similar enough to Vim's, so it won't be too much of a
> problem.

that, the "muscle memory", and the superior speed of cmdline
user-inteface to that of GUI, are preciesly the reasons i've gone with
sc, and also dwm, dwb, mutt, zathura, newsbeuter and ...

there is a lack of import/export of csv files in sc though but i
understand that libreoffice has a module for that. i use a python script
for that, from linuxjournal.com i think.

[piemas25]

Hi all, I think I've realized something important.

But first, two questions: 

 - is there no way to "Undo" my last actions in sc?

 - is there a way to perform actions in "visual mode", like in Vim?

I have read the whole manual and done two tutorial. I have printed the man page but it is very long, so i haven't read it all yet. sc deserves a more thorough tutorial.

This week I tried CSV.vim and sc, and I replaced one of my most complex spreadsheets by an app. Here is what i learned, and I think it's important.

1. A Database is meant to store data, and describe the relationship between data. A CSV file is a database in a human-readable format, so you can easily navigate it, and even do some changes. That is exactly what CSV.vim helps you do:

    - sort the data according to any given column

    - search for a value in one given column

    - filter the data (eg: "only show rows that contain "haha" in column 5, and "houhou" in column 7")

    - analyse the data (probability distribution on one column)

    - do substitutions in one column

    - etc

Very handy stuff but it doesn't replace a spreadsheet, it's very different. 

2. An App lets you perform actions on the data (which is stored in a database). It describes possible interactions between different piece of data, and lets you extract, process and display some information. It's the most flexible kind of tool, it can do pretty much anything.

3. A spreadsheet tells a story.

It stores data, lets you act on it, and it displays it all on one screen in order to tell a story. It could be the story of your finances, or it could be statistics that help you make a decision. But it's main strength is to display the information in a readable format, and let you change parameters and see how that changes the story.

sc really gives you that. You can: 

    - format cells or columns: you can color them, choose specific formats for numbers, put the text on the left/center/right, etc.

    - use formulas in cells to tell the story of the relashionship between data

    - name a cell or a range, so you can easily refer to it in another cell (that is proper story telling, that is!)

    - lock some cells in order for someone less experienced to change the parameters of the story without damaging it => you can share your story with anyone and let them play with it !

A spreadsheet can do less things than an app though, it is less good for expressing complex relashionships between complex sets of data, and it doesn't have access to the same external services (webservices, programming libraries, etc). 

But sc is smart! It enables you to easily plug some cells with external commands, so it can use any app out there. Awesome! The app does all the hard work, and in the end the spreadsheet tells the story in a readable format, and lets people play with it by tweaking some parameters.

Until now I has used spreadsheets for all of the above. Recently i've realised that some of my spreadsheets should better become apps with a database and a command line interface. And now I also know that I could use a spreadsheet to manipulate some of my apps, and display the results in an easy to manipulate manner. I don't  always need a full fledged HTML+Javascript frontend, sometimes an sc frontend is good enough.

How does this sound?

[glphvgacs]

On Sun, Mar 30, 2014 at 02:59:06PM +0100, piemas25 wrote:
> Hi all, I think I've realized something important.
>
> But first, two questions:
> - is there no way to "Undo" my last actions in sc?

msharov's does, don't know about the original one.
https://github.com/msharov/sc/blob/master/help.c#L141

> - is there a way to perform actions in "visual mode", like in Vim?

not at this moment, see these bugs for feature request:

https://github.com/msharov/sc/issues/2
https://github.com/msharov/sc/issues/3

> 3. A spreadsheet tells a story.
> It stores data, lets you act on it, and it displays it all on one screen in
> order to tell a story. It could be the story of your finances, or it could
> be statistics that help you make a decision. But it's main strength is to
> display the information in a readable format, and let you change parameters
> and see how that changes the story.

i would say have a look at R and gnuplot if you want to abstract out
details and get the big picture if you like. (there might be better
plotting tools for R out there, i just don't know them well).

[piemas25]

I don't manage to install msharov's version of sc, maybe because my OS is 64bits.

But I'm not sure that this version does Undo of actions. The u command in msharov's version is like in the original sc: it does Undo when you are in Input mode. It can undo changes on some text that you are writting, but I don't think it can undo spreadsheet actions, like deleting a column.

Unfortunately I couldn't find a sc mailing list. The community seems pretty small.

[Jostein Berntsen]

I have the same problem installing this version of sc. Gets this output:

o/vmtbl.o: In function `growtbl':
vmtbl.c:(.text.growtbl+0x24): undefined reference to `LINES'
vmtbl.c:(.text.growtbl+0x56): undefined reference to `COLS'
vmtbl.c:(.text.growtbl+0x2bf): undefined reference to `stdscr'
vmtbl.c:(.text.growtbl+0x2cd): undefined reference to `wmove'
vmtbl.c:(.text.growtbl+0x2d4): undefined reference to `stdscr'
vmtbl.c:(.text.growtbl+0x2d9): undefined reference to `wclrtoeol'
vmtbl.c:(.text.growtbl+0x2e5): undefined reference to `printw'
collect2: error: ld returned 1 exit status
make: *** [sc] Error 1


Jostein

[glphvgacs]

what's your system?

[glphvgacs]

On Sun, Mar 30, 2014 at 08:43:51PM +0200, Jostein Berntsen wrote:

well you should file a bug on github, this is really off-topic here.

[Jostein Berntsen]

I am on Slackware 64bbit. Will post on github.

[Andres Martinelli]

Hello. I am working on a terminal spreadsheet based on "sc".
Anyone interested can take a look at:
https://github.com/andmarti1424/scim
Andres M.

[Josef Fortier]

I'd put a vote in for csv.vim combined with dbext (with DBI)

dbext allows a post-processing function which in combination with csv allows arbitrary SQL commands on a column basis, which comes close to the standard cell manipulations of a spreadsheet.