آموزش کامل کتابخانه openpyxl در پایتون: از کار با اکسل تا اتوماسیون پیشرفته

فهرست مطالب

openpyxl چیست و چه کاربردی دارد؟
نصب و راه‌اندازی سریع
ایجاد، خواندن و ذخیره‌سازی فایل‌های اکسل
مدیریت شیت‌ها (Worksheets)
خواندن و نوشتن داده‌ها در سلول‌ها
استایل‌دهی و فرمت‌بندی سلول‌ها (فونت، رنگ، کادر)
فرمول‌نویسی و چالش مقادیر None
بهینه‌سازی حافظه برای داده‌های بزرگ (Read-Only / Write-Only)
رسم نمودار در اکسل با پایتون
مقایسه حالت عادی و حالت جریانی (Streaming)
پرسش‌های متداول
عیب‌یابی سریع مشکلات رایج

خلاصه سریع و نکات کلیدی

کتابخانه openpyxl قدرتمندترین ابزار پایتون برای خواندن، نوشتن و استایل‌دهی فایل‌های اکسل با فرمت .xlsx بدون نیاز به نصب نرم‌افزار مایکروسافت اکسل است. این کتابخانه بر خلاف Pandas، دسترسی کامل به جزئیات ظاهری سلول‌ها، فرمول‌ها، ادغام سلول‌ها و رسم نمودار را فراهم می‌کند. برای پردازش فایل‌های بسیار سنگین (بالای ۱۰۰ مگابایت)، استفاده از حالت‌های Read-only و Write-only برای جلوگیری از پر شدن حافظه RAM ضروری است.

کار با فایل‌های اکسل یکی از رایج‌ترین نیازمندی‌ها در اتوماسیون اداری، گزارش‌گیری مالی و تحلیل داده است. پایتون ابزارهای مختلفی برای کار با داده‌ها دارد، اما اگر هدف شما علاوه بر انتقال داده، حفظ فرمت‌بندی، اعمال فونت و رنگ، ایجاد نمودارهای زنده و فرمول‌نویسی دقیق باشد، کتابخانه openpyxl گزینه اول شما خواهد بود.

openpyxl چیست و چه کاربردی دارد؟

openpyxl یک کتابخانه متن‌باز و تخصصی در پایتون برای خواندن و نوشتن فایل‌های اکسل (فرمت XLSX/XLSM/XLTX/XLTM) است. این کتابخانه به شما اجازه می‌دهد ساختار ساختاریافته‌ی فایل‌های اکسل را کاملاً در دست بگیرید و بدون نیاز به اجرای خود نرم‌افزار اکسل، گزارش‌های حرفه‌ای ایجاد کنید.

تفاوت اصلی openpyxl با کتابخانه‌هایی مثل Pandas در این است که پانداس صرفاً داده‌ها را به شکل جدول دیده‌بانی می‌کند و استایل‌ها یا نمودارها در آن از بین می‌روند. اما openpyxl تمام ویژگی‌های بصری، ساختاری و فرمولی اکسل را حفظ کرده و قابلیت سفارشی‌سازی کامل آن‌ها را فراهم می‌سازد. اگر در پروژه‌های خود به ابزارهای برنامه‌نویسی مختلف نیاز دارید، بررسی مجموعه‌ی قطعه‌کدهای کاربردی پایتون می‌تواند به اتوماسیون سریع‌تر کارهای شما کمک کند.

نصب و راه‌اندازی سریع

برای نصب این کتابخانه کافی است از مدیر بسته‌ی پایتون (pip) استفاده کنید. استاندارد جدید openpyxl هیچ وابستگی اجباری به نرم‌افزارهای جانبی ندارد.

    pip install openpyxl

اگر قصد دارید تصاویری با فرمت PNG یا JPG را وارد شیت‌های اکسل کنید، پیشنهاد می‌شود بسته pillow را نیز در کنار آن نصب کنید:

    pip install pillow

ایجاد، خواندن و ذخیره‌سازی فایل‌های اکسل

در openpyxl، فایل اصلی اکسل با کلاس Workbook شناخته می‌شود. وقتی یک Workbook جدید می‌سازید، به‌طور خودکار حداقل یک شیت فعال در آن قرار دارد.

ساخت فایل جدید و ذخیره‌سازی

# ۱. فراخوانی کلاس Workbook
from openpyxl import Workbook
# ۲. ایجاد یک شیء جدید

wb = Workbook()
# ۳. انتخاب شیت فعال

ws = wb.active

ws.title = “گزارش فروش”
# ۴. مقداردهی به یک سلول

ws[‘A1’] = “نام محصول”

ws[‘B1’] = “قیمت”
# ۵. ذخیره فایل در مسیر دلخواه

wb.save(“sales_report.xlsx”)

بارگذاری و خواندن فایل موجود

برای باز کردن فایل‌های موجود از تابع load_workbook استفاده می‌کنیم.

from openpyxl import load_workbook
# بارگذاری فایل

wb = load_workbook(“sales_report.xlsx”)
# مشاهده اسامی تمام شیت‌ها

print(wb.sheetnames)
# انتخاب یک شیت خاص با نام آن

ws = wb[“گزارش فروش”]

print(ws[‘A1’].value)

مدیریت شیت‌ها (Worksheets)

مدیریت شیت‌ها شامل ایجاد شیت جدید، تغییر نام، تغییر رنگ تب، کپی کردن و حذف شیت‌ها است. این عملیات با متدهای بسیار ساده در openpyxl انجام می‌شوند:

ایجاد شیت جدید: wb.create_sheet(title="نام_شیت", index=0) (پارامتر index موقعیت قرارگیری را تعیین می‌کند).
حذف شیت: wb.remove(wb["نام_شیت"])
کپی کردن شیت: target = wb.copy_worksheet(source_sheet)
تغییر رنگ تب شیت: ws.sheet_properties.tabColor = "107C41" (کد رنگی HEX).

خواندن و نوشتن داده‌ها در سلول‌ها

شما می‌توانید هم با آدرس متنی سلول (مثل A1) و هم با اندیس سطر و ستون (شماره سطر و ستون از 1 شروع می‌شود) به داده‌ها دسترسی داشته باشید.

# روش اول: استفاده از آدرس متنی

ws[‘A2’] = “لپ‌تاپ”
# روش دوم: استفاده از متد cell (سطر ۲، ستون ۲)

ws.cell(row=2, column=2, value=45000000)
# افزودن یک سطر کامل داده به صورت یکجا

ws.append([“ماوس”, 350000, “موجود”])

نکته تخصصی: اندیس‌گذاری در openpyxl برخلاف لیست‌های پایتون، 1-based است؛ یعنی سطر اول و ستون اول عدد ۱ هستند نه ۰.

پیمایش (Iterate) در میان سلول‌ها

برای خواندن محدوده‌ای از سلول‌ها، متد iter_rows بهینه‌ترین گزینه است:

for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=2, values_only=True):

    print(row)

استایل‌دهی و فرمت‌بندی سلول‌ها (فونت، رنگ، کادر)

یکی از نقاط قوت اصلی openpyxl، قدرت بالا در ظاهرپردارزی شیت‌هاست. ماژول openpyxl.styles ابزارهای کامل مانند Font، PatternFill، Alignment و Border را در اختیار شما قرار می‌دهد.

اگر در برنامه‌نویسی نرم‌افزارهای مدیریتی فعالیت دارید و فایل‌های اکسل را به عنوان خروجی برنامه‌های دسکتاپ تولید می‌کنید، ترکیب این خروجی‌ها با فرمت‌بندی‌های بصری بسیار اهمیت دارد. برای ساخت سیستم‌های نرم‌افزاری پیشرفته‌تر می‌توانید آموزش کامل ساخت نرم‌افزارهای دسکتاپ با pyqt5 را مطالعه کنید.

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
cell = ws[‘A1’]
# ۱. تنظیم فونت، سایز و رنگ متن

cell.font = Font(name=‘Tahoma’, size=12, bold=True, color=‘FFFFFF’)
# ۲. تنظیم رنگ پس‌زمینه (Fill)

cell.fill = PatternFill(start_color=‘1E3A8A’, end_color=‘1E3A8A’, fill_type=‘solid’)
# ۳. چیدمان و راست‌چین کردن متن

cell.alignment = Alignment(horizontal=‘center’, vertical=‘center’)
# ۴. افزودن حاشیه (Border)

thin_border = Side(style=‘thin’, color=‘000000’)

cell.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border)

فرمول‌نویسی و چالش مقادیر None

نوشتن فرمول در openpyxl به سادگی نسبت دادن رشته فرمول اکسل به سلول است. برای مثال: ws['C10'] = "=SUM(C1:C9)".

اما یک چالش بسیار مهم که خیلی از برنامه‌نویسان با آن مواجه می‌شوند، خواندن خروجی این فرمول‌ها در پایتون است.

نکته کلیدی: چرا خروجی فرمول None می‌شود؟

openpyxl موتور ارزیابی فرمول ندارد و فرمول‌ها را اجرا نمی‌کند! اگر فایلی را با data_only=True باز کنید، openpyxl مقدارِ محاسبه‌شده‌ای که اکسل قبلاً هنگام ذخیره‌سازی فایل در کَش نوشته است را می‌خواند. اگر آن فایل هرگز توسط نرم‌افزار اکسل باز و ذخیره نشده باشد، خواندن آن سلول مقدار None را برمی‌گرداند.

# خواندن فرمول به صورت رشته متنی

wb_formula = load_workbook(“data.xlsx”)

print(wb_formula.active[‘C10’].value)  # خروجی: ‘=SUM(C1:C9)’
# خواندن مقدار عددی حاصل از فرمول (Cached Value)

wb_data = load_workbook(“data.xlsx”, data_only=True)

print(wb_data.active[‘C10’].value)     # خروجی: 125000 (یا None اگر توسط اکسل محاسبه نشده باشد)

بهینه‌سازی حافظه برای داده‌های بزرگ (Read-Only / Write-Only)

وقتی یک فایل اکسل چند صد مگابایتی یا با چند صد هزار سطر را باز می‌کنید، openpyxl تمام شیءهای درون XML فایل را در حافظه RAM بازسازی می‌کند که می‌تواند باعث کندی شدید یا حتی خطای Out of Memory شود.

چنانچه حجم داده‌های عددی شما بالاست، استفاده از بسته‌های محاسباتی مثل آرایه‌ها و متدهای بهینه‌شده نامپای پیش از درج داده در اکسل، سرعت پردازش را فوق‌العاده افزایش می‌دهد.

استفاده از حالت Read-Only

from openpyxl import load_workbook
# بارگذاری جریانی (Streamed) برای مصرف کم حافظه

wb = load_workbook(“large_file.xlsx”, read_only=True)

ws = wb.active
for row in ws.iter_rows(values_only=True):

    process_data(row)
# حتماً در پایان فایل را ببندید

wb.close()

استفاده از حالت Write-Only

from openpyxl import Workbook
wb = Workbook(write_only=True)

ws = wb.create_sheet()
for i in range(100000):

    ws.append([f“Item {i}”, i * 1.5])
wb.save(“large_output.xlsx”)

رسم نمودار در اکسل با پایتون

openpyxl از انواع نمودارهای میله‌ای، خطی، دایره‌ای و پراکندگی پشتیبانی می‌کند. برای ساخت نمودار باید سه مرحله را طی کنید: تعریف مرجع داده‌ها (Reference)، ایجاد شیء نمودار و اضافه کردن آن به شیت.

from openpyxl.chart import BarChart, Reference
# ۱. تعیین محدوده داده‌های نمودار

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)

categories = Reference(ws, min_col=1, min_row=2, max_row=5)
# ۲. تنظیم مشخصات نمودار

chart = BarChart()

chart.title = “نمودار مقایسه‌ای فروش”

chart.style = 10

chart.add_data(data, titles_from_data=True)

chart.set_categories(categories)
# ۳. اضافه کردن نمودار به سلول E2

ws.add_chart(chart, “E2”)

مقایسه حالت عادی و حالت جریانی (Streaming)

جدول زیر تفاوت‌های کلیدی میان روش استاندارد بارگذاری در openpyxl و روش جریانی (Read-Only / Write-Only) را خلاصه می‌کند:

ویژگی / وضعیت	حالت جریانی (Read/Write-Only)
مصرف حافظه RAM	بسیار پایین و ناچیز (ثابت)
سرعت پردازش فایل‌های بزرگ	فوق‌العاده سریع
قابلیت ویرایش سلول‌های تصادفی	غیرممکن (فقط پیمایش ترتیبی)
پشتیبانی از استایل و نمودار	محدود یا غیرفعال

پرسش‌های متداول

۱. آیا openpyxl می‌تواند فایل‌های پسوند xls (قدیمی) را باز کند؟

خیر، openpyxl فقط از فرمت‌های جدیدتر مبتنی بر XML مانند XLSX پشتیبانی می‌کند. برای باز کردن فایل‌های قدیمی XLS باید از کتابخانه xlrd استفاده کنید.

۲. چطور سلول‌ها را در openpyxl با هم ادغام (Merge) کنیم؟

با استفاده از دستور ws.merge_cells('A1:D1') می‌توانید سلول‌های محدوده مورد نظر را ادغام کنید. برای خارج کردن از حالت ادغام نیز از ws.unmerge_cells('A1:D1') استفاده می‌شود.

۳. چرا هنگام خواندن مقادیر فرمول، عبارت None دریافت می‌کنم؟

زیرا openpyxl خود فرمول را ارزیابی نمی‌کند. هنگام استفاده از data_only=True، سیستم سعی می‌کند آخرین مقدار ذخیره‌شده توسط خود نرم‌افزار اکسل را بخواند. اگر فایل قبلاً در نرم‌افزار اکسل باز و ذخیره نشده باشد، این مقدار وجود ندارد.

۴. تفاوت اصلی openpyxl با Pandas در چیست؟

پانداس ابزاری برای تحلیل داده و پردازش آماری است، اما openpyxl یک کتابخانه برای ساختاردهی و دستکاری کامل ساختار فایل اکسل (استایل، نمودار، فرمول، شیت‌ها) است.

عیب‌یابی سریع مشکلات رایج

خطای PermissionError: [Errno 13] Permission denied

علت: فایل اکسلی که قصد دارید روی آن بنویسید یا ذخیره‌اش کنید، در نرم‌افزار Excel یا برنامه دیگری باز است.
راه‌حل: فایل اکسل را کاملاً ببندید و مجدداً اسکریپت را اجرا کنید.

خطای KeyError: ‘Worksheet Sheet1 does not exist’

علت: شیتی با این نام در فایل اکسل وجود ندارد (به بزرگی و کوچکی حروف و فاصله‌ها حساس است).
راه‌حل: اسامی درست شیت‌ها را با print(wb.sheetnames) چک کنید.

عدم نمایش تصاویر افزوده شده در فایل خروجی

علت: کتابخانه Pillow روی سیستم شما نصب نیست.
راه‌حل: دستور pip install pillow را در ترمینال اجرا کنید.

سوالات متداول

آیا برای استفاده از openpyxl نیاز به نصب مایکروسافت اکسل داریم؟

خیر، کتابخانه openpyxl به صورت کاملاً مستقل عمل می‌کند و برای ساخت، خواندن و ویرایش فایل‌های XLSX نیازی به نصب بودن نرم‌افزار اکسل ندارد.

تفاوت اصلی openpyxl با Pandas در چیست؟

پانداس برای تحلیل و پردازش داده‌ها استفاده می‌شود و استایل‌ها یا نمودارها را حفظ نمی‌کند؛ اما openpyxl دسترسی کامل به ظاهر سلول‌ها، فرمول‌ها و ساختار بصری اکسل را فراهم می‌سازد.

چرا خروجی فرمول‌ها هنگام خواندن فایل None نشان داده می‌شود؟

اگر فایل توسط اکسل باز و محاسبات آن ذخیره نشده باشد یا در هنگام بارگذاری از آرگومان data_only=True استفاده نکرده باشید، مقدار محاسباتی فرمول None خواهد بود.

چگونه فایل‌های اکسل بسیار سنگین را بدون افت سرعت پردازش کنیم؟

برای جلوگیری از پر شدن حافظه RAM در فایل‌های بالای ۱۰۰ مگابایت، باید از حالت‌های بهینه‌شده Read-Only برای خواندن و Write-Only برای نوشتن استفاده کنید.