Ben Chuanlong Du's Blog

And let it direct your passion with reason.

Working with Spreadsheet in Python

It is suggested that you avoid using Excel files (or other spreadsheet tools) for storing data. Parquet file is currently the best format for storing table-like data. If you do want to interact and manipulate your data using Excel (or other spreadsheet tools), dump your data into CSV files and then load them into Excel when needed. However, if you do want to work with spreadsheet in Python, below are some options.

Quadratic

Quadratic is a data science spreadsheet which has Python (JavaScript and SQL) built-in.

Google Spreadsheet

Please refer to Use Python with Google Spreadsheet for detailed discussions.

pandas

If you just want to read an Excel spreadsheet to a pandas DataFrame or write a pandas DataFrame to an Excel file, it is best to use the pandas library directly. Notice that openpyxl is required if you read/write Excel files using pandas.

openpyxl

openpyxl is the underlying library that pandas leverages for reading/writing Excel files.

xlsxwriter

xlsxwriter is a Python module for writing files in the Excel 2007+ XLSX file format. XlsxWriter is designed only as a file writer. It cannot read or modify an existing Excel file. However, if an Excel file is created from data (understandable by Python), you can create a new Excel file from the same data to overwrite the existing one. This sort of gives you a flavor of updating an existing Excel file.

xlwings

xlwings is a commerical software but has a opensource community edition. It requires an installation of Excel and therefore only works on Windows and macOS. Note that macOS currently does not support UDFs.

References

https://xlsxwriter.readthedocs.io/

https://xlsxwriter.readthedocs.io/faq.html

https://xlsxwriter.readthedocs.io/examples.html

https://github.com/xlwings/xlwings

Comments