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)