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)