import xlrd
import xlwt
from pathlib import Path, PurePath

'''
WARNING:
xlrd: 1.2.0 version support .xls and .xlsx, upper version only support .xls
xlwt: only support .xls
'''
src_file = 'D:/productivity/01/data'
dst_file = 'D:/productivity/01/result.xls'

# extract target data from all src xlsx files
p = Path(src_file)
files = [x for x in p.iterdir() if PurePath(x).match('*.xlsx')]

content = []

for file in files:
    username = file.stem
    data   = xlrd.open_workbook(file)
    sheet  = data.sheets()[0]
    answer0 = sheet.cell_value(rowx=4, colx=4) # row/col index counted from 0
    answer1 = sheet.cell_value(rowx=10, colx=4)

    temp = f'{username},{answer0},{answer1}'
    content.append(temp.split(',')) # this temp text has changed to a list after use split()
    print(temp)

# save target data as result to dst_file
sheet_header = ['name', 'question1', 'question2']
work_book    = xlwt.Workbook(encoding='utf-8')
result_sheet = work_book.add_sheet('result')

row = 0
col = 0
# sheet header
for cell_header in sheet_header:
    result_sheet.write(row, col, cell_header)
    col += 1

# sheet result data
row += 1
for line in content:
    col = 0
    for cell in line:
        result_sheet.write(row, col, cell)
        col += 1
    row += 1

work_book.save(dst_file)