Simplifying Excel Tasks with Python: Data Handling and Testing Tutorial

Working with Python and Excel is actually quite handy, especially if you are into it. In this article, you can see how to create Excel files, write data and formulas into them and read these. Pretty much simple CRUD methods.

The interesting part of the article are the tests and the test coverage tools, provided in Python.

This is how test coverage looks like.

This is how the file with the excel operations looks like:

The following functions are tested:

  • read_excel(file_path)
  • manipulate_data(df)
  • write_to_excel_with_formulas(df, output_file_path)

    This is how the functions are tested:

The command to test the functions is:

  • python -m unittest

Then for the test coverage, these are the commands used:

  • coverage run -m unittest discover
  • coverage report
  • coverage html

To open the index.html with the default browser (or the Excel file) from the terminal:

  • start .\htmlcov\index.html
  • start excel.exe .\test_output.xlsx

This is the YouTube video with the live coding for that one:

Thank you for watching!

Enjoy it!

Tagged with: , , , , , , ,