def write_excel_rds(self):
work = workbook.Workbook(self.filename)
worksheet = work.add_worksheet()
format_title = work.add_format({'bold':True,'font_size':12})
format_title.set_align('center')
format_title.set_align('vcenter')
format_body = work.add_format({'font_size':10})
format_color_red = work.add_format({'bold': True, 'font_size': 10, 'bg_color': 'red'}) #加粗字体并调整字体背景颜色
format_color_red.set_align('center')
format_color_red.set_align('vcenter')
format_body.set_align('center')
format_body.set_align('vcenter')
format_color_yellow = work.add_format({'bold': True, 'font_size': 10, 'bg_color': 'yellow'}) # 加粗字体并调整字体背景颜色
format_color_yellow.set_align('center')
format_color_yellow.set_align('vcenter')
worksheet.set_row(0, 22)
worksheet.set_column(0, 0, 27)
worksheet.set_column(1, 1, 20)
worksheet.set_column(2, 3, 20)
worksheet.set_column(4, 3, 15)
worksheet.set_column(6, 5, 15)
title = self.title
row = 0
col = 0
for item in title:
worksheet.write(row, col, item, format_title)
col += 1
for line in self.id:
row += 1
col = 0
worksheet.write(row, col, line, format_body)
row = 0
for line2 in self.alias:
row += 1
col = 1
worksheet.write(row, col, line2, format_body)
row = 0
for line3 in self.times:
row +=1
col = 2
worksheet.write(row, col, line3, format_body)
row = 1
for disk_line in self.disk_info: #遍历每个实例的信息
col = 2
for key in disk_line: #遍历单个实例的信息
col += 1
if type(key) is int and key >= 80 and key < 85:
worksheet.write(row, col, key, format_color_yellow)
elif type(key) is float and key >= 80 and key <85:
worksheet.write(row, col, key, format_color_yellow)
elif type(key) is int and key >= 85:
worksheet.write(row, col, key, format_color_red)
elif type(key) is float and key >= 85:
worksheet.write(row, col, key, format_color_red)
else:
worksheet.write(row, col, key, format_body)
row +=1
work.close()
def write_excel(file, data):
'''
1、设置 Excel 样式
2、将数据写入到 Excel 中
'''
# 生成 Excel 文件
work = workbook.Workbook(file)
# 建立工作表，表名默认
worksheet = work.add_worksheet()
# 设置字体加粗、字体大小
format_title = work.add_format({'bold': True, 'font_size': 16})
# 设置水平对齐、垂直对齐
format_title.set_align('center')
format_title.set_align('vcenter')
format_body = work.add_format({'font_size': 14})
# 设置样式，行高、列宽
worksheet.set_row(0, 25)
worksheet.set_column(0, 0, 30)
worksheet.set_column(1, 1, 20)
worksheet.set_column(2, 3, 28)
worksheet.set_column(4, 5, 25)
worksheet.set_column(6, 6, 12)
worksheet.set_column(7, 9, 16)
worksheet.set_column(10, 11, 25)
# 定义表头
title = (
'实例 ID',
'所在区域',
'主机名称',
'主机别名',
'公网地址',
'私网地址',
'CPU 核数',
'内存大小 MB',
'网络带宽 MB',
'运行状态',
'创建时间',
'过期时间'
)
row = 0
col = 0
# 表头写入文件，引用样式
for item in title:
worksheet.write(row, col, item, format_title)
col+=1
# 内容写入文件，引用样式
for line in data:
row+=1
col = 0
for key in line:
worksheet.write(row, col, key, format_body)
col+=1
work.close()
def __setup_workbook(self):
# Workbook() takes one, non-optional, argument
# which is the filename that we want to create.
self.__workbook = workbook.Workbook(self.__file, {'strings_to_numbers': True})
# The workbook object is then used to add new
# worksheet via the add_worksheet() method.
self.__worksheet = self.__workbook.add_worksheet(name="Summary") # name=dt.today().strftime("%y%m%d"))
self.__worksheet_error = self.__workbook.add_worksheet(name="Errors Chart")
self.__worksheet_remark = self.__workbook.add_worksheet(name="Remarks Chart")
self.__chart_error = self.__workbook.add_chart(self.__chart_type)
self.__chart_remark = self.__workbook.add_chart(self.__chart_type)
self.__bold = self.__workbook.add_format({'bold': True})
def write(self, data):
if self.suffix == 'xlsx':
from xlsxwriter import workbook
wb = workbook.Workbook(self.path)
ws = wb.add_worksheet()
self._workbook_write(ws, data, isinstance(data[0], dict))
wb.close()
elif self.suffix == 'txt':
ff = open(self.path, 'w', encoding='utf8')
for item in data:
line = self.delimiter.join(item)
ff.write(line + '\n')
ff.close()
def _make_xlsx(metrics, hours):
logging.debug('Creating spreadsheet.')
ext = '_metrics.xlsx'
save_path = metrics[PRES_PATH]
if (metrics[PRES_COUNT] == 1):
for key in metrics[PRES_DICT]:
pres_name = str(key)
file_name = pres_name[0:-5]
else:
pres_name = ''
file_name = save_path.parts[-1]
file_name += ext
full_name = Path.joinpath(save_path,file_name)
logging.debug('Spreadsheet \'%s\' will be created at %s.',file_name,save_path)
#Create the spreadsheet
spreadsheet = workbook.Workbook(full_name)
default_cells = spreadsheet.add_format({'font_color':'black'})
yellow_cells = spreadsheet.add_format({'font_color':'#BBBB00'})
red_cells = spreadsheet.add_format({'font_color':'red'})
pres_data = metrics[PRES_DICT]
#Cover-page
################
# Folder path, no of pres || File name
#
# Tot wordcount: ...
#TODO: wordcount comment (how long would this take the slowest reader to read.)
# [ Planned hours: ...
# WPM Note: ... ]
#
# Pres name | slide count | word count | wps
# Pres1.pptx| Num | NUM | Num
# ...
################
coversheet = spreadsheet.add_worksheet("Summary")
row = 0
col = 0
#Title
if (metrics[PRES_COUNT] == 1): #If there is only one presentation, we'll put it here.
coversheet.write(row, col,str(Path.joinpath(save_path,pres_name)))
col0_max = len(str(Path.joinpath(save_path,pres_name)))
else: # If > 1, put folder here and number of presentations.
coversheet.write(row, col + 0, str(save_path))
coversheet.write(row, col + 1, str(metrics[PRES_COUNT]) + ' presentations')
col0_max = len(str(save_path))
col13_max = 14
row += 2
#Brief
tot_words = 0
for key in pres_data:
tot_words += pres_data[key][0]
coversheet.write(row, col + 0, "Total Wordcount: " + str(tot_words))
if hours > 0: #Only do this is time specified
tot_wpm, tot_wpmcomment = _get_wpm_comment(tot_words, hours)
row += 1
coversheet.write(row, col + 0, "Planned hours: " + str(hours))
row += 1
coversheet.write(row, col + 0, "Words/Min: " + str(tot_wpm) + "WPM - " + tot_wpmcomment)
row += 2
#Body
coversheet.write(row, col + 0, "Title")
coversheet.write(row, col + 1, "Slide count")
coversheet.write(row, col + 2, "Word count")
coversheet.write(row, col + 3, "Words/Slide")
for key in pres_data:
row += 1
coversheet.write(row, col + 0, key)
coversheet.write(row, col + 1, len(pres_data[key][1]))
coversheet.write(row, col + 2, pres_data[key][0])
coversheet.write(row, col + 3, int(pres_data[key][0]/len(pres_data[key][1])))
if len(key) > col0_max: col0_max = len(key) + 2
coversheet.set_column(0, 0, col0_max)
coversheet.set_column(1, 3, col13_max)
logging.debug('Coversheet prepared.')
# Data-sheets
##########
# Pres Name
# Slide No | Slide Title | Word Count
# 1 | Blah | ...
# ...
####
# Pres Name
# Slide No | Slide Title | Word Count
# 1 | Blah | ...
# ...
####
# ...
#########
for keys in pres_data:
logging.debug('Creating worksheet for %s (%s slides).',keys, pres_data[keys])
datasheet = spreadsheet.add_worksheet(keys.split()[0][0:30]) #Doesn't allow more than 31 chars - this takes first word and cuts if still too long
slide_title = _get_titles(pres_data[keys][2])
col = 0
row = 0
# Title
datasheet.write(row, col + 0, keys)
row += 1
# Data
datasheet.write(row, col + 0, "Slide No.")
datasheet.write(row, col + 1, "Word count")
datasheet.write(row, col + 2, "Title")
datasheet.write(row, col + 3, "Comment")
col2_max = 10
col3_max = 12
for slides in pres_data[keys][1]:
cell_colour = default_cells
row += 1
slide_wc = pres_data[keys][1][slides]
slide_comment = _get_slide_comment(slide_wc)
if 'warning' in slide_comment.lower() or 'caution' in slide_comment.lower():
cell_colour = yellow_cells
elif 'extreme' in slide_comment.lower():
cell_colour = red_cells
datasheet.write(row, col + 0, slides, cell_colour)
datasheet.write(row, col + 1, slide_wc, cell_colour)
datasheet.write(row, col + 2, slide_title[slides], cell_colour)
datasheet.write(row, col + 3, slide_comment, cell_colour)
if len(slide_title[slides]) > col2_max: col2_max = len(slide_title[slides])
if len(slide_comment) > col3_max: col3_max = len(slide_comment)
datasheet.set_column(0, 1, 10) # Col A, B set to 10
datasheet.set_column(2, 2, col2_max) # Col C dynamic with slide title
datasheet.set_column(3, 3, col3_max) # Col D dynamic with comment
spreadsheet.close()
return full_name