python library for handling excel files (xls | xlsx) [closed]

  • Last Update :
  • Techknowledgy :

If you're running Windows:

$ python pip install pandas
$ pip install pandas

Note that you may get a ModuleNotFoundError or ImportError error when running the code in this article. For example:

ModuleNotFoundError: No module named 'openpyxl'

First, let's import the Pandas module:

import pandas as pd

Now, let's use a dictionary to populate a DataFrame:

df = pd.DataFrame({
   'States': ['California', 'Florida', 'Montana', 'Colorodo', 'Washington', 'Virginia'],
   'Capitals': ['Sacramento', 'Tallahassee', 'Helena', 'Denver', 'Olympia', 'Richmond'],
   'Population': ['508529', '193551', '32315', '619968', '52555', '227032']

Suggestion : 2

The library we identified for parsing Excel files is xlrd. This library is part of a series of libraries for working with Excel files in Python.,The first thing we recommend is searching the Web to see which libraries other people recommend. If you search for “parse excel using python”, you will find the xlrd library surfaces at the top of the search results.,Besides learning how to parse Excel using the xlrd library, we also learned a few new Python programming concepts, which are summarized in Table 4-1.,In this chapter, we are looking at Excel files. If you visit PyPI in your browser, you can search for libraries relating to Excel and see lists of matching package results you can download. This is one way to explore which package you should use.

First, we will be evaluating Excel data. Let’s install the package to do that— xlrd. To install the package, we use pip in the following way:

pip install xlrd

To remove the package, we would run the uninstall command:

pip uninstall xlrd

If you get the following error, that means you don’t have pip installed:

-bash: pip: command not found

To see how it works, open up your Python interpreter and try the following:


The output should be:

[0, 1, 2]

A counter is a way to control the flow of your program. By using a counter, you can control your for loop by adding an if statement and increasing the count with each iteration of the loop. If the count ends up greater than a value of your choosing, the for loop will no longer process the code controlled by it. Try the following example in your interpreter:

count = 0

for i in range(1000):
   if count < 10:
   print i
count += 1

print 'Count: ', count

Create a sample list:

x = ['cat', 'dog', 'fish', 'monkey', 'snake']

To pull out the second item, you can refer to the item by adding an index, as shown here:

>>> x[2]

If this isn’t the result you expected, remember that Python starts counting at 0. So, to get the second item as humans would identify it, we have to use the number 1:

>>> x[1]

Slicing is another useful practice related to indexing. Slicing allows you to take a “slice” out of another list or iterable object. For example:

>>> x[1: 4]
   ['dog', 'fish', 'monkey']

If you don’t include the first or last number, the slice will go to the end. Here are a few examples:

x[2: ]
   ['fish', 'monkey', 'snake']

x[-2: ]
   ['monkey', 'snake']

x[: 2]
   ['cat', 'dog']

x[: -2]
   ['cat', 'dog', 'fish']

Use comments in your code as a way to help the future you (and others) understand why you did something. To comment in your code, put a # before the comment:

# This is a comment in Python.Python will ignore this line.

For a multiline comment, use the following format:

This is the formatting
for a multiline comment.
If your comment is really long or you want to
insert a longer description, you should use
this type of comment.

Suggestion : 3

Last Updated : 19 Feb, 2022

NOTE: xlrd has explicitly removed support for reading xlsx sheets.
Command to install xlrd module : 

pip install xlrd

Output :


Output : 


Suggestion : 4

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.,It was born from lack of existing library to read/write natively from Python the Office Open XML format.,openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files,Professional support for openpyxl is available from Clark Consulting & Research and Adimian. Donations to the project to support further development and maintenance are welcome.

from openpyxl
import Workbook
wb = Workbook()

# grab the active worksheet
ws =

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] =

# Save the file"sample.xlsx")
$ pip install openpyxl
$ pip install pillow

Sometimes you might want to work with the checkout of a particular version. This may be the case if bugs have been fixed but a release has not yet been made.

$ pip install - e hg + https: //

Suggestion : 5

Openpyxl is a Python module that can be used for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. Furthermore, this module enables a Python script to modify Excel files. For instance, if we want togo through thousands of rows but just read certain data points and make small changes to these points, we can do this based on some criteria with openpyxl. ,In this section, we will learn how to read multiple xlsx files in Python using openpyxl. Additionally to openpyxl and Path, we are also going to work with the os module. ,In the first step, to reading a xlsx file in Python, we need to import the modules we need. That is, we will import Path and openpyxl:,Basically, here’s the simplest form of using openpyxl for reading a xlsx file in Python:

Basically, here’s the simplest form of using openpyxl for reading a xlsx file in Python:

.wp - block - code {
      border: 0;
      padding: 0;

   .wp - block - code > div {
      overflow: auto;

   .shcb - language {
      border: 0;
      clip: rect(1 px, 1 px, 1 px, 1 px); -
      webkit - clip - path: inset(50 % );
      clip - path: inset(50 % );
      height: 1 px;
      margin: -1 px;
      overflow: hidden;
      padding: 0;
      position: absolute;
      width: 1 px;
      word - wrap: normal;
      word - break: normal;

   .hljs {
      box - sizing: border - box;

   .hljs.shcb - code - table {
      display: table;
      width: 100 % ;

   .hljs.shcb - code - table > .shcb - loc {
      color: inherit;
      display: table - row;
      width: 100 % ;

   .hljs.shcb - code - table.shcb - loc > span {
      display: table - cell;

   .wp - block - code code.hljs: not(.shcb - wrap - lines) {
      white - space: pre;

   .wp - block - code code.hljs.shcb - wrap - lines {
      white - space: pre - wrap;

   .hljs.shcb - line - numbers {
      border - spacing: 0;
      counter - reset: line;

   .hljs.shcb - line - numbers > .shcb - loc {
      counter - increment: line;

   .hljs.shcb - line - numbers.shcb - loc > span {
      padding - left: 0.75 em;

   .hljs.shcb - line - numbers.shcb - loc::before {
      border - right: 1 px solid #ddd;
      content: counter(line);
      display: table - cell;
      padding: 0 0.75 em;
      text - align: right; -
      webkit - user - select: none; -
      moz - user - select: none; -
      ms - user - select: none;
      user - select: none;
      white - space: nowrap;
      width: 1 % ;
import openpyxl
from pathlib
import Path

xlsx_file = Path('SimData', 'play_data.xlsx')
wb_obj = openpyxl.load_workbook(xlsx_file)

# Read the active sheet:
   sheet = wb_obj.activeCode language: Python(python)

In the first step, to reading a xlsx file in Python, we need to import the modules we need. That is, we will import Path and openpyxl:

import openpyxl
from pathlib
import PathCode language: Python(python)

In the second step, we will create a variable using Path. Furthermore, this variable will point at the location and filename of the Excel file we want to import with Python:

# Setting the path to the xlsx file:
xlsx_file = Path('SimData', 'play_data.xlsx')</code></pre>Code language: Python (python)

Now, in the fourth step, we are going to read the active sheet using the active method:

sheet = wb_obj.activeCode language: Python(python)

In the final, and fifth step, we can work, or manipulate, the Excel sheet we have imported with Python. For example, if we want to get the value from a specific cell we can do as follows:

print(sheet["C2"].value) Code language: Python(python)