راهنمایی کامل کار با فایل های اکسل(Excel) در پایتون

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

در این آموزش یاد میگیرید که چطور در پایتون با فایل های اکسل کار کنید. یاد میگیرید که چطور فایل های اکسل را تغییر داده یا اطلاعات را از آنها استخراج کنید. همچنین میبینید که چطور میتوانید با پایتون صفحات گسترده ساده و پیچیده شامل استایل و نمودار ایجاد کنید. در این مقاله از پکیج openpyxl استفاده خواهیم کرده که فعلا بهترین پکیج پایتون برای کار با صفحات excel است.

این آموزش برای برنامه نویسانی نوشته شده است که با مواردی مانند دیکشنری و لیست پایتون به خوبی آشنا بوده و درک درستی از برنامه نویسی شی گرا در پایتون دارند.

مقاله پیشنهادی: آموزش کامل کار با pip پایتون

 #  شروع کار با اکسل در پایتون

حالا وقت آن است که شروع کنیم! در قدم اول باید پکیج openpyxl را نصب کنید. برای اینکه بتوانید از این آموزش استفاده کنید باید نسخه پایتون شما بالاتر از 3.7 و نسخه openpyxl هم بالاتر از 2.6.2 باشد. با دستور زیر میتوانید این پکیج را نصب کنید:

$ pip install openpyxl

بعد از نصب پکیج، با کد زیر میتوانید یک صفحه اکسل ساده ایجاد کنید:

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "hello"
sheet["B1"] = "world!"

workbook.save(filename="hello_world.xlsx")

بعد از اجرای کد بالا،‌ یک فایل اکسل جدید به نام hello_world.xlsx برای شما ایجاد خواهد شد و اگر این فایل را اجرا کنید، نتیجه شبیه به تصویر زیر خواهد بود:

خوشحال باشید که اولین فایل اکسل را با پایتون ایجاد کردید!

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

 #  خواندن فایل های اکسل با پایتون

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

برای خواندن اطلاعات از فایل نمونه ای که داریم باید به شکل زیر کار کنیم:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="sample.xlsx")
>>> workbook.sheetnames
['Sheet 1']

>>> sheet = workbook.active
>>> sheet
<Worksheet "Sheet 1">

>>> sheet.title
'Sheet 1'

در کد بالا، ابتدا فایل sample.xlsx را با استفاده از load_workbook باز کرده و سپس می‌توانید از workbook.sheetnames برای دیدن همه برگه‌ها استفاده کنید. پس از آن workbook.active اولین برگه موجود را انتخاب می کند و در این صورت می بینید که Sheet 1 را به طور خودکار انتخاب می کند.

اکنون، پس از باز کردن یک صفحه گسترده، به راحتی می توانید داده ها را از آن به صورت زیر بخوانید:

>>> sheet["A1"]
<Cell 'Sheet 1'.A1>

>>> sheet["A1"].value
'marketplace'

>>> sheet["F10"].value
"G-Shock Men's Grey Sport Watch"

برای گرفتن مقدار یک سلول باید از value استفاده کنید در غیر اینصورت، آبجکت Cell را دریافت خواهید کرد. همچنین میتوانید از cell برای گرفتن مقدار سلول با استفاده از ایندکس آن استفاده کنید. دقت کنید که حتما از value استفاده کنید تا مقدار واقعی سلول را به شما بدهد:

>>> sheet.cell(row=10, column=6)
<Cell 'Sheet 1'.F10>

>>> sheet.cell(row=10, column=6).value
"G-Shock Men's Grey Sport Watch"

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

متد load_workbook دو آرگومان دیگر دارد که نحوه باز کردن یک فایل اکسل را تغییر میدهد. هر دو این مقادیر boolean هستند:

1. read_only که فایل را در حالت فقط خواندنی باز کرده و برای باز کردن فایل های خیلی بزرگ مناسب است.
2. data_only فرمول های بارگیری را نادیده می گیرد و در عوض فقط مقادیر را بارگذاری می کند.

 +  وارد کردن اطلاعات از فایل اکسل

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

 -  پیمایش در اطلاعات

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

می توانید داده ها را با ترکیبی از ستون ها و ردیف ها برش دهید:

>>> sheet["A1:C2"]
((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>),
 (<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>))

می توانید محدوده هایی از ردیف ها یا ستون ها را دریافت کنید:

>>> # Get all cells from column A
>>> sheet["A"]
(<Cell 'Sheet 1'.A1>,
 <Cell 'Sheet 1'.A2>,
 ...
 <Cell 'Sheet 1'.A99>,
 <Cell 'Sheet 1'.A100>)

>>> # Get all cells for a range of columns
>>> sheet["A:B"]
((<Cell 'Sheet 1'.A1>,
  <Cell 'Sheet 1'.A2>,
  ...
  <Cell 'Sheet 1'.A99>,
  <Cell 'Sheet 1'.A100>),
 (<Cell 'Sheet 1'.B1>,
  <Cell 'Sheet 1'.B2>,
  ...
  <Cell 'Sheet 1'.B99>,
  <Cell 'Sheet 1'.B100>))

>>> # Get all cells from row 5
>>> sheet[5]
(<Cell 'Sheet 1'.A5>,
 <Cell 'Sheet 1'.B5>,
 ...
 <Cell 'Sheet 1'.N5>,
 <Cell 'Sheet 1'.O5>)

>>> # Get all cells for a range of rows
>>> sheet[5:6]
((<Cell 'Sheet 1'.A5>,
  <Cell 'Sheet 1'.B5>,
  ...
  <Cell 'Sheet 1'.N5>,
  <Cell 'Sheet 1'.O5>),
 (<Cell 'Sheet 1'.A6>,
  <Cell 'Sheet 1'.B6>,
  ...
  <Cell 'Sheet 1'.N6>,
  <Cell 'Sheet 1'.O6>))

دقت کنید که در تمام روش های بالا اطلاعات به شکل یک tuple برگشت داده میشود.

مقاله پیشنهادی: wsgi پایتون چیست؟

 -  تبدیل اطلاعات به دیگر ساختارهای پایتونی

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

همانطور که قبلاً دیدید، نتیجه همه پیمایش ها به صورت تاپل است. با این حال، از آنجایی که یک تاپل چیزی بیش از یک لیست تغییرناپذیر نیست، می توانید به راحتی به داده های آن دسترسی داشته باشید و آن را به ساختارهای دیگر تبدیل کنید.

برای مثال، فرض کنید می‌خواهید اطلاعات محصول را از صفحه‌گسترده sample.xlsx استخراج کرده و در یک دیکشنری ذخیره کنید که در آن هر کلید یک ID محصول است.

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

اول از همه، نگاهی به سرفصل ها بیندازید و ببینید به چه اطلاعاتی بیشتر اهمیت می دهید:

>>> for value in sheet.iter_rows(min_row=1,
...                              max_row=1,
...                              values_only=True):
...     print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

این کد لیستی از نام تمام ستون هایی که در صفحه گسترده دارید را برمی گرداند. برای شروع، ستون های زیر را میگیریم:

1. product_id
2. product_parent
3. product_title
4. product_category

خوشبختانه ستون های مورد نیاز در کنار یکدیگر قرار دارند، بنابراین می توانید از min_column و max_column برای به دست آوردن آسان داده های مورد نظر خود استفاده کنید:

>>> for value in sheet.iter_rows(min_row=2,
...                              min_col=4,
...                              max_col=7,
...                              values_only=True):
...     print(value)
('B00FALQ1ZC', 937001370, 'Invicta Women\'s 15150 "Angel" 18k Yellow...)
('B00D3RGO20', 484010722, "Kenneth Cole New York Women's KC4944...)
...

حالا که می دانیم چگونه می توانیم تمام اطلاعات مهم محصول مورد نیاز خود را به دست آورید، بیایید این داده ها را در یک دیکشنری قرار دهیم:

import json
from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

products = {}

# Using the values_only because you want to return the cells' values
for row in sheet.iter_rows(min_row=2,
                           min_col=4,
                           max_col=7,
                           values_only=True):
    product_id = row[0]
    product = {
        "parent": row[1],
        "title": row[2],
        "category": row[3]
    }
    products[product_id] = product

# Using json here to be able to format the output for displaying later
print(json.dumps(products))

کد بالا یک نتیجه json به شکل زیر برمیگرداند:

{
  "B00FALQ1ZC": {
    "parent": 937001370,
    "title": "Invicta Women's 15150 ...",
    "category": "Watches"
  },
  "B00D3RGO20": {
    "parent": 484010722,
    "title": "Kenneth Cole New York ...",
    "category": "Watches"
  }
}

در اینجا می توانید ببینید که خروجی فقط به 2 محصول محدود شده است، اما اگر اسکریپت را همانطور که هست اجرا کنید، باید 98 محصول دریافت کنید.

مقاله پیشنهادی: رمزگذاری و رمزگشایی فایل ها با استفاده از پایتون

 +  اضافه کردن اطلاعات جدید به اکسل

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

به اولین فایل اکسلی که ایجاد کردید (hello_world.xlsx) برگردید و سعی کنید آن را باز کنید و داده هایی مانند این را به آن اضافه کنید:

from openpyxl import load_workbook

# Start by opening the spreadsheet and selecting the main sheet
workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active

# Write what you want into a specific cell
sheet["C1"] = "writing ;)"

# Save the spreadsheet
workbook.save(filename="hello_world_append.xlsx")

اگر فایل جدید را باز کنید نتیجه به شکل زیر خواهد بود:

 #  نوشتن اطلاعات به اکسل در پایتون

چیزهای مختلفی وجود دارد که می توانید در یک صفحه گسترده بنویسید، از متن ساده یا عدد گرفته تا فرمول های پیچیده، نمودارها یا حتی تصاویر.

 +  ایجاد کردن یک فایل اکسل ساده

قبلاً یک مثال بسیار سریع از نحوه نوشتن "Hello world" در یک صفحه گسترده دیدیم، بنابراین می توانید با آن شروع کنید:

from openpyxl import Workbook

filename = "hello_world.xlsx"
workbook = Workbook()
sheet = workbook.active
sheet["A1"] = "hello"
sheet["B1"] = "world!"
workbook.save(filename=filename)

توضیح کد بالا:

• خط 5 نشان میدهد که چطور میتوانید یک فایل خالی اکسل ایجاد کنید.
• خط 8 و 9 نشان میدهد که چطور میتوانید سلول ها را مقداردهی کنید.
• خط 11 نشان میدهد که چطور باید بعد از اتمام کار فایل را ذخیره کنید.

کار دیگری که میتوانیم برای ساده تر کردن کدمان انجام دهیم اضافه کردن تابع زیر است:

>>> def print_rows():
...     for row in sheet.iter_rows(values_only=True):
...         print(row)

این کار باعث میشود که با فراخوانی تابع بالا، تمام مقادیر فایل اکسل چاپ شود.

مقاله پیشنهادی: ساخت ماشین حساب گرافیکی در پایتون

 +  اضافه کردن و آپدیت کردن مقادیر سلول ها

قبلاً یاد گرفتید که چگونه به صفحه گسترده مقادیری مانند این اضافه کنید:

>>> sheet["A1"] = "value"

راه دیگری برای انجام این کار وجود دارد، ابتدا یک سلول را انتخاب کرده و سپس مقدار آن را تغییر دهید:

>>> cell = sheet["A1"]
>>> cell
<Cell 'Sheet'.A1>

>>> cell.value
'hello'

>>> cell.value = "hey"
>>> cell.value
'hey'

مقدار جدید فقط زمانی در اکسل ذخیره می‌شود که workbook.save را فراخوانی کنید.

openpyxl هنگام اضافه کردن یک مقدار، در صورتی که آن سلول از قبل وجود نداشته باشد یک سلول جدید ایجاد می کند:

>>> # Before, our spreadsheet has only 1 row
>>> print_rows()
('hello', 'world!')

>>> # Try adding a value to row 10
>>> sheet["B10"] = "test"
>>> print_rows()
('hello', 'world!')
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, 'test')

همانطور که می بینید، هنگام تلاش برای اضافه کردن یک مقدار به سلول B10، ده تاپل خالی ایجاد شده، فقط برای اینکه بتوانید آن مقدار test را داشته باشید.

 +  مدیریت کردن ردیف ها و ستون های اکسل

یکی از رایج ترین کارهایی که هنگام دستکاری فایل های اکسل باید انجام دهید اضافه کردن یا حذف ردیف ها و ستون ها است. پکیج openpyxl به شما امکان می دهد این کار را به روشی بسیار ساده با استفاده از متد های زیر انجام دهید:

• insert_rows
• delete_rows
• insert_cols
• delete_cols

تمام متدها بالا دو آرگومان میگیرند:

1. idx
2. amount

با استفاده از hello_world.xlsx، بیایید ببینیم این متد ها چگونه کار می کنند:

>>> print_rows()
('hello', 'world!')

>>> # Insert a column before the existing column 1 ("A")
>>> sheet.insert_cols(idx=1)
>>> print_rows()
(None, 'hello', 'world!')

>>> # Insert 5 columns between column 2 ("B") and 3 ("C")
>>> sheet.insert_cols(idx=3, amount=5)
>>> print_rows()
(None, 'hello', None, None, None, None, None, 'world!')

>>> # Delete the created columns
>>> sheet.delete_cols(idx=3, amount=5)
>>> sheet.delete_cols(idx=1)
>>> print_rows()
('hello', 'world!')

>>> # Insert a new row in the beginning
>>> sheet.insert_rows(idx=1)
>>> print_rows()
(None, None)
('hello', 'world!')

>>> # Insert 3 new rows in the beginning
>>> sheet.insert_rows(idx=1, amount=3)
>>> print_rows()
(None, None)
(None, None)
(None, None)
(None, None)
('hello', 'world!')

>>> # Delete the first 4 rows
>>> sheet.delete_rows(idx=1, amount=4)
>>> print_rows()
('hello', 'world!')

تنها چیزی که باید به خاطر بسپارید این است که هنگام درج داده های جدید (ردیف ها یا ستون ها)، درج قبل از پارامتر idx اتفاق می افتد.

 +  مدیریت کردن برگه های اکسل

درست است که مدیریت کردن برگه ها کمتر استفاده میشود اما بهتر است آن را بدانید. اگر به نمونه کدهای این آموزش نگاهی بیاندازید، متوجه کد تکراری زیر خواهید شد:

sheet = workbook.active

این راهی برای انتخاب صفحه پیش فرض از یک صفحه اکسل است. با این حال، اگر صفحه‌گسترده‌ای را با چندین صفحه باز می‌کنید، همیشه می‌توانید برگه خاصی را مانند کد زیر انتخاب کنید:

>>> # Let's say you have two sheets: "Products" and "Company Sales"
>>> workbook.sheetnames
['Products', 'Company Sales']

>>> # You can select a sheet using its title
>>> products_sheet = workbook["Products"]
>>> sales_sheet = workbook["Company Sales"]

همچنین میتوانید عنوان  برگه را به راحتی عوض کنید:

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> products_sheet.title = "New Products"

>>> workbook.sheetnames
['New Products', 'Company Sales']

اگر میخواهید یک برگه را حذف کنید یا یک برگه جدید ایجاد کنید میتوانید از متدهای create_sheet و remove استفاده کنید:

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> operations_sheet = workbook.create_sheet("Operations")
>>> workbook.sheetnames
['Products', 'Company Sales', 'Operations']

>>> # You can also define the position to create the sheet at
>>> hr_sheet = workbook.create_sheet("HR", 0)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales', 'Operations']

>>> # To remove them, just pass the sheet as an argument to the .remove()
>>> workbook.remove(operations_sheet)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales']

>>> workbook.remove(hr_sheet)
>>> workbook.sheetnames
['Products', 'Company Sales']

 +  استایل دهی به اکسل

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

همچنین می‌توانید انتخاب کنید که یک استایل را مستقیماً به یک سلول اعمال کنید یا یک الگو ایجاد کنید و از آن برای اعمال استایل ها به سلول‌های متعدد استفاده مجدد کنید.

بیایید با نگاهی به استایل ساده سلولی شروع کنیم و دوباره از sample.xlsx خود به عنوان صفحه‌گسترده پایه استفاده کنیم:

>>> # Import necessary style classes
>>> from openpyxl.styles import Font, Color, Alignment, Border, Side

>>> # Create a few styles
>>> bold_font = Font(bold=True)
>>> big_red_text = Font(color="00FF0000", size=20)
>>> center_aligned_text = Alignment(horizontal="center")
>>> double_border_side = Side(border_style="double")
>>> square_border = Border(top=double_border_side,
...                        right=double_border_side,
...                        bottom=double_border_side,
...                        left=double_border_side)

>>> # Style some cells!
>>> sheet["A2"].font = bold_font
>>> sheet["A3"].font = big_red_text
>>> sheet["A4"].alignment = center_aligned_text
>>> sheet["A5"].border = square_border
>>> workbook.save(filename="sample_styles.xlsx")

اگر صفحه گسترده خود را باز کنید، باید چند سبک مختلف را در 5 خانه اول ستون A مشاهده کنید:

 +  اضافه کردن نمودار به اکسل

یکی دیگر از کارهای قدرتمندی که می توانید با صفحات گسترده انجام دهید، ایجاد تنوع باورنکردنی از نمودارها است.

نمودارها یک راه عالی برای تجسم و درک سریع مقدار زیادی از داده ها هستند. انواع مختلفی از نمودار وجود دارد: نمودار میله ای، نمودار دایره ای، نمودار خطی و غیره. openpyxl از بسیاری از آنها پشتیبانی می کند.

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

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

workbook = Workbook()
sheet = workbook.active

# Let's create some sample sales data
rows = [
    ["Product", "Online", "Store"],
    [1, 30, 45],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    sheet.append(row)

اکنون یک نمودار میله ای ایجاد کرده که تعداد کل فروش هر محصول را نشان می دهد:

chart = BarChart()
data = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=8,
                 min_col=2,
                 max_col=3)

chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, "E2")
workbook.save("chart.xlsx")

در زیر می توانید یک نمودار میله ای بسیار ساده را مشاهده کنید که تفاوت بین فروش آنلاین محصول و فروش محصول در فروشگاه را نشان می دهد:

به راحتی توانستید نمودارهایی زیبا را به فایل اکسل اضافه کنید.

 #  نتیجه گیری

بعد از یک مقاله طولانی یاد گرفتید که چطور در پایتون با فایل های اکسل کار کنید. در این آموزش مهم ترین مواردی که نیاز است هنگام کار با فایل های اکسل در پایتون بلد باشید را برای شما آوردیم.