How to get the cell number in Excel using Python?

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_cellin 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
• Select a spreadsheet:
• Read a spreadsheet
• References

 

How to read a microsoft excel file using python ?

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