python - 在xlsxwriter中更改add_table工作表作者的数字格式(Change number format for add_table worksheet writer in xlsxwriter)

Question

I want to adjust my numbers format in my excel table from 1000,12 to the European style (with thousand separator): 1.000,12 with python. (我想将我的excel表中的数字格式从1000,12调整为欧式风格（带千位分隔符）： 1.000,12带python）。)

In excel the format is set-up with: #.##0,00 (在excel中，格式设置为： #.##0,00)

When I want to use it in my workbook with xlsxwriter (flattened is a dataframe table): (当我想在xlsxwriter工作簿中使用它时（展平的是一个数据xlsxwriter表）：)

import xlsxwriter
workbook = xlsxwriter.Workbook('excel.xlsx', options={'nan_inf_to_errors': True})
worksheet1 = workbook.add_worksheet('table1')
numbersformat = workbook.add_format({'num_format': '#.##0,00'})
worksheet1.add_table(3, 1, flattened.shape[0]+3, flattened.shape[1],
    {'data': flattened.values.tolist(),
    'columns': [{'header': c} for c in flattened.columns.tolist()],
    'style': 'Table Style Medium 9',
    'format': numbersformat})

I get an empty dataframe. (我得到一个空的数据框。) Whereever leads to the outcome of the table: (导致表结果的地方：)

worksheet1.add_table(3, 1, flattened.shape[0]+3, flattened.shape[1],
    {'data': flattened.values.tolist(),
    'columns': [{'header': c} for c in flattened.columns.tolist()],
    'style': 'Table Style Medium 9',})

My dataframe flattened looks like this: (我展flattened数据框如下所示：)

Department Name Month:1 Month:2 Month:All
Bistro     Ama  82.75            82.75
Bistro     Beb  100.00  212.00   312.00
All             182.75  212.00    394.75

  ask by PV8 translate from so

Answer 1

I want to adjust my numbers format in my excel table from 1000,12 to the European style (with thousand separator): 1.000,12 with python. (我想将我的excel表中的数字格式从1000,12调整为欧式风格（带千位分隔符）：1.000,12（带python）。)

In excel the format is set-up with: #.##0,00 (在excel中，格式设置为： #.##0,00)

The issue here is that even though the format is displayed as #.##0,00 it is actually stored by Excel as #,##0.00 (ie, US locale). (这里的问题是，即使格式显示为#.##0,00它实际上也被Excel存储为#,##0.00 （即美国语言环境）。) Your OS probably has a default thousands separator of "." (您的操作系统可能默认使用千位分隔符“。”。) and decimal separator of "," and Excel changes #,##0.00 to #.##0,00 when it reads and displays the values. (和“，”的十进制分隔符，并且Excel在读取并显示值时将#,##0.00更改为#.##0,00 。)

Your program should work as expected if you change the code to have the US style format: (如果将代码更改为美式格式，则程序应可以按预期工作：)

numbersformat = workbook.add_format({'num_format': '#,##0.00'})

Update: (更新：)

You are also adding the format in the wrong place. (您还在错误的位置添加了格式。) It is a property of the column not the whole table. (它是列的属性，而不是整个表的属性。) Here is a working example: (这是一个工作示例：)

import pandas as pd
import xlsxwriter

flattened = pd.DataFrame({'Department': ['Bistro', 'Bistro', 'All'],
                          'Name':       ['Ama',    'Beb',    None],
                          'Month:1':    [182.75,    1100.00, 1182.75],
                          'Month:2':    ['',        1212.00, 1212.00],
                          'Month:All':  [182.75,    2312.00, 2394.75]})

# Set the dataframe column order.
flattened = flattened[['Department', 'Name',  'Month:1',
                       'Month:2', 'Month:All']]

workbook = xlsxwriter.Workbook('excel.xlsx',
                               options={'nan_inf_to_errors': True})

worksheet1 = workbook.add_worksheet('table1')

# Make the columns wider for clarity.
worksheet1.set_column(1, flattened.shape[1], 11)

numbersformat = workbook.add_format({'num_format': '#,##0.00'})

worksheet1.add_table(3, 1, flattened.shape[0]+3, flattened.shape[1],
    {'data': flattened.values.tolist(),
     'columns': [{'header': c, 'format': numbersformat}
                 for c in flattened.columns.tolist()],
     'style': 'Table Style Medium 9'})

workbook.close()

Output: (输出：)

And here is the same file when the Windows or macOS regional settings have "," as the decimal separator and "." (当Windows或macOS区域设置的小数点分隔符为“，”时，这是同一文件。) as the thousands separator: (作为千位分隔符：)

The difference is explained in the XlsxWriter docs: Number Formats in different locales . (区别在XlsxWriter文档中进行了解释： 不同区域设置中的数字格式 。)