英文:
Negative Number formatting with pyexcelerate in python
问题
I understand your request. Here is the translated content:
我有一个要求，需要从Python编写一个带有一些数字格式的Excel表格。
我们需要使用括号和红色字体来格式化负数。
-1.2 应该格式化为 (1.20)，并显示为红色。
最初，我尝试将其转换为字符串来实现它。
# 为整个列设置格式
format_obj = pyexcelerate.Format('0.00')
worksheet.set_col_style(i, pyexcelerate.Style(format=format_obj))
# 在循环中为单个单元格设置格式
worksheet[i][j].value = '(' + str(float(df.iloc[i, j]) * -1) + ')' # 格式化为 (1.2)
worksheet[i][j].style.alignment.horizontal = 'right' # 右对齐，与数字类似
worksheet[i][j].style.font.color = pyexcelerate.Color(255, 0, 0) # 红色字体
外观上一切都看起来不错。但问题是，在Excel中它变成了文本单元格。
然后，我尝试以不同的方式进行自定义格式化。
# 在循环中为单个单元格设置格式
worksheet[i][j].style.font.color = pyexcelerate.Color(255, 0, 0) # 红色字体
worksheet[i][j].style.format.format = '(0.00)' # 带括号的数字格式
但问题是，这里 -1.2 转换为 -(1.20) 并显示为红色。
当然，现在它是Excel中的数字单元格。
然而，我不想要负号。
所以，有没有办法做到这一点？
我想使用 pyexcelerate，因为整个项目都是基于这个库构建的。
英文:
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.
答案1
得分: 1
The pyexcelerate.Format object is identical to the "format" option in Excel.
因此，为了使用括号格式化负数，尝试像这样做：
format_obj = pyexcelerate.Format('##0.00;[Red](##0.00)')
您也可以验证这在Excel中有效，并且有更多详细信息在这里。如果这不起作用，那可能是需要进一步调查的错误。
英文:
The pyexcelerate.Format object is identical to the "format" option in Excel.
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论