Premise: because the use of VBA code has poor readability and scalability, you want to use Python to improve the reuse rate. The current idea is to write basic information in Excel. When a macro is executed, the Python code is called in VBA through the library to generate statistics. Which library can be used for this purpose? Premise: because the use of VBA code has poor readability and scalability, you want to use Python to improve the reuse rate.
The current idea is to write basic information in Excel. When a macro is executed, the Python code is called in VBA through the library to generate statistics. Which library can be used for this purpose? Reply: maintain an Excel-based project for 5 years. The "python (or other language) excel operations" file can be divided into two types based on whether Excel is used.
In the first type, an excel file is only used to store data. python only reads and writes data. In this case, you do not need to rely on Excel for file operations, and can execute files without excel on your machine. In this scenario, csv, txt, and other Excel-supported text file formats are all good choices. In this case, the so-called "python reading and writing excel files" is actually a python reading and writing text files. This text file is in a certain format, and you can get twice the result with half the effort by finding a csv class library. If csv/txt is too simple and multiple worksheets are required in the file, they can be saved in the xls/xlsx format. The corresponding read/write operations are described in xlrd and openpyxl mentioned above.
Category 2: operations on files not only read and write data, but also include other operations, such as inserting rows and columns, setting the font color, and waiting. At this time, the file format must be excel only (xls *). In fact, the most basic operation is to call the Excel API through COM, which is actually called by VBA. Python and many other languages Support COM, and Excel is obtained in the script. application, you can write the Application just like in VBA. workbooks (1 ). worksheet (1. The following sample is written in ruby, and python should be similar.
# Reference COM
Require "win32ole"
# Connecting an opened Excel File
XlApp = WIN32OLE. connect ("Excel. Application ")
# The next step is no different from the way VBA is written.
Wb = xlApp. activeWorkbook # obtain the currently activated workbook
Ws = wb. worksheets (1) # obtain the first worksheet
Ws. range ('a1 '). value = "hello" # write "hello" to search for pyxll in A1 of sheet1. If you have xlwings, excel becomes a gui. Combined with numpy, pandas is a variety of great features.
For example, insert the matplotlib chart directly. For example, you can use the sort code of python to complete the complex sorting that can be completed by writing dozens of rows and one hundred rows in vba.
I just read the new version of the api, and there is a new black technology. You can install a plug-in excel and use python to write custom formulas and use C # instead. You do not need to use VBA. According to the policy, C # Will be used after being transplanted to UWP. I want to use js... Escape ε = ('o later) cython cannot be separated from python runtime. The python. dll is still required. Excel can call python. You can use pywin32 to support com.
I used it too. I wrote a function in python and called it in excel.
You may need to use cx freeze to package it and install it on the client without using cython. I once had this idea, because it is really difficult to adapt to the syntax of vba, write vba for a while, and write other things will not come back.
The tools mentioned above have also been tried and fail.
Finally, I used a method to save the country by curve:
1. Use vba to write the json export function. Note that it is a function that can directly convert the data in excel to json and save it. Each time the source data changes, it will be re-exported and saved.
2. Reading json data using python and performing operations. As we all know, python is very friendly in reading json data.
Of course, the disadvantage is that this method can only be used to calculate data and cannot change the excel Data datanitro. I think it is still a bit professional. Although it is not financial, it is a lot of financial data.
Background: Company programmers cannot give me a database account, even if they only have read permission. The company's Intranet web system auth is not complete, so I can easily bypass permissions to crawl data.
At first, xlrd and xlwt were used. Later I found these two tools a little difficult to write, and the performance was not very good. Use the built-in csv module instead. At this stage, the automation is not high and requires a large amount of manual intervention and pre-processing.
Now, because of the large data volume, I have a local lnmp environment and navicat to import the original paypal data and operate the database. Now I am writing this script, combining the email module and windows scheduled task to complete all automation. SolverStudio for Excel
C # Python is supported. Has anyone tried it?
"An integrated environment for optimisation using modelling languages within Excel"
Developing for Excel
If you are interested in developing for Excel, you might like the following resources.
Excel XLL add-in libary for writing C ++ add-ins using Visual Studio 2010 (incl Express): Excel xll add-in library
Excel-DNA provides. net integration with Excel: Excel-DNA-Home
PyXLL
, A "free for non-commercial cial and evaluation purposes" Excel extension, allows Python code to be used to add menus and user functions within Excel.
Pycel
Will compile a spreadsheet into Python. It was developed to allow non-linear optimisation of aircraft design.
PyWorkbooks
Is an 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 that "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."
