Скачать или смотреть How to Read Values Instead of Formulas from Excel Files in Python

Discover how to effectively read values instead of formulas from Excel files in Python using openpyxl and other libraries. This guide provides clear solutions and best practices for handling Excel data.
---
This video is based on the question https://stackoverflow.com/q/63058281/ asked by the user 'user3443033' ( https://stackoverflow.com/u/3443033/ ) and on the answer https://stackoverflow.com/a/63058458/ provided by the user 'Charlie Clark' ( https://stackoverflow.com/u/2385133/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Read values instead of formulas from excel file saved by python

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Read Values Instead of Formulas from Excel Files in Python

When working with Excel files in Python, you might encounter a situation where you need to read values from a sheet that contains formulas. A common challenge arises when using libraries like openpyxl to write data. If the Excel sheet is saved with formulas left intact, trying to read the values using data_only=True might result in unexpected outcomes, such as receiving None instead of the actual computed values. In this guide, we will explore this problem in detail and provide effective solutions.

Understanding the Problem

Imagine you've created an Excel file with two sheets: one for input data and another for output that includes formulas referencing the first sheet. After populating the input sheet programmatically with Python, you attempt to read values from the output sheet but only receive None. This is likely due to the way Excel handles the calculation of formulas when they are saved.

Example Scenario

Here's a quick look at what you may be doing:

Writing data to the Input sheet using openpyxl.

Attempting to read calculated values from the blockMesh sheet where formulas exist, but ending up with None values.

Solution Overview

To effectively read the computed values from the Excel sheet, you need to follow these steps:

Recalculate Formulas: Before reading the values, ensure that the formulas are recalculated. This can be achieved using applications that support headless operations, such as LibreOffice.

Use Iteration for Data Extraction: Instead of manually managing row counters, use built-in methods to iterate through rows more efficiently.

Step-by-Step Solution

Step 1: Recalculate Formulas with Headless LibreOffice

To ensure that all formulas are calculated before reading them with Python:

Install and use LibreOffice in headless mode.

This can be accomplished by running a command that opens the Excel file in LibreOffice and saves it again, prompting it to recalculate formulas.

Example Command

[[See Video to Reveal this Text or Code Snippet]]

This command processes the workbook and forces a recalculation of all formulas, creating a new Excel file with calculated values.

Step 2: Modify Your Code for Reading Data

After recalculating the formulas, modify your reading script as follows to get the actual values:

[[See Video to Reveal this Text or Code Snippet]]

Summary

Waiting for Calculated Values: Ensure that your Excel files are saved with recalculated values by using applications like LibreOffice.

Using Your Code Efficiently: Use built-in functions such as iter_rows to simplify your data handling in Python.

With these approaches, you can seamlessly read values from Excel files that include formulas, enabling you to process data effectively in your Python applications. If you face further challenges, don't hesitate to seek community help or collaborate with others who have worked with similar tasks!