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

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

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

操作

创建新的excel

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

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路径workbook = openpyxl.Workbook()sheet = workbook.activesheet.title = ‘1号sheet’workbook.save(‘1.xlsx’)

修改单元格、excel另存为

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

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路径workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一个workbook数据类型的值sheet = workbook.active # 获取活动表sheet[‘A1’] = ‘name’workbook.save(‘test.xlsx’)

添加数据

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

import osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringpath = r”C:UsersasukaDesktop”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 osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路径workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一个workbook数据类型的值sheet = workbook.active # 获取活动表print(‘当前活动表是：’+str(sheet))cell = sheet[‘A1’]font = cell.fontprint(‘当前单元格的字体样式是’)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 osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 osimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 = alignmentworkbook.save(‘test.xlsx’)

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

import osimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 = 20workbook.save(‘test.xlsx’)

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

from openpyxl import load_workbookfrom openpyxl.utils import get_column_letterimport osos.chdir(r’C:UsersasukaDesktop’)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 = heightfor i in range(1, ws.max_column + 1): ws.column_dimensions[get_column_letter(i)].width = widthworkbook.save(‘test.xlsx’)

合并、拆分单元格

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

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”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 = alignmentcell = sheet[‘C12’]alignment = openpyxl.styles.Alignment(horizontal=”center”, vertical=”center”, text_rotation=0, wrap_text=True)cell.alignment = alignmentworkbook.save(‘test.xlsx’)

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

sheet表创建新的sheet(create_sheet)

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

import osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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 osimport openpyxlpath = r”C:UsersasukaDesktop”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与pandasNumPy是 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 npimport pandas as pdxx = 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 npimport pandas as pdxx = 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 21 3 4 52 6 7 83 9 10 114 12 13 14 语文 数学 外语0 1 2 31 4 5 62 7 8 93 10 11 124 13 14 15”’

xlsxwriter

xlsxwriter模块一般是和xlrd模块搭配使用的，xlsxwriter：负责写入数据，xlrd：负责读取数据。1.创建一个工作簿

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”os.chdir(path)# 这一步相当于创建了一个新的”工作簿”；# “demo.xlsx”文件不存在，表示新建”工作簿”；# “demo.xlsx”文件存在，表示新建”工作簿”覆盖原有的”工作簿”；workbook = xlsxwriter.Workbook(“demo.xlsx”)# close是将”工作簿”保存关闭，这一步必须有，否则创建的文件无法显示出来。workbook.close()

2.创建sheet表

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”os.chdir(path)workbook = xlsxwriter.Workbook(“cc.xlsx”) # 创建一个名为cc.xlsx的文件worksheet = workbook.add_worksheet(“2018年销售量”) # 创建一个名为“2018年销售量”的sheet表workbook.close()

3.写入数据

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”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()