line.append(array) line.append(length) line.append(lnRatio) line.append(RVlnRatio) line.append(result) line.append(retrace) line.append(added_steps) return line if (Strategy_Predictor == True): indir = "/Users/alexanderboone/Desktop/test/data/" outdir = "/Users/alexanderboone/Desktop/test/Steps_Counter_OutPuts/" wb = xlsxwriter.Workbook( "/Users/alexanderboone/Desktop/test/Steps_Counter_OutPuts/StrategyPredictor_test.xlsx" ) sheet = wb.add_worksheet('Sheet') header = [ "Participant", "TrialNo", "TrialID", "Total Steps", "shortcut steps", "learned steps", "reversal steps", "array", "LENGTH", "lnRatio", "RVlnRatio", "result", "retrace", "extra box count" ] for i in range(len(header)): sheet.write(0, i, header[i]) steps = 0 scCounter = 0 lnCounter = 0 RVlnCounter = 0
from selenium import webdriver from selenium.webdriver.common.keys import Keys import re import time import csv import xlsxwriter browser = webdriver.Chrome( 'C:/chromedriver_win32/chromedriver') #path to chrome webdriver workbook = xlsxwriter.Workbook('France.xlsx') #filename worksheet = workbook.add_worksheet('France') #worksheet name worksheet.write(0, 0, 'User Names') worksheet.write(0, 1, 'Bio') roww = 1 with open('Book1.csv', 'r') as csvfile: reader = csv.reader(csvfile, skipinitialspace=True) for row in reader: print(row[0].replace('', '')) lst = '' try: browser.get('https://www.instagram.com/' + row[0].replace('', '')) time.sleep(5) details = browser.find_element_by_xpath( '/html/body/div[1]/section/main/div/header/section/div[2]' ).text #lst = re.findall('\S+@\S+', details)#if you just want to scrap emails from bio except: pass if (len(lst) != 0):
# URL #url_box = soup.find('a', href=True) #url_box = soup.find('meta', attrs={'itemprop': 'url'}) #url_box = soup.find('meta', attrs={'property': 'vr:canonical'}) # PArtial solution (I imported urllib.request vs answer given in only urllib): # ^^https://stackoverflow.com/questions/1752317/how-to-get-the-true-url-of-a-file-on-the-web-python url = urllib.request.urlopen(pg).geturl() #url = url_box.text.strip() print(url) data.append((title, author, time, url)) try: workbook = xlsxwriter.Workbook( 'C:/Users/noona/Downloads/python/projects/aToBeCompleted/scrapeNewsArticles/indexXLFrom.xlsx' ) worksheet = workbook.add_worksheet() # support: http://xlsxwriter.readthedocs.io/tutorial03.html worksheet.set_column('A:A', 67) worksheet.set_column('B:B', 66) worksheet.set_column('C:C', 33) worksheet.set_column('D:D', 81) bold = workbook.add_format({'bold': True}) worksheet.write('A1', 'Title', bold) worksheet.write('B1', 'Author', bold) worksheet.write('C1', 'Time Updated', bold) worksheet.write('D1', 'URL/Link', bold)
def __init__(self, file): self.workbook = xlsxwriter.Workbook(file) self.worksheet = self.workbook.add_worksheet() self.colors = self.create_colors() self.used_colors = set() self.row = 0
__author__ = 'R.Azh' from TestPython.test_excel.data import ipsla_data import xlsxwriter color_mapping = { 'good': "rgb(117, 209, 117)", 'average': "rgb(255, 198, 103)", 'extreme': "rgb(139, 0, 0)", 'worst_case': "rgb(242, 108, 104)", 'out_of_scale': "rgb(107, 38, 7)", 'unavailable': "rgb(183, 183, 183)", } data = ipsla_data workbook = xlsxwriter.Workbook('ip_sla.xlsx') worksheet = workbook.add_worksheet('ip_sla') qoe_ranges = {} for key, value in color_mapping.items(): color = literal_eval(value.lstrip('rgb')) color = "#{0:02x}{1:02x}{2:02x}".format(*color) qoe_ranges[key] = { 'value': key.replace('_', ' ').title(), 'format': workbook.add_format({'bg_color': color}) } # print(qoe_ranges) for key in qoe_ranges: worksheet.conditional_format( 1, 1, len(data) + 1, 15, { 'type': 'text',
page = page * 20 except: page = 5 * 20 # 转成字符串 # %y 两位数的年份表示(00 - 99) # %Y 四位数的年份表示(000 - 9999) # %m 月份(01 - 12) # %d 月内中的一天(0 - 31) # %H 24小时制小时数(0 - 23) # %I 12小时制小时数(01 - 12) # %M 分钟数(00 = 59) # %S 秒(00 - 59) today = time.strftime('%Y%m%d%H%M', time.localtime()) # 创建一个Excel文件 workbook = xlsxwriter.Workbook('../excel/' + today + '.xlsx') # 创建一个工作表 worksheet = workbook.add_worksheet("keyword") # 总表 itemlist = [] # 当查询销售上升销售热门的时候用这个 if type == 0 or type == 1: # 制定查询规则 rank = "sale" if type == 0: type = "up" else: type = "hot" # 写入excel的行数
# -*- coding utf-8 -*- # import xlsxwriter # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook('demo.xlsx') worksheet = workbook.add_worksheet() # Widen the first column to make the text clearer. worksheet.set_column('A:A', 20) # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': True}) # Write some simple text. worksheet.write('A1', 'Hello') # Text with formatting. worksheet.write('A2', 'World', bold) # Write some numbers, with row/column notation. worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) # Insert an image. worksheet.insert_image('B5', 'logo.png') workbook.close()
from selenium.webdriver.common.by import By from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from selenium.webdriver.chrome.options import Options from bs4 import BeautifulSoup # 엑셀 처리 임포트 import xlsxwriter # 이미지 바이트 처리 from io import BytesIO import urllib.request as req chrome_options = Options() chrome_options.add_argument("--headless") # 브라우저가 실행 안 되는 옵션 # 엑셀 처리 선언 workbook = xlsxwriter.Workbook( "C:/Users/young/OneDrive/crawling/python_crawl/crawling_result.xlsx") # 워크 시트 worksheet = workbook.add_worksheet() # webdriver 설정(Chrome, Firefox 등) - Headless 모드 # browser = webdriver.Chrome('./webdriver/chrome/chromedriver.exe', options=chrome_options) # webdriver 설정(Chrome, Firefox 등) - 일반 모드 browser = webdriver.Chrome('./webdriver/chrome/chromedriver.exe') # 크롬 브라우저 내부 대기 browser.implicitly_wait(5) # 브라우저 사이즈 browser.set_window_size(1200, 800) # maximize_window(), minimize_window()
import socket import pandas as pd import xlsxwriter #Constants excel_path = 'example.xlsx' column_name = 'Domain' # Instance of result workbook = xlsxwriter.Workbook('result.xlsx') worksheet = workbook.add_worksheet() # Putting the headers worksheet.write("A1", "DOMAIN") worksheet.write("B1", "IP") # Reading data from excel data = pd.read_excel (excel_path) rows = pd.DataFrame(data, columns= [column_name]) # Gambi :( row = 1 for domain in rows['Domain']: result = '' try: result = socket.gethostbyname(domain) except Exception as exc: result = exc finally: row = row + 1
j = j-1 list[j+1] = data if __name__ == "__main__": noOfSts = int(input('Enter the number of students to enroll: ')) data_list = [] for i in range(0, noOfSts): user = Student() data_list.append(user.dataList()) print() insertionSort(data_list, 1) insertionSort(data_list, 0) # printing data in an excel file import xlsxwriter as xw outWorkbook = xw.Workbook("out.xlsx") outSheet = outWorkbook.add_worksheet() outSheet.write("B1", "Student's names") outSheet.write("A1", "Student's id") outSheet.write("C1", "Department") outSheet.write("D1", "E-mail id") outSheet.write("E1", "Password") id = 3001 for i in range(len(data_list)): outSheet.write(i+1, 0, id) id = id+1 for j in range(len(data_list[i])): outSheet.write(i+1, j+1, data_list[i][j]) outWorkbook.close()
import requests import feedparser import datetime from datetime import timedelta, date import string from pytz import timezone from pytz import all_timezones import xlsxwriter sources = ["https://www.cnbc.com", "https://www.bloomberg.com", "https://www.reuters.com", "https://www.cnn.com", "https://money.cnn.com", "https://www.forbes.com", "https://www.marketwatch.com"] req_str = 'https://news.google.com/rss/search' workbook = xlsxwriter.Workbook('aapl_test.xlsx') worksheet = workbook.add_worksheet() wb_rows = [] def daterange(start_date, end_date): for n in range(int ((end_date - start_date).days)): yield start_date + timedelta(n) start_date = date(2010, 2, 1) end_date = date(2020, 2, 8) cnt = 0 for req_date in daterange(start_date, end_date): print(str(cnt)) cnt += 1 # req_date = datetime.datetime(2020, 1, 29)
####################################################################### # # Example of how to hide a worksheet with XlsxWriter. # # Copyright 2013, John McNamara, [email protected] # import xlsxwriter workbook = xlsxwriter.Workbook('hide_sheet.xlsx') worksheet1 = workbook.add_worksheet() worksheet2 = workbook.add_worksheet() worksheet3 = workbook.add_worksheet() worksheet1.set_column('A:A', 30) worksheet2.set_column('A:A', 30) worksheet3.set_column('A:A', 30) # Hide Sheet2. It won't be visible until it is unhidden in Excel. worksheet2.hide() worksheet1.write('A1', 'Sheet2 is hidden') worksheet2.write('A1', "Now it's my turn to find you!") worksheet3.write('A1', 'Sheet2 is hidden') workbook.close()
############################################################################### # _*_ coding: utf-8 # # A simple Unicode spreadsheet in Python 2 using the XlsxWriter Python module. # # Copyright 2013-2018, John McNamara, [email protected] # # To write Unicode text in UTF-8 to a xlsxwriter file in Python 2: # # 1. Encode the file as UTF-8. # 2. Include the "coding" directive at the start of the file. # 3. Use u'' to indicate a Unicode string. import xlsxwriter workbook = xlsxwriter.Workbook('unicode_python2.xlsx') worksheet = workbook.add_worksheet() worksheet.write('B3', u'Это фраза на русском!') workbook.close()
def WriteToExcel(): output = StringIO.StringIO() workbook = xlsxwriter.Workbook(output) # Here we will adding the code to add data worksheet_s = workbook.add_worksheet("Summary") title = workbook.add_format({ 'bold': True, 'font_size': 14, 'align': 'center', 'valign': 'vcenter' }) header = workbook.add_format({ 'bg_color': '#F7F7F7', 'color': 'black', 'align': 'center', 'valign': 'vcenter', 'border': 1 }) cell = workbook.add_format({ 'bg_color': '#FFFFFF', 'color': 'black', 'align': 'justify', 'valign': 'middle', 'border': 1 }) cell_center = workbook.add_format({ 'bg_color': '#FFFFFF', 'color': 'black', 'align': 'center', 'valign': 'middle', 'border': 1 }) title_text = u"Reporte de inscritos al Primer Torneo Nacional de FT 2017 - Sutamarchán" worksheet_s.merge_range('A2:D2', title_text, title) worksheet_s.write(4, 0, ugettext("#"), header) worksheet_s.write(4, 1, ugettext("Documento"), header) worksheet_s.write(4, 2, ugettext("Nombre completo"), header) worksheet_s.write(4, 3, ugettext("Club"), header) worksheet_s.set_column('B:B', 13) worksheet_s.set_column('C:C', 40) worksheet_s.set_column('D:D', 30) worksheet_s.set_row(4, 28) # the rest of the headers from the HTML file partners = User.objects.filter(suscribed=True) for idx, partner in enumerate(partners): row = 5 + idx worksheet_s.write_number(row, 0, idx + 1, cell_center) worksheet_s.write_string(row, 1, str(partner.doc_id), cell) worksheet_s.write_string(row, 2, partner.get_full_name(), cell) worksheet_s.write(row, 3, partner.club.name, cell_center) # the rest of the data workbook.close() xlsx_data = output.getvalue() # xlsx_data contains the Excel file return xlsx_data
tempnum = int(results + 2) link = "https://api.themoviedb.org/3/search/movie?api_key=1dcf69b9b95240032c80e5d374ca2bee&language=en-US&query=" + name movie_name = [] movie_overview = [] movie_release = [] # sending get request and saving the response as response object r = requests.get(url=link) data = r.json() results = data['total_results'] for i in range(0, tempnum): movie_name.append(data['results'][i]['original_title']) movie_release.append(data['results'][i]['release_date']) movie_overview.append(data['results'][i]['overview']) workbook = xlsxwriter.Workbook('movie.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A1', 'Movie Name') worksheet.write('B1', 'Movie Release Date') worksheet.write('C1', 'Movie Description') for i in range(2, tempnum): wb = 'A' + str(i) wbb = 'B' + str(i) wbbb = 'C' + str(i) worksheet.write(wb, movie_name[i - 2]) worksheet.write(wbb, movie_release[i - 2]) worksheet.write(wbbb, movie_overview[i - 2]) # Finally, close the Excel file # via the close() method. workbook.close()
import xlsxwriter # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook('Expenses01.xlsx') worksheet = workbook.add_worksheet() # Some data we want to write to the worksheet. a = 0 b = 'as' expenses = (['flag', "ans"], [a, b]) # Start from the first cell. Rows and columns are zero indexed. row = 0 col = 0 # Iterate over the data and write it out row by row. for item, cost in (expenses): worksheet.write(row, col, item) worksheet.write(row, col + 1, cost) row += 1 # Write a total using a formula. #worksheet.write(row, 0, 'Total') #worksheet.write(row, 1, '=SUM(B1:B4)') workbook.close()
#!/usr/bin/env python3 # vim: set fileencoding=utf-8 """Vytvoření sešitu s delší tabulkou, nastavení barvy pozadí.""" import xlsxwriter # vytvoření objektu reprezentujícího celý sešit with xlsxwriter.Workbook("example19.xlsx") as workbook: # vložení nového listu do sešitu worksheet = workbook.add_worksheet() # nastavení šířky sloupců worksheet.set_column("A:A", 20) # barvy buněk colors = ( "black", "blue", "brown", "cyan", "gray", "green", "lime", "magenta", "navy", "orange", "pink", "purple", "red", "silver", "white",
####################################################################### # # Example of how to set Excel worksheet tab colours using Python # and the XlsxWriter module.. # # Copyright 2013, John McNamara, [email protected] # import xlsxwriter workbook = xlsxwriter.Workbook('tab_colors.xlsx') # Set up some worksheets. worksheet1 = workbook.add_worksheet() worksheet2 = workbook.add_worksheet() worksheet3 = workbook.add_worksheet() worksheet4 = workbook.add_worksheet() # Set tab colours worksheet1.set_tab_color('red') worksheet2.set_tab_color('green') worksheet3.set_tab_color('#FF9900') # Orange # worksheet4 will have the default colour. workbook.close()
import string name = 'email' df = pd.read_excel(name + '.xlsx') username = df['Username'].values.tolist() Email = df['Email'].values.tolist() Password = df['Password'].values.tolist() count = 0 res = [] for item in range(len(username)): url = (username[count] + '.page.tl') count = count + 1 res.append(url) letters = string.ascii_lowercase rand = (''.join(random.choice(letters) for i in range(5))) workbook = xlsxwriter.Workbook(name + rand + 'Output.xlsx') sheet = workbook.add_worksheet() sheet.write("A1", "Email") sheet.write("B1", "Username") sheet.write("C1", "URL") for item in range(len(Email)): sheet.write(item + 1, 0, Email[item]) sheet.write(item + 1, 1, username[item]) sheet.write(item + 1, 2, res[item]) workbook.close()
def main(): veris_logger.updateLogger(cfg) logging.info('Beginning main loop.') # Create spreadsheets workbook = xlsxwriter.Workbook(args.output) list_of_headings = workbook.add_worksheet("List of Headings") enumerations = workbook.add_worksheet("Enumerations") example = workbook.add_worksheet("Example") # Pull in objects and type from verisc.json Maybe add format. Add some ordering. Add some hard-coded notes. Add hard-coded order. row = 0 keynames, keyenums = recurse_schema(schema, "", "") sorted_keynames = [] for i in range(len(ORDER)): sorted_keynames = sorted_keynames + sorted( [k for k in keynames if k[0].startswith(ORDER[i])]) sorted_keynames = sorted_keynames + sorted( list(set(keynames).difference(sorted_keynames))) list_of_headings.write(row, 0, "keyname") list_of_headings.write(row, 1, "format") list_of_headings.write(row, 2, "notes") row += 1 for keyname, keytype in sorted_keynames: list_of_headings.write(row, 0, keyname) list_of_headings.write(row, 1, FORMAT_OVERRIDE.get(keyname, FORMAT[keytype])) list_of_headings.write(row, 2, NOTES.get(keyname, "")) row += 1 # Object, enumeration, (probably label too) if add_labels: # parse enums and labels from labels file labels_dict = recurse_labels(labels, "") sorted_labels_list = [] for i in range(len(ORDER)): # sorted_labels_list = sorted_labels_list + sorted([{k: labels_dict[k]} for k in list(labels_dict.keys()) if k.startswith(ORDER[i])]) sorted_labels_list = sorted_labels_list + sorted( [{ k: labels_dict[k] } for k in list(labels_dict.keys()) if k.startswith(ORDER[i])], key=lambda d: sorted(d.items())) missing_keys = set(labels_dict.keys()).difference( [list(k.keys())[0] for k in sorted_labels_list]) for k in missing_keys: sorted_labels_list.append({k: labels_dict[k]}) # parse enums from schema row = 0 enumerations.write( row, 0, "Enumerations for {0}".format(schema['description'])) row += 1 enumerations.write(row, 0, "keyname") enumerations.write(row, 1, "enumeration") enumerations.write(row, 2, "label") row += 1 for keyname in sorted_labels_list: k = list(keyname.keys())[0] enumerations.write(row, 0, k) for enum_label in keyname[k]: enumerations.write(row, 1, enum_label[0]) enumerations.write(row, 2, enum_label[1]) row += 1 else: # parse enums from schema sorted_schema_list = [] for i in range(len(ORDER)): sorted_schema_list = sorted_schema_list + sorted([{ k: keyenums[k] } for k in list(keyenums.keys()) if k.startswith(ORDER[i])]) missing_keys = set(keyenums.keys()).difference( [list(k.keys())[0] for k in sorted_schema_list]) for k in missing_keys: sorted_schema_list.append({k: keyenums[k]}) row = 0 enumerations.write( row, 0, "Enumerations for {0}".format(schema['description'])) row += 1 enumerations.write(row, 0, "keyname") enumerations.write(row, 1, "enumeration") row += 1 for keyname in sorted_schema_list: k = list(keyname.keys())[0] enumerations.write(row, 0, k) for enum in keyname[k]: enumerations.write(row, 1, enum) row += 1 # Build examples from Test VERIS records. if args.test_examples is not None: try: if not os.path.isdir(args.test_examples): raise ValueError("Directory does not exist.") testfiles = glob.glob(args.test_examples.rstrip("/") + "/*.json") if len(testfiles) <= 0: raise ValueError("No test files found in directory.") if args.num_examples > 0 and len(testfiles) > args.num_examples: testfiles = random.sample(testfiles, args.num_examples) #logging.debug(testfiles) # read in and flatten files records = [] for filename in testfiles: with open(filename, 'r') as filehandle: j = json.load(filehandle) #logging.debug(j) record = recurse_veris(j, "") #logging.debug(record) records.append(record) # get columns columns = set(DEFAULT_COLUMNS) for record in records: #logging.debug(record.keys()) columns = columns.union(list(record.keys())) #logging.debug(columns) # order columns sorted_columns = [] for i in range(len(ORDER)): sorted_columns = sorted_columns + sorted( [k for k in columns if k.startswith(ORDER[i])]) sorted_columns = sorted_columns + sorted( list(set(columns).difference(sorted_columns))) #logging.debug(sorted_columns) # write header row = 0 for i in range(len(sorted_columns)): example.write(row, i, sorted_columns[i]) row += 1 #logging.debug(records) for record in records: for k, v in record.items(): example.write(row, sorted_columns.index(k), v) row += 1 # add a 'repeat' for the last 2 example.write(row - 2, 0, 25) # 25 is just hardcoded number for example example.write(row - 1, 0, 5) # 5 is just hardcoded number for example except: #logging.info("No test files found.") raise workbook.close() logging.info('Ending main loop.')
from docx import Document import xlsxwriter import sys path = sys.argv[1] if path.endswith(".docx"): suff_ind = -5 elif path.endswith(".doc"): suff_ind = -4 else: suff_ind = -4 path_xls = path[:suff_ind] + ".xlsx" print("准备将word中的表格写入文件" + path_xls + "中") workbook = xlsxwriter.Workbook(path_xls) doc = Document(path) for table in doc.tables: worksheet1 = workbook.add_worksheet() for r_ind, row in enumerate(table.rows): row_text = [c.text for c in row.cells] print(row_text) # TODO: print row in Excel with xlsxwriter or openpyxl # For xlsxwriter: use worksheet.write_row() # See https://xlsxwriter.readthedocs.io/worksheet.html for details for c_ind, cell in enumerate(row_text): worksheet1.write(r_ind, c_ind, cell) # For openpyxl: see doc in https://openpyxl.readthedocs.io/en/stable/tutorial.htm
file_sheet_names = fh.sheet_names() sheet_names_lst.append(file_sheet_names) for shnum in range(x): print("正在读取文件:" + str(fl) + "的第" + str(shnum) + "个sheet表的内容...") sheet_value = getFilect(fl, shnum) datavalue[shnum] += sheet_value # rvalue=getFilect(fl,shnum) #定义最终合并后生成的新文件 sheet_names = sorted(sheet_names_lst, key=lambda x: len(x), reverse=True)[0] # 得到最终的sheet名字 print(sheet_names) result_path = os.path.join(os.getcwd(), "result") merge_file = "merge_file.xlsx" # endfile='F:/test/excel3.xlsx' #创建一个sheet工作对象 wb = xlsxwriter.Workbook(merge_file) ws_handles = [wb.add_worksheet(sheet_names[shnum]) for shnum in range(x)] for shnum in range(x): sheet_data = list(set(datavalue[shnum])) for a in range(len(sheet_data)): for b in range(len(sheet_data[a])): c = sheet_data[a][b] ws_handles[shnum].write(a, b, c) wb.close() print("文件合并完成")
conversion_rate = "-" return (conversions, conversion_rate) def getGoalValues(exp_id, var_id, goal_id, baseline_variation_id): conversions, conversion_rate = getGoalConversions(exp_id, var_id, goal_id) if var_id == baseline_variation_id: return (conversions, conversion_rate, "-", "-") else: conversions, conversion_rate = getGoalConversions(exp_id, var_id, goal_id) b_conversions, b_conversion_rate = getGoalConversions(exp_id, baseline_variation_id, goal_id) improvement = "-" if (b_conversion_rate == 0 or conversion_rate == "-" or b_conversion_rate == "-") else (float(conversion_rate) / float(b_conversion_rate)) - 1 CTB = CTBNormal(exp_id, var_id, goal_id) return (conversions, conversion_rate, improvement, CTB) workbook = xlsxwriter.Workbook(name); worksheet = workbook.add_worksheet() formats = { "percent" : {'num_format': '0.0%'}, "decimal" : {'num_format': '0.00'}, 'fill_green': {'bg_color': '#E7EFD8'}, "fill_red" : {"bg_color" : '#F2DCDB'}, "center": {'align': 'center'}, "bottom": {'bottom':1}, "font": {"font_name": "Gill Sans Light"}, "font_bold": {"font_name": "Gill Sans"}, "wrap": {"text_wrap": "True"}, "fill_grey": {'bg_color': '#D9D9D9'} }
def export_to_excel(): export_file = export_name + ' ' + date + '.xlsx' ref_list = assemble_palets() nor = round(1000 * 5 / (8 * 60), 1) for i in range(len(ref_list)): if type(ref_list[i][2]) == int: ref_list[i][2] = ref_list[i][2] * nor work_time = 0 laze_time = 0 for k in range(len(ref_list)): if ref_list[k][2] == 1 and ref_list[k][2] > 1: work_time += round(min_step / 60, 1) else: laze_time += round(min_step / 60, 1) cheet_values_a = '=Данные!$A$1:$A$' + str(len(ref_list)) cheet_values_b = '=Данные!$B$1:$B$' + str(len(ref_list)) cheet_values_c = '=Данные!$C$1:$C$' + str(len(ref_list)) col_a = [ref_list[i][0] for i in range(len(ref_list))] col_b = [ref_list[i][1] for i in range(len(ref_list))] col_c = [ref_list[i][2] for i in range(len(ref_list))] while True: try: #настройки листа workbook = xlsxwriter.Workbook(export_file) worksheet1 = workbook.add_worksheet('График') #Добавление листа worksheet2 = workbook.add_worksheet('Данные') #Добавление листа worksheet1.set_landscape() #Альбомная ориентация worksheet1.set_paper(9) #А4 worksheet1.center_horizontally() #Центровка по горизонтали worksheet1.center_vertically() #Центровка по вертикали worksheet1.set_margins( 0.05, 0.05, 0.05, 0.05) #поля листа для распечатанной страницы worksheet1.set_header( 'График') #заголовок напечатанной страницы и параметры worksheet1.hide_gridlines(1) #не печатать сетку worksheet1.print_area('A1:S35') #Cells A1 to H20. worksheet1.set_print_scale( 150) #масштабный коэффициент для распечатанной страницы ## worksheet.write(row, 1, '=SUM(B1:B4)') merge_format = workbook.add_format({ 'align': 'center', 'bold': True, 'border': 1, 'size': 16, 'align': 'center', 'valign': 'vcenter', }) worksheet1.merge_range( 'B3:R3', 'График производительности линии "Поятос" по интервалам времени', merge_format) worksheet1.write('B6', 'Норма формовок в смену') worksheet1.write('F6', norm_press) worksheet1.write('B7', 'Фактическое число формовок') worksheet1.write('F7', palets_total) worksheet1.write('B8', 'Норма формовок за интервал') worksheet1.write('F8', '=F6*5/(8*60)') worksheet1.write('B9', 'Расч. кол. формовок за интервал') worksheet1.write('F9', '=F7*5/(8*60)') worksheet1.write('B10', 'Нормативная длительность форм., с') worksheet1.write('F10', '=8*3600/F6') worksheet1.write('B11', 'Расчетная длительность форм., с') worksheet1.write('F11', '=8*3600/F7') worksheet1.write('M6', 'Время в работе (прессование)') worksheet1.write('Q6', work_time) worksheet1.write('M7', 'Время без формовок (простой)') worksheet1.write('Q7', laze_time) worksheet1.write('M8', 'Неучтенное время') worksheet1.write('Q8', '=Q9-Q7-Q6') worksheet1.write('M9', 'Длительность смены') worksheet1.write('Q9', work_duration) worksheet1.write('M10', 'Производит. без уч. простоев') worksheet1.write('Q9', '') worksheet1.write('M13', 'Разраб.: А.П. Бежанов') worksheet1.write('M14', 'Проверил:') worksheet2.write_column('A1', col_a) worksheet2.write_column('B1', col_b) worksheet2.write_column('C1', col_c) norm_line = workbook.add_chart({'type': 'area'}) norm_line.add_series({ 'name': 'диапазон \nбез измерений', #Название окна графика 'categories': cheet_values_a, #значения оси Х 'values': cheet_values_c, #значения оси У 'line': { 'color': 'red', 'width': 2, }, 'trendline': { 'name': 'Норматив', #Название окна графика 'type': 'linear', 'display_equation': True } }) graph_line = workbook.add_chart({'type': 'line'}) #тип заполненная линия graph_line.add_series({ 'name': 'Количество\n формовок', #Название окна графика 'categories': cheet_values_a, #значения оси Х 'values': cheet_values_b, #значения оси У 'line': { 'display_equation': True } }) graph_line.set_style(33) graph_line.combine(norm_line) graph_line.set_size({ 'width': 1200, 'height': 400 }) #размер графика graph_line.set_chartarea({'border': { 'none': True }}) #отключение границы окна графика ## graph_line.set_legend({'none': True}) #отключает легенду worksheet1.insert_chart('A15', graph_line, { 'x_offset': 5, 'y_offset': 5 }) #позиция и смещение окна графика workbook.close() print(export_file, 'успешно сгенерирован.') break except PermissionError: #если не закрыт файл, то просим закрыть err_msg = 'Не могу сохранить данные, закройте файл: "' + export_file + '" и нажмите ENTER' input(err_msg)
'sum': 10, 'pass': 5, 'testDate': '2017-06-05 15:26:49', 'fail': 5, 'appVersion': '17051515', 'appSize': '14M', 'appName': "'简书'" } info = [{ "id": 1, "title": "第一次打开", "caseName": "testf01", "result": "通过", "phoneName": "三星" }, { "id": 1, "title": "第一次打开", "caseName": "testf01", "result": "通过", "img": "d:\\1.PNG", "phoneName": "华为" }] workbook = xlsxwriter.Workbook('Report.xlsx') worksheet = workbook.add_worksheet("测试总况") worksheet2 = workbook.add_worksheet("测试详情") bc = OperateReport(wd=workbook) bc.init(worksheet, sum) bc.detail(worksheet2, info) bc.close() #
minmax_wb = openpyxl.load_workbook(sys.argv[2], read_only=True) minmax_ws = minmax_wb['Limits'] # Open Area XML file and look for root # Delete namespace from root tag before launching this script print("Opening axml file") tree = ET.parse(sys.argv[1]) root = tree.getroot() namespaces = {} # This dictionary is used to convert Rockwell Datatypes to NEOPLM Datatypes typedict = {'Real':'Double', 'Integer':'Integer', 'Boolean':'Boolean', 'String':'String'} # Create a workbook and add a worksheet. print("Creating workbook") workbook = xlsxwriter.Workbook(sys.argv[1].split('.')[0] + '_LocalPhaseDefinition_INTERMEDIATE.xlsx') worksheet = workbook.add_worksheet('Phase Definitions') worksheet1 = workbook.add_worksheet('Parameters') worksheet2 = workbook.add_worksheet('FDS_Scope') worksheet3 = workbook.add_worksheet('Min_Max') print("Copying bounds and EU to Min_Max worksheet") n = m = 0 for row in minmax_ws.rows: m = 0 for cell in row: worksheet3.write(n,m,cell.value) m += 1 n += 1 # create formats
else: dicc[display] = 1 #SORTING sorted_M = sorted(dicc.items(), key=lambda x: x[1], reverse=True) sorted_dicc = {} x_values = [] y_values = [] for s in sorted_M: sorted_dicc[s[0]] = s[1] x_values.append(s[0]) y_values.append(s[1]) #Excel part workbook = xlsxwriter.Workbook('FHIR_Global_Results.xlsx') worksheet = workbook.add_worksheet("Medication") row = 4 for sd in sorted_dicc: worksheet.write(row, 1, sd) worksheet.write(row, 2, sorted_dicc[sd]) row = row + 1 dicc_len = len(sorted_dicc) final_pos = dicc_len + 4 #Drawing the chart chart = workbook.add_chart({'type': 'pie'}) chart.add_series({ 'categories': '=Medication!B5:B' + str(final_pos),
debug = True print("Debug mode turned on.") pdf_file = args.pdf_file pdf_file_name = os.path.splitext(pdf_file)[0] key_dir = args.key_dir #set environment os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = key_dir #File conversion create_main_folders(pdf_file, im_folder, output_folder, debug) convert_pdf(pdf_file, im_folder, resolution, debug) image_file_ls = os.listdir(os.path.join(pdf_file_name, im_folder)) #Pass image into Google Vision and writes to Excel workbook = xlsxwriter.Workbook(os.path.join(pdf_file_name, 'results.xlsx')) for im_file in image_file_ls: im_file_name = os.path.splitext(im_file)[0] worksheet = workbook.add_worksheet(im_file_name) like_isbn_ls = recognise_text(pdf_file_name, im_file, im_file_name, im_folder, output_folder, isbn_check, debug) verified_isbn_ls = verify_isbn(like_isbn_ls) write_to_excel(worksheet, verified_isbn_ls) workbook.close() print(datetime.datetime.now())
def main(): # __________________________________________________________________________ # command-line options, arguments try: parser = argparse.ArgumentParser( description= "psql2xls - utility for saving Postgres SQL queries results to .xlsx file" ) parser.add_argument('-c', '--config', action='store', default="config.ini", metavar="<CONFIG_FILE>", help="configuration file path") parser.add_argument('-o', '--output', action='store', default=None, metavar="<OUTPUT_FILE>", help="output file path") parser.add_argument('-f', '--overwrite', action='store_true', default=False, help="allow overwrite output file") args = parser.parse_args() except SystemExit: return False # __________________________________________________________________________ # read configuration file try: config_ini = configparser.ConfigParser() config_ini.read(os.path.abspath(args.config)) except Exception as err: print("[!!] Exception :: {}\n{}".format( err, "".join(traceback.format_exc())), flush=True) return False # __________________________________________________________________________ if not config_ini.sections() or 'default' not in config_ini.sections(): print("[EE] Missing configuration section :: default", flush=True) return False if not [x for x in config_ini.sections() if x != 'default']: print("[..] Nothing to do", flush=True) return False # __________________________________________________________________________ # generate default config config_default = { 'output': None, 'overwrite': False, 'font_name': 'Liberation Sans', 'font_size': 10, 'host': "localhost", 'port': 5432, 'base': "postgres", 'user': "******", 'pass': None, 'query': None } for x in config_default: try: config_default[x] = config_ini['default'][x] except KeyError: pass except Exception as err: print("[!!] Exception :: {}\n{}".format( err, "".join(traceback.format_exc())), flush=True) return False # inspect: output if args.output: config_default['output'] = args.output if not config_default['output']: print("[EE] Invalid option value :: output", flush=True) return False # inspect: overwrite if args.overwrite: config_default['overwrite'] = args.overwrite if isinstance(config_default['overwrite'], str): if config_default['overwrite'].lower() in ('true', 'yes', 'on'): config_default['overwrite'] = True else: config_default['overwrite'] = False # inspect: font_name if not config_default['font_name']: print("[EE] Invalid option value :: font_name", flush=True) return False # inspect: font_size if not config_default['font_size']: print("[EE] Invalid option value :: font_size", flush=True) return False # __________________________________________________________________________ # check permission if not fs_check_access_file(config_default['output'], config_default['overwrite']): return False # __________________________________________________________________________ # make workbook workbook = xlsxwriter.Workbook(config_default['output']) workbook_format_global = workbook.formats[0] workbook_format_global.set_font_name(config_default['font_name']) workbook_format_global.set_font_size(config_default['font_size']) # special cell formats cell_format_header = workbook.add_format({ 'bold': True, 'font_name': config_default['font_name'], 'font_size': config_default['font_size'] }) cell_format_dt = workbook.add_format({ 'bold': False, 'font_name': config_default['font_name'], 'font_size': config_default['font_size'], 'num_format': _DT_FORMAT }) cell_format_time = workbook.add_format({ 'bold': False, 'font_name': config_default['font_name'], 'font_size': config_default['font_size'], 'num_format': _TIME_FORMAT }) cell_format_date = workbook.add_format({ 'bold': False, 'font_name': config_default['font_name'], 'font_size': config_default['font_size'], 'num_format': _DATE_FORMAT }) # ================================================================================================================== # ================================================================================================================== # Start of the work cycle # ================================================================================================================== for page in [x for x in config_ini.sections() if x != 'default']: print("[..] Generated page :: {} ...".format(page)) re_simple_str = re.compile(r"^([\w\- ]+)$") if not re_simple_str.search(page): print("[EE] Page name is not simple string", flush=True) return False # __________________________________________________________________________ # generate page config config_page = config_default.copy() for x in config_page: try: config_page[x] = config_ini[page][x] except KeyError: pass except Exception as err: print("[!!] Exception :: {}\n{}".format( err, "".join(traceback.format_exc())), flush=True) return False # print(config_page) # TEST # ______________________________________________________________________ # inspect: query if 'query' not in config_page or not config_page['query']: print("[EE] Invalid option value :: query", flush=True) return False # ______________________________________________________________________ # database connection postgres_dsn = "{}{}{}{}{}".format( "host='{}' ".format(config_page['host']) if config_page['host'] else '', "port='{}' ".format(config_page['port']) if config_page['port'] else '', "user='******' ".format(config_page['user']) if config_page['user'] else '', "dbname='{}' ".format(config_page['base']) if config_page['base'] else '', "password='******'".format(config_page['pass']) if config_page['pass'] else '', ).strip() # print(postgres_dsn) # TEST db = pg_connect(postgres_dsn) if not db: return False # ______________________________________________________________________ # make worksheet worksheet = workbook.add_worksheet(page) row_num = 0 column_width = {} # ______________________________________________________________________ # execute queries, supported multiple queries for query in config_page['query'].split(";\n"): cursor = pg_query(db, query.strip()) if not cursor: return False for col_num, column in enumerate(cursor.description): # print(column) # <class 'psycopg2.extensions.Column'> # print("HEAD >", row_num, col_num, column.name) # TEST if column.name != "?column?": worksheet.write(row_num, col_num, column.name, cell_format_header) # NOTE: calculate the maximum length of a row in a column length = len(column.name) column_width.setdefault(col_num, 0) if length > column_width[col_num]: column_width[col_num] = length row_num += 1 for column in cursor.fetchall(): for col_num, value in enumerate(column): # print("DATA >", row_num, col_num, value) # TEST # print(type(value), value) #### TEST # special cell formats if isinstance(value, datetime.datetime): # WARNING: check <'datetime.datetime'> before <'datetime.date'> worksheet.write(row_num, col_num, value, cell_format_dt) length = len(_DT_FORMAT) elif isinstance(value, datetime.time): worksheet.write(row_num, col_num, value, cell_format_time) length = len(_TIME_FORMAT) elif isinstance(value, datetime.date): worksheet.write(row_num, col_num, value, cell_format_date) length = len(_DATE_FORMAT) else: worksheet.write(row_num, col_num, value) length = len(str(value)) # NOTE: calculate the maximum length of a row in a column column_width.setdefault(col_num, 0) if length > column_width[col_num]: column_width[col_num] = length # ______________________________________________________________ row_num += 1 # ______________________________________________________________________ # set the width of the column # NOTE: limit the maximum width # print(column_width) # TEST for x in column_width: length = column_width[x] if length < 80: length += 1 else: length = 80 # print(x, length) # TEST worksheet.set_column(x, x, length) # __________________________________________________________________________ # write file workbook.close() print("[OK] Workbook saved :: {}".format(config_default['output']), flush=True) # __________________________________________________________________________ return True
def submit(self, ent): self.ent = ent t0 = time.time() print('submit', t0) response = request.urlopen( "https://search.51job.com/list/080200,000000,0000,00,9,99," + request.quote(ent) + ",2,1.html?lang=c&stype=&postchannel=0000&workyear=99&cotype=99°reefrom=99&jobterm=99&companysize" "=99&providesalary=99&lonlat=0%2C0&radius=-1&ord_field=0&confirmdate=9&fromType=&dibiaoid=0&address=&lin" "e=&specialarea=00&from=&welfare=") res = response.read() response.close() soup = BeautifulSoup(res, 'html.parser', from_encoding='gbk') pages = soup.select_one(".dw_page .td") print(pages.get_text()[1:4]) m = int(pages.get_text()[1:4]) / 30 print(m) urls = [k.get('href') for k in soup.select('div.el p.t1 a')] pool = multiprocessing.Pool(processes=multiprocessing.cpu_count() * 2) for url in urls: pool.apply_async(self.page, args=( url, self.filename, ), callback=self.callback) # pool.close() # pool.join() pg = 2 limit = int(pages.get_text()[1:4]) # limit = 100 for x in range(pg, limit, 1): pool.apply_async(self.ppage, args=( x, x + 1, self.filename, ), callback=self.callback) pool.close() pool.join() # f.close() t1 = time.time() print('submit==================', t1 - t0) # print(self.key_num) test_need = xlsxwriter.Workbook('result_testskill_1.xlsx') worksheet = test_need.add_worksheet('skill_needs') headings = ['skills', 'count'] worksheet.write_row('A1', headings) row = 1 col = 0 for key in self.key_num: if len(key) <= 20: worksheet.write(row, col, key) worksheet.write(row, col + 1, self.key_num[key]) row += 1 test_need.close() print("success", time.time() - t1)