Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "how to install and use xlwings", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to install and use xlwings" article.

Brief introduction of Excel Module for Python Operation

Python operates Excel modules. The modules mentioned on the Internet are: xlwings, xlrd, xlwt, openpyxl, pyxll and so on. The functions they provide can be summarized in two forms:

1. Reading and writing Excel files with Python is actually reading and writing formatted text files. There is no difference between operating excel files and operating text and csv files. Excel files are just used to store data.

2, in addition to manipulating the data, you can also adjust the table width of the Excel file, font color and so on. It should also be mentioned that it is also feasible to use COM to call Excel's API to manipulate Excel documents, which is quite troublesome and basically no different from VBA.

About xlwings

Why does xlwings make Excel fly? because xlwings supports read and write operations of Excel. For specific use, please refer to the official website, the emergence of all technologies are to meet people's inertia, so xlwings can make tedious work simple and concise.

Xlwings is an open source and free tool, which can easily read and write data in Excel files and modify cell format.

Xlwings can also seamlessly connect with matplotlib, numpy and pandas, support reading and writing numpy and pandas data types, and import matplotlib visual charts into excel.

The most important thing is that xlwings can call the program written by VBA in the Excel file, or you can let VBA call the program written in Python.

Advantages of xlwings

Xlwings can easily read and write data in Excel files, and can modify the cell format.

Xlwings can be seamlessly connected with Matplotlib and Pandas

Xlwings can call the program written by VBA in the Excel file, or you can let VBA call the program written by Python.

Xlwings is open source and free, and is constantly being updated

Xlwings basic operation xlwings installation and use

Like other module usage, xlwings needs to be installed before it can be used, and this article is based on the Windows environment of Python version 3.6.

Module installation

The easiest way to install xlwings is through pip:

Pip install xlwings

Or use conda:

Conda install xlwings

Or

Conda install-c conda-forge xlwings import module using import xlwings as xwPython to Excel

The easiest and easiest way to connect to a workbook is provided by xw.Book: it looks for the workbook in all application instances and returns an error, but if the same workbook is open in multiple instances, to connect to a workbook in the active application instance, you need to use xw.books and reference a specific application, using the following differences:

Header1Header2Header3New bookxw.Book () xw.books.add () Unsaved bookxw.Book ('Book1') xw.books [' Book1'] UBook by (full) namexw.Book

Note: when specifying a file path on Windows, you should use the original string by placing an r before the string, or use a double backslash: d:\ Test\ file.xlsx

Excel active object # active application (that is, Excel instance) app = xw.apps.active# active workbook wb = xw.books.active # active appwb = app.books.active # active worksheet sht = xw.sheets.active # active workbook sht = wb.sheets.active # active worksheet Rangexw.Range ('A1') # active in specific workbook Basic operations on the active table of the active workbook of the application

The following code shows the basic operations:

Open the form

Reference worksheet

Reference cell

Reference area

Write data (data is written according to the row by default. If you want to specify the corresponding column write, you need to add the corresponding parameter. The specified parameter is: transpose = True)

Read data

Import xlwings as xw# Open form file_path = r'D:/test/file.xlsx'xw.Book (file_path) # fixed Open form xw.books.open (file_path) # frequently Open form # reference worksheet sht = wb.sheets ['sheet1'] # reference cell rng = xw.Range (' A1') # rng = sht [0jue 0] # the first column of the first line of this code is A1 Equivalent to pandas slice # reference region rng = sht.range ('a1pura5') # rng = sht [' a1visa5'] # rng = sht [: 5Power0] # write data sht.range ('a1'). Value =' Hello Excel' # specify a cell to write data # write data sht.range ('a1') by row. Value = [1, 2, 3, 4, 5, 6, 7 8] # write data sht.range ('a2') .options (transpose=True). Value = [2, 3, 4, 5, 6, 7, 8] # write data sht.range (' a9'). Expand ('table'). Value = [[' a9'). 'kink,' l']] # read and write data print (sht.range ('A1 value D5). Value) xlwings combined with matplotlib

Xlwings in conjunction with Matplotlib uses speaking pictures to paste into Excel, and using the pictures.add () method, you can easily paste Matplotlib pictures into a table as pictures. The detailed code is as follows:

Fig = plt.figure () # specify canvas # plt.plot ([1, 2, 3, 4, 5]) plt.plot ([36 sht.pictures.add 5 pint 3pc 25 name='myplt', update=True 78]) plt.plot ([9 Meng 10 Meng 31]) plt.plot ([6 Meng 14 Min 45 Jo 31]) sht = xw.Book (ringing Glump hand Test.xlsx'). Sheets [0] sht.pictures.add (fig, name='myplt', update=True) above is about "how to install and use xlwings". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.