是否可以使用 Xlsxwriter 从 Python 中的 Excel 工作表中读取数据？如果是这样怎么办？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20490422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to read data from an Excel sheet in Python using Xlsxwriter? If so how?
提问by Ambi
I'm doing the following calculation.
我正在做以下计算。
worksheet.write_formula('E5', '=({} - A2)'.format(number))
I want to print the value in E5 on the console. Can you help me to do it? Is it possible to do it with Xlsxwriter or should I use a different library to the same?
我想在控制台上打印 E5 中的值。你能帮我做吗？是否可以使用 Xlsxwriter 来完成，还是应该使用不同的库？
采纳答案by jmcnamara
It is not possible to read data from an Excel file using XlsxWriter.
无法使用 XlsxWriter 从 Excel 文件中读取数据。
There are some alternatives listed in the documentation.
回答by amandeep1991
Not answering this specific question, just a suggestion- simply try pandas and read data from excel. Thereafter you can simply manipulate the data using pandas DataFrame built-in methods:
不回答这个具体问题，只是一个建议- 只需尝试 Pandas 并从 excel 读取数据。此后，您可以使用 pandas DataFrame 内置方法简单地操作数据：
df = pd.read_excel(file_,index_col=None, header=0)
df is the pandas.DataFrame, just go through DataFrame from this it's cookbook site. If you are unaware about this package, you might get surprised by this awesome python module.
df 是 pandas.DataFrame，只需从它的食谱网站浏览 DataFrame 。如果你不知道这个包，你可能会对这个很棒的 python 模块感到惊讶。
回答by Tim Baker
If you want to use xlsxwriter for manipulating formats and formula that you can't do with pandas, you can at least import your excel file into an xlsxwriter object using pandas. Here's how.
如果您想使用 xlsxwriter 来处理您无法使用 Pandas 处理的格式和公式，您至少可以使用 Pandas 将您的 excel 文件导入到 xlsxwriter 对象中。就是这样。
import pandas as pd
import xlsxwriter
def xlsx_to_workbook(xlsx_in_file_url, xlsx_out_file_url, sheetname):
"""
Read EXCEL file into xlsxwriter workbook worksheet
"""
workbook = xlsxwriter.Workbook(xlsx_out_file_url)
worksheet = workbook.add_worksheet(sheetname)
#read my_excel into a pandas DataFrame
df = pd.read_excel(xlsx_in_file_url)
# A list of column headers
list_of_columns = df.columns.values
for col in range(len(list_of_columns)):
#write column headers.
#if you don't have column headers remove the folling line and use "row" rather than "row+1" in the if/else statments below
worksheet.write(0, col, list_of_columns[col] )
for row in range (len(df)):
#Test for Nan, otherwise worksheet.write throws it.
if df[list_of_columns[col]][row] != df[list_of_columns[col]][row]:
worksheet.write(row+1, col, "")
else:
worksheet.write(row+1, col, df[list_of_columns[col]][row])
return workbook, worksheet
# Create a workbook
#read you Excel file into a workbook/worksheet object to be manipulated with xlsxwriter
#this assumes that the EXCEL file has column headers
workbook, worksheet = xlsx_to_workbook("my_excel.xlsx", "my_future_excel.xlsx", "My Sheet Name")
###########################################################
#Do all your fancy formatting and formula manipulation here
###########################################################
#write/close the file my_new_excel.xlsx
workbook.close()
