CC BY
31Muhammad al-Xorazmiy nomidagi TATU Farg'ona filiali "Al-Farg'oniy avlodlari" elektron ilmiy jurnali ISSN 2181-4252 Tom: 1 | Son: 4 | 2023-yil
"Descendants of Al-Farghani" electronic scientific journal of Fergana branch of TATU named after Muhammad al-Khorazmi. ISSN 2181-4252 Vol: 1 | Iss: 4 | 2023 year
Электронный научный журнал "Потомки Аль-Фаргани" Ферганского филиала ТАТУ имени Мухаммада аль-Хоразми ISSN 2181-4252 Том: 1 | Выпуск: 4 | 2023 год
Leveraging Python for Enhanced Excel Functionality: A Practical Exploration
I
Introduction: In the realm of data analysis and automation, Python has emerged as a go-to programming language for its simplicity, versatility, and extensive library support. Microsoft Excel, with its widespread use in business and academia, provides a familiar interface for users. The integration of Python with Excel combines the strengths of both, offering enhanced functionality and efficiency. This article explores the synergy between Python and Excel, highlighting their collaborative potential across various problem-solving scenarios.
On August 22, 2023, Microsoft unveiled the inclusion of the Python programming language in the beta version of Microsoft 365's Excel, aiming to enhance data analysis and visualization capabilities. According to Stefan Kinnestrand, the General Manager at Microsoft, users can now manipulate and analyze data in Excel using Python libraries and graphical representations, and subsequently refine their insights
Soliyev B.,
Senior lecturer of the Fergana branch of the Tashkent University of Information Technologies named after
Muhammad al-Khorazmi
through Excel formulas, charts, and pivot tables. The integration allows advanced data analysis within Excel's familiar interface, with direct access to Python from the Excel ribbon.
Microsoft clarified that users won't have to install additional software or configure add-ins, as Python integration is seamlessly incorporated into the built-in Excel and Power Query connectors. Additionally, a new PY function has been introduced, enabling the display of Python data in an Excel spreadsheet grid. Through a collaboration with Anaconda, the enterprise Python repository, widely-used Python libraries like pandas, statsmodels, and Matplotlib will soon be accessible to all Microsoft 365 users within Excel.
"I'm delighted that this excellent tight integration of Python and Excel has now seen the light of day. I expect that both communities will find new and exciting applications in this collaboration,
267
Odilov A.,
Student of the Fergana branch of the Tashkent University of Information Technologies named after Muhammad al-
Khorazmi
Abdurasulova Sh.,
Student of the Fergana branch of the Tashkent University of Information Technologies named after Muhammad al-
Khorazmi
Abstract: In today's data-driven world, the synergy between Python, a versatile programming language, and Microsoft Excel, a ubiquitous spreadsheet application, has become increasingly vital. This article delves into the integration of Python with Excel, presenting a comprehensive overview of its applications across various problem domains. From automating repetitive tasks to performing complex data analysis, the collaboration between Python and Excel proves to be a powerful solution. This article explores the seamless integration, discusses relevant literature, presents a methodology for implementation, showcases results through real-world examples, and concludes with insights into the potential benefits and future developments.
Keywords: Python, Microsoft Excel, Data Analysis, Automation, Integration, Spreadsheet, Programming, Data Visualization, Data Processing.
Muhammad al-Xorazmiy nomidagi TATU Farg'ona filiali "Al-Farg'oniy avlodlari" elektron ilmiy jurnali ISSN 2181-4252 Tom: 1 | Son: 4 | 2023-yil
"Descendants of Al-Farghani" electronic scientific journal of Fergana branch of TATU named after Muhammad al-Khorazmi. ISSN 2181-4252 Vol: 1 | Iss: 4 | 2023 year
Электронный научный журнал "Потомки Аль-Фаргани" Ферганского филиала ТАТУ имени Мухаммада аль-Хоразми ISSN 2181-4252 Том: 1 | Выпуск: 4 | 2023 год
expanding the capabilities of each partner. When I joined Microsoft three years ago, I had no idea that this was possible," explained Python creator Guido van Rossum, who has been an engineer emeritus at Microsoft since November 2020.
Microsoft clarified that the inclusion of Python in Excel will be part of the Microsoft 365 subscription during the preview testing phase of the updated product. However, following the conclusion of internal testing, certain features will be restricted unless accessed through a paid license.
Restrictions in the integration of Python and Excel include running exclusively in the cloud, utilizing protected libraries from Anaconda, disallowing network access, preventing access to user tokens, and restricting Python code from accessing other file properties such as formulas, charts, pivot tables, macros, or VBA code.
Literature Review: The integration of Python with Microsoft Excel has been a subject of interest for researchers and practitioners alike. Numerous studies have demonstrated the advantages of using Python to augment Excel's capabilities. Research indicates that this collaboration facilitates seamless data manipulation, analysis, and visualization. Notable works have explored the implementation of Python scripts to automate repetitive tasks, improve data accuracy, and enable advanced analytics within the Excel environment.
Methodology: Implementing Python with Excel involves several approaches, such as using external libraries like openpyxl and pandas for data manipulation, or leveraging tools like xlwings for a bidirectional interface between Python and Excel. This section outlines a practical methodology for integrating Python with Excel, covering the installation of necessary libraries, script development, and execution within the Excel environment. Additionally, it explores the utilization of Python for automating routine tasks, handling large datasets, and generating dynamic visualizations.
Results: To illustrate the effectiveness of Python-Excel integration, this section presents real-world examples and outcomes. Examples range from simple tasks like automating cell formatting to more complex scenarios such as conducting statistical
analysis and creating interactive dashboards. Screenshots, code snippets, and visual representations showcase the tangible benefits of utilizing Python for data manipulation and analysis within the Excel framework.
Below are a few examples of Python code snippets that demonstrate the integration of Python with Microsoft Excel using different libraries.
1. Using openpyxl for basic Excel operations:
import openpyxl
# Create a new Excel workbook workbook = openpyxl.Workbook()
# Access the default sheet sheet = workbook.active
# Write data to cells sheet['A1'] = 'Hello' sheet['B1'] = 'World'
# Save the workbook workbook.save('example.xlsx')
2. Using pandas for data manipulation: import pandas as pd
# Read data from Excel into a DataFrame df = pd.readexcel ('example.xlsx )
# Perform data manipulation (e.g., filtering, sorting)
filtered data = df[df['Column1'] > 10].sort_values(by='Column2')
# Write the modified DataFrame back to Excel filtered_data.to_excel('modified_example.xlsx',
index=False)
3. Using xlwings for bidirectional communication:
import xlwings as xw
268
Muhammad al-Xorazmiy nomidagi TATU Farg'ona filiali "Al-Farg'oniy avlodlari" elektron ilmiy jurnali ISSN 2181-4252 Tom: 1 | Son: 4 | 2023-yil
"Descendants of Al-Farghani" electronic scientific journal of Fergana branch of TATU named after Muhammad al-Khorazmi. ISSN 2181-4252 Vol: 1 | Iss: 4 | 2023 year
Электронный научный журнал "Потомки Аль-Фаргани" Ферганского филиала ТАТУ имени Мухаммада аль-Хоразми ISSN 2181-4252 Том: 1 | Выпуск: 4 | 2023 год
# Connect to the active Excel instance wb = xw.Book()
# Access the active sheet sheet = wb.sheets.active
# Write data from Python to Excel data_to_write = [[1, 2, 3], [4, 5, 6]] sheet.range('A1').value = data_to_write
# Read data from Excel to Python dataread = sheet.range('A1').expand().value print(data_read)
These examples provide a glimpse into the capabilities of Python when integrated with Microsoft Excel.
Step by step in Python using the openpyxl library for creating an Excel file, adding data, performing calculations, and formatting cells. Step 1: Install openpyxl
If you haven't installed the openpyxl library, you can do so using the following command:
pip install openpyxl
Step 2: Create an Excel file and sheet
import openpyxl
# Create a new Excel workbook workbook = openpyxl.Workbook()
# Access the default sheet sheet = workbook.active
# Optionally, you can also create a new sheet workbook.create_sheet("Sheet2")
# Save the workbook workbook.save('example.xlsx')
This code creates an Excel workbook with the default sheet (Sheetl) and an additional sheet named "Sheet2". The workbook is then saved as "example.xlsx".
Step 3: Add data to the sheet
# Access the default sheet sheet = workbook.active
# Add data to cells sheet['A1'] = 'Name' sheet['B1'] = 'Age' sheet['A2'] = 'John' sheet['B2'] = 25 sheet['A3'] = 'Alice' sheet['B3'] = 30
# Save the workbook workbook.save('example.xlsx')
In this step, we add headers and some sample data to the Excel sheet.
Step 4: Perform calculations
# Access the default sheet sheet = workbook.active
# Perform calculations (e.g., sum of ages) sheet['A4'] = 'Total Age'
sheet['B4'] = '=SUM(B2:B3)'
# Save the workbook workbook.save('example.xlsx')
Here, we add a total row and use a formula to calculate the sum of ages.
Step 5: Format data in cells
from openpyxl.styles import Font, Alignment
# Access the default sheet sheet = workbook.active
# Format headers for cell in sheet['A1:B1']: for col in cell:
col.font = Font(bold=True) col.alignment = Alignment(horizontal= 'center')
269
Muhammad al-Xorazmiy nomidagi TATU Farg'ona filiali "Al-Farg'oniy avlodlari" elektron ilmiy jurnali ISSN 2181-4252 Tom: 1 | Son: 4 | 2023-yil
"Descendants of Al-Farghani" electronic scientific journal of Fergana branch of TATU named after Muhammad al-Khorazmi. ISSN 2181-4252 Vol: 1 | Iss: 4 | 2023 year
Электронный научный журнал "Потомки Аль-Фаргани" Ферганского филиала ТАТУ имени Мухаммада аль-Хоразми ISSN 2181-4252 Том: 1 | Выпуск: 4 | 2023 год
# Format total row sheet['A4'].font = Font(bold=True) sheet['B4'].font = Font(bold=True,
color= "FF0000") # Red font color
sheet['B4'].alignment = Alignment(horizontal= 'center')
# Save the workbook workbook.save('example _formatted.xlsx')
This step demonstrates how to format cells, making headers bold, center-aligning text, and changing the font color of the total cell.
After running these steps, you'll have an Excel file named "example_formatted.xlsx" with multiple sheets, data, calculations, and formatted cells. Feel free to adapt these steps based on your specific requirements.
Working with diagrams in Excel can be accomplished using Python and the openpyxl library. Let's walk through the process of creating a simple chart (diagram) in an Excel sheet. Step 1: Install openpyxl If you haven't installed the openpyxl library, you can do so using the following command:
pip install openpyxl
Step 2: Create an Excel file and sheet
import openpyxl
# Create a new Excel workbook workbook = openpyxl.Workbook()
# Access the default sheet sheet = workbook.active
# Add data to cells sheet['A1'] = 'Category' sheet['B1'] = 'Value 1' sheet['C1'] = 'Value 2' sheet['A2'] = 'A' sheet['B2'] = 10 sheet['C2'] = 15 sheet['A3'] = 'B'
sheet['B3'] = 20 sheet['C3'] = 25
# Save the workbook workbook.save('examplechart.xlsx')
Step 3: Add a Bar Chart to the Excel sheet
from openpyxl.chart import BarChart, Reference
# Access the default sheet sheet = workbook.active
# Create a BarChart object chart = BarChart()
# Define the data for the chart data = Reference(sheet, min_col=2,
min_row=1, max_col=3, max_row=3)
categories = Reference(sheet, min_col=1, min_row=2, max_row=3)
# Add data to the chart chart.add_data(data, titles_from_data=True) chart.se tcategories(categories)
# Add the chart to the worksheet sheet.add_chart(chart, "E5")
# Save the workbook workbook.save('examplechart.xlsx')
In this step, we create a simple bar chart using the data in columns B and C. The chart is added to the worksheet starting from cell E5.
Step 4: Customize the Chart (Optional)
# Customize the chart chart.title = "Sample Bar Chart" chart.xaxis.title = 'Categories' chart.yaxis.title = 'Values'
# Save the workbook
workbook.save ('example_chart_customized.xls
x')
270
Muhammad al-Xorazmiy nomidagi TATU Farg'ona filiali "Al-Farg'oniy avlodlari" elektron ilmiy jurnali ISSN 2181-4252 Tom: 1 | Son: 4 | 2023-yil
"Descendants of Al-Farghani" electronic scientific journal of Fergana branch of TATU named after Muhammad al-Khorazmi. ISSN 2181-4252 Vol: 1 | Iss: 4 | 2023 year
Электронный научный журнал "Потомки Аль-Фаргани" Ферганского филиала ТАТУ имени Мухаммада аль-Хоразми ISSN 2181-4252 Том: 1 | Выпуск: 4 | 2023 год
You can customize the chart title, as well as the titles for the X and Y axes according to your preferences.
After running these steps, you'll have an Excel file named "example_chart_customized.xlsx" with a bar chart embedded in the sheet.
Conclusion: The collaboration between Python and Microsoft Excel offers a potent solution for addressing diverse challenges in data analysis and automation. This article has provided insights into the integration's practical applications, supported by a literature review, a detailed methodology, and tangible results. As organizations continue to grapple with large datasets and seek efficient ways to analyze and visualize data, the combination of Python and Excel emerges as a compelling strategy. Moving forward, the continued development of tools and libraries is expected to further enhance the capabilities of this integration, unlocking new possibilities for users across various domains.
References
1. Soliev B. N., kizi Abdurasulova D. B., Yakubov M. S. USING GINJA TEMPLATES TO CREATE E-COMMERCE PLATFORMS //Publishing House "Baltija Publishing". - 2023.
2. Elevating E-Commerce in Uzbekistan with Python. (2023). Journal of Technical Research and Development, 1(1), 43-45. https://jtrd.mcdir.me/index.php/jtrd/article/view/2
3. Navigating the E-Commerce Landscape in Uzbekistan with Python. (2023). Journal of Technical Research and Development, 1(1), 46-50. https://jtrd.mcdir.me/index.php/jtrd/article/view/1
4. Uzbekistan's Digital Market: Python's E-Commerce Impact. (2023). Journal of Technical Research and Development, 1(1), 58-61. https://jtrd.mcdir.me/index.php/jtrd/article/view/5
5. Python's Role in Revolutionizing ECommerce in Uzbekistan. (2023). Journal of Technical Research and Development, 1(1), 51-54. https://jtrd.mcdir.me/index.php/jtrd/article/view/4
6. Nabijonovich S. B. EMPOWERING VIDEO ANALYTICS WITH AI-DRIVEN TEXT RECOGNITION IN PYTHON FOR STREAMLINED
INSIGHTS //Galaxy International Interdisciplinary Research Journal. - 2023. - Т. 11. - №. 11. - С. 25-30.
7. https://letsl earnabout.net/blog/what-is-django-rest-framework-and-why-you-should-learn-it/
8. https://mkdev.me/ru/posts/chto-takoe-dj ango-rest-framework
9. https://habr.com/ru/news/756266/
271
