In this article we want to learn about Effortless Spreadsheet Handling with Python 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.
Installation
To install PyExcel, you can use pip, the package installer for Python:
|
1 |
pip install pyexcel |
Reading Data from Spreadsheets
PyExcel supports different spreadsheet formats, including Excel, CSV, TSV, and more. This is an example of how to read data from Excel file:
|
1 2 3 |
import pyexcel data = pyexcel.get_array(file_name="example.xlsx") |
This line of code load the data from the “example.xlsx” file into a two-dimensional array in Python. You can also use the get_dict() function to load the data into a dictionary.
Writing Data to Spreadsheets
PyExcel also makes it easy to write data back to spreadsheets. This is an example of how to write data to Excel file.
|
1 2 3 4 5 6 7 8 9 10 |
import pyexcel data = [ ["Name", "Age", "Gender"], ["Alice", 25, "Female"], ["Bob", 30, "Male"], ["Charlie", 35, "Male"], ] pyexcel.save_as(array=data, dest_file_name="example.xlsx") |
This code save the data to an Excel file named “example.xlsx”. You can also use the save_as_dict() function to save data from a dictionary.
Manipulating Data
PyExcel also provides number of functions for manipulating data in spreadsheets. For example, you can easily transpose data using transpose() function:
|
1 2 3 4 5 6 7 |
import pyexcel data = pyexcel.get_array(file_name="example.xlsx") transposed_data = pyexcel.utils.transpose(data) pyexcel.save_as(array=transposed_data, dest_file_name="transposed.xlsx") |
This code transpose the data in the “example.xlsx” file and save the result to a new file named “transposed.xlsx”.
What are other Options Instead of PyExcel
There are several other Python packages that you can use for working with Excel files. some popular options include:
- 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 library that allows you to read and write Excel files using Python. this library supports many features of Excel including 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, while 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.
- pyxlsb: pyxlsb is library that allows you to read and write Excel binary files (xlsb) using Python. It’s a relatively new library but can be useful if you need to work with binary Excel files.
These are just a few of the many options available for working with Excel files in Python. The best library for you will depend on your specific needs and use case.
Learn More on Python
- Python-Docx: Creating Microsoft Word Documents with Python
- Python and Microsoft Word: A Beginner’s Guide to Automating Documents
- How to Install docx2python: Python Library for Word Documents
- Merge Microsoft Word Documents with Python Docxcompose
- Asynchronous Web Development with Python and aiohttp
- Python Treq: An Introduction to a Powerful HTTP Client Library
- Introduction to Python httplib2 Library
- An Introduction to Python’s urllib Library
- Python httpx: A High-Performance HTTP Client for Python 3
Final Thoughts
Python PyExcel is good and powerful library for working with spreadsheets in Python. With PyExcel library you can read and write data to different spreadsheet formats, as well as manipulate or read and write data using different built-in functions. Whether you’re working with Excel, CSV, TSV, or another format PyExcel provides simple and effective way to get the job done. (Effortless Spreadsheet Handling with Python PyExcel)
