Premise: Because the use of VBA code readability and poor extensibility, want to use Python implementation, to improve the reuse rate.
The current idea is to write the basic information in Excel, when executing a macro, VBA calls Python code through the library, generate statistics, what library can I use to implement?
Reply content:
Maintain an Excel-based project 5 years of bitter wrath. "Python (or any other language) action Excel" file, depending on whether you rely on Excel, can be divided into two categories.
In the first category, Excel files are just used to store data, and Python's operations on it are read and write only. This time, the operation of the file does not need to rely on Excel, your machine does not have Excel can execute. In this scenario, Csv,txt and other Excel-supported text file formats are a good choice. In this case, the so-called "Python Read and write Excel file", is actually python read-write text file, is nothing more than this text file is a certain format, find a CSV Class library processing can do more with less. If the csv/txt is too simple, the file needs multiple worksheet, then can be saved in xls/xlsx format, the corresponding read and write operation with the XLRD and Openpyxl mentioned above.
The second type, the operation of the file not only read and write data, including more other operations, such as inserting rows and columns, setting font color, wait. The file format must be the Excel only format (xls*). The corresponding operation, in fact, the most basic, is to call the Excel API through COM, actually the VBA call is also this thing. Python, and many other languages, are supported by COM, and in the script you get the Excel.Application, you can write Application.workbooks (1) as in VBA. Worksheet (1). The following sample is written in Ruby, and Python should be similar.
#引用COM
Require "Win32ole"
#连接一个已经打开的Excel
xlapp = Win32ole.connect ("Excel.Application")
#接下去就和VBA的写法没什么两样了
WB = Xlapp.activeworkbook #获取当前激活的workbook
WS = Wb.worksheets (1) #获取第一个worksheet
Ws.range (' A1 '). Value = "Hello" #在sheet1的A1写入 "Hello" search pyxll, you deserve to have Xlwings,excel has become a GUI, combined with numpy,pandas all kinds of cool.
There are, for example, inserting matplotlib diagrams directly, such as using a Python-brought sort line of code to complete a complex sequence of dozens of rows of 100 rows.
Just looked at the next version of the API, there are new Black technology, can excel install a plug-in and then use Python to write custom formulas instead of C # can also, do not need to use VBA. And by policy, after porting to the UWP later, it's all in C #. I want to use JS ... Escape ε= (´o ') Cython also can't get out of Python runtime. The Python.dll still wants it. Excel calls Python no problem. You can use Pywin32 to support COM.
I used to, wrote a function in Python, called in Excel.
You feel the need to wrap it in CX freeze, install it on the client, and don't need to use Cython. I have had this idea, because it is very difficult to adapt to the grammar of VBA, write a VBA, and then write the other on the various break not back.
The tools you mentioned upstairs have tried and failed.
Finally, I used a curve to the salvation of the method:
1. Use VBA to write the function to export JSON, note, is the function, you can convert the data in Excel directly into JSON and save directly, each time the source data changes are re-exported and saved.
2. Using Python to read JSON data and perform operations, it is well known that Python's method of reading JSON is very friendly
Of course, the disadvantage is that this method can only be used to calculate data, cannot change the Excel data Datanitro The problem I think I still a bit professional, although not financial, but a lot of processing financial data.
Background: The company programmer is absolutely impossible to give me a database account, even if only read permission. Company intranet Web System Auth is not perfect, I can easily bypass the permissions to crawl data.
At first, with Xlrd and XLWT. The two tools were later found to be cumbersome to write and performance was not very good. Instead, use the built-in CSV module. At this stage, the degree of automation is not high, requiring significant manual intervention and pre-processing
Now because of the large amount of data, I have a local LNMP environment and NAVICAT, the PayPal raw data into the operation of the database. This script is now being written, combining the email module with the Windows scheduled task to complete automation. Solverstudio for Excel
Support C # python, etc, has anyone ever tried?
"An integrated environment for optimisation using modelling languages within Excel"
Developing for Excel
If you were interested in developing for Excel, you might like the following resources.
Excel XLL add-in libary for writing C + + add-ins using Visual Studio (incl Express): Excel xll add-in Library
Excel-dna provides. NET Integration with Excel:excel-dna-home
Pyxll
, a "free for non-commercial and evaluation purposes" Excel extension, allows Python code to is used to add menus and user Functions within Excel.
Pycel
Would compile a spreadsheet into Python. It is developed to allow Non-linear optimisation of aircraft design.
Pyworkbooks
is a open source Python interface for Excel and GNumeric; It looks very nice, with careful thought given to speed issues (e.g. implenting generators).
Python for Excel
(PFE) is a commercial (?) Python scripting system for Excel, provides a complete set of tools for building of fully featured applications with Excel front end. It is in-process control of Excel with an out-of-process developement and debugging environment. "
