P
P
ProgerFromGod2018-12-06 16:30:21
Python
ProgerFromGod, 2018-12-06 16:30:21

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

11 answer(s)
M
Mikhail Lyalin, 2018-12-06
@mr_jok

best = give up excel

S
sim3x, 2018-12-06
@sim3x

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

#
#, 2018-12-06
@mindtester

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

O
Oleg, 2018-12-07
@OlegPyatakov

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.

C
CH1979, 2018-12-07
@CH1979

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

D
Dmitry, 2018-12-06
@dimoff66

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

S
Saboteur, 2018-12-07
@saboteur_kiev

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.

D
Dima, 2018-12-07
@v_m_smith

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

N
Nikita Maksimov, 2018-12-08
@prosto_mimo_prohodil

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

E
Eugene Kuznetsov, 2018-12-20
@KEugene

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".

P
Platon Lukyanov, 2021-02-04
@platonlukyanov2007

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question