我正在使用我编写的下面的类来尝试动态创建一个包含多个工作表的单个Excel文件,其中每个工作表中都有一个打印的数据框和一个柱形图。在
在启动工作簿时,与代码的交互(如下所示)应该起作用:test = Workbook('Test Workbook')
然后,您可以添加任意数量的图表:
^{pr2}$
然后制作出工作手册:test.produce()
输入数据帧有标头。第一列是文本类别,后面的列(数目不等)是以小数形式表示的数据,这些数据将以百分比表示。在
问题:代码运行得相当好,似乎所有的工作表都与图表分开生成,但有些图表显示为“未引用”,这意味着当我单击柱形图中的条形图时,它不会突出显示源数据。一些用代码制作的图表,做了适当的参考,所以我不确定问题在哪里,也没有明显的趋势。在import xlsxwriter
import pandas as pd
class Workbook:
def __init__(self, workbook_name):
self.workbook_name = workbook_name
self.workbook = xlsxwriter.Workbook(str(self.workbook_name) + '.xlsx')
self.letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P']
def produce(self):
self.workbook.close()
print 'Created ' + str(self.workbook_name) + '.xlsx'
def print_frame(self, worksheet, dataframe, df_width, start_data_index):
col_as_lists = []
col_names = list(dataframe.columns.values)
# loops through columns in df and converts to list
for n in range(0, df_width):
col_n = dataframe[col_names[n]].tolist()
# checks to see if column has numbers, if so -> convert to float!
if n < start_data_index:
col_n.insert(0, col_names[n])
elif self.is_number(col_n[0]):
convert = col_n[0:]
convert = [float(x) for x in convert]
convert.insert(0, col_names[n])
col_n = convert
else:
col_n.insert(0, col_names[n])
col_as_lists.append(col_n)
# Prints each list into the worksheet.
worksheet.write_column(self.letters[n] + '1', col_as_lists[n])
#Formats numerical data as percentage
percentformat = self.workbook.add_format({'num_format': '0%'})
worksheet.set_column(self.letters[start_data_index] + ':' + self.letters[df_width], None, percentformat)
def add_chart(self, dataframe, tab_name, start_data_index):
df_width = len(dataframe.columns)
worksheet = self.workbook.add_worksheet(tab_name)
self.print_frame(worksheet, dataframe, df_width, start_data_index)
chart = self.workbook.add_chart({'type': 'column'})
df_length = (len(dataframe.index))
for n in range(start_data_index, df_width):
chart.add_series({
'name': '=' + tab_name +'!$' + self.letters[n] + '$1',
'categories': '=' + tab_name +'!$' + self.letters[start_data_index - 1] + '$2:$'+ self.letters[start_data_index - 1] + '$' + str(df_length + 1),
'values': '=' + tab_name +'!$' + self.letters[n] + '$2:$'+ self.letters[n] + '$' + str(df_length + 1),
'fill': {'color': '#FFB11E'},
'data_labels': {'value': True, 'center': True}
})
chart.set_title({'name': tab_name})
chart.set_x_axis({'major_gridlines': {'visible': False}})
chart.set_y_axis({'major_gridlines': {'visible': False}, 'max': .70})
worksheet.insert_chart(self.letters[df_width + 2] + '2', chart)
return
def is_number(self, s):
""" Function used to help with detecting and converting floats
from string to number data types."""
try:
float(s)
return True
except ValueError:
return False