Introduction¶

pyexcel providesone application programming interface to read,manipulate and write data in various excel formats. This library makesinformation processing involving excel files an enjoyable task. The data inexcel files can be turned intoarray or dictwith minimal code and vice versa. This library focuses on dataprocessing using excel files. Therefore, fonts, colors and chartswere not and will not be considered.

The idea originated from the common usability problem: when an excel filedriven web application is delivered for non-developer users (ie: team assistant,human resource administrator etc). The fact is that not everyone knows (or cares)about the differences between various excel formats: csv, xls, xlsx are allthe same to them. Instead of training those users about file formats, thislibrary helps web developers to handle most of the excel fileformats by providing a common programming interface. To add a specific excelfile format type to you application, all you need is to install an extra pyexcelplugin. Hence no code changes to your application and no issues with excel fileformats any more. Looking at the community, this library and its associated onestry to become a small and easy to install alternative to Pandas.

Installation¶

You can install pyexcel via pip:

$pipinstallpyexcel

or clone it and install it:

$gitclonehttps://github.com/pyexcel/pyexcel.git$cdpyexcel$pythonsetup.pyinstall

Suppose you have the following data in a dictionary:

you can easily save it into an excel file using the following code:

>>>importpyexcel>>># make sure you had pyexcel-xls installed>>>a_list_of_dictionaries=[...{..."Name":'Adam',..."Age":28...},...{..."Name":'Beatrice',..."Age":29...},...{..."Name":'Ceri',..."Age":30...},...{..."Name":'Dean',..."Age":26...}...]>>>pyexcel.save_as(records=a_list_of_dictionaries,dest_file_name="your_file.xls")

And here’s how to obtain the records:

>>>importpyexcelasp>>>records=p.iget_records(file_name="your_file.xls")>>>forrecordinrecords:...print("%s is aged at%d"%(record['Name'],record['Age']))Adam is aged at 28Beatrice is aged at 29Ceri is aged at 30Dean is aged at 26>>>p.free_resources()

Custom data rendering:

>>># pip install pyexcel-text==0.2.7.1>>>importpyexcelasp>>>ccs_insight2=p.Sheet()>>>ccs_insight2.name="Worldwide Mobile Phone Shipments (Billions), 2017-2021">>>ccs_insight2.ndjson="""...{"year": ["2017", "2018", "2019", "2020", "2021"]}...{"smart phones": [1.53, 1.64, 1.74, 1.82, 1.90]}...{"feature phones": [0.46, 0.38, 0.30, 0.23, 0.17]}...""".strip()>>>ccs_insight2pyexcel sheet:+----------------+------+------+------+------+------+| year           | 2017 | 2018 | 2019 | 2020 | 2021 |+----------------+------+------+------+------+------+| smart phones   | 1.53 | 1.64 | 1.74 | 1.82 | 1.9  |+----------------+------+------+------+------+------+| feature phones | 0.46 | 0.38 | 0.3  | 0.23 | 0.17 |+----------------+------+------+------+------+------+

Advanced usage :fire:¶

If you are dealing with big data, please consider these usages:

>>>defincrease_everyones_age(generator):...forrowingenerator:...row['Age']+=1...yieldrow>>>defduplicate_each_record(generator):...forrowingenerator:...yieldrow...yieldrow>>>records=p.iget_records(file_name="your_file.xls")>>>io=p.isave_as(records=duplicate_each_record(increase_everyones_age(records)),...dest_file_type='csv',dest_lineterminator='\n')>>>print(io.getvalue())Age,Name29,Adam29,Adam30,Beatrice30,Beatrice31,Ceri31,Ceri27,Dean27,Dean

Two advantages of above method:

1. Add as many wrapping functions as you want.

2. Constant memory consumption

For individual excel file formats, please install them as you wish:

Plugin shopping guide¶

Since 2020, all pyexcel-io plugins have dropped the support for python versionswhich are lower than 3.6. If you want to use any of those Python versions, please use pyexcel-ioand its plugins versions that are lower than 0.6.0.

Except csv files, xls, xlsx and ods files are a zip of a folder containing a lot ofxml files

The dedicated readers for excel files can stream read

In order to manage the list of plugins installed, you need to use pip to add or removea plugin. When you use virtualenv, you can have different plugins per virtualenvironment. In the situation where you have multiple plugins that does the same thingin your environment, you need to tell pyexcel which plugin to use per function call.For example, pyexcel-ods and pyexcel-odsr, and you want to get_array to use pyexcel-odsr.You need to append get_array(…, library=’pyexcel-odsr’).

Footnotes

zipped csv file

zipped tsv file

For compatibility tables of pyexcel-io plugins, please clickhere

Usage¶

Suppose you want to process the following excel data :

• example table

Here are the example usages:

>>>importpyexcelaspe>>>records=pe.iget_records(file_name="your_file.xls")>>>forrecordinrecords:...print("%s is aged at%d"%(record['Name'],record['Age']))Adam is aged at 28Beatrice is aged at 29Ceri is aged at 30Dean is aged at 26>>>pe.free_resources()

Design¶

New tutorial¶

Old tutorial¶

Cook book¶

Real world cases¶

API documentation¶

Developer’s guide¶

Change log¶