Python处理Excel效率高十倍(下篇)通篇硬干货，再也不用加班啦

科技 07-02 来源：竹婉

《用Python处理Excel表格》下篇来啦！

身为工作党或学生党的你，平日里肯定少不了与Excel表格打交道的机会。当你用Excel处理较多数据时，还在使用最原始的人工操作吗？现在教你如何用Python处理Excel，从此处理表格再也不加班，时间缩短数十倍！

上篇我们进行了一些事前准备，目的是用Python提取Excel表中的数据。而这一篇便是在获取数据的基础上，对Excel表格的实操处理。

操作

创建新的excel

第9行代码用来指定创建的excel的活动表的名字：
·不写第9行，默认创建sheet
·写了第9行，创建指定名字的sheet表

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = '1号sheet'
workbook.save('1.xlsx')

修改单元格、excel另存为

第9行代码，通过给单元格重新赋值，来修改单元格的值
第9行代码的另一种写法sheet['B1'].value = 'age'
第10行代码，保存时如果使用原来的（第7行）名字，就直接保存；如果使用了别的名字，就会另存为一个新文件

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
sheet['A1'] = 'name'
workbook.save('test.xlsx')

添加数据

插入有效数据
使用append()方法，在原来数据的后面，按行插入数据

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

data = [
    ['素子',23],
    ['巴特',24],
    ['塔奇克马',2]
]
for row in data:
    sheet.append(row)   # 使用append插入数据
workbook.save('test.xlsx')

插入空行空列

·insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在idx行数的下方插入
·insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在idx列数的左侧插入

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

sheet.insert_rows(idx=3, amount=2)
sheet.insert_cols(idx=2, amount=1)
workbook.save('test.xlsx')

删除行、列

·delete_rows(idx=数字编号, amount=要删除的行数)
·delete_cols(idx=数字编号, amount=要删除的列数)

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

sheet.delete_rows(idx=10)           # 删除第10行
sheet.delete_cols(idx=1, amount=2)  # 删除第1列，及往右共2列
workbook.save('test.xlsx')

移动指定区间的单元格(move_range)

move_range(“数据区域”,rows=,cols=)：正整数为向下或向右、负整数为向左或向上

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

sheet.move_range('D11:F12',rows=0,cols=-3)  # 移动D11到F12构成的矩形格子
workbook.save('test.xlsx')

字母列号与数字列号之间的转换
核心代码

from openpyxl.utils import get_column_letter, column_index_from_string

# 根据列的数字返回字母
print(get_column_letter(2))  # B
# 根据字母返回列的数字
print(column_index_from_string('D'))  # 4

举个例子：

import os
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('2.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

# 根据列的数字返回字母
print(get_column_letter(2))  # B
# 根据字母返回列的数字
print(column_index_from_string('D'))  # 4

字体样式
查看字体样式

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：'+str(sheet))

cell = sheet['A1']
font = cell.font
print('当前单元格的字体样式是')
print(font.name, font.size, font.bold, font.italic, font.color)

'''
当前活动表是：
当前单元格的字体样式是
等线 11.0 False False 
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme'
'''

修改字体样式

openpyxl.styles.Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
其中，字体颜色中的color是RGB的16进制表示

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print(sheet)

cell = sheet['A1']
cell.font = openpyxl.styles.Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")

workbook.save('test.xlsx')

再者，可以使用for循环，修改多行多列的数据，在这里介绍了获取的方法

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print(sheet)

cell = sheet['A']
for i in cell:
    i.font = openpyxl.styles.Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")

workbook.save('test.xlsx')

设置对齐格式

Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
水平对齐：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，'right，‘general’
垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’

import os
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

cell = sheet['A1']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

workbook.save('test.xlsx')

当然，你仍旧可以调用for循环来实现对多行多列的操作

import os
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

cell = sheet['A']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
for i in cell:
    i.alignment = alignment
    
workbook.save('test.xlsx')

设置行高列宽

设置行列的宽高：
·row_dimensions[行编号].height = 行高
·column_dimensions[列编号].width = 列宽

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

# 设置第1行的高度
sheet.row_dimensions[1].height = 50
# 设置B列的卷度
sheet.column_dimensions['B'].width = 20

workbook.save('test.xlsx')

设置所有单元格
（显示的结果是设置所有，有数据的单元格的）

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import os

os.chdir(r'C:\Users\asuka\Desktop')

workbook = load_workbook('1.xlsx')
print(workbook.sheetnames)  # 打印所有的sheet表
ws = workbook[workbook.sheetnames[0]]  # 选中最左侧的sheet表

width = 2.0  # 设置宽度
height = width * (2.2862 / 0.3612)  # 设置高度

print("row:", ws.max_row, "column:", ws.max_column)  # 打印行数，列数
for i in range(1, ws.max_row + 1):
    ws.row_dimensions[i].height = height
for i in range(1, ws.max_column + 1):
    ws.column_dimensions[get_column_letter(i)].width = width

workbook.save('test.xlsx')

合并、拆分单元格

合并单元格有下面两种方法，需要注意的是，如果要合并的格子中有数据，即便python没有报错，Excel打开的时候也会报错。
merge_cells(待合并的格子编号)
merge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

# 方法1：
sheet.merge_cells('A12:B13')
# 方法2：
sheet.merge_cells(start_row=12, start_column=3, end_row=13, end_column=4)

# 加一个居中对齐
cell = sheet['A12']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

cell = sheet['C12']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

workbook.save('test.xlsx')

拆分单元格的方法同上
unmerge_cells(待合并的格子编号)
unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

sheet表
创建新的sheet(create_sheet)

create_sheet(“新的sheet名”)：创建一个新的sheet表

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

workbook.create_sheet('3号sheet')    # 创建新的sheet表
print(workbook.sheetnames)			 # 查看所有的sheet表
workbook.save('test.xlsx')

'''
当前活动表是：
['Sheet1', 'Sheet2', '3号sheet']
'''

修改sheet名字(title)

第11行，使用title修改sheet表的名字

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

sheet.title = '1号sheet'  # 修改sheet表
workbook.save('test.xlsx')

复制sheet表(copy_worksheet)

在“操作”>“修改单元格、excel另存为”中提到了另存为，其实复制sheet表就是一个另存为的过程，你要是在12行代码保存的时候使用第7行的文件名，那么复制的sheet表就保存到自己身上，内容跟copy.xlsx一样。

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

workbook.copy_worksheet(sheet)	# 复制sheet表
workbook.save('copy.xlsx')

删除sheet表(remove)

remove(“sheet名”)：删除某个sheet表
要删除某sheet表，需要激活这个sheet表，即：将其作为活动表（关于活动表的定义请看前面文章开头写的有）下面8~11行代码展示了原始活动表与手动更换活动表，第13行代码删掉活动表

import os
import openpyxl

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))
sheet = workbook['3号sheet']     # 手动切换到要删除的sheet表，一旦切换，这张表就是活动表
print('当前活动表是：' + str(sheet))

workbook.remove(sheet)  # 删除当前活动表
print(workbook.sheetnames)
workbook.save('test.xlsx')

'''
当前活动表是：
当前活动表是：
['Sheet1', 'Sheet2']
'''

操作多个Excel表

背景知识
numpy与pandas
NumPy是 Python 语言的一个扩展程序库，支持大量的维度数组与矩阵运算，此外也针对数组运算提供大量的数学函数库；pandas 是基于NumPy 的一种工具，该工具是为解决数据分析任务而创建的，我们需要利用Pandas进行Excel的合并

1.下面的代码生成了一个5行3列的包含15个字符的嵌套列表
（注意，第4行代码：15是等于35的，如果是15对应43，或者16对应5*3都会报错）
（注意，第5行代码，虽然5行3列是15个数据，但是可以指定数据从1开头，到16结束）

import numpy as np
import pandas as pd

xx = np.arange(15).reshape(5, 3)
yy = np.arange(1, 16).reshape(5, 3)

print(xx)
print(yy)

'''
[[ 0  1  2]
 [ 3  4  5]
 [ 6  7  8]
 [ 9 10 11]
 [12 13 14]]
[[ 1  2  3]
 [ 4  5  6]
 [ 7  8  9]
 [10 11 12]
 [13 14 15]]
'''

2.添加表头
使用pandas库的DataFrame来添加表头。关于打印的结果，把最左侧的一列去掉之后会发现结果很和谐，这是因为最左侧的一列代表行号。此时xx变量的类型是

import numpy as np
import pandas as pd

xx = np.arange(15).reshape(5, 3)
yy = np.arange(1, 16).reshape(5, 3)
xx = pd.DataFrame(xx, columns=["语文", "数学", "外语"])
yy = pd.DataFrame(yy, columns=["语文", "数学", "外语"])
print(xx)
print(yy)

'''
结果：
   语文  数学  外语
0   0   1   2
1   3   4   5
2   6   7   8
3   9  10  11
4  12  13  14
   语文  数学  外语
0   1   2   3
1   4   5   6
2   7   8   9
3  10  11  12
4  13  14  15
'''

xlsxwriter

xlsxwriter模块一般是和xlrd模块搭配使用的，
xlsxwriter：负责写入数据，
xlrd：负责读取数据。

1.创建一个工作簿

import xlsxwriter
import os

path = r"C:\Users\asuka\Desktop"
os.chdir(path)

# 这一步相当于创建了一个新的"工作簿"；
# "demo.xlsx"文件不存在，表示新建"工作簿"；
# "demo.xlsx"文件存在，表示新建"工作簿"覆盖原有的"工作簿"；
workbook = xlsxwriter.Workbook("demo.xlsx")

# close是将"工作簿"保存关闭，这一步必须有，否则创建的文件无法显示出来。
workbook.close()

2.创建sheet表

import xlsxwriter
import os

path = r"C:\Users\asuka\Desktop"
os.chdir(path)

workbook = xlsxwriter.Workbook("cc.xlsx")   # 创建一个名为cc.xlsx的文件
worksheet = workbook.add_worksheet("2018年销售量")  # 创建一个名为“2018年销售量”的sheet表

workbook.close()

3.写入数据

import xlsxwriter
import os

path = r"C:\Users\asuka\Desktop"
os.chdir(path)

# 创建一个名为【demo.xlsx】工作簿；
workbook = xlsxwriter.Workbook("demo.xlsx")

# 创建一个名为【2018年销售量】工作表；
worksheet = workbook.add_worksheet("2018年销售量")

# 使用write_row方法，为【2018年销售量】工作表，添加一个表头；
headings = ['产品', '销量', "单价"]
worksheet.write_row('A1', headings)
# 使用write方法，在【2018年销售量】工作表中插入一条数据；
# write语法格式：worksheet.write(行,列,数据)

data = ["苹果", 500, 8.9]
for i in range(len(headings)):
    worksheet.write(1, i, data[i])

workbook.close()