search

ihdavids / orgextended

Sublime Text OrgMode Extension
MIT License
205 stars 15 forks source link

ods<--> org-table with formula #64

Open ouboub opened 2 years ago

ouboub commented 2 years ago

Hi

I attach a zip file that contains an org file with two tables

  1. in the first the columns are added
  2. in the second the rows

moreover the ods version of these tables are also included, and finally the csv version with the (ods) formula

so the question is: could a lisp function implemented that would convert

| Student  | Mark1 | Mark2 | Mark3 | Result | Result-simple | ResultSkaled |
|----------+-------+-------+-------+--------+---------------+--------------|
| Student1 |     2 |     3 |     4 |      9 |             9 |          900 |
| Student2 |     4 |     2 |     1 |      7 |             7 |          700 |
#+TBLFM: $5=vsum($2..$4)::$6=$2+$3+$4::$7=vsum($2..$4)*100

to 

| Student  | Mark1 | Mark2 | Mark3 | Result      | Result-simple | ResultSkaled    |
| Student1 |     2 |     3 |     4 | =SUM(B2:D2) | =SUM(B2:D2)   | =SUM(A2:D2)*100 |
| Student2 |     4 |     2 |     1 | =SUM(B3:D3) | =SUM(B3:D3)   | =SUM(A3:D3)*100 |

and back

and also

| Account      |     |
|--------------+-----|
| Account1     | 200 |
| Account1     | 300 |
|--------------+-----|
| All accounts | 500 |
#+TBLFM: @4$2=vsum(@I..@II)

to 

| Account      |             |
| Account1     |         200 |
| Account1     |         300 |
| All accounts | =SUM(B2:B3) |
ihdavids commented 2 years ago

Looks like there is a python ods exporter: https://pythonhosted.org/pyexcel-ods/

But I don't think I would want to include that in the orgextended package. That is one of the major limitations of the sublime extension system, very few packages are included and you do not easily have access to pip, so I would have to embed that extension into the package.

On the conversion side. Well the plugin certainly could do it.

As you may know the babel system lets you use a table as a data source for a python (or other language) snippet, which can be used to export the table to CSV et al. (I use it regularly to build mermaid charts and other things) That said, you don't get access to the formulas in that flow only the evaluated data so you would need to extend the plugin.

As you can see with the cell highlighting it has knowledge of which formulas impact which cells. It's also actually running as a converted python expression under the hood. It's rather lame but I have an interpreter that handles the expressions. There is a mapping table that already defines each of the functions for the interpreter. That code is.... not pretty, as is much of the system as I tend to hack on it quickly while working on other things, but you could probably leverage the table evaluation routine to output a csv with converted expressions fairly easily.

It's a good idea but I probably won't be able to get to it myself in the short term.

ihdavids commented 2 years ago

I would be happy to help provide insight / review something if you want to give it a try?