How to get data from excel file ‘as is’ using openpyxl?

I have an excel file – the data in which looks like.

[   ("a", 4, 4,2, 5,32),   ("b", 6, 7,2, 7,32),   ("c", 6, 7,2, None) ]

I want to get float values ​​of cells – in which separator is comma. But when accessing them, a value with a dot is returned. Is it possible to do this with opepyxl or other libraries?

what i tried

ref_workbook = load_workbook('file.xlsx')
sheet_ranges = ref_workbook['Sheet']         
sheet_ranges['C2'].value,# == 4.2, expected 4,2

I also tried pandas, but it also formats cells with commas.

Answer

This will set the locale to German, which uses a comma as the decimal separator and a dot as the thousands separator, and then format the float value using this locale.

You can also use ‘fr_FR’ for French, ‘pt_BR’ for Brazilian Portuguese and ‘es_ES’ for Spanish etc.

import pandas as pd
import locale

def modify_float_separator(row_num, col_num):
    value = df.iloc[row_num, col_num]
    # Check if the value is a float
    if isinstance(value, float):
        # Set the locale to German
        locale.setlocale(locale.LC_ALL, 'de_DE')
        # Format the float using the German locale
        formatted_value = locale.format_string("%.2f", value, grouping=True)
        return formatted_value


# Read the Excel file into a DataFrame
df = pd.read_excel('example.xlsx')


print(str(df.iloc[1,0])) # 5.32
print(modify_float_separator(1,0)) # 5,32