def draw_jpg(img_path):
img_pic = resize(Image.open(img_path))
img_name = os.path.basename(img_path)
out_file = img_name + '.xlsx'
if os.path.exists(out_file):
os.remove(out_file)
workbook = openpyxl.Workbook()
worksheet = workbook.active
width, height = img_pic.size
for w in range(1, width + 1):
for h in range(1, height + 1):
if img_pic.mode == 'RGB':
r, g, b = img_pic.getpixel((w - 1, h - 1))
elif img_pic.mode == 'RGBA':
r, g, b, a = img_pic.getpixel((w - 1, h - 1))
hex_rgb = int_to_16(r) + int_to_16(g) + int_to_16(b)
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
# 调整列宽
worksheet.column_dimensions[str(_w)].width = 1
# 调整行高
worksheet.row_dimensions[h].height = 6
cell.fill = fills.PatternFill(fill_type="solid", fgColor=hex_rgb)
print('write in:', w, ' | all:', width + 1)
print('saving...')
workbook.save(out_file)
print('success!')
def draw_cell():
workbook = openpyxl.Workbook()
worksheet = workbook.active
count = 0
max_cell = 10000
for w in range(1, max_cell + 1):
for h in range(1, max_cell + 1):
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
# 调整列宽
worksheet.column_dimensions[_w].width = 1
# 调整行高
worksheet.row_dimensions[h].height = 6
# if count < 255 ** 3:
# back = int_to_16(num=count)
# back = '0' * (6 - len(back)) + back
# else:
# back = ''.join([int_to_16(random.randint(0, 255)) for _ in range(3)])
cell.fill = fills.PatternFill(fill_type="solid", fgColor='333333')
count += 1
print('write in:', w, ' | all:', w + 1)
print('saving...')
workbook.save('test.xlsx')
print('success!')
def main():
File = input("Enter Image file name: ")
print('Processing Image ...')
count = time.time()
# Process Image in each pixel
img = Image.open(File)
img = img.rotate(0, expand=1)
width, height = img.size
ratio = width / height
# For some god damm reason, Excel can only display file below 1400kb without error
# I can only display on 310Col X 430Row in 50% magnification
# So image's width <= 310px
# image's height <= 430px
# Resize img
if (ratio < 1):
# width < height => Vertical image
resize_rate = height / 430
height = int(height // resize_rate)
width = int(ratio * height)
else:
# height < width => Horizontal image
resize_rate = width / 353
width = int(width // resize_rate)
height = int(width // ratio)
img = img.resize((width, height), Image.BICUBIC)
# EXCEL
wb = Workbook()
sheet = wb.active
for x in range(width):
for y in range(height):
sheet.row_dimensions[y + 2].height = 1
r, g, b = img.getpixel((x, y))
# Fill in 1 cell
color = hexcode(r, g, b)
sheet.cell(row=y + 2, column=x + 1).fill = fills.PatternFill(
fill_type='solid', start_color=color, end_color=color)
for col in sheet.columns:
column = col[0].column
sheet.column_dimensions[column].width = 0.15
wb.save('result.xlsx')
print('Finish in {:0.5f}s'.format(time.time() - count))
def draw_jpg(img_path):
img_pic = resize(Image.open(img_path))
img_name = os.path.basename(img_path)
out_file = './result/' + img_name.split('.')[0] + '.xlsx'
if os.path.exists(out_file):
os.remove(out_file)
workbook = openpyxl.Workbook()
worksheet = workbook.active
width, height = img_pic.size
for w in range(1, width + 1):
for h in range(1, height + 1):
if img_pic.mode == 'RGB':
r, g, b = img_pic.getpixel((w - 1, h - 1))
elif img_pic.mode == 'RGBA':
r, g, b, a = img_pic.getpixel((w - 1, h - 1))
hex_rgb = int_to_16(r) + int_to_16(g) + int_to_16(b)
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
# 调整列宽
# openpyxl > 2.6.1
# _w_letter = openpyxl.utils.get_column_letter(_w)
# worksheet.column_dimensions[_w_letter].width = 1
# openpyxl < 2.5.12
worksheet.column_dimensions[_w].width = 1
# 调整行高
worksheet.row_dimensions[h].height = 6
# back = random.choice(['112233','223344','334455','556677','667788','778899','ababcb'])
# back = ''.join([int_to_16(random.randint(0,16)) for _ in range(3) ])
# 颜色数量*单元格数量 超过一定数量后打开excel会报错
cell.fill = fills.PatternFill(fill_type="solid", fgColor=hex_rgb)
print('write in:', w, ' | all:', width + 1)
print('saving...')
workbook.save(out_file)
print('success!')
print('The file is saved in :',os.path.abspath('test.xlsx'))
def format_excel_sheet(report, ltp_file):
# Create a workbook
workbook = Workbook()
sheet = workbook.active
bold_font = Font(bold=True, color=colors.DARKYELLOW, size=20)
# set the width of the column
sheet.column_dimensions['A'].width = 30
sheet.column_dimensions['B'].width = 40
sheet.column_dimensions['C'].width = 10
sheet['A1'].font = bold_font
sheet.merge_cells('A1:D1')
sheet['A1'] = 'LTP Test report'
my_yellow = colors.Color('e6e600')
my_fill_yellow = fills.PatternFill(patternType='solid',
fgColor=my_yellow)
sheet['A3'] = 'Module'
sheet.cell(row=3, column=1).fill = my_fill_yellow
sheet['B3'] = 'Test Case'
sheet.cell(row=3, column=2).fill = my_fill_yellow
sheet['C3'] = 'Result'
sheet.cell(row=3, column=3).fill = my_fill_yellow
sheet['D3'] = 'Exit Code'
sheet.cell(row=3, column=4).fill = my_fill_yellow
Generator.append_data_into_cells(sheet, report)
filename_ltp = ltp_file.split('\\')
filename_ltp_no_ext = (filename_ltp[-1].split('.'))[0]
output_file = 'l4b-software___testReport' + '___' + filename_ltp_no_ext + '.xlsx'
try:
workbook.save(filename=output_file)
except PermissionError as e:
print("\n\n\n Excel file is open. Please close the excel file !!!")
def draw_color():
workbook = openpyxl.Workbook()
worksheet = workbook.active
count = 0
MAX_WIDTH = 255
for w in range(1, MAX_WIDTH + 1):
for h in range(1, MAX_WIDTH + 1):
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
# 调整列宽
worksheet.column_dimensions[_w].width = 1
# 调整行高
worksheet.row_dimensions[h].height = 6
if count < 255**3:
back = int_to_16(num=count)
back = '0' * (6 - len(back)) + back
else:
back = ''.join(
[int_to_16(random.randint(0, 255)) for _ in range(3)])
cell.fill = fills.PatternFill(fill_type="solid", fgColor=back)
count += 1
print('write in:', w, ' | all:', w + 1)
print('saving...')
workbook.save('test.xlsx')
print('success!')
print('The file is saved in :', os.path.abspath('test.xlsx'))
def append_data_into_cells(worksheet, report):
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in report._listModules:
worksheet.cell(row=current_row,
column=current_column).value = module._name
for tca in module._listTestCases:
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Conf'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
# Change bar filling and line color
# serie1= chart.series[0];
# serie1.graphicalProperties.solidFill = "7E3F00"
# serie2 = chart.series[1];
chart.style = 2
worksheet.add_chart(chart, "F3")
def CreateAndAppendClassLists(seperateCampOrderList, classListName):
print('Sorting Class Lists')
for fileName in os.listdir(os.path.dirname(os.path.abspath(__file__))):
if classListName in fileName:
if '.xlsx' not in fileName:
os.rename(fileName, fileName + '.xlsx')
fileName = fileName + '.xlsx'
seperatedNameOrderList = []
for order in seperateCampOrderList:
if len(order['lineItems'][0]['customizations'][0]['value'].split()) > 1 and len(
order['lineItems'][0]['customizations'][1]['value'].split()) > 1 and len(
order['lineItems'][0]['customizations'][2]['value'].split()) == 0:
seperatedNameOrderList.append(order)
elif len(order['lineItems'][0]['customizations'][0]['value'].split()) > 1 and len(
order['lineItems'][0]['customizations'][1]['value'].split()) == 0 and len(
order['lineItems'][0]['customizations'][2]['value'].split()) > 1:
seperatedNameOrderList.append(order)
elif len(order['lineItems'][0]['customizations'][0]['value'].split()) > 1 and len(
order['lineItems'][0]['customizations'][1]['value'].split()) > 1 and len(
order['lineItems'][0]['customizations'][2]['value'].split()) > 1:
seperatedNameOrderList.append(order)
seperatedNameOrderList.append(order)
seperatedNameOrderList.append(order)
prevOrderID = 0
thirdOrder = False
book = load_workbook(fileName)
writer = pd.ExcelWriter(fileName, engine='openpyxl')
writer.book = book
if 'Summary' not in book.sheetnames:
book.create_sheet("Summary")
book.save(fileName)
if 'Sheet' in book.sheetnames:
sheet = book.get_sheet_by_name('Sheet')
book.remove_sheet(sheet)
classDates = []
for order in seperatedNameOrderList:
if order['orderNumber'] == prevOrderID:
if thirdOrder:
order['lineItems'][0]['customizations'][0]['value'] = order['lineItems'][0]['customizations'][2]['value']
order['lineItems'][0]['customizations'][3]['value'] = order['lineItems'][0]['customizations'][5]['value']
thirdOrder = False
else:
order['lineItems'][0]['customizations'][0]['value'] = order['lineItems'][0]['customizations'][1]['value']
order['lineItems'][0]['customizations'][3]['value'] = order['lineItems'][0]['customizations'][4]['value']
thirdOrder = True
else:
thirdOrder = False
prevOrderID = order['orderNumber']
time = order['lineItems'][0]['variantOptions'][0]['value'].split(',')[1].replace(' ', '')[0:5].replace(':', '')
if 'Tech Club' in fileName:
venue = order['lineItems'][0]['productName'].split('- ')[2].split(',')[0].split()[0]
else:
if 'Synge' in order['lineItems'][0]['productName']:
venue = order['lineItems'][0]['productName'].split('- ')[1].split(',')[0].replace(' ','')
else:
venue = order['lineItems'][0]['productName'].split('- ')[1].split(',')[0].split()[0]
if 'Summer' in fileName or 'Easter' in fileName:
day = order['lineItems'][0]['variantOptions'][0]['value'].split(',')[0].split()[1].split('-')[0]
month = order['lineItems'][0]['variantOptions'][0]['value'].split(',')[0].split()[0][0:3]
startDate = datetime.strptime(order['lineItems'][0]['variantOptions'][0]['value'].split(',')[0].split()[1].split('-')[0] + ' ' + order['lineItems'][0]['variantOptions'][0]['value'].split(',')[0].split()[0], '%d %B')
numClasses = 5
sheetName = venue + '_' + day + '_' + month + '_' + time
elif 'Autumn' in fileName:
day = order['lineItems'][0]['variantOptions'][0]['value'][0:3]
startDate = ''
numClasses = 12
sheetName = day + '_' + venue + '_' + time
elif 'Spring' in fileName:
day = order['lineItems'][0]['variantOptions'][0]['value'][0:3]
startDate = ''
numClasses = 18
sheetName = day + '_' + venue + '_' + time
for i in range(0, numClasses):
classDates.append((startDate + timedelta(days=i)).strftime('%d %B'))
if sheetName not in book.sheetnames:
writer = pd.ExcelWriter(fileName, engine='openpyxl')
writer.book = book
dataGapSize = 40
classListTemplateLines = [
['Attendence'],
[''],
['Total No. Of Students'],
[''],
['Date'],
['Student Name', 'Tutor'],
['Week ' + str(classNum + 1) for classNum in range(numClasses)],
[' ' for gap in range(dataGapSize - 1)],
['Gender',
'Order ID',
'Email',
'Billing Name',
'Phone',
'Student Name(s)',
'Student Date(s) of Birth',
'Student\'s School and Class',
'Are you a returning Academy of Code student?',
'If you are a returning student, when was your last term with AoC?',
'Additional support for your child',
'Photography Consent',
'Other Details',
'Other Teacher Notes']
]
classListTemplateLines[5] = classListTemplateLines[5] + classListTemplateLines[6] + classListTemplateLines[7] + classListTemplateLines[8]
for i in range(0,3):
del classListTemplateLines[6]
classListTemplateLines[4] = classListTemplateLines[4]+ [''] + classDates
templateDataFrame = pd.DataFrame(data=classListTemplateLines)
templateDataFrame.to_excel(writer, sheet_name=sheetName, header=False, index=False)
writer.save()
if len(order['lineItems'][0]['customizations'][0]['value'].split()) == 1 and len(order['lineItems'][0]['customizations'][1]['value'].split()) == 1:
if len(order['lineItems'][0]['customizations'][2]['value'].split()) == 1:
d = [[order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['orderNumber'],
order['customerEmail'],
order['billingAddress']['firstName'] + ' ' +
order['billingAddress']['lastName'],
order['billingAddress']['phone'],
order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['lineItems'][0]['customizations'][3]['value'],
order['lineItems'][0]['customizations'][6]['value'],
order['lineItems'][0]['customizations'][7]['value'],
order['lineItems'][0]['customizations'][8]['value'],
order['lineItems'][0]['customizations'][10]['value'],
order['lineItems'][0]['customizations'][11]['value'],
'']
]
else:
d = [[order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'],
order['orderNumber'],
order['customerEmail'],
order['billingAddress']['firstName'] + ' ' +
order['billingAddress']['lastName'],
order['billingAddress']['phone'],
order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'],
order['lineItems'][0]['customizations'][3]['value'],
order['lineItems'][0]['customizations'][6]['value'],
order['lineItems'][0]['customizations'][7]['value'],
order['lineItems'][0]['customizations'][8]['value'],
order['lineItems'][0]['customizations'][10]['value'],
order['lineItems'][0]['customizations'][11]['value'],
'']
]
elif len(order['lineItems'][0]['customizations'][0]['value'].split()) == 1 and len(order['lineItems'][0]['customizations'][2]['value'].split()) == 1:
if len(order['lineItems'][0]['customizations'][1]['value'].split()) == 1:
d = [[order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['orderNumber'],
order['customerEmail'],
order['billingAddress']['firstName'] + ' ' +
order['billingAddress']['lastName'],
order['billingAddress']['phone'],
order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][1]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['lineItems'][0]['customizations'][3]['value'],
order['lineItems'][0]['customizations'][6]['value'],
order['lineItems'][0]['customizations'][7]['value'],
order['lineItems'][0]['customizations'][8]['value'],
order['lineItems'][0]['customizations'][10]['value'],
order['lineItems'][0]['customizations'][11]['value'],
'']
]
else:
d = [[order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['orderNumber'],
order['customerEmail'],
order['billingAddress']['firstName'] + ' ' +
order['billingAddress']['lastName'],
order['billingAddress']['phone'],
order['lineItems'][0]['customizations'][0]['value'] + ' ' +
order['lineItems'][0]['customizations'][2]['value'],
order['lineItems'][0]['customizations'][3]['value'],
order['lineItems'][0]['customizations'][6]['value'],
order['lineItems'][0]['customizations'][7]['value'],
order['lineItems'][0]['customizations'][8]['value'],
order['lineItems'][0]['customizations'][10]['value'],
order['lineItems'][0]['customizations'][11]['value'],
'']
]
else:
d = [[order['lineItems'][0]['customizations'][0]['value'],
order['orderNumber'],
order['customerEmail'],
order['billingAddress']['firstName'] + ' ' +
order['billingAddress']['lastName'],
order['billingAddress']['phone'],
order['lineItems'][0]['customizations'][0]['value'],
order['lineItems'][0]['customizations'][3]['value'],
order['lineItems'][0]['customizations'][6]['value'],
order['lineItems'][0]['customizations'][7]['value'],
order['lineItems'][0]['customizations'][8]['value'],
order['lineItems'][0]['customizations'][10]['value'],
order['lineItems'][0]['customizations'][11]['value'],
'']
]
book.save(fileName)
book = load_workbook(fileName)
maxCol = book.get_sheet_by_name(sheetName).max_column
for k in range(maxCol-len(d[0])-1):
d[0].insert(1, '')
orderDataFrame = pd.DataFrame(data=d)
AppendDfToExcel(fileName, orderDataFrame, sheetName, book)
totalStudentsCell = book[sheetName].cell(row=3, column=2)
totalStudentsCell.value = book[sheetName].max_row - 6
for i in range(1, len(classDates) + 3):
attendanceCell = book[sheetName].cell(row=1, column=i)
if i > 2:
attendanceCell.value = '=COUNTIF(' + get_column_letter(i) + '7:' + get_column_letter(i) + str(book[sheetName].max_row) + ',"Y")'
attendanceCell.fill = fills.PatternFill(patternType='solid', fgColor=Color(rgb='00FFF2CC'))
for row in range(1, 7):
for col in range(1, book[sheetName].max_column + 1):
colLetter = get_column_letter(col)
cell = book[sheetName][colLetter + str(row)]
cell.font = Font(bold=True)
classDates = []
writer.save()
book.save(fileName)
SortWorkSheets(fileName)
def append_data_into_cells(worksheet):
"""append data into the worksheet"""
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in Generator.report_csv._listModules:
for tca in module._listTestCases:
worksheet.cell(row=current_row,
column=current_column).value = tca._module_git
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 3
worksheet.cell(
row=current_row, column=current_column
).value = f"=VLOOKUP(A{current_row},'ES6 - LTP Test Results'!A:B,2)"
current_column += 1
worksheet.cell(
row=current_row, column=current_column
).value = f'=IF(AND(C{current_row}<>F{current_row},F{current_row}<>"N/A"),"Different","OK")'
# current_column += 1
# worksheet.cell(row=current_row, column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Skipped'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
# worksheet.auto_filter.ref = 'A5:C1766'
# worksheet.auto_filter.add_sort_condition('A{0}:A{1}'.format(5, 1766))
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
chart.style = 10
worksheet.add_chart(chart, "I8")
def fusionner_classeur(filename):
"""Fusionne les données du classeur Excel (document .xlsx).
La première feuille du tableur doit contenir des noms sur la colonne A.
Chaque autre feuille doit contenir :
- des noms sur la colonne A,
- des notes sur la colonne B.
Ces colonnes ne doivent pas avoir d'entête.
Les autres colonnes sont ignorées.
S'il y a un espace dans la colonne des noms, la lecture des noms s'interrompt.
Une feuille est générée en fin de document, et contient le résultat de la fusion.
"""
if not filename.endswith('.xlsx'):
raise RuntimeError(f"File {filename} does not seem to be a .xlsx file.")
spreadsheet = load_workbook(filename)
scores_nb = []
for num, sheet in enumerate(spreadsheet, start=1):
print(f'Reading {sheet.title!r} sheet...')
# Guess format: one column for name and surname, or two distinct columns.
name_has_2_cols = (isinstance(sheet['B1'].value, str) and sheet['B1'].value != '')
# Detect the table height.
for height, cell in enumerate(sheet['A']):
val = cell.value
if not isinstance(val, str) or val.strip() == '':
break
print(' -', height, 'lines')
if name_has_2_cols:
names = [f'{a[0].value} {b[0].value}' for a, b in zip(sheet[f'A1:A{height}'],
sheet[f'B1:B{height}'])]
else:
names = [cell[0].value for cell in sheet[f'A1:A{height}']]
if num == 1:
fusion = Fusion(names)
# No scores on 1st sheet
continue
scores = []
for j, col in enumerate(sheet.iter_cols(), start=1):
if j == 1 or (j == 2 and name_has_2_cols):
# Skip names columns
continue
# Collect scores in the current column.
vals = [cell.value for cell in col[:height]]
#If a column is empty, disgard all following columns.
if all(val is None for val in vals):
break
scores.append(vals)
print(' -', len(scores), 'column(s) of scores')
scores_nb.append(len(scores))
data = {}
for student, student_scores in zip(names, zip(*scores)):
data[student] = student_scores
fusion.importer(data)
new = spreadsheet.create_sheet('Fusion')
for i, name in enumerate(sorted(fusion.names), start=1):
new[f'A{i}'] = name
# Format for cells that need special attention.
my_red = colors.Color(rgb='00FF1111')
my_fill = fills.PatternFill(patternType='solid', fgColor=my_red)
# Calcul des positions de chaque série de données dans le tableur
# On mémorise une fois pour toute la position de la 1re colonne correspondant
# à chaque série de données, pour éviter de la recalculer ensuite.
positions = []
pos = 2
for n in range(len(fusion.imported)):
positions.append(pos)
pos += scores_nb[n] + 1
# Fusions réussies
for i, name in enumerate(sorted(fusion.names), start=1):
for n, found in enumerate(fusion.imported):
j = positions[n]
if found[name] is not None:
old_name, scores, fiability = found[name]
new.cell(i, j).value = old_name
if fiability >= 2:
new.cell(i, j).fill = my_fill
for k, score in enumerate(scores, start=1):
new.cell(i, j + k).value = score
if fiability >= 2:
new.cell(i, j + k).fill = my_fill
# Éléments pour lesquels la fusion n'a pas fonctionné
i0 = len(fusion.names) + 2
all_merged = True
for n, remaining in enumerate(fusion.not_imported):
j = positions[n]
for i, (old_name, scores) in enumerate(sorted(remaining.items()), start=i0):
all_merged = False
new.cell(i, j).value = old_name
new.cell(i, j).fill = my_fill
for k, score in enumerate(scores, start=1):
new.cell(i, j + k).value = score
new.cell(i, j + k).fill = my_fill
if not all_merged:
new.cell(i0, 1).value = \
"Attention, certaines données n'ont pas pu être fusionnées :"
my_font = fonts.Font(color=my_red, bold=True, italic=True)
new.cell(i0, 1).font = my_font
for i, _ in enumerate(new.iter_cols()):
new.column_dimensions[get_column_letter(i+1)].width = 25
spreadsheet.active = len(spreadsheet.sheetnames) - 1
assert '.' in filename
base, ext = filename.split('.')
spreadsheet.save(f'{base}_output.{ext}')