Search code examples
pythonexcelnumber-formattingpyexcelerate

Negative Number formatting with pyexcelerate in python

I have a requirement to write an excel sheet from python with some number formatting. We need to format a -ve number with brackets and in red font. -1.2 to be formatted to (1.20) with color red.

Initially, I tried achieving it by converting it into string

# setting format for entire column
format_obj=pyexcelerate.Format('0.00')
worksheet.set_col_style(i,pyexcelerate.Style(format=format_obj))

# Setting format for individual cells in loop
worksheet[i][j].value='(' + str(float(df.iloc[i,j])*-1) + ')' #Formatting -1.2 to (1.2)
worksheet[i][j].style.alignment.horizontal='right'            #Right Align similar to numbers
worksheet[i][j].style.font.color=pyexcelerate.Color(255,0,0)  #Coloring Red

So, look wise everything came good. But problem is that in excel it comes as a text cell.

Then I tried doing it different way with a custom formatting.

# Setting format for individual cells in loop
worksheet[i][j].style.font.color=pyexcelerate.Color(255,0,0) # Coloring Red
worksheet[i][j].style.format.format='(0.00)' # Numeric formatting with brackets

But here the problem is -1.2 is converting to -(1.20) in red. Ofcourse, now it is numeric cell in excel. However, I don't want the -ve sign there.

So, is there a way to do that ? I want to use pyexcelerate as the entire project is built on that library.

Solution

  • The pyexcelerate.Format object is identical to the "format" option in Excel.

    enter image description here

    Therefore, in order to format a negative number with parentheses, try doing something like

    format_obj = pyexcelerate.Format('##0.00;[Red](##0.00)')

    You can verify that that works in Excel too and there are more details on that here. If that doesn't work then that might be a bug that requires more investigation.