def merge_ddl(db1, db2, sheet): db1 = db1.fillna('null') db2 = db2.fillna('null') gap = pd.merge(db1, db2, on = ['table_name','column_name'], how='outer') for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) continue #print(col[0]+":"+col[1]) if col[3] == col[8] and col[4] == col[9] \ and col[5] == col[10] and col[6] == col[11] : '''Don't compare postion col[2] == col[7] ''' gap = gap.drop(index) gap = gap.sort_values(by = ['table_name','column_name']) gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index+3,column=i+1).value = col[i] if i in (2,3,4,5,6): sheet.cell(row=index+3,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA") elif i in (7,8,9,10,11): sheet.cell(row=index+3,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E")
def merge_sp(db1, db2, sheet): db1 = db1.fillna('null') db2 = db2.fillna('null') gap = pd.merge(db1, db2, on = ['Type','name'], how='outer') for index, col in gap.iterrows(): if identify_backup_tables(str(col[0]).lower()): gap = gap.drop(index) sp_a = str(col[2]).replace('[','').replace(']','').replace('\r','').replace('\t','').replace('\n','').replace(' ','').upper() sp_b = str(col[3]).replace('[','').replace(']','').replace('\r','').replace('\t','').replace('\n','').replace(' ','').upper() if sp_a == sp_b: gap = gap.drop(index) gap = gap.fillna('null') gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index+2,column=i+1).value = col[i] if i == 2: sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA") elif i == 3: sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E") elif i == 4: sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="68AE59")
def write_info_to_xls(years, diff_year, path): wb = openpyxl.Workbook() sheet_index = 0 for year in years: wb.create_sheet(index=sheet_index, title=year) sheet_index = +1 sheet = wb.get_sheet_by_name(year) sheet.cell(row=1, column=1).fill = styles.PatternFill(fill_type='solid', fgColor="e26b0a") for i in range(4): sheet.cell(row=1, column=i + 2).fill = styles.PatternFill( fill_type='solid', fgColor="0070C0") sheet.column_dimensions['A'].width = 15 sheet.column_dimensions['B'].width = 20 sheet.column_dimensions['C'].width = 20 sheet.column_dimensions['D'].width = 20 sheet.column_dimensions['E'].width = 20 sheet['A1'] = '想看人数' sheet['B1'] = '上映日期' sheet['C1'] = '片名' sheet['D1'] = '类型' sheet['E1'] = '制片国家/地区' for i in range(len(diff_year[year])): sheet['A' + str(i + 2)] = diff_year[year][i]['num'] sheet['B' + str(i + 2)] = diff_year[year][i]['date'] sheet['C' + str(i + 2)] = diff_year[year][i]['name'] sheet['D' + str(i + 2)] = diff_year[year][i]['type'] sheet['E' + str(i + 2)] = diff_year[year][i]['region'] wb.save(path)
def formatting(obj, type_formatting): global wbf1, wbf2, ws1, ws2 wbf1 = openpyxl.load_workbook('E:\\8_apr.xlsx') wbf2 = openpyxl.load_workbook('E:\\27_yanvarya.xlsx') ws1 = wbf1['Лист1'] ws2 = wbf2['Лист1'] yellowFill = styles.PatternFill(fill_type='solid', start_color=styles.colors.YELLOW, end_color=styles.colors.YELLOW) greenFill = styles.PatternFill(fill_type='solid', start_color=styles.colors.GREEN, end_color=styles.colors.GREEN) redFill = styles.PatternFill(fill_type='solid', start_color=styles.colors.RED, end_color=styles.colors.RED) if type_formatting == 'Yellow': ws1['{}'.format(case_finding(obj, ws1))].fill = yellowFill ws2['{}'.format(case_finding(obj, ws2))].fill = yellowFill elif type_formatting == 'Green': ws1['{}'.format(case_finding(obj, ws1))].fill = greenFill ws2['{}'.format(case_finding(obj, ws2))].fill = greenFill elif type_formatting == 'Red': ws1['{}'.format(case_finding(obj, ws1))].fill = redFill ws2['{}'.format(case_finding(obj, ws2))].fill = redFill wbf1.save('test1.xlsx') wbf2.save('test2.xlsx')
def merge_view(dev, qa, uat, sheet, prod='none'): dev = dev.fillna('null') qa = qa.fillna('null') uat = uat.fillna('null') gap = pd.merge(dev, qa, on=['Type', 'name'], how='outer') gap = pd.merge(gap, uat, on=['Type', 'name'], how='outer') if isinstance(prod, str): for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) col[2] = str(col[2]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') col[3] = str(col[3]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') col[4] = str(col[4]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') if col[2] == col[3] == col[4]: gap = gap.drop(index) else: prod = prod.fillna('null') gap = pd.merge(gap, prod, on=['Type', 'name'], how='outer') for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) col2 = str(col[2]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') col3 = str(col[3]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') col4 = str(col[4]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') col5 = str(col[5]).replace('[', '').replace(']', '').replace( '\r', '').replace('\t', '').replace('\n', '').replace(' ', '') if col2 == col3 == col4 == col5: gap = gap.drop(index) gap = gap.fillna('null') gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index + 2, column=i + 1).value = col[i] if i == 2: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="C28EEA") elif i == 3: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="DAC45E") elif i == 4: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="68AE59") elif i == 5: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="5B7190")
def merge_ddl(dev, qa, uat, sheet, prod='none'): dev = dev.fillna('null') qa = qa.fillna('null') uat = uat.fillna('null') gap = pd.merge(dev, qa, on=['table_name', 'column_name'], how='outer') gap = pd.merge(gap, uat, on=['table_name', 'column_name'], how='outer') if isinstance(prod, str): for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) if col[3] == col[8] == col[13] and col[4] == col[9] == col[14] \ and col[5] == col[10] == col[15] and col[6] == col[11] == col[16]: '''Don't compare postion col[2] == col[7] == col[12] and ''' gap = gap.drop(index) else: prod = prod.fillna('null') gap = pd.merge(gap, prod, on=['table_name', 'column_name'], how='outer') for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) if col[3] == col[8] == col[13] == col[18] and col[4] == col[9] == col[14] == col[19] \ and col[5] == col[10] == col[15] == col[20] and col[6] == col[11] == col[16] == col[21]: '''Don't compare postion col[2] == col[7] == col[12] == col[17] and''' gap = gap.drop(index) gap = gap.sort_values(by=['table_name', 'column_name']) gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index + 3, column=i + 1).value = col[i] if i in (2, 3, 4, 5, 6): sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="C28EEA") elif i in (7, 8, 9, 10, 11): sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="DAC45E") elif i in (12, 13, 14, 15, 16): sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="68AE59") elif i in (17, 18, 19, 20, 21): sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="5B7190")
def form_cell(row, column, value): ws.merge_cells(start_row=row, end_row=row, start_column=column, end_column=6) target_cell = ws.cell(row=row, column=column, value=value) font = styles.Font(color='FFFFFF', bold=True) fill = styles.PatternFill(fill_type='solid', fgColor='0076CE') target_cell.fill = fill target_cell.font = font
def check(self, index): #形式不備の自動チェック file_name = "./【高林様】抽出依頼リスト - コピー.xlsx" book = px.load_workbook(file_name) fill_yel = pxstyle.PatternFill(patternType='solid', fgColor='FFFF00', bgColor='FFFF00') sheet = book.worksheets[0] #社名 if sheet["D" + str(index)].value == None: sheet["D" + str(index)].fill = fill_yel #TEL try: match = re.match('^0\d{2,3}-\d{1,4}-\d{4}$', sheet["E" + str(index)].value) except: match = False if match: print("No." + str(index) + "TEL number is OK") else: sheet["E" + str(index)].fill = fill_yel #住所(県名) if sheet["F" + str(index)].value == None: sheet["F" + str(index)].fill = fill_yel try: if " " in sheet["F" + str(index)].value: sheet["F" + str(index)].value = replace(" ", "") if " " in sheet["F" + str(index)].value: sheet["F" + str(index)].value = replace(" ", "") match = re.match('東京都|北海道|(?:京都|大阪)府|.{2,3}県', sheet["F" + str(index)].value) if match: print("No." + str(index) + "prefecture name is OK") else: sheet["F" + str(index)].fill = fill_yel except: pass #住所の数字を半角へ変換 if sheet["F" + str(index)].value == None: sheet["F" + str(index)].fill = fill_yel try: if " " in sheet["F" + str(index)].value: sheet["G" + str(index)].value = replace(" ", "") if " " in sheet["F" + str(index)].value: sheet["G" + str(index)].value = replace(" ", "") trans_table = str.maketrans({ "1": "1", "2": "2", "3": "3", "4": "4", "5": "5", "6": "6", "7": "7", "8": "8", "9": "9" }) text = sheet["G" + str(index)].value text.translate(trans_table) except: pass book.save(file_name)
def test_to_excel_styleconverter(ext): from openpyxl import styles hstyle = { "font": {"color": "00FF0000", "bold": True}, "borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"}, "alignment": {"horizontal": "center", "vertical": "top"}, "fill": {"patternType": "solid", "fgColor": {"rgb": "006666FF", "tint": 0.3}}, "number_format": {"format_code": "0.00"}, "protection": {"locked": True, "hidden": False}, } font_color = styles.Color("00FF0000") font = styles.Font(bold=True, color=font_color) side = styles.Side(style=styles.borders.BORDER_THIN) border = styles.Border(top=side, right=side, bottom=side, left=side) alignment = styles.Alignment(horizontal="center", vertical="top") fill_color = styles.Color(rgb="006666FF", tint=0.3) fill = styles.PatternFill(patternType="solid", fgColor=fill_color) number_format = "0.00" protection = styles.Protection(locked=True, hidden=False) kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle) assert kw["font"] == font assert kw["border"] == border assert kw["alignment"] == alignment assert kw["fill"] == fill assert kw["number_format"] == number_format assert kw["protection"] == protection
def TOTALSCONDITIONALSTYLE(worksheet, cellrange, secondcolumn, firstrow): lessthanrule = xlformatrule.FormulaRule(formula=[ f"{xlutils.get_column_letter(secondcolumn)}{firstrow}<{xlutils.get_column_letter(secondcolumn-1)}{firstrow}" ], fill=xlstyle.PatternFill( fill_type="solid", start_color="DEEBF6", end_color="DEEBF6")) worksheet.conditional_formatting.add(cellrange, lessthanrule) greaterthanrule = xlformatrule.FormulaRule(formula=[ f"{xlutils.get_column_letter(secondcolumn)}{firstrow}>{xlutils.get_column_letter(secondcolumn-1)}{firstrow}" ], fill=xlstyle.PatternFill( fill_type="solid", start_color="FFF3CB", end_color="FFF3CB")) worksheet.conditional_formatting.add(cellrange, greaterthanrule)
def stylesPatternfill(self, color=None): ''' 设置单元格样式 :param color: 单元格颜色 :return: ''' self.fill = styles.PatternFill(fill_type='solid', start_color=color) return self.fill
def operate_excel2(workbook,sheet,index,cols,value,flag): font = Font(color=RED) fill = sty.PatternFill(fill_type="solid", fgColor="FFF68F") sheet.cell(index,cols).font = font if flag: sheet.cell(index, cols).fill = fill sheet.cell(index, cols).value = value workbook.save(fileName)
def init_ws(ws): fill = sty.PatternFill(fill_type='solid', fgColor="20b2aa") ws.cell(row=1, column=1, value='时间').fill = fill ws.cell(row=1, column=2, value='类型').fill = fill ws.cell(row=1, column=3, value='数据库').fill = fill ws.cell(row=1, column=4, value='表').fill = fill ws.cell(row=1, column=5, value='回滚SQL').fill = fill ws.cell(row=1, column=6, value='执行SQL').fill = fill ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 7
def write(self, **kwargs): sheet = self.Sheet if "sheet" in kwargs: sheet = kwargs["sheet"] sheet.cell(row=self.Row, column=self.Column).value = self.Nickname # стиль ячейки не может быть изменён, только перезаписан fill_pattern = excel_styles.PatternFill( patternType='solid', fgColor=excel_styles.Color(CodesToColor[self.PrevCode])) sheet.cell(row=self.Row, column=self.Column).fill = fill_pattern
def insert_clo(row2List,workbook,sheet): index = 0 fill = sty.PatternFill(fill_type="solid", fgColor="FFF68F") for i in range(len(row2List)): if '枚举值描述' == row2List[i]: index = i sheet.insert_cols(index+1)#插入一列 for i, row in enumerate(sheet.rows): row[index].fill = fill if i == 1: row[index ].value = u'上报值' workbook.save(fileName)
def test_to_excel_styleconverter(ext): from openpyxl import styles hstyle = { "font": { "color": '00FF0000', "bold": True, }, "borders": { "top": "thin", "right": "thin", "bottom": "thin", "left": "thin", }, "alignment": { "horizontal": "center", "vertical": "top", }, "fill": { "patternType": 'solid', 'fgColor': { 'rgb': '006666FF', 'tint': 0.3, }, }, "number_format": { "format_code": "0.00" }, "protection": { "locked": True, "hidden": False, }, } font_color = styles.Color('00FF0000') font = styles.Font(bold=True, color=font_color) side = styles.Side(style=styles.borders.BORDER_THIN) border = styles.Border(top=side, right=side, bottom=side, left=side) alignment = styles.Alignment(horizontal='center', vertical='top') fill_color = styles.Color(rgb='006666FF', tint=0.3) fill = styles.PatternFill(patternType='solid', fgColor=fill_color) number_format = '0.00' protection = styles.Protection(locked=True, hidden=False) kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle) assert kw['font'] == font assert kw['border'] == border assert kw['alignment'] == alignment assert kw['fill'] == fill assert kw['number_format'] == number_format assert kw['protection'] == protection
def update_storage(): table = self.sheet_storage indices = self.get_indices(table) for row in range(2, table.max_row + 1): table.cell(row, col_storage).value = self.data_storage[table.cell( row, indices['序号']).value]['storage'] for row in range(2, self.sheet_storage.max_row + 1): num_storage = self.sheet_storage.cell(row, col_storage).value num_storage = 0 if not num_storage else int(num_storage) if num_storage <= 0: for col in range(1, self.sheet_storage.max_column + 1): self.sheet_storage.cell(row, col).fill = sty.PatternFill( patternType='solid', fgColor="fa8072") else: for col in range(1, self.sheet_storage.max_column + 1): self.sheet_storage.cell( row, col).fill = sty.PatternFill(fgColor="ffffff") self.f_product.save(self.path + '/product.xlsx') print("已更新今日最新库存")
def cria_planilha_de_erros(self) -> None: workbook: Workbook = Workbook() ws = workbook.active ws.title = 'Erros' cabecalho = ws.cell(row=1, column=1, value='Erros encontrados no processamento da planilha') cabecalho.fill = styles.PatternFill('solid', fgColor='808080') for index, erro in enumerate(self.erros, 2): ws.cell(row=index, column=1, value=erro) filename = f'arquivo_resultado_{self.arquivo.pk}.xlsx' with NamedTemporaryFile() as tmp: workbook.save(tmp.name) self.arquivo.resultado.save(name=filename, content=File(tmp))
def write_excel(ws, row, list): ll = len(list) l = list[ll - 1] sql = '' back_sql = '' fill = sty.PatternFill(fill_type='solid', fgColor="e32636") if (list[1] == "DELETE"): sql = "DELETE FROM `" + list[ll - 3] + "`.`" + list[ll - 2] + "` WHERE " sql = for_fun(l, sql, 3, " AND ", ";", list) back_sql = sql.replace("DELETE FROM", "INSERT INTO") \ .replace("WHERE", "VALUES (") \ .replace("AND", ",") \ .replace(";", ");") if (list[1] == "INSERT"): fill = sty.PatternFill(fill_type='solid', fgColor="4de680") sql = "INSERT INTO `" + list[ll - 3] + "`.`" + list[ll - 2] + "` VALUES (" sql = for_fun(l, sql, 3, " , ", ");", list) back_sql = sql.replace("INSERT INTO", "DELETE FROM") \ .replace("VALUES (", "WHERE ") \ .replace(",", "AND") \ .replace(");", ";") if (list[1] == "UPDATE"): sql = "UPDATE `" + list[ll - 3] + "`.`" + list[ll - 2] + "` SET " sql = for_fun(l, sql, l + 4, " , ", "", list) sql = sql + " WHERE " sql = for_fun(l, sql, 3, " AND ", ";", list) back_sql = "UPDATE `" + list[ll - 3] + "`.`" + list[ll - 2] + "` SET " back_sql = for_fun(l, back_sql, 3, " , ", "", list) back_sql = back_sql + " WHERE " back_sql = for_fun(l, back_sql, l + 4, " AND ", ";", list) fill = sty.PatternFill(fill_type='solid', fgColor="f28500") ws.cell(row=row, column=1, value='%s' % list[0]) ws.cell(row=row, column=2, value='%s' % list[1]).fill = fill ws.cell(row=row, column=3, value='%s' % list[ll - 3]) ws.cell(row=row, column=4, value='%s' % list[ll - 2]) ws.cell(row=row, column=5, value='%s' % back_sql) ws.cell(row=row, column=6, value='%s' % sql)
def merge(dev, qa, sheet, key_column): dev = dev.fillna('null') qa = qa.fillna('null') gap = pd.merge(dev, qa, on=key_column, how='outer') for index, col in gap.iterrows(): if str(col[1]) == 'nan' or str(col[2]) == 'nan': pass else: gap = gap.drop(index) gap = gap.sort_values(by=key_column) gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index + 2, column=i + 1).value = col[i] if i == 1: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="C28EEA") elif i == 2: sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill( fill_type='solid', fgColor="DAC45E")
def __color_row(sheet: opx.worksheet.worksheet.Worksheet, row: int, color: str, fill_type: Optional[str] = "solid") -> None: """ Color the row with desire color :param sheet: Sheet where the row will be colored :param row: Row to be colored accessed with cell.row :param color: Color in hexadecimal notation :param fill_type: How the row will be colored :return: None """ for row_cells in sheet.iter_cols(1, sheet.max_column, row, row): for cell in row_cells: cell.fill = opx_style.PatternFill(start_color=color, end_color=color, fill_type=fill_type)
def update_backup(): table_backup_today = self.f_backup[self.sheet_date] self.copy_from_sheet_to_new(self.sheet_storage, table_backup_today, self.f_backup, '/backup.xlsx') for row in range(2, table_backup_today.max_row + 1): num_storage = table_backup_today.cell(row, col_storage).value num_storage = 0 if not num_storage else int(num_storage) if num_storage <= 0: for col in range(1, table_backup_today.max_column + 1): table_backup_today.cell(row, col).fill = sty.PatternFill( patternType='solid', fgColor="fa8072") self.f_backup.save(self.path + '/backup.xlsx') print("已备份今日最新库存")
def merge_index(db1, db2, sheet): db1 = db1.fillna('null') db2 = db2.fillna('null') gap = pd.merge(db1, db2, on = ['table_nm','index_nm'], how='outer') for index, col in gap.iterrows(): if identify_backup_tables(col[0].lower()): gap = gap.drop(index) continue if col[2] == col[3]: gap = gap.drop(index) gap = gap.fillna('null') gap = gap.reset_index(drop=True) for index, col in gap.iterrows(): for i in range(0, len(col), 1): sheet.cell(row=index+2,column=i+1).value = col[i] if i == 2: sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA") elif i == 3: sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E")
def _formatComparisonWorksheet(self, worksheet): for character in 'ABCDEFGHIJKLMNOPQRSTUVWXYZ': for index in range(1, len(self.snp_table)): cell_index = character + str(index) cell = worksheet[cell_index] print(cell) value = cell.value if value == '.': cell.fill = styles.Fill(bgColor = styles.Color('00000000'), fill_type = 'solid') elif value == 'X': cell.fill = styles.PatternFill(bgColor = styles.Color('12345678'), fill_type = 'solid') return worksheet
def create_document(): work_book = openpyxl.Workbook() work_sheet = work_book.active image = Image.open("task_pic.png") pixel_map = image.load() width, height = image.size for i in range(1, width): for j in range(1, height): cell = work_sheet.cell(row=j, column=i) pixel_color = pixel_map[i, j] color = "FF{0:02X}{1:02X}{2:02X}".format(*pixel_color) cell.fill = styles.PatternFill(fill_type="solid", start_color=color, end_color=color) if (i * height + j) % 1000 == 0: progress_bar((i * height + j) / (width * height)) work_book.save("task3.xlsx")
def write_sheet_by_df(self, dataset, rgb=None, sheet_name=""): """ 写入excel,并可以通过rgb参数设置背景色 :param dataset: :param rgb: :param sheet_name: :return: """ ws = self.get_sheet(sheet_name) for data in dataset: row = ws.max_row ws.append(data) if rgb: for i in range(1, len(data) + 1): ws.cell(row=row + 1, column=i).fill = style.PatternFill( fill_type='solid', fgColor=rgb)
def exportar_planilha_importacao_usuarios_perfil_codae(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_CODAE.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'CODAE' headers = [ 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', 'Número CRN' ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"COORDENADOR_GESTAO_ALIMENTACAO_TERCEIRIZADA,' 'COORDENADOR_DIETA_ESPECIAL,' 'COORDENADOR_SUPERVISAO_NUTRICAO,' 'COORDENADOR_GESTAO_PRODUTO"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('G2:G1048576') workbook.save(response) return response
def exportar_planilha_importacao_usuarios_perfil_escola(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_ESCOLA.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'ESCOLA' headers = [ 'Cód. EOL da U.E', 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"DIRETOR, DIRETOR CEI"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('H2:H1048576') workbook.save(response) return response
def diff(filepath1, filepath2): #flag=True yellowfill = styles.PatternFill(fgColor='FFFF00', fill_type='solid') #设置颜色 wb1 = load_workbook(filepath1) ws1 = wb1.active wb2 = load_workbook(filepath2) #print(wb2.sheetnames) ws2 = wb2.active row1 = [] row2 = [] for row in ws1.iter_rows(): row1.append(row) for row in ws2.iter_rows(): row2.append(row) if len(row1) != len(row2): print('数据表行数不一致,分别为:%d,%d' % (len(row1), len(row2))) return for i in range(len(row1) - 1): for j in range(len(row1[0]) - 1): if row1[i][j].value != row2[i][j].value: try: value1 = float(row1[i][j].value) value2 = float(row2[i][j].value) if abs(value1 - value2) > 0.01: print('第%d行第%d列数据不一致' % (i, j)) print(row1[i][j].value, row2[i][j].value) ws1.cell(row=i + 1, column=j).fill = yellowfill # 不一样就 ws2.cell(row=i + 1, column=j).fill = yellowfill except: print('第%d行第%d列数据不一致' % (i, j)) print(row1[i][j].value, row2[i][j].value) ws1.cell(row=i + 1, column=j).fill = yellowfill ws2.cell(row=i + 1, column=j).fill = yellowfill #if not flag: #return wb1.save(filepath1) wb2.save(filepath2)
def main(): yellow_color = 'FFFF00' yellow_fill = styles.PatternFill(start_color=yellow_color, end_color=yellow_color, fill_type='solid') dxf = styles.differential.DifferentialStyle(fill=yellow_fill) duplicate_rule = formatting.Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None) sheet_path = r'K:\Morfeus\BMAnderson\CNN\Data\Data_Liver\DicomInfo.xlsx' paths = [ r'K:\Morfeus\BMAnderson\CNN\Data\Data_Liver\Miccai_Challenge', r'K:\Morfeus\BMAnderson\CNN\Data\Data_Liver\Data' ] wb = return_workbook(sheet_path=sheet_path) for path in paths: add_patients_from_path(path=path, wb=wb) sheet1 = wb.active n = sheet1.max_row sheet1.conditional_formatting.add('A2:A' + str(n), duplicate_rule) wb.save(sheet_path) print('Complete spreadsheet saved.')