I have been using openpyxl to make changes to column size and datatypes and structure the overall thing in Excel. However, I have a few percentage values which are stored in string format:
However, when I double click on the cell value it automatically gets converted into numerical format, but the "%" symbol is retained like this:
I am using the following piece of code:
import openpyxl
from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00
wb = openpyxl.load_workbook(filename = path)
worksheet = wb.active
string_check= re.compile('%')
if (string_check.search(cell.value) != None):
cell.number_format = FORMAT_PERCENTAGE_00
wb.save(filename = path)
But when I open back the Excel file, I see the issue still persists. The comment with the yellow exclamation mark still appears and on double-clicking the cell it goes away, i.e. the numerical value with the "%" symbol stays but in float format. How can I make sure that Python is able to save the excel file with the numerical values with "%" symbol in float format?