python操作excel笔记

#coding=utf-8
#######################################################
#filename: .py
#author:
#date:2019-01-08
#function：
#######################################################

import openpyxl
import os

wb = openpyxl.load_workbook('example.xlsx')
print(type(wb))
print(os.getcwd())
sheet = wb.get_sheet_names()
print(sheet)
sheet = wb.get_sheet_by_name('Sheet1')
print(sheet)
print(type(sheet))
print(sheet.title)
print(wb.get_active_sheet())
print(sheet.cell(row=1,column =2).value)
for i in range(1,8,2):
    print(i,sheet.cell(row=i,column=2).value)

#print(sheet.get_highest_row())
#print(sheet.get_highest_column()) 属性函数被替代
max_rows = sheet.max_row
print('max_rows:'+ str(max_rows))
print('max_colum:'+ str(sheet.max_column))

#12.3.4 列字母和数字之间的转换
from openpyxl.utils import get_column_letter,column_index_from_string
column_letter =  get_column_letter(2)
print(column_letter)
print(column_index_from_string('A'))
#print(get_column_letter(sheet.max_column))
#print(column_index_from_string('A'))

#12.3.5 从表中取得行和列
print(tuple(sheet['A1':'B10']))
for cells in sheet['A1':'B10']:
    for index in cells:
        print(index.coordinate,index.value)
    print('--- END OF ROW ---')

#12.6 更新一个电子表格
price_updates = {'apple':12,
                 'lemon':13.5,
                 'nuna':600
                 }

for rowNum in range(2,sheet.max_row+1,1):
    producename = sheet.cell(row=rowNum,column=3).value
    print('producename: '+str(producename))
    if producename in price_updates:
        sheet.cell(row=rowNum,column=4).value = price_updates[producename]
wb.save('example_01.xlsx')
print('Done.')

#12.7 设置单元格字体风格
from openpyxl.styles import Font,Color,PatternFill
sheet['A1'].font=(Font(size=24,italic=True))
sheet['A1'].fill = PatternFill(fill_type='solid',fgColor="0d5330")
wb.save('example_01.xlsx')
print('Font set Done.')