"Python" is a great Excel action module xlwings

"Xlwings"

When it comes to Python operating Excel, there are a lot of modules that can support this work. The bottom-level Win32 module, for example, can operate not only Excel, but also other windows-like software.

Others are more familiar with the three XLRD,XLWT and Xlutils. Responsible for the Excel file reading, writing, read and write conversion work. Although the general Excel operation can be done through these three modules, there are still a lot of inconvenient places. For example, writing and reading in cell format is cumbersome, and the format will not be copied together by xlutils an Excel read and then written to a new file.

In addition, I recently met the need to fill in the data based on an Excel template. While it is possible to convert a template into XLWT code to write dead in a build script, it is too cumbersome to regenerate a file each time, and a grid-by-grid write can make the code soar. Helpless under another find his way, try to use xlwings this module.

Basic use

Unlike XLRD, the basic idea of xlwings design is not for a single Excel document, but rather to work with an Excel project that contains multiple Excel documents. By building the concepts of logic components such as apps, you can make the entire Excel project more orderly and easier to calculate and communicate with each other. The conceptual hierarchy of the various models designed in Xlwings is as follows:

Where the app is a logical grouping, a book can be thought of as an Excel document, sheet corresponds to a worksheet, and range corresponds to the content of an area in a specific table. First, here's one of the most common, simple procedures for opening an Excel document and working with it:

Importxlwings as Xwbook= XW. Book ('/path/to/test.xlsx')#The interface will pop up the Excel window, if the test.xlsx file does not exist will be an error, if TEST.XLSX has been opened, directly return the file objectPrintBook.name,book.fullname#print file name and absolute pathPrintBook.app#you can see which app your book is inPrintBook.sheets#is also a class list structure that holds various sheet objectsBook.activate ()#if Excel does not get the focus of the current system, call this method to go back to ExcelBook.close ()#close the Excel document, but just close the file itself and do not close the Excel program ... To close an Excel program, you need to call the Kill method that responds to the app instance. After experimenting, calling close causes the default created app instances to disappear automatically, so that kill cannot be called, so Excel is not shut down
So the best way is not to call this close but call App.kill ().
Sheet =Book.sheets[0]#other ways to get sheet objects are book.sheets[' sheet_name ']

It says that to get a specific sheet, a sheet can be called by:

Sheet.activate       sheet.charts         sheet.index sheet.api            sheet.clear          sheet.name sheet.autofit        Sheet.clear_contents sheet.names sheet.book           sheet.delete         sheet.pictures sheet.cells          Sheet.impl           Sheet.range
... Wait a minute

These methods, such as activate,autofit,cliear_content, are quite interesting. The most important method is the range, which can be used to obtain a specific range of data.

For example, Sheet.range (' A1 ') gets the object to the A1 cell, and by fetching the Value property of the object, it can read/change the cell values, and none of this will affect the format of the cell itself.

A more imba approach is that Sheet.range (' a1:c3 ') can be used to retrieve objects of all cells in a single area at once. When the value object is tuned, the data collection is also a two-dimensional list, which makes it easy to map the data in Excel into Python. For setting up data, the incoming data can be a structure that does not conform to the specified area, but the results are subtle. Suggested experiments

It should be noted that, as with XLRD, the merged cell only has a value in the child cell in the upper-left corner, and none is the same as the non-filled cell when reading value.

For range, there's more to be done.

In addition to simply calling value and assigning values to value to read and write to Excel, the following interfaces can be used

Range.add_hyperlink  range.clear_contents range.count range.address        range.color          range.current_region Range.api            range.column         range.end range.autofit        range.column_width   range.expand range.clear          Range.columns        Range.formula
... Wait a minute

Some of the interface usage tips:

Range.add_hyperlink (' https://www.baidu.com ', ' Baidu ')

Range.color = (128,128,128) RGB channel color, can get or set

Range.row/column get the first few rows/columns, note the first and not the subscript

Range.formula can be used to set calculation expressions for in-table calculations

Range.current_region returns the area representation of the current range, which is more difficult to describe, like a cell in Excel that is connected to each other is a liancheng, and no adjacent two pieces are independent of one another.

Range.count returns how many cells are in this range, and the merged cells are still calculated as

Range.offset (A, B) gets to the current range to the right a, move down the area of the same size, AB can be negative

Range.rows/columns returns each Range object of a row/column

　　

About the app

In fact, the app has not been carefully studied, simply speaking, when through XW. When book creates an instance, it adds an instance of the app to Xw.apps by default, and book belongs to the app.

As mentioned above, solve the problem that Excel program can't close can use the Kill method of this app instance. App.kill () can close the Excel program together with the file. In addition, if you do not want the Excel program to jump out, you can set App.visible=false, instant settings immediately take effect

　　

"Python" is a great Excel action module xlwings