Answer the question
In order to leave comments, you need to log in
Programming language for working with Excel?
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 the question
In order to leave comments, you need to log in
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
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
As an alternative, consider calling VBA macros from sources external to the file being processed:
I used the XlsxWriter + Pandas bundle . Completely working solution. But, alas, only for xlsx. xls support - no.
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.
Try the free PowerQuery plugin. It has the functional language M from PowerBI, much more powerful than VBA
Java + Apache POI. I worked on such a bunch with excel tables. It feels very convenient, the library is understandable.
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".
Try pandas, in principle, and then life will become easier in the IT world)
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question