Excel is a spreadsheet software developed by Microsoft for Windows and macOS. Because of its intuitive interface, excellent calculation ability and chart tools, together with successful marketing, Excel has always been the most popular data processing software on personal computers. Of course, Excel also has many competing products, such as Google Sheets, LibreOffice Calc, and Numbers. These competing products can basically also be compatible with Excel, at least they can read and write newer Excel files. Of course, these are not the focus of our discussion. Mastering how to operate Excel files with Python programs can make daily office automation work easier and more pleasant. Also, in many commercial projects, importing and exporting Excel files are very common functions.
In this lesson, we continue with another third-party library, openpyxl, and first install it.
pip install openpyxlThe advantage of openpyxl is that after we open an Excel file, we can do both reading and writing operations on it, and in convenience it is better than xlwt and xlrd. Besides that, if we want to do style editing and formula calculation, using openpyxl is much simpler than the way introduced in the previous lesson. Also, openpyxl supports operations such as pivot data and inserting charts, so its functions are very powerful. One point that needs to be emphasized again is that openpyxl does not support operating Excel files of versions before Office 2007.
For example, suppose there is an Excel file named 阿里巴巴2020年股票数据.xlsx in the current folder. If we want to read and display its contents, we can use the code below.
import datetime
import openpyxl
# Load a workbook -> Workbook
wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
# Get worksheet names
print(wb.sheetnames)
# Get a worksheet -> Worksheet
sheet = wb.worksheets[0]
# Get the cell range
print(sheet.dimensions)
# Get the row count and column count
print(sheet.max_row, sheet.max_column)
# Get the value of a specific cell
print(sheet.cell(3, 3).value)
print(sheet['C3'].value)
print(sheet['G255'].value)
# Get multiple cells, returned as nested tuples
print(sheet['A2:C5'])
# Read the data in all cells
for row_ch in range(2, sheet.max_row + 1):
for col_ch in 'ABCDEFG':
value = sheet[f'{col_ch}{row_ch}'].value
if type(value) == datetime.datetime:
print(value.strftime('%Y年%m月%d日'), end='\t')
elif type(value) == int:
print(f'{value:<10d}', end='\t')
elif type(value) == float:
print(f'{value:.4f}', end='\t')
else:
print(value, end='\t')
print()Tip: The Excel file
阿里巴巴2020年股票数据.xlsxused in the code above can be downloaded from the Baidu Netdisk link given later. Link: https://pan.baidu.com/s/1rQujl5RQn9R7PadB2Z5g_g Password:e7b4.
There is one point that needs to remind everyone of. openpyxl has two ways to get a specified cell. One is through the cell method. It should be noted that the row index and column index of this method both start from 1. This is to match the habit of people who are used to Excel. The other is through index operation. By specifying the coordinate of the cell, such as C3 and G255, we can also get the corresponding cell. Then through the value attribute of the cell object, we can get the value of the cell. Through the code above, I believe everyone also noticed that through slice operations like sheet['A2:C5'] or sheet['A2':'C5'], we can get multiple cells. This operation returns nested tuples, which is equal to getting multiple rows and columns.
Next, let us use openpyxl to write an Excel file.
import random
import openpyxl
# Step 1: create a workbook
wb = openpyxl.Workbook()
# Step 2: add a worksheet
sheet = wb.active
sheet.title = '期末成绩'
titles = ('姓名', '语文', '数学', '英语')
for col_index, title in enumerate(titles):
sheet.cell(1, col_index + 1, title)
names = ('关羽', '张飞', '赵云', '马超', '黄忠')
for row_index, name in enumerate(names):
sheet.cell(row_index + 2, 1, name)
for col_index in range(2, 5):
sheet.cell(row_index + 2, col_index, random.randrange(50, 101))
# Step 4: save the workbook
wb.save('考试成绩表.xlsx')When using openpyxl to operate Excel, if we want to adjust the style of a cell, we can directly operate through the properties of the cell object, the Cell object. The properties of the cell object include font, alignment, border and so on. For details, you can refer to the official documentation of openpyxl. When using openpyxl, if we need to do formula calculation, we can completely do it in the same way as in Excel. The code is shown below.
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
# Alignment settings
alignment = Alignment(horizontal='center', vertical='center')
# Border line style
side = Side(color='ff7f50', style='mediumDashed')
wb = openpyxl.load_workbook('考试成绩表.xlsx')
sheet = wb.worksheets[0]
# Adjust row height and column width
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['E'].width = 120
sheet['E1'] = '平均分'
# Set the font
sheet.cell(1, 5).font = Font(size=18, bold=True, color='ff1493', name='华文楷体')
# Set the alignment
sheet.cell(1, 5).alignment = alignment
# Set the border
sheet.cell(1, 5).border = Border(left=side, top=side, right=side, bottom=side)
for i in range(2, 7):
# Calculate the average score for each student with a formula
sheet[f'E{i}'] = f'=average(B{i}:D{i})'
sheet.cell(i, 5).font = Font(size=12, color='4169e1', italic=True)
sheet.cell(i, 5).alignment = alignment
wb.save('考试成绩表.xlsx')With the openpyxl library, we can also insert charts directly into Excel. The overall process is quite similar to inserting charts in Excel itself. We can create a chart object of the desired type, configure it through its properties, and most importantly bind data to it, meaning what the horizontal axis represents, what the vertical axis represents, and what the actual values are. Finally, the chart object can be added to the worksheet. The code is shown below.
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook(write_only=True)
sheet = wb.create_sheet()
rows = [
('类别', '销售A组', '销售B组'),
('手机', 40, 30),
('平板', 50, 60),
('笔记本', 80, 70),
('外围设备', 20, 10),
]
# Add rows to the worksheet
for row in rows:
sheet.append(row)
# Create a chart object
chart = BarChart()
chart.type = 'col'
chart.style = 10
# Set the chart title
chart.title = '销售统计图'
# Set the vertical-axis title
chart.y_axis.title = '销量'
# Set the horizontal-axis title
chart.x_axis.title = '商品类别'
# Set the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5, max_col=3)
# Set the category range
cats = Reference(sheet, min_col=1, min_row=2, max_row=5)
# Add data to the chart
chart.add_data(data, titles_from_data=True)
# Set the categories
chart.set_categories(cats)
chart.shape = 4
# Add the chart to a specified position in the worksheet
sheet.add_chart(chart, 'A10')
wb.save('demo.xlsx')Running the code above and opening the generated Excel file produces the effect shown below.
After mastering the way of using Python programs to operate Excel, you can solve many tedious jobs of processing Excel spreadsheets in daily office work. The most common one is merging multiple Excel files with the same data format into one file, and extracting specified data from multiple Excel files or worksheets. If the amount of data is larger or the way of processing data is more complex, we still recommend everyone use the pandas library, one of the magic tools of Python data analysis.