ใช้งาน Python OpenPyXL to read/write Excel

สำหรับบทความนี้เป็นอีกหนึ่งบทความที่จะมาเรียนรู้และสอนเกี่ยวกับเรื่องการใช้ Library ตัวหนึ่งที่มีชื่อว่า OpenPyXL ในภาษา Python ที่ใช้ในการจัดการสิ่งต่างๆใน Microsoft Excel ซึ่งมีความแตกต่างจากบทความก่อนหน้าที่ใช้ในการคิดคำนวนตารางที่มีมากกว่า 1 ตารางเเละสามารถมีข้อมูลที่ต่างชนิดกันได้ ซึ่งในบทความนี้โดยรวมเเล้วจะอยู่ในการจัดการข้อมูลของโปรแกรมใน Microsoft Excel ที่บอกถึงการเเก้ไขหรือเพิ่มเติมข้อมูลในเเต่ละ Cell ลงใน Microsoft Excel รวมถึงการคำนวณเเละตั้งเงื่อนไขต่างๆให้กับข้อมูลในตารางด้วย และสำหรับท่านใดที่ยังไม่ได้อ่านบทความ Python Pandas ประยุกต์ใช้งาน Excel ก็สามารถย้อนกลับไปอ่านได้เลยนะครับ

จุดประสงค์

• สามารถประยุกต์ใช้ภาษา Python ในการจัดการข้อมูลต่างๆใน Microsoft Excel
• สามารถนำตัว OpenPyXL มาประยุกต์ใช้งานในชีวิตจริงได้
• รู้จักเครื่องมือที่ใช้ในการจัดการข้อมูล
• สามารถสร้างชุดข้อมูลที่ต้องการลงใน Microsoft Excel ได้โดยการใช้ภาษา Python ร่วมกับ OpenPyXL



Software & Tools

• Python (Version 3+)
• Jupyternotebook หรือสามารถใช้เป็นตัวอื่นก็ได้ เช่น PyCharm ,IDLE(Code Editor)
• OpenPyXL(Library)

มาทำความรู้จักกับ openpyxl กัน

 openpyxl   เป็น Library ตัวหนึ่งในภาษา Python ซึ่งมีไว้สำหรับการอ่านหรือเขียนไฟล์ Excel 2010 เช่น  xlsx, xlsm, xltx, xltm ซึ่งตัว OpenPyXL เองก็ไม่ได้เป็น Library ที่มีมาตั้งเเต่เเรกใน Python ดังนั้นเราจึงจำเป็นต้องติดตั้งตัว Library นี้ด้วยตัวเอง

วิธีการติดตั้งตัว OpenPyXL

    ในการติดตั้งของเจ้าตัว  openpyxl   สามารถติดตั้งด้วยการเรียกใช้คำสั่ง  pip install ได้เลย

pip install openpyxl

การติดตั้งลง Command Prompt

ใช้สำหรับการติดตั้งใน Command Prompt เพื่อให้สามารถใช้ได้ในโปรเเกรม IDLE หรืออาจจะใช้ลงใน Terminal ของโปรเเกรม PyCharm เพื่อให้สามารถเรียกใช้ตัว Library นี้ได้ในโปรเเกรม PyCharm

conda install openpyxl

การติดตั้งลง Anaconda Prompt

ใช้สำหรับการติดตั้งใน Anaconda Prompt เพื่อให้สามารถเรียกใช้ OpenPyXL ใน Jupyter Notebooks ได้ ซึ่งถ้าหากว่าใครใช้ MacOS ก็สามารถเลือกติดตั้งตามรูปเเบบที่ตัวเองต้องการได้เลย เช่น ถ้าต้องการใช้งาน Library บนตัว IDLE ก็ให้ใช้คำสั่ง  pip install openpyxl  ลงใน Terminal ของ MacOs นั้นได้เลย ส่วนถ้าเป็น Jupyter Notebooks ก็แค่เปลี่ยนเป็น   conda install openpyxl 

ทั้งนี้หากใครที่ไม่เเน่ใจว่าเคยลงไปเเล้วหรอยังก็สามารถเช็คได้ด้วยคำสั่งตามด้านล่างนี้

pip list openpyxl

หรือ

conda list openpyxl

การเช็คตัว Library

และถ้าหากต้องการที่จะถอนการติดตั้งก็สามารถทำได้ด้วยคำสั่งตามด้านข้างนี้

pip uninstall openpyxl

หรือ

conda uninstall openpyxl

ถอนการติดตั้ง

เมื่อสามารถติดตั้งได้เสร็จเรียบร้อยเเล้วก็มาเข้าสู่ขั้นตอนต่อไปกันเลยครับ

การสร้างไฟล์หรือการเปิดไฟล์ของ Excel   

เมื่อพูดถึงการเรียกใช้ Library ขั้นตอนเเรกหนีไม่พ้นการ import เเน่นอนก็ให้ทำการ import ตัว Library เข้ามาเลยด้วยคำสั่งด้านล่าง (ผมจะให้ตัวย่อเป็น xl)

import openpyxl as xl

จากนั้นเราจะทำการสร้าง Workbook ขึ้นมาใหม่ เเละทำการบันทึกออกมาเป็นไฟล์ Excel

file = xl.Workbook()
file.save('filename.xlsx')

 Workbook  คือ Object ที่หน้าตาเหมือนกับ Excel สร้างมาเพื่อเป็นตัวเเทนของไฟล์ Excel ที่เราต้องการทำการอ่านหรือเขียน

สร้างไฟล์ใน Jupyter

เนื่องจากสร้างขึ้นมาถึงแล้วเราทำการบันทึกทันที ดังนั้นไฟล์ที่ได้นี้ก็จะเป็นไฟล์ Excel เปล่า

ไฟล์ที่ถูกสร้างขึ้น

เเต่ถ้าหากต้องการที่จะเปิดไฟล์ Excel ที่มีอยู่เเล้วขึ้นมาเพื่อจะใช้งาน สามารถดูคำสั่งด้านล่างเลย

xl.load_workbook('filename.xlsx')

ให้เราเลือกวิธีใดวิธีหนึ่งซึ่งไม่ว่าจะเลือกสร้างใหม่หรืออ่านไฟล์เก่าเข้ามาก็จะได้ออปเจ็ค ขึ้นมา จากนั้นจะใส่อะไรลงไปหรือจะดูข้อมูลข้างในก็สามารถทำได้เเล้ว

การออกเเบบ worksheet 

ในหนึ่งไฟล์นั้นจำเป็นที่จะต้องมีแผ่นงานอย่างน้อย 1 แผ่น ซึ่งในหนึ่งไฟล์นั้นก็สามารถที่จะมีแผ่นงานหลายๆแผ่นอยู่ในไฟล์เดียวกันได้ ซึ่งในกรณีที่เราได้ทำการสร้างไฟล์ขึ้นเเน่นอนว่าจะต้องมีเเผ่นงานที่คู่กันมาอยู่เเล้ว 1 แผ่น เเละถ้าหากเราต้องการที่จะเช็คว่าจำนวนแผ่นงานของไฟล์ที่เรากำลังทำอยู่นั้นมีอยู่กี่เเผ่น สามารถดูคำสั่งข้างล่างนี้ได้เลยครับ

file.worksheets # ถามถึงแผ่นงาน
file.sheetnames # ถามชื่อแผ่นงาน

เช็คเเผ่นงาน

ถ้าหากว่าเรานั้นต้องการที่จะทำการเปลี่ยนชื่อของแผ่นงานเราก็สามารถที่จะใช้คำสั่งด้านล่างนี้เลย

namesheet = file.worksheets[0]
namesheet.title = 'filename'

เปลี่ยนชื่อแผ่นงานพร้อมบันทึก

ไฟล์ที่ถูกบันทึก

ต่อไปเราจะมาลองสร้างแผ่นงานเพิ่มจากเดิม สามารถดูคำสั่งด้านล่างได้เลยครับ

file.create_sheet('filename')

สร้างเเละเช็คเเผ่นงาน

ในกรณีที่มีการเพิ่มแผ่นงานเข้ามาในไฟล์ ปัญหาที่จะตามมาก็คือการที่เราจะสั่งงานนั้นมันจะได้เเค่ทีละแผ่นงานซึ่งหากว่าเราต้องการที่จะไปสั่งงานในแผ่นงานที่ 2 เราก็จำเป็นที่จะต้องใช้คำสั่งตามด้านล่างนี้เลย

file.active # เช็คว่ากำลังทำงานอยู่แผ่นงานไหน
file.active = file['filename'] # เปลี่ยนแผ่นงาน

เปลี่ยนแผ่นงานทำงาน

หรือหากเราอยากจะสร้างแผ่นงานมาเเละใช้งานในเเผ่นงานนั้นเลย คำสั่งด้านล่างเลยครับ

file.active = file.create_sheet('filename')

เพียงเท่านี้แผ่นงานที่เราทำการสร้าง ก็จะถูกใช้งานทันที

จัดการในส่วนของข้อมูลในช่อง

ในแต่ละแผ่นงานจะประกอบไปด้วยช่องของตาราง ซึ่งเมื่อใช้  openpyxl  จัดการพวกช่องนี้ก็อยู่ในรูปของออปเจ็คด้วยการเข้าถึงออปเจ็คที่แทนตัวช่องที่ต้องการนั้นอาจใช้เลขแถวและหลัก

นอกจากนี้ยังสามารถเข้าถึงได้โดยใส่ชื่อของช่องนั้นลงไป ชื่อของช่องนั้นจะเรียกเรียกแนวตั้งเป็นตัวเลขตั้งแต่ 1, 2, 3 ไป ส่วนแนวนอนเป็นตัวอักษรตั้งแต่ A, B, C  เช่น 'A1' ซึ่งคือช่องเเรก
การระบุ Object ที่ต้องการ

namesheet.cell(5,5)
namesheet['E4']

การระบุช่อง

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

namesheet.cell(5,5).value = 'E5'
namesheet['E4'] = 'E4'

กรอกข้อมูลพร้อมบันทึก

หลังจากบันทึกเสร็จ

ทั้งนี้ยังสามารถกรอกในหลายช่องพร้อมกำหนดเงื่อนไขได้อีกด้วยคำสั่งจะเป็นเช่นไรไปดูกันครับ

n = 5 # กำหนดค่าเริ่มต้น
for row in namesheet['B2:E7']: # กำหนดกรอบข้อมูล
    for i in row: 
        i.valua = n # ใส่ค่า
        i += 5 # บวกค่าเพิ่มทีละ 5



กรอกค่าหลายช่องพร้อมเงื่อนไข

ค่าข้อมูลหลังจากบันทึกเสร็จ

จะเห็นได้ว่าเราสามารถที่จะกำหนดเงื่อนไขต่างๆในการกรอกข้อมูลลงในแผ่นงานได้ จุดนี้ก็สามารถนำไปประยุกต์กันได้เลยครับ

การลบข้อมูลเเละแทรกข้อมูลแบบทั้งเเถว

เรามาพูดถึงการเเทรกกันก่อนเลยนะครับในการเเทรกนั้นจะมีคำสั่งที่สำคัญอยู่ 2 คำสั่ง นั่นคือ     .insert_rows()  ซึ่งจะใช้การเเทรกในเเนวนอนหรือว่าเเถวนั่นเอง และ   .insert_cols()  ซึ่งจะใช้ในการเเทรกในเเนวตั้งหรือว่าคอลัมน์ การใช้งานเหมือนกันเเต่ต่างกันเเค่เเนวที่จะถูกเเทรก

คำสั่งแทรกเเถว

namesheet.insert_rows(4,4)

เเทรกที่เเถว 4 จำนวน 4 เเถว

ตารางหลังจากการบันทึก

คำสั่งแทรกคอลัมน์

namesheet.insert_cols(3,1)

เเทรกที่คอลัมน์ 3 จำนวน 1 คอลัมน์

ตารางหลังจากการบันทึกการเเทรกตาราง

ต่อไปเราก็จะมาพูดกันในเรื่องของการลบข้อมูลทั้งเเถวซึ่งการลบข้อมูลก็เหมือนกับการเเทรกเลยจะมีคำสั่งที่สำคัญอยู่ 2 คำสั่ง นั่นคือ   .delete_rows()  ซึ่งจะใช้ในการลบข้อมูลในเเนวนอนหรือว่าเเถวนั่นเอง และ   .delete_cols()  ซึ่งจะใช้ในการลบข้อมูลในเเนวตั้งหรือว่าคอลัมน์

วิธีการใช้คำสั่งมีความคล้ายกับการเเทรกข้อความมากซึ่งผมจะยกตัวอย่างเดียวที่มีทั้งการลบในเเนวเเถวเเละคอลัมน์

ลบข้อมูลเเนวตั้งเเละเเนวนอนตามที่กำหนด

ตารางหลังลบข้อมูลเเละบันทึก

เมื่อสามารถเเทรกเเถวหรือลบข้อมูลในเเถวต่างๆได้ ตรงนี้ก็น่าจะสามารถประยุกต์ใช้งานตามที่ต้องการได้เลย

การผสานช่องข้อมูล

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

namesheet.merge_cells('ช่องที่ต้องการ')

ผสานช่องข้อมูล A1 ถึง B3

หลังคำสั่งผสานเเล้วบันทึก

การผสานมีประโยชน์มากในการสร้างตารางเพราะการทำหัวข้อเรื่องต่างๆข้อตารางก็ต้องผสานช่องข้อมูล

การใส่สีในช่องข้อมูลเเบบพื้นหลัง

การใส่สีลงช่องนั้นมีไว้สำหรับตกเเต่งในตารางข้อมูลเกิดความน่าสนใจเพิ่มมากขึ้น เริ่มจากทำการ  import   PatternFill  เข้ามาซึ่งเราไม่จำเป็นต้อง install Library ใดๆเพิ่ม

from openpyxl.styles import PatternFill

จากนั้นก็มาเริ่มใส่สีกันเลย ดูคำสั่งได้ด้านล่างนี้เลยครับ

namesheet['C1'].fill = PatternFill(patternType='lightGrid',fgColor='FF22AA')
namesheet['C2'].fill = PatternFill(patternType='darkHorizontal',fgColor='FFAA33')
namesheet['C3'].fill = PatternFill(patternType='darkUp',fgColor='3895A1')
namesheet['C4'].fill = PatternFill(patternType='solid',fgColor='6632FF')
namesheet['C5'].fill = PatternFill(patternType='darkGray',fgColor='55AA11')
namesheet['C6'].fill = PatternFill(patternType='lightGray',fgColor='994433')
namesheet['C7'].fill = PatternFill(patternType='lightDown',fgColor='11AAEE')
namesheet['C8'].fill = PatternFill(patternType='darkTrellis',fgColor='226611')
namesheet['C9'].fill = PatternFill(patternType='mediumGray',fgColor='AA6600')

ใส่สีลงช่องข้อมูล

ตารางบันทึกหลังใส่สี

ทางเรา STACKPYTHON คาดหวังว่าทุกคนที่อ่านบทความนี้จบ จะได้รับความรู้ครบถ้วนตามจุดประสงค์ที่ทางเราได้ตั้งไว้ข้างต้น

ขอบคุณมากครับที่เข้ามาอ่านบทความต่างๆของทาง STACKPYTHON สำหรับบทความนี้ก็ขอจบลงเพียงเท่านี้ก่อนครับ พบกันบทความหน้าได้เลยครับ มีคำถามหรือข้อเสนอแนะตรงไหนก็คอมเมนต์กันเข้ามาได้เลยนะครับ 

Nu STACKPYTHON

Follow us on

Medium: STACKPYTHON

Youtube: STACKPYTHON

Facebook: STACKPYTHON

Python Courses