Excel Manipulation with Python & PyExcel

In this article we want to learn about Excel Manipulation with Python & PyExcel, so first of all let’s talk about PyExcel.

What is Python PyExcel ?

Python PyExcel is library that has simple interface for reading, writing data in variety of spreadsheet formats. With PyExcel, you can easily load data from spreadsheet, manipulate it in Python and then save the results back to the spreadsheet.

How to Install PyExcel?

For installation of PyExcel, you can use pip, simply open your command prompt or terminal and write this command.

pip install pyexcel

1	pip install pyexcel

For the xlsx form extension you need to also install this library.

pip install pyexcel pyexcel-xlsx

1	pip install pyexcel pyexcel-xlsx

For xls format extension you need to install this library also.

pip install pyexcel-xls

1	pip install pyexcel-xls

How to Read Data from Spreadsheets with PyExcel

PyExcel supports different spreadsheet formats, including Excel, CSV, TSV and many more. This is an example of how to read data from Excel file:

import pyexcel as p

records = p.iget_records(file_name="file.xlsx")
for record in records:
    print("%s is aged at %d" % (record['Name'], record['Age']))

import pyexcel as p

records = p.iget_records(file_name="file.xlsx")

for record in records:

print("%s is aged at %d" % (record['Name'], record['Age']))

This will be the result

Excel Manipulation with Python & PyExcel

Writing Data to Excel with PyExcel
PyExcel also makes it easy to write data back to spreadsheets. This is an example of how to write data to Excel file.

import pyexcel

data = [
    ["Name", "Age", "Gender"],
    ["Alice", 25, "Female"],
    ["Bob", 30, "Male"],
    ["Charlie", 35, "Male"],
]

pyexcel.save_as(array=data, dest_file_name="file.xlsx")

import pyexcel

data = [

["Name", "Age", "Gender"],

["Alice", 25, "Female"],

["Bob", 30, "Male"],

["Charlie", 35, "Male"],

]

pyexcel.save_as(array=data, dest_file_name="file.xlsx")

This will be the result

Excel Manipulation with Python XlsxWriter

What are other Options Instead of PyExcel

There are several other Python packages that you can use for working with Excel files. these are some popular options:

Pandas: Pandas is good and powerful library for data manipulation and analysis in Python. with this library we can read and write Excel files, and provide many functions for working with data in spreadsheet format. also It can handle large datasets with easy and can perform advanced data processing and analysis.
openpyxl: openpyxl is a library that allows you to read and write Excel files using Python. this library supports different features of Excel like formatting and charts and can be used to manipulate existing Excel files or create new excel files from scratch.
xlrd and xlwt: These two libraries are used for reading and writing Excel files. xlrd is used for reading data from Excel files, and xlwt is used for writing data to Excel files. They are lightweight and easy to use, but don’t have all the features of Pandas or openpyxl.

XlsxWriter: XlsxWriter is Python library for creating Excel files. It can be used to write data, formatting and add charts to Excel files and supports many of the features of Excel. It’s especially useful for creating reports and generating Excel files programmatically.

Subscribe and Get Free Video Courses & Articles in your Email