openpyxl

OpenPyXL模組：基本功能操作

l   在命令提示字元輸入pip install openpyxl，下載並安裝OpenPyXL模組。

l   開啟Excel檔與工作表：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
# 此Workbook物件代表Excel檔
aBook = openpyxl.load_workbook("example.xlsx")
print(type(aBook)) à <class 'openpyxl.workbook.workbook.Workbook'>
print(aBook.sheetnames) à ['Sheet1', 'Sheet2', 'Sheet3']
# 此Worksheet物件代表工作表
aSheet = aBook["Sheet3"]
print(type(aSheet)) à <class 'openpyxl.worksheet.worksheet.Worksheet'>
print(aSheet) à <Worksheet "Sheet3">
print(aSheet.title) à Sheet3
bSheet = aBook.active
print(bSheet) à <Worksheet "Sheet1">

l   取得Excel檔工作表的儲存格：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook["Sheet1"]
# Cell物件取得儲存格的方法一
aCell = aSheet["B1"]
print(aCell) à <Cell 'Sheet1'.B1>
print("Row " + str(aCell.row) + ", Column " + aCell.column + " is " + aCell.value)à Row 1, Column B is Apples
print("Cell " + aCell.coordinate + " is " + aCell.value) à Cell B1 is Apples
# Cell物件取得儲存格的方法二
print(aSheet.cell(row = 1, column = 2)) à <Cell 'Sheet1'.B1>
print(aSheet.cell(row = 1, column = 2).value) à Apples
# Worksheet物件取得使用中的欄列範圍
print(aSheet.max_row) à 7
print(aSheet.max_column) à 3

l   取得Excel檔工作表的儲存格範圍：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook["Sheet1"]
# Generator物件含有矩形範圍的Cell物件
print(list(aSheet["A1":"B2"])) à [(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>)]
# 印出Generator物件矩形範圍的所有Cell物件
for rowCell in aSheet["A1":"C2"]:
    for iCell in rowCell:
        print(iCell.coordinate, iCell.value)
    print("---End of Row---")

l   以欄列取得Excel檔工作表的儲存格範圍：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook.active
# Generator物件含有矩形範圍的Cell物件
print(list(aSheet.rows)) à [(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), …, (<Cell 'Sheet1'.A7>, <Cell 'Sheet1'.B7>, <Cell 'Sheet1'.C7>)]
print(list(aSheet.columns)[1]) à (<Cell 'Sheet1'.B1>, …, <Cell 'Sheet1'.B7>)
# 印出Generator物件矩形範圍的所有Cell物件
for iCell in list(aSheet.columns)[1]:
    print(iCell.coordinate, iCell.value)

l   欄的字母和數字之間的轉換：
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
print(get_column_letter(27)) à AA
print(column_index_from_string("AA")) à 27

l   建立並儲存Excel檔：
import openpyxl
aBook = openpyxl.Workbook()
print(aBook.sheetnames) à ['Sheet']
aSheet = aBook.active
aSheet.title = "Hello World"
print(aBook.sheetnames) à ['Hello World']
aBook.save("test.xlsx")

l   建立與刪除Excel檔工作表：
import openpyxl
aBook = openpyxl.Workbook()
print(aBook.sheetnames) à ['Sheet']
aBook.create_sheet()
print(aBook.sheetnames) à ['Sheet', 'Sheet1']
aBook.create_sheet(index = 0, title = "First Sheet")
print(aBook.sheetnames) à ['First Sheet', 'Sheet', 'Sheet1']
del aBook["Sheet"]
print(aBook.sheetnames) à ['First Sheet', 'Sheet1']
aBook.remove(aBook["Sheet1"])
print(aBook.sheetnames) à ['First Sheet']

l   將值寫入Excel檔工作表的儲存格：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
aSheet["A1"] = "Hello World"
print(aSheet["A1"].value) à Hello World

l   OpenPyXL模組相關內容：
A Python library to read/write Excel2010 xlsx/xlsm files

OpenPyXL模組：視覺化功能操作

l   設定儲存格的字型：
import openpyxl
from openpyxl.styles import Font
aBook = openpyxl.Workbook()
aSheet = aBook.active
# Font物件為字體Times New Roman且粗體，並指定給font屬性
aSheet["A1"].font = Font(name = "Times New Roman", bold = True)
aSheet["A1"] = "Hello World"
# Font物件為大小24點且斜體，並指定給font屬性
aSheet["C3"].font = Font(size = 24, italic = True)
aSheet["C3"] = "Hello Poor World"
aBook.save("test.xlsx")

l   設定儲存格的列高和欄寬：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
# row_dimensions含有RowDimension物件，可設定為0到409之間
aSheet.row_dimensions[1].height = 70
# column_dimensions含有ColumnDimension物件，可設定為0到255之間
aSheet.column_dimensions["C"].width = 50
aBook.save("test.xlsx")
bBook = openpyxl.Workbook()
bSheet = bBook.active
# 可設定隱藏列高
bSheet.row_dimensions[1].hidden = True
# 可設定隱藏欄寬
bSheet.column_dimensions["C"].hidden = True
bBook.save("test2.xlsx")

l   設定儲存格的合併和取消合併：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
aSheet.merge_cells("A1:C3")
aSheet["A1"] = "Hello World"
aBook.save("test.xlsx")
bBook = openpyxl.load_workbook("test.xlsx")
bSheet = bBook.active
bSheet.unmerge_cells("A1:C3")
bBook.save("test2.xlsx")

l   凍結窗格：
import openpyxl
# 本書範例檔produceSales.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("produceSales.xlsx")
aSheet = aBook.active
# 儲存格左方和上方將被凍結，儲存格本身不凍結
aSheet.freeze_panes = "A2"
aBook.save("test.xlsx")
bBook = openpyxl.load_workbook("test.xlsx")
bSheet = bBook.active
# 若要解除凍結窗格，傳入None或"A1"皆可
bSheet.freeze_panes = None
bBook.save("test2.xlsx")

l   繪製長條圖、折線圖、圓餅圖：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
for i in range(1, 11):
    aSheet["A" + str(i)] = i
# Reference物件的5個引數分別為Worksheet物件、範圍區域的左上角儲存格欄列、範圍區域的右下角儲存格欄列，第二至第五引數可只填正整數
aRefer = openpyxl.chart.Reference(aSheet, min_col = 1, min_row = 1, max_col = 1, max_row = 10)
# Series物件
aSerie = openpyxl.chart.Series(aRefer, title = "First Series")
# Chart物件：長條圖
aChart = openpyxl.chart.BarChart()
aChart.title = "Bar Chart Title"
aChart.append(aSerie)
aSheet.add_chart(aChart, "C1")
# Chart物件：折線圖
bChart = openpyxl.chart.LineChart()
bChart.title = "Line Chart Title"
bChart.append(aSerie)
aSheet.add_chart(bChart, "C15")
# Chart物件：圓餅圖
cChart = openpyxl.chart.PieChart()
cChart.title = "Pie Chart Title"
cChart.append(aSerie)
aSheet.add_chart(cChart, "C29")
aBook.save("test.xlsx")

l   繪製散佈圖：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
for i in range(1, 11):
    aSheet["A" + str(i)] = i
    aSheet["B" + str(i)] = i**2 + 5
aRefer = openpyxl.chart.Reference(aSheet, 2, 1, 2, 10)
bRefer = openpyxl.chart.Reference(aSheet, 1, 1, 1, 10)
aSerie = openpyxl.chart.Series(aRefer, bRefer, title = "First Series")
# Chart物件：散佈圖
aChart = openpyxl.chart.ScatterChart()
aChart.title = "Scatter Chart Title"
aChart.append(aSerie)
aSheet.add_chart(aChart, "C1")
aBook.save("test.xlsx")

Selenium模組

l   在命令提示字元輸入pip install selenium，下載並安裝Selenium模組。

l   以下以Google Chrome瀏覽器為例，下載chromedriver：
ChromeDriver -WebDriver for Chrome

l   WebDriver物件與WebElement物件範例：
from selenium import webdriver
chromedriverPath = r"C:\Users\Timmy\Documents\Python\chromedriver.exe"
driver = webdriver.Chrome(chromedriverPath)
print(type(driver))à <class 'selenium.webdriver.chrome.webdriver.WebDriver'>
driver.get("http://inventwithpython.com")
# 參考下列WebDriver物件方法說明
element = driver.find_element_by_class_name("display-3")
print(type(element))à <class 'selenium.webdriver.remote.webelement.WebElement'>
# 參考下列WebElement物件屬性或方法說明
print(element.text) à Learn to Program. For Free.

l   WebDriver物件方法說明－find_element_*、find_elements_*：
WebDriver物件方法find_element_*與find_elements_*皆返回WebElement物件，前者尋找比對符合的第一個元素，後者尋找比對符合的所有元素串列；如果找不到，會丟出NoSuchElement例外。
返回元素標籤為test的元素(只有此方法不分大小寫，"test"與"TEST"都可以比對<test>元素標籤)：
.find_element_by_tag_name("test")
.find_elements_by_tag_name("test")返回元素標籤為p且class屬性的值為test的元素：
.find_element_by_css_selector("p.test")
.find_elements_by_css_selector("p.test")返回class屬性的值為test的元素：
.find_element_by_class_name("test")
.find_elements_by_class_name("test")返回id屬性的值為test的元素：
.find_element_by_id("test")
.find_elements_by_id("test")返回name屬性的值為test的元素：
.find_element_by_name("test")
.find_elements_by_name("test")返回完全符合網頁文字test的<a>元素：
.find_element_by_link_text("test")
.find_elements_by_link_text("test")返回部分符合網頁文字test的<a>元素：
.find_element_by_partial_link_text("test")
.find_elements_by_partial_link_text("test")

l   WebDriver物件方法說明－.back()、.forward()、.refresh()、.quit()：
點按瀏覽器下一頁按鈕：
.back()點按瀏覽器上一頁按鈕：
.forward()點按瀏覽器重新整理按鈕：
.refresh()點按瀏覽器關閉按鈕：
.quit()

l   WebElement物件屬性或方法說明－.tag_name、.get_attribute()、.text、.location、.is_displayed()：
from selenium import webdriver
chromedriverPath = r"C:\Users\Timmy\Documents\Python\chromedriver.exe"
driver = webdriver.Chrome(chromedriverPath)
driver.get("http://inventwithpython.com")
element = driver.find_element_by_link_text("Al's blog")
# 返回元素標籤字串
print(element.tag_name) à a
# 返回元素屬性的值的字串
print(element.get_attribute("href")) à http://inventwithpython.com/blog/
# 返回元素文字字串
print(element.text) à Al's blog
# 返回元素位置座標
print(element.location) à {'x': 151, 'y': 8152}
# 返回元素可見與否
print(element.is_displayed()) à True

l   WebElement物件屬性或方法說明－.send_keys()、.clear()、.submit()、.is_enabled()：
from selenium import webdriver
chromedriverPath = r"C:\Users\Timmy\Documents\Python\chromedriver.exe"
driver = webdriver.Chrome(chromedriverPath)
driver.get("https://mail.yahoo.com")
# 以下方法要對文字欄位的<input>或<textarea>元素進行操作
element = driver.find_element_by_id("login-username")
# 填寫表單
element.send_keys("Hello World")
# 清除表單
element.clear()
# 提交表單
element.submit()
# 返回提交的表單啟用與否
print(element.is_enabled()) à True

l   WebElement物件屬性或方法說明－.click()、.is_selected()：
from selenium import webdriver
chromedriverPath = r"C:\Users\Timmy\Documents\Python\chromedriver.exe"
driver = webdriver.Chrome(chromedriverPath)
driver.get("http://www.wibibi.com/info.php?tid=190")
# 以下方法要對選項按鈕欄位的<input>元素進行操作
element = driver.find_element_by_name("location")
# 點按頁面
element.click()
# 返回核取方塊或選項按鈕勾選與否
print(element.is_selected()) à True

l   使用selenium.webdriver.common.keys模組傳送鍵盤按鍵：
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
chromedriverPath = r"C:\Users\Timmy\Documents\Python\chromedriver.exe"
driver = webdriver.Chrome(chromedriverPath)
driver.get("https://nostarch.com/")
element = driver.find_element_by_tag_name("html")
# 參考下列Keys物件屬性說明
element.send_keys(Keys.END)

l   Keys物件屬性說明：
鍵盤的上下左右方向鍵：
Keys.UP, Keys.DOWN, Keys.LEFT, Keys.RIGHT鍵盤的Enter按鍵和Mac的Return按鍵功能相同：
Keys.ENTER, Keys.RETURN鍵盤的Esc按鍵：
Keys.ESCAPE其它與鍵盤按鍵名稱相同的Keys物件屬性：
Keys.TAB, Keys.F1, Keys.F2, …, Keys.F12, Keys.BACK_SPACE, Keys.DELETE, Keys.HOME, Keys.END, Keys.PAGE_UP, Keys.PAGE_DOWN