python openpyxl的使用

1.Xlsx创建并写入

#encoding=utf-8

from openpyxl import Workbook
import os,sys
import time
exepath=r'C:\Users\613\Desktop\logfiles'
os.chdir(exepath)
book =Workbook()
sheet = book.active
sheet['A1']=23
sheet['A2']=43

now =time.strftime('%x')
sheet['A3']=now

book.save('excelstudy.xlsx')

 #append，直接在表格后面添加数据

rows = (
    (82, 1, 57),
    (82, 38, 12),
    (23, 52, 78),
    (52, 21, 98),
)
for row in rows:
    sheet.append(row)

book =openpyxl.load_workbook('excelstudy.xlsx')
sheet = book.active
a1 = sheet['A1']
a2 = sheet['A2']
a3 = sheet.cell(row=3, column=1)

print(a1.value)
print(a2.value)
print(a3.value)

读出A4：C7单元格的数据，并使用format()使得读出的在控制台上整洁地输出数据book =openpyxl.load_workbook('excelstudy.xlsx')
sheet = book.active
cells=sheet['A4':'C7']
for c1,c2,c3 in cells:
    print("{0:8}{1:8}{2:8}".format(c1.value,c2.value,c3.value))

iter_rows()方法将工作表中的单元格返回为行，下面示例逐行遍历数据

cells=sheet['A4':'C7']
for row in sheet.iter_rows(min_row=4,min_col=1,max_row=6,max_col=3):
    for cell in row:
        print(cell.value,end=" ")
    print()

iter_cols()方法将工作表中的单元格作为列返回

for row in sheet.iter_cols(min_row=4,min_col=1,max_row=6,max_col=3):
    for cell in row:
        print(cell.value,end=' ')
    print()

处理数据，打印出数学统计信息

for row in rows:
    for cell in row:
        values.append(cell.value)

print("Number of values: {0}".format(len(values)))
print("Sum of values: {0}".format(sum(values)))
print("Minimum value: {0}".format(min(values)))
print("Maximum value: {0}".format(max(values)))
print("Mean: {0}".format(stats.mean(values)))
print("Median: {0}".format(stats.median(values)))
print("Standard deviation: {0}".format(stats.stdev(values)))
print("Variance: {0}".format(stats.variance(values)))

auto_filter属性，该属性设置过滤条件和排序条件

os.chdir(exepath)
book =openpyxl.load_workbook('excelstudy.xlsx')
sheet = book.active

data = [
    ['Item', 'Colour'],
    ['Android', 'brown'],
    ['Python', 'black'],
    ['Excel', 'white'],
    ['C#', 'brown'],
    ['PHP', 'gold'],
    ['CSS', 'brown'],
    ['Java', 'white'],]

for r in data:
    sheet.append(r)
sheet.auto_filter.ref='A1:b8'
sheet.auto_filter.add_filter_column(1,['brown','white'])
sheet.auto_filter.add_sort_condition('B2:B8')
book.save('excelstudy.xlsx')

dimensions表示sheet的维度
book =openpyxl.load_workbook('excelstudy.xlsx')
sheet = book.active

sheet['A3'] = 12
sheet['B3'] = 23


print(sheet.dimensions)
print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))
print("Minimum column: {0}".format(sheet.min_column))
print("Maximum column: {0}".format(sheet.max_column))

for c1, c2 in sheet[sheet.dimensions]:
    print(c1.value, c2.value)

book.save('excelstudy.xlsx')

book =openpyxl.load_workbook('excelstudy.xlsx')
book.create_sheet("May",0)#创建名字叫May的sheet
print(book.sheetnames)#查看所有sheet名字
book["January"]#获取名为‘January' 的sheetbook["January"].sheet_properties.tabColor='007722'#修改sheet名字为January的颜色

合并单元格并居中

水平对齐:“distributed”,“justify”,“center”,“left”,“fill”,“centerContinuous”,“right”,“general”

垂直对齐:“bottom”,“distributed”,“justify”,“center”,“top”

merge_cells(待合并的格子编号)

merage_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)

unmerge_cells(待取消合并格子编号)

unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结

os.chdir(exepath)
book =openpyxl.load_workbook('excelstudy.xlsx')
sheet = book.active
sheet.merge_cells('A1:B2')
#merge_cells(start_row=4,start_column=4,end_row=5,end_column=5)
cell=sheet.cell(row=2,column=1)
A1_cell=sheet['A1']
A1_cell.value='Moon Day'
A1_cell.alignment=Alignment(horizontal='center',vertical='center'，text_rotation=45,wrap_text=True)

冻结窗口freeze_pans

B1=sheet['C3']
B1.value='56'
sheet.freeze_pans='C3'

Font(name=“字体名”,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)

cell=sheet.cell(row=7,column=2)
cell.value='=SUM(A1:B6)'
print(cell.value)
font =Font(name='隶书',size=12,bold=True,italic=True,color='59a868')
cell.font=font
book.save('excelstudy.xlsx')

获取表格中字体样式：

cell=sheet['A1']
font =cell.font
print(font.name,font.size,font.bold,font.italic)

# 设置边框样式
# Side(style=边线样式,color=边线颜色)
# Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
#边线样式:“double”,“dotted”,“hair”,“dashed”,“dashDot”,“thin”,“medium”,“thick”

cell=sheet['B8']
side_left=Side(style='thin',color='123241')
side_right=Side(style='dotted',color='EE3123')
border=Border(left=side_left,right=side_right,top=side_left,bottom=side_right)
cell.border=border

# PatternFill(fill_type=填充样式,fgColor=填充颜色)
# GradientFill(stop=(渐变颜色1,渐变颜色2,…))

cell_1=sheet["A7"]
cell_2=sheet["A8"]
pattern_fill=PatternFill(fill_type='solid',fgColor='6E8312')
grandient_fill=GradientFill(stop=('FFFFFF','3E7AAB','1A1A1A'))
cell_1.fill=pattern_fill
cell_2.fill=grandient_fill
book.save('excelstudy.xlsx')

# row_dimensions[行编号].height = 行高
# column_dimensions[列编号].width = 列宽

sheet.row_dimensions[6].height = 50
sheet.column_dimensions["A"].width = 20