python - 在python中将文本转换为数字

Question

输入是一个文本表格——一些列是数字，一些是文本。我有 python 脚本将此文本文件转换为 xlsx。在 Excel 或 Libre 中打开后，所有字段都是文本。是否有将某些列转换为数字的选项？

猫脚本.py:

import csv
import sys
from xlsxwriter.workbook import Workbook

# Add some command-line logic to read the file names.
tsv_file = sys.argv[1]
xlsx_file = sys.argv[2]

# Create an XlsxWriter workbook object and add a worksheet.
workbook = Workbook(xlsx_file)
worksheet = workbook.add_worksheet()

# Create a TSV file reader.
tsv_reader = csv.reader(open(tsv_file, 'rb'), delimiter='\t')

# Read the row data from the TSV file and write it to the XLSX file.
for row, data in enumerate(tsv_reader):
    worksheet.write_row(row, 0, data)

# Close the XLSX file.
workbook.close()

运行脚本:

python script.py in.txt out.xlsx

我想更改脚本以将一些列转换为文本，将一些列转换为数字并保留 xlsx 格式。知道怎么做吗？

Answer 1

您可以使用 strings_to_numbers XlsxWriter 构造函数选项。来自docs :

strings_to_numbers: Enable the worksheet.write() method to convert strings to numbers, where possible, using float() in order to avoid an Excel warning about “Numbers Stored as Text”. The default is False. To enable this option use:

workbook = xlsxwriter.Workbook(filename, {'strings_to_numbers': True})

示例:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx', {'strings_to_numbers': True})
worksheet = workbook.add_worksheet()

worksheet.write(0, 0, 'Hello')
worksheet.write(1, 0, '123') # Write this string as a number.

workbook.close()

输出: