การสร้างรายงาน Excel แบบกำหนดเองด้วย Python: คู่มือฉบับสมบูรณ์

สำหรับการสร้างรายงาน Excel แบบอัตโนมัติและเพิ่มความสะดวกในการอ่าน Python มีไลบรารีที่ทรงพลังอย่าง OpenPyXL และ Pillow ซึ่งช่วยให้เราสามารถสร้างรายงาน Excel ด้วยข้อมูลแบบไดนามิกและการจัดรูปแบบได้อย่างมืออาชีพ บทความนี้จะอธิบายวิธีการใช้สคริปต์ Python เพื่อสร้างรายงาน Excel แบบกำหนดเอง โดยเน้นไปที่การเติมข้อมูล, แทรกภาพ, และจัดการแถวหรือคอลัมน์ใน Excel

สคริปต์นี้ทำอะไรได้บ้าง?

สคริปต์นี้ออกแบบมาเพื่อ:

โหลดเทมเพลต Excel และเติมข้อมูลแบบไดนามิกลงในเทมเพลต
กำหนดเนื้อหา เช่น การแทรกข้อความ รูปภาพ และกล่องเช็ค (checkbox)
ซ่อนหรือจัดกลุ่มแถว ตามเงื่อนไขที่กำหนด
จัดการตำแหน่งและขนาดของรูปภาพ รวมถึงการจัดวางภาพไว้ตรงกลางของเซลล์
บันทึกไฟล์ Excel ที่ปรับแต่งแล้วพร้อมชื่อไฟล์ที่มีเวลาปัจจุบัน

โค้ดทั้งหมด

นี่คือโค้ด Python สำหรับการสร้างรายงาน Excel แบบกำหนดเอง:

from openpyxl import load_workbook, Workbook
from datetime import datetime
from openpyxl.drawing.image import Image
import re

from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.utils import get_column_letter, column_index_from_string
from django.conf import settings
from PIL import Image as PILImage
import os

def set_image_with_offset(sheet, img, cell_coordinate, offset_x=0, offset_y=0):
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))  # แยกตัวอักษรคอลัมน์
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))  # แยกหมายเลขแถว
    col_idx = column_index_from_string(col_letter) - 1
    row_idx = row_number - 1

    col_width = sheet.column_dimensions[col_letter].width or 10  # ความกว้างของคอลัมน์
    row_height = sheet.row_dimensions[row_number].height or 15  # ความสูงของแถว

    col_pixels = col_width * 7.5  # 1 หน่วยคอลัมน์ ≈ 7.5 พิกเซล
    row_pixels = row_height * 0.75  # 1 หน่วยแถว ≈ 0.75 พิกเซล

    cell_x = col_idx * col_pixels
    cell_y = row_idx * row_pixels

    final_x = cell_x + offset_x
    final_y = cell_y + offset_y

    pos = XDRPoint2D(pixels_to_EMU(final_x), pixels_to_EMU(final_y))
    size = XDRPositiveSize2D(pixels_to_EMU(img.width), pixels_to_EMU(img.height))

    img.anchor = AbsoluteAnchor(pos=pos, ext=size)
    sheet.add_image(img)

def center_image_in_cell(sheet, img, cell_coordinate):
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))

    col_width = sheet.column_dimensions[col_letter].width or 10
    row_height = sheet.row_dimensions[row_number].height or 15

    col_pixels = col_width * 7.5
    row_pixels = row_height * 0.75

    img_width, img_height = img.width, img.height

    offset_x = int((col_pixels - img_width) / 2 * pixels_to_EMU(1))
    offset_y = int((row_pixels - img_height) / 2 * pixels_to_EMU(1))

    img.anchor = AbsoluteAnchor(
        pos=XDRPoint2D(pixels_to_EMU(offset_x), pixels_to_EMU(offset_y)),
        ext=XDRPositiveSize2D(pixels_to_EMU(img_width), pixels_to_EMU(img_height)),
    )

    sheet.add_image(img)

def gen_xlsx(template_file, selected_sheets, prefix_filename, data):
    checked_image_path = f"{settings.BASE_DIR}/report/checkbox_checked.jpg"
    unchecked_image_path = f"{settings.BASE_DIR}/report/checkbox_unchecked.jpg"

    workbook = load_workbook(template_file)

    for sheet_name in workbook.sheetnames:
        if sheet_name not in selected_sheets:
            del workbook[sheet_name]

    for sheet_name in selected_sheets:
        sheet = workbook[sheet_name]

        for row in sheet.iter_rows():
            for cell in row:
                if cell.value and isinstance(cell.value, str) and cell.value.startswith("<") and cell.value.endswith(">"):
                    placeholder = cell.value.strip("<>")
                    value = data.get(placeholder)

                    if isinstance(value, str):
                        cell.value = value
                    elif isinstance(value, bool):
                        img = Image(checked_image_path if value else unchecked_image_path)
                        center_image_in_cell(sheet, img, cell.coordinate)
                        cell.value = None

    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_path = f"{prefix_filename}_{timestamp}.xlsx"
    workbook.save(output_path)
    return output_path

if __name__ == "__main__":
    data = {
        "customer": "สมชาย ใจดี",
        "inspect_date": "2025-02-01",
        "lot_no": "LOT12345",
        "staff_name": "ทีมตรวจสอบ",
        "size": "ขนาดกลาง",
        "pcs": "30 ชิ้น",
        "hardness.d1_act": "12.5",
        "hardness.acc": True,
        "hardness.spe_acc": False,
        "dimension_app.d1_act": "45.8",
        "dimension_app.acc": True,
        "dimension_app.spe_acc": True,
    }

    output_file = gen_xlsx(
        template_file="./inspection_template.xlsx",
        selected_sheets=["hardness", "dimensions"],
        prefix_filename="./reports/inspection_report",
        data=data,
    )

    print(f"ไฟล์ที่สร้าง: {output_file}")

คำอธิบายของข้อมูลตัวอย่าง

ข้อมูลลูกค้า: ข้อมูลทั่วไป เช่น ชื่อลูกค้า วันที่ตรวจสอบ และหมายเลขล็อต
ผลการวัดและการตรวจสอบ: ข้อมูลที่เติมในเซลล์ เช่น ค่าตัวเลขหรือค่าจริง/เท็จ (True/False)
ตรรกะของกล่องเช็ค: ค่าที่เป็น True หรือ False จะถูกแปลงเป็นภาพกล่องเช็คที่เลือกหรือลบออกโดยอัตโนมัติ

วิธีการใช้งาน

เตรียมไฟล์ Excel เทมเพลตที่มี Placeholder เช่น <customer>, <inspect_date> เป็นต้น
กำหนดข้อมูลในรูปแบบของพจนานุกรม (dictionary) โดยใช้คีย์ที่ตรงกับ Placeholder ในไฟล์ Excel
เรียกใช้ฟังก์ชัน gen_xlsx โดยระบุเส้นทางของไฟล์เทมเพลต, ชื่อชีท, ชื่อไฟล์ผลลัพธ์ และข้อมูลที่ต้องการใส่
รันสคริปต์ และไฟล์ Excel ที่ปรับแต่งแล้วจะถูกสร้างขึ้น

สรุป

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