python & excel 的自动化(datawhale组队学习task2)

Freshman小白

于 2022-07-16 17:03:13 发布

阅读量974

点赞数 2

分类专栏： datawahle组队学习 python 文章标签：大数据 python 自动化学习

本文链接：https://blog.csdn.net/weixin_67016521/article/details/125783104

版权

python 同时被 2 个专栏收录

15 篇文章 1 订阅

订阅专栏

datawahle组队学习

6 篇文章 0 订阅

订阅专栏

openpyxl模块

（需要另外安装）可以读/写 .xlsx /.xlsm /.xltx /.xltm 的格式文件，但是不支持去读 /.xls 格式

excel的读取：

excel的基本信息:

🌟load_workbook(path)

import openpyxl

wb = openpyxl.load_workbook('./OpenPyXl_test/用户行为偏好.xlsx')
print(wb)
'''查看工作簿属性值'''
print(wb.sheetnames)
'''查看工作簿的表单'''
print(wb.active.title)
'''展示打开时出现的工作表(活动表)'''

<openpyxl.workbook.workbook.Workbook object at 0x1158eb7c0>
['订单时长分布', 'Sheet3']
订单时长分布

读取sheet的内容：

sheet = wb.get_sheet_by_name('Sheet3')
print('表格名字:{}'.format(sheet.title))
print('表格大小:{}'.format(sheet.dimensions))

‘’当我使用get_sheet_by_name()成功返回结果同时跳出了警告

DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).‘’

所以按照建议我使用了索引方式

wb['sheetname']

sheet = wb['Sheet3']
print('表格名字:{}'.format(sheet.title))
print('表格大小:{}'.format(sheet.dimensions))



表格名字:Sheet3
表格大小:A1:I17

获取单元格内容（索引）：

value表示单元格值;row表示行;column表示列;coordinate表示坐标

sheet = wb['订单时长分布']
print('b1单元格的值；{}'.format(sheet['B1'].value)) #单元格缩影字母大小写均可
print('单元格所在行:{}  所在列：{}  坐标：{}' .format(sheet['B1'].row, sheet['b1'].column, sheet['b1'].coordinate))



b1单元格的值；日期
单元格所在行:1  所在列：2  坐标：B1

获取单元格内容（ cell(row= , column= ) ）：

'''打印第二列的十行'''
for i in range(1, 11):
    print(sheet.cell(row=i, column=2).value)



日期
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00

表格的最大行、列：

sheet.max_row                     sheet.max_column

读取多个单元格（索引）：

cells = sheet['A1:C8']
'''索引可以直接选定一个范围'''
for rows in cells:
    for cell in rows:
        print(cell.value, end=" |")
    print("\n")

练习：找表单中的空格子

import openpyxl
wb = openpyxl.load_workbook('./OpenPyXl_test/用户行为偏好.xlsx')
sheet = wb['Sheet3']
cells = sheet3[sheet3.dimensions]
for rows in cells:
    for cell in rows:
        if cell.value == None:
            print('空格子有：{}'.format(cell.coordinate))

excel的写入：

print('修改前：{}'.format(sheet3['a1'].value))
sheet3['a1'].value = 'hello world'
print('修改后:{}'.format(sheet3['a1'].value))
wb.save(filename='./OpenPyXL_test/用户行为偏好_1.xlsx')
'''保存到一个新的excel，
若filename为原来的excel则会直接修改'''

修改前：1
修改后:hello world

创建新的表格写入数据并保存:

creat_sheet（title= , index= ）创建新的表格；

new_wb = openpyxl.Workbook()
print('初始表格：{}'.format(new_wb.sheetnames))
#添加表格 index可以确定sheet创建的位置
new_wb.create_sheet(title='new_sheet', index=0)
print('添加后：{}'.format(new_wb.sheetnames))
#为新表格赋值
new_wb['new_sheet']['A1'].value = 'hello'
print(new_wb['new_sheet']['a1'].value)
new_wb.save(newpath)

初始表格：['Sheet']
添加后：['new_sheet', 'Sheet']
hello

注： 如sheet['D15'] = '=SUM(D2:D14)'可以直接给单元格赋值公式,,需要使用xlwings使公式生效

# 使用 xlwings 打开 excel 文件然后保存 使写入的 公式生效
import xlwings as xw 
# 打开工作簿
app = xw.App(visible=False, add_book=False)
wb = app.books.open('用户行为偏好_1.xlsx')  
wb.save()
# 关闭工作簿
wb.close()
app.quit()

insert_cols(idx= )插入列

insert_rows(idx= )插入行

delete_rows(idx= )删除行

delete_cols(idx= )删除列

move_range('单元格/一个范围或一格'，rows=1,cols=-1)
移动单元格
cols，rows参数：当数字为正即向下或向右，为负即为向上或向左

excel样式的设置：

cell.font 返回某单元格的字体信息

cell.font = Font(name=, size= , bold= ,italic= ,color = )可以设置单元格字体样式

openpyxl.style.PatternFill(fill_type='',fgColor="")设置背景色

openpyxl.style.alignment(horizontal='center', vertical='center')设置文字对齐

side（style=None, color=None, border_style=None）设置边线样式

border（）设置边框

例：

side = Side(border_style='double', color='FF000000')
border = Border(left=side, right=side, top=side, bottom=side, diagonal=side, diagonalDown=True, diagonalUp=True)
for cell in cells:
    cell.border = border
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

merge_cells()合并单元格

unmerge_cells()接触合并

xlwings模块

本人使用的是macos，发现无论是pycharm还是idle在使用xlwingsa.App()的时候都会出现

aem.aemsend.EventError: Command failed: The user has declined permission. (-1743)

目前找到的解决方案只有使用终端运行python然后会跳出允许修改excel的提示，允许后可以运行，但是对于pycharm和idle都不知道如何给他们赋予权限。

求助！

创建工作表：

import xlwings as xw

#创建一个app应用，打开Excel程序
app = xw.App(visible=True, add_book=False)

#新建一个工作簿
wb = app.books.add()
#新建sheet 起名为newsheet
sheet = wb.sheets.add('newsheet')
#在新建的sheet表中A1位置插入一个值：Datawhale
sheet.range('A1').value = 'Datawhale'
#保存新建的工作簿，并起一个名字
wb.save('./XLWings_test/xlwings_wb.xlsx')
wb.close()
app.quit()

打开原有的工作簿：

'''上述代码中'''
wb.books.add()
'''是添加表'''

wb.books.open(path)
'''则为打开已经存在的工作簿'''

工作表的更多操作：

wb.sheet.add('name')添加表

wn.sheet('name').delete()删除表

wb.sheet.count返回当前工作表数量

写入数据：

sht1 = wb.sheet.add('newsheet')
# 在工作表中指定位置插入数据
sht1.range('B1').value = 'Datawhale'

# 在工作表指定位置插入多个数据 默认是横向插入
sht1.range('B2').value = ['DATAWHALE', 'FOR', 'THE', 'LEARNER']

# 在工作表指定位置竖向插入多个数据
# 设置 options(transpose=True)表示转置的意思
sht1.range('B3').options(transpose=True).value = [1, 2, 3, 4]

# 在工作表指定位置开始插入多行数据
sht1.range('B7').value = [['a', 'b'], ['c', 'd']]

# 在工作表指定位置开始插入多列数据
sht1.range('B9').options(transpose=True).value = [['a', 'b'], ['c', 'd']]

'''单元格写入公式'''
sht1.range('F2').formula = '=sum(B2:E2)'

'''删除单元格的值'''
sht1.range('B1').clear()

读取单元格的值：

sht1.range('b1').value返回b1一个单元格的值

sht1.range('b1:c5').value返回b1到c5区域的值（按行输出）

添加options(transpose=True)可改为按列输出

单元格样式设置:

'''合并单元格'''
sht1.range('B3:C3').api.merge()
'''添加超链接'''
sht1.range('C2').add_hyperlink(address='https://datawhale.club',
                   text_to_display='DATAWHALE 官网',
                   screen_tip='点击查看 DATAWHALE 官网 ')

'''设置单元格颜色'''
sht1.range('B1').color = (93,199,221)
'''设置单元格文本颜色'''
sht1.range('B1').api.font_object.color.set((255,0,0))
'''设置字体样式'''
sht1.range('B3').api.font_object.font_style.set('加粗')
'''设置字体大小'''
sht1.range('B3').api.font_object.font_size.set(20)

完