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 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 process_article(worksheet, Article, EntryIndex):
logging.debug('found Article')
try:
Cell = 'D' + str(EntryIndex)
worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left',
vertical='top',
wrap_text=True,
shrink_to_fit=False)
JournalString = Article.find('Journal').find('Title').text
JournalString = JournalString + ", Vol {}".format(
Article.find('Journal').find('JournalIssue').find('Volume').text)
JournalString = JournalString + ", {}".format(
Article.find('Journal').find('JournalIssue').find('PubDate').find(
'Year').text)
JournalString = JournalString + ", {}".format(
Article.find('Journal').find('JournalIssue').find('PubDate').find(
'Month').text)
JournalString = JournalString + " {}".format(
Article.find('Journal').find('JournalIssue').find('PubDate').find(
'Day').text)
except AttributeError:
logging.debug('did not find Journal.Title or some subfields')
worksheet[Cell] = JournalString
try:
Cell = 'A' + str(EntryIndex)
worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left',
vertical='top',
wrap_text=True,
shrink_to_fit=False)
worksheet[Cell] = Article.find('ArticleTitle').text
except AttributeError:
worksheet[Cell] = 'NA'
logging.debug('did not find ArticleTitle')
try:
Cell = 'B' + str(EntryIndex)
worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left',
vertical='top',
wrap_text=True,
shrink_to_fit=False)
worksheet[Cell] = "{}, {}".format(
Article.find('AuthorList').find('Author').find('LastName').text,
Article.find('AuthorList').find('Author').find('Initials').text)
except AttributeError:
worksheet[Cell] = 'NA'
logging.debug('did not find ArticleTitle')
try:
Cell = 'C' + str(EntryIndex)
worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left',
vertical='top',
wrap_text=True,
shrink_to_fit=False)
worksheet[Cell] = Article.find('Abstract').find('AbstractText').text
except AttributeError:
worksheet[Cell] = 'NA'
logging.debug('did not find AbstractText')
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 process_pmid(worksheet, PMIDField, EntryIndex):
Cell = 'E' + str(EntryIndex)
worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left',
vertical='top',
wrap_text=True,
shrink_to_fit=False)
worksheet[Cell] = PMIDField.text
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 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 align_center():
for i in range(_ws.max_column+1):
if(i > 0):
for j in range( _ws.max_row+1):
if(j > 0):
cell = _ws.cell(row=j,column=i)
cell.alignment = __oxl_styles.Alignment(horizontal='center', vertical='center')
def stylesAlignment(self):
'''
设置字体样式,默认垂直
:return:
'''
self.Alignment = styles.Alignment(horizontal='center')
return self.Alignment
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 export_excel(kinders, header):
wb = Workbook()
ws = wb.active
sheet = wb['Sheet']
widths = [13, 15, 35, 16, 16, 9, 9, 9, 18]
sheet.row_dimensions[1].height = 30
for i, h in enumerate(header):
sheet.column_dimensions[chr(65 + i)].width = widths[i]
cell = ws.cell(row=1, column=i + 1)
cell.value = h
cell.alignment = styles.Alignment(horizontal='center',
vertical='center')
for row, data in enumerate(kinders, start=2):
for col, v in enumerate(data.values(), start=1):
cell = ws.cell(row=row, column=col)
cell.value = v
cell.alignment = styles.Alignment(horizontal='center')
wb.save('result.xlsx')
def make_workbook(self, gst, igst):
len_gst = len(gst)
len_igst = len(igst)
wb = Workbook()
x = datetime.datetime.strftime(self.start, '%d-%m-%Y')
y = datetime.datetime.strftime(self.end, '%d-%m-%Y')
c = Utils.current_time()
c = datetime.datetime.strftime(c, '%d-%m-%Y')
dest_filename = c + '_' + x + '_' + y + '_' + str(len_igst +
len_gst) + '.xlsx'
ws1 = wb.active
ws1.title = "GST"
for row in range(len_gst):
ws1.append(gst[row])
ws1['M' +
str(row + 1)].alignment = styles.Alignment(wrap_text=True)
wb["GST"].freeze_panes = "A2"
ws2 = wb.create_sheet('IGST')
for row in range(len_igst):
ws2.append(igst[row])
ws2['L' +
str(row + 1)].alignment = styles.Alignment(wrap_text=True)
wb["IGST"].freeze_panes = "A2"
wb.save("C:\\Users\\Dell\\Desktop\\" + dest_filename)
#Database.initialize()
#start = datetime.datetime.strptime('27-01-2019','%d-%m-%Y')
#end = datetime.datetime.strptime('27-08-2019','%d-%m-%Y')
#x = Excel_export(start,end).make_sheet()
#print(x.gst)
#print(x.igst)
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 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 write_price(self, change_root, wholesale_root):
#book = xlwt.Workbook()
#sheet = book.add_sheet('Sheet', cell_overwrite_ok=True)
#rb = xlrd.open_workbook('Copy of Hook Full Price List interns.xlsx')
#r_sheet = rb.sheet_by_index(4)
#book = copy(rb)
#sheet = book.get_sheet(4)
book = openpyxl.load_workbook('Hook Full Price List interns.xlsx')
#r_sheet = rb['A-Z INTERNAL']
#book = copy(rb)
sheet = book['A-Z INTERNAL']
final_list = []
self.to_database(change_root, final_list)
title = []
title.append(final_list.pop(0))
temp = format().quicksort(final_list, 1)
final_list = title + temp
colnum = 9
for i in range(len(final_list)):
provider = final_list.pop(0)
provider.pop(5)
provider.pop(5)
for j in range(colnum):
if i == 0:
#st = xlwt.easyxf('align: horiz center')
row = sheet.row_dimensions[1]
row.alignment = styles.Alignment(horizontal='center')
#for row in rows:
#cell = row[i][j]
#cell.alignment = Alignment(horizontal='center')
sheet.cell(row=i + 1, column=j + 1).value = provider[j + 1]
else:
sheet.cell(row=i + 1, column=j + 1).value = provider[j + 1]
sheet.freeze_panes = sheet['A2']
book.save('Pricing Sheet.xlsx')
log_file().info("Source Compiler.log",
'Pricing sheet has been updated.')
print('Pricing sheet has been updated.')
def __align(sheet: opx.worksheet.worksheet.Worksheet,
row: int,
start_col: int,
end_col: int,
horizontal_align: str = "center") -> None:
"""
Align row from start_col to end_col cells to some horizontal_align
:param sheet: Sheet where a row will be aligned
:param row: Row to be aligned accessed with cell.row
:param start_col: Starting column
:param end_col: Ending column
:param horizontal_align: How the row will be aligned
:return: None
"""
for row_cells in sheet.iter_cols(start_col, end_col, row, row):
for cell in row_cells:
cell.alignment = opx_style.Alignment(horizontal_align)
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)
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):
return self.ForTarget(item, "TextRenderer")(
session=self.session, renderer=self.renderer.delegate_text_renderer)
def RenderHeader(self, worksheet, column):
cell = worksheet.cell(
bottom=xl_styles.Side(border_style='thin', color='FF000000'),
# diagonal=xl_styles.Side(border_style=None,color='FF000000'),
# diagonal_direction=0,outline=xl_styles.Side(border_style=None,color='FF000000'),
# vertical=xl_styles.Side(border_style=None,color='FF000000'),
# horizontal=xl_styles.Side(border_style=None,color='FF000000')
)
border_tbl = dict(thin=thin_border, )
#################################################################
# Align Types #
# #
# If you add an align type, remember to add it to align_tbl #
#################################################################
wrap_center_alignment = xl_styles.Alignment(horizontal='center',
vertical='top',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
wrap_right_alignment = xl_styles.Alignment(horizontal='right',
vertical='top',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
wrap_alignment = xl_styles.Alignment(horizontal='general',
vertical='top',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
wrap_vert_center_alignment = xl_styles.Alignment(horizontal='center',
workbook.sheets = dict((ws.title, ws) for ws in book.worksheets)
sheet_name = date.today().strftime('prediction on %d %b %Y')
result.to_excel(
workbook,
sheet_name=sheet_name,
index=False) # don't show the row numbers
sheet = workbook.sheets[sheet_name]
# Set column widths. You have to change this if you add columns
col_widths = zip('ABCDEFGHIJ', (9, 16, 12, 20, 12, 20, 12, 16, 14, 14))
for col, width in col_widths:
sheet.column_dimensions[col].width = width
# Bold and center the headers
cell = col + "1"
try:
sheet[cell].font = styles.Font(bold=True)
sheet[cell].alignment = styles.Alignment(horizontal = 'center')
except TypeError:
sheet[cell].style.font = styles.fonts.Font()
sheet[cell].style.font_style = styles.fonts.Font.bold = True
sheet[cell].style.alignment = styles.alignment.Alignment()
sheet[cell].style.alignment_style = styles.alignment.Alignment.HORIZONTAL_CENTER
# Make a horizontal boundary between each week
try:
separator = styles.borders.Border(bottom=styles.borders.Side(style='thin'))
except AttributeError:
separator = styles.borders.Border()
separator.border_style = styles.borders.Border.BORDER_THIN
for row_offset in result[['Week','Home Team']].groupby('Week').count().cumsum().values:
for col_offset in range(result.shape[1]):
try:
sheet.cell( row=row_offset[0]+1, column=col_offset+1
def __init__(self,
filename=None,
model_dir=None,
license_summary=False,
params=None):
self.wb = None
self.filename = filename
self.params = params
if self.params is None:
self.params = {}
if filename is not None:
self.wb = openpyxl.load_workbook(filename=filename)
else:
self.wb = openpyxl.Workbook()
self.ws_models = self.wb.active
self.ws_models.title = 'Index'
thin = styles.Side(
border_style=self.params.get('side_border', 'thin'),
color=self.params.get('side_color', '999999'))
for i in range(1, len(group_styles) + 1):
key = 'suns_group_%s' % i
name = 'suns_group_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['group_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_group_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['point_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
if 'suns_hdr' not in self.wb.named_styles:
hdr_style = styles.NamedStyle(name='suns_hdr')
hdr_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('hdr_color', 'dddddd'))
hdr_style.font = styles.Font(bold=True)
hdr_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
hdr_style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(hdr_style)
if 'suns_group_entry' not in self.wb.named_styles:
model_entry_style = styles.NamedStyle(
name='suns_group_entry')
model_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_entry_style.font = styles.Font()
model_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(model_entry_style)
if 'suns_group_text' not in self.wb.named_styles:
model_text_style = styles.NamedStyle(
name='suns_group_text')
model_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_text_style.font = styles.Font()
model_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(model_text_style)
if 'suns_point_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_point_variable_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_variable_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_variable_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_variable_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_symbol_entry' not in self.wb.named_styles:
repeating_entry_style = styles.NamedStyle(
name='suns_symbol_entry')
repeating_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_entry_style.font = styles.Font()
repeating_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(repeating_entry_style)
if 'suns_symbol_text' not in self.wb.named_styles:
repeating_text_style = styles.NamedStyle(
name='suns_symbol_text')
repeating_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_text_style.font = styles.Font()
repeating_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(repeating_text_style)
if 'suns_comment' not in self.wb.named_styles:
symbol_text_style = styles.NamedStyle(name='suns_comment')
symbol_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('comment_color', 'dddddd'))
# fgColor=self.params.get('symbol_color', 'fffcd9'))
symbol_text_style.font = styles.Font()
symbol_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
symbol_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(symbol_text_style)
if 'suns_entry' not in self.wb.named_styles:
entry_style = styles.NamedStyle(name='suns_entry')
entry_style.fill = styles.PatternFill('solid',
fgColor='ffffff')
entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(entry_style)
if 'suns_text' not in self.wb.named_styles:
text_style = styles.NamedStyle(name='suns_text')
text_style.font = styles.Font()
text_style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(text_style)
if 'suns_hyper' not in self.wb.named_styles:
hyper_style = openpyxl.styles.NamedStyle(name='suns_hyper')
hyper_style.font = openpyxl.styles.Font(color='0000ee',
underline='single')
hyper_style.alignment = openpyxl.styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(hyper_style)
for i in range(len(models_hdr)):
self.set_cell(self.ws_models, 1, i + 1, models_hdr[i][0],
'suns_hdr')
if models_hdr[i][1]:
self.ws_models.column_dimensions[chr(
65 + i)].width = models_hdr[i][1]
return f"{month.strftime('%B_%Y')}_Inventory"
def MONTHTITLE(month):
return f"{month.strftime('%B %Y')}"
####################################################################
"""-----------------------------------------------------------------
STYLE SETUP
-----------------------------------------------------------------"""
####################################################################
TITLESTYLE = xlstyle.NamedStyle(name='TITLESTYLE')
TITLESTYLE.font = xlstyle.Font(bold=True, size=16)
TITLESTYLE.alignment = xlstyle.Alignment(horizontal="center")
TOTALCELLSTYLE = xlstyle.NamedStyle(name="TOTALCELLSTYLE")
TOTALCELLSTYLE.font = xlstyle.Font(bold=True, size=12, color='FFFFFF')
ALLTHICKBORDERS = xlstyle.Border(
**{
side: xlstyle.Side(border_style='thick', color='000000')
for side in ['left', 'right', 'top', 'bottom']
})
TOTALCELLSTYLE.border = ALLTHICKBORDERS
TOTALCELLSTYLE.fill = xlstyle.PatternFill(fill_type="solid",
start_color='BFBFBF')
MONTHTABLESTYLE = xltable.TableStyleInfo(name="TableStyleMedium4",
showFirstColumn=False,
showLastColumn=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_b = styles.Font(name='Calibri',
size=11,
bold=True,
color='FF000000')
thin = styles.Side(border_style="thin", color="000000")
border = styles.Border(outline = thin, top = thin, left=thin, right=thin, bottom=thin, vertical=thin, horizontal=thin)
alignment = styles.Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
ws.merge_cells('B1:D2')
# show error if insert value into first
# row - строка, column - поле
ws['C8'] = str(ws['C9'].value) + '!'
ws.cell(row=1, column=2, value='huy')
b1 = ws['B1']
b1.font = font_b
b1.alignment = alignment
# заливка
def pathName(self, path):
# workbook_name = '0806黄爱枝(1).xlsx'
# path = "/Users/liulunyang/Desktop/0806黄爱枝.xlsx"
wb = openpyxl.load_workbook(path)
# wb = Workbook()
ws = wb.active
# ws["A1"] = 0
# ws.remove_named_range()
# help(ws)
# ws.merge_cells('A2:B2')
# 需要先取消合并
max_row = ws.max_row
print(max_row)
ws.unmerge_cells(start_row=max_row,
start_column=1,
end_row=max_row,
end_column=3)
ws.unmerge_cells(start_row=max_row,
start_column=5,
end_row=max_row,
end_column=8)
ws.unmerge_cells(start_row=max_row,
start_column=10,
end_row=max_row,
end_column=14)
ws.unmerge_cells(start_row=max_row - 1,
start_column=1,
end_row=max_row - 1,
end_column=3)
ws.unmerge_cells(start_row=max_row - 1,
start_column=5,
end_row=max_row - 1,
end_column=8)
ws.unmerge_cells(start_row=max_row - 1,
start_column=10,
end_row=max_row - 1,
end_column=14)
ws.unmerge_cells('A1:O1')
# 删除列
for i in range(4):
ws.delete_cols(1)
ws.delete_cols(2)
ws.delete_cols(8)
ws.delete_cols(8)
# 删除第一行
ws.delete_rows(1)
ws.delete_rows(max_row - 2)
# 改变列宽
ws.column_dimensions['A'].width = 25
ws.column_dimensions['B'].width = 25
ws.column_dimensions['C'].width = 13
ws.column_dimensions['D'].width = 3.8
ws.column_dimensions['E'].width = 8
ws.column_dimensions['F'].width = 11
ws.column_dimensions['G'].width = 11
'''
这方法对一行不起作用
ws.column_dimensions['A'].font = Font(bold=True,size=15)
ws['A1'].font = Font(size = 20)
'''
ws.cell(row=max_row - 2, column=1).value = "总计:"
# print(ws['D2'].value,len(ws['D2'].value))
ws.row_dimensions[max_row - 2].height = 15
for i in range(max_row - 3):
if i == max_row - 4:
break
ws.cell(row=i + 1, column=1).font = Font(size=12, bold=True)
ws.cell(row=i + 1,
column=1).alignment = styles.Alignment(vertical='center',
horizontal='center')
a = ws.cell(row=i + 2, column=1).value
print(a, len(a))
d = ws.cell(row=i + 2, column=4).value
print(d)
if d == 0:
d = 1
if len(a) < 14:
if d > 1:
ws.row_dimensions[i + 2].height = 13 * d
else:
ws.row_dimensions[i + 2].height = 15 * d
else:
if d > 2:
ws.row_dimensions[i + 2].height = 13 * d
else:
ws.row_dimensions[i + 2].height = 15 * 2
## 有问题这里, 改变cell的背景颜色
# ws['A1'].styles=styles.PatternFill(fill_type='solid',fgColor="0d5330")
# ws.row_dimensions[1].fill = styles.PatternFill(bgColor=colors.BLACK)
'''
元格样式
font(字体类)：字号、字体颜色、下划线等
fill(填充类)：颜色等
border(边框类)：设置单元格边框
alignment(位置类)：对齐方式
number_format(格式类)：数据格式
protection(保护类)：写保护
'''
fill = styles.PatternFill(fill_type=None, bgColor=colors.WHITE)
# dashDot','dashDotDot', 'dashed','dotted',
# 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
# 'mediumDashed', 'slantDashDot', 'thick', 'thin'
# 修改边框
border = Border(left=Side(style='hair', color=colors.BLACK),
right=Side(style='hair', color=colors.BLACK),
top=Side(style="hair", color=colors.BLACK),
bottom=Side(style='hair', color=colors.BLACK))
ws['D1'] = '数量'
for r in range(max_row - 2):
for i in range(7):
# ws.cell(row= r + 1, column =1+i).fill = styles.PatternFill(bgColor=colors.BLACK)
ws.cell(row=r + 1, column=1 + i).fill = fill
ws.cell(row=r + 1, column=1 + i).border = border
ws.cell(row=r + 1,
column=1).alignment = styles.Alignment(wrap_text=True)
# 改变第一行的居中对齐
for i in range(7):
ws.cell(row=1, column=i + 1).alignment = styles.Alignment(
vertical='center', horizontal='center')
ws.cell(row=1, column=1 + i).font = Font(size=12, bold=True, b=1)
wb.save(path)
# 自适应行高 没有实现
# help(openpyxl.styles)
'''
def bloco3_resumo_por_acao(worksheet, acoes, conta_associacao, periodo):
global OFFSET
thin = styles.Side(border_style="thin", color="000000")
linha_inicial = 21 + OFFSET
linha_atual = linha_inicial
offset_local = 0
total_valores = 0
total_conciliacao = 0
destinacoes = ['C', 'K', 'CK']
totalizador = {
SALDO_ANTERIOR: {
'C': 0,
'K': 0,
'CK': 0
},
CREDITO: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_REALIZADA: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_NAO_REALIZADA: {
'C': 0,
'K': 0,
'CK': 0
},
SALDO_REPROGRAMADO_PROXIMO: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS: {
'C': 0,
'K': 0,
'CK': 0
},
SALDO_BANCARIO: {
'C': 0,
'K': 0,
'CK': 0
},
TOTAL_SALDO_BANCARIO: {
'C': 0,
'K': 0,
'CK': 0
},
CREDITO_NAO_DEMONSTRADO: {
'C': 0,
'K': 0,
'CK': 0
},
}
logging.info(f'Linha inicial:{linha_inicial}')
for linha_acao, acao_associacao in enumerate(acoes):
# Movendo as linhas para baixo antes de inserir os dados novos
linha_atual = linha_inicial + (linha_acao * 3)
offset_local = linha_atual - linha_inicial
logging.info(
f'LAção:{linha_acao}, LAtual:{linha_atual}, offset:{offset_local}, Ação:{acao_associacao.acao.nome}'
)
if offset_local > 0:
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
fechamento_periodo = FechamentoPeriodo.fechamentos_da_acao_no_periodo(
acao_associacao=acao_associacao,
periodo=periodo,
conta_associacao=conta_associacao).first()
sub_valores, sub_conciliacao, totalizador = sintese_receita_despesa(
worksheet, acao_associacao, conta_associacao, periodo,
fechamento_periodo, linha_atual, totalizador)
total_valores += sub_valores
total_conciliacao += sub_conciliacao
for destinacao_idx in range(0, 2):
if destinacao_idx == 0:
try:
worksheet.unmerge_cells(
f'B{linha_atual + 1}:B{linha_atual + 3}')
except ValueError:
# Ignora caso o campo ja esteja desmergeado
pass
row = list(worksheet.rows)[linha_atual + destinacao_idx]
row[0].value = destinacoes[destinacao_idx]
if destinacao_idx == 0:
worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}')
top_left_cell = worksheet[f'B{linha_atual + 1}']
top_left_cell.value = acao_associacao.acao.nome
top_left_cell.font = styles.Font(name='Arial',
size=10.5,
b=True,
color="000000")
top_left_cell.alignment = styles.Alignment(horizontal="left",
vertical="center")
# Ajusta o fundo para que a apenas as Colunas E, F, I e L tenham fundo cinza na linha de destinação CK
for col in ('E', 'F', 'I', 'L'):
col_cell = worksheet[
f'{col}{linha_atual + destinacao_idx + 1}']
col_cell.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
col_cell.fill = styles.PatternFill(
"solid",
fgColor="808080" if destinacao_idx == 2 else "FFFFFF")
linha_atual += 3
worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}')
top_left_cell = worksheet[f'B{linha_atual + 1}']
top_left_cell.value = "TOTAL"
top_left_cell.font = styles.Font(name='Arial',
size=10.5,
b=True,
color="000000")
top_left_cell.alignment = styles.Alignment(horizontal="left",
vertical="center")
for idx, destinacao in enumerate(destinacoes):
row = list(worksheet.rows)[linha_atual + idx]
row[SALDO_ANTERIOR].value = formata_valor(
totalizador[SALDO_ANTERIOR][destinacao])
row[CREDITO].value = formata_valor(totalizador[CREDITO][destinacao])
row[DESPESA_REALIZADA].value = formata_valor(
totalizador[DESPESA_REALIZADA][destinacao])
row[DESPESA_NAO_REALIZADA].value = formata_valor(
totalizador[DESPESA_NAO_REALIZADA][destinacao])
row[SALDO_REPROGRAMADO_PROXIMO].value = formata_valor(
totalizador[SALDO_REPROGRAMADO_PROXIMO][destinacao])
row[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS].value = formata_valor(
totalizador[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS][destinacao])
row[SALDO_BANCARIO].value = formata_valor(
totalizador[SALDO_BANCARIO][destinacao])
if destinacao != 'CK':
row[CREDITO_NAO_DEMONSTRADO].value = formata_valor(
totalizador[CREDITO_NAO_DEMONSTRADO][destinacao])
if destinacao == 'K':
valor_total_reprogramado_proximo = totalizador[
SALDO_REPROGRAMADO_PROXIMO]['CK']
valor_total_reprogramado_proximo = (
valor_total_reprogramado_proximo +
totalizador[SALDO_REPROGRAMADO_PROXIMO]['C']
if totalizador[SALDO_REPROGRAMADO_PROXIMO]['C'] > 0 else
valor_total_reprogramado_proximo)
valor_total_reprogramado_proximo = (
valor_total_reprogramado_proximo +
totalizador[SALDO_REPROGRAMADO_PROXIMO]['K']
if totalizador[SALDO_REPROGRAMADO_PROXIMO]['K'] > 0 else
valor_total_reprogramado_proximo)
row[TOTAL_REPROGRAMADO_PROXIMO].value = formata_valor(
valor_total_reprogramado_proximo)
# Apresenta o subtotal bancário da ação na posição destinada ao valor
valor_saldo_bancario_total = totalizador[SALDO_BANCARIO]['C'] + totalizador[SALDO_BANCARIO]['K'] + \
totalizador[SALDO_REPROGRAMADO_PROXIMO]['CK']
row[TOTAL_SALDO_BANCARIO].value = "Subtotal"
row[TOTAL_SALDO_BANCARIO].value = formata_valor(
valor_saldo_bancario_total)
OFFSET += offset_local
print(ws.max_row)
words = [chr(i) for i in range(65, 91)]
current_max_row = ws.max_row
for i in range(0, len(words)):
foo = ws.cell(row=ws.max_row + 1, column=ws.min_column)
# # or
# foo = ws.cell(row = current_max_row+i, column = ws.min_column)
print(i)
foo.value = words[i]
# 單元格對齊
ws.cell(row=11, column=11).value = "CENTER"
align_center = styles.Alignment(horizontal="center", vertical="center")
ws.cell(row=11, column=11).alignment = align_center
# 合併單行儲存格
# 只有起始格的資料會留下,不會置中
ws.cell(10, 11).value = "merge_single_line"
ws.merge_cells("K10:M10")
# 合併多行儲存格
ws.cell(6, 11).value = "merge_multi-line"
ws.merge_cells(start_row=6, start_column=11, end_row=9, end_column=15)
# 移除表單
wb.remove_sheet(ws1)
# 插入列行
ws.insert_rows(3)
class TemplateRender:
align_center_center = styles.Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
align_center_center_not_wrap = styles.Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
align_left_top = styles.Alignment(horizontal='left',
vertical='top',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
align_left_top_not_wrap = styles.Alignment(horizontal='left',
vertical='top',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
def __init__(self, report_columns=1, header_width=50, file_name='', copyfile=False, open_in_excel=False,
dirs_copy_name=[], sheet_title=''):
self.sheet_count = 1
self.current_line = 0
self.report_columns = report_columns
self.header_width = header_width
self.file_name = file_name
self.wb = Workbook()
self.ws = self.wb.active
self.copyfile = copyfile
self.open_in_excel = open_in_excel
self.dirs_copy_name = dirs_copy_name
self.first_data_row = None
self.add_sheet(sheet_title)
def add_sheet(self, sheet_title=''):
self.current_line = 1
if self.sheet_count > len(self.wb.worksheets):
self.wb.create_sheet()
self.ws = self.wb.worksheets[self.sheet_count-1]
if sheet_title:
self.ws.title = sheet_title
self.sheet_count += 1
def add_data_row(self, rows, aligment=None):
for row in rows:
for en, el in enumerate(row):
if isinstance(el, datetime.date):
self.ws.cell(row=self.current_line, column=en + 1).value = el.strftime('%d.%m.%Y')
else:
self.ws.cell(row=self.current_line, column=en + 1).value = el
if aligment is not None:
self.ws.cell(row=self.current_line, column=en + 1).alignment = aligment
if self.first_data_row is None:
self.first_data_row = self.current_line
self.current_line += 1
def add_header_row(self, title):
for col_num in range(1, self.report_columns+1):
self.ws.cell(row=self.current_line, column=col_num).alignment = self.align_left_top_not_wrap
self.ws.cell(row=self.current_line, column=1).value = title
self.current_line += 1
def add_titles_row(self, rows):
for en, row in enumerate(rows):
self.ws.cell(row=self.current_line, column=en + 1).value = row[0]
self.ws.cell(row=self.current_line, column=en + 1).alignment = self.align_center_center
self.ws.column_dimensions[self.ws.cell(row=self.current_line, column=en + 1).column_letter].width = row[1]
self.current_line += 1
def close_template_file(self):
logging.warning(self.file_name)
self.wb.save(filename=self.file_name)
self.wb.close()
# if self.copyfile:
# self.copy_template_file()
if self.open_in_excel:
self.open_template_in_excel()
def copy_template_file(self):
for dir_copy_name in self.dirs_copy_name:
shutil.copy(self.file_name, os.path.join(dir_copy_name, self.file_name))
def open_template_in_excel(self):
temp_file_name = os.path.join(os.environ['TMP'],
"report_" + ''.join(random.choice(string.ascii_letters +
string.digits) for _ in range(10)) + '.xlsx')
shutil.copy(self.file_name, temp_file_name)
subprocess.Popen(temp_file_name, shell=True)
class TraceabilityGenerator:
''' Utility class for generating a requirements traceability matrix'''
HEADER_FONT = styles.Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
CELL_FONT = styles.Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
SUMMARY_FONT = styles.Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
HEADER_ALIGNMENT = styles.Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
CELL_ALIGNMENT = styles.Alignment(horizontal='left',
vertical='bottom',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
SUMMARY_ALIGNMENT = styles.Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
REQ_NOT_MET_FILL = styles.PatternFill(start_color='EE1111',
end_color='EE1111',
fill_type='solid')
REQ_MET_FILL = styles.PatternFill(start_color='0FFFFF',
end_color='0FFFFF',
fill_type='solid')
COL_COUNT_FORMULA = 'COUNTA(\'%s\'!%s:%s)-1'
PERCENT_FORMULA = '=IF(%s%d, %s%d/%s%d, 0.0)'
REQ_IS_MET_FORMULA = 'OR(%s,ISBLANK($%s$%d))'
@staticmethod
def generateTraceabilityMatrix(reqMap, args):
''' Generates a workbook with a traceability matrix from a
requirements map'''
outputDir = os.path.expanduser(args.outputDir)
outputDir = os.path.expandvars(outputDir)
# make sub-directories for exported traceability matrix
if (not os.path.exists(outputDir)):
os.makedirs(outputDir)
outfile = os.path.join(outputDir, args.outfile + '.xlsx')
wb = Workbook()
# generate summary sheet
summarySheet = wb.active
TraceabilityGenerator._generateTraceabilitySummary(
summarySheet, reqMap, args)
# generate sheet for each module
for moduleName, module in six.iteritems(reqMap):
TraceabilityGenerator._generateTraceabilitySheet(
wb, moduleName, module, args)
wb.save(outfile)
@staticmethod
def _generateTraceabilitySummary(sheet, reqMap, args):
''' Generates a summary sheet with summary details
for each module and overall summary'''
sheet.title = 'Summary'
sheet.column_dimensions['A'].width = 50
sheet.column_dimensions['B'].width = 30
sheet.column_dimensions['C'].width = 15
sheet.column_dimensions['D'].width = 3
sheet.column_dimensions['E'].width = 15
sheet.column_dimensions['F'].width = 3
sheet.column_dimensions['G'].width = 15
cellRow = 1
for moduleName in reqMap:
cellRow += TraceabilityGenerator._generateModuleSummary(
sheet, moduleName, cellRow, 1, args)
cellRow += 1
# set summary of module summaries
cell = sheet.cell(row=cellRow, column=1)
cell.value = 'Summary'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellRow += 1
moduleCol = 3
expectedCount = TraceabilityGenerator._getSummaryFormula(reqMap, 'A')
if (True == args.checkSrcLinks):
# set summary of source links
actualCount = TraceabilityGenerator._getSummaryFormula(
reqMap, get_column_letter(moduleCol))
TraceabilityGenerator._generateSummaryColSummary(
sheet, 'Source Links:', actualCount, expectedCount, cellRow, 2)
cellRow += 1
moduleCol += 1
if (True == args.checkTestLinks):
# set summary of test links
actualCount = TraceabilityGenerator._getSummaryFormula(
reqMap, get_column_letter(moduleCol))
TraceabilityGenerator._generateSummaryColSummary(
sheet, 'Test Links:', actualCount, expectedCount, cellRow, 2)
cellRow += 1
moduleCol += 1
@staticmethod
def _getSummaryFormula(reqMap, colStr):
''' Generates an summary formula for all the modules
for the specified column (requirement type)'''
countFormula = '=0'
for moduleName in reqMap:
countFormula += '+' + TraceabilityGenerator.COL_COUNT_FORMULA % (
moduleName, colStr, colStr)
return countFormula
@staticmethod
def _generateSummaryColSummary(sheet, colName, countFormula, totalFormula,
rowOffset, colOffset):
''' Generates a column summary details for all the modules in the format
<summary_description> | <actual_value> / <expected_value> = <percent_value>'''
# set summary type
cell = sheet.cell(row=rowOffset, column=colOffset)
cell.value = colName
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set actual summary count
cell = sheet.cell(row=rowOffset, column=colOffset + 1)
cell.value = countFormula
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'right'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=colOffset + 2)
cell.value = '/'
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set expected summary count
cell = sheet.cell(row=rowOffset, column=colOffset + 3)
cell.value = totalFormula
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'left'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=colOffset + 4)
cell.value = '='
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set percentage of actual/expected
cell = sheet.cell(row=rowOffset, column=(colOffset + 5))
cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\
get_column_letter(colOffset+3), rowOffset,
get_column_letter(colOffset+1), rowOffset,
get_column_letter(colOffset+3), rowOffset)
cell.font = TraceabilityGenerator.SUMMARY_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
cell.number_format = '0.00%'
@staticmethod
def _generateModuleColSummary(sheet, moduleName, colName, countCol,
totalCol, rowOffset, colOffset):
''' Generates a column summary details for the specified module in the format
<summary_description> | <actual_value> / <expected_value> = <percent_value>'''
# set column summary type
cell = sheet.cell(row=rowOffset, column=colOffset)
cell.value = colName
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set column actual count
cell = sheet.cell(row=rowOffset, column=(colOffset + 1))
cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % (
moduleName, get_column_letter(countCol),
get_column_letter(countCol))
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'right'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=(colOffset + 2))
cell.value = '/'
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set column expected count
cell = sheet.cell(row=rowOffset, column=(colOffset + 3))
cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % (
moduleName, get_column_letter(totalCol),
get_column_letter(totalCol))
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'left'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=(colOffset + 4))
cell.value = '='
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set actual / expected percentage
cell = sheet.cell(row=rowOffset, column=(colOffset + 5))
cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\
get_column_letter(colOffset+3), rowOffset,
get_column_letter(colOffset+1), rowOffset,
get_column_letter(colOffset+3), rowOffset)
cell.font = TraceabilityGenerator.SUMMARY_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
cell.number_format = '0.00%'
@staticmethod
def _generateModuleSummary(sheet, moduleName, rowOffset, colOffset, args):
''' Generates a summary section for the specified module'''
cellRow = 0
# set module name
cell = sheet.cell(row=rowOffset + cellRow, column=colOffset)
cell.value = moduleName
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellRow += 1
# start at offset because of default columns: (name | text | satisfied)
moduleCol = 4
if (True == args.checkSrcLinks):
# set source links summary
TraceabilityGenerator._generateModuleColSummary(
sheet, moduleName, 'Source Links:', moduleCol, 1,
rowOffset + cellRow, colOffset + 1)
moduleCol += 1
cellRow += 1
if (True == args.checkTestLinks):
# set test links summary
TraceabilityGenerator._generateModuleColSummary(
sheet, moduleName, 'Test Links:', moduleCol, 1,
rowOffset + cellRow, colOffset + 1)
cellRow += 1
return cellRow
@staticmethod
def _generateTraceabilitySheet(workbook, moduleName, module, args):
''' Generates a sheet with all the requirements, requirements
details, and requirement links for the specified module'''
# create sheet for module
moduleSheet = workbook.create_sheet(title=moduleName)
moduleSheet.fill = TraceabilityGenerator.REQ_MET_FILL
# set column dimensions for requirement name
moduleSheet.column_dimensions['A'].width = 30
# set column dimensions for requirement text
moduleSheet.column_dimensions['B'].width = 70
# set header and requirement text as fixed
moduleSheet.freeze_panes = 'B2'
# set column for requirement satisfaction
moduleSheet.column_dimensions['C'].hidden = True
cellRow = 1
# setup header
cellCol = 1
# set start column for conditional formatting
startCol = get_column_letter(cellCol)
# add requirement name column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Requirement Name'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
# add requirement text column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Requirement Text'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
areReqLinksChecked = \
(True == args.checkSrcLinks) or \
(True == args.checkTestLinks)
if (True == areReqLinksChecked):
# add requirement satisfied column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'SATISFIED'
cellCol += 1
if (True == args.checkSrcLinks):
# set column dimension for test links
moduleSheet.column_dimensions[get_column_letter(
cellCol)].width = 75
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Source Code Links'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
if (True == args.checkTestLinks):
# set column dimension for test links
moduleSheet.column_dimensions[get_column_letter(
cellCol)].width = 75
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Test Links'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
# get end column for conditional formatting
endCol = get_column_letter(cellCol - 1)
cellRow += 1
# add row for each requirement in module
for req, reqValue in six.iteritems(module):
rowCol = 1
# requirement name
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = req
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
rowCol += 1
# requirement text
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = reqValue.reqText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
rowCol += 1
if (True == areReqLinksChecked):
# requirement satisfied
reqMetCell = moduleSheet.cell(row=cellRow, column=rowCol)
reqMetFormula = 'False'
rowCol += 1
# TODO add requirements as hyperlinks
'=HYPERLINK(<URL>, <text>)'
if (True == args.checkSrcLinks):
# add source links
linksText = ''
for link in reqValue.reqLinks:
if (tLinkType.LINK_TYPE__SRC == link.linkType):
linksText += link.linkName
if ((link.linkFile is not None)
and (link.linkFileLineNum is not None)):
if (True == args.basename):
linksText += ' - (%s line %s)' % (
os.path.basename(link.linkFile),
link.linkFileLineNum)
else:
linksText += ' - (%s line %s)' % (
link.linkFile, link.linkFileLineNum)
linksText += '\n'
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = linksText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % (
reqMetFormula, get_column_letter(rowCol), cellRow)
rowCol += 1
if (True == args.checkTestLinks):
# add test links
linksText = ''
for link in reqValue.reqLinks:
if (tLinkType.LINK_TYPE__TEST == link.linkType):
linksText += link.linkName + '\n'
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = linksText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % (
reqMetFormula, get_column_letter(rowCol), cellRow)
rowCol += 1
# set requirement met column value
reqMetCell.value = '=IF(NOT(%s), "PASS", "FAIL")' % (
reqMetFormula)
# set conditional formatting for if requirement is met
moduleSheet.conditional_formatting.add(
'%s%d:%s%d' % (startCol, cellRow, endCol, cellRow),
formatting.rule.FormulaRule(
formula=[reqMetFormula],
stopIfTrue=True,
fill=TraceabilityGenerator.REQ_NOT_MET_FILL))
cellRow += 1
def insertData(rollNoRow, rollNoCol, mySheet):
# Getting current date
date = datetime.now().strftime('%d')
month = datetime.now().strftime('%b')
year = datetime.now().strftime('%Y')
# Adding full stop so that when last digit is 0,
# it doesnt consider last session as 0
full_date = month + "\n" + date + ",\n" + year + "."
# # Reading .txt File
txtfile = "list.txt"
if not (os.path.exists(txtfile)):
print(f"File '{txtfile}' Does NOT Exists")
exit(1)
file = open("list.txt", "r")
# rollNoRow Storing to another variable
tempRollNoRow = rollNoRow
# Default row, col for "list.txt"
row, col = 0, 0
# By Default, considering that only one lecture was held
multipleSessions = False
# Getting date of last attendance recorded
lastDateAt = mySheet.cell(row=rollNoRow, column=max_col)
lastDateAt.alignment = styles.Alignment(wrapText=True)
# Checking if attendance of same date exists
if (lastDateAt.value is not None) and (full_date in lastDateAt.value):
print("\nNOTE: More than 1 sessions found")
print("So, naming column name as S-1, S-2, ..\n")
# Multiple lectures were held
multipleSessions = True
# Getting last digit of last session
if lastDateAt.value[-1] >= '0' and lastDateAt.value[-1] <= '9':
lastSessionNo = int(lastDateAt.value[-1])
else:
# Renaming cellvalue to session1
lastSessionNo = 1
lastDateAt.value = full_date + "\n S-1"
# Insert date at last column
insertDateAt = mySheet.cell(row=rollNoRow, column=max_col + 1)
insertDateAt.alignment = styles.Alignment(wrapText=True,
horizontal='center')
# If Multiple lectures were held
if multipleSessions:
insertDateAt.value = full_date + "\n S-" + str(lastSessionNo + 1)
# if Multiple lectures were not held
else:
insertDateAt.value = full_date
# Iterating over each attendee
for attendee in file:
# Remove blank lines
if not attendee.strip():
continue
# Finding Roll no from entire String
roll = findall("\d+", attendee)
# Teachers wont have roll no, so ignoring them
if not len(roll):
print("Skipping", attendee, end="")
continue
# rollNoRow must be changed after each Iteration
# so giving back the same value
rollNoRow = tempRollNoRow
# Iterating over all roll no of class
for i in range(rollNoRow + 1, max_row + 1):
# Getting cell_object of roll no of classes
cell = mySheet.cell(row=i, column=rollNoCol)
# NOTE: roll[0] is a `string`
# If roll no of text file matches the roll no of xls file
if (int(roll[0]) == cell.value):
print("Inserteddddd")
# Getting cell_object where new attendance will be recorded
insertAtCell = mySheet.cell(row=i, column=max_col + 1)
# Store only first Number found from list
insertAtCell.value = 'P'
insertAtCell.alignment = styles.Alignment(horizontal='center')
# Printing those rollno who were recorded as present
print(roll[0])
break
# Going to next record in .xls file
rollNoRow += 1
# Going to next record in .txt file
row += 1
然后保存一个csv，还得设置index=False，不然第一列会多出来索引，然后得设置编码，否则用excel打开会乱码
然后保存一个excel，也得设置index=False，并且pandas保存后，原来的格式都没了，所以把excel的格式设置都放在了最后
"""
print('正在保存csv...')
df = read_excel(target_dir + "渠道数据整合模板.xlsx", engine='openpyxl')
df.sort_values(by='日期', ascending=False, inplace=True)
df.to_csv(target_dir + "渠道数据整合模板.csv", index=False, encoding='utf_8_sig')
df.to_excel(target_dir + "渠道数据整合模板.xlsx", index=False)
print('正在设置xlsx文件格式...')
# 设置excel格式
wb2 = load_workbook(target_dir + "渠道数据整合模板.xlsx")
ws2 = wb2.active
# 左对齐，上下居中
alignment = styles.Alignment(horizontal='left', vertical='center')
# 要对每个单元格都设置
for row in ws2.rows:
for cell in row:
cell.alignment = alignment
# 设置列宽
ws2.column_dimensions['A'].width = 20
ws2.column_dimensions['B'].width = 20
ws2.column_dimensions['C'].width = 20
ws2.column_dimensions['D'].width = 20
ws2.column_dimensions['E'].width = 20
ws2.column_dimensions['F'].width = 20
ws2.column_dimensions['G'].width = 20
ws2.column_dimensions['H'].width = 20
ws2.column_dimensions['I'].width = 20
ws2.column_dimensions['J'].width = 20
