def write_items(sheet, files, row_start):
font12 = Styles.Font(
name=FAMILY2,
size=12,
)
font14 = Styles.Font(name=FAMILY2, size=14)
align1 = Styles.Alignment(horizontal="center", vertical="center")
align2 = Styles.Alignment(horizontal="left", vertical="center")
for f in files:
for col in range(1, 7):
sheet.cell(row=row_start, column=col).border = thin_border
cell = sheet.cell(row=row_start, column=1)
cell.font = font12
cell.alignment = align2
cell.value = row_start - 3
cell = sheet.cell(row=row_start, column=2)
cell.font = font14
cell.alignment = align2
cell.value = f[1]
cell = sheet.cell(row=row_start, column=3)
cell.font = font14
cell.alignment = align2
cell.value = f[0]
row_start += 1
def color(self, value):
if value == "FAIL":
return styles.Font(color=styles.colors.RED)
elif value == "PASS":
return styles.Font(color=styles.colors.GREEN)
else:
return
def generate_block_sheet(self, workbook):
worksheet = workbook.create_sheet(title="{}".format(self.block.name))
self.generate_dates()
headers = ['Regd. No.', 'Name'] + [
date.strftime("%d/%m/%y") for date in self.generated_dates
]
row_num = 1
for col_num, column_title in enumerate(headers, 1):
cell = worksheet.cell(row=row_num, column=col_num)
cell.font = styles.Font(bold=True)
cell.value = column_title
for attendance in self.attendance_list:
row_num += 1
row_data = [attendance.student.regd_no, attendance.student.name
] + self.get_student_attendance(attendance)
for col_num, cell_value in enumerate(row_data, 1):
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = cell_value
if cell_value == 'P': cell.font = styles.Font(color='28A745')
elif cell_value == 'A':
cell.font = styles.Font(bold=True, color='DC3545')
def diff_export_excel(request):
sel_date_start=request.GET['date_start']
sel_date_end=request.GET["date_end"]
diff = datadiff.objects\
.exclude(amount=0)\
.exclude(id_shop__sName__in=['元隆利嘉生活馆','满洲里友谊商厦'])\
.filter(id_shop__shopType__in=["D", "C"], date__range=(sel_date_start,sel_date_end))\
.order_by("id_shop", "date")
# 创建一个excel表格对象
F_styleB=styles.Font(color=styles.colors.BLACK,bold=True)
F_styleR=styles.Font(color=styles.colors.RED,bold=True,italic=True)
F_styleCenter=styles.Alignment(horizontal='center',vertical='center',indent=1,readingOrder=2,text_rotation=0)
diff_excel_book = openpyxl.Workbook()
diff_excel_book.remove_sheet(diff_excel_book.get_sheet_by_name(u'Sheet'))
book_sheet = diff_excel_book.create_sheet(u"销售差异")
book_sheet.append([u"店铺",u'销售经理', u"日期", u"系统金额", u"上报金额", u"差异金额",u'正确金额', u"差异原因", u"备注"])
for i in range(1,8):
book_sheet.cell(row=1,column=i).font=F_styleB
row=2
for diff_y in diff:
book_sheet.cell(row=row, column=1).value = diff_y.id_shop.sysName
book_sheet.cell(row=row, column=1).alignment=F_styleCenter
book_sheet.cell(row=row, column=2).value = diff_y.id_shop.managerId.name
book_sheet.cell(row=row, column=2).alignment = F_styleCenter
book_sheet.cell(row=row, column=3).value = diff_y.date
book_sheet.cell(row=row, column=3).alignment = F_styleCenter
book_sheet.cell(row=row, column=4).value = diff_y.sys_amount
book_sheet.cell(row=row, column=4).alignment = F_styleCenter
book_sheet.cell(row=row, column=5).value = diff_y.shop_amount
book_sheet.cell(row=row, column=5).alignment = F_styleCenter
book_sheet.cell(row=row, column=6).value = diff_y.amount
book_sheet.cell(row=row, column=6).alignment = F_styleCenter
book_sheet.cell(row=row, column=7).value = diff_y.true_amount
if diff_y.true_amount!=diff_y.shop_amount:
book_sheet.cell(row=row, column=7).font = F_styleR
book_sheet.cell(row=row, column=7).alignment = F_styleCenter
book_sheet.cell(row=row, column=8).value = diff_y.diff
book_sheet.cell(row=row, column=8).alignment=F_styleCenter
book_sheet.cell(row=row, column=9).value = diff_y.remark
book_sheet.cell(row=row, column=9).alignment = F_styleCenter
row+=1
month_start = str(sel_date_start).replace('-','')
month_end = str(sel_date_end).replace('-','')
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=销售差异%s-%%s.xlsx'%month_start %month_end
diff_excel_book.save(response)
return response
def remark(self, number, line, row):
"""填写备注"""
info = self.sheet[row + str(number)].comment
temp = re.search(r"考勤异常,上班打卡时间.*,下班打卡时间.*,迟到(\d+)小时(\d+)分钟",
info.content) # 提取迟到时间的正则
hour = temp.group(1) # 提取迟到小时
minute = temp.group(2) # 提取迟到分钟
if int(hour) == 0:
if self.sheet['BI' + str(number)].value is not None:
self.sheet['BI' + str(number)].value = self.sheet['BI' + str(number)].value + \
str(datetime.datetime.now().month - 1) + \
"月" + str(line - 4) + "日" \
+ "迟到" + str(minute) + "分钟,"
self.check_time(number, hour, minute)
else:
self.sheet['BI' + str(number)].value = str(datetime.datetime.now().month - 1) + \
"月" + str(line - 4) + "日" \
+ "迟到" + str(minute) + "分钟,"
self.check_time(number, hour, minute)
else:
if self.sheet['BI' + str(number)].value is not None:
self.sheet['BI' + str(number)].value = self.sheet['BI' + str(number)].value + \
str(datetime.datetime.now().month - 1) + \
"月" + str(line - 4) + "日" \
+ "迟到" + str(hour) + "小时" + str(minute) + "分钟,"
self.check_time(number, hour, minute)
else:
self.sheet['BI' + str(number)].value = str(datetime.datetime.now().month - 1) + \
"月" + str(line - 4) + "日" \
+ "迟到" + str(hour) + "小时" + str(minute) + "分钟,"
self.check_time(number, hour, minute)
self.sheet['BI' + str(number)].font = styles.Font(name='宋体',
size=6,
bold=True)
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 do_low_hanging_fruit_analysis(self):
func_list = [
self.domain_user_admin, self.everyone_admin,
self.authenticated_users_admin, self.domain_users_control,
self.everyone_control, self.authenticated_users_control,
self.domain_users_rdp, self.everyone_rdp,
self.authenticated_users_dcom, self.domain_users_dcom,
self.everyone_dcom, self.authenticated_users_dcom,
self.shortest_acl_path_domain_users,
self.shortest_derivative_path_domain_users,
self.shortest_hybrid_path_domain_users,
self.shortest_acl_path_everyone,
self.shortest_derivative_path_everyone,
self.shortest_hybrid_path_everyone,
self.shortest_acl_path_auth_users,
self.shortest_derivative_path_auth_users,
self.shortest_hybrid_path_auth_users, self.kerberoastable_path_len,
self.high_admin_comps
]
sheet = self.workbook._sheets[2]
self.write_single_cell(sheet, 1, 1, "Domain Users to Domain Admins")
self.write_single_cell(sheet, 1, 2, "Everyone to Domain Admins")
self.write_single_cell(sheet, 1, 3,
"Authenticated Users to Domain Admins")
font = styles.Font(bold=True)
sheet.cell(1, 1).font = font
sheet.cell(1, 2).font = font
sheet.cell(1, 3).font = font
for f in func_list:
s = timer()
f(sheet)
print "{} completed in {}s".format(f.__name__, timer() - s)
def add_record_to_excel(data):
"""
Create a new record to excel file or add to existing file
"""
EXCEL_FILE = "dbmonitor.xlsx"
file_exists = path.exists(EXCEL_FILE)
if file_exists:
book = load_workbook(EXCEL_FILE)
ws = book.active
for item in data:
ws.append(list(item.values()))
else:
print("new file")
book = Workbook()
ws = book.active
dbs = []
header = list(data[0].keys()) # get the headers from the keys
ws.append(header)
row = ws.row_dimensions[1]
row.font = styles.Font(bold=True, size=13)
for item in data:
ws.append(list(item.values()))
book.save("dbmonitor.xlsx")
def write_headers(sheet):
print(sheet.title)
rd = sheet.row_dimensions
print(rd[0].height)
sheet.merge_cells("A1:G1",
start_row=1,
end_row=1,
start_column=1,
end_column=7)
title = sheet.cell(row=1, column=1)
title.font = Styles.Font(name=FAMILY1, size=25, bold=False)
title.alignment = Styles.Alignment(horizontal="center", vertical="center")
title.value = "长山收费站{}年{}收文登记薄".format(
datetime.datetime.fromtimestamp(time.time()).year, sheet.title)
sheet.merge_cells("A2:G2",
start_row=2,
end_row=2,
start_column=1,
end_column=7)
for i, item in enumerate(
["序号", "收文时间", "文件名称/标题", "发件单位/部门", "文件分类", "资料类型", "备注"]):
cell = sheet.cell(row=3, column=i + 1)
cell.alignment = Styles.Alignment(horizontal="center",
vertical="center")
cell.border = thin_border
cell.value = item
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 format_spreadsheet(ws):
# freeze top row
ws.freeze_panes = ws['A2']
# set top row to bold
# we can't address the whole row at once, gotta do cells.
# not going to bother with a content checker
top_row = [ws['A1'], ws['B1'], ws['C1'], ws['D1'], ws['E1']]
for r in top_row:
r.font = styles.Font(bold=True)
dims = {}
for row in ws.rows:
for cell in row:
# set length of cells to length of content, with 100 char limit.
if cell.value:
if len(cell.value) < 100:
cell_length = len(cell.value) + 1
else:
cell_length = 100
dims[cell.column] = max((dims.get(cell.column,
0), cell_length))
# Set alignment to wrap text, this preserves newlines.
cell.alignment = styles.Alignment(wrap_text=True)
for col, value in dims.items():
ws.column_dimensions[col].width = value
return ws
def matrix_to_excel(path, core_matrix, names):
from openpyxl import styles
from openpyxl.utils import get_column_letter
names = [name for name in names if name in core_matrix]
writer = pd.ExcelWriter(path, engine="openpyxl")
core_matrix.to_excel(writer)
ws = writer.sheets["Sheet1"]
rotated = styles.Alignment(textRotation=45, horizontal="center", vertical="bottom")
bottom = styles.Alignment(vertical="bottom")
for row in ws["A1:ZZ1"]:
for cell in row:
if cell.value in names:
cell.alignment = rotated
ws.column_dimensions[get_column_letter(cell.column)].width = 2.6
elif cell.value == "title":
cell.alignment = bottom
ws.column_dimensions[get_column_letter(cell.column)].width = 40
else:
cell.alignment = bottom
for row in ws["A1:A1000"]:
for cell in row:
if not cell.value:
continue
cell.hyperlink = "https://github.com/" + "/pull/".join(
cell.value.split("#")
)
cell.alignment = styles.Alignment(horizontal="right")
cell.font = styles.Font(color="0000ff")
ws.freeze_panes = "C2"
writer.close()
def to_igschel_hiso(model, name):
input_keys = input_keys_template
if features == 6:
del input_keys[1]
wb = Workbook()
ws = wb.active
for i, x in enumerate(range(features)):
ws.cell(row=i + 1, column=1).value = input_keys[i]
ws.cell(row=i + 1, column=2).value = 1
for x in range(hidden):
cell = chr(ord("A") + x)
ws.cell(row=x + 1, column=4).value = \
"=SUMPRODUCT(B1:B{features}, weights0!{cell}1:{cell}{features}) + weights1!A{idx}" \
.format(cell=cell, features=features, idx=x + 1)
for x in range(outputs):
cell = chr(ord("A") + x)
ws.cell(row=x + 1, column=6).value = 2**x
ws.cell(row=x + 1, column=7).value = \
"=SUMPRODUCT(D1:D{hidden}, weights2!{cell}1:{cell}{hidden}) + weights3!A{idx}" \
.format(cell=cell, hidden=hidden, idx=x + 1)
rule = ColorScaleRule(start_type='percentile',
start_value=40,
start_color='ffb6d7a8',
mid_type='percentile',
mid_value=70,
mid_color='ff9fc5e8',
end_type='percentile',
end_value=95,
end_color='ffea9999')
bold = styles.Font(bold=True)
ws.conditional_formatting.add('D1:D{}'.format(hidden), rule)
ws.conditional_formatting.add('G1:G{}'.format(outputs), rule)
ws.conditional_formatting.add(
'G1:G{}'.format(outputs),
CellIsRule(operator='equal',
formula=['MAX(G$1:G${})'.format(outputs)],
font=bold))
w = model.get_weights()
for y in range(4):
weights = wb.create_sheet("weights{}".format(y))
wy = w[y]
shape = wy.shape
if len(shape) == 2:
for ix, iy in np.ndindex(shape):
weights.cell(row=ix + 1, column=iy + 1).value = float(wy[ix,
iy])
else:
for ix in range(shape[0]):
weights.cell(row=ix + 1, column=1).value = float(wy[ix])
wb.save(name + ".xlsx")
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 write_column_data(self, sheet, title, results):
count = len(results)
offset = 1
font = styles.Font(bold=True)
c = sheet.cell(offset, self.col_count)
c.font = font
sheet.cell(offset, self.col_count, value=title.format(count))
for i in xrange(0, count):
sheet.cell(i + offset + 1, self.col_count, value=results[i])
self.col_count += 1
def check_time2(self, number, letter):
"""在对应迟到的表格内填上迟到次数"""
if self.sheet[letter + str(number)].value is None:
self.sheet[letter + str(number)].value = 1
else:
self.sheet[letter + str(number)].value = int(
self.sheet[letter + str(number)].value) + 1
self.sheet[letter + str(number)].font = styles.Font(name='宋体',
size=6,
bold=True)
def modify_cell_format(save_dir):
wt=load_workbook(save_dir)
sheet=wt.get_sheet_by_name('Sqldata')
sheet.column_dimensions['A'].width =40
sheet.column_dimensions['B'].width =25
sheet.column_dimensions['C'].width =150
size_font=styles.Font(size=12,bold=True)
colums=sheet.max_column
for i in range(1,colums+1):
sheet.cell(row=1,column=i).font=size_font
#sheet.column_dimensions[1].font=size_font
wt.save(save_dir)
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 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 stylestFout(self, size, bold=None, color="FF010701"):
'''
单元格字体的设置
:param size: 字体大小 int
:param bold: 是否加粗,默认为None,需要传 True
:param color: 颜色默认黑色,需要传入其他
:return: 无
'''
self.font = styles.Font(name=u'微软雅黑',
size=size,
bold=bold,
color=color)
return self.font
def reformatExcel(readDict, destFile):
wb = opxl.Workbook()
ws = wb.active
HeadFt = opst.Font(bold=True)
HeadAlign = opst.Alignment(horizontal='center', vertical='center')
flatFt = opst.Font(color='FF00FF00')
slope_epsilon= 0.0005
#Prepare header rows
ws['A1'].value = 'Temperature'
ws['A1'].font = HeadFt
ws['A1'].alignment = HeadAlign
ws.column_dimensions['A'].width = 20
columnIterator = 2
for wellpos in readDict.keys():
# fix this
ws[oput.get_column_letter(columnIterator)+'1'].value = wellpos
ws[oput.get_column_letter(columnIterator) + '1'].font = HeadFt
ws[oput.get_column_letter(columnIterator) + '1'].alignment = HeadAlign
columnIterator = columnIterator+1
#Preparing the temperature column
rowIterator = 2
firstWell = list(readDict.keys())[0]
for temp in readDict[firstWell].keys():
ws['A'+str(rowIterator)].value = temp
ws['A' + str(rowIterator)].alignment = HeadAlign
rowIterator = rowIterator+1
columnIterator=2
for well in readDict.keys():
rowIterator=2
tempList= list(readDict[well].keys())
for temp in tempList:
ws[oput.get_column_letter(columnIterator)+str(rowIterator)].value = readDict[well][temp]
rowIterator = rowIterator+1
columnIterator = columnIterator+1
wb.save(filename=destFile)
def _format_report_title(ws_report):
"""
Форматирование отчета
:param ws_report:
:return:
"""
# форматирование заголовков
for char in "ABCDEFGH":
ws_report.column_dimensions[char].width = 30
ws_report[char + '1'].font = style.Font(bold=True, size=13)
ws_report[char + '1'].alignment = style.Alignment(wrap_text=True)
# форматирование столбца с D
for index in range(2, ws_report.max_row + 1):
_cell = ws_report[f'D{index}']
_cell.number_format = '[h]:mm:ss'
def __add_font(cell: opx.worksheet.worksheet.Cell,
font_name: str,
font_size: int,
font_color: str,
font_bold: Optional[bool] = False) -> None:
"""
Add font to the excel cell
:param cell: Cell to be given a new font
:param font_name: Name of a font
:param font_size: Size of a font
:param font_color: Color of a font
:param font_bold: True if a font should be bold else False
:return: None
"""
cell.font = opx_style.Font(name=font_name, size=font_size, bold=font_bold, color=font_color)
def write_column(self, sheetNum, row, column, title, results):
try:
count = len(results)
sheet = self.workbook._sheets[sheetNum]
# Update title cell
font = styles.Font(bold=True)
titleCell = sheet.cell(row, column)
titleCell.font = font
sheet.cell(row, column, value=title.format(count))
# Update the rows
for i in range(0, count):
sheet.cell(i + row + 1, column, value=results[i])
except Exception as e:
logging.error('Failed to write column data.')
logging.error(e)
def do_front_page_analysis(self):
func_list = [self.create_node_statistics,
self.create_edge_statistics, self.create_qa_statistics]
sheet = self.workbook._sheets[0]
self.write_single_cell(sheet, 1, 1, "Node Statistics")
self.write_single_cell(sheet, 1, 2, "Edge Statistics")
self.write_single_cell(sheet, 1, 3, "QA Info")
font = styles.Font(bold=True)
sheet.cell(1, 1).font = font
sheet.cell(1, 2).font = font
sheet.cell(1, 3).font = font
for f in func_list:
s = timer()
f(sheet)
print "{} completed in {}s".format(f.__name__, timer() - s)
def setupBaseFormats():
# The common fonts borders and number_formats are set up here
return {
'fonts': {
'header':
opStyles.Font(color="444444", size=14, bold=True),
'memberHeader':
opStyles.Font(color="333388", size=12, bold=True),
'memberName':
opStyles.Font(color="333388", size=10, bold=True),
'memberValue':
opStyles.Font(color="000000", name='Courier', size=10, bold=False),
'boolean1':
opStyles.Font(color="000000", name='Courier', size=10, bold=True),
'boolean0':
opStyles.Font(color="888888", name='Courier', size=10, bold=False)
},
'borders': {
'thinBox':
op.styles.Border(left=op.styles.Side(style='thin'),
right=op.styles.Side(style='thin'),
top=op.styles.Side(style='thin'),
bottom=op.styles.Side(style='thin')),
'thickBottom':
op.styles.Border(
bottom=op.styles.Side(style='thick', color='000000')),
'thinBottom':
op.styles.Border(
bottom=op.styles.Side(style='thin', color='000000')),
'thinRight':
op.styles.Border(
right=op.styles.Side(style='thin', color='000000')),
'thinBottomBlue':
op.styles.Border(
bottom=op.styles.Side(style='thin', color='000088'))
},
'number_formats': {
'percentage': '0.00%'
}
}
'''
try:
val = self.uppercase[col - 1]
except Exception:
shang = col // 26 #取商值
yu = col % 26 #取余数
'''有bug'''
val = self.uppercase[shang - 1] + self.uppercase[yu]
return val
if __name__ == '__main__':
# print(excelReport.Gold)
wb = openpyxl.load_workbook("caogao.xlsx")
ws = wb["报告模板"]
ws.cell(1, 1).font = styles.Font(color="FF010701")
wb.save("caogao.xlsx")
# case = caseExcel()
# case.status("工作流接口测试用例.xlsx")
# print(case.excelDic)
# path = r'C:\Users\Administrator\Desktop\文档\博云BeyondDevOps测试用例.xlsx'
# wb = xlrd.open_workbook(path)
# sh = wb.sheet_by_name('POC测试用例')
# s = sh.cell_value(1,1)
# print(s)
# print(sh.nrows)
# print(sh.cell_value(1,1))
# a = caseExcel()
# a.status()
# for name in a.excelDic:
# for case in a.excelDic[name]:
from openpyxl import formatting, styles
wb = Workbook()
ws = wb.active
red_color = 'ffc7ce'
red_color_font = '9c0103'
red_font = styles.Font(size=14, bold=True, color=red_color_font)
red_fill = styles.PatternFill(start_color=red_color,
end_color=red_color,
fill_type='solid')
for row in range(1, 10):
ws.cell(row=row, column=1, value=row - 5)
ws.cell(row=row, column=2, value=row - 5)
ws.conditional_formatting.add(
'A1:A10',
formatting.CellIsRule(operator='lessThan',
formula=['0'],
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'B1:B10',
formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save("test.xlsx")
from openpyxl.styles import colors
from openpyxl.styles import fills
from rekall_lib import utils
from rekall.ui import renderer
from rekall.ui import text
import six
if six.PY3:
long = int
# pylint: disable=unexpected-keyword-arg,no-value-for-parameter
# pylint: disable=redefined-outer-name
HEADER_STYLE = styles.Style(font=styles.Font(bold=True))
SECTION_STYLE = styles.Style(
fill=styles.PatternFill(
fill_type=fills.FILL_SOLID, start_color=styles.Color(colors.RED)))
FORMAT_STYLE = styles.Style(
alignment=styles.Alignment(vertical="top", wrap_text=False))
class XLSObjectRenderer(renderer.ObjectRenderer):
"""By default the XLS renderer delegates to the text renderer."""
renders_type = "object"
renderers = ["XLSRenderer"]
STYLE = None
def _GetDelegateObjectRenderer(self, item):
def set_word_style(path, sheet_name):
wb = load_workbook(path)
ws = wb[sheet_name] # sheet_name=wb.sheetnames[0]
font1 = styles.Font(name=u'宋体', size=12, bold=True)
ws.cell(row=1, column=1).font = font1
wb.save(path)
