Programming language for working with Excel?

Question

Python

Hello! There is a need for programming routine work in Excel. Himself an amateur, I used to try to write in VBA . I decided to create a website (for friends) and process Excel files. Does VBA run inside the Excel file, then the question arose about external file handlers/editor.
I searched on the net and made a choice on Python - library - Openpyxl .
I've been using it for 3 days and so far it feels like the library is terrible, not sharpened for excel. The trouble is that there are always data type errors (tuple of tuples, iteration type is not subject, etc.). Even the difficulty in specifying previously unknown ranges.
My tasks are mainly in the creation of accounting algorithms, data sampling, spreading into certain cells, arithmetic operations, etc.
What can replace openpyxl with? I thought about Pandas , but it seems to be for analysis mainly, is it suitable for my tasks and is it easier to use?
I understand that a bad dancer.......... But what is the best solution for an amateur proger?

Answer 1

best = give up excel

Answer 2

If Excel, the format with which everything begins and ends, requires coloring the sheet, formulas, etc., etc., then there is no alternative to VBA. You can try sharp, but it will also require the included excel.
If there is only data in excel, then almost any PL normally does csv import and export

Answer 3

https://docs.microsoft.com/ru-ru/office/open-xml/o...
https://habr.com/sandbox/122135/
https://github.com/ClosedXML/ClosedXML
https:/ /github.com/JanKallman/EPPlus
.. and so on... be friends with search engines

Answer 4

As an alternative, consider calling VBA macros from sources external to the file being processed:

• Any running program from MS Office. For example, I have experience in creating an intracorporate service on Outlook. Those. you can use mail as an internal interface.
  
• Call VBA macros from Command Line/Powershell. This option allows you to connect a Python web backend with VBA business logic on Windows.
  

Answer 5

I used the XlsxWriter + Pandas bundle . Completely working solution. But, alas, only for xlsx. xls support - no.

Answer 6

PHP Spreadsheet
https://habr.com/post/64762/

Answer 7

The excel format is proprietary.
Existing libraries are created mainly for beautiful data export to Excel, and not full-fledged work with this format.
Therefore, you really need to get away from writing formulas in Excel. Do all the calculations in your software, and export only static data to Excel for printing or viewing by the user. No interactivity. Maximum - simple filters.

Answer 8

Try the free PowerQuery plugin. It has the functional language M from PowerBI, much more powerful than VBA

Answer 9

Java + Apache POI. I worked on such a bunch with excel tables. It feels very convenient, the library is understandable.

Answer 10

It seems to me that something soft and warm is mixed with excel here. If you need a "wrapper" for working with Excel, then this is one option (here you can search for a long time and choose what will allow you to solve certain tasks and what manuals are). If the main thing for you is to get access to the object model, then it doesn’t matter what library and what it can do, as long as an object is created (including the Excel application), and methods and functions are a complete set from Microsoft (“through a dot”, you can even peep in the office VBE) with the description "from the manufacturer".

Answer 11

Try pandas, in principle, and then life will become easier in the IT world)