Tôi đang thực hiện tính toán sau.
worksheet.write_formula['E5', '=[{} - A2]'.format[number]]
Tôi muốn in giá trị trong E5 trên bảng điều khiển. Bạn có thể giúp tôi làm điều đó? Có thể làm điều đó với xlsxwriter hay tôi nên sử dụng một thư viện khác để giống nhau?
Hỏi ngày 10 tháng 12 năm 2013 lúc 9:20Dec 10, 2013 at 9:20
2
Nếu bạn muốn sử dụng xlsxwriter để thao tác các định dạng và công thức mà bạn không thể làm với gấu trúc, bạn ít nhất có thể nhập tệp excel của mình vào đối tượng XLSXWRITER bằng gấu trúc. Đây là cách.
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[]
Đã trả lời ngày 19 tháng 4 năm 2019 lúc 9:08Apr 19, 2019 at 9:08
Không trả lời câu hỏi cụ thể này, chỉ là một gợi ý - chỉ cần thử gấu trúc và đọc dữ liệu từ Excel. Sau đó, bạn có thể chỉ cần điều khiển dữ liệu bằng các phương thức tích hợp Pandas DataFrame:just a suggestion - simply try pandas and read data from excel. Thereafter you can simply manipulate the data using pandas DataFrame built-in methods:
df = pd.read_excel[file_,index_col=None, header=0]
DF là pandas.dataframe, chỉ cần đi qua DataFrame từ trang web Cookbook này. Nếu bạn không biết về gói này, bạn có thể ngạc nhiên bởi mô -đun Python tuyệt vời này.awesome python module.
Đã trả lời ngày 23 tháng 10 năm 2018 lúc 16:58Oct 23, 2018 at 16:58
amandeep1991amandeep1991amandeep1991
1.31413 Huy hiệu bạc17 Huy hiệu đồng13 silver badges17 bronze badges
Các phần sau đây giải thích cách viết các loại dữ liệu khác nhau vào bảng tính Excel bằng XLSXWRITER.
Viết dữ liệu vào một ô bảng tính
Phương pháp
df = pd.read_excel[file_,index_col=None, header=0]
0 df = pd.read_excel[file_,index_col=None, header=0]
1 là phương tiện phổ biến nhất để viết dữ liệu Python cho các ô dựa trên loại của nó:import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]
Phương pháp
df = pd.read_excel[file_,index_col=None, header=0]
1 sử dụng df = pd.read_excel[file_,index_col=None, header=0]
3 của dữ liệu để xác định phương pháp cụ thể nào để sử dụng để viết dữ liệu. Các phương pháp này sau đó ánh xạ một số loại Python cơ bản đến các loại Excel tương ứng. Ánh xạ như sau:
4 | Con số | 1, 6 |
7 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | 9 | import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]0 |
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]1 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | 9 | import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]0 |
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]1 | Con số | 1, 6 |
7 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | & nbsp; | & nbsp; |
8 | 9 | import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]0 |
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]1
4 | Con số | 1, 6 |
7 | & nbsp; | 8 |
df = pd.read_excel[file_,index_col=None, header=0]
9Writing user defined types section below.import xlsxwriter
workbook = xlsxwriter.Workbook['write_data.xlsx']
worksheet = workbook.add_worksheet[]
worksheet.write[0, 0, 1234] # Writes an int
worksheet.write[1, 0, 1234.56] # Writes a float
worksheet.write[2, 0, 'Hello'] # Writes a string
worksheet.write[3, 0, None] # Writes None
worksheet.write[4, 0, True] # Writes a bool
workbook.close[]
0
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]1
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]
Sợi dây
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]
df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]3
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [[1, 1, 1, 1, 1], [2, 2, 2, 2, 1], [3, 3, 3, 3, 1], [4, 4, 4, 4, 1], [5, 5, 5, 5, 1]] for row_num, row_data in enumerate[my_list]: for col_num, col_data in enumerate[row_data]: worksheet.write[row_num, col_num, col_data] workbook.close[]
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]4worksheet class has two utility functions called
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]5 and
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]6 which are basically a loop around the
df = pd.read_excel[file_,index_col=None, header=0]
1 method:import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]
import xlsxwriter
workbook = xlsxwriter.Workbook['write_data.xlsx']
worksheet = workbook.add_worksheet[]
worksheet.write[0, 0, 1234] # Writes an int
worksheet.write[1, 0, 1234.56] # Writes a float
worksheet.write[2, 0, 'Hello'] # Writes a string
worksheet.write[3, 0, None] # Writes None
worksheet.write[4, 0, True] # Writes a bool
workbook.close[]
5
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]6
import xlsxwriter workbook = xlsxwriter.Workbook['write_dict.xlsx'] worksheet = workbook.add_worksheet[] my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items[]: worksheet.write[0, col_num, key] worksheet.write_column[1, col_num, value] col_num += 1 workbook.close[]
Chuỗi [trống]
df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]8
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]9
import pandas as pd # Create a Pandas dataframe from the data. df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}] # Create a Pandas Excel writer using XlsxWriter as the engine. writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter'] # Convert the dataframe to an XlsxWriter Excel object. df.to_excel[writer, sheet_name='Sheet1'] # Close the Pandas Excel writer and output the Excel file. writer.save[]
df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]1
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]2Working with Python Pandas and XlsxWriter.
import xlsxwriter
workbook = xlsxwriter.Workbook['write_list.xlsx']
worksheet = workbook.add_worksheet[]
my_list = [1, 2, 3, 4, 5]
for row_num, data in enumerate[my_list]:
worksheet.write[row_num, 0, data]
workbook.close[]
3
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]4
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]5
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[]
0Boolean
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[]
1df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate[my_list]: worksheet.write[row_num, 0, data] workbook.close[]7
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[]
2Phương pháp
df = pd.read_excel[file_,index_col=None, header=0]
1 cũng xử lý một vài loại excel khác được mã hóa dưới dạng chuỗi python trong 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[]
3Chuỗi công thức
Công thức
df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]0
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[]
4Chuỗi URL
URL
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[]
5df = pd.read_excel[file_,index_col=None, header=0]
1, import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]2worksheet instance, an integer
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]4 and
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]5 value, a
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]6 that matches the type added to
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [[1, 1, 1, 1, 1], [2, 2, 2, 2, 1], [3, 3, 3, 3, 1], [4, 4, 4, 4, 1], [5, 5, 5, 5, 1]] for row_num, row_data in enumerate[my_list]: for col_num, col_data in enumerate[row_data]: worksheet.write[row_num, col_num, col_data] workbook.close[]0 and some additional parameters. Usually the additional parameter[s] will only be a cell format instance. However, if you need to handle other additional parameters, such as those passed to
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate[my_list]: worksheet.write[0, col_num, data] workbook.close[]2 then you can have more generic handling like this:
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[]
6Lưu ý, bạn không phải xử lý rõ ràng các phạm vi tế bào kiểu
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]9. Chúng sẽ được chuyển đổi thành giá trị hàng và cột trước khi chức năng của bạn được gọi.
Bạn cũng có thể sử dụng các tham số
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]4 và
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [1, 2, 3, 4, 5] worksheet.write_row[0, 1, my_list] worksheet.write_column[1, 0, my_list] workbook.close[]5 để kiểm soát logic của hàm. Ví dụ, bạn muốn ẩn/thay thế mật khẩu người dùng bằng ‘**** khi viết dữ liệu chuỗi. Nếu dữ liệu của bạn được cấu trúc sao cho dữ liệu mật khẩu ở cột thứ hai, ngoài hàng tiêu đề, bạn có thể viết một hàm xử lý như thế này:
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[]
7Giá trị trả về của các hàm xử lý ghi
Các chức năng được sử dụng trong phương thức
import xlsxwriter workbook = xlsxwriter.Workbook['write_list.xlsx'] worksheet = workbook.add_worksheet[] my_list = [[1, 1, 1, 1, 1], [2, 2, 2, 2, 1], [3, 3, 3, 3, 1], [4, 4, 4, 4, 1], [5, 5, 5, 5, 1]] for row_num, row_data in enumerate[my_list]: for col_num, col_data in enumerate[row_data]: worksheet.write[row_num, col_num, col_data] workbook.close[]0 sẽ trả về một trong các giá trị sau:
import xlsxwriter workbook = xlsxwriter.Workbook['write_data.xlsx'] worksheet = workbook.add_worksheet[] worksheet.write[0, 0, 1234] # Writes an int worksheet.write[1, 0, 1234.56] # Writes a float worksheet.write[2, 0, 'Hello'] # Writes a string worksheet.write[3, 0, None] # Writes None worksheet.write[4, 0, True] # Writes a bool workbook.close[]
6: Để chỉ ra rằng điều khiển là quay trở lại phương thức cha mẹ
1 để tiếp tục như bình thường. Điều này được sử dụng nếu logic chức năng xử lý của bạn quyết định rằng bạn không cần phải xử lý mã thông báo phù hợp.df = pd.read_excel[file_,index_col=None, header=0]
- Giá trị trả về của hàm
import xlsxwriter workbook = xlsxwriter.Workbook['write_dict.xlsx'] worksheet = workbook.add_worksheet[] my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items[]: worksheet.write[0, col_num, key] worksheet.write_column[1, col_num, value] col_num += 1 workbook.close[]
5 được gọi là. Đây thường là 0 không có lỗi và số âm cho lỗi. Điều này gây ra sự trở lại ngay lập tức từ phương thức gọi
1 với giá trị trả về đã được chuyển trở lại.df = pd.read_excel[file_,index_col=None, header=0]
Ví dụ: giả sử bạn muốn bỏ qua các giá trị
import xlsxwriter workbook = xlsxwriter.Workbook['write_dict.xlsx'] worksheet = workbook.add_worksheet[] my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items[]: worksheet.write[0, col_num, key] worksheet.write_column[1, col_num, value] col_num += 1 workbook.close[]7 trong dữ liệu của mình vì Excel không hỗ trợ họ. Bạn có thể tạo một chức năng xử lý như sau phù hợp với phao và đã viết một ô trống nếu đó là một
import xlsxwriter workbook = xlsxwriter.Workbook['write_dict.xlsx'] worksheet = workbook.add_worksheet[] my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items[]: worksheet.write[0, col_num, key] worksheet.write_column[1, col_num, value] col_num += 1 workbook.close[]7 hoặc nếu không thì vừa trở lại
df = pd.read_excel[file_,index_col=None, header=0]
1 để tiếp tục như bình thường: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[]
8Nếu bạn muốn hoàn toàn giảm hoàn toàn các giá trị
import xlsxwriter workbook = xlsxwriter.Workbook['write_dict.xlsx'] worksheet = workbook.add_worksheet[] my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items[]: worksheet.write[0, col_num, key] worksheet.write_column[1, col_num, value] col_num += 1 workbook.close[]7 và không thêm bất kỳ định dạng nào vào ô, bạn chỉ có thể trả về 0, không có lỗi:
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[]
9