The Complete Guide to Python Openpyxl for Beginners in 2020

In this tutorial, we will learn about the following topics:

• Openpyxl Introduction
• Openpyxl Working Process
• Openpyxl Installation
• Openpyxl Write Data to Cell
• Openpyxl Reading Excel File
• Openpyxl Read Data from Cell
• Openpyxl Read Multiple Cells
• Openpyxl Sheets
• Openpyxl Filter and sort data
• Openpyxl Merging cells
• Openpyxl Append Values
• Openpyxl Iterate by Column
• Openpyxl Iterate by rows
• Openpyxl Adding chart to Excel file
• Openpyxl Adding Image

---

Python Openpyxl Introduction

Python provides the Openpyxl module, which is used to deal with Excel files without involving third-party Microsoft application software. By using this module, we can have control over excel without open the application. It is used to perform excel tasks such as read data from excel file, or write data to the excel file, draw some charts, accessing excel sheet, renaming sheet, modification (adding and deleting) in excel sheet, formatting, styling in the sheet, and any other task. Openpyxl is very efficient to perform these tasks for you.

Data scientists often use the Openpyxl to perform different operations such as data copying to data mining as well as data analysis.

---

Openpyxl Working Process

The Openpyxl library is used to write or read the data in the excel file and many other tasks. An excel file that we use for operation is called Workbook that contains a minimum of one Sheet and a maximum of tens of sheets.

• Sheets consist of Rows (horizontal series) starting from 1 and Columns (vertical series) starting from A.
• Row and column together make a grid and form a cell that may store some data. Data can be of any type, such as numeric, string.
• Openpyxl provides flexibility to read data from the individual cell or write data to it.

---

Installation of Openpyxl

In the above section, we have discussed the openpyxl briefly and its working process. In order to use Openpyxl, one should have Python 3.7 and openpyxl 2.6.2 installed in the system. Let’s start working with openpyxl by installing openpyxl using the following command:

pip install openpyxl  

The xlsx is the extension of the XML spreadsheet file. The xlsx file supports macros. Let’s understand the basic operation related to the excel file. Consider the following code:

from openpyxl import Workbook  
import time  
  
wb = Workbook()  
sheet = wb.active  
  
sheet['A1'] = 87  
sheet['A2'] = "Devansh"  
sheet['A3'] = 41.80  
sheet['A4'] = 10  
  
now = time.strftime("%x")  
sheet['A5'] = now  
  
wb.save("sample_file.xlsx")  

Output:

In the above code, we have written data into the five cells A1, A2, A3, A4, and A5. These cells consist of different types of values. We have imported Workbook class from the openpyxl module. A workbook class is a container that contains all parts of the document.

Here we have defined a new workbook. At least one sheet is always made with a workbook.

wb = Workbook()  

We get the location of the active sheet.

sheet['A1'] = 87  
sheet['A2'] = 'Devansh'  

We have saved all data to the sample_file.xlsx file using the save() method.

---

Openpyxl Write Data to Cell

We can add data to the excel file using the following Python code. First, we will import the load_workbook function from the openpyxl module, then create the object of the file and pass filepath as an argument. Consider the following code:

from openpyxl import load_workbook  
wb = load_workbook(r'C:\Users\DEVANSH SHARMA\Desktop\demo.xlsx')  
  
sheet = wb.active  
sheet['A1'] = 'Devansh Sharma'  
  
sheet.cell(row=2, column=2).value = 5  
wb.save(r'C:\Users\DEVANSH SHARMA\Desktop\demo.xlsx')  

Output:

---

Openpyxl Append values

Openpyxl provides an append() method, which is used to append the group of values. We can append any type of value. These values are appended at the bottom of the current working sheet. Consider the following code:

from openpyxl import Workbook  
  
wb = Workbook()  
sheet = wb.active  
  
data = (  
    (11, 48, 50),  
    (81, 30, 82),  
    (20, 51, 72),  
    (21, 14, 60),  
    (28, 41, 49),  
    (74, 65, 53),  
    ("Peter", 'Andrew',45.63)  
)  
  
for i in data:  
    sheet.append(i)  
wb.save('appending_values.xlsx')  

Output:

---

Openpyxl Read Data from cell

We can read the data that we have previously written in the cell. There are two methods to read a cell, firstly we can access it by cell name, and secondly, we can access it by the cell() function. For example, we are reading the data from the sample_file.xlrs file.

import openpyxl  
  
wb = openpyxl.load_workbook('sample_file.xlsx')  
  
sheet = wb.active  
  
x1 = sheet['A1']  
x2 = sheet['A2']  
#using cell() function  
x3 = sheet.cell(row=3, column=1)  
  
print("The first cell value:",x1.value)  
print("The second cell value:",x2.value)  
print("The third cell value:",x3.value)  

Output:

The first cell value: 87
The second cell value: Devansh
The third cell value: 41.8

---

Openpyxl Read multiple cells

We can read the values from the multiple cells. In the following example, we have marks.xlsx named excel file and we will read each cell of file using the range operator. Let’s have a look at the following program:

import openpyxl  
  
wb = openpyxl.load_workbook('marks.xlsx')  
  
sheet = wb.active  
#  
cells = sheet['A1','B7']  
# cells behave like range operator  
for i1,i2 in cells:  
    print("{0:8} {1:8}".format(i1.value,i2.value))  

Output:

Student_name        Marks
Tony Stark           47
Loki                 59
Oddin                73
Nick Fury            62
Samaul               75
Peter Parkar         80

---

Openpyxl Iterate by rows

The openpyxl provides the iter_row() function, which is used to read data corresponding to rows. Consider the following example:

from openpyxl import Workbook  
  
wb = Workbook()  
sheet = wb.active  
  
rows = (  
    (90, 46, 48, 44),  
    (81, 30, 32, 16),  
    (23, 95, 87,27),  
    (65, 12, 89, 53),  
    (42, 81, 40, 44),  
    (34, 51, 76, 42)  
)  
  
for row in rows:  
    sheet.append(row)  
  
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=4):  
    for cell in row:  
        print(cell.value, end=" ")  
    print()  
  
book.save('iter_rows.xlsx')  

Output:

90 46 48 44 
81 30 32 16 
23 95 87 27 
65 12 89 53 
42 81 40 44 
34 51 76 42

---

Openpyxl Iterate by Column

The openpyxl provides iter_col() method which return cells from the worksheet as columns. Consider the following example:

from openpyxl import Workbook  
  
book = Workbook()  
sheet = book.active  
  
rows = (  
     (90, 46, 48, 44),  
     (81, 30, 32, 16),  
     (23, 95, 87, 27),  
     (65, 12, 89, 53),  
     (42, 81, 40, 44),  
     (34, 51, 76, 42)  
)  
for row in rows:  
    sheet.append(row)  
  
for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):  
    for cell in row:  
        print(cell.value, end=" ")  
    print()  
  
book.save('iterbycols.xlsx')  

Output:

90 81 23 65 42 34 
46 30 95 12 81 51 
48 32 87 89 40 76

---

Openpyxl Sheets

As we know that each workbook can have multiple sheets. First, we need to create more than one sheet in a single workbook then we can access those excel sheets using Python. In the following example, we have created a workbook with three sheets:

import openpyxl  
wb = openpyxl.load_workbook('dimension_1.xlsx')  
  
#Getting list of all sheet available in workbook  
print(wb.get_sheet_names())  
  
# Returning object  
active_sheet = wb.active  
print(type(active_sheet))  
  
# Title of sheet  
sheet = wb.get_sheet_by_name("Monday")  
print(sheet.title)  

Output:

['Sheet', 'Sunday', 'Monday', 'Tuesday', 'Wednesday']
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Monday

It will look like the following image.

---

Openpyxl filter and sort data

The auto_filter attribute is used to set filtering and sorting conditions. Consider the following code:

from openpyxl import Workbook  
  
wb = Workbook()  
sheet = wb.active  
  
sheet['A3'] = 40  
sheet['B3'] = 26  
  
row_count = [  
    (93,45),  
    (23,54),  
    (80,43),  
    (21,12),  
    (63,29),  
    (34,15),  
    (80,68),  
    (20,41)  
]  
  
for row in row_count:  
    sheet.append(row)  
  
print(sheet.dimensions)  
  
for a1,a2 in sheet[sheet.dimensions]:  
    print(a1.value, a2.value)  
  
sheet.auto_filter.add_sort_condition('B2:B8')  
sheet.auto_filter.add_filter_column(1, ['40', '26'])  
  
wb.save('dimension_1.xlsx')  

Output:

A3:B11
40 26
93 45
23 54
80 43
21 12
63 29
34 15
80 68
20 41

---

Openpyxl Merging cell

We can merge the cell using the merge_cells() method. When we merge the cells, the top-left one is removed from the worksheet. The openpyxl also provides the unmerged_cells() method to unmerge the cell. Consider the following code:

from openpyxl.styles import Alignment  
  
wb = Workbook()  
sheet = wb.active  
  
sheet.merge_cells('A1:B2')  
  
cell = sheet.cell(row=1, column=1)  
cell.value = 'Devansh Sharma'  
cell.alignment = Alignment(horizontal='center', vertical='center')  
  
wb.save('merging.xlsx')  

Output:

The freezing panes are simply mean to freeze an area of worksheet as visible while scrolling to other parts of the worksheet. It is useful feature where we can display the top row or leftmost column on the screen. We can do this by passing the cell name to the freeze_panes variable. To unfreeze all panes, set freeze_panes to ‘None’. Consider the following code:

from openpyxl import Workbook  
from openpyxl.styles import Alignment  
  
wb = Workbook()  
sheet = wb.active  
  
sheet.freeze_panes = 'A1'  
  
wb.save('freez_cells.xlsx')  

Output:

Run the above code and scroll the worksheet.

Openpyxl formulas

We can write formula into the cell. These formulas are used to perform operations in excel file. After writing in the cell execute it from the workbook. Consider the following example:

from openpyxl import Workbook  
wb = Workbook()  
sheet = wb.active  
  
rows_count = (  
    (14, 27),  
    (22, 30),  
    (42, 92),  
    (51, 32),  
    (16, 60),  
    (63, 13)  
)  
  
for i in rows_count:  
    sheet.append(i)  
  
cell = sheet.cell(row=7, column=3)  
cell.value = "=SUM(A1:B6)"  
cell.font = cell.font.copy(bold=True)  
  
wb.save('formulas_book.xlsx')  

Output:

Openpyxl Cell Inverter

The openpyxl cell inverter is used to invert the row and column of the cell in the spreadsheet. For example, the value at 3 rows will be inverted into 5 columns and row at row 5 will invert to column 3 (vice versa). You can see in the following images:

This program is written with the help of nested for loop. First the data structure writes in the sheetData[x][y] for the cell at column x and row y then the newly created spreadsheet in the spreadData[y][x] for the cell at column y and row x.

---

Adding Chart to Excel File

Charts are effective way to represent the data. Using the charts, it visualizes the data which can be easily untestable. There are various types of chart: pie chart, line chart, bar chart, and so on. We can draw a chart on a spreadsheet using an openpyxl module.

For building any chart on the spreadsheet, we need to define the chart type like BarChart, LineChart, and so on. We also import reference which represents data that is used for the chart. It is important to define what data we want to represent on the chart. Let’s understand by the following example:

from openpyxl import Workbook  
from openpyxl.chart import BarChart, Reference  
  
wb = Workbook()  
sheet = wb.active  
  
# Let's create some sample student data  
rows = [  
    ["Serial_no", "Roll no", "Marks"],  
    [1, "0090011", 75],  
    [2, "0090012", 60],  
    [3, "0090013", 43],  
    [4, "0090014", 97],  
    [5, "0090015", 63],  
    [6, "0090016", 54],  
    [7, "0090017", 86],  
]  
  
for i in rows:  
    sheet.append(i)  
  
chart = BarChart()  
values = Reference(worksheet=sheet,  
                 min_row=1,  
                 max_row=8,  
                 min_col=2,  
                 max_col=3)  
  
chart.add_data(values, titles_from_data=True)  
sheet.add_chart(chart, "E2")  
  
wb.save("student_chart.xlsx")  

Output:

In the above code, we have created the sample data and drawn the bar chart corresponding to sample data.

Now we will create the line chart. Consider the following code:

import random  
from openpyxl import Workbook  
from openpyxl.chart import LineChart, Reference  
  
wb = Workbook()  
spreadsheet = wb.active  
  
# Let's create some sample data  
rows = [  
    ["", "January", "February", "March", "April",  
    "May", "June", "July", "August", "September",  
     "October", "November", "December"],  
    [1, ],  
    [2, ],  
    [3, ],  
]  
  
for row in rows:  
    spreadsheet.append(row)  
  
for row in spreadsheet.iter_rows(min_row=2,  
                           max_row=4,  
                           min_col=2,  
                           max_col=13):  
    for cell in row:  
        cell.value = random.randrange(5, 100)  
  
chart = LineChart()  
data = Reference(worksheet=spreadsheet,  
                 min_row=2,  
                 max_row=4,  
                 min_col=1,  
                 max_col=13)  
  
chart.add_data(data, from_rows=True, titles_from_data=True)  
spreadsheet.add_chart(chart, "C6")  
  
wb.save("line_chart1.xlsx")  

Output:

In the above code, we are used from_rows = True as a parameter, it denotes chart plot row by row instead of the column by column.

---

Adding Image

Images are not generally used in a spreadsheet but sometimes we can use as per our requirement. We can use an image for the branding purposes or to make the spreadsheet more personal and attractive. For loading an image to spreadsheet, we need to install an additional module called pillow by the following command.

pip install pillow  

In the following program, we are importing the image into the excel file.

from openpyxl import load_workbook  
from openpyxl.drawing.image import Image  
  
# Let's use the hello_world spreadsheet since it has less data  
workbook = load_workbook(filename="student_chart1.xlsx")  
sheet = workbook.active  
  
logo = Image(r"C:\Users\DEVANSH SHARMA\Pictures\Screenshots\image.png")  
  
# A bit of resizing to not fill the whole spreadsheet with the logo  
logo.height = 150  
logo.width = 150  
  
sheet.add_image(logo, "E2")  
workbook.save(filename="hello_world_logo1.xlsx")  

In this tutorial, we have covered all basic and advance concept of openpyxl.

Thank you for reading! Please share if you liked it!

#python #openpyxl

What is GEEK

Buddha Community

Why use Python for Software Development

No programming language is pretty much as diverse as Python. It enables building cutting edge applications effortlessly. Developers are as yet investigating the full capability of end-to-end Python development services in various areas. 

By areas, we mean FinTech, HealthTech, InsureTech, Cybersecurity, and that's just the beginning. These are New Economy areas, and Python has the ability to serve every one of them. The vast majority of them require massive computational abilities. Python's code is dynamic and powerful - equipped for taking care of the heavy traffic and substantial algorithmic capacities. 

Programming advancement is multidimensional today. Endeavor programming requires an intelligent application with AI and ML capacities. Shopper based applications require information examination to convey a superior client experience. Netflix, Trello, and Amazon are genuine instances of such applications. Python assists with building them effortlessly. 

5 Reasons to Utilize Python for Programming Web Apps 

Python can do such numerous things that developers can't discover enough reasons to admire it. Python application development isn't restricted to web and enterprise applications. It is exceptionally adaptable and superb for a wide range of uses.

Robust frameworks 

Python is known for its tools and frameworks. There's a structure for everything. Django is helpful for building web applications, venture applications, logical applications, and mathematical processing. Flask is another web improvement framework with no conditions. 

Web2Py, CherryPy, and Falcon offer incredible capabilities to customize Python development services. A large portion of them are open-source frameworks that allow quick turn of events. 

Simple to read and compose 

Python has an improved sentence structure - one that is like the English language. New engineers for Python can undoubtedly understand where they stand in the development process. The simplicity of composing allows quick application building. 

The motivation behind building Python, as said by its maker Guido Van Rossum, was to empower even beginner engineers to comprehend the programming language. The simple coding likewise permits developers to roll out speedy improvements without getting confused by pointless subtleties. 

Utilized by the best 

Alright - Python isn't simply one more programming language. It should have something, which is the reason the business giants use it. Furthermore, that too for different purposes. Developers at Google use Python to assemble framework organization systems, parallel information pusher, code audit, testing and QA, and substantially more. Netflix utilizes Python web development services for its recommendation algorithm and media player. 

Massive community support 

Python has a steadily developing community that offers enormous help. From amateurs to specialists, there's everybody. There are a lot of instructional exercises, documentation, and guides accessible for Python web development solutions. 

Today, numerous universities start with Python, adding to the quantity of individuals in the community. Frequently, Python designers team up on various tasks and help each other with algorithmic, utilitarian, and application critical thinking. 

Progressive applications 

Python is the greatest supporter of data science, Machine Learning, and Artificial Intelligence at any enterprise software development company. Its utilization cases in cutting edge applications are the most compelling motivation for its prosperity. Python is the second most well known tool after R for data analytics.

The simplicity of getting sorted out, overseeing, and visualizing information through unique libraries makes it ideal for data based applications. TensorFlow for neural networks and OpenCV for computer vision are two of Python's most well known use cases for Machine learning applications.

Summary

Thinking about the advances in programming and innovation, Python is a YES for an assorted scope of utilizations. Game development, web application development services, GUI advancement, ML and AI improvement, Enterprise and customer applications - every one of them uses Python to its full potential. 

The disadvantages of Python web improvement arrangements are regularly disregarded by developers and organizations because of the advantages it gives. They focus on quality over speed and performance over blunders. That is the reason it's a good idea to utilize Python for building the applications of the future.

#python development services #python development company #python app development #python development #python in web development #python software development

Learn Python - Full Course for Beginners [Tutorial]

This course will give you a full introduction into all of the core concepts in python. Follow along with the videos and you’ll be a python programmer in no time!
⭐️ Contents ⭐
⌨️ (0:00) Introduction
⌨️ (1:45) Installing Python & PyCharm
⌨️ (6:40) Setup & Hello World
⌨️ (10:23) Drawing a Shape
⌨️ (15:06) Variables & Data Types
⌨️ (27:03) Working With Strings
⌨️ (38:18) Working With Numbers
⌨️ (48:26) Getting Input From Users
⌨️ (52:37) Building a Basic Calculator
⌨️ (58:27) Mad Libs Game
⌨️ (1:03:10) Lists
⌨️ (1:10:44) List Functions
⌨️ (1:18:57) Tuples
⌨️ (1:24:15) Functions
⌨️ (1:34:11) Return Statement
⌨️ (1:40:06) If Statements
⌨️ (1:54:07) If Statements & Comparisons
⌨️ (2:00:37) Building a better Calculator
⌨️ (2:07:17) Dictionaries
⌨️ (2:14:13) While Loop
⌨️ (2:20:21) Building a Guessing Game
⌨️ (2:32:44) For Loops
⌨️ (2:41:20) Exponent Function
⌨️ (2:47:13) 2D Lists & Nested Loops
⌨️ (2:52:41) Building a Translator
⌨️ (3:00:18) Comments
⌨️ (3:04:17) Try / Except
⌨️ (3:12:41) Reading Files
⌨️ (3:21:26) Writing to Files
⌨️ (3:28:13) Modules & Pip
⌨️ (3:43:56) Classes & Objects
⌨️ (3:57:37) Building a Multiple Choice Quiz
⌨️ (4:08:28) Object Functions
⌨️ (4:12:37) Inheritance
⌨️ (4:20:43) Python Interpreter
📺 The video in this post was made by freeCodeCamp.org
The origin of the article: https://www.youtube.com/watch?v=rfscVS0vtbw&list=PLWKjhJtqVAblfum5WiQblKPwIbqYXkDoC&index=3

🔥 If you’re a beginner. I believe the article below will be useful to you ☞ What You Should Know Before Investing in Cryptocurrency - For Beginner
⭐ ⭐ ⭐The project is of interest to the community. Join to Get free ‘GEEK coin’ (GEEKCASH coin)!
☞ **-----CLICK HERE-----**⭐ ⭐ ⭐
Thanks for visiting and watching! Please don’t forget to leave a like, comment and share!

#python #learn python #learn python for beginners #learn python - full course for beginners [tutorial] #python programmer #concepts in python

Citrix Bugs Allow Unauthenticated Code Injection, Data Theft

Multiple vulnerabilities in the Citrix Application Delivery Controller (ADC) and Gateway would allow code injection, information disclosure and denial of service, the networking vendor announced Tuesday. Four of the bugs are exploitable by an unauthenticated, remote attacker.

The Citrix products (formerly known as NetScaler ADC and Gateway) are used for application-aware traffic management and secure remote access, respectively, and are installed in at least 80,000 companies in 158 countries, according to a December assessment from Positive Technologies.

Other flaws announced Tuesday also affect Citrix SD-WAN WANOP appliances, models 4000-WO, 4100-WO, 5000-WO and 5100-WO.

Attacks on the management interface of the products could result in system compromise by an unauthenticated user on the management network; or system compromise through cross-site scripting (XSS). Attackers could also create a download link for the device which, if downloaded and then executed by an unauthenticated user on the management network, could result in the compromise of a local computer.

“Customers who have configured their systems in accordance with Citrix recommendations [i.e., to have this interface separated from the network and protected by a firewall] have significantly reduced their risk from attacks to the management interface,” according to the vendor.

Threat actors could also mount attacks on Virtual IPs (VIPs). VIPs, among other things, are used to provide users with a unique IP address for communicating with network resources for applications that do not allow multiple connections or users from the same IP address.

The VIP attacks include denial of service against either the Gateway or Authentication virtual servers by an unauthenticated user; or remote port scanning of the internal network by an authenticated Citrix Gateway user.

“Attackers can only discern whether a TLS connection is possible with the port and cannot communicate further with the end devices,” according to the critical Citrix advisory. “Customers who have not enabled either the Gateway or Authentication virtual servers are not at risk from attacks that are applicable to those servers. Other virtual servers e.g. load balancing and content switching virtual servers are not affected by these issues.”

A final vulnerability has been found in Citrix Gateway Plug-in for Linux that would allow a local logged-on user of a Linux system with that plug-in installed to elevate their privileges to an administrator account on that computer, the company said.

#vulnerabilities #adc #citrix #code injection #critical advisory #cve-2020-8187 #cve-2020-8190 #cve-2020-8191 #cve-2020-8193 #cve-2020-8194 #cve-2020-8195 #cve-2020-8196 #cve-2020-8197 #cve-2020-8198 #cve-2020-8199 #denial of service #gateway #information disclosure #patches #security advisory #security bugs

Python Programming: A Beginner’s Guide

Python is an interpreted, high-level, powerful general-purpose programming language. You may ask, Python’s a snake right? and Why is this programming language named after it? Well, you are in the right place to discover the answer! and I’ll also answer the why, what, and how regarding Python programming.

Why do we need to know about Python?

People prefer Python over French (What 😮)

According to a recent survey, in the UK, Python overtook French to be the most popular language taught in primary schools. (OMG!) 6 of 10 parents preferred their children to learn Python over French.

The survey by Ocado group

So hurry up🏃‍♂️🏃‍♀️(or these kids will for sure)! Get ready to learn it! Because there’s a possibility of you being hired in one of the companies mentioned below!!!

#python #python-programming #software-development #python3 #python-tips #learn-python #python-top-story #python-skills

42 Exciting Python Project Ideas & Topics for Beginners [2021]

Python Project Ideas

Python is one of the most popular programming languages currently. It looks like this trend is about to continue in 2021 and beyond. So, if you are a Python beginner, the best thing you can do is work on some real-time Python project ideas.

We, here at upGrad, believe in a practical approach as theoretical knowledge alone won’t be of help in a real-time work environment. In this article, we will be exploring some interesting Python project ideas which beginners can work on to put their Python knowledge to test. In this article, you will find 42 top python project ideas for beginners to get hands-on experience on Python

Moreover, project-based learning helps improve student knowledge. That’s why all of the upGrad courses cover case studies and assignments based on real-life problems. This technique is ideally for, but not limited to, beginners in programming skills.

But first, let’s address the more pertinent question that must be lurking in your mind:

#data science #python project #python project ideas #python project ideas for beginners #python project topics #python projects #python projects for beginners