def _write_data(sheet: xlsxwriter.worksheet.Worksheet, row: int, col: int,
data) -> int:
if isinstance(data, datetime.datetime):
data_str = data.strftime('%Y-%m-%d %H:%M:%S')
sheet.write(row, col, data_str)
elif data is not None:
sheet.write(row, col, data)
return col + 1
def _export_header(sheet: xlsxwriter.worksheet.Worksheet, row: int):
col = 0
sheet.write(row, col, '关键词')
col += 1
sheet.write(row, col, '话题')
col += 1
sheet.write(row, col, '时间')
col += 1
sheet.write(row, col, '阅读量')
col += 1
sheet.write(row, col, '讨论量')
return row + 1
def _export_item(sheet: xlsxwriter.worksheet.Worksheet, row: int, item: WeiboTopicDetailItem) -> int:
col = 0
sheet.write(row, col, item.keyword)
col += 1
sheet.write(row, col, item.title)
col += 1
public_str = _get_date_str(item.date)
sheet.write(row, col, public_str)
col += 1
sheet.write(row, col, item.read_count)
col += 1
sheet.write(row, col, item.discus_count)
return row + 1
def give_info_in_table(worksheet: xlsxwriter.worksheet.Worksheet, day_n):
"""Заносим информацию в лист таблицы"""
data = cur.execute(
'''SELECT * FROM events WHERE day = ? ORDER BY start_hour''',
(day_n, )).fetchall()
row = 0
for col, item in enumerate(['с', 'по', 'название', 'описание']):
worksheet.write(row, col, item)
for row, items in enumerate(data):
# беру часы и минуты начала и конца события и привожу их к правильному виду
st_h, st_m, end_h, end_m = [str(i).rjust(2, '0') for i in items[2:-2]]
start, end = f'{st_h}:{st_m}', f'{end_h}:{end_m}'
items = [start, end] + list(items[-2:])
for col, item in enumerate(items):
worksheet.write(row + 1, col, item)
def _export_header(sheet: xlsxwriter.worksheet.Worksheet, row: int):
col = 0
sheet.write(row, col, '关键词')
col += 1
sheet.write(row, col, '发表时间')
col += 1
sheet.write(row, col, '来源网站')
col += 1
sheet.write(row, col, '报道标题')
def _export_item(sheet: xlsxwriter.worksheet.Worksheet, row: int,
site_name: str, item) -> int:
col = 0
sheet.write(row, col, item.keyword)
col += 1
public_str = _get_date_str(item.publish)
sheet.write(row, col, public_str)
col += 1
sheet.write(row, col, site_name)
col += 1
sheet.write(row, col, item.title)
return row + 1
def _write_row(sheet: xlsxwriter.worksheet.Worksheet, row: int, key: str,
data: dict):
sheet.write(row, 0, key)
for idx, keyword in enumerate(KEYWORDS, 1):
sheet.write(row, idx, data[keyword])
def _populate_worksheet(self, workbook: xlsxwriter.workbook.Workbook,
worksheet: xlsxwriter.worksheet.Worksheet,
data: dict[str, Any]) -> None:
"""Populates a worksheet with the info.
Args:
workbook (xlsxwriter.workbook.Workbook): Workbook to which
the worksheet belongs to.
worksheet (xlsxwriter.worksheet.Worksheet): Worksheet to
write into.
data (dict): data to populate the worksheet cells with.
"""
worksheet_format_bold = workbook.add_format({'bold': True})
# worksheet_format_currency = workbook.add_format(
# {'num_format': f'{self.__symbol}# ##0;-{self.__symbol}# ##0'})
worksheet_format_default = workbook.add_format({
'align': 'left',
'valign': 'vcenter'
})
worksheet_format_link = workbook.add_format({
'font_color': 'blue',
'align': 'left',
'valign': 'vcenter'
})
worksheet_format_note = workbook.add_format({
'text_wrap': True,
'align': 'justify',
'valign': 'vcenter'
})
worksheet_rows = [
'Band', 'Year', 'Album', 'Cat Number', 'Format', 'Format Quantity',
'Styles', 'Have', 'Want', 'Notes', 'For Sale', 'Lowest Price',
'Mint (M)', 'Near Mint (NM or M-)', 'Very Good Plus (VG+)',
'Very Good (VG)', 'Good Plus (G+)', 'Good (G)'
]
worksheet.set_row(0, 25)
worksheet.write_row(0, 0, worksheet_rows, worksheet_format_bold)
worksheet.freeze_panes(1, 0)
worksheet_row = 1
for artist in Bar('Writing ').iter(sorted(data.keys())):
for release in data[artist].keys():
worksheet.set_row(worksheet_row, 25)
worksheet.write_string(worksheet_row,
0,
artist,
cell_format=worksheet_format_default)
worksheet.write_number(worksheet_row,
1,
data[artist][release]['year'],
cell_format=worksheet_format_default)
worksheet.write_string(worksheet_row,
3,
data[artist][release]['catno'],
cell_format=worksheet_format_default)
worksheet.write_string(worksheet_row,
4,
data[artist][release]['format'],
cell_format=worksheet_format_default)
worksheet.write_string(worksheet_row,
5,
data[artist][release]['quantity'],
cell_format=worksheet_format_default)
worksheet.write_string(worksheet_row,
6,
data[artist][release]['styles'],
cell_format=worksheet_format_default)
if data[artist][release].get('uri', None):
worksheet.write_url(worksheet_row,
2,
data[artist][release]['uri'],
string=data[artist][release]['album'],
cell_format=worksheet_format_link)
worksheet.write_number(
worksheet_row,
7,
data[artist][release]['have'],
cell_format=worksheet_format_default)
worksheet.write_number(
worksheet_row,
8,
data[artist][release]['want'],
cell_format=worksheet_format_default)
worksheet.write_string(worksheet_row,
9,
data[artist][release]['notes'],
cell_format=worksheet_format_note)
worksheet.write_number(
worksheet_row,
10,
data[artist][release]['num_for_sale'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
11,
data[artist][release]['lowest_price'],
cell_format=worksheet_format_default)
if data[artist][release].get('prices', None):
worksheet.write_string(
worksheet_row,
12,
data[artist][release]['prices']['mint'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
13,
data[artist][release]['prices']['near_mint'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
14,
data[artist][release]['prices']['very_good_plus'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
15,
data[artist][release]['prices']['very_good'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
16,
data[artist][release]['prices']['good_plus'],
cell_format=worksheet_format_default)
worksheet.write_string(
worksheet_row,
17,
data[artist][release]['prices']['good'],
cell_format=worksheet_format_default)
else:
worksheet.write_string(
worksheet_row,
2,
data[artist][release]['album'],
cell_format=worksheet_format_default)
worksheet_row += 1
if self._logger:
self._logger.info('Data saved.')
def _export_weibo_title(sheet: xlsxwriter.worksheet.Worksheet):
sheet.write(0, 0, '所属话题')
sheet.write(0, 1, '爬取时间')
sheet.write(0, 2, '用户名')
sheet.write(0, 3, '内容')
sheet.write(0, 4, '发表时间')
sheet.write(0, 5, '转发数')
sheet.write(0, 6, '评论数')
sheet.write(0, 7, '点赞数')
def _export_topic_title(sheet: xlsxwriter.worksheet.Worksheet):
sheet.write(0, 0, '关键字')
sheet.write(0, 1, '话题')
sheet.write(0, 2, '爬取时间')
sheet.write(0, 3, '阅读量')
sheet.write(0, 4, '讨论量')
def _export_task_3_title(sheet: xlsxwriter.worksheet.Worksheet):
sheet.write(0, 0, '关键字')
sheet.write(0, 1, '爬取时间')
sheet.write(0, 2, '微博链接')
sheet.write(0, 3, '转发数')
sheet.write(0, 4, '评论数')
sheet.write(0, 5, '点赞数')
sheet.write(0, 6, '标题')
sheet.write(0, 7, '内容')
sheet.write(0, 8, '用户名')
sheet.write(0, 9, '发表时间')
def populateWorkbook(sheet: xlsxwriter.worksheet.Worksheet):
sheet.set_column(0, 0, 15)
for i in range(1, testsCount + 1):
sheet.write(i + 1, 0, f"Experiment {i}")
sheet.write(meanRow, 0, "Mean")
sheet.write(deviationRow, 0, "Deviation")
sheet.write(medianRow, 0, "Median")
referenceCol = 1
for i in range(len(fn.functionsMap)):
key = list(fn.functionsMap.keys())[i]
function = fn.functionsMap[key]
function.maxEval = maxEvals
function.curEval = 0
function.clearDict()
algorithms = getAlgos(function)
sheet.merge_range(0, referenceCol, 0,
referenceCol + len(algorithms) - 1, key)
for j in range(len(algorithms)):
algoKey = list(algorithms.keys())[j]
alg = algorithms[algoKey]
sheet.write(1, referenceCol + j, algoKey)
fitnessResults = []
for testIdx in range(testsCount):
try:
alg.reset()
alg.solve(None, None, maxEvals * 2)
except AssertionError as err:
print(f"Assertion failed: {sys.exc_info()}")
print(f"Current algo: {algoKey}")
raise
except:
# Exception is thrown, when max evals is exceeded. This is used to stop algorithm immediately
pass
# sheet.write(2 + testIdx, referenceCol + j, testIdx)
sheet.write(2 + testIdx, referenceCol + j, alg.fitness)
fitnessResults.append(alg.fitness)
sheet.write(meanRow, referenceCol + j,
statistics.mean(fitnessResults))
sheet.write(deviationRow, referenceCol + j,
statistics.stdev(fitnessResults))
sheet.write(medianRow, referenceCol + j,
statistics.median(fitnessResults))
referenceCol += len(algorithms)
def add_to_worksheet(sheet: xlsxwriter.worksheet.Worksheet, row: int, col: int,
val: str) -> None:
sheet.write(row, col, val)