Python在Excel中设置数字格式和获取应用数字格式后的值

目录

安装Python Excel库

Python在Excel中设置数字格式

Python获取Excel中应用数字格式的单元格的显示值

总结

---

Excel 数字格式是用于控制单元格中数字显示方式的一组规则或代码。通过设置不同的数字格式，可以定义数字的显示方式，如小数位数、货币符号、百分比、日期和时间等。

以下是一些常见的 Excel 数字格式:

• 常规格式：默认情况下，Excel 会根据数据自动选择合适的显示格式。
• 数字格式：设置数字的小数位数，如 "0.00"、"#,##0.00"。
• 货币格式：以货币符号显示金额，如 "￥0.00"。
• 会计格式：以会计样式显示金额，数字右对齐，货币符号居中。
• 日期格式：用于显示日期和时间数据，如"2024年6月26日"、"6/26/24"、"2024-06-26"等。
• 百分比格式：将数字显示为百分比形式，如 "10.00%"。
• 分数格式：将数字显示为分数形式，如 "10 1/2"。
• 科学计数法格式：以指数形式显示数字，如 "1.23E+06"。
• 文本格式：将数字视为文本处理，不进行任何计算。
• 自定义格式：用户可以根据自己的需求创建自定义的数字格式。这可以使用各种格式代码来控制数字的显示方式，比如加入千位分隔符、百分号、自定义文本等。

这篇文章将探讨如何使用Python在Excel中设置这些常见的数字格式，以及如何获取应用数字格式后的值。

• Python在Excel中设置数字格式
• Python获取Excel中应用数字格式的单元格的显示值

安装Python Excel库

在Python中，我们可以使用Spire.XLS for Python库来给Excel中的数据设置数字格式。它支持几乎所有常见的数字格式。

你可以使用以下pip命令从PyPI安装Spire.XLS for Python:

pip install Spire.Xls

如果不清楚如何安装，可以查看这篇文档：如何在 Windows 中安装 Spire.XLS for Python

Python在Excel中设置数字格式

Spire.XLS for Python 使用数字格式代码（见：微软数字格式代码）来以不同格式显示单元格数据，如数字、货币、会计、日期、百分比、分数、科学计数、文本和自定义格式等。

以下列表展示了 Excel 数字格式代码中使用的一些常见元素：

• “@” ：文本占位符。按输入的方式显示文本。
• “0” ：数字占位符。如果有可用数字，则显示数字；否则显示零。
• “#” ：数字占位符。如果有可用数字，则显示数字，但不显示前导或尾随零。
• “.” ：小数点分隔符。表示数字中的小数位置。
• “,” ：千位分隔符。将三位数字分开。
• “$” ：货币符号。表示数字中使用的货币。
• “%” ：百分比符号。将数字乘以 100 并显示为百分比。
• “E+” 或 “e+” ：指数表示法。以科学记数法表示数字。
• “-” ：负号。显示负数或表示负值。
• “[Color]” ：条件格式。根据指定条件应用格式。
• “/” ：分数分隔符。将数字格式化为分数。
• “;” ：部分分隔符。在数字格式中为正值、负值和零值分隔不同部分。

这些元素可以组合成各种数字格式代码来显示数字。例如：

• “0.00” ：以两位小数显示数字。
• “#,##0” ：以千位分隔符显示数字。
• “0.00%” ：以百分比形式显示数字并保留两位小数。
• “$#,##0.00” ：以货币值显示数字，并显示美元符号和两位小数。
• “[Green]0.00;[Red]-0.00;[Blue]0.00” ：将正值显示为绿色，负值显示为红色，零值显示为蓝色。

Spire.XLS for Python 提供了 CellRange.NumberFormat 属性，支持通过格式代码对单个单元格或单元格范围应用各种类型的数字格式。

以下代码展示了如何使用 Python 和 Spire.XLS for Python 对 Excel 单元格应用多种不同的数字格式：

from spire.xls import *
from spire.xls.common import *

# 实例化 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
# workbook.LoadFromFile("Example.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets[0]

sheet.Range["A1"].Text = "数字格式"
sheet.Range["B1"].Text = "示例输入"
sheet.Range["C1"].Text = "示例输出"
sheet.Range["A1:C1"].Style.Font.IsBold = True
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center

# 应用不同类型的数字格式
sheet.Range["A2"].Text = "数字"
sheet.Range["B2"].Text = "1234.5678"
sheet.Range["C2"].NumberValue = 1234.5678
sheet.Range["C2"].NumberFormat = "0"

sheet.Range["A3"].Text = "数字"
sheet.Range["B3"].Text = "1234.5678"
sheet.Range["C3"].NumberValue = 1234.5678
sheet.Range["C3"].NumberFormat = "0.00"

sheet.Range["A4"].Text = "数字"
sheet.Range["B4"].Text = "1234.5678"
sheet.Range["C4"].NumberValue = 1234.5678
sheet.Range["C4"].NumberFormat = "#,##0.00"

# 应用货币格式
sheet.Range["A5"].Text = "货币"
sheet.Range["B5"].Text = "1234.5678"
sheet.Range["C5"].NumberValue = 1234.5678
sheet.Range["C5"].NumberFormat = "\"$\"#,##0.00"

# 应用日期格式
sheet.Range["A6"].Text = "日期"
sheet.Range["B6"].Text = DateTime.get_Today().ToShortDateString()
sheet.Range["C6"].DateTimeValue = DateTime.get_Today()
sheet.Range["C6"].NumberFormat = "mm/dd/yyyy"

# 应用科学计数格式
sheet.Range["A7"].Text = "科学计数"
sheet.Range["B7"].Text = "1234567.0005"
sheet.Range["C7"].NumberValue = 1234.5678
sheet.Range["C7"].NumberFormat = "0.00E+00"

# 应用会计格式
sheet.Range["A8"].Text = "会计"
# 正数
sheet.Range["B8"].Text = "5"
sheet.Range["C8"].NumberValue = 5 
sheet.Range["A9"].Text = "会计"
# 负数
sheet.Range["B9"].Text = "-5"
sheet.Range["C9"].NumberValue = -5
sheet.Range["A10"].Text = "会计"
# 零
sheet.Range["B10"].Text = "0"
sheet.Range["C10"].NumberValue = 0
sheet.Range["C8:C10"].NumberFormat = "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)"

# 应用自定义格式
sheet.Range["A11"].Text = "自定义"
# 正数
sheet.Range["B11"].Text = "5"
sheet.Range["C11"].NumberValue = 5 
sheet.Range["A12"].Text = "自定义"
# 负数
sheet.Range["B12"].Text = "-5"
sheet.Range["C12"].NumberValue = -5
sheet.Range["A13"].Text = "自定义"
# 零
sheet.Range["B13"].Text = "0"
sheet.Range["C13"].NumberValue = 0
sheet.Range["C11:C13"].NumberFormat = "[Green]0.00;[Red]-0.00;[Blue]0.00"

# 应用百分比格式
sheet.Range["A14"].Text = "百分比"
sheet.Range["B14"].Text = "0.58"
sheet.Range["C14"].NumberValue = 0.58
sheet.Range["C14"].NumberFormat = "0.00%"

# 应用分数格式
sheet.Range["A15"].Text = "分数"
sheet.Range["B15"].Text = "0.25"
sheet.Range["C15"].NumberValue = 0.25
sheet.Range["C15"].NumberFormat = "# ?/?"

# 应用文本格式
sheet.Range["A16"].Text = "文本"
sheet.Range["B16"].Text = "001"
sheet.Range["C16"].Value = "001"
sheet.Range["C16"].NumberFormat = "@"

# 设置列宽
sheet.SetColumnWidth(1, 23)
sheet.SetColumnWidth(2, 44)
sheet.SetColumnWidth(3, 23)

# 保存结果文件
workbook.SaveToFile("设置数字格式.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Python获取Excel中应用数字格式的单元格的显示值

在应用数字格式后，Excel单元格中的显示值可能和实际的值不相同。在将 Excel 数据导入到其他系统或程序时，可能需要读取呈现给用户的显示值。

通过使用 CellRange.DisplayedText 属性，可以轻松获取 Excel 单元格的显示值。

以下代码展示了如何使用 Python 和 Spire.XLS for Python 获取应用了数字格式的 Excel 单元格的显示值：

from spire.xls import *
from spire.xls.common import *

# 实例化 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("设置数字格式.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets[0]

# 获取单元格 C2 的显示文本
displayedText = sheet.Range["C2"].DisplayedText
print(displayedText)

workbook.Dispose()

注意，CellRange.DisplayedText属性不仅可以获取设置数字格式后的值，也可以获取任意其他单元格的显示值。

总结

这篇文章介绍了如何使用Python在Excel中设置各种不同的数字格式，如数字、货币、会计、日期、百分比、分数、科学计数和文本等，以及如何使用Python获取设置数字格式后的Excel单元格的显示值。你可以根据自己的需要，对文章的代码进行修改或扩展。

本文完结。