xlwings - let Excel fly into the sky

This article was reproduced from the public number: Python Technology

Life is short, I use Python!

Python as a scripting language, its programming methods are more and more popular with programmers, and its application is more and more extensive, in which the data analysis position talent demand is increasing, the use of Python-related modules for data analysis can greatly improve work efficiency, reduce the workload of data analysts. With more and more scenarios using Python in the day-to-day office, and a lot of repetitive work being done directly to the program, Python Operation Excel is also a must-have skill for every data analyst, so today's article takes a look at the artifacts in Python that can manipulate Excel worksheets.

Introduction to Python's Operation Excel module

Python operates Excel modules, the modules mentioned on the web are roughly: xlwings xlrd xlwt openpyxl pyxll etc., and they provide functionality in two forms:

• 1, read and write Excel files with Python, in fact, is to read and write a formatted text file, the operation of excel files and operation text csv are no different, Excel files are only used to store data.
• 2, in addition to operating data, you can also adjust the table width of Excel files, font color and so on. Also mentioned is that it is also possible to manipulate Excel documentation by calling Excel API with COM which is quite cumbersome and no different from VBA Excel

About xlwings

Why xlwings make Excel fly, because xlwings Excel read and write operations. Refer to the official website, all the technology is to meet human inertia, so xlwings can make the tedious work simple and concise.

Xlwings is an open source and free tool that makes it easy to read and write data from Excel files and to modify cell formats.

xlwings can also seamlessly connect matplotlib numpy and pandas support reading and writing numpy pandas data types, and import matplotlib visualizations into excel

Most xlwings can call programs written by VBA in Excel files, or you can have VBA call programs written in Python

xlwings benefits

• xlwings makes it easy to read and write data in Excel files and can modify cell formats
• xlwings can be seamlessly connected Matplotlib and Pandas
• xlwings can call a program written by VBA in Excel file, or it can have VBA call a program written by Pytho n.
• xlwings open source is free and has been updated

xlwings basic operation

xlwings are installed and used

As with other modules, xlwings to be installed before it can be used, and this article environment is python 3.6 version of the Windows environment.

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

Introduction of module use

import xlwings as xw

Python to Excel

The easiest and easiest way to connect to a workbook is by xw. Book provides: It finds the workbook in all application instances and returns an error, but if the same workbook is open in more than one instance, to connect to a workbook in an active application instance, you need to use xw.books and reference a specific application, using the following differences:

Header1	Header2	Header3
New book	xw. Book()	xw.books.add()
Unsaved book	xw. Book('Book1')	xw.books['Book1']
UBook by (full)name	xw. Book(r'D:/test/file.xlsx')	xw.books.open(r'D:/test/file.xlsx')

Note: When you specify a file path on Windows, you should use the original string by placing an r in front of the string, or use a double backslash: D:\Test\file.xlsx

Excel active object

# 活动应用程序（即Excel实例）
app = xw.apps.active


# 活动工作簿
wb = xw.books.active  # 在活动app
wb = app.books.active  # 在特定app


# 活动工作表
sht = xw.sheets.active  # 在活动工作簿
sht = wb.sheets.active  # 在特定工作簿


# 活动工作表的Range
xw.Range('A1')  #在活动应用程序的活动工作簿的活动表上

Basic operation

The following code shows the basics:

• Open the table
• Reference worksheet
• Reference cell
• The reference area
• Write data (data writes are written by row by default, and if you want to specify the appropriate column write, you need to add the appropriate parameters, specifying the parameters: transpose - True)
• Read the data

import xlwings as xw
# 打开表格
file_path = r'D:/test/file.xlsx'


xw.Book(file_path)   # 固定打开表格
xw.books.open(file_path) # 频繁打开表格


# 引用工作表
sht = wb.sheets['sheet1']


# 引用单元格
rng = xw.Range('A1')
# rng = sht[0,0] # 此代码第一行的第一列即a1,相当于 pandas 的切片


# 引用区域
rng = sht.range('a1:a5')
# rng = sht['a1:a5']
# rng = sht[:5,0]


# 写入数据


sht.range('a1').value = 'Hello Excel' # 指定一个单元格写入数据


# 按行写入数据
sht.range('a1').value = [1, 2, 3, 4,5,6,7,8]


# 按照列写入数据
sht.range('a2').options(transpose=True).value = [2, 3, 4, 5, 6, 7, 8]


# 按照二维列表的方式写入数据


sht.range('a9').expand('table').value = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i'],['j', 'k', 'l']]


# 读取写入的数据


print(sht.range('A1:D5').value)

xlwings combine matplotlib

xlwings in conjunction with Matplotlib can be attached to Excel using the pictures.add() method to easily paste Matplotlib diagram into a table as a picture. The detailed code is as follows:

 fig = plt.figure()  # 指定画布
 # plt.plot([1, 2, 3, 4, 5])
 plt.plot([36,5,3,25,78])
 plt.plot([9,10,31,45])
 plt.plot([6,14,45,31])
 sht = xw.Book(r'G:/test/test.xlsx').sheets[0]
 sht.pictures.add(fig, name='myplt', update=True)

summary

Grinding knife does not mistakenly cut wood workers, today's article is mainly the operation of Excel tools xlwings introduction, we all use tools to practice up, good practice how to screw the inside work, Ollie to!

That's what W3Cschool编程狮 has to say about xlwings - making Excel fly into the sky, and hopefully it will help you.