python使用openpyxl庫(kù)讀寫(xiě)Excel表格的方法（增刪改查操作）

pip install openpyxl

import openpyxl

book = openpyxl.Workbook()
book.save(u'我的表格.xlsx')

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
# ... 
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book.create_sheet('我的Sheet')
book.save(u'我的表格.xlsx')
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
if None != book[u'我的Sheet']:
    print('我的Sheet 存在')
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
for sheet_name in book.sheetnames:
    print(sheet_name)
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
sheet.cell(1,1).value = 'name'
sheet.cell(2,1).value = '姓名'
sheet.cell(3,1).value = '林新發(fā)'
book.save(u'我的表格.xlsx')
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
cell = sheet.cell(3,1)
# 獲取單元格的值
print('value:%s'%cell.value)
# 獲取行號(hào)、列號(hào)
print('row: %d, col: %d'%(cell.row, cell.column))
# 獲取列名
print('column_letter: %s'%cell.column_letter)
# 單元格的坐標(biāo)
print('coordinate: %s'%cell.coordinate)
# 單元格數(shù)據(jù)格式，n: 數(shù)字，s:字符串，d: 日期
print('data_type: %s'%cell.data_type)
# 單元格編碼格式
print('encoding: %s'%cell.encoding)
# 單元格樣式
print('style: %s'%cell.style)
# 單元格批注
print('comment: %s'%cell.comment)
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
# 一行一行遍歷
for one_row in sheet.rows:
    for cell in one_row:
        print(cell, cell.value)
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
# 一列一列遍歷
for one_col in sheet.columns:
    for cell in one_col:
        print(cell, cell.value)
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
print('max_row: %d, max_column: %d'%(sheet.max_row, sheet.max_column))
book.close()

import openpyxl

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
# 刪除第5行
sheet.delete_rows(5)
# 刪除第4列
sheet.delete_cols(4)
book.save(u'我的表格.xlsx')
book.close()

import openpyxl
from openpyxl.styles import Font

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
cell = sheet.cell(4,1)
cell.font = Font(name='微軟雅黑',size=18,color='00FFCC99',b=True,i=False)
book.save(u'我的表格.xlsx')
book.close()

import openpyxl
from openpyxl.styles import PatternFill

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
cell = sheet.cell(4,2)
# 設(shè)置填充色
cell.fill = PatternFill('solid',fgColor='FFBB00')
book.save(u'我的表格.xlsx')
book.close()

import openpyxl
from openpyxl.utils import get_column_letter

book = openpyxl.load_workbook(u'我的表格.xlsx')
sheet = book['Sheet']
# 取第4行
row_4 = sheet.row_dimensions[4]
# 設(shè)置行高
row_4.height = 15
# 取第2列
col_2 = sheet.column_dimensions[get_column_letter(2)]
# 設(shè)置列寬
col_2.width = 40
book.save(u'我的表格.xlsx')
book.close()

from openpyxl.utils import column_index_from_string
print(column_index_from_string('B'))
# 輸出2