Python自动化与Excel

Python自动化与Excel

在日常工作中，excel的使用非常频繁，但是很多的excel表格数据格式混乱，excel处理大型数据时比较慢，这个是时候使用Python进行excel表格的处理非常的高效便捷。Python中的openpyxl模块可以很好的满足这些需求。在终端中使用pip install openpyxl进行模块的安装。

1. excel表格的读取

1.1 读取对应表格

from openpyxl import load_workbook # 加载模块
exl = load_workbook(filename = 'test.xlsx')
print(exl.sheetnames) #输出各sheet的名称，函数还有很多其他的方法可以查看

sheet = exl['work'] 

#如果excel表中只有一个sheet可以使用active读取
sheet = exl.active

print(sheet.dimensions) #输出excel表格内容所占据的范围

1.2 读取单元格的值

cell = sheet.cell(row = 1, column = 2) #读取指定行列的值

cell_1 = sheet['A1'] #读取指定坐标的值

print(cell_1.row, cell_1.column, cell.cordinate) #获取单元格对应的行列和坐标

1.3 获取多个单元格值

cells = sheet['A1:C8'] #获取指定坐标范围A1:C8区域的值

Row = sheet[1] #获取shhet表中第1行的值
Rows = sheet[1:2] #获取shhet表中第1到第2行的值

Column = sheet['A'] #获取第A列的值
Columns = sheet['A:C'] #获取第A到C列的值

#指定范围内的表格的值
#指定行范围内获取
for row in sheet.iter_rows(min_row = 1, max_row = 5,
                          min_col = 2, max_col = 6):
    print(row)
    for cell in row:
        print(cell.value)
#指定列范围内获取
for col in sheet.iter_cols(min_row = 1, max_row = 5,
                          min_col = 2, max_col = 6):
    print(col)
    for cell in col:
        print(cell.value)

2. excel表格的写入

2.1 写入单元格并保存

from openpyxl import load_workbook

exl = load_workbook(filename = 'test.xlsx')
sheet = exl.active
sheet['A1'] = 'hello world'       
#或者cell = sheet['A1'] 
#cell.value = 'hello world'
exl.save(filename = 'test.xlsx') #存入原Excel表中，若创建新文件则可命名为不同名称

2.2 写入行数据并保存

import xlwt  #注意模块的安装pip3 install xlwt
#写入一行数据

workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
sheet = workbook.add_sheet('My Worksheet')
# 写入excel
# 参数对应 行, 列, 值
sheet.write(1,0,label = 'this is test')
# 保存
workbook.save('new_test.xls')

#写入多行数据
exl=xlwt.Workbook(encoding='utf-8')
worksheet=exl.add_sheet('My Worksheet')

data = [['hello',22,'hi'],
		['hell',23,'h'],
		['he',25,'him']]
for i in range(len(data)):
    for j in range(len(data[i])):
        worksheet.write(i,j,data[i][j])
exl.save('test1.xls')

2.3 将公式写入单元格

sheet['A2'] = '=SUM(A1:D1)'
exl.save(filename='test.xlsx')

2.4 插入、删除和移动数据

#插入1列数据
sheet.insert_cols(idx=2) #idx=2第2列，第2列前插入一列
#插入多列数据
sheet.insert_cols(idx=2, amount=5) #在第2列前插入5列数据
#插入一行
sheet.insert_rows(idx=2)
#插入多行
sheet.insert_rows(idx=2, amount=5)
#删除多列数据
sheet.delete_cols(idx=5, amount=2) #在第5列前删除2列
#删除多行数据
sheet.delete_rows(idx=2, amount=5)
#移动数据
sheet.move_range('C5:F10', rows=2, cols=-3)  #向下和向右为正方向，向上及向左为负

2.5 sheet表操作

#创建新的sheet
from openpyxl import Workbook

workbook=Workbook()
sheet=workbook.active
sheet.title = 'test' #修改sheet的名称
workbook.save(filename='new_test.xlsx')
exl.create_sheet('new_sheet')

#复制已有的sheet
exl.copy_worksheet(sheet)

2.6 创建新的excel表格

from openpyxl import load_workbook

workbook = Workbook()
sheet = workbook.active
workbook.save(filename = 'new_test.xlsx')

3. excel样式调整

3.1 设置字体样式

设置字体样式可以采用Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1'] #指定单元格的字体样式设置
font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save(filename='new_test.xlsx')

workbook = Workbook()
sheet = workbook.active
cells = sheet[2]
font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF000000')
for cell in cells:
	cell.font = font
workbook.save(filename='new_test.xlsx')

3.2 设置对齐和单元格样式

水平对齐：distributed, justify, center, left, fill, centerContinuous, right, general

垂直对齐：bottom, distributed, justify, center, top

单元格边框样式： Side(style边线样式， color边线颜色) Border(左右上下边线)

边线样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

from openpyxl import Workbook
from openpyxl.styles import Font, Side, Border, GradientFill

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
side = Side(style='thin', color='FF000000')
#先定好side的格式
border = Border(left=side, right=side, top=side, bottom=side)
#代入边线中
cell.border = border
workbook.save(filename='new_test.xlsx')

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
pattern_fill = PatternFill(fill_type='solid', fgColor = '66ffcc')
cell.fill = pattern_fill
#单色填充
cell2 = sheet['A3']
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff', '000000'))
cell2.fill = gradient_fill
#渐变填充
workbook.save(filename='new_test.xlsx')

3.3 设置行高和列宽

from openpyxl import Workbook 

workbook = Workbook()
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20 
workbook.save(filename='new_test.xlsx')

3.4 合并、取消合并单元格

#合并单元格
sheet.merge_cells('A1:B2')
sheet.merge_cells(start_row=1, start_column=3,
				  end_row=2, end_column=4)
#取消合并单元格
sheet.unmerge_cells('A1:B2')
sheet.unmerge_cells(start_row=1, start_column=3,
				    end_row=2, end_column=4)