search

pythonanywhere / dirigible-spreadsheet

Dirigible, the Pythonic spreadsheet in your browser
MIT License
214 stars 50 forks source link

Compiling Excel spreadsheets to Python #4

Open LFoglietti opened 4 years ago

LFoglietti commented 4 years ago

Please have a look at this post.

(https://github.com/vallettea/koala/issues/249)

conradho commented 4 years ago

thanks, what would you like us to consider?

LFoglietti commented 4 years ago

Assuming I can manipualte Excel using another language ( C#, Java, Python...) we have 3 main scenarios for a simple worksheet where A1=5, A2=10 A3=Sum(A1:A2) =15

1) Read & Write Python Writes A1=20. Excel Calculate A3. Python Reads A3=30. Excel Must be installed.

2) Read & Write & UDF I write a Python function that sum 2 numbers. PySum(x. y) A3 now becomes PySum(A1:A2) =15 as if I were using and UDF. Python Writes A1=20. Excel calculates A3 which contains a pythonic UDF. Excel calls Python passing A1, A2. Python calculates PySum(). Python reads A3=30. This can be skipped because Python already knows Pysum() = 30. Even if I have only PythonicUDF in my sheet, the Excel engine is responsible for building a calculation tree. Pythonic Udf calculation will then be in Python ( c# java ecc..) leveraging speed, library and so on. Excel Must be installed.

3) Complete Integration Load Excel file in to Python. Excel is now a Python Object which behaves exactly as Excel. The sum function is automatically transformed in a Pysum(). I can now tell to my Python object to add a row between 1 and 2. Pysum will become PySum(A1:A3) and the result will be placed in Pythonic A4 as it would be in case 1 and 2 in Excel A4. You can think of your Excel file as a pithon ( or C# or java) 2D array where you can use Excel sintax and get the same Excel result. From now on everything is handled by Pithon which will clone and write its object to Excel if asked. Python changes its A1 array value to 20. Pithon calculates its A3 array value = 30. Excel does not need be installed. We used excel only to upload data and formula but we could have called a blank Python object and build it as our excel file as we could do with Read and Write in (1) and (2). Excel does not need to be installed.

Can your Library be considered as going towards (3).

millenniumhand commented 4 years ago

I suppose you could use parts of dirigible to accomplish some of that if you wanted to. There's a function to load Excel sheets into a dirigible sheet in dirigible/sheets/worksheet.py that would probably be a good place to start.

LFoglietti commented 4 years ago

Thanks for your answer. Where can I see which excel functions are supported?

millenniumhand commented 4 years ago

At this stage (since dirigible is an archive for an abandoned project that we are not working on anymore) your best bet is to work through the code so that you understand how it works. For any Excel formulae that it does not already support, I believe you can add code to add those functions (I think - it's been a while since I worked on this).

LFoglietti commented 4 years ago

Thanks . It looks like a very good project.

LFoglietti commented 4 years ago

Here are the functions supported. ( https://github.com/pythonanywhere/dirigible-spreadsheet/blob/master/documentation/spreadsheet-functions.rst)