# 若上午或下午的标志位不是出勤，那么标记异常
if AM != "出勤" or PM != "出勤":
if AM != "出勤":
per_row[5] = "异常"
per_row[6] += AM
if PM != "出勤":
per_row[5] = "异常"
per_row[6] += PM
# print(per_row[3], AM, PM, start_time, end_time, min_time, max_time)
#############################################################################
# 输出已经过检验的day_list列表至EXCLE xlsx文件中
#############################################################################
# 创建考勤报表工作簿
wb_report = openpyxl.Workbook()
ws_report = wb_report.active
# 设置工作表格式
# 写入表头
header = ["部门", "员工编号", "姓名"] + date_list
ws_report.append(header)
# 写入用户信息
for per_row in user_list:
ws_report.append(per_row)
# 读day_list列表，将信息写入单元格
# 定位单元格行row 列col
for per_row in day_list:
def splitDeals():
# Open an existing excel file
wb = openpyxl.load_workbook('data' + ".xlsx")
sheet = wb.worksheets[0]
# Create a new excel file
out = openpyxl.Workbook()
# Open the worksheet we want to edit
outsheet = out.create_sheet("data")
# if 'sheet' appears randomly we can delete it
rm = out.get_sheet_by_name('Sheet')
out.remove_sheet(rm)
start = 2
end = sheet.max_row + 1
#################
# DO STUFF HERE #
#################
ID = "A"
COMPANY = "B"
FINANCIAL_INSTITUTION = "C"
DEAL_TYPE = "D"
DEAL_SUB_TYPE = "E"
ORIGINAL_DEAL_TYPE = "F"
AMOUNT_MM_USD = "G"
COMMENTS = "H"
DATE = "I"
outsheet[ID + '1'].value = "Transaction Id"
outsheet[COMPANY + '1'].value = "Company"
outsheet[FINANCIAL_INSTITUTION + '1'].value = "Financial Institution"
outsheet[DEAL_TYPE + '1'].value = "Deal Type"
outsheet[DEAL_SUB_TYPE + '1'].value = "Deal Sub Type"
outsheet[ORIGINAL_DEAL_TYPE + '1'].value = "Original Deal Type"
outsheet[AMOUNT_MM_USD + '1'].value = "Amount MM USD"
outsheet[COMMENTS + '1'].value = "Comments"
outsheet[DATE + '1'].value = "Date"
# Only care about column C
count = 2
for row in range(start, end):
if sheet[FINANCIAL_INSTITUTION + str(row)].value:
banks = sheet[FINANCIAL_INSTITUTION + str(row)].value.replace(
',', '\n').split('\n')
else:
banks = ["Undisclosed"]
for bank in banks:
outsheet[ID + str(count)].value = sheet[ID + str(row)].value
outsheet[COMPANY + str(count)].value = sheet[COMPANY +
str(row)].value
outsheet[FINANCIAL_INSTITUTION + str(count)].value = bank.strip()
outsheet[DEAL_TYPE + str(count)].value = sheet[DEAL_TYPE +
str(row)].value
outsheet[DEAL_SUB_TYPE + str(count)].value = sheet[DEAL_SUB_TYPE +
str(row)].value
outsheet[ORIGINAL_DEAL_TYPE +
str(count)].value = sheet[ORIGINAL_DEAL_TYPE +
str(row)].value
outsheet[AMOUNT_MM_USD + str(count)].value = sheet[AMOUNT_MM_USD +
str(row)].value
outsheet[COMMENTS + str(count)].value = sheet[COMMENTS +
str(row)].value
outsheet[DATE + str(count)].value = sheet[DATE + str(row)].value
count = count + 1
out.save("organized.xlsx")
# LMK when the script is done
pygame.init()
pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
pygame.mixer.music.play()
time.sleep(5)
pygame.mixer.music.stop()
def energies(self, file, energies, frequencies=None,
stoichiometry=None, corrections=None):
"""Writes detailed information from multiple Energies objects to
single xlsx file.
Parameters
----------
file: string
path to file
energies: list of glassware.Energies
Energies objects that is to be exported
frequencies: glassware.DataArray, optional
DataArray object containing frequencies
stoichiometry: glassware.InfoArray, optional
InfoArray object containing stoichiometry information
corrections: list of glassware.DataArray
DataArray objects containing energies corrections"""
wb = oxl.Workbook()
ws = wb.active
ens_no = len(energies)
ws.title = 'Collective overview'
headers = ['Gaussian output file', 'Populations / %',
'Energies / hartree']
headers += ['Imag'] if frequencies is not None else []
headers += ['Stoichiometry'] if stoichiometry is not None else []
cells = ['A1', 'B1', f'{chr(66+ens_no)}1', f'{chr(66+2*ens_no)}1',
f'{chr(67+2*ens_no)}1']
for header, cell in zip(headers, cells):
ws[cell] = header
names = [self._header[en.genre] for en in energies]
ws.append([''] + names + names)
ws.merge_cells('A1:A2')
ws.merge_cells(f'B1:{chr(65+ens_no)}1')
ws.merge_cells(f'{chr(66+ens_no)}1:{chr(65+2*ens_no)}1')
if frequencies is not None or stoichiometry is not None:
ws.merge_cells(f'{chr(66+2*ens_no)}1:{chr(66+2*ens_no)}2')
if frequencies is not None and stoichiometry is not None:
ws.merge_cells(f'{chr(67+2*ens_no)}1:{chr(67+2*ens_no)}2')
ws.freeze_panes = 'A3'
# data = self.ts.energies
filenames = energies[0].filenames
fmts = ['0'] + ['0.00%'] * len(energies) + \
['0.' + '0' * (8 if en.genre == 'scf' else 6) for en in
energies] + \
['0', '0']
values = [en.values for en in energies]
populs = [en.populations for en in energies]
imag = frequencies.imaginary if frequencies is not None else []
stoich = stoichiometry.values if stoichiometry is not None else []
rows = zip_longest(filenames, *populs, *values, imag, stoich)
for row_num, values in enumerate(rows):
filtered_values = ((f, v) for f, v in zip(fmts, values)
if v is not None)
for col_num, (fmt, value) in enumerate(filtered_values):
cell = ws.cell(row=row_num + 3, column=col_num + 1)
cell.value = value
cell.number_format = fmt
# set cells width
widths = [0] + [10] * ens_no + [16] * ens_no
widths += [6] if frequencies is not None else []
widths += [0] if stoichiometry is not None else []
for column, width in zip(ws.columns, widths):
if not width:
width = max(len(str(cell.value)) for cell in column) + 2
ws.column_dimensions[column[0].column].width = width
# proceed to write detailed info on separate sheet for each energy
corrs = {c.genre[:3]: c for c in corrections} \
if corrections is not None else {}
for en in energies:
genre = en.genre
corr = corrs.get(genre, None)
fmts = ['0', '0.00%'] + ['0.0000'] * 2 + \
['0.00000000' if genre == 'scf' else '0.000000'] * 2
ws = wb.create_sheet(title=self._header[genre])
ws.freeze_panes = 'A2'
header = ['Gaussian output file', 'Population / %',
'Min. B. Factor', 'DE / (kcal/mol)',
'Energy / Hartree']
header += ['Correction / Hartree'] if corr is not None else []
ws.append(header)
corr = corr.values if corr is not None else []
rows = zip_longest(en.filenames, en.populations, en.min_factors,
en.deltas, en.values, corr)
for row_num, values in enumerate(rows):
filtered_values = ((f, v) for f, v in zip(fmts, values)
if v is not None)
for col_num, (fmt, value) in enumerate(filtered_values):
cell = ws.cell(row=row_num + 2, column=col_num + 1)
cell.value = value
cell.number_format = fmt
# set cells width
widths = [0, 15, 14, 15, 16, 19]
for column, width in zip(ws.columns, widths):
if not width:
width = max(len(str(cell.value)) for cell in column) + 2
ws.column_dimensions[column[0].column].width = width
wb.save(file)
logger.info('Energies export to xlsx files done.')
def run(output_path=None, user_provided_master_path=None, date_range=None):
"""
Main function to run this analyser.
:param output_path:
:param user_provided_master_path:
:return:
"""
if user_provided_master_path:
logger.info(f"Using master file: {user_provided_master_path}")
NUMBER_OF_PROJECTS = projects_in_master(user_provided_master_path)
else:
logger.info(f"Using default master file (refer to config.ini)")
NUMBER_OF_PROJECTS = projects_in_master(
os.path.join(ROOT_PATH,
runtime_config['MasterForAnalysis']['name']))
wb = openpyxl.Workbook()
segment_series_generator = _segment_series()
logger.debug(f"Using block_start of {BLOCK_START}")
logger.debug(f"Using day_range of {DAY_RANGE}")
logger.debug(f"Using block_skip of {BLOCK_SKIP}")
logger.debug(f"Using block_end of {BLOCK_END}")
logger.debug(f"Using forecast_actual_skip of {FORECAST_ACTUAL_SKIP}")
for p in range(1, NUMBER_OF_PROJECTS + 1):
proj_num, st_row = _row_calc(p)
wb = gather_data(st_row,
proj_num,
wb,
block_start_row=BLOCK_START,
interested_range=DAY_RANGE,
master_path=user_provided_master_path,
date_range=date_range)[0]
chart = ScatterChart()
chart.title = CHART_TITLE
chart.style = CHART_STYLE
chart.height = CHART_HEIGHT
chart.width = CHART_WIDTH
chart.x_axis.title = CHART_X_AXIS_TITLE
chart.y_axis.title = CHART_Y_AXIS_TITLE
chart.legend = None
chart.x_axis.majorUnit = CHART_X_AXIS_MAJOR_UNIT
chart.x_axis.minorGridlines = None
chart.y_axis.majorUnit = CHART_Y_AXIS_MAJOR_UNIT
derived_end = 2
if GREYMARKER:
markercol = _grey_marker_colours
else:
markercol = _marker_colours
for p in range(NUMBER_OF_PROJECTS):
for i in range(
1, 7
): # 7 here is hard-coded number of segments within a project series (ref: dict in _segment_series()
if i == 1:
inner_start_row = derived_end
else:
inner_start_row = derived_end
_inner_step = next(segment_series_generator)
series, derived_end = _series_producer(wb.active, inner_start_row,
_inner_step[1] - 1)
if _inner_step[0] == 'pvr_gate_zero':
series.marker.symbol = "diamond"
series.marker.graphicalProperties.solidFill = markercol[0]
elif _inner_step[0] == 'sobc':
series.marker.symbol = "circle"
series.marker.graphicalProperties.solidFill = markercol[0]
elif _inner_step[0] == 'obc':
series.marker.symbol = "triangle"
series.marker.graphicalProperties.solidFill = markercol[0]
elif _inner_step[0] == 'fbc':
series.marker.symbol = "square"
series.marker.graphicalProperties.solidFill = markercol[0]
elif _inner_step[0] == 'readiness_closure_exit':
series.marker.symbol = "plus"
series.marker.graphicalProperties.solidFill = markercol[0]
else:
series.marker.symbol = "triangle"
series.marker.graphicalProperties.solidFill = markercol[0]
series.marker.size = 10
chart.series.append(series)
segment_series_generator = _segment_series()
derived_end = derived_end + 1
wb.active.add_chart(chart, CHART_ANCHOR_CELL)
try:
if output_path:
wb.save(
os.path.join(output_path[0],
'swimlane_assurance_milestones.xlsx'))
logger.info(
f"Saved swimlane_assurance_milestones.xlsx to {output_path}")
else:
output_path = os.path.join(ROOT_PATH, 'output')
wb.save(
os.path.join(output_path,
'swimlane_assurance_milestones.xlsx'))
logger.info(
f"Saved swimlane_assurance_milestones.xlsx to {output_path}")
except PermissionError:
logger.critical(
"Cannot save swimlane_assurance_milestones.xlsx file - you already have it open. Close and run again."
)
return
#Excel関数の埋め込み
import openpyxl
book = openpyxl.Workbook()
active_sheet = book.active
active_sheet["B2"] = 1
active_sheet["B3"] = 10
active_sheet["B4"] = 100
active_sheet["B5"] = "=sum(B2,B3,B4)"
book.save("library/use_file/openpyxl/xlsx/sample8.xlsx")
#print(type(wb))
#print(wb.get_sheet_names())
l = wb.get_sheet_names()
l.pop()
#anotherSheet = wb.active
#print(anotherSheet)
#sheet = wb.get_sheet_by_name('Current')
#print(sheet)
#print(sheet['A1'].value)
#print(sheet.max_row)
#print(sheet.max_column)
#print(sheet.cell(row=1, column=2).value)
wb1 = openpyxl.Workbook()
print(wb1.active)
sheet1 = wb1.get_sheet_by_name('Sheet')
wb1.remove_sheet(sheet1)
for i in l:
sheet = wb.get_sheet_by_name(i)
sheet1 = wb1.create_sheet(title=i)
n = sheet.max_row
print(n)
m = sheet.max_column
print(m)
for i in range(1, n + 1):
for j in range(1, m + 1):
sheet1.cell(row=i,
column=j).value = sheet.cell(row=i,
def __init__(self, filename):
self.wb = openpyxl.Workbook()#生成一个实例
self.wb.save(filename)#生成一个空excel文件
self.filename = filename
self.wb = load_workbook(self.filename)#读取excel
self.ws = self.wb.active # 激活sheet
def open(self, file_name, **keywords):
BookWriter.open(self, file_name, **keywords)
self.native_book = openpyxl.Workbook()
def make_excel_file():
remote_addr = ipaddress.ip_address(request.remote_addr)
if remote_addr != ipaddress.ip_address("127.0.0.1"):
return "Access Denied", 403
hospital_id = request.args.get("hpid")
exporting_path = request.args.get("path")
#print(exporting_path)
db = Database(**dns)
def get_columns(result):
#q_columns = f'''
# SHOW COLUMNS FROM `{ table }`;
#'''
#res_columns = db.query( q_columns )
#columns = list( map( lambda x: x[ 'Field' ], res_columns ) )
#return columns
return result[0].keys()
def table_to_excel(table, ws):
if isinstance(table[0], dict):
columns = table[0].keys()
elif isinstance(table[0], list):
columns = table[0]
if isinstance(table[1][0], tuple):
table = table[1]
else:
table.pop(0)
x = 1
for column in columns:
ws.cell(row=1, column=x, value=column)
x += 1
for i, table_row in enumerate(table):
for j, column in enumerate(columns):
value = None
if isinstance(table[0], dict):
value = table_row[column]
elif isinstance(table[0], tuple):
value = table_row[j]
elif isinstance(table[0], list):
try:
value = table_row[j]
except IndexError:
value = None
if value == b'\x00':
if column == 'sex':
value = 'Female'
else:
value = False
elif value == b'\x01':
if column == 'sex':
value = 'Male'
else:
value = True
#print( value )
ws.cell(row=2 + i, column=j + 1, value=value)
# baseline
q_baseline = f'''
SELECT * FROM `patients`
LEFT JOIN `ucg`
ON `patients`.`ucg_id` = `ucg`.`ucg_id`
WHERE `patients`.`hospital_id` = { hospital_id }
ORDER BY `patients`.`patient_number`;
'''
baseline_table = db.query(q_baseline)
wb = xl.Workbook()
baseline_ws = wb.active
baseline_ws.title = "Baseline"
table_to_excel(baseline_table, baseline_ws)
for count, col in enumerate([1, 2, 3, 5]):
baseline_ws.delete_cols(col - count)
#1st session
q_first_session = f'''
SELECT `patients`.`patient_number`, `first_ablation`.* FROM `patients`
LEFT JOIN `first_ablation`
ON `patients`.`patient_serial_number` = `first_ablation`.`patient_serial_number`
WHERE `patients`.`hospital_id` = { hospital_id }
ORDER BY `patients`.`patient_number`;
'''
first_session_table = db.query(q_first_session)
first_session_ws = wb.create_sheet(title="1st session")
table_to_excel(first_session_table, first_session_ws)
for count, col in enumerate([2, 4, 5]):
first_session_ws.delete_cols(col - count)
#1st session medicine
q_first_medicine = f'''
SELECT `patients`.`patient_number`, `internal_medicine`.* FROM `patients`
LEFT JOIN `first_ablation`
ON `patients`.`patient_serial_number` = `first_ablation`.`patient_serial_number`
LEFT JOIN `internal_medicine`
ON `first_ablation`.`internal_medicine_id` = `internal_medicine`.`internal_medicine_id`
WHERE `patients`.`hospital_id` = { hospital_id }
ORDER BY `patients`.`patient_number`;
'''
first_medicine_table = db.query(q_first_medicine)
first_medicine_ws = wb.create_sheet(title="1st session内服薬")
table_to_excel(first_medicine_table, first_medicine_ws)
for count, col in enumerate([2]):
first_medicine_ws.delete_cols(col - count)
#Following session
q_following_session = f'''
SELECT `patients`.`patient_number`, `following_ablation`.*, `ucg`.*, `internal_medicine`.* FROM `patients`
LEFT JOIN `following_ablation`
ON `following_ablation`.`patient_serial_number` = `patients`.`patient_serial_number`
LEFT JOIN `ucg`
ON `following_ablation`.`ucg_id` = `ucg`.`ucg_id`
LEFT JOIN `internal_medicine`
ON `following_ablation`.`internal_medicine_id` = `internal_medicine`.`internal_medicine_id`
WHERE `patients`.`hospital_id` = { hospital_id }
ORDER BY `patient_number`, `following_ablation`.`date`;
'''
following_session_table = db.query(q_following_session)
# horizonize
following_session_df = pd.DataFrame(following_session_table)
following_session_df = following_session_df.drop([
'following_ablation_id', 'patient_serial_number', 'ucg_id',
'internal_medicine_id'
],
axis=1)
patients = following_session_df["patient_number"].unique()
fs_table = []
iterated_count = 0
for patient in patients:
following_session_a_patient = following_session_df[
following_session_df["patient_number"] == patient]
patient_row = []
for count, row in enumerate(following_session_a_patient.itertuples()):
row_list = list(row)
row_list.pop(0)
patient_row.extend(row_list)
if count > iterated_count:
iterated_count = count
#print( patient_row )
fs_table.append(patient_row)
original_headers = following_session_df.columns.tolist()
#original_headers.pop(0)
header_list = []
for i in range(iterated_count):
header_list.extend(original_headers)
fs_table.insert(0, header_list)
#print( fs_table )
following_session_ws = wb.create_sheet(title="following session")
table_to_excel(fs_table, following_session_ws)
#for count,col in enumerate([2,4,5]):
# following_session_ws.delete_cols(col - count)
#Follow up
q_followup = f'''
SELECT `patients`.`patient_number`, `follow_up`.*,
`ucg1`.*, `ucg2`.*, `ucg3`.*, `blood1`.*, `blood2`.*, `blood3`.* FROM `patients`
LEFT JOIN `follow_up` ON `patients`.`patient_serial_number` = `follow_up`.`patient_serial_number`
LEFT JOIN `ucg` as `ucg1` ON `follow_up`.`ucg_id1` = `ucg1`.`ucg_id`
LEFT JOIN `ucg` as `ucg2` ON `follow_up`.`ucg_id2` = `ucg2`.`ucg_id`
LEFT JOIN `ucg` as `ucg3` ON `follow_up`.`ucg_id3` = `ucg3`.`ucg_id`
LEFT JOIN `blood_exam` as `blood1` ON `follow_up`.`blood_id1` = `blood1`.`blood_id`
LEFT JOIN `blood_exam` as `blood2` ON `follow_up`.`blood_id2` = `blood2`.`blood_id`
LEFT JOIN `blood_exam` as `blood3` ON `follow_up`.`blood_id3` = `blood3`.`blood_id`
WHERE `patients`.`hospital_id` = { hospital_id }
ORDER BY `patients`.`patient_number`;
'''
followup_table = db.query(q_followup, duplicated=True)
#print(followup_table[0])
#print(followup_table[1])
followup_ws = wb.create_sheet(title="follow up")
table_to_excel(followup_table, followup_ws)
wb.save(exporting_path)
return ("dekitayo!")
soup = BeautifulSoup(open('yiqing.html','rb'))
table = soup.table
head = Head(table)
body = Body(table)
#body = Body_China(table)
#body = Body_country(body)
# tmp = []
# for i in body:
# tmp.append(i[1])
# import pickle
# with open("tmp2","wb") as f:
# pickle.dump(tmp, f)
outwb = openpyxl.Workbook() # 打开一个将写的文件
outws = outwb.create_sheet(index=0)
for i in range(len(body)):
for j in range(len(head)):
outws.cell(i*len(head)+j+1, 1).value = body[i][0]
outws.cell(i*len(head)+j+1, 2).value = body[i][1]
outws.cell(i*len(head)+j+1, 3).value = head[j]
if j==0:
outws.cell(i*len(head)+j+1, 4).value = body[i][j+2]
else:
outws.cell(i*len(head)+j+1, 4).value = int(body[i][j+2])-int(body[i][j+1])
try:
outws.cell(i*len(head)+j+1, 5).value = body[i][j+2]
except:
print(i,j)
def export(self) -> str:
def cm_to_inch(cm: float) -> float:
return cm * 0.393701
def prepare(ws: openpyxl.worksheet.Worksheet) -> None:
ws.header_footer.setHeader(
'&L&"Calibri,Regular"&K000000&G&C&"Lao UI,Bold"&8Total Project Construction Pty. Ltd.&"Lao UI,Regular"&K000000_x000D_ACN 117 578 560 ABN 84 117 578 560_x000D_PO Box 313 HALL ACT_x000D_P: 02-6230 2455 F:02-6230 2488_x000D_E: [email protected]'
)
ws.header_footer.setFooter(
f'&L&"Arial,Italic"&9&K000000App. A - Contract Variations&R&"Arial,Italic"&9&K000000{self.name}'
)
ws.page_margins.top = cm_to_inch(3.4)
ws.page_margins.bottom = cm_to_inch(2)
ws.page_margins.left = cm_to_inch(1.2)
ws.page_margins.right = cm_to_inch(1.1)
fill = PatternFill(patternType='solid', fgColor=Color('D8E4BC'))
wb: openpyxl.Workbook = openpyxl.Workbook()
ws: openpyxl.worksheet.Worksheet = wb.active
ws.title = 'Claim - TOTAL'
prepare(ws)
if self.client is None:
client_name = ''
client_first_line_address = ''
client_second_line_address = ''
else:
client_name = self.client.name
client_first_line_address = self.client.first_line_address
client_second_line_address = self.client.second_line_address
if self.superintendent is None:
superintendent_name = ''
superintendent_first_line_address = ''
superintendent_second_line_address = ''
else:
superintendent_name = self.superintendent.name
superintendent_first_line_address = self.superintendent.first_line_address
superintendent_second_line_address = self.superintendent.second_line_address
ws['A1'].value = 'Client: '
ws['A1'].value += ' '.join([client_name, superintendent_name])
ws['A1'].font = Font(name='Lao UI', size=10, bold=True)
ws['A2'].value = ' '
ws['A2'].value += ' '.join(
[client_first_line_address, superintendent_first_line_address])
ws['A3'].value = ' '
ws['A3'].value += ' '.join(
[client_second_line_address, superintendent_second_line_address])
ws['C1'].value = f'Reference #: {arrow.now("Australia/Canberra").format("MM")}-{self.reference_number}'
ws['C1'].font = Font(name='Lao UI', size=10, bold=True)
ws['C3'].value = f'Date: {arrow.now("Australia/Canberra").format("DD/MM/YY")}'
ws['C3'].font = Font(name='Lao UI', size=10)
ws['A4'].value = 'PROGRESS CLAIM No.'
ws['A4'].font = Font(name='Lao UI', size=14)
ws['A4'].fill = fill
ws['B4'].value = f'Project No: {self.reference_number}'
ws['B4'].font = Font(name='Lao UI', size=10)
ws['C4'].value = f'{arrow.now("Australia/Canberra").format("MMMM")}'
ws['C4'].font = Font(name='Lao UI', size=10)
ws['B5'].value = 'Approval terms: '
ws['B5'].font = Font(name='Lao UI', size=10)
ws['A6'].value = f'Project: {self.name}'
ws['A6'].font = Font(name='Lao UI', size=11, bold=True)
ws['B6'].value = 'Contract'
ws['B6'].font = Font(name='Lao UI', size=10, bold=True)
ws['B6'].alignment = Alignment(horizontal='center')
ws['C6'].value = 'Completed'
ws['C6'].font = Font(name='Lao UI', size=10, bold=True)
ws['C6'].alignment = Alignment(horizontal='center')
ws['A7'].value = ''
ws['B7'].value = 'Value'
ws['B7'].font = Font(name='Lao UI', size=10, bold=True)
ws['B7'].alignment = Alignment(horizontal='center')
ws['C7'].value = 'To Date'
ws['C7'].font = Font(name='Lao UI', size=10, bold=True)
ws['C7'].alignment = Alignment(horizontal='center')
ws.merge_cells('D6:D7')
ws['D6'].value = '%'
ws['D6'].font = Font(name='Arial', size=12, bold=True)
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
for row in [6, 7]:
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.fill = fill
ws['A1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['B1'].border = Border(
top=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['D1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['B2'].border = Border(
right=Side(border_style='medium', color='FF000000'))
ws['A3'].border = Border(
bottom=Side(border_style='medium', color='FF000000'))
ws['B3'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['A4'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
)
ws['A6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['B6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['D6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['A7'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['B7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['D7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
row = 8
def _comp(p):
return p.id
self.progress_items.sort(key=_comp)
for progress_item in self.progress_items:
ws[f'A{row}'].value = progress_item.name
ws[f'B{row}'].value = progress_item.contract_value
ws[f'C{row}'].value = progress_item.completed_value
ws[f'D{row}'].value = f'= C{row}/B{row}'
# print(ws['B{}'.format(row)].number_format)
# print(ws['C{}'.format(row)].number_format)
row += 1
for irow in range(8, row):
for column in 'ABCD':
cell = ws[f'{column}{irow}']
cell.font = Font(name='Lao UI', size=9)
cell.border = Border(
left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
)
if column == 'D':
cell.alignment = Alignment(vertical='center',
horizontal='center')
ws[f'A{row}'].value = 'TOTAL OF CONTRACT'
ws[f'B{row}'].value = f'=SUM(B{8}:B{row - 1})'
ws[f'C{row}'].value = f'=SUM(C{8}:C{row - 1})'
ws[f'D{row}'].value = f'=C{row}/B{row}'
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.font = Font(name='Lao UI', size=9, bold=True)
row += 1
ws[f'A{row}'].value = 'Variations - See Appendix A attached'
ws[f'B{row}'].value = r"='Appendix A'!D34"
ws[f'C{row}'].value = r"='Appendix A'!E34"
ws[f'D{row}'].value = f'=C{row}/B{row}'
for row in [row - 1, row]:
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.fill = fill
cell.border = Border(top=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'),
left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Totals Excluding GST'
ws[f'B{row}'].value = f'=B{row - 1} + B{row - 2}'
ws[f'C{row}'].value = f'=C{row - 1} + C{row - 2}'
ws[f'D{row}'].value = f'=C{row}/B{row}'
for column in 'BCD':
cell = ws[f'{column}{row}']
cell.fill = fill
cell.border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Less paid to date'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Value of work completed this period'
ws[f'C{row}'].value = f'=C{row - 2} - C{row - 1}'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'GST this period'
ws[f'C{row}'].value = f'=C{row - 1} * 10%'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
for irow in range(row - 3, row + 1):
for column in 'ABCD':
cell = ws[f'{column}{irow}']
cell.font = Font(name='Lao UI', size=9)
row += 1
ws[f'A{row}'].value = 'TOTAL PAYABLE THIS CLAIM'
ws[f'A{row}'].font = Font(name='Lao UI', size=9, bold=True)
ws[f'C{row}'].value = f'=C{row - 2} + C{row - 1}'
ws[f'C{row}'].font = Font(name='Lao UI', size=9, bold=True)
ws[f'C{row}'].border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
for irow in range(8, row + 1):
ws[f'B{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws[f'C{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws[f'D{irow}'].number_format = r'0.00%'
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 8
ws.sheet_view.view = 'pageLayout'
ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
ws.title = 'Appendix A'
prepare(ws)
ws.merge_cells('A1:D1')
ws['A1'].style.alignment.wrap_text = True
ws['A1'].value = f'{self.name}\nJOB #: {self.reference_number}'
ws['A1'].fill = fill
ws['A1'].font = Font(name='Lao UI', size=11, bold=True)
ws.merge_cells('A3:B3')
ws['A3'].value = "Appendix 'A' - Contract variations"
ws['A3'].font = Font(name='Lao UI', size=10, bold=True)
# ws['E3'].value = '=TODAY()'
ws['E3'].value = datetime.today()
ws['E3'].number_format = 'mm-dd-yy'
ws['E3'].font = Font(name='Lao UI', size=9)
ws['A5'].value = 'NO.'
ws['B5'].value = 'ITEM'
ws['C5'].value = 'PENDING'
ws['D5'].value = 'APPROVED'
ws['E5'].value = 'COMPLETED'
for cell in ['A5', 'B5', 'C5', 'D5', 'E5']:
ws[cell].font = Font(name='Lao UI', size=10, bold=True)
ws[cell].alignment = Alignment(horizontal='center')
ws[cell].fill = fill
ws[cell].border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
row = 6
self.variations.sort(key=lambda v: v.vid)
for variation in self.variations:
ws['A' + str(row)].value = row - 5
ws['B' + str(row)].value = variation.description
if variation.pending or variation.approved:
if variation.pending:
column = 'C'
else:
column = 'D'
ws[column + str(row)].value = variation.amount
elif variation.declined:
ws['B' + str(
row
)].value = f"{variation.description} (declined {variation.amount})"
ws['C' + str(row)].value = 0.0
if variation.completed:
ws['E' + str(row)].value = variation.amount
row += 1
while row < 34:
for column in ['A', 'B', 'C', 'D', 'E']:
cell = ws[column + str(row)]
cell.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
for index in range(6, row):
for column in ['A', 'B', 'C', 'D', 'E']:
cell = ws[f'{column}{index}']
cell.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
if column == 'A':
cell.font = Font(name='Lao UI', size=10, bold=True)
cell.alignment = Alignment(vertical='center',
horizontal='center')
else:
cell.font = Font(name='Lao UI', size=9)
if column == 'B':
cell.alignment = Alignment(vertical='center',
horizontal='left')
else:
cell.alignment = Alignment(vertical='center')
cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws.merge_cells(f'A{row}:B{row}')
ws[f'A{row}'].value = 'TOTALS'
ws[f'C{row}'].value = f'=SUM(C6:C{row - 1})'
ws[f'D{row}'].value = f'=SUM(D6:D{row - 1})'
ws[f'E{row}'].value = f'=SUM(E6:E{row - 1})'
for column in 'ABCDE':
cell = ws[f'{column}{row}']
cell.alignment = Alignment(vertical='center', horizontal='center')
cell.fill = fill
cell.border = Border(left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'),
top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'))
if column != 'A':
cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws.row_dimensions[1].height = 30
for row in range(len(self.variations)):
ws.row_dimensions[6 + row].height = 30
ws.column_dimensions['A'].width = 5
ws.column_dimensions['B'].width = 40
ws.column_dimensions['C'].width = 13
ws.column_dimensions['D'].width = 13
ws.column_dimensions['E'].width = 13
ws.cell('A1').alignment = Alignment(wrapText=True)
ws.sheet_view.view = 'pageLayout'
for index, variation in enumerate(self.variations):
new_ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
prepare(new_ws)
new_ws.title = f'V{index + 1}'
new_ws.merge_cells('A1:H1')
new_ws['A1'].value = 'CONTRACT VARIATION'
new_ws['A1'].fill = fill
new_ws['A1'].font = Font(name='Lao UI', size=16, bold=True)
new_ws['A1'].alignment = Alignment(vertical='center',
horizontal='center')
for column in 'ABCDEFGH':
new_ws[column + '1'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'))
new_ws.merge_cells('B3:H3')
new_ws['B3'].value = f'PROJECT: {self.name}'
new_ws['B3'].fill = fill
new_ws['B3'].font = Font(name='Lao UI', size=12, bold=True)
for column in 'BCDEFGH':
new_ws[f'{column}3'].border = Border(
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
new_ws['B5'].value = 'VARIATION NO:'
new_ws['B5'].fill = fill
new_ws['C5'].fill = fill
new_ws['B5'].font = Font(name='Lao UI', size=12, bold=True)
new_ws.merge_cells('D5:E5')
new_ws['D5'].value = index + 1
new_ws['D5'].fill = fill
new_ws['D5'].font = Font(name='Lao UI', size=14, bold=True)
new_ws.merge_cells('G5:H5')
new_ws['G5'].value = f'TPC REF: {self.reference_number}'
new_ws['G5'].font = Font(name='Lao UI', size=14, bold=True)
new_ws['G5'].alignment = Alignment(vertical='center',
horizontal='left')
for column in 'BCDE':
cell = new_ws[column + str(5)]
cell.border = Border(top=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'))
for column in 'BCDEFGH':
cell = new_ws[column + str(6)]
cell.border = Border(
bottom=Side(border_style='medium', color='FF000000'))
row = 7
if len(variation.items) > 1:
new_ws.merge_cells(f'B{row}:G{row}')
new_ws[f'B{row}'].value = variation.description
new_ws[f'B{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'B{row}'].alignment = Alignment(vertical='center')
row += 1
for item in variation.items:
new_ws.merge_cells(f'B{row}:G{row}')
new_ws[f'B{row}'].value = item.description
new_ws[f'B{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'B{row}'].alignment = Alignment(vertical='center')
new_ws[f'H{row}'].value = item.amount
new_ws[f'H{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'H{row}'].alignment = Alignment(vertical='center')
new_ws[
f'H{row}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
new_ws.row_dimensions[row].height = 40
row += 1
while row < 13:
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'H{row}'].font = Font(name='Lao UI', size=11)
row += 1
for column in 'BCDEFGH':
cell = new_ws[column + str(row - 1)]
cell.border = Border(
bottom=Side(border_style='thin', color='FF000000'))
new_ws['B' + str(row)].value = 'Value of work'
new_ws['H' + str(row)].value = f'=SUM(H7:H{row - 1})'
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
row += 1
new_ws['B' +
str(row)].value = f'Add OH/Profit {self.margin * 100}%'
new_ws['H' +
str(row)].value = f'=H{row - 1} * {self.margin * 100}%'
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
if self.admin_fee is not None and self.admin_fee != 0:
row += 1
new_ws['B' + str(row)].value = 'Fixed administration fee'
new_ws['H' + str(row)].value = self.admin_fee
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
for column in 'BCDEFGH':
new_ws[column + str(row)].border = Border(
bottom=Side(border_style='thin', color='FF000000'))
row += 1
new_ws['B' + str(row)].value = 'Subtotal'
if self.admin_fee is None:
new_ws['H' + str(row)] = f'=H{row - 2} + H{row - 1}'
else:
new_ws['H' +
str(row)] = f'=H{row - 3} + H{row - 2} + H{row - 1}'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
bold=True)
row += 1
new_ws['B' + str(row)] = 'Add GST'
new_ws['H' + str(row)].value = f'=H{row - 1} * 0.1'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
underline='singleAccounting')
for column in 'BCDEFGH':
new_ws[column + str(row)].border = Border(
bottom=Side(border_style='medium', color='FF000000'))
row += 1
new_ws.merge_cells(f'B{row}:C{row}')
for column in 'BCDEFGH':
new_ws[f'{column}{row}'].fill = fill
new_ws['B' + str(row)] = 'TOTAL'
new_ws['H' + str(row)].value = f'=H{row - 1} + H{row - 2}'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
bold=True)
for idx in range(7, row + 1):
new_ws['H' + str(
idx
)].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
row += 4
new_ws[f'B{row}'].value = 'Variation Prepared By:'
new_ws[f'G{row}'].value = 'Variation Prepared For:'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
row += 5
new_ws[f'B{row}'].value = 'FOR'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'B{row + 1}'].value = 'Total Project Construction Pty Ltd'
new_ws[f'B{row + 1}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].value = 'FOR'
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row + 1}'].value = variation.prepared_for
new_ws[f'G{row + 1}'].font = Font(name='Lao UI', size=11)
row += 3
new_ws[f'B{row}'].value = 'Date:'
# new_ws['C{}'.format(row)].value = '=TODAY()'
new_ws[f'C{row}'].value = datetime.today()
new_ws[f'C{row}'].number_format = 'mm-dd-yy'
new_ws[f'G{row}'].value = 'Date:'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'C{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
new_ws.row_dimensions[1].height = 60
new_ws.row_dimensions[3].height = 40
new_ws.column_dimensions['A'].width = 3
new_ws.column_dimensions['D'].width = 6
new_ws.column_dimensions['F'].width = 4
new_ws.column_dimensions['H'].width = 12
new_ws.sheet_view.view = 'pageLayout'
fn = self.name + '.xlsx'
wb.save('generated/' + fn)
return fn
def Creat_excel(excel_name):
pwd = os.getcwd()
print(pwd)
wb = openpyxl.Workbook() #创建文件对象
wb.save(filename=excel_name) #保存文件，名字为excel01
print(excel_name + "新建成功")
def output_excel(categories, count_eval, avg_iou, detection, unnecessary,
pre_label_list, recalls, precisions):
size = len(categories)
# エクセル作成
wb = px.Workbook()
# シート作成
ws_1 = wb.active
ws_1 = wb.create_sheet(title='推論結果')
# セル結合
ws_1.merge_cells(start_row=1, start_column=1, end_row=3, end_column=1)
ws_1.merge_cells(start_row=1,
start_column=2,
end_row=1,
end_column=2 + size)
ws_1.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
ws_1.merge_cells(start_row=1,
start_column=3 + size,
end_row=1,
end_column=4 + size * 2)
ws_1.merge_cells(start_row=2,
start_column=3,
end_row=2,
end_column=2 + size)
ws_1.merge_cells(start_row=2,
start_column=3 + size,
end_row=2,
end_column=2 + size * 2)
ws_1.merge_cells(start_row=2,
start_column=3 + size * 2,
end_row=3,
end_column=3 + size * 2)
ws_1.merge_cells(start_row=2,
start_column=4 + size * 2,
end_row=3,
end_column=4 + size * 2)
ws_1.merge_cells(start_row=1,
start_column=5 + size * 2,
end_row=3,
end_column=5 + size * 2)
ws_1.merge_cells(start_row=1,
start_column=6 + size * 2,
end_row=3,
end_column=6 + size * 2)
ws_1.merge_cells(start_row=1,
start_column=7 + size * 2,
end_row=3,
end_column=7 + size * 2)
ws_1.merge_cells(start_row=1,
start_column=8 + size * 2,
end_row=3,
end_column=8 + size * 2)
# ヘッダーを記述
ws_1.cell(row=1, column=1).value = 'ラベル名'
ws_1.cell(row=1, column=2).value = '正解ボックス'
ws_1.cell(row=1, column=3 + size).value = '推論ボックス'
ws_1.cell(row=2, column=2).value = '未検出'
ws_1.cell(row=2, column=3).value = '検出'
ws_1.cell(row=2, column=3 + size).value = '検出'
ws_1.cell(row=2, column=3 + size * 2).value = '過検出'
ws_1.cell(row=2, column=4 + size * 2).value = '不要'
ws_1.cell(row=1, column=5 + size * 2).value = '平均IOU'
ws_1.cell(row=1, column=6 + size * 2).value = '再現率(Recall)'
ws_1.cell(row=1, column=7 + size * 2).value = '適合率(Precision)'
ws_1.cell(row=1, column=8 + size * 2).value = 'F値(F-measure)'
# ラベル名を記述
for i, label in enumerate(categories):
ws_1.cell(row=4 + i, column=1).value = label
ws_1.cell(row=3, column=3 + i).value = label
ws_1.cell(row=3, column=3 + size + i).value = label
# 評価データを記述
for i, box in enumerate(count_eval):
for j, num in enumerate(box):
ws_1.cell(row=4 + i, column=2 + j).value = num
# 平均IOUを記述
for i, iou in enumerate(avg_iou):
ws_1.cell(row=4 + i, column=5 + size * 2).value = iou
#再現率、適合率、F値を記述
for i in range(len(recalls)):
ws_1.cell(row=4 + i, column=6 + size * 2).value = recalls[i]
ws_1.cell(row=4 + i, column=7 + size * 2).value = precisions[i]
if recalls[i] != 0 and precisions[i] != 0:
fmeasure = 2 * recalls[i] * precisions[i] / (recalls[i] +
precisions[i])
else:
fmeasure = 0
ws_1.cell(row=4 + i, column=8 + size * 2).value = fmeasure
# アルファベットのリストを生成
alphabet_list = [chr(i) for i in range(97, 105 + size * 2)]
# セル幅を広めに修正
# for i, c in enumerate(alphabet_list):
# ws_1.column_dimensions[c].width = 14
# 文字位置を変更
for i in range(1, 9 + size * 2):
for j in range(1, size + 4):
ws_1.cell(row = j, column = i).alignment = \
Alignment(horizontal = 'center', vertical = 'center')
# 枠線を生成
for i in range(1, 9 + size * 2):
for j in range(1, size + 4):
ws_1.cell(row = j, column = i).border = \
borders.Border(top = borders.Side(style = borders.BORDER_THIN, color = '000000'),
left = borders.Side(style = borders.BORDER_THIN, color = '000000'),
right = borders.Side(style = borders.BORDER_THIN, color = '000000'),
bottom = borders.Side(style = borders.BORDER_THIN, color = '000000'))
# フォントを変更
font = Font(name='メイリオ')
for i in range(1, 9 + size * 2):
for j in range(1, size + 4):
ws_1.cell(row=j, column=i).font = font
# シート作成
ws_2 = wb.create_sheet(title='IOU詳細')
# セル結合
ws_2.merge_cells(start_row=1, start_column=7, end_row=2, end_column=7)
ws_2.merge_cells(start_row=1, start_column=8, end_row=1, end_column=10)
ws_2.merge_cells(start_row=1, start_column=11, end_row=1, end_column=13)
# ヘッダーを記述
ws_2.cell(row=1, column=1).value = '画像'
ws_2.cell(row=1, column=2).value = '正解ラベル'
ws_2.cell(row=1, column=3).value = '推論ラベル'
ws_2.cell(row=1, column=4).value = 'IOU'
ws_2.cell(row=1, column=5).value = 'スコア'
ws_2.cell(row=1, column=7).value = 'ラベル名'
ws_2.cell(row=1, column=8).value = '検出'
ws_2.cell(row=1, column=11).value = '過検出'
ws_2.cell(row=2, column=8).value = '0.6未満'
ws_2.cell(row=2, column=9).value = '0.6以上0.7未満'
ws_2.cell(row=2, column=10).value = '0.7以上'
ws_2.cell(row=2, column=11).value = '0.6未満'
ws_2.cell(row=2, column=12).value = '0.6以上0.7未満'
ws_2.cell(row=2, column=13).value = '0.7以上'
# データを記述
for i, data in enumerate(detection):
ws_2.cell(row=2 + i, column=1).value = data[0]
ws_2.cell(row=2 + i, column=2).value = data[1]
ws_2.cell(row=2 + i, column=3).value = data[2]
ws_2.cell(row=2 + i, column=4).value = data[5]
ws_2.cell(row=2 + i, column=5).value = data[6]
cnt = 0
for i, x in enumerate(pre_label_list):
switch = 0
for j, y in enumerate(detection):
if (x[0] == y[0] and x[1] == y[2] and x[6] == y[4]
and x[7] == y[6]):
switch = 1
if (switch == 0):
ws_2.cell(row=len(detection) + 2 + cnt, column=1).value = x[0]
ws_2.cell(row=len(detection) + 2 + cnt, column=3).value = x[1]
ws_2.cell(row=len(detection) + 2 + cnt, column=5).value = x[7]
cnt += 1
iou_split = [[0 for i in range(7)] for j in range(len(categories))]
for i, label in enumerate(categories):
iou_split[i][0] = label
for data in detection:
if (label == data[1] and data[1] == data[2] and data[5] < 0.6):
iou_split[i][1] += 1
elif (label == data[1] and data[1] == data[2] and data[5] >= 0.7):
iou_split[i][3] += 1
elif (label == data[1] and data[1] == data[2] and data[5] >= 0.6
and data[5] < 0.7):
iou_split[i][2] += 1
if (label == data[1] and data[1] != data[2] and data[5] < 0.6):
iou_split[i][4] += 1
elif (label == data[1] and data[1] != data[2] and data[5] >= 0.7):
iou_split[i][6] += 1
elif (label == data[1] and data[1] != data[2] and data[5] >= 0.6
and data[5] < 0.7):
iou_split[i][5] += 1
# データを記述
for i, data in enumerate(iou_split):
ws_2.cell(row=3 + i, column=7).value = data[0]
ws_2.cell(row=3 + i, column=8).value = data[1]
ws_2.cell(row=3 + i, column=9).value = data[2]
ws_2.cell(row=3 + i, column=10).value = data[3]
ws_2.cell(row=3 + i, column=11).value = data[4]
ws_2.cell(row=3 + i, column=12).value = data[5]
ws_2.cell(row=3 + i, column=13).value = data[6]
# アルファベットのリストを生成
alphabet_list = [chr(i) for i in range(97, 110)]
# セル幅を広めに修正
for i, c in enumerate(alphabet_list):
ws_2.column_dimensions[c].width = 14
# 文字位置を変更
for i in range(1, 6):
ws_2.cell(row = 1, column = i).alignment = \
Alignment(horizontal = 'center', vertical = 'center')
for i in range(6, 14):
for j in range(1, size + 3):
ws_2.cell(row = j, column = i).alignment = \
Alignment(horizontal = 'center', vertical = 'center')
# 枠線を生成
for i in range(7, 14):
for j in range(1, size + 3):
ws_2.cell(row = j, column = i).border = \
borders.Border(top = borders.Side(style = borders.BORDER_THIN, color = '000000'),
left = borders.Side(style = borders.BORDER_THIN, color = '000000'),
right = borders.Side(style = borders.BORDER_THIN, color = '000000'),
bottom = borders.Side(style = borders.BORDER_THIN, color = '000000'))
# フォントを変更
for i in range(1, 14):
for j in range(1, len(detection) + 1):
ws_2.cell(row=j, column=i).font = font
# シート削除
wb.remove(wb['Sheet'])
# エクセル保存
save_excel_path = os.path.join(args.pre_xml, 'evaluation.xlsx')
wb.save(save_excel_path)
print('Successful Completion ({})'.format(save_excel_path))
def __init__(self, record):
self.rb = openpyxl.Workbook()
self.records_for_report = record
self.api_analysis_record = self.create_analysis(record)
def generate_analysis_report(result: dict,
file_path: str,
analyzer_name_dict: dict = {},
stock_name_dict: dict = {}):
wb = openpyxl.Workbook()
ws_score = wb.active
ws_score.title = 'Score'
ws_comments = wb.create_sheet('Comments')
ws_score['A1'] = 'Securities\\Analyzer'
ws_comments['A1'] = 'Securities\\Analyzer'
ROW_OFFSET = 2
total_score = []
column = 1
for analyzer_uuid, analysis_result in result.items():
# Write securities column
if column == 1:
# The first run. Init the total score list here.
# Flaw: The first column of result should be the full one. Otherwise the index may out of range.
total_score = [[] for _ in range(0, len(analysis_result))
] # 100: Pass; 0: Fail; 50: Pass with None
row = 2
col = index_to_excel_column_name(column)
for r in analysis_result:
securities_name = stock_name_dict.get(r.securities, '')
display_text = (
r.securities + ' | ' +
securities_name) if securities_name != '' else r.securities
ws_score[col + str(row)] = display_text
ws_comments[col + str(row)] = display_text
row += 1
column = 2
# Write analyzer name
row = 1
col = index_to_excel_column_name(column)
analyzer_name = analyzer_name_dict.get(analyzer_uuid, analyzer_uuid)
ws_score[col + str(row)] = analyzer_name
ws_comments[col + str(row)] = analyzer_name
# Write scores
row = ROW_OFFSET
for r in analysis_result:
ws_score[col + str(row)] = r.score if r.score is not None else '-'
ws_comments[col + str(row)] = r.reason
if r.score is not None:
total_score[row - ROW_OFFSET].append(r.score)
fill_style = __score_to_fill_style(r.score)
ws_score[col + str(row)].fill = fill_style
ws_comments[col + str(row)].fill = fill_style
row += 1
column += 1
# Write total score
row = 1
col = index_to_excel_column_name(column)
for score in total_score:
if row == 1:
ws_score[col + str(row)] = 'Total Result'
row = 2
if len(score) > 0:
min_score = min(score)
# avg_score = sum(score) / float(len(score))
else:
min_score = None
# avg_score = None
fill_text = __score_to_fill_text(min_score)
fill_style = __score_to_fill_style(min_score)
ws_score[col + str(row)] = fill_text
ws_comments[col + str(row)] = fill_text
ws_score[col + str(row)].fill = fill_style
ws_comments[col + str(row)].fill = fill_style
row += 1
# Write file
wb.save(file_path)
def __init__(self):
#Before this class/function comes into action
# have every necessary field already generated
self.wb = oxl.Workbook()
import requests
import openpyxl
client_id = '77dhvEQYUb57IC1W2u8j'
client_secret = '2Bjqxuk_Pn'
start, num = 1, 0
excel_file = openpyxl.Workbook()
excel_sheet = excel_file.active
excel_sheet.column_dimensions['B'].width = 100
excel_sheet.column_dimensions['C'].width = 100
excel_sheet.append(['랭킹', '제목', '링크'])
for index in range(10):
start_number = start + (index * 100)
naver_open_api = 'https://openapi.naver.com/v1/search/shop.json?query=샤오미&display=100&start=' + str(
start_number)
header_params = {
"X-Naver-Client-Id": client_id,
"X-Naver-Client-Secret": client_secret
}
res = requests.get(naver_open_api, headers=header_params)
if res.status_code == 200:
data = res.json()
for item in data['items']:
num += 1
#print(num, item['title'], item['link'])
excel_sheet.append([num, item['title'], item['link']])
else:
print("Error Code:", res.status_code)
import pandas as pd
from pandas import DataFrame
print(os.path.abspath('.')) # 打开文件夹统计xlsx个数
n = [
i for i in os.listdir('.') # 循环查找"."文件
if os.path.isfile(i) and os.path.splitext(i)[1] == '.xlsx'
] # 如果是.xlsx计数 加一得到这些文件 放入字典中【】
print(n)
print(len(n))
print(n[0])
wb0 = openpyxl.load_workbook(filename=n[0]) # 打开文件judge1
ws0 = wb0.active
# 第一个评分judge1表作为基准表
print(ws0.cell(1, 1).value)
wb1 = openpyxl.Workbook().save("list.xlsx")
wb1 = openpyxl.load_workbook("list.xlsx")
ws1 = wb1.active
# 两个表格同时使用 将基准表的值赋给最终总表
for i in range(1, 12): # 11为列数，共统计11列
ws1.cell(1, i).value = ws0.cell(1, i).value
ws1.cell(2, i).value = ws0.cell(2, i).value
# 复制第一，二行赋值
wb1.save("list.xlsx")
# for i in range(1, len(n)):
# print("judge"+str(i))
# ws1.cell(1, i+1).value = "judge"+str(i)
# # 赋值judge1，judge2，judge3表头
# for i in range(2, ws0.max_row+1):
# ws1.cell(i, 2).value = ws0.cell(i, 2).value
#from sklearn.tree import DecisionTreeClassifier
#clf = DecisionTreeClassifier()
#clf.fit(X_train,Y_train)
from sklearn.ensemble import RandomForestClassifier
clf2 = RandomForestClassifier(n_estimators=1500, random_state=0)
clf2.fit(X_train, Y_train)
#Y_pred = clf.predict(X_test)
Y_pred2 = clf2.predict(X_test)
Y_pred2_list = Y_pred2.tolist()
##type(list(Y_pred2[0]))
import openpyxl as xl
wb = xl.Workbook()
sheet = wb.active
#sheet['A1'].value = 1
from openpyxl.utils import get_column_letter
#get_column_letter(1)
j = 1
for i in Y_pred2_list:
sheet['A' + str(j)] = j
sheet['B' + str(j)] = i
j = j + 1
wb.save('preds2.xlsx')
#import csv
#
def run(url):
global wb, ws, driver
# Init start time
start = datetime.datetime.now().replace(microsecond=0)
# Init Log
log_file = open(LOG_FILE, 'w+')
log_file.close()
# Init Driver
LOGGER.setLevel(logging.DEBUG)
driver = init_driver()
if driver is None:
sys.exit()
# Init Excel
wb = openpyxl.Workbook()
ws = wb.active
# TODO: Check if max page exists
# Init Scraping
original_url = url
proceed = True
try:
driver.get(original_url)
title = driver.title
except Exception as e:
log("ERROR Failed openning " + original_url + " - " +
str(e).split(os.linesep, 1)[0])
close_driver()
proceed = False
if proceed is True:
# Get all URLs
ads_urls = []
for page_index in range(1, args.pages + 1):
if driver is None:
driver = init_driver()
if driver is None:
log("WARN Failed to initialize driver, page " +
page_index + " will be skipped")
continue
index = original_url.rfind('/')
url = original_url[:index] + '/page-' + str(
page_index) + original_url[index:]
page_urls = get_page_ads(url, page_index)
if len(page_urls) == 0:
continue
for ad_url in page_urls:
if ad_url not in ads_urls:
ads_urls.append(ad_url)
if (page_index) % 10 == 0 or page_index == args.pages:
close_driver()
# Parse ads
total = len(ads_urls)
log("INFO * Total of " + str(total) + " ads will be scraped")
for index, ad_url in enumerate(ads_urls):
if driver is None:
driver = init_driver()
log("INFO Parsing " + str(index + 1) + "/" + str(total) + ": " +
ad_url)
try:
parse_ad(ad_url)
except selenium.common.exceptions.TimeoutException as e:
log("ERROR Failed to load page " + str(page_index) +
" before time out elapsed, closing driver...")
close_driver()
except Exception as e:
log("ERROR Failed to parse " + ad_url + " - (" +
str(e).split(os.linesep, 1)[0] + ")")
if (index + 1) % 20 == 0 or index + 1 == total:
close_driver()
# Save Results to Excel
try:
if os.path.isfile(RESULTS_FILE):
os.remove(RESULTS_FILE)
wb.save(RESULTS_FILE)
log("INFO Results saved as " + RESULTS_FILE + " in " + DIR_PATH)
except Exception as e:
log("ERROR Failed to save results " + str(e))
# Calculate Duration
end = datetime.datetime.now().replace(microsecond=0)
# Send eMail
if args.mail is True and proceed is True:
if can_send_email():
from_name = "Kijiji Scrapper"
if os.path.isfile(RESULTS_FILE):
message = title + os.linesep
message += original_url + os.linesep
message += "Duration - " + str(end - start) + os.linesep
send_mail(from_name, args.recipients,
"Kijiji Scraping Results", message,
[RESULTS_FILE, LOG_FILE], args.smtp_server,
args.smtp_server_port, args.smtp_server_username,
args.smtp_server_password, True)
else:
message = "Operation Failed." + os.linesep
message += "Logs Attached" + os.linesep
message += "Duration - " + str(end - start)
send_mail(from_name, args.recipients,
"Kijiji Scraping Results - Failed", "Logs attached",
[LOG_FILE], args.smtp_server, args.smtp_server_port,
args.smtp_server_username, args.smtp_server_password,
True)
log("INFO Sent results by mail to: " + ','.join(args.recipients))
else:
log("ERROR Can not send eMail")
log("INFO Script finsihed in " + str(end - start))
# Cleanup
if (args.mail):
if os.path.exists(RESULTS_FILE):
os.remove(RESULTS_FILE)
if os.path.exists(LOG_FILE):
os.remove(LOG_FILE)
print("Bye.")
def export_as_xslx(modeladmin, request, queryset):
# get fields to export
opts = modeladmin.model._meta
if not fields:
field_names = [field.name for field in opts.fields]
else:
field_names = fields
# Create a response header
response = HttpResponse(
content_type=
'application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename={}.xlsx'.format(
str(opts).replace('.', '_'))
# Create a new workbook
wb = openpyxl.Workbook()
ws = wb.active
ws.title = str(opts).replace('.', '_')
# Write the header (if desired)
if header:
def makeHeaderCell(field):
c = cell.Cell(ws, value=field)
c.font = styles.Font(bold=True)
return c
ws.append([makeHeaderCell(field) for field in field_names])
# Write each of the rows
for row in queryset.values_list(*field_names):
def makeCell(prop):
try:
return to_excel(prop)
except:
return str(prop)
ws.append([makeCell(c) for c in row])
# adjust column widths
# adapted from https://stackoverflow.com/a/39530676
for col in ws.columns:
max_length = 0
column = col[0].column # Get the column name
for c in col:
try:
if len(str(c.value)) > max_length:
max_length = len(c.value)
except:
pass
adjusted_width = (max_length + 2) * 1.2
ws.column_dimensions[column].width = adjusted_width
# and export
wb.save(response)
return response
def infer_schema(data,
fname,
output_root='',
sample_size=1.0,
type_threshold=0.5,
n_jobs=1,
base_schema=None):
"""
Infer data types for all columns for the input table
Parameters
----------
data: pandas DataFrame
data table to infer
fname: string
the output file name
output_root: string, default=''
the root directory for the output file
sample_size: int or float(<= 1.0), default=1.0
int: number of sample rows to infer the data type (useful for large tables)
float: sample size in percentage
type_threshold: float(<= 1.0), default=0.5
threshold for inferring data type
n_jobs: int, default=1
the number of jobs to run in parallel
base_schema: pandas DataFrame, default=None
data schema to base on
"""
# check sample_size
if sample_size > 1:
if int(sample_size) != sample_size:
raise ValueError(
'sample_size: only accept integer when it is > 1.0')
if sample_size > data.shape[0]:
print("sample_size: %d is larger than the data size: %d" %
(sample_size, data.shape[0]))
# check output_root
if output_root != '':
if not os.path.isdir(output_root):
raise ValueError('output_root: root not exists')
# check type_threshold
if (type_threshold <= 0) or (type_threshold > 1):
raise ValueError('type_threshold: should be in (0, 1]')
# check base_schema
if base_schema is not None:
if type(base_schema) != pd.core.frame.DataFrame:
raise ValueError('base_schema: only accept pandas DataFrame')
# open a new workbook to store all result
wb = openpyxl.Workbook()
ws = wb['Sheet']
ws.title = 'schema'
# calculate sample size
if sample_size <= 1.0:
sample_size = int(data.shape[0] * sample_size)
# dictionary to store dropna sample data values
data_dropna_sample_values = {}
for col in data.columns.values:
if len(data[col].dropna()) <= sample_size:
data_dropna_sample_values[col] = data[col].dropna().values
else:
data = data.sample(sample_size).reset_index(drop=True)
data_dropna_sample_values[col] = data[col].dropna().values
# use data_dropna_sample_values to infer data type for each column
_n_jobs = np.min([n_jobs, len(data.columns.values)])
type_infos = Parallel(n_jobs=_n_jobs)(delayed(_infer_dtype)(
data_dropna_sample_values[col], col, type_threshold)
for col in data.columns.values)
type_infos_df = pd.DataFrame(type_infos)[['column', 'type']]
# dtype mapping for basic stat calculation
data_types = {}
for col in data.columns.values:
data_types[col] = type_infos_df.loc[type_infos_df['column'] == col,
'type'].values[0]
# get basic statistic information for all columns
stat_infos = Parallel(n_jobs=_n_jobs)(delayed(_cal_column_stat)(
data_dropna_sample_values[col], col, data_types[col])
for col in data.columns.values)
stat_infos_df = pd.DataFrame(stat_infos)
# merge dtype infomation with stat information
full_infos_df = type_infos_df.merge(stat_infos_df, on='column', how='left')
# add include column
full_infos_df['include'] = 1
full_infos_df = full_infos_df[[
'column', 'type', 'include', 'sample_value', 'sample_num_uni',
'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max',
'sample_std'
]]
# if base_schema is provided, we can compare with base schema
if base_schema is not None:
base_schema = base_schema[['column', 'type', 'include']]
base_schema.columns = [
'base_%s' % (col) for col in base_schema.columns.values
]
full_infos_df = full_infos_df.merge(base_schema,
left_on='column',
right_on='base_column',
how='outer')
# compare with the base schema
full_infos_df['base_column'] = full_infos_df['base_column'].apply(
lambda x: 'column not in base table' if pd.isnull(x) else x)
full_infos_df['column'] = full_infos_df['column'].apply(
lambda x: 'column not in current table' if pd.isnull(x) else x)
# reorder the column
full_infos_df['include'] = base_schema['base_include']
full_infos_df = full_infos_df[[
'column', 'base_column', 'type', 'base_type', 'include',
'sample_value', 'sample_num_uni', 'sample_uni_percentage',
'sample_min', 'sample_median', 'sample_max', 'sample_std'
]]
# add data validation for type column
val_type = DataValidation(type="list",
formula1='"key,numeric,str,date"',
allow_blank=False)
ws.add_data_validation(val_type)
# add data validation for include column
val_include = DataValidation(type="list",
formula1='"0,1"',
allow_blank=False)
ws.add_data_validation(val_include)
# get col_name, excel column mapping
column_mapping = {}
for i, col in enumerate(full_infos_df.columns):
column_mapping[col] = xlsxwriter.utility.xl_col_to_name(i)
# write everything into the worksheet
for r_idx, r in enumerate(
dataframe_to_rows(full_infos_df, index=False, header=True)):
ws.append(r)
for cell_idx, cell in enumerate(
ws.iter_cols(max_col=ws.max_column,
min_row=ws.max_row,
max_row=ws.max_row)):
cell = cell[0]
if r_idx != 0:
val_type.add(ws['%s%d' % (column_mapping['type'], ws.max_row)])
val_include.add(ws['%s%d' %
(column_mapping['include'], ws.max_row)])
if cell_idx == 0:
cell.font = Font(bold=True)
else:
cell.style = 'Accent5'
# add conditional formating
red_fill = PatternFill(bgColor="FFC7CE")
red_font = Font(color="9C0006")
green_fill = PatternFill(bgColor="C6EFCE")
green_font = Font(color="006100")
blue_fill = PatternFill(bgColor="9ECAE1")
blue_font = Font(color="08306B")
orange_fill = PatternFill(bgColor="FDD0A2")
orange_font = Font(color="A63603")
purple_fill = PatternFill(bgColor="DADAEB")
purple_font = Font(color="3F007D")
# red highlight if there is any inconsistent between base and the target
if base_schema is not None:
col1 = column_mapping['column']
col2 = column_mapping['base_column']
ws.conditional_formatting.add(
'%s2:%s%d' % (col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
col1 = column_mapping['type']
col2 = column_mapping['base_type']
ws.conditional_formatting.add(
'%s2:%s%d' % (col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# yellow highlight column type and include (which need to be modified)
ws['%s1' % (column_mapping['type'])].style = 'Neutral'
ws['%s1' % (column_mapping['include'])].style = 'Neutral'
# green highlight for the mkey type and red highlight for the error type
type_cols = [column_mapping['type']]
if 'base_type' in column_mapping.keys():
type_cols.append(column_mapping['base_type'])
for col in type_cols:
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="error"' % (col)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="key"' % (col)],
stopIfTrue=True,
fill=green_fill,
font=green_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="numeric"' % (col)],
stopIfTrue=True,
fill=blue_fill,
font=blue_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="str"' % (col)],
stopIfTrue=True,
fill=orange_fill,
font=orange_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="date"' % (col)],
stopIfTrue=True,
fill=purple_fill,
font=purple_font))
# red highlight for include = 0
ws.conditional_formatting.add(
'%s2:%s%d' %
(column_mapping['include'], column_mapping['include'], ws.max_row),
FormulaRule(formula=['%s2=0' % (column_mapping['include'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# red highlight for sample_num_uni = 0 or 1, only one unique value
ws.conditional_formatting.add(
'%s2:%s%d' % (column_mapping['sample_num_uni'],
column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=0' % (column_mapping['sample_num_uni'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (column_mapping['sample_num_uni'],
column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=1' % (column_mapping['sample_num_uni'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# adjust the column format for the worksheet
_adjust_ws(ws=ws, row_height=20)
wb.save(filename=os.path.join(output_root, 'data_schema_%s.xlsx' %
(fname)))
def write_coupon_data_in_excel(row_data: list[Any]) -> None:
"""
Write row data in coupon_data.xlsx file. If this file not exits,
function to create this file and write column name.
row_data: list[Any] -> list consist of any data.
"""
xlsx_file_name: str = 'coupon_data.xlsx'
try:
coupon_data_workbook = openpyxl.load_workbook(xlsx_file_name)
except FileNotFoundError:
new_coupon_data_workbook = openpyxl.Workbook()
# Delete default list in workbook.
for sheet_name in new_coupon_data_workbook.sheetnames:
sheet = new_coupon_data_workbook[sheet_name]
new_coupon_data_workbook.remove(sheet)
coupon_data_list = new_coupon_data_workbook.create_sheet('Coupon data')
for column in range(1, len(COLUMNS_NAMES) + 1):
coupon_data_list.cell(
row=1, column=column).value = COLUMNS_NAMES[column - 1]
new_coupon_data_workbook.save(xlsx_file_name)
new_coupon_data_workbook.close()
coupon_data_workbook = openpyxl.load_workbook(xlsx_file_name)
coupon_data_list = coupon_data_workbook['Coupon data']
row: int = 2
column_letters = string.ascii_uppercase[:len(row_data)]
row_is_free: bool = False
while True:
for col_letter in column_letters:
if coupon_data_list[col_letter + str(row)].value is None:
row_is_free = True
else:
row_is_free = False
break
if row_is_free:
for column in range(1, len(row_data) + 1):
coupon_data_list.cell(
row=row, column=column).value = row_data[column - 1]
row += 1
break
else:
row += 1
try:
coupon_data_workbook.save(xlsx_file_name)
coupon_data_workbook.close()
except PermissionError:
print(
'Пожалуйста, закройте файл coupon_data.xlsx, так как парсер не может работать с открытым файлом для вывода.'
)
exit()
# blackRowInserter.py : inserts blank rows from row N to row M to spreadsheet
import sys
import openpyxl
# ENTER IN TERMINAL : blackRowInserter.py StartingRowNumber NumberOfBlankRows yourFile.xlsx
N = int(sys.argv[1]) # Starting row
M = int(sys.argv[2]) # Number of blank rows
filename = sys.argv[3] # File name
print('Opening Workbook...')
wb = openpyxl.load_workbook(filename)
sheet = wb.get_sheet_by_name('Sheet')
new_wb = openpyxl.Workbook()
new_sheet = new_wb.active
print('Adding Blank rows...')
for row in range(1, sheet.max_row+1):
for col in range(1, sheet.max_column+1):
if row < N:
new_sheet.cell(row=row, column=col).value = sheet.cell(row=row, column=col).value
else:
new_sheet.cell(row=row+M, column=col).value = sheet.cell(row=row, column=col).value
print('Done')
new_wb.save('yesblank.xlsx')
def reorganize():
# Open an existing excel file
wb = openpyxl.load_workbook('data' + ".xlsx")
sheet = wb.worksheets[0]
# Create a new excel file
out = openpyxl.Workbook()
# Open the worksheet we want to edit
outsheet = out.create_sheet("data")
# if 'sheet' appears randomly we can delete it
rm = out.get_sheet_by_name('Sheet')
out.remove_sheet(rm)
#################
# DO STUFF HERE #
#################
COMPANY = "A"
FINANCIAL_INSTITUTION = "B"
DEAL_TYPE = "C"
AMOUNT_USD = "D"
COMMENTS = "E"
DATE = "F"
outsheet[COMPANY + '1'].value = "Company"
outsheet[FINANCIAL_INSTITUTION + '1'].value = "Financial Institution"
outsheet[DEAL_TYPE + '1'].value = "Deal Type"
outsheet[AMOUNT_USD + '1'].value = "Amount USD"
outsheet[COMMENTS + '1'].value = "Comments"
outsheet[DATE + '1'].value = "Date"
# Data will always be in A column
deals = []
deal = []
for row in range(2, sheet.max_row + 1):
info = sheet['A' + str(row)].value
deal.append(info)
if type(info) is not str:
deals.append(deal)
deal = []
for row in range(0, len(deals)):
deal = deals[row]
if len(deals[row]) == 6:
outsheet[COMPANY + str(row + 2)].value = deal[0]
outsheet[FINANCIAL_INSTITUTION + str(row + 2)].value = deal[1]
outsheet[DEAL_TYPE + str(row + 2)].value = deal[2]
outsheet[AMOUNT_USD + str(row + 2)].value = deal[3]
outsheet[COMMENTS + str(row + 2)].value = deal[4]
outsheet[DATE + str(row + 2)].value = deal[5]
elif len(deals[row]) == 4:
outsheet[COMPANY + str(row + 2)].value = deal[0]
outsheet[DEAL_TYPE + str(row + 2)].value = deal[1]
outsheet[COMMENTS + str(row + 2)].value = deal[2]
outsheet[DATE + str(row + 2)].value = deal[3]
# HARD ONE
elif len(deals[row]) == 5:
# If we are missing the financial transaction fill it out like this
if deal[2][0] == "$":
print(str(row + 2) + " is missing TRANSACTION")
outsheet[COMPANY + str(row + 2)].value = deal[0]
outsheet[DEAL_TYPE + str(row + 2)].value = deal[1]
outsheet[AMOUNT_USD + str(row + 2)].value = deal[2]
outsheet[COMMENTS + str(row + 2)].value = deal[3]
outsheet[DATE + str(row + 2)].value = deal[4]
# Otherwise we are missing dollar amount so fill out like this
else:
print(str(row + 2) + " is missing AMOUNT")
outsheet[COMPANY + str(row + 2)].value = deal[0]
outsheet[FINANCIAL_INSTITUTION + str(row + 2)].value = deal[1]
outsheet[DEAL_TYPE + str(row + 2)].value = deal[2]
outsheet[COMMENTS + str(row + 2)].value = deal[3]
outsheet[DATE + str(row + 2)].value = deal[4]
print("Reorganized " + str(len(deals)) + " deals")
out.save("organized.xlsx")
# LMK when the script is done
pygame.init()
pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
pygame.mixer.music.play()
time.sleep(5)
pygame.mixer.music.stop()
def open(self):
self.workbook = openpyxl.Workbook()
def create_best_path_select_data(self,
route_content,
output_excel='best.xlsx'):
""" Creates the matrix of best path selection
Provides the test described in `smb://10.128.3.91/SharePoint01/31_VerificationRoom/31_13_検証環境セット/BGP-Best-Path-SelectionのAll-in-One設定_20161118改良/`
The test uses predefined Ixia config and follows predefined steps
"""
wb = openpyxl.Workbook()
wb.guess_types = True
tmp = output_excel.split('.')
sheet_name = tmp[0]
ws = wb.create_sheet(sheet_name, 0)
# adding column name
ws.append([
'INTF', 'Prefix', 'ProtoPref', 'LocalPref', 'AS_PATH', 'Origin', 'MED',
'Protocol', 'I/E', 'IGP', 'Age', 'RouterID', 'CLLength', 'Peer',
'Win/Loose', 'Reason'
])
# sparsing route information
route_info_list = re.findall(
r"(\S+/.{1,3}) (?:.*?entr.*?announced.*?)\r\n(( {8}[ *](\S+) *Pref.*?\r\n( {16}.*?\r\n)+)+)\r\n",
route_content, re.DOTALL)
line = 1 # excel start at number 1
for route_info in route_info_list:
route = route_info[0]
proto_list = re.findall(
r" {8}([ *])(\S+) *Preference: (\S+?)(?:/\S+){0,1}\r\n(( {16}.*?\r\n)+)",
route_info[1], re.DOTALL)
for proto_info in proto_list:
if proto_info[0] == '*':
win = 'win'
if line > 1:
ws.append([
'', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', ''
])
line = line + 1
else:
win = 'loose'
proto = proto_info[1]
pp = proto_info[2]
match_intf = re.search(r" via (\S+),", proto_info[3], re.MULTILINE)
if match_intf:
intf_index = int(match_intf.group(1).split('.')[1])
via_intf = chr(ord('A') + intf_index - 1)
else:
via_intf = '-'
match_localpref = re.search(r"Localpref: (\S+)", proto_info[3],
re.MULTILINE)
if match_localpref: local_pref = match_localpref.group(1)
else: local_pref = '-'
match_as_path = re.search(r"AS path: (.*?)\r\n", proto_info[3],
re.MULTILINE)
if match_as_path:
as_list = match_as_path.group(1).split()
as_path = len(as_list) - 1 # the last is the origin
if as_list[-1] == 'E':
origin = 'EGP'
else:
origin = 'IGP'
else:
as_path = '-'
origin = '-'
match_med = re.search(r"Metric: (\S+) ", proto_info[3],
re.MULTILINE)
if match_med:
med = match_med.group(1)
else:
med = '-'
match_type = re.search(r"Local AS: (\S+) Peer AS: (\S+)",
proto_info[3], re.MULTILINE)
if match_type:
if match_type.group(1) == match_type.group(2):
type = 'IBGP'
else:
type = 'EBGP'
else:
type = '-'
match_igp = re.search(r"Metric2: (\S+)", proto_info[3],
re.MULTILINE)
if match_igp:
igp = match_igp.group(1)
else:
igp = '-'
match_router_id = re.search(r"Router ID: (\S+)", proto_info[3],
re.MULTILINE)
if match_router_id:
router_id = match_router_id.group(1)
else:
router_id = '-'
match_cll = re.search(r"Cluster list: (.*)\r\n", proto_info[3],
re.MULTILINE)
if match_cll:
cll = len(match_cll.group(1).split())
else:
cll = '0'
match_peer = re.search(r"Source: (\S+)", proto_info[3],
re.MULTILINE)
if match_peer:
peer = match_peer.group(1)
else:
peer = '-'
# match_age = re.search(r"Age: (\S+)",proto_info[3],re.MULTILINE)
# if match_age:
# age = reduce(lambda x, y: x*60+y, [int(i) for i in match_age.group(1).split(':')])
# else:
# age = '-'
match_age = re.search(r"Age: (.*?) (?:\r\n| )", proto_info[3],
re.MULTILINE)
if match_age:
m = re.match(r"(\d+w){0,1}(\d+d){0,1} {0,1}(\S+)",
match_age.group(1))
age = 0
if m.group(1):
age = age + int(m.group(1)[:-1]) * 7 * 24 * 60 * 60
if m.group(2): age = age + int(m.group(2)[:-1]) * 24 * 60 * 60
age = age + sum(
int(x) * 60**i
for i, x in enumerate(reversed(m.group(3).split(":"))))
else:
age = '-'
match_reason = re.search(r"Inactive reason: (.+)\r\n",
proto_info[3], re.MULTILINE)
if match_reason:
reason = match_reason.group(1)
else:
reason = '-'
BuiltIn().log(
" Added row: \
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" %
(via_intf, route, pp, local_pref, as_path, origin, med, proto,
type, igp, age, router_id, cll, peer, win, reason))
row = [
via_intf, route, pp, local_pref, as_path, origin, med, proto,
type, igp, age, router_id, cll, peer, win, reason
]
ws.append(row)
line = line + 1
# coloring style
# HeaderFill = PatternFill(start_color='ffff00',end_color='ffff00',fill_type='solid')
RedFont = Font(color=colors.RED)
BlueFont = Font(color=colors.BLUE)
#for cell in ws[1]:
# cell.fill = HeaderFill
#for cell in ws['O']:
# if cell.value=='win': cell.font = RedFont
for cell in reversed(ws['P']):
row = int(cell.row)
if cell.value == 'Update source':
ws['N%d' % row].font = BlueFont
ws['N%d' % (row - 1)].font = RedFont
if cell.value == 'Cluster list length':
ws['M%d' % row].font = BlueFont
ws['M%d' % (row - 1)].font = RedFont
if cell.value == 'Router ID':
ws['L%d' % row].font = BlueFont
ws['L%d' % (row - 1)].font = RedFont
if cell.value == 'Active preferred':
ws['K%d' % row].font = BlueFont
ws['K%d' % (row - 1)].font = RedFont
if cell.value == 'IGP metric':
ws['J%d' % row].font = BlueFont
ws['J%d' % (row - 1)].font = RedFont
if cell.value == 'Interior > Exterior > Exterior via Interior':
ws['I%d' % row].font = BlueFont
ws['I%d' % (row - 1)].font = RedFont
if cell.value == 'Route Metric or MED comparison':
ws['H%d' % row].font = BlueFont
ws['H%d' % (row - 1)].font = RedFont
if cell.value == 'Always Compare MED':
ws['G%d' % row].font = BlueFont
ws['G%d' % (row - 1)].font = RedFont
if cell.value == 'Origin':
ws['F%d' % row].font = BlueFont
ws['F%d' % (row - 1)].font = RedFont
if cell.value == 'AS path':
ws['E%d' % row].font = BlueFont
ws['E%d' % (row - 1)].font = RedFont
if cell.value == 'Local Preference':
ws['D%d' % row].font = BlueFont
ws['D%d' % (row - 1)].font = RedFont
if cell.value == 'Route Preference':
ws['C%d' % row].font = BlueFont
ws['C%d' % (row - 1)].font = RedFont
# adjust column width
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12
ws.column_dimensions['E'].width = 12
ws.column_dimensions['H'].width = 12
ws.column_dimensions['L'].width = 12
ws.column_dimensions['M'].width = 12
ws.column_dimensions['O'].width = 12
ws.column_dimensions['P'].width = 32
tab = Table(displayName="Table1", ref="A1:P%d" % line)
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=False,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
# top = Border(top=border.top)
rows = ws["A1:P%d" % line]
for row in rows:
for cell in row:
cell.alignment = Alignment(horizontal="right")
ws.append([])
ws.append(['※1 Protocol Preference の値はルータの import policy で変更'])
ws.append(['※2 ここでは Always Compare MED 有りの試験のみ実施。'])
ws.append(
['※3 1.6.0.0/staticの場合、ルータにて PP 170 の static経路を設定。ただし、正常に試験できているか不明。'])
ws.append(['※4 全経路配信後、IF-B から配信しているこの経路(1.9.0.0) だけ一回フラップさせるた。'])
save_path = os.getcwd() + '/' + Common.get_result_folder(
) + '/' + output_excel
wb.save(save_path)
BuiltIn().log("Created the best path select matrix")
# ch19_29.py
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 建立空白的活頁簿
ws = wb.active # 獲得目前工作表
ws['A1'] = '台科大'
ws['B1'] = '明志科大'
ws['C1'] = '北科大'
ws.row_dimensions[1].height = 40 # 高度是40pt
ws.column_dimensions['B'].width = 20 # 寬度是20個英文字元寬
ws['A1'].alignment = Alignment(horizontal='left', vertical='top')
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws['C1'].alignment = Alignment(horizontal='right', vertical='bottom')
wb.save('out19_29.xlsx') # 將活頁簿儲存
def open_excel(self):
ws = openpyxl.Workbook(write_only=True)
return ws
xw.append((b + line_slopesL[k] * points[k]) / (line_slopesL[k] - s))
yw.append(s * xw[k] + b)
xp3 = [(radius_throat + line_slopesL[k] * points[k]) /
(line_slopesL[k] - line_slopesR[-1]), xw[k]]
yp3 = [
line_slopesR[-1] * (radius_throat + line_slopesL[k] * points[k]) /
(line_slopesL[k] - line_slopesR[-1]) + radius_throat, yw[k]
]
plt.plot(xp3, yp3, 'g')
xw.insert(0, 0)
yw.insert(0, radius_throat)
new_set = np.transpose([xw, yw])
new_set = new_set.tolist()
wb = op.Workbook()
ws = wb.active
for i in new_set:
ws.append(i)
wb.save(filename="nozzle_points.xlsx")
wb.close()
plt.plot(xw, yw)
radius_exit = max(yw)
print(yw[-1])
ar = (radius_throat / radius_exit)**2
print(radius_exit)
plt.show()