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",
"２": "2",
"３": "3",
"４": "4",
"５": "5",
"６": "6",
"７": "7",
"８": "8",
"９": "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.')