def get_donor_cell(self):
size = 9
if self.has_acceptor:
cell = WriteOnlyCell(ws_result, value='OK')
cell.fill = PatternFill(start_color='FFdff0d8',
end_color='FFdff0d8',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='3c763d',
bold=True)
cell.comment = Comment(text="Good =)", author=self.author)
else:
cell = WriteOnlyCell(ws_result, value='NO')
cell.fill = PatternFill(start_color='FFf2dede',
end_color='FFf2dede',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='a94442',
bold=True)
cell.comment = Comment(
text="There are no acceptor ({}) on the site: {}".format(
self.acceptor, self.donor),
author=self.author)
return cell
def write_to_excel2_2(datas, file_full_name):
workbook = openpyxl.Workbook(write_only=True) # 默认无sheet sheet名从'Sheet'开始
workbook.create_sheet() # 创建名为'Sheet'的sheet
for data in datas:
worksheet = workbook.create_sheet() # 创建名为'Sheet1'的sheet
alignment_title = Alignment(horizontal='center')
font_title = Font(bold=True, size=10)
font_content = Font(size=9)
keys = data[0].keys()
row = []
for key in keys:
cell = WriteOnlyCell(worksheet,
str(key) if key is not None else '')
cell.font = font_title
cell.alignment = alignment_title
row.append(cell)
worksheet.append(row)
for js in data:
row = []
for key in keys:
cell = WriteOnlyCell(
worksheet,
str(js[key]) if js[key] is not None else '')
cell.font = font_content
cell.alignment = alignment_title
row.append(cell)
worksheet.append(row)
workbook.remove(workbook[workbook.sheetnames[0]]) # 删除名为'Sheet'的sheet
workbook.save(file_full_name)
log_util.log('file_util.write_to_excel', 'filename:%s' % (file_full_name))
def __get_cell(self, string, color=None, style=None):
"""
Helper method for log message. This method takes a string as well as color and style
arguments to create a write-only cell.
Args:
string: a string object to be written to the cell's value field.
color: a string object containing a color hex code "######"
style: a string choosing 1 of 3 formatting options (ITALICS, BOLD, UNDERLINED)
"""
if not MODULE_INSTALLED:
return
cell = WriteOnlyCell(self.worksheet, value=string)
if color is not None:
# pylint: disable=E0237
cell.fill = PatternFill("solid", fgColor=color)
# pylint: disable=E0237
cell.font = Font(
name=self.font_name,
bold=(style == self.BOLD),
italic=(style == self.ITALICS),
underline=("single" if style == self.UNDERLINED else "none"),
)
# pylint: disable=E0237
cell.alignment = self.__align
return cell
def _create_cell(self, worksheet: WriteOnlyWorksheet, data: dict) -> WriteOnlyCell:
cell = WriteOnlyCell(ws=worksheet, value=data.get('value'))
cell.column = data.get('column')
cell.row = data.get('row')
cell.font = self._create_cell_font(data.get('font'))
cell.alignment = self._dict_to_object(data.get('alignment'), Alignment)
cell.border = self._create_cell_borders(data.get('border'))
return cell
def write_ref_headings(self, data):
cells = []
for d in data:
cell = WriteOnlyCell(self._sheet, d)
cell.font = cell.font.copy(bold=True)
cells.append(cell)
self._sheet.append(cells)
self._sheet.merged_cells.ranges.append('A1:G1')
self._written_lines += 1
def get_status_cell(self):
size = 8
if self.url_status < 400:
cell = WriteOnlyCell(ws_result, value=self.status_reason)
color = '3c763d' if self.has_acceptor else 'a94442'
cell.font = Font(name=self.font, size=size, color=color, bold=True)
cell.comment = Comment(text="Good =)", author=self.author)
else:
cell = WriteOnlyCell(ws_result, value="Site Access Problem")
cell.font = Font(name=self.font,
size=size,
color='a94442',
bold=True)
cell.comment = Comment(
text="Seems we have some troubles with the site: {}".format(
self.acceptor),
author=self.author)
return cell
def make_sheet(self, title: str, headers: List[str]) -> Worksheet:
sheet = self.workbook.create_sheet(title=title)
sheet.freeze_panes = "A2"
sheet.sheet_properties.filterMode = True
cells = []
for header in headers:
header_ = sanitize_text(header)
cell = WriteOnlyCell(sheet, value=header_)
cell.font = self.HEADER_FONT
cell.fill = self.HEADER_FILL
cells.append(cell)
sheet.append(cells)
return sheet
def get_cell(self,
value,
wrap=False,
shrink_to_fit=False,
bold=False,
unlock=False) -> WriteOnlyCell:
cell = WriteOnlyCell(self.work_sheet, value=value)
if bold:
cell.font = self.font_regular_bold
else:
cell.font = self.font_regular
if wrap:
cell.alignment = self.alignment_wrap_text
elif shrink_to_fit:
cell.alignment = self.alignment_shrink_to_fit
if unlock:
cell.protection = Protection(locked=False)
return cell
def _append_excel_row(self, worksheet, data, header=False):
if header:
font = self.header_font
else:
font = self.data_font
if not font:
row = data
else:
row = []
for cell in data:
cell = WriteOnlyCell(worksheet, cell)
cell.font = font
row.append(cell)
worksheet.append(row)
def write_to_new_file(self, path):
write_work_book = xl.Workbook(write_only=True)
ws = write_work_book.create_sheet('Sample list')
read_work_sheet = self.workbook['Sample list']
normal_style = self.workbook._named_styles['Normal']
for row in read_work_sheet.iter_rows():
write_row = list()
for cell in row:
write_cell = WriteOnlyCell(ws, value=cell.value)
if cell.font != normal_style.font:
write_cell.font = cell.font
if cell.fill is not None:
write_cell.fill = cell.fill
write_row.append(write_cell)
ws.append(write_row)
write_work_book.save(path)
def get(self, request, **kwargs):
if self.model == self.MODEL_LAT_LONG:
headers = self.COMMON_HEADERS + self.LAT_LONG_HEADERS
elif self.model == self.MODEL_EASTING_NORTHING:
headers = self.COMMON_HEADERS + self.EASTING_NORTHING_HEADERS
else:
return HttpResponseBadRequest(
"Unknown site template model {}. Must be one of {}.".format(
self.model,
[self.MODEL_LAT_LONG, self.MODEL_EASTING_NORTHING]))
wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.title = 'Sites'
cells = []
for header in headers:
cell = WriteOnlyCell(ws, value=header)
cell.font = self.HEADER_FONT
cells.append(cell)
ws.append(cells)
file_name = 'Sites_template_' + self.model
return WorkbookResponse(wb, file_name=file_name)
def test1():
current_dir = path.dirname(path.abspath(__file__))
file_path = f'{current_dir}{sep}openpyxl.xlsx'
# 创建空表格
wb = Workbook()
# 读取表格
# wb = load_workbook(file_path)
# grab the active worksheet
ws = wb.active
# ws2 = wb.create_sheet("Sheet2") # insert at the end (default)
# ws3 = wb.create_sheet("Sheet3") # insert at the end (default)
# ws = wb.create_sheet("Mysheet", 0) # insert at first position
# ws = wb.create_sheet("Mysheet", -1) # insert at the penultimate position
# target = wb.copy_worksheet(ws)
# sheet 标题
ws.title = "Sheet Title"
# 通过标题获取 Sheet 对象
# ws3 = wb["Sheet Title"]
# sheet 标题的背景色
ws.sheet_properties.tabColor = "FF0000"
print('\n ==============输出工作表名称================\n')
print(wb.sheetnames)
for sheet in wb:
print(sheet.title, sheet.max_row, sheet.max_column)
# Data can be assigned directly to cells
ws['A1'] = 42
ws['B1'] = 43
ws['C1'] = 44
print('\n================ 写数据 =========================\n')
# Rows can also be appended
ws.append([1, 2, 3])
value = ws.cell(4, 4, 44)
print("Cell 对象: ", value)
# Python types will automatically be converted
ws['A3'] = datetime.now()
print('number_format: ', ws['A3'].number_format)
special_cell = WriteOnlyCell(ws, value="hello world")
special_cell.font = Font(name='Courier', size=12)
special_cell.comment = Comment(text="A comment", author="Author's Name")
ws.append([special_cell, '张三', None])
# 在第 4 行上插入一行，第 4 行变成空行
# ws.insert_rows(4)
ws.delete_rows(4)
# Save the file
print(f'文件保存路径：{current_dir}')
# This operation will overwrite existing files without warning.
wb.save(file_path)
print('\n================ 读数据 =========================\n')
cell_range = ws['A1':'C2']
print('获取 A1 - C2 之间的数据：')
for cell in cell_range:
print(cell)
print()
# 访问一组单元格
# colC = ws['C']
# col_range_C_D = ws['C:D']
# row10 = ws[10]
# row_range_5_10 = ws[5:10]
""" for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell) """
# For performance reasons the Worksheet.iter_cols() method is not available in read-only mode.
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
list = []
for cell in col:
list.append(cell.value)
print(list)
print()
""" print('获取所有的行数据：')
for cell in ws.rows:
print(cell)
print() """
# For performance reasons the Worksheet.columns property is not available in read-only mode.
""" print('获取所有的列数据：')
for cell in ws.columns:
print(cell)
print() """
cell_values = ws.values
for row in cell_values:
list = []
for col in row:
list.append(col)
print(list)
wb.close
def members_excel_export():
community = g.community
attributes = [attrgetter(a) for a in MEMBERS_EXPORT_ATTRS]
BaseModel = db.Model
wb = openpyxl.Workbook()
if wb.worksheets:
wb.remove_sheet(wb.active)
ws_title = _("%(community)s members", community=community.name)
ws_title = ws_title.strip()
if len(ws_title) > 31:
# sheet title cannot exceed 31 char. max length
ws_title = ws_title[:30] + "…"
ws = wb.create_sheet(title=ws_title)
row = 0
cells = []
cols_width = []
for _col, label in enumerate(MEMBERS_EXPORT_HEADERS, 1):
value = text_type(label)
cell = WriteOnlyCell(ws, value=value)
cell.font = HEADER_FONT
cell.alignment = HEADER_ALIGN
cells.append(cell)
cols_width.append(len(value) + 1)
ws.append(cells)
for membership_info in _members_query().all():
row += 1
cells = []
for col, getter in enumerate(attributes):
value = None
try:
value = getter(membership_info)
except AttributeError:
pass
if isinstance(value, (BaseModel, Role)):
value = text_type(value)
cell = WriteOnlyCell(ws, value=value)
cells.append(value)
# estimate width
value = text_type(cell.value)
width = max(len(l) for l in value.split("\n")) + 1
cols_width[col] = max(width, cols_width[col])
ws.append(cells)
# adjust columns width
MIN_WIDTH = 3
MAX_WIDTH = openpyxl.utils.units.BASE_COL_WIDTH * 4
for idx, width in enumerate(cols_width, 1):
letter = openpyxl.utils.get_column_letter(idx)
width = min(max(width, MIN_WIDTH), MAX_WIDTH)
ws.column_dimensions[letter].width = width
fd = BytesIO()
wb.save(fd)
fd.seek(0)
response = current_app.response_class(fd, mimetype=XLSX_MIME)
filename = "{}-members-{}.xlsx".format(
community.slug, strftime("%d:%m:%Y-%H:%M:%S", gmtime())
)
response.headers["content-disposition"] = 'attachment;filename="{}"'.format(
filename
)
return response
def main(comments, output, catalogs):
"""
Convert .PO files to an XLSX file.
po-to-xls tries to guess the locale for PO files by looking at the
"Language" key in the PO metadata, falling back to the filename. You
can also specify the locale manually by adding prefixing the filename
with "<locale>:". For example: "nl:locales/nl/mydomain.po".
"""
has_msgctxt = False
for (locale, catalog) in catalogs:
has_msgctxt = has_msgctxt or any(m.msgctxt for m in catalog)
fuzzy_font = Font(italic=True, bold=True)
messages = []
seen = set()
for (_, catalog) in catalogs:
for msg in catalog:
if not msg.msgid or msg.obsolete:
continue
if (msg.msgid, msg.msgctxt) not in seen:
messages.append((msg.msgid, msg.msgctxt, msg))
seen.add((msg.msgid, msg.msgctxt))
book = openpyxl.Workbook(write_only=True)
sheet = book.create_sheet(title=u'Translations')
row = []
has_msgctxt_column = has_occurrences_column = has_comment_column = has_tcomment_column = None
if has_msgctxt:
has_msgctxt_column = True
row.append(ColumnHeaders.msgctxt)
row.append(ColumnHeaders.msgid)
if 'reference' in comments or 'all' in comments:
has_occurrences_column = True
row.append(ColumnHeaders.occurrences)
if 'extracted' in comments or 'all' in comments:
has_comment_column = True
row.append(ColumnHeaders.comment)
if 'translator' in comments or 'all' in comments:
has_tcomment_column = True
row.append(ColumnHeaders.tcomment)
for (i, cat) in enumerate(catalogs):
row.append(cat[0])
sheet.append(row)
ref_catalog = catalogs[0][1]
with click.progressbar(messages, label='Writing catalog to sheet') as todo:
for (msgid, msgctxt, message) in todo:
row = []
if has_msgctxt_column is not None:
row.append(msgctxt)
row.append(msgid)
msg = ref_catalog.find(msgid, msgctxt=msgctxt)
if has_occurrences_column:
o = []
if msg is not None:
for (entry, lineno) in msg.occurrences:
if lineno:
o.append(u'%s:%s' % (entry, lineno))
else:
o.append(entry)
row.append(u', '.join(o) if o else None)
if has_comment_column:
row.append(msg.comment if msg is not None else None)
if has_tcomment_column:
row.append(msg.tcomment if msg is not None else None)
for cat in catalogs:
cat = cat[1]
msg = cat.find(msgid, msgctxt=msgctxt)
if msg is None:
row.append(None)
elif 'fuzzy' in msg.flags:
cell = WriteOnlyCell(sheet, value=msg.msgstr)
cell.font = fuzzy_font
row.append(cell)
else:
row.append(msg.msgstr)
sheet.append(row)
sheet.freeze_panes = 'B1'
book.save(output)
def export_email_approval_list(local_filename,
found_attachments,
tdocs_without_emails=None,
tdoc_data=None):
if (local_filename is None) or (local_filename == ''):
return
# found_attachments -> collections.namedtuple('RevisionDoc', 'time tdoc filename absolute_url sender_name sender_address chairman_notes')
print('Starting email approval export: {0} emails'.format(
len(found_attachments)))
# Faster variant writing first most data not using VBA
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()
ws.title = "Revisions"
# Add title row
ws.append([
'TD#', 'Time', 'Filename mention', 'Sender', 'Company', 'Email', 'AI',
"Chairman's notes"
])
# Add email entries
for idx, item in enumerate(found_attachments, start=2):
filename_cell = WriteOnlyCell(ws, value=item.filename)
sender_name_cell = WriteOnlyCell(ws, value=item.sender_name)
link_cell = WriteOnlyCell(ws, value='Link')
# Link to file. May not always be a path
if item.absolute_url != '':
filename_cell.hyperlink = 'file:///' + item.absolute_url
filename_cell.font = Font(underline="single", color='00EA0A8E')
# Link to author
sender_name_cell.hyperlink = 'mailto:' + item.sender_address
sender_name_cell.font = Font(underline="single", color='00EA0A8E')
# Link to email
link_cell.hyperlink = 'file:///' + item.email_url
link_cell.font = Font(underline="single", color='00EA0A8E')
# Write row
ws.append([
item.tdoc, item.time, filename_cell, sender_name_cell,
get_company_name_based_on_email(item.sender_address), link_cell,
str(item.ai_folder),
str(item.chairman_notes)
])
if tdocs_without_emails is not None and tdoc_data is not None:
ws = wb.create_sheet()
ws.title = "TDocs without emails"
ws.append([
'TD#', 'AI', 'Type', 'Doc For', 'Title', 'Source', 'Rel',
'Work Item', 'Comments'
])
tdocs_info = tdoc_data.tdocs.loc[list(tdocs_without_emails), [
'AI', 'Type', 'Doc For', 'Title', 'Source', 'Rel', 'Work Item',
'Comments'
]]
print('{0} TDocs without matching emails'.format(len(
tdocs_info.index)))
for row_index, row in tdocs_info.iterrows():
ws.append([
row_index,
str(row['AI']),
str(row['Type']),
str(row['Doc For']),
str(row['Title']),
str(row['Source']),
str(row['Rel']),
str(row['Work Item']),
str(row['Comments'])
])
print('Saving Excel table structure')
wb.save(filename=local_filename)
print('Closing Excel File')
wb.close()
print('Excel File closed')
# Only necessary things with VBA (much slower)
try:
print('Applying Excel formatting')
wb = open_excel_document(filename=local_filename)
# ws = wb.ActiveSheet
ws = wb.Sheets("Revisions")
ws.Range("A:A").ColumnWidth = 14
ws.Range("B:B").ColumnWidth = 18
ws.Range(
"C:C"
).ColumnWidth = 30 # Reduced to 30 as we now just send around revision numbers
ws.Range("D:D").ColumnWidth = 40
ws.Range("E:E").ColumnWidth = 17
ws.Range("F:F").ColumnWidth = 9
ws.Range("G:G").ColumnWidth = 25
ws.Range("H:H").ColumnWidth = 60
ws.Range("A:B").HorizontalAlignment = -4108
ws.Range("G:G").HorizontalAlignment = -4108
ws.Range("A:H").VerticalAlignment = -4108
ws.Range("A:H").WrapText = True
ws.Range("H:H").WrapText = True
ws.Range("A1:H1").Font.Bold = True
set_first_row_as_filter(wb, 'Revisions')
ws.AutoFilter.Sort.SortFields.Clear()
# https://docs.microsoft.com/en-us/office/vba/api/excel.xlsortorder
xlAscending = 1
# https://stackoverflow.com/questions/11766118/excel-constants-for-sorting
xlSortOnValues = 0
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("G:G")) # AI
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("A:A")) # TD
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("B:B")) # Time
ws.AutoFilter.Sort.Apply()
if tdocs_without_emails is not None and tdoc_data is not None:
ws = wb.Sheets("TDocs without emails")
# TD, AI, Type, Doc For, Title, source, rel, work item, comments
ws.Range("A:A").ColumnWidth = 11
ws.Range("B:B").ColumnWidth = 7
ws.Range("C:C").ColumnWidth = 11
ws.Range("D:D").ColumnWidth = 12
ws.Range("E:E").ColumnWidth = 50
ws.Range("F:F").ColumnWidth = 45
ws.Range("G:G").ColumnWidth = 7
ws.Range("H:H").ColumnWidth = 7
ws.Range("I:I").ColumnWidth = 50
ws.Range("A:I").HorizontalAlignment = -4108
ws.Range("A:I").VerticalAlignment = -4108
ws.Range("A:I").WrapText = True
ws.Range("A1:I1").Font.Bold = True
set_first_row_as_filter(wb,
'TDocs without emails',
already_activated=True)
ws.AutoFilter.Sort.SortFields.Clear()
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("B:B")) # AI
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("A:A")) # TD
ws.AutoFilter.Sort.Apply()
print('Finished email approval export')
revisions_ws = wb.Worksheets('Revisions').Activate()
add_pivot_table_to_workbook_active_sheet(wb,
cell_range="A:G",
data_field="TD#",
row_field="Sender",
column_field=None,
ws_name="Delegate Summary")
revisions_ws = wb.Worksheets('Revisions').Activate()
add_pivot_table_to_workbook_active_sheet(wb,
cell_range="A:G",
data_field="TD#",
row_field="Company",
column_field=None,
ws_name="Company Summary")
wb.SaveAs(local_filename)
except:
traceback.print_exc()
def members_excel_export():
community = g.community
attributes = [attrgetter(a) for a in MEMBERS_EXPORT_ATTRS]
BaseModel = db.Model
wb = openpyxl.Workbook()
if wb.worksheets:
wb.remove_sheet(wb.active)
ws_title = _("%(community)s members", community=community.name)
ws_title = ws_title.strip()
if len(ws_title) > 31:
# sheet title cannot exceed 31 char. max length
ws_title = ws_title[:30] + "…"
ws = wb.create_sheet(title=ws_title)
row = 0
cells = []
cols_width = []
for _col, label in enumerate(MEMBERS_EXPORT_HEADERS, 1):
value = str(label)
cell = WriteOnlyCell(ws, value=value)
cell.font = HEADER_FONT
cell.alignment = HEADER_ALIGN
cells.append(cell)
cols_width.append(len(value) + 1)
ws.append(cells)
for membership_info in _members_query().all():
row += 1
cells = []
for col, getter in enumerate(attributes):
value = None
try:
value = getter(membership_info)
except AttributeError:
pass
if isinstance(value, (BaseModel, Role)):
value = str(value)
cell = WriteOnlyCell(ws, value=value)
cells.append(value)
# estimate width
value = str(cell.value)
width = max(len(l) for l in value.split("\n")) + 1
cols_width[col] = max(width, cols_width[col])
ws.append(cells)
# adjust columns width
MIN_WIDTH = 3
MAX_WIDTH = openpyxl.utils.units.BASE_COL_WIDTH * 4
for idx, width in enumerate(cols_width, 1):
letter = openpyxl.utils.get_column_letter(idx)
width = min(max(width, MIN_WIDTH), MAX_WIDTH)
ws.column_dimensions[letter].width = width
fd = BytesIO()
wb.save(fd)
fd.seek(0)
response = current_app.response_class(fd, mimetype=XLSX_MIME)
filename = "{}-members-{}.xlsx".format(
community.slug, strftime("%d:%m:%Y-%H:%M:%S", gmtime())
)
response.headers["content-disposition"] = 'attachment;filename="{}"'.format(
filename
)
return response
def cell(val):
cell = WriteOnlyCell(ws, value=val)
cell.font = Font(name='Calibri', size=FONT_SIZE)
cell.alignment = Alignment(vertical='center')
return cell
def make_cell(value, size=9, bold=False):
cell = WriteOnlyCell(ws_result, value=value)
cell.font = Font(name='Verdana', size=size, bold=bold)
return cell
def injection_table(file_names, cmsw):
"""Connects to an individual database and determines which injections were puffs, injections,
and leaves some uncatagorized to be classified by a person looking at the surrounding data
"""
xlsx2_name = str(cmsw).replace('s', '') + 'rods-detailed-data.xlsx'
wb = Workbook(write_only=True)
data_sheet = wb.create_sheet()
yellow = PatternFill(fill_type="solid", start_color=YLW, end_color=YLW)
green = PatternFill(fill_type="solid", start_color=GRN, end_color=GRN)
alignment = Alignment(wrap_text=True, shrink_to_fit=True)
data_sheet.title = 'Sheet1'
data_sheet.row_dimensions.group(2, 3, hidden=True)
data_sheet.column_dimensions.group('B', 'E', hidden=True)
data_sheet.column_dimensions.group('H', 'R', hidden=True)
data_sheet.column_dimensions.group('U', 'U', hidden=True)
data_sheet.column_dimensions.group('X', 'AC', hidden=True)
data_sheet.column_dimensions.group('AF', 'AI', hidden=True)
# values for width do not match actual values
# columns ending in .42 are .71 over the actual width
# columns ending in .01 are .72 over the actual width
data_sheet.column_dimensions['A'].width = 18.42
data_sheet.column_dimensions['G'].width = 10.01
data_sheet.column_dimensions['V'].width = 12.01
data_sheet.column_dimensions['W'].width = 11.01
data_sheet.column_dimensions['AD'].width = 14.42
data_sheet.column_dimensions['AE'].width = 10.42
data_sheet.column_dimensions['AJ'].width = 12.42
data_sheet.freeze_panes = 'A2'
frmcell = [
WriteOnlyCell(ws=data_sheet, value='Contrast Aspirated'),
WriteOnlyCell(ws=data_sheet, value='Contrast Diverted'),
WriteOnlyCell(ws=data_sheet, value='Percent Saved'),
WriteOnlyCell(ws=data_sheet, value='Contrast to Patient'),
WriteOnlyCell(ws=data_sheet, value='Cumulative'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate from Syringe'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate to Patient'),
WriteOnlyCell(ws=data_sheet, value='Volume Attempted')
]
empty_cell = WriteOnlyCell(ws=data_sheet, value='')
for cell in range(len(frmcell)):
frmcell[cell].font = Font(bold=True)
cases = [
[
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, frmcell[0], empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, frmcell[1], frmcell[2],
empty_cell, frmcell[3], frmcell[4], empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, frmcell[5],
frmcell[6], empty_cell, empty_cell, empty_cell, empty_cell,
frmcell[7]
],
[
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell
],
[
WriteOnlyCell(ws=data_sheet, value='Time Stamp'),
WriteOnlyCell(ws=data_sheet, value='Syringe Revision'),
WriteOnlyCell(ws=data_sheet, value='PMDV Revision'),
WriteOnlyCell(ws=data_sheet, value='Syringe Address'),
WriteOnlyCell(ws=data_sheet, value='PMDV Address'),
WriteOnlyCell(ws=data_sheet, value='Injection or Aspiration'),
WriteOnlyCell(ws=data_sheet, value='Aspirating Contrast'),
WriteOnlyCell(ws=data_sheet, value='Replacing Device'),
WriteOnlyCell(ws=data_sheet, value='Current DyeVert Diameter'),
WriteOnlyCell(ws=data_sheet, value='Current Syringe Diameter'),
WriteOnlyCell(ws=data_sheet,
value='Starting Syringe Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='Ending Syringe Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='Syringe Linear Plunger Position'),
WriteOnlyCell(ws=data_sheet, value='Volume(Injected / Aspirated)'),
WriteOnlyCell(
ws=data_sheet,
value='Starting DyeVert Plus Reservoir Plunger Position'),
WriteOnlyCell(
ws=data_sheet,
value='Ending DyeVert Plus Reservoir Plunger Position'),
WriteOnlyCell(
ws=data_sheet,
value='DyeVert Plus Reservoir Linear Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='DyeVert Plus Reservoir Volume Diverted'),
WriteOnlyCell(
ws=data_sheet,
value='DyeVert Plus Reservoir Contrast Volume Diverted'),
WriteOnlyCell(ws=data_sheet, value='PercentContrastSaved'),
WriteOnlyCell(ws=data_sheet,
value='Total Injection Volume to Patient'),
WriteOnlyCell(ws=data_sheet, value='Volume of Contrast Injected'),
WriteOnlyCell(ws=data_sheet,
value='Cumulative Contrast Volume Injected'),
WriteOnlyCell(ws=data_sheet, value='Volume of Other Injected'),
WriteOnlyCell(ws=data_sheet,
value='Starting Contrast Percentage in Syringe'),
WriteOnlyCell(ws=data_sheet,
value='Ending Contrast Percentage in Syringe'),
WriteOnlyCell(
ws=data_sheet,
value='Starting Contrast Percentage in DyeVert Plus Reservoir'
),
WriteOnlyCell(
ws=data_sheet,
value='Ending Contrast Percentage in DyeVert Plus Reservoir'),
WriteOnlyCell(ws=data_sheet, value='Duration'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate from Syringe'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate to Patient'),
WriteOnlyCell(ws=data_sheet, value='Contrast Line Pressure'),
WriteOnlyCell(ws=data_sheet,
value='DyeVert Plus Stopcock Position'),
WriteOnlyCell(ws=data_sheet, value='System IsSystemPaused'),
empty_cell, empty_cell
]
]
case_number = 0
for file_name in file_names:
con = sqlite.connect(file_name)
with con:
cur = con.cursor()
cur.execute('SELECT * FROM CMSWCases')
rows = cur.fetchall()
case_id_number = {}
global TIME_STAMP, SYRINGE_REVISION, PMDV_REVISION, IS_AN_INJECTION, IS_ASPIRATING_CONTRAST, \
DYEVERT_DIAMETER, SYRINGE_DIAMETER, STARTING_SYRINGE_POSITION, ENDING_SYRINGE_POSITION, \
LINEAR_SYRINGE_MOVEMENT, SYRINGE_VOLUME_INJECTED_OR_ASPIRATED, STARTING_DYEVERT_POSITION, \
ENDING_DYEVERT_POSITION, LINEAR_DYEVERT_MOVEMENT, DIVERT_VOLUME_DIVERTED, \
DYEVERT_CONTRAST_VOLUME_DIVERTED, PERCENT_CONTRAST_SAVED, INJECTION_VOLUME_TO_PATIENT, \
CONTRAST_VOLUME_TO_PATIENT, CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT, OTHER_VOLUME_TO_PATIENT, \
STARTING_CONTRAST_PERCENT_IN_SYRINGE, STARTING_CONTRAST_PERCENT_IN_DYEVERT, \
ENDING_CONTRAST_PERCENT_IN_DYEVERT, DURATION, FLOW_RATE_TO_FROM_SYRINGE, FLOW_RATE_TO_PATIENT, \
PREDOMINANT_CONTRAST_LINE_PRESSURE, STARTING_DYEVERT_STOPCOCK_POSITION, IS_SYSTEM_PAUSED, \
ENDING_CONTRAST_PERCENT_IN_SYRINGE, SYRINGE_ADDRESS, PMDV_ADDRESS, IS_DEVICE_REPLACEMENT, \
SERIAL_NUMBER, DATE_OF_PROCEDURE, THRESHOLD_VOLUME, ATTEMPTED_CONTRAST_INJECTION_VOLUME, \
DIVERTED_CONTRAST_VOLUME, CUMULATIVE_VOLUME_TO_PATIENT, PERCENTAGE_CONTRAST_DIVERTED, TOTAL_DURATION
if not rows == []:
if rows[0][2] == '2.1.56' or rows[0][2] == '2.1.24' or rows[0][2] == '2.1.67' or \
rows[0][2] == '2.0.1981' or rows[0][2] == '2.0.2013':
TIME_STAMP = 2
SYRINGE_REVISION = 3
PMDV_REVISION = 4
IS_AN_INJECTION = 5
IS_ASPIRATING_CONTRAST = 6
DYEVERT_DIAMETER = 7
SYRINGE_DIAMETER = 8
STARTING_SYRINGE_POSITION = 9
ENDING_SYRINGE_POSITION = 10
LINEAR_SYRINGE_MOVEMENT = 11
SYRINGE_VOLUME_INJECTED_OR_ASPIRATED = 12
STARTING_DYEVERT_POSITION = 13
ENDING_DYEVERT_POSITION = 14
LINEAR_DYEVERT_MOVEMENT = 15
DIVERT_VOLUME_DIVERTED = 16
DYEVERT_CONTRAST_VOLUME_DIVERTED = 17
PERCENT_CONTRAST_SAVED = 18
INJECTION_VOLUME_TO_PATIENT = 19
CONTRAST_VOLUME_TO_PATIENT = 20
CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT = 21
OTHER_VOLUME_TO_PATIENT = 22
STARTING_CONTRAST_PERCENT_IN_SYRINGE = 24
STARTING_CONTRAST_PERCENT_IN_DYEVERT = 25
ENDING_CONTRAST_PERCENT_IN_DYEVERT = 26
DURATION = 27
FLOW_RATE_TO_FROM_SYRINGE = 28
FLOW_RATE_TO_PATIENT = 29
PREDOMINANT_CONTRAST_LINE_PRESSURE = 30
STARTING_DYEVERT_STOPCOCK_POSITION = 31
IS_SYSTEM_PAUSED = 32
ENDING_CONTRAST_PERCENT_IN_SYRINGE = 33
SYRINGE_ADDRESS = 34
PMDV_ADDRESS = 35
IS_DEVICE_REPLACEMENT = 36
else:
SERIAL_NUMBER = 3
DATE_OF_PROCEDURE = 5
THRESHOLD_VOLUME = 8
ATTEMPTED_CONTRAST_INJECTION_VOLUME = 13
DIVERTED_CONTRAST_VOLUME = 14
CUMULATIVE_VOLUME_TO_PATIENT = 15
PERCENTAGE_CONTRAST_DIVERTED = 16
TOTAL_DURATION = 20
TIME_STAMP = 2
SYRINGE_REVISION = 3
PMDV_REVISION = 4
IS_AN_INJECTION = 8
IS_ASPIRATING_CONTRAST = 9
DYEVERT_DIAMETER = 10
SYRINGE_DIAMETER = 11
STARTING_SYRINGE_POSITION = 12
ENDING_SYRINGE_POSITION = 13
LINEAR_SYRINGE_MOVEMENT = 14
SYRINGE_VOLUME_INJECTED_OR_ASPIRATED = 15
STARTING_DYEVERT_POSITION = 16
ENDING_DYEVERT_POSITION = 17
LINEAR_DYEVERT_MOVEMENT = 18
DIVERT_VOLUME_DIVERTED = 19
DYEVERT_CONTRAST_VOLUME_DIVERTED = 20
PERCENT_CONTRAST_SAVED = 21
INJECTION_VOLUME_TO_PATIENT = 22
CONTRAST_VOLUME_TO_PATIENT = 23
CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT = 24
OTHER_VOLUME_TO_PATIENT = 25
STARTING_CONTRAST_PERCENT_IN_SYRINGE = 27
STARTING_CONTRAST_PERCENT_IN_DYEVERT = 29
ENDING_CONTRAST_PERCENT_IN_DYEVERT = 30
DURATION = 31
FLOW_RATE_TO_FROM_SYRINGE = 32
FLOW_RATE_TO_PATIENT = 33
PREDOMINANT_CONTRAST_LINE_PRESSURE = 34
STARTING_DYEVERT_STOPCOCK_POSITION = 35
IS_SYSTEM_PAUSED = 36
ENDING_CONTRAST_PERCENT_IN_SYRINGE = 28
SYRINGE_ADDRESS = 5
PMDV_ADDRESS = 4
IS_DEVICE_REPLACEMENT = 7
for row in rows:
case_id_number[row[CMSW_CASE_ID]] = row[CASE_ID][-23:-4]
with con:
cur = con.cursor()
cur.execute('SELECT * FROM CMSWInjections')
rows = cur.fetchall()
for row in rows:
perc_saved = WriteOnlyCell(value=row[PERCENT_CONTRAST_SAVED],
ws=data_sheet)
if case_number != row[CASE_ID]:
case_number = row[CASE_ID]
_cmsw = cmsw_read.cmsw_id_read(file_name)
cases.append([
WriteOnlyCell(ws=data_sheet, value='CMSW'), empty_cell,
empty_cell, empty_cell, empty_cell,
WriteOnlyCell(ws=data_sheet, value=_cmsw), empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell
])
cases.append([
WriteOnlyCell(ws=data_sheet, value='Case'), empty_cell,
empty_cell, empty_cell, empty_cell,
WriteOnlyCell(ws=data_sheet,
value=case_id_number[case_number]),
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell
])
if row[IS_AN_INJECTION] == 1:
inj_asp = 'INJ'
else:
inj_asp = 'ASP'
if row[IS_ASPIRATING_CONTRAST] == 1:
contrast_asp = 'Yes'
else:
contrast_asp = ''
if row[IS_DEVICE_REPLACEMENT] == 1:
replacement = 'Yes'
else:
replacement = ''
if inj_asp == 'INJ':
perc_saved.font = Font(bold=True)
if row[CONTRAST_VOLUME_TO_PATIENT] + row[
DYEVERT_CONTRAST_VOLUME_DIVERTED] >= 3:
puff_inj = WriteOnlyCell(value='Injection',
ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[CONTRAST_VOLUME_TO_PATIENT] + row[
DYEVERT_CONTRAST_VOLUME_DIVERTED] <= 2:
puff_inj = WriteOnlyCell(value='Puff', ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[FLOW_RATE_TO_FROM_SYRINGE] >= 2.5:
puff_inj = WriteOnlyCell(value='Injection',
ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[FLOW_RATE_TO_FROM_SYRINGE] <= 2:
puff_inj = WriteOnlyCell(value='Puff', ws=data_sheet)
puff_inj.font = Font(bold=True)
else:
debug_msg = 'Event ' + str(row[0]) + ' in cmsw ' + str(cmsw_read.cmsw_id_read(file_name)) + \
', case ' + str(row[CASE_ID]) + ' matched neither type'
logging.warning(debug_msg)
print(debug_msg)
puff_inj = empty_cell
else:
puff_inj = empty_cell
perc_saved.font = Font(bold=False)
for entry in range(len(row)):
if row[entry] is None:
newrow = list(row)
newrow[entry] = '0'
row = newrow
cases.append([
WriteOnlyCell(ws=data_sheet, value=row[TIME_STAMP]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_REVISION]),
WriteOnlyCell(ws=data_sheet, value=row[PMDV_REVISION]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_ADDRESS]),
WriteOnlyCell(ws=data_sheet, value=row[PMDV_ADDRESS]),
WriteOnlyCell(ws=data_sheet, value=inj_asp),
WriteOnlyCell(ws=data_sheet, value=contrast_asp),
WriteOnlyCell(ws=data_sheet, value=replacement),
WriteOnlyCell(ws=data_sheet, value=row[DYEVERT_DIAMETER]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_DIAMETER]),
WriteOnlyCell(ws=data_sheet,
value=row[STARTING_SYRINGE_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[ENDING_SYRINGE_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[LINEAR_SYRINGE_MOVEMENT]),
WriteOnlyCell(
ws=data_sheet,
value=row[SYRINGE_VOLUME_INJECTED_OR_ASPIRATED]),
WriteOnlyCell(ws=data_sheet,
value=row[STARTING_DYEVERT_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[ENDING_DYEVERT_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[LINEAR_DYEVERT_MOVEMENT]),
WriteOnlyCell(ws=data_sheet,
value=row[DIVERT_VOLUME_DIVERTED]),
WriteOnlyCell(ws=data_sheet,
value=round(
row[DYEVERT_CONTRAST_VOLUME_DIVERTED],
2)), perc_saved,
WriteOnlyCell(ws=data_sheet,
value=row[INJECTION_VOLUME_TO_PATIENT]),
WriteOnlyCell(ws=data_sheet,
value=round(row[CONTRAST_VOLUME_TO_PATIENT],
2)),
WriteOnlyCell(
ws=data_sheet,
value=round(row[CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT],
2)),
WriteOnlyCell(ws=data_sheet,
value=row[OTHER_VOLUME_TO_PATIENT]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_CONTRAST_PERCENT_IN_SYRINGE]),
WriteOnlyCell(
ws=data_sheet,
value=row[ENDING_CONTRAST_PERCENT_IN_SYRINGE]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_CONTRAST_PERCENT_IN_DYEVERT]),
WriteOnlyCell(
ws=data_sheet,
value=row[ENDING_CONTRAST_PERCENT_IN_DYEVERT]),
WriteOnlyCell(ws=data_sheet, value=row[DURATION]),
WriteOnlyCell(ws=data_sheet,
value=round(
float(row[FLOW_RATE_TO_FROM_SYRINGE]),
2)),
WriteOnlyCell(ws=data_sheet,
value=round(float(row[FLOW_RATE_TO_PATIENT]),
2)),
WriteOnlyCell(
ws=data_sheet,
value=row[PREDOMINANT_CONTRAST_LINE_PRESSURE]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_DYEVERT_STOPCOCK_POSITION]),
WriteOnlyCell(ws=data_sheet, value=row[IS_SYSTEM_PAUSED]),
WriteOnlyCell(ws=data_sheet, value=''),
WriteOnlyCell(
ws=data_sheet,
value=round(
float(row[CONTRAST_VOLUME_TO_PATIENT]) +
float(row[DYEVERT_CONTRAST_VOLUME_DIVERTED]), 2)),
puff_inj
])
cases[-1][29].font = Font(color=BLUE)
cases[-1][30].font = Font(color=BLUE)
print('Applying formatting', end='')
for case in range(len(cases)):
if case % 10000 == 0:
print('.', end='')
for cell in range(len(cases[case])):
_cell = cases[case][cell]
_cell.alignment = alignment
if cases[case][5].internal_value == 'INJ' and _cell != empty_cell:
if cases[case][19].internal_value == 0:
_cell.fill = yellow
def write_body(self):
# Collect the messages
messages = []
seen = set()
for f in self.po_files:
for msg in f.po_file:
# Has message
if not msg.msgid or msg.obsolete:
continue
if (msg.msgid, msg.msgctxt) not in seen:
messages.append((msg.msgid, msg.msgctxt))
seen.add((msg.msgid, msg.msgctxt))
# used to write the first columns
reference_po_file = self.po_files[0].po_file
# The rest of the rows
for msgid, msgctxt in messages:
row = []
# Message context
if self.has_message_context:
row.append(self.get_cell(msgctxt))
# Message id
row.append(self.get_cell(msgid, wrap=self.wrap_message_id))
msg = reference_po_file.find(msgid, msgctxt=msgctxt)
# Metadata comment columns
if self.has_comment_references:
data = []
if msg is not None:
for (entry, lineno) in msg.occurrences:
if lineno:
data.append("%s:%s" % (entry, lineno))
else:
data.append(entry)
if data:
row.append(
self.get_cell(", ".join(data),
wrap=self.wrap_comments,
shrink_to_fit=not self.wrap_comments))
else:
row.append(
self.get_cell(None,
wrap=self.wrap_comments,
shrink_to_fit=not self.wrap_comments))
if self.has_comment_source:
data = None
if msg is not None:
data = msg.comment
row.append(
self.get_cell(data,
wrap=self.wrap_comments,
shrink_to_fit=not self.wrap_comments))
if self.has_comment_translator:
data = None
if msg is not None:
data = msg.tcomment
row.append(
self.get_cell(data,
wrap=self.wrap_comments,
shrink_to_fit=not self.wrap_comments))
# Write the language rows, aka strings to translate
for f in self.po_files:
po_file = f.po_file
msg = po_file.find(msgid, msgctxt=msgctxt)
if msg is None:
row.append(
self.get_cell(None,
wrap=self.wrap_message_translation,
unlock=self.unlock_message_locale))
elif "fuzzy" in msg.flags:
# Weird case
cell = WriteOnlyCell(self.work_sheet, value=msg.msgstr)
cell.font = self.font_fuzzy
row.append(cell, unlock=self.unlock_message_locale)
else:
# Normal case
row.append(
self.get_cell(msg.msgstr,
wrap=self.wrap_message_translation,
unlock=self.unlock_message_locale))
self.work_sheet.append(row)
