python之Excel操作

教你如何用python操作Excel

首先pip install openpyxl
pip install pillow
import openpyxl ##导入模块
path= r’path.xlsx’
workbook = openpyxl.load_workbook(path) ##加载工作项
sheet_name = workbook.get_sheet_names()
print(sheet_name) ##获得所有标签页
sheet = workbook[‘asax’] ##获取sheet对象
cp_sheet = workbook.copy_worksheet(sheet) ##复制sheet
cell = sheet[‘A1’] ##获取指定单元格

##访问多个单元格
cell_range = sheet[‘A1’ : ‘C1’]
for cells in cell_range:
for cell in cells:
print(cell)

row = sheet.iter_rows(min_row = 1,max_col = 3,max_row = 1) ##获取指定范围的行
for cell in row:
print(cell)

for cell in sheet.rows: ##获取所有行
print(cell)
sheet.columns:获取所有列

sheet = workbook[‘sheet1’] ##获取sheet1
workbook.remove(sheet) ##移除sheet

val = sheet[[A1’].value
print(val) ## 获得sheet[‘A1’]的值

new_val = sheet[‘A1’].value = ‘222’
print(new_val) ##重新赋值

写入Excel

import openpyxl
path = r’path.xlsx’
workbook = openpyxl.Workbook() ##创建一个工作本
sheet = workbook.create_sheet(‘assz’) ##创建一个sheet
sheet[‘[A1’] = “11111111” ##写入文本形式
workbook.save(path)
sheet[‘A2’] = datetime.datetime(2020) ##写入数字形式
sheet[‘A3’] = ‘=sum(1,1)’ ##写入公式
sheet.unmerge_cell(‘A2:D2’) ##取消合并单元格

插入图片

img = Image(r’path.jpg’) ##设置图像
sheet[‘A1’] = “angle” ##设置图像单元格说明
sheet.add_image(img,‘A1’) ##插入图片

sheet.column_dimensions.group(‘A’,‘B’,hidden= True) ##隐藏列A-B
sheet.row_dimensions.group(1,5,hidden = True) ##隐藏行1-5
sheet.row_dimensions[1].height = 50 ##设置行高为50
sheet.column_dimensions[‘A’].width = 30 ##设置列宽为30

自动换行

import openpyxl
from openpyxl.styles import Alignment
worksheet.cell(row = 1,column=1),alignment = Alignment(wrapText = True)