第17天—python辦公自動化—讀寫Excel檔案

Python操作Excel

python讀寫excel的方式有很多，不同的模塊在讀寫的方法上稍有區別：

• 用xlrd和xlwt進行excel讀寫；

• 用openpyxl進行excel讀寫；

Python操作Excel需要三方庫的支持，如果要兼容Excel 2007以前的版本，也就是xls格式的Excel檔案，可

以使用三方庫xlrd和xlwt，前者用于讀Excel檔案，后者用于寫Excel檔案，如果使用較新版本的Excel，即

操作xlsx格式的Excel檔案，也可以使用openpyxl庫，

進入中斷輸入下面的命令，安裝所需三方庫

pip install xlwt xlrd openpyxl

使用xlrd讀取Excel檔案

import xlrd

# 作業簿 ---> 一個Excel檔案 ---> Book
wb = xlrd.open_workbook('resources/阿里巴巴2020年股票資料.xls')
# 獲取作業表名字
print(wb.sheet_names())
# 獲取指定作業表 ---> Worksheet
# sheet = wb.sheet_by_name('sheet1')
sheet = wb.sheet_by_index(0)
# 獲取作業表的行數和列數
print(sheet.nrows, sheet.ncols)
# 獲取指定行 ---> Row
print(sheet.row(0))
print(sheet.row_slice(0, start_colx=0, end_colx=3))
# 獲取指定列 ---> Column
# print(sheet.row(4))
print(sheet.col_slice(4, start_rowx=1, end_rowx=11))
# 獲取單元格的資料
# print(sheet.row(0)[0].value)
print(sheet.cell(2, 2).value)
# 遍歷整個表單
print(f'交易日期\t\t\t最高價\t\t最低價\t\t開盤價\t\t收盤價\t\t成交量\t\t調整收盤價')
for row in range(1, sheet.nrows):
    for col in range(sheet.ncols):
        value = sheet.cell(row, col).value
        if col == 0:
            curr_date = xlrd.xldate_as_datetime(value, 0)
            print(curr_date.strftime('%Y年%m月%d日'), end='\t')
        elif col == 5:
            print(f'{int(value):<10d}', end='\t')
        else:
            print(f'{value:.4f}', end='\t')
    print()

想知道更多關于xlrd模塊的知識，可以閱讀它的xlrd官方檔案，

Python中的時間日期型別

from datetime import datetime

date1 = datetime(1990, 5, 3)
print(date1)

date2 = datetime(1999, 12, 9, 1, 1, 1)
print(date2)

date3 = datetime.now()
print(date3)

delta = date3 - date1
print(delta.days, delta.seconds)

print(date2.strftime('%Y年%m月%d日 %H時%M分%S秒'))
print(date2.strftime('%Y-%m-%d %H:%M:%S:'))

使用openpyxl讀取Excel檔案

import datetime

import openpyxl

# 加載一個作業簿 ---> Workbook
wb = openpyxl.load_workbook('resources/阿里巴巴2020年股票資料.xlsx')
# 獲取作業表名字
print(wb.sheetnames)
# 獲取指定作業表
sheet = wb.worksheets[0]
print(type(sheet))
# 獲取作業表的行數和列數
print(sheet.dimensions)
print(sheet.max_row, sheet.max_column)
print(sheet.cell(3, 3).value)
print(sheet['C3'].value)
# 回圈遍歷所有的單元格
for row_ch in range(1, 255):
    for col_ch in 'ABCDEFG':
        value = sheet[f'{col_ch}{row_ch}'].value
        if type(value) == datetime.datetime:
            print(value.strftime('%Y年%m月%d日'), end='\t')
        elif type(value) == int:
            print(f'{value:<10d}', end='\t')
        elif type(value) == float:
            print(f'{value:.4f}', end='\t')
        else:
            print(value, end='\t\t')
    print()

# 回圈遍歷所有的單元格方法二：
for row in range(2, sheet.max_row + 1):
    for col in range(1, sheet.max_column + 1):
        # cell方法的行和列索引都是從1開始的，注意! !!
        value = sheet.cell(row, col).value
        if col == 1:
            print(value.strftime('%Y年%m月%d日'), end='\t')
        elif col == 6:
            print(f'{value:<10d}', end='\t')
        else:
            print(f'{value:.4f}', end='\t')
    print()

# 回圈遍歷所有的單元格方法三：
for row in sheet.iter_rows(min_row=2):
    for cell in row:
        value = cell.value
        if type(value) == datetime.datetime:
            print(value.strftime('%Y年%m月%d日'), end='\t')
        elif type(value) == int:
            print(f'{value:<10d}', end='\t')
        else:
            print(f'{value:.4f}', end='\t')
    print()

使用xlwt寫Excel檔案

import random

import xlwt

# 第一步：創建作業簿（Workbook）
wb = xlwt.Workbook()

# 第二步：添加作業表（Worksheet）
sheet = wb.add_sheet('期末成績')  # type: xlwt.Worksheet

# 第三步：向單元格寫入資料（）
header_style = xlwt.XFStyle()
header_pattern = xlwt.Pattern()
header_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 0 - 黑色、1 - 白色、2 - 紅色、3 - 綠色、4 - 藍色、5 - 黃色、6 - 粉色、7 - 青色
# pattern.pattern_fore_colour = 5
header_pattern.pattern_fore_colour = xlwt.Style.colour_map['aqua']
header_style.pattern = header_pattern
header_font = xlwt.Font()
# 設定字體大小（20是基準單位，18表示18px）
header_font.height = 20 * 18
# 是否使用粗體
header_font.bold = True
# 是否使用斜體
# font.italic = False
header_style.font = header_font
header_alignment = xlwt.Alignment()
# 垂直方向的對齊方式
header_alignment.vert = xlwt.Alignment.VERT_CENTER
# 水平方向的對齊方式
header_alignment.horz = xlwt.Alignment.HORZ_CENTER
header_style.alignment = header_alignment

titles = ['姓名', '語文', '數學', '英語']
for col_index, item in enumerate(titles):
	# 設定單元格的資料和樣式
    sheet.write(0, col_index, item, header_style)

# Todo:寫入5個學生3門課程的成績，成績用40-108的亂數表示
names = ['A', 'B', 'C', 'D', 'E']
for row_index, name in enumerate(names):
    sheet.write(row_index + 1, 0, name)
    for col in range(1, 4):
        sheet.write(row_index + 1, col, random.randrange(50, 101))

# 第四步：保存作業簿
wb.save('resources/成績表.xls')

給excel表添加其他資料

import xlrd
import xlwt
# 使用三方庫xlutils
from xlutils.copy import copy

wb1 = xlrd.open_workbook('resources/成績表.xls')
wb2 = copy(wb1)  # type: xlwt.Workbook
sheet = wb2.get_sheet(0)
sheet.write(0, 4, '平均分')
for row_index in range(1, 6):
    sheet.write(row_index, 4, xlwt.Formula(f'average(B{row_index + 1}:D{row_index + 1})'))
wb2.save('resources/成績表.xls')

想知道更多關于xlwtt模塊的知識，可以閱讀它的xlwt官方檔案，

使用openpyxl寫Excel檔案

import random

import openpyxl

# 第一步：創建作業簿（Workbook）
wb = openpyxl.Workbook()

# 第二步：添加作業表（Worksheet）
# sheet = wb.create_sheet('期末成績')
sheet = wb.active
sheet.title = '期末成績'

# 第三步：向單元格寫入資料

titles = ['姓名', '語文', '數學', '英語']
for col_index, item in enumerate(titles):
    sheet.cell(1, col_index + 1, item)

# Todo:寫入5個學生3門課程的成績，成績用40-108的亂數表示
names = ['A', 'B', 'C', 'D', 'E']
for row_index, name in enumerate(names):
    sheet.cell(row_index + 2, 1, name)
    for col in range(2, 5):
        sheet.cell(row_index + 2, col, random.randrange(50, 101))

# 第四步：保存作業簿
wb.save('resources/成績表.xlsx')

給excel表添加其他資料

import openpyxl
from openpyxl.styles import Font, Alignment

# 居中
alignment = Alignment(horizontal='center', vertical='center')

wb = openpyxl.load_workbook('resources/成績表.xlsx')
sheet = wb.worksheets[0]

sheet['E1'] = '平均分'
for i in range(2, 7):
    sheet[f'E{i}'] = f'=average(B{i}:D{i})'
    sheet.cell(i, 5).font = Font(size=12, bold=True, color='ff1493')
    sheet.cell(i, 5).font = alignment

wb.save('resources/成績表.xlsx')

想了解更多openpyxl模塊的知識，可以閱讀它的openpyxl官方檔案，

excel檔案匯總

將三個表的資料內容合并到一個Excel檔案中

其中一個表資料如下：

import openpyxl

summary_wb = openpyxl.Workbook()
summary_sheet = summary_wb.active
summary_sheet.title = '匯總'
summary_sheet.append(('購藥時間', '社保卡號', '商品編碼', '商品名稱', '銷售數量', '應收金額', '實收金額'))

names = ('高新', '新津', '犀浦')
for name in names:
    workbook = openpyxl.load_workbook(f'resources/小寶劍大藥房（{name}店）2018年銷售資料.xlsx')
    sheet = workbook.worksheets[0]
    # 通過指定單元格區域獲取到對應的行（嵌套元組）
    rows = sheet[f'A3:G{sheet.max_row}']
    data = [[cell.value for cell in row]
            for row in rows if row[0].value]
    # 篩選非空格單元格
    for row in data:
        summary_sheet.append(row)
summary_wb.save('resources/小寶劍大藥房2018年銷售資料匯總.xlsx')