ใช้ Python กรอกฟอร์ม Excel ด้วย openpyxl

library ที่ใช้ทำงานบน excel ได้ 100% ตั้งแต่ตั้งค่า font จนถึงการทำ pivot table เรียนกับ Ultimate Python

เอกสารประกอบ

การใช้ openpyxl กับ Excel

openpyxl เป็น library ที่ออกแบบมาเพื่อให้ทำงานกับ excel โดยเฉพาะ โดยสามารถทำงานต่างๆที่เกี่ยวข้องกับ excel ได้แทบทุกอย่าง ไม่ว่าจะเป็นการจัดการข้อมูล format การจัดการ sheet การทำกราฟ pivot table อย่างครบวงจร ทำให้เป็นอีกเครื่องมือหนึ่งที่ใช้จัดการงาน Excel แบบอัตโนมัติได้

ติดตั้ง openpyxl

openpyxl เป็น library ที่ใช้เพื่อการทำความเข้าใจข้อมูลใน Excel และ csv ซึ่งในกรณีที่ใช้โปรแกรม Anaconda และ Jupyter Notebook ที่ติดตั้งอยู่บนคอมพิวเตอร์ จะไม่จำเป็นต้องใช้ openpyxl ในการใช้ร่วมกับ pandas ในการเปิดไฟล์ เนื่องจากในโปรแกรมดังกล่าวมีการติดตั้งชุดคำสั่งที่ทำหน้าที่เปิดไฟล์ Excel มาให้แล้วนั่นเอง

แต่สำหรับการเปิดไฟล์ Excel ด้วย library pandas บนโปรแกรมออนไลน์ จะต้องติดตั้ง openpyxl เพื่อใช้ในการเปิดไฟล์ Excel ซึ่งการติดตั้งเครื่องมือบนโปรแกรมบนเครื่องคอมพิวเตอร์ของเราต้องทำเพียงครั้งเดียวเท่านั้น แต่สำหรับการติดตั้งโปรแกรมออนไลน์จะต้องติดตั้งใหม่ทุกครั้งที่ใช้งาน

In [1]:
!pip install openpyxl 

Collecting openpyxl   Downloading openpyxl-3.0.7-py2.py3-none-any.whl (243 kB)      |████████████████████████████████| 243 kB 4.5 MB/s eta 0:00:01 Collecting et-xmlfile   Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB) Installing collected packages: et-xmlfile, openpyxl Successfully installed et-xmlfile-1.1.0 openpyxl-3.0.7 

เรียนรู้เกี่ยวกับ Openpyxl บน Real Python

นำ openpyxl มาใช้

เมื่อทำการติดตั้งเรียบร้อยจะต้องนำชุดคำสั่งมาใช้งานบนไฟล์ Notebook ปัจจุบันที่เรากำลังเปิดใช้งานเพื่อเขียนโค้ดอยู่ ผ่านคำสั่ง import ซึ่งจะต้องทำทุกครั้งในการเปิดใช้งานไฟล์ Notebook

ในการใช้ openpyxl ทำงานกับ Excel เราจะใช้คำสั่ง load_workbook ที่ใช้ดึงข้อมูลจาก Excel มาทำงานด้วย ซึ่งเราจะใช้คำสั่ง from เพิ่มเติมเพื่อกำหนดการนำคำสั่ง มาใช้เฉพาะ

In [2]:
from openpyxl import load_workbook

ดึงข้อมูลจาก Excel

เราจะใช้คำสั่ง load_workbook() และรับ path ของไฟล์ที่ต้องการที่จะเปิดโดยเราจะได้ข้อมูลมาเป็น object ประเภท workbook โดยข้อมูลที่ได้มานั้นจะมีข้อมูลทุกอย่างที่บันทึกไว้บน excel มากกว่าแค่ข้อมูล แต่รวมถึง formatting ต่างๆ ด้วย

In [3]:
workbook = load_workbook('North.xlsx')workbook

Out[3]:
<openpyxl.workbook.workbook.Workbook at 0x7f3ea01ed780>

ข้อมูลจาก worksheet

ในการทำงานกับ openpyxl ข้อมูล workbook จะถูกแบ่งเป็นข้อมูลย่อยรายชีทที่เรียกว่า worksheet ซึ่งการดึงข้อมูล worksheet จะใช้สัญลักษณ์ [ ] ร่วมกับการระบุชชื่อชีท เป็น string

In [4]:
worksheet = workbook['North']worksheet

Out[4]:
<Worksheet "North">

ข้อมูลจาก cell

เราสามารถดึงข้อมูลจาก cell ใดๆ ได้โดยการระบุ cell ที่ต้องการเพื่อดึงข้อมูลจาก worksheet โดยการระบุ cell ที่ต้องการที่ใช้กับสัญลักษณ์ [ ] ที่ทำงานกับ worksheet ซึ่งเราจะได้ข้อมูล cell มาที่มีการเก็บข้อมูลหลายอย่างไว้ข้างใน

In [5]:
cell = worksheet['A1']cell

Out[5]:
<Cell 'North'.A1>

• ข้อมูลจาก cell

เราสามารถดูข้อมูลที่เก็บอยู่ใน cell ได้โดยการใช้คำสั่ง .value ที่ดึงค่าที่เก็บใน cell ออกมา

In [6]:
cell.value

Out[6]:
'Product'

• ข้อมูล font จาก cell

เราสามารถดูข้อมูลที่เก็บอยู่ใน cell ได้โดยการใช้คำสั่ง .value ที่ดึงค่าที่เก็บใน cell ออกมา

In [7]:
cell.font

Out[7]:
<openpyxl.styles.fonts.Font object> Parameters: name='Calibri', charset=None, family=2.0, b=True, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object> Parameters: rgb=None, indexed=None, auto=None, theme=7, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme=None

ข้อมูลจาก column

เราสามารถดึงข้อมูลจาก column ใดๆ ได้จากการระบุ column ร่วมกับสัญลักษณ์ [ ] ซึ่งเราจะได้ข้อมูลใน column เป็น tuple ที่เก็บข้อมูล cell เอาไว้

In [8]:
column = worksheet['A']column

Out[8]:
(<Cell 'North'.A1>,  <Cell 'North'.A2>,  <Cell 'North'.A3>,  <Cell 'North'.A4>,  <Cell 'North'.A5>,  <Cell 'North'.A6>,  <Cell 'North'.A7>,  <Cell 'North'.A8>,  <Cell 'North'.A9>,  <Cell 'North'.A10>,  <Cell 'North'.A11>,  <Cell 'North'.A12>)

ข้อมูลจาก row

เราสามารถดึงข้อมูลจาก row ใดๆ ได้จากการระบุ row ร่วมกับสัญลักษณ์ [ ] ซึ่งเราจะได้ข้อมูลใน row เป็น tuple ที่เก็บข้อมูล cell เอาไว้

In [9]:
row = worksheet[1]row

Out[9]:
(<Cell 'North'.A1>,  <Cell 'North'.B1>,  <Cell 'North'.C1>,  <Cell 'North'.D1>,  <Cell 'North'.E1>)

การแก้ไขข้อมูลใน cell

สามารถทำได้โดยการดึงข้อมูลใน cell ที่ต้องการด้วยคำสั่ง .value และใช้การกำหนดค่าผ่าน = เพื่อกำหนดค่าใหม่ลงไปที่ cell เดิม ซึ่งคำสั่งดังกล่าวจะทำการเปลี่ยนแปลงค่าที่อยู่ใน cell ดังกล่าวแต่จะไม่ทำการเปลี่ยน format ของ cell นั้น

ข้อมูลใน cell

In [10]:
cell.value

Out[10]:
'Product'

การแก้ไขข้อมูล

In [11]:
cell.value = 'New Product'cell.value

Out[11]:
'New Product'

การแก้ไขข้อมูลใน column ด้วย for loop

สามารถทำได้โดยการดึงข้อมูลใน cell ที่ต้องการด้วยคำสั่ง .value และใช้การกำหนดค่าผ่าน = เพื่อกำหนดค่าใหม่ลงไปที่ cell เดิม ซึ่งคำสั่งดังกล่าวจะทำการเปลี่ยนแปลงค่าที่อยู่ใน cell ดังกล่าวแต่จะไม่ทำการเปลี่ยน format ของ cell นั้น

ข้อมูลใน column B

In [12]:
qtr1 = worksheet['B']qtr1

Out[12]:
(<Cell 'North'.B1>,  <Cell 'North'.B2>,  <Cell 'North'.B3>,  <Cell 'North'.B4>,  <Cell 'North'.B5>,  <Cell 'North'.B6>,  <Cell 'North'.B7>,  <Cell 'North'.B8>,  <Cell 'North'.B9>,  <Cell 'North'.B10>,  <Cell 'North'.B11>,  <Cell 'North'.B12>)

เปลี่ยนค่าด้วย for loop

In [13]:
for number in qtr1[1:]:number.value = number.value*10

เช็คค่าใหม่

In [14]:
for number in qtr1[1:]:print(number.value)

67780 36340 8750 8540 6540 5340 4220 3560 3540 2550 2330 

การบันทึกข้อมูลเป็น Excel

เมื่อทำการเปลี่ยนแปลงข้อมูลเสร็จเราสามารถใช้คำสั่ง .save() กับ workbook เพื่อกำหนด path ในการบันทึกข้อมูลใหม่ออกเป็น excel ได้ทันที

In [15]:
workbook.save('New North.xlsx')

เสร็จสิ้นการใช้ openpyxl ทำงาน Excel

เข้ากลุ่มแลกเปลี่ยนความรู้

เรียนเรียน Python ใช้ทำงานจัดการ Excel อัตโนมัติ ใช้ทำ Data Analysis

เริ่มไว ใช้ได้ทันที พร้อมการดูแลจากผู้สอนโดยตรง และกลุ่มแลกเปลี่ยนความรู้

เรียนรู้เกี่ยวกับคอร์สเรียนเพิ่มเติม https://ultimatepython.teachable.com/p/python-excel-automation