ویرایش فایل اکسل با پایتون (python)

مدیران دیتابیس ها معمولاً داده های مورد نیاز را در یک فایل اکسل به اشتراک می گذارند ، قالب بندی را انجام می دهند و آنها را با مقامات مربوطه به اشتراک می گذارند. این یک تمرین منظم است. پایتون به خوبی با Microsoft Excel ادغام می شود.

این مطلب شامل موضوعات زیر است:

• با استفاده از Python یک نمونه فایل اکسل ایجاد کنید
• با استفاده از Python داده ها را از یک فایل اکسل وارد کنید
• با استفاده از Python داده ها را در صفحه اکسل قالب بندی کنید

ابزارها

کتابخانه XlsxWriter: ما برای نوشتن پرونده های Microsoft Excel با فرمت XLSX از ماژول XlsxWriter Python استفاده می کنیم. این گزینه های مختلف قالب بندی ، نمودارها ، جعبه های متن ، رشته های چند فرمتی و ماکرو برای فایل های اکسل را فراهم می کند.

کتابخانه Xlrd: یک کتابخانه پایتون برای خواندن داده ها از فایل های اکسل می باشد. این برنامه از هر دو فرمت XLS و XLSX برای خواندن داده ها و قالب بندی اطلاعات از فایل های اکسل پشتیبانی می کند.

ماژول XlsxWriter و xlrd Python را با استفاده از ابزار pip نصب کنید:

pip install XlsxWriter
pip install xlrd

ایجاد فایل اکسل

بیایید یک نمونه فایل اکسل بدون هیچ گونه قالب بندی ایجاد کنیم. کد زیر را در پایتون اجرا کنید:

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocme.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Welcome to Python')
workbook.close()

کد بالا یک فایل اکسل با عنوان Welcome.xlsx در پوشه C: \ temp ایجاد می کند، یک صفحه کار جدید در آن اضافه می کند، متن "Welcome to Python" را در ستون A1 می نویسد و کتاب کار را می بندد.

اگر فایل Welcome.xlsx را باز کنید داده های وارد شده توسط کد پایتون را نشان می دهد.

تقسیم کلمات بین ستون ها

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

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocme.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Welcome')
worksheet.write('B1', 'To')
worksheet.write('C1', 'Python')
workbook.close()

هنگامی که کد بالا را اجرا کردیم ، فایل Welcome.xlsx را بازنویسی می کند:

ورود داده ها در چندین ردیف و ستون همراه با سر ستون

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

ستون ها را با استفاده از worksheet.write تعریف می کنیم:

worksheet.write('A1', 'Name')
worksheet.write('B1', 'Department')

برای ردیف ها و ستون ها شاخص را شروع می کنیم. برای ردیف اول و ستون اول ، از یک شمارنده نمایه سازی صفر استفاده می کند. ما نام ستون ها را در مرحله قبل مشخص کردیم ، بنابراین شمارنده من از سطر 1 و col = 0 شروع می شود.

داده های مورد نیاز را در قالب آرایه در متغیر data می نویسیم. این شامل داده هایی است که می خواهیم در اکسل نمایش دهیم:

data = (
['Yashar', 'IT'],
['Mehdi','Physiotherapist'],
['Mitra', 'Student'],
['Yasaman','Bank Manager'],)

کد زیر شامل یک حلقه FOR برای عبور از هر سطر و ستون است:

for name, score in (data):
worksheet.write(row, col, name)
worksheet.write(row, col + 1, score)
row += 1

بنابراین اسکریپت کامل به صورت یر خواهد بود:

نویسه های پررنگ (Bold) با استفاده از اسکریپت های پایتون

در بسیاری از موارد ، ما نام ستون ها را با حروف پررنگ ایجاد می کنیم تا کاربران بتوانند ستون را با داده های واقعی متفاوت کنند. همانطور که در زیر نشان داده شده است ، در پایتون ویژگی bold را فعال می کنیم:

bold = workbook.add_format({'bold': True})

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

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welcome.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Name', bold)
worksheet.write('B1', 'Department', bold)
row = 1
col = 0
data = (['Yashar', 'IT'],['Mehdi','Physiotherapist'],['Mitra', 'Student'],['Yasaman','Bank Manager'],)
for name, score in (data):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, score)
    row += 1
workbook.close()

می توانید ستون های Name و Department را با حروف درشت مشاهده کنید:

تغییر عرض ستون های اکسل

ما می توانیم عرض ستون را برای سلولهای اکسل در پایتون تعریف کنیم. برای این کار از تابع worksheet.set_column همراه با عرض ستون استفاده می کنیم.

worksheet.set_column('B:B', 60)

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

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocme.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Name', bold)
worksheet.write('B1', 'Department', bold)
row = 1
col = 0
data = (['Yashar', 'Hi, You are on iYashar.ir'],['Mehdi','How do you get to see a physiotherapist?'],
'Mitra', 'I am a student of class 1 in Hafez primary school.'],['Yasaman','Are you a Bank Manager?'],)
worksheet.set_column('B:B', 60)
for name, score in (data):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, score)
    row += 1
workbook.close()

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

تغییر رنگ و اندازه قلم

اکنون ، بیایید تغییر رنگ و اندازه قلم را برای سر ستون ها بررسی کنیم.

ما رنگ فونت را با استفاده از font_color و اندازه قلم را با استفاده از متغیرهای set_font_size در اسکریپت های پایتون تعریف می کنیم. اکنون رنگ قرمز را برای سر ستون با اندازه قلم 16 اضافه کنیم:

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocm.xlsx')
worksheet = workbook.add_worksheet()
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
cell_format.set_font_size(16)
worksheet.write('A1', 'Name', cell_format)
worksheet.write('B1', 'Department', cell_format)
row = 1
col = 0
data = (['Yashar', 'Hi, You are on iYashar.ir'],['Mehdi','How do you get to see a physiotherapist?'],['Mitra', 'I am a student of class 1 in Hafez primary school.'],['Yasaman','Are you a Bank Manager?'],)
worksheet.set_column('B:B', 60)
worksheet.set_column('B:B', 60)
for name, score in (data):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, score)
    row += 1
workbook.close()

اجرای کد، خروجی زیر را ایجاد می کند:

اضافه کردن یک زیر خط برای سر ستون

برای اضافه کردن زیر خط از تابع cell_format.set_underline استفاده می نماییم.

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

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocme1.xlsx')
worksheet = workbook.add_worksheet()
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
cell_format.set_font_size(16)
cell_format.set_underline()
worksheet.write('A1', 'Name', cell_format)
worksheet.write('B1', 'Department', cell_format)
row = 1
col = 0
data = (['Yashar', 'Hi, You are on iYashar.ir'],['Mehdi','How do you get to see a physiotherapist?'],['Mitra', 'I am a student of class 1 in Hafez primary school.'],['Yasaman','Are you a Bank Manager?'],)
worksheet.set_column('B:B', 60)
worksheet.set_column('B:B', 60)
for name, score in (data):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, score)
    row += 1
workbook.close()

خروجی به شکل زیر خواهد بود :

زیر خط حالت های مختلفی دارد:

1 = قالب پیش فرض

2 = دوبار زیر خط زدن

33 = زیر خط حسابداری واحد

34 = زیر حسابداری دوگانه

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

cell_format.set_underline(34)

اگر قالب را به دو خط زیرین تغییر دهیم ، خروجی زیر را می گیرید:

cell_format.set_underline(2)

تراز بندی متن

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

سر صفحه باید تراز وسط باشد، ردیف های دیگر رنگ داده باید به رنگ آبی و تراز وسط باشد:

در این حالت ، ما دو قالب داریم - یکی برای هدر ستون و دیگری برای بقیه ستون ها. در اسکریپت های Python زیر ، cell_format و cell_format1 را تعریف کردیم:

در Cell_format ، یک خط دیگر برای ترازبندی قلم اضافه می کنیم.

cell_format.set_align('center')

در قالب دیگری ، ما با استفاده از ویژگی font_color رنگ قلم را تعریف کردیم و تراز را روی مرکز تنظیم کردیم.

cell_format1 = workbook.add_format({'font_color': 'blue'})
cell_format1.set_align('center')

اکنون دو نوع قالب متن داریم. ما باید آنها را فقط در ستون های مناسب اعمال کنیم. به عنوان مثال سرآیند ستون ها همانند مثال قبلی به نظر می رسد با این تفاوت که تراز شده در مرکز است. ما برای سر ستون ها از cell_format استفاده می کنیم:

cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
cell_format.set_font_size(16)
cell_format.set_underline(2)
cell_format.set_align('center')

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

cell_format1 = workbook.add_format({'font_color': 'blue'})
cell_format1.set_align('center')

این قالب سلول جدید را برای داده های A1 تا A5 و B2 تا B5 با استفاده از تابع worksheet.set_column اعمال می کنیم:

worksheet.set_column('B2:B5',60,cell_format1)
worksheet.set_column('A1:A5', 20,cell_format1)

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

import xlsxwriter
workbook = xlsxwriter.Workbook('c:\\temp\\Welocme.xlsx')
worksheet = workbook.add_worksheet()
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
cell_format.set_font_size(16)
cell_format.set_underline(2)
cell_format.set_align('center')
cell_format1 = workbook.add_format({'font_color': 'blue'})
cell_format1.set_align('center')
worksheet.write('A1', 'Name', cell_format)
worksheet.write('B1', 'Department', cell_format)
row = 1
col = 0
data = (['Yashar', 'Hi, You are on iYashar.ir'],['Mehdi','How do you get to see a physiotherapist?'],['Mitra', 'I am a student of class 1 in Hafez primary school.'],['Yasaman','Are you a Bank Manager?'],)
worksheet.set_column('B1:B1', 60)
worksheet.set_column('B2:B5',60,cell_format1)
worksheet.set_column('A1:A5', 20,cell_format1)
for name, score in (data):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, score)
    row += 1
workbook.close()

نتیجه

در این مقاله ، ما اسکریپت های پایتون را برای قالب بندی داده ها در Microsoft Excel بررسی کردیم. پایتون یک زبان قدرتمند است و ما می توانیم کارهای زیادی را با چند خط کد انجام دهیم.

منبع: SQLSHACK