def format_combined_sheet(self):
# GC-MS
ws = self.wb["GC-MS"]
number_format_list = make_column_property_dict(
{
'1': '0.000',
'2': '0.0',
'3': '0',
'5': '0',
'6': '0'
},
repeat=self.n_experiments,
length=9)
width_dict = make_column_property_dict(
{
"1": 14,
"2": 12,
"4": 0,
"5": 9,
"6": 9,
"8": 15,
"9": 10
},
repeat=self.n_experiments,
length=9)
alignment_list = make_column_property_dict({
"5": "center",
"6": "center",
"8": "center"
})
format_sheet(ws, number_format_list, width_dict, alignment_list)
for offset in range(self.n_experiments):
merge_string = get_column_letter(1 + (
9 * offset)) + '1:' + get_column_letter(9 + (9 * offset)) + '1'
ws.merge_cells(merge_string)
format_header(
ws,
make_column_property_dict(
{
"1": "center",
"2": "center",
"5": "center",
"6": "center",
"8": "center",
"9": "center"
},
repeat=self.n_experiments,
length=9), 1, 2)
def comparison_format(input_file): # Formatting for Final Output
print('\nGenerating XLSX Output...')
"""From http://openpyxl.readthedocs.io/en/default/"""
wb = load_workbook(input_file)
# grab the active worksheet
ws = wb["Comparison"]
# ws.cell(column=1, row=1).value = u"Threshold α={}".format(a_value)
number_format_list = {
'E': '0.000',
'F': '0.000000',
'G': '0.00%',
'I': '0.00',
'J': '0.00',
'K': '0.00%', # left Sample
'M': '0.000',
'N': '0.000000',
'O': '0.00%',
'Q': '0.00',
'R': '0.00',
'S': '0.00%', # right sample
'U': '0.000000',
'V': '0.000000', # RT t-test
'Y': '0.000000',
'Z': '0.000000', # Area t-test
'AC': '0.000000',
'AD': '0.000000', # Area Welch's t-test
'AG': '0.0',
'AH': '0.0000',
'AI': '0.00%', # MS Comparison
}
spacer_width = 1
width_list = {
'B': 12,
'C': spacer_width * 2,
'D': 0, # Name and CAS
'E': 8,
'F': 11,
'G': 7,
'H': spacer_width,
'I': 15,
'J': 14,
'K': 8,
'L': spacer_width * 2, # left Sample
'M': 8,
'N': 11,
'O': 7,
'P': spacer_width,
'Q': 15,
'R': 14,
'S': 8,
'T': spacer_width * 2, # right Sample
'U': 11,
'V': 10,
'W': 7,
'X': spacer_width, # RT t-test
'Y': 11,
'Z': 10,
'AA': 7,
'AB': spacer_width, # Area t-test
'AC': 11,
'AD': 10,
'AE': 7,
'AF': spacer_width, # Area Welch's t-test
'AG': 8,
'AH': 10,
'AI': 8, # MS Comparison
}
alignment_list = make_column_property_dict({'5': 'right'}, {'B': 'center'},
repeat=32,
length=1)
for column in ['W', 'AA', 'AE']:
alignment_list[column] = 'center'
format_sheet(ws, number_format_list, width_list, alignment_list) #
"""Header"""
# Row 1
ws.merge_cells("E1:K1")
ws.merge_cells("M1:S1")
ws.merge_cells('U1:AE1')
# Row 2
for offset in range(7):
merge_string = get_column_letter(5 + (
4 * offset)) + '2:' + get_column_letter(7 + (4 * offset)) + '2'
# print(merge_string)
ws.merge_cells(merge_string)
# MS Comparisons
ws.cell(column=33, row=1).value = "MS Comparisons"
ws.merge_cells('AG1:AI2')
# Centering text
format_header(
ws, make_column_property_dict({"2": "center"}, repeat=35, length=1), 1,
2)
# Save the file
wb.save(input_file)
return
def format_xlsx(self, input_file):
"""
Formatting for Combined Output
:param input_file: xlsx file to format
:type input_file: str
"""
print('\nGenerating XLSX Output...')
# From http://openpyxl.readthedocs.io/en/default/
wb = load_workbook(input_file)
"""GC-MS"""
ws = wb["GC-MS"]
number_format_list = make_column_property_dict(
{
'1': '0.000',
'2': '0.0',
'3': '0',
'5': '0',
'6': '0'
},
repeat=self.PL_len,
length=9)
width_dict = make_column_property_dict(
{
"1": 14,
"2": 12,
"4": 0,
"5": 9,
"6": 9,
"8": 15,
"9": 10
},
repeat=self.PL_len,
length=9)
alignment_list = make_column_property_dict({
"5": "center",
"6": "center",
"8": "center"
})
format_sheet(ws, number_format_list, width_dict, alignment_list)
for offset in range(self.PL_len):
merge_string = get_column_letter(1 + (
9 * offset)) + '1:' + get_column_letter(9 + (9 * offset)) + '1'
ws.merge_cells(merge_string)
format_header(
ws,
make_column_property_dict(
{
"1": "center",
"2": "center",
"5": "center",
"6": "center",
"8": "center",
"9": "center"
},
repeat=self.PL_len,
length=9), 1, 2)
"""Matches"""
ws = wb["Matches"]
header_number_format_dict = make_column_property_dict(
{
'5': '0.000',
'6': '0.00'
}, {
"A": "0.000",
"B": "0.00"
}, {
"17": "0.000",
"18": '0.000000',
"19": '0.00%',
"21": "0.00",
"22": "0.00",
"23": '0.00%'
},
repeat=self.PL_len,
length=3)
header_h_alignment_list = make_column_property_dict(
{
'5': "right",
'6': "right",
}, {
"A": "right",
"B": "right"
}, {
"17": "right",
"18": "right",
"19": "right",
"21": "right",
"22": "right",
"23": "right"
},
repeat=self.PL_len,
length=3)
# header_v_alignment_list = make_column_property_dict(
# {'5': "center", '6': "center"},
# {"A": "center", "B": "center", "AF": "center", "AG": "center", "AH": "center", "AJ": "center",
# "AK": "center", "AL": "center"})
hits_number_format_dict = make_column_property_dict(
{
'4': '0',
'5': '0',
'6': '0'
}, {"C": "0"}, {
"5": "0.0",
"6": "0.0000",
"7": "0.00%",
"9": "0.0",
"10": "0.0000",
"11": "0.00%",
"13": "0.0",
"14": "0.0000",
"15": "0.00%"
},
repeat=self.PL_len,
length=3)
hits_alignment_dict = make_column_property_dict(
{
'4': 'center',
'5': 'center',
'6': 'center'
}, {
"B": "center",
"C": "center"
}, {"13": "center"},
repeat=self.PL_len,
length=3)
width_dict = make_column_property_dict({
"4": 8,
"5": 8,
"6": 11
}, {"B": 12}, {
"5": 6,
"6": 9,
"7": 9,
"9": 6,
"10": 9,
"11": 9,
"13": 5,
"14": 7,
"15": 9,
"17": 8,
"18": 9,
"19": 9,
"21": 14,
"22": 12,
"23": 9,
"4": 1,
"8": 1,
"12": 1,
"16": 1,
"20": 1
},
repeat=self.PL_len,
length=3)
format_matches(ws, header_number_format_dict, header_h_alignment_list,
hits_number_format_dict, hits_alignment_dict,
width_dict)
format_header(ws,
make_column_property_dict({"4": "center"},
repeat=self.PL_len,
length=3)) # First Row
format_header(ws,
make_column_property_dict(
{"2": "center"}, {"A": "right"},
repeat=((self.PL_len * 3) + 25),
length=1), 2, 2) # Second Row
format_header(ws,
make_column_property_dict({"2": "center"},
repeat=((self.PL_len * 3) +
25),
length=1), 3, 3) # Third Row
for offset in range(self.PL_len):
merge_string = get_column_letter(4 + (
3 * offset)) + '1:' + get_column_letter(6 + (3 * offset)) + '1'
ws.merge_cells(merge_string)
offset = self.PL_len * 3
for index in [5, 9, 13, 17, 21]:
merge_string = get_column_letter(
index + offset) + '2:' + get_column_letter(index + 2 +
offset) + '2'
ws.merge_cells(merge_string)
"""Statistics"""
number_format_list = {
'C': '0',
'E': '0.000',
'F': '0.000000',
'G': '0.00%',
'I': '0.00',
'J': '0.00',
'K': '0.00%',
'M': '0.0',
'N': '0.0000',
'O': '0.00%',
'Q': '0.0',
'R': '0.0000',
'S': '0.00%',
'U': '0.0',
'V': '0.0000',
'W': '0.00%',
'Y': '0.0',
'Z': '0.000',
'AA': '0.000%'
}
width_dict = {
"B": 12,
"C": 2,
"D": 1,
"E": 8,
"F": 11,
"G": 8,
"H": 1,
"I": 13,
"J": 12,
"K": 10,
"L": 1,
"M": 8,
"N": 10,
"O": 10,
"P": 1,
"Q": 8,
"R": 10,
"S": 10,
"T": 1,
"U": 6,
"V": 8,
"W": 10,
"X": 1,
"Y": 8,
"Z": 9,
"AA": 9
}
alignment_list = {"B": "center", "C": "center"}
for sheet in [
"Statistics_Full", "Statistics", "Statistics - Lit Only"
]:
ws = wb[sheet]
format_sheet(ws, number_format_list, width_dict, alignment_list)
for offset in range(6):
ws.merge_cells(
get_column_letter(5 + (4 * offset)) + '1:' +
get_column_letter(7 + (4 * offset)) + '1')
format_header(
ws,
make_column_property_dict({"2": "center"}, repeat=27,
length=1), 1, 2)
"""Contents Page"""
ws = wb.create_sheet("Index", 0)
contents = [
["GC-MS", "Combined GC-MS data aligned by retention time.", True],
[
"Matches",
"List of possible matching compounds for each retention time, based on all samples.",
True
],
[
"Statistics_Full",
"Statistics for the top hit for each retention time.", True
],
[
"Statistics",
"Statistics for the top hit for each retention time, filtered by the occurrence of the compound\
in the hits, and the mass spectral similarity between samples.", True
],
[
"Statistics - Lit Only",
"As above, but only for compounds reported in literature as being present in propellant or GSR.",
True
],
]
print("\nThe worksheets in the output xlsx file are as follows:")
ws.append(["GunShotMatch Version {} Output".format(__version__)])
for row in contents:
print(row[0] + " " * (24 - len(row[0])) + row[1])
if row[2]:
ws.append([
"", '=HYPERLINK("#\'{0}\'!A1","{0}")'.format(row[0]),
row[1]
])
else:
ws.append(["", "{0}".format(row[0]), row[1]])
br()
ws.column_dimensions["B"].width = 50.0
for row in ws.iter_rows(min_row=2,
max_row=len(contents) + 1,
min_col=2,
max_col=2):
for cell in row:
if cell.value:
if cell.value.startswith("=HYPERLINK"):
cell.font = Font(color="0000EE", underline="single")
# ws.cell(column=4, row=2).value = "Sample list:"
# for index, prefix in enumerate(prefixList):
# ws.cell(row=2, column=(5+index)).value = prefix
# Save the file
wb.save(input_file)
return
def format_matches_sheet(self):
ws = self.wb["Matches"]
header_number_format_dict = make_column_property_dict(
{
'5': '0.000',
'6': '0.00'
}, {
"A": "0.000",
"B": "0.00"
}, {
"17": "0.000",
"18": '0.000000',
"19": '0.00%',
"21": "0.00",
"22": "0.00",
"23": '0.00%'
},
repeat=self.n_experiments,
length=3)
header_h_alignment_list = make_column_property_dict(
{
'5': "right",
'6': "right",
}, {
"A": "right",
"B": "right"
}, {
"17": "right",
"18": "right",
"19": "right",
"21": "right",
"22": "right",
"23": "right"
},
repeat=self.n_experiments,
length=3)
# header_v_alignment_list = make_column_property_dict(
# {'5': "center", '6': "center"},
# {"A": "center", "B": "center", "AF": "center", "AG": "center", "AH": "center", "AJ": "center",
# "AK": "center", "AL": "center"})
hits_number_format_dict = make_column_property_dict(
{
'4': '0',
'5': '0',
'6': '0'
}, {"C": "0"}, {
"5": "0.0",
"6": "0.0000",
"7": "0.00%",
"9": "0.0",
"10": "0.0000",
"11": "0.00%",
"13": "0.0",
"14": "0.0000",
"15": "0.00%"
},
repeat=self.n_experiments,
length=3)
hits_alignment_dict = make_column_property_dict(
{
'4': 'center',
'5': 'center',
'6': 'center'
}, {
"B": "center",
"C": "center"
}, {"13": "center"},
repeat=self.n_experiments,
length=3)
width_dict = make_column_property_dict({
"4": 8,
"5": 8,
"6": 11
}, {"B": 12}, {
"5": 6,
"6": 9,
"7": 9,
"9": 6,
"10": 9,
"11": 9,
"13": 5,
"14": 7,
"15": 9,
"17": 8,
"18": 9,
"19": 9,
"21": 14,
"22": 12,
"23": 9,
"4": 1,
"8": 1,
"12": 1,
"16": 1,
"20": 1
},
repeat=self.n_experiments,
length=3)
for row in ws.iter_rows(min_row=1,
max_row=ws.max_row,
min_col=1,
max_col=ws.max_column):
for cell in row:
cell.alignment = Alignment(vertical="center", wrap_text=False)
headers = [x + 1 for x in range(ws.max_row)][3::6]
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[get_column_letter(
column_cells[0].column)].width = length
# ws.column_dimensions[column_cells[0].column].bestFit = True
for row in ws.iter_rows(min_row=4,
max_row=ws.max_row,
min_col=1,
max_col=ws.max_column):
for cell in row:
# Column Headers
if cell.row in headers:
if header_number_format_dict and (
cell.column in header_number_format_dict):
cell.number_format = header_number_format_dict[
cell.column]
if header_h_alignment_list and (
cell.column in header_h_alignment_list):
cell.alignment = Alignment(
horizontal=header_h_alignment_list[cell.column],
vertical="bottom",
wrap_text=False)
# Hit Data
else:
if hits_number_format_dict and (
cell.column in hits_number_format_dict):
cell.number_format = hits_number_format_dict[
cell.column]
if hits_alignment_dict and (cell.column
in hits_alignment_dict):
cell.alignment = Alignment(
horizontal=hits_alignment_dict[cell.column],
vertical="center",
wrap_text=False)
if width_dict:
for column in width_dict:
if width_dict[column] == 0:
ws.column_dimensions[column].hidden = True
else:
ws.column_dimensions[column].width = width_dict[column]
format_header(ws,
make_column_property_dict({"4": "center"},
repeat=self.n_experiments,
length=3)) # First Row
format_header(ws,
make_column_property_dict(
{"2": "center"}, {"A": "right"},
repeat=((self.n_experiments * 3) + 25),
length=1), 2, 2) # Second Row
format_header(ws,
make_column_property_dict(
{"2": "center"},
repeat=((self.n_experiments * 3) + 25),
length=1), 3, 3) # Third Row
for offset in range(self.n_experiments):
merge_string = get_column_letter(4 + (
3 * offset)) + '1:' + get_column_letter(6 + (3 * offset)) + '1'
ws.merge_cells(merge_string)
offset = self.n_experiments * 3
for index in [5, 9, 13, 17, 21]:
merge_string = get_column_letter(
index + offset) + '2:' + get_column_letter(index + 2 +
offset) + '2'
ws.merge_cells(merge_string)
def format_statistics_sheet(self, sheet_name="Statistics"):
"""
Formatting for Statistics Sheet
"""
# Statistics
number_format_list = {
'C': '0',
'E': '0.000',
'F': '0.000000',
'G': '0.00%',
'I': '0.00',
'J': '0.00',
'K': '0.00%',
'M': '0.0',
'N': '0.0000',
'O': '0.00%',
'Q': '0.0',
'R': '0.0000',
'S': '0.00%',
'U': '0.0',
'V': '0.0000',
'W': '0.00%',
'Y': '0.0',
'Z': '0.000',
'AA': '0.000%'
}
width_dict = {
"B": 12,
"C": 2,
"D": 1,
"E": 8,
"F": 11,
"G": 8,
"H": 1,
"I": 13,
"J": 12,
"K": 10,
"L": 1,
"M": 8,
"N": 10,
"O": 10,
"P": 1,
"Q": 8,
"R": 10,
"S": 10,
"T": 1,
"U": 6,
"V": 8,
"W": 10,
"X": 1,
"Y": 8,
"Z": 9,
"AA": 9
}
alignment_list = {"B": "center", "C": "center"}
ws = self.wb[sheet_name]
format_sheet(ws, number_format_list, width_dict, alignment_list)
for offset in range(6):
ws.merge_cells(
get_column_letter(5 + (4 * offset)) + '1:' +
get_column_letter(7 + (4 * offset)) + '1')
format_header(
ws, make_column_property_dict({"2": "center"}, repeat=27,
length=1), 1, 2)