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)
