时间戳

首页 > 解决方案 > 自动化多个 Excel 文件的单元格属性

excel - 自动化多个 Excel 文件的单元格属性

问题描述

我有大约 100 个 Excel 文件，其中列 K 和 L 表示为浮点数，例如 0.5677。我想将这些列表示为百分比，在本例中为 56.8%。有没有办法可以自动化这个？显然我可以手动调整列，但这非常耗时。

我没有使用宏或 VBA 的经验。

任何帮助将不胜感激。

亲切的问候，M。

标签： excel

解决方案

我找到了一种使用 Python Pandas 的有用方法。就我而言，Pandas 也是 Excel 文件的来源。

import pandas as pd

# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Numbers':    [1010, 2020, 3030, 2020, 1515, 3030, 4545],
                   'Percentage': [.1,   .2,   .33,  .25,  .5,   .75,  .45 ],
})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})

# Note: It isn't possible to format any cells that already have a format such
# as the index or headers or any cells that contain dates or datetimes.

# Set the column width and format.
worksheet.set_column('B:B', 18, format1)

# Set the format but not the column width.
worksheet.set_column('C:C', None, format2)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

推荐阅读