def creates_new_worksheet(
workbook: xlsxwriter.Workbook, filename: str, wavelength_range: list,
full_values: dict) -> xlsxwriter.Workbook.worksheet_class:
"""
Creates a new worksheet inside the workbook object.
"""
# Wavelength values are coming string type, for chart I need numbers
wavelength_range = [float(wv) for wv in wavelength_range]
worksheet = workbook.add_worksheet(f'{filename[:30]}')
worksheet.write(0, 0, 'Criado por:')
worksheet.write(0, 1, 'https://analytools.herokuapp.com')
worksheet.write(1, 0, 'nm')
worksheet.write_column(2, 0, wavelength_range)
row = 1
col = 1
for sample, data in full_values.items():
worksheet.write(row, col, sample)
worksheet.write_column(row + 1, col, data)
col += 1
# Creates a chart type
chart = workbook.add_chart({'type': 'scatter', 'subtype': 'smooth'})
# Creates a big list of letters, which represents columns of worksheets
letters_list = list(ascii_uppercase) + [
('A' + letter) for letter in list(ascii_uppercase)
] + [('B' + letter) for letter in list(ascii_uppercase)
] + [('C' + letter) for letter in list(ascii_uppercase)]
for i in range(len(full_values.items())):
chart.add_series({
'categories':
f'={filename}!$A$3:$A${len(wavelength_range) + 2}',
'values':
f'={filename}!${letters_list[i + 1]}$3:'
f'${letters_list[i + 1]}${len(wavelength_range) + 2}',
})
chart.set_title({'name': f'{filename}'})
chart.set_x_axis({
'name': 'nm',
'min': wavelength_range[0],
'max': wavelength_range[len(wavelength_range) - 1],
})
chart.set_y_axis({'name': 'A'})
chart.set_size({'width': 650, 'height': 500})
worksheet.insert_chart('E4', chart)
def write_excel(date, name, names=None, shift=1, id_col=0, chart_cell='D1', val_col=1):
workbook = Workbook(name)
sheet_name = 'TextTiling'
worksheet = workbook.add_worksheet(sheet_name)
i = 0
for value in date:
if names == None:
value_name = "%d - %d" % (i + shift, i + shift + 1)
else:
value_name = names[i]
worksheet.write(i, id_col, value_name)
worksheet.write(i, val_col, value)
i += 1
chart = workbook.add_chart({ 'type': 'column'})
chart.add_series({
'name': 'Cosines',
'categories': [sheet_name, 0, id_col, i - 1, id_col],
'values': [sheet_name, 0, val_col, i - 1, val_col],
})
chart.set_size({'width': 920, 'height': 776})
worksheet.insert_chart(chart_cell, chart)
workbook.close()
def excel_writer(arr):
book_name = 'Products Survey%s.xlsx' % strftime('%Y%m%d%H%m%S',
localtime())
sheet_name = 'Product Information'
review_detail_name = 'Review Detail'
wb = Workbook(book_name, options={'strings_to_urls': 0})
ws = wb.add_worksheet(sheet_name)
ws2 = wb.add_worksheet(review_detail_name)
ncol = len(pro_infos)
style = wb.add_format({
'font_name': 'Times New Roman',
'align': 'center',
'valign': 'vcenter',
'border': 1,
})
red = wb.add_format({
'color': 'red',
'bold': 1,
'align': 'center',
'valign': 'vcenter',
'border': 1,
'font_name': 'Times New Roman'
})
green = wb.add_format({
'color': 'green',
'bold': 1,
'align': 'center',
'valign': 'vcenter',
'border': 1,
'font_name': 'Times New Roman'
})
blue = wb.add_format({
'color': 'blue',
'bold': 1,
'align': 'center',
'valign': 'vcenter',
'border': 1,
'font_name': 'Times New Roman'
})
orange = wb.add_format({
'color': 'orange',
'bold': 1,
'align': 'center',
'valign': 'vcenter',
'border': 1,
'font_name': 'Times New Roman'
})
style_arr = [red, green, orange, blue]
style_arr.extend([style_arr] * 16)
style.set_text_wrap()
ws.set_column(1, 21, 20)
ws.set_column(0, 0, 10)
row_high = [(0, 20), (1, 120), (2, 50), (3, 20), (4, 20), (5, 20), (6, 20),
(7, 20), (8, 20), (9, 20), (10, 120), (11, 20), (12, 20),
(13, 20), (14, 120)]
for item in row_high:
ws.set_row(item[0], item[1])
first_col = [
'产品信息', '产品图片', '类目', '尺寸', '尺寸（cm）', '重量', '重量（kg）', '卖家', '发货方式',
'评论数', '评分', '售价', '上架日期', '销量', '销量趋势'
]
for j in range(len(first_col)):
ws.write(j, 0, first_col[j], style)
for j in range(20):
ws.write(0, j + 1, j + 1, style)
for j in range(1, 602):
ws2.set_row(j + 1, 23)
ws2.merge_range(1, 0, 6, 0, '产品图片', style)
nn = 7
for j in range(102):
ws2.merge_range(5 * j + nn, 0, 5 * j + nn + 1, 0, '标题', style)
ws2.write(5 * j + nn + 2, 0, '评分', style)
ws2.write(5 * j + nn + 3, 0, '款式', style)
ws2.write(5 * j + nn + 4, 0, '评论时间', style)
ws2.set_column(1, 3 * ncol, 20)
ws2.set_column(0, 0, 10)
for j in range(ncol):
ws2.merge_range(0, 3 * j + 1, 0, 3 * j + 3, j + 1, style)
ws2.merge_range(1, 3 * j + 1, 6, 3 * j + 3, '', style)
for I in range(ncol):
try:
url = pro_infos[I][1]['Url']
col_num = 1
asin = pro_infos[I][1]['ASIN']
pic_link = pro_infos[I][1]['Picture']
get_img(pic_link, asin)
rank = pro_infos[I][1]['Best Sellers Rank']
size = pro_infos[I][1]['Package Dimensions']
weight = pro_infos[I][1]['Shipping Weight']
cm_size = pro_infos[I][1]['Package Dimensions(cm)']
kg_weight = pro_infos[I][1]['Shipping Weight(kg)']
price = pro_infos[I][1]['Price']
review_num = pro_infos[I][1]['Customer Reviews']
saler = pro_infos[I][1]['Saler']
delivery = pro_infos[I][1]['Delivery Way']
first_date = pro_infos[I][1]['Date First Available']
review_rate = pro_infos[I][1]['Review Rate']
one_star = pro_infos[I][1]['Review Details']['1 star']
two_star = pro_infos[I][1]['Review Details']['2 star']
three_star = pro_infos[I][1]['Review Details']['3 star']
four_star = pro_infos[I][1]['Review Details']['4 star']
five_star = pro_infos[I][1]['Review Details']['5 star']
stars = [one_star, two_star, three_star, four_star, five_star]
ws.insert_image(
col_num, I + 1, 'images/%s.jpg' % asin, {
'x_scale': 0.2,
'y_scale': 0.2,
'x_offset': 10,
'y_offset': 10,
'url': url
})
ws.write(col_num, I + 1, '', style)
col_num += 1
name_list = ['1 star', '2 star', '3 star', '4 star', '5 star']
num_list = [int(stars[j].strip().strip('%')) for j in range(5)]
grey_list = [100 - j for j in num_list]
rects = plt.barh(range(5),
num_list,
tick_label=name_list,
color='orange')
plt.barh(range(5),
grey_list,
color='grey',
left=[rect.get_width() for rect in rects])
k = 0
for rect in rects:
plt.text(102,
rect.get_y() + rect.get_height() / 2,
str(num_list[k]) + '%',
ha='left',
va='center',
fontdict={'fontsize': 'xx-large'})
plt.text(0,
rect.get_y() + rect.get_height() / 2,
name_list[k],
ha='right',
va='center',
fontdict={
'color': 'blue',
'fontsize': 'xx-large'
})
k += 1
plt.text(10, -1, str(review_rate), fontdict={'fontsize': 30})
plt.axis('off')
if not exists('reviews_shot'):
mkdir('reviews_shot')
plt.savefig('reviews_shot/%s.jpg' % asin)
plt.close()
row_value = [
str(rank),
str(size),
str(cm_size),
str(weight),
str(kg_weight),
str(saler),
str(delivery),
int(review_num[:-16].replace(',', ''))
]
for j in range(len(row_value)):
ws.write(j + col_num, I + 1, row_value[j], style)
col_num += len(row_value)
ws.insert_image(col_num, I + 1, 'reviews_shot/%s.jpg' % asin, {
'x_scale': 0.2325,
'y_scale': 0.3,
'x_offset': 3,
'y_offset': 13
})
ws.write(col_num, I + 1, '', style)
col_num += 1
row_value2 = [float(price[1:]), str(first_date)]
for j in range(len(row_value2)):
ws.write(j + col_num, I + 1, row_value2[j], style)
col_num += len(row_value2)
# 销量， 销量趋势
for j in range(2):
ws.write(j + col_num, I + 1, '', style)
col_num += 2
ws.write(100, I + 1, str(url))
ws2.insert_image(
1, 3 * I + 1, 'images/%s.jpg' % asin, {
'x_scale': 0.2,
'y_scale': 0.2,
'x_offset': 180,
'y_offset': 10,
'url': url
})
logging.info('准备翻译评论--ASIN: %s ......\n' % asin)
threads = [
TranslaterThread(wb, ws2, pro_infos[I][1]['Reviews info'],
style, arr) for j in range(10)
]
for thread in threads:
thread.start()
for thread in threads:
thread.join()
except KeyError as e:
logging.info(e, url)
for I in range(len(arr[:20])):
ws2.write(0, 3 * ncol + 1, 'Words', style)
ws2.write(0, 3 * ncol + 2, 'Translation', style)
ws2.write(0, 3 * ncol + 3, 'Frequency', style)
ws2.write(I + 1, 3 * ncol + 1, str(arr[I][0]), style)
try:
ws2.write(I + 1, 3 * ncol + 2, translate_format(arr[I][0]), style)
except KeyError:
logging.info(arr[I][0])
ws2.write(I + 1, 3 * ncol + 3, int(arr[I][1]), style)
# 插入词频饼状图
chart = wb.add_chart({'type': 'pie'})
chart.add_series({
'name':
'Word Frequency Data',
'categories':
['%s' % review_detail_name, 1, 3 * ncol + 1, 11, 3 * ncol + 1],
'values':
['%s' % review_detail_name, 1, 3 * ncol + 3, 11, 3 * ncol + 3],
'data_labels': {
'value': 1,
'category': 1
}
})
chart.set_title({'name': 'Word Frequency Top 10'})
chart.set_style(10)
ws2.insert_chart(4, 3 * ncol + 4, chart, {'x_offset': 25, 'y_offset': 20})
logging.info('\nExcel写入完成......')
wb.close()
planilha.set_column('A:A', 20)
planilha.set_column('B:B', 20)
bold = excel_file.add_format({'bold': True})
# Creating DataSet to create chart
header = ['CATEGORY', 'VALUES']
data = [['Maca', 'Uva', 'Pera', 'Morango'], [30, 13, 17, 40]]
planilha.write_row('A1', header, bold)
planilha.write_column('A2', data[0])
planilha.write_column('B2', data[1])
# Creating Chart
chart = excel_file.add_chart({'type': 'pie'})
chart.add_series({
'name': 'Fruits liked',
'categories': ['Sheet1', 1, 0, 4, 0],
'values': ['Sheet1', 1, 1, 4, 1]
})
chart.set_title({'name': 'My fruits most liked'})
chart.set_style(10)
planilha.insert_chart('D1', chart, {'x_offset': 20, 'y_offset': 25})
# Adding image to plan
planilha.insert_image("A20", "logo.png")
excel_file.close()
def anter(fname):
#read through 'text' file
with open(fname) as f:
content = [x.split() for x in f.readlines()]
#Store document title and material title
title = content[6][3]
material = " ".join([x for x in content[4][3:]])
#Clean up material title
if "THERMAL EXPANSION" in material:
material = material.replace("THERMAL EXPANSION ","")
savnam = "{0}.xlsx".format(title)
#Storing temp and expansion values
temp_column = [float(n[1]) for n in content[16:]]
expansion_column = [(float(n[3]) / 0.000001) for n in content[16:]]
max_temp_idx = temp_column.index(max(temp_column))
temp_index = []
desired_temps = [1000,1260,1538]
for temp in desired_temps:
#In case, machinery fails halfway through
try:
temp_index.append([i + 5 for i, x in enumerate(temp_column[:max_temp_idx]) if (temp - x) > 0 and (temp - x) < 1][0])
except IndexError:
temp_index.append(max_temp_idx)
temp_1000 , temp_1260 , temp_1538 = temp_index
workbook = Workbook(savnam)
worksheet = workbook.add_worksheet()
chartsheet = workbook.add_chartsheet()
deg = u"\u2103"
xl_values = {
'A1':'Temperature','B1':'Expansion',
'A2' : '(' + deg + ')','B2' : '(ppm)',
'E4' : 'Expansion','F4' : 'Temperature',
'G4' : 'Row',
'F5' : desired_temps[0],'F6' : desired_temps[1],'F7' : desired_temps[2],
'G5' : temp_1000,'G6' : temp_1260,'G7' : temp_1538
}
for key, val in xl_values.items():
worksheet.write(key,val)
worksheet.set_column('A:A', 11)
worksheet.write_column('A4', temp_column)
worksheet.write_column('B4', expansion_column)
worksheet.set_column('F:F', 11)
worksheet.write_formula('E5','=ROUND(SLOPE(INDIRECT("B4:B"&G5), INDIRECT("A4:A"&G5)),3)')
worksheet.write_formula('E6','=ROUND(INDIRECT("B"&G6)/10000,3)')
worksheet.write_formula('E7','=ROUND(INDIRECT("B"&G7)/10000,3)')
chart1 = workbook.add_chart({'type': 'scatter', 'subtype': 'straight'})
#Getting slope for the footer
expansion_slope = best_fit(temp_column[:temp_1000 - 3],expansion_column[:temp_1000 - 3])
foot = "THERMAL EXPANSION = {0} E-06".format(expansion_slope)
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$4:$A$4300',
'values': '=Sheet1!$B$4:$B$4300',
})
chart1.set_title ({'name': material,
'name_font': {
'name': 'Arial',
'size': 12,
}})
chart1.set_y_axis({'name': 'Expansion(ppm)'})
chart1.set_x_axis({'name': 'Temperature('+ deg +')','min': 0,'max': 1600,
'label_position': 'low'
,'major_gridlines': {
'visible': True}})
chart1.set_legend({'none': True})
chart1.set_style(1)
chartsheet.set_header('&L&D &R&F')
chartsheet.set_footer('&R'+ foot)
chartsheet.set_chart(chart1)
chartsheet.activate()
workbook.close()
print(material)
print(title)
class ExcelGrading:
def __init__(self, file_name, running_command):
self.workbook = Workbook(file_name + '.xlsx')
self.worksheet = self.workbook.add_worksheet()
self.running_command = running_command
self.set_grades_titles()
self.row = 1
def set_grades_titles(self):
title_style = self.workbook.add_format({
'bold': 1,
'font_color': 'black'
})
self.worksheet.write('A1', 'Student ID', title_style)
self.worksheet.write("B1", "Runtime", title_style)
self.worksheet.write("C1", "Number Of Wrong Answers", title_style)
self.worksheet.write("D1", "Number Of Delayed Answers", title_style)
self.worksheet.write("E1", "Final Grade", title_style)
def save_file(self):
self.workbook.close()
def add_grade(self, file_dir_path: str, student_id: str):
process = subprocess.Popen(self.running_command + " " + file_dir_path,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
stdout, stderr = process.communicate()
try:
stderr = stderr.decode()
if ErrorKey.ProgramNotFound in stderr:
grade = mistakes = rt = too_long = 0
else:
_, grade = stderr.split('Report: ', maxsplit=1)
grade, _ = grade.split(
'\n************************************\r', maxsplit=1)
grade, rt = grade[7:].replace(' ', '').split("runtime:")
grade = float(grade)
mistakes = stderr.count(ErrorKey.Mistakes)
too_long = stderr.count(ErrorKey.TooLong)
except (UnicodeDecodeError, ValueError, TypeError):
grade = mistakes = rt = too_long = 0
high_style = self.workbook.add_format({
'bold': 1,
'font_color': 'green'
})
low_style = self.workbook.add_format({'bold': 1, 'font_color': 'red'})
self.worksheet.write(self.row, 0, student_id)
self.worksheet.write(self.row, 1, rt)
self.worksheet.write(self.row, 2, mistakes)
self.worksheet.write(self.row, 3, too_long)
if 50 < grade < 90:
self.worksheet.write_number(self.row, 4, grade)
else:
self.worksheet.write_number(
self.row, 4, grade, high_style if grade > 90 else low_style)
self.row += 1
def set_chart(self):
# Create a new chart object.
chart = self.workbook.add_chart({'type': 'line'})
# Add a series to the chart.
chart.add_series({
'values': '=Sheet1!$E$2:$E$' + str(self.row),
'trendline': {
'type': 'linear'
}
})
# Insert the chart into the worksheet.
self.worksheet.insert_chart('G1', chart)
