python操作excel之openpyxl库——代码资料整理汇总
**
import openpyxl
from openpyxl import Workbook
import datetime
import dir
dir.dir()
import openpyxl
workbook = openpyxl.load_workbook('data.xlsx')
worksheet = workbook.get_sheet_by_name('Sheet1')
row1=[row.value for row in list(worksheet.rows)[0]]
print('第1行值:%s'%(row1))
col2=[col.value for col in list(worksheet.columns)[1]]
print('第2列值',col2)
cell_1_2=worksheet.cell(row=1,column=2).value
print('第1行第2列值',cell_1_2)
max_row=worksheet.max_row
读写数据
import dir
dir.dir()
import openpyxl
workbook = openpyxl.load_workbook('data.xlsx')
worksheet = workbook.get_sheet_by_name('Sheet1')
from openpyxl import Workbook
wb = Workbook()
ws=wb.active
for i in range(max_row):
rowi=[row.value for row in list(worksheet.rows)[i]]
for j in range(max_col):
aij=rowi[j]
ws.cell(row=i+1, column=j+1,value=aij)
row6=[i.value for i in list(ws.rows)[5]]
column4=[i.value for i in list(ws.columns)[3]]
row6
wb.save('hzx1.xlsx')
新建、读取、写入(openpyxl)
import openpyxl
from openpyxl import Workbook
import datetime
import dir
dir.dir()
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
ws1 = wb.create_sheet("Mysheet")
ws2 = wb.create_sheet("Mysheet", )
ws4=wb.create_sheet('New Title')
print(wb.sheetnames)
ws3 = wb["New Title”]
ws4 = wb.get_sheet_by_name("New Title")
for sheet in wb:
print(sheet.title)
c = ws['A4']
d = ws.cell(row=4, column=2, value=10)
for i in range(1,101):
for j in range(1,101):
ws.cell(row=i, column=j,value=f'{i}{j}')
wb.save('hzx.xlsx')
根据
max_row=worksheet.max_row
max_col=worksheet.max_column
print(max_row,max_col)
for i in range(max_row):
rowi=[row.value for row in list(worksheet.rows)[i]]
for j in range(max_col):
aij=rowi[j]
ws.cell(row=i+1, column=j+1,value=aij)
From openpyxl import Workbook
wb = Workbook()
ws=wb.active()
row6=[i.value for i in list(ws.rows)[5]]
column4=[i.value for i in list(ws.columns)[3]]
cell_range = ws['A1':'C2']
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
ws = wb.active
tuple(ws.rows)
tuple(ws.columns)
8.保存数据
wb.save('文件名称.xlsx’)
改变sheet标签按钮颜色
ws.sheet_properties.tabColor = "1072BA"
print(sheet.max_row)
print(sheet.max_column)
import openpyxl
import d
d.d()
wb=openpyxl.Workbook()
wb=openpyxl.load_workbook('hzx.xlsx')
sheet=wb.worksheets[0]
for row in sheet.iter_rows():
for cell in row:
print(cell.coordinate, cell.value)
sheet['A1'].value='1111111'
sheet.cell(1,1).value='1111111'
sheet.cell(1,1,'111111')
wb.create_sheet(index=0,title='小猪')
wb.create_sheet(index=1,title='佩奇')
for row in sheet.iter_rows(min_row=1,max_col=3,max_row=3):
print(row)
for row in ws1.rows:
for cell in row:
print(cell.value,end=“\t”)
(4)根据数字得到字母,根据字母得到数字
from openpyxl.utils import get_column_letter,column_index_from_string
print(get_column_letter(2))
print(column_index_from_string('D'))
删除工作表
wb.remove(sheet)
del wb[sheet]
(6)矩阵置换
rows = [
['Number', 'data1', 'data2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10]]
list(zip(*rows))
输出如下格式:
[('Number', 2, 3, 4, 5, 6, 7),
('data1', 40, 40, 50, 30, 25, 50),
('data2', 30, 25, 30, 10, 5, 10)]
10.设置单元格风格
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
font = Font(name='微软雅黑', size=10, bold=False, italic=False, vertAlign=None,
underline='none', strike=False, color='FF000000')
sheet['A1'].font=font
sheet.cell(1,1).font=font
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
sheet['A1'].font = bold_itatic_24_font
fill = PatternFill(fill_type='darkUp',start_color='FFFF00',end_color='FF0000')
sheet.cell(1,1).fill=fill
border = Border(left=Side(border_style='dashDotDot',color='9932CC'),
right=Side(border_style='dashDotDot',color='121212'),
top=Side(border_style='dashDotDot',color='8B0A50'),
bottom=Side(border_style='dashDotDot',color='B3EE3A'),)
sheet.cell(5,4).border=border
alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
indent=0)
sheet.cell(5,3).alignment=alignment
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
sheet.cell(6,5).value='=HYPERLINK("%s","%s")' % ("https://www.baidu.com","百度一下")
sheet.cell(6,3).value='=HYPERLINK("%s","%s")' % ("#A1", "跳转到A1")
sheet['F1']='=SUM(A1:E1)'
sheet.merge_cells('A1:E1')
sheet.unmerge_cells('A1:E1')
(4)设置行高和列宽
sheet.row_dimensions[2].height = 40
sheet.column_dimensions['C'].width = 30