Excel spreadsheets
are one of those topic’s you have to deal . Not because your boss need them or because
marketing wants them, you have to learn, how to work with spreadsheets, and
that’s when knowing
openpyxl
comes
in handy!
Spreadsheets are a
very user-friendly way to change large
datasets without any technical
background. That’s why they’re still so commonly used today.
In this article, Iam
going to tell you. how to use openpyxl to:
- Manipulate Excel spreadsheets.
- Extract information from
spreadsheets.
- Create simple or more complex
spreadsheets, including adding styles, charts, and so on.
This article is for intermediate developers, who have good
knowledge of Python data structures, such as dictionaries and lists,
but also feel comfortable around OOPS and more intermediate level
topics.
Getting Started With
openpyxl
Now, let’s start installing the package. For this
tutorial, you should use Python 3.8 and openpyxl 2.6.2. To install the package,
you can do the following:
$ pip install openpyxl
After you will
install the package, you should be able to create a spreadsheet with the
following code:
from openpyxl import Workbook
workbook=Workbook()
sheet=workbook.active
sheet["A1"]="hello"
sheet["B1"]="world!”
workbook.save(filename="first.xlsx")
The code above should
create a file called
first.xlsx
in the folder you are using to
run the code. If you open that file with Excel you should see something like
this:
Your first
spreadsheet created!
A example that Reading
an Excel Spreadsheet
Let’s start reading
some spreadsheets! To begin with, open our sample spreadsheet:
>>>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'
In the code above,
you first open the spreadsheet
sample.xlsx
using load_workbook()
,
and then you can use workbook. sheetnames
to see all the sheets you have
available to work with.
After that,
workbook.active
selects the first available sheet
and, in this case, you can see that it selects Sheet
1 automatically.
Using these methods is the default way of opening a spreadsheet, and you’ll see
it many times during this tutorial.
Now, after opening a
spreadsheet, you can easily retrieve data from it like this:
>>>sheet["A1"]
<Cell 'Sheet 1'.A1>
>>>sheet["A1"].value
>>>sheet["F10"].value
To return the actual
value of a cell, you need to do
.value
.
Otherwise, you’ll get the main Cell
object. You can also use the
method .cell()
to retrieve a cell using index
notation. Remember to add .value
to get the actual value and not a Cell
object:>>>sheet.cell(row=10,column=6)
<Cell 'Sheet 1'.F10>
>>>sheet.cell(row=10,column=6).value
You can see that the
results returned are the same, no matter which way you decide to go with.
However, in this tutorial, you’ll be mostly using the first approach:
["A1"]
.
The above shows you
the quickest way to open a spreadsheet. However, you can pass additional
parameters to change the way a spreadsheet is loaded.
Why
spreadsheets are so popular?
There are many
reasons for the popularity of spreadsheet's. Among them are:
·
Everyone want them: As cited above, Microsoft Excel is
installed on virtually every computer in the world, ready to be fired up for
any of the above (and many other) tasks at no extra cost or trouble of the
user. For the few that don’t (or tasks that require more collaborative
authoring) Google Sheets are free and available online at all times.
·
Interoperability: Because everyone has them, you can
send anyone a spreadsheet (read: Excel-file) and trust that they will be able
to open it.
·
Learning curve: Even when people realize there may be
a better tool for their task, there’s never the right time to learn that new
tool: “I can do it in a day in Excel”.
Getting
data from cells
Once
you have selected the worksheet, you can extract the value of a particular data
cell as follows.
sheet
.cell
(0,0).value
Adding
styles to cells
With
xlwt
, not only can you write values to
cells, but you can add custom styles too! The following snippet shows how to do
this:style
= xlwt
.XFStyle
()
font
= xlwt
.Font
('Helvica')
style
.font
= font
pattern
= xlwt
.Pattern
()
pattern
.pattern
= xlwt
.Pattern
.SOLID_PATTERN
pattern
.pattern_fore_colour
= xlwt
.Style
.colour_map
['green']
style
.pattern
= pattern
sheet
.write
(0,0,"Some data", style
)
You
can iterate it over a loop to extract data in the whole sheet.
You
can detect an empty cell by using
empty_cell
in xlrd
.if sheet
.cell
(0,0).value
== xlrd
.empty_cell
.value
:
You can use numpy(), for spreadsheets in python :-
To read an excel file using python, a solution is to use the python module called xlrd. An example with a file called 'read_excel_file_with_python.xlsx':
Get the spreadsheet names
import xlrd
import numpy as np
workbook = xlrd.open_workbook('read_excel file with_python.xlsx')
SheetNameList = workbook.sheet_names()
for i in np.arange( len(List_1) ):
print( List_1[i] )
Select a spreadsheet:
worksheet = workbook.sheet_by_name(List_1[0])
rows = worksheet.nrows
cells = worksheet.ncols
print( 'rows, cells', rows, cells )
returns
rows, cells 10 6
Read a spreadsheet
row = 0
while row < _rows:
row = worksheet.row(row)
print( 'Row: ', row )
print( row, len(row), row[0] )
cell = 0
while cell < cells:
type = worksheet.cell_type(row, cell)
value = worksheet.cell_value(row, cell)
print( ' ', type, ':', value )
cell += 1
row += 1
0 Comments