کار با فایل های اکسل (بخش دوم)

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

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

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

در این آموزش، شما یاد می گیرید که چگونه از openpyxl استفاده کنید:

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

کار با فایل های اکسل (بخش اول)

وارد کردن داده ها از صفحه گسترده

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

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

تکرار از طریق داده ها

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

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

>>> 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[۵]
(<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[۵:۶]
((<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>))

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

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

.iter_rows()
.iter_cols()

هر دو روش می توانند آرگومان های زیر را دریافت کنند:

min_row
max_row
min_col
max_col

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

>>> for row in sheet.iter_rows(min_row=۱,
...                            max_row=۲,
...                            min_col=۱,
...                            max_col=۳):
...     print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>)
(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)


>>> for column in sheet.iter_cols(min_row=۱,
...                               max_row=۲,
...                               min_col=۱,
...                               max_col=۳):
...     print(column)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>)
(<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>)
(<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>)

مشاهده خواهید کرد که در مثال اول، هنگام تکرار از طریق ردیف ها با استفاده از iter_rows ، یک عنصر tuple در هر ردیف انتخاب می کنید. در حالی که هنگام استفاده از iter_cols و تکرار از طریق ستون ها، درعوض یک ستون برای هر ستون دریافت خواهید کرد.

یک آرگومان اضافی که می توانید به هر دو روش منتقل کنید مقادیر Boolean_only است. وقتی روی True تنظیم شود، مقادیر سلول به جای شی C Cell برمی گردند:

>>> for value in sheet.iter_rows(min_row=۱,
...                              max_row=۲,
...                              min_col=۱,
...                              max_col=۳,
...                              values_only=True):
...     print(value)
('marketplace', 'customer_id', 'review_id')
('US', 3653882, 'R3O9SGZBVQBV76')

اگر می خواهید از کل مجموعه داده تکرار شود، می توانید از ویژگی های rows یا column به طور مستقیم استفاده کنید، که میانبرهای استفاده از iter_rows و iter_cols هستند بدون هیچ آرگومانی:

>>> for row in sheet.rows:
...     print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>
...
<Cell 'Sheet 1'.M100>, <Cell 'Sheet 1'.N100>, <Cell 'Sheet 1'.O100>)

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

تغییر داده ها با استفاده از ساختارهای پیش فرض داده Python

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

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

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

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

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

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

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

product_id
product_parent
product_title
product_category

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

>>> for value in sheet.iter_rows(min_row=۲,
...                              min_col=۴,
...                              max_col=۷,
...                              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=۲,
                           min_col=۴,
                           max_col=۷,
                           values_only=True):
    product_id = row[۰]
    product = {
        "parent": row[۱],
        "title": row[۲],
        "category": row[۳]
    }
    products[product_id] = product

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

کد بالا JSON مشابه این را برمی گرداند:

{
  "B00FALQ1ZC": {
    "parent": ۹۳۷۰۰۱۳۷۰,
    "title": "Invicta Women's 15150 ...",
    "category": "Watches"
  },
  "B00D3RGO20": {
    "parent": ۴۸۴۰۱۰۷۲۲,
    "title": "Kenneth Cole New York ...",
    "category": "Watches"
  }
}

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

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

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

برای این منظور ، از کلاسهای داده جدید Python که از Python 3.7 در دسترس هستند استفاده خواهید کرد. اگر از نسخه قدیمی Python استفاده می کنید، می توانید به جای آن از کلاسهای پیش فرض استفاده کنید.

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

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

دو عنصر قابل توجه وجود دارد که می توانید از داده های موجود استخراج کنید:

Products
Reviews

یک محصول دارای موارد زیر می باشد:

ID
Title
Parent
Category

این بازبینی چند قسمت دیگر دارد:

ID
Customer ID
Stars
Headline
Body
Date

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

بنابراین، اجرای ساده این دو کلاس می تواند در یک فایل کلاس جداگانه نوشته شود. classes.py:

import datetime
from dataclasses import dataclass

@dataclass
class Product:
    id: str
    parent: str
    title: str
    category: str

@dataclass
class Review:
    id: str
    customer_id: str
    stars: int
    headline: str
    body: str
    date: datetime.datetime

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

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

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

>>> # Or an alternative
>>> for cell in sheet[۱]:
...     print(cell.value)
marketplace
customer_id
review_id
product_id
product_parent
...

بیایید یک فایل mapping.py ایجاد کنیم که در آن لیستی از تمام نام های زمینه و محل ستون آنها (با صفر اندیس گزاری شده) در صفحه گسترده وجود دارد:

# Product fields
PRODUCT_ID = ۳
PRODUCT_PARENT = ۴
PRODUCT_TITLE = ۵
PRODUCT_CATEGORY = ۶

# Review fields
REVIEW_ID = ۲
REVIEW_CUSTOMER = ۱
REVIEW_STARS = ۷
REVIEW_HEADLINE = ۱۲
REVIEW_BODY = ۱۳
REVIEW_DATE = ۱۴

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

from datetime import datetime
from openpyxl import load_workbook
from classes import Product, Review
from mapping import PRODUCT_ID, PRODUCT_PARENT, PRODUCT_TITLE, \
    PRODUCT_CATEGORY, REVIEW_DATE, REVIEW_ID, REVIEW_CUSTOMER, \
    REVIEW_STARS, REVIEW_HEADLINE, REVIEW_BODY

# Using the read_only method since you're not gonna be editing the spreadsheet
workbook = load_workbook(filename="sample.xlsx", read_only=True)
sheet = workbook.active

products = []
reviews = []

# Using the values_only because you just want to return the cell value
for row in sheet.iter_rows(min_row=۲, values_only=True):
    product = Product(id=row[PRODUCT_ID],
                      parent=row[PRODUCT_PARENT],
                      title=row[PRODUCT_TITLE],
                      category=row[PRODUCT_CATEGORY])
    products.append(product)

    # You need to parse the date from the spreadsheet into a datetime format
    spread_date = row[REVIEW_DATE]
    parsed_date = datetime.strptime(spread_date, "%Y-%m-%d")

    review = Review(id=row[REVIEW_ID],
                    customer_id=row[REVIEW_CUSTOMER],
                    stars=row[REVIEW_STARS],
                    headline=row[REVIEW_HEADLINE],
                    body=row[REVIEW_BODY],
                    date=parsed_date)
    reviews.append(review)

print(products[۰])
print(reviews[۰])

پس از اجرای کد بالا، باید خروجی مانند این را بدست آورید:

Product(id='B00FALQ1ZC', parent=۹۳۷۰۰۱۳۷۰, ...)
Review(id='R3O9SGZBVQBV76', customer_id=۳۶۵۳۸۸۲, ...)

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

مطالب پیشنهادی برای شما

۱۲

About
Latest Posts

محمد صداقتی

"علاقمند به تکنولوژی"
از سال ۸۸ وارد دنیای الکترونیک و فناوری اطلاعات شدم و در زمینه مشاوره، طراحی و ساخت پروژه‌های سخت‌افزاری فعالیت می‌کنم.
تجربه برنامه نویسی به زبان های پایتون و سی و همچنین کار با انواع میکروکنترلرها و بردهای سخت افزاری را دارم.

Latest posts by محمد صداقتی (see all)

تفاوت بین روتر و فایروال - مهر ۱۰, ۱۴۰۳
مسیریابی استاتیک در ویندوز - مهر ۸, ۱۴۰۳
پروتکل SNMP - شهریور ۱۵, ۱۴۰۳

exell, micro learning, openpyxl, python, python GUI, Raspberry, Raspberry Pi, آموزش پایتون, اکسل در پایتون, پایتون, دیتا لاگر, دیتابیس, گیلان, محمد صداقتی, میکرو آموزش

پیشرونیک