def get_donor_cell(self):
size = 9
if self.has_acceptor:
cell = WriteOnlyCell(ws_result, value='OK')
cell.fill = PatternFill(start_color='FFdff0d8',
end_color='FFdff0d8',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='3c763d',
bold=True)
cell.comment = Comment(text="Good =)", author=self.author)
else:
cell = WriteOnlyCell(ws_result, value='NO')
cell.fill = PatternFill(start_color='FFf2dede',
end_color='FFf2dede',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='a94442',
bold=True)
cell.comment = Comment(
text="There are no acceptor ({}) on the site: {}".format(
self.acceptor, self.donor),
author=self.author)
return cell
def compareExecute(self, excel_expect, excel_actual):
wb_expect = load_workbook(excel_expect, data_only=False)
wb_actual = load_workbook(excel_actual, data_only=False)
sheet_expect = wb_expect.active
sheet_actual = wb_actual.active
list = []
for row in sheet_expect.rows:
for cell in row:
list.append(cell.value)
fill = PatternFill("solid", fgColor="FF0000")
for index_row, row in enumerate(sheet_actual.rows):
for index_column, cell in enumerate(row):
index = index_row * sheet_actual.max_column + index_column
if cell.value != list[index]:
try:
comment = Comment('预期是：{0},\n实际结果是：{1}'.format(list[index], cell.value), 'lxy')
comment.width = 400
comment.height = 400
cell.comment = comment
cell.fill = fill
self.textBrowser.append(
'第{2}行第{3}列，\n预期是：{0},\n实际结果是：{1}'.format(list[index], cell.value, index_row + 1,
index_column + 1))
except:
pass
wb_actual.save(excel_actual)
def test_cell_copy_comment(self, copier):
ws1 = copier.source
ws2 = copier.target
c1 = ws1['A1']
c1.comment = Comment("A Comment", "Nobody")
copier._copy_cells()
assert ws2['A1'].comment == Comment("A Comment", "Nobody")
def show_message(sh, r, c, message, type="error", accumulate=True):
"""
It serves to show a cell with some type of error (warning or error)
A message is shown in the comment
The name of the sheet is changed with a prefix indicating there is at least an issue to be solved
:param sh:
:param r:
:param c:
:param type:
:param message:
:return:
"""
cell = sh.cell(row=r, column=c)
fill = cell.fill
if type == "error":
fill = PatternFill("solid", fgColor="CC0000")
elif type == "warning":
fill = PatternFill("solid", fgColor="FFFF33")
elif type == "info":
fill = PatternFill("solid", fgColor="87CEEB")
cell.fill = fill
if accumulate:
comment = cell.comment
if comment:
comment.text += "\n" + message
else:
comment = Comment(message, "NIS")
else:
comment = Comment(message, "NIS")
cell.comment = comment
def format_output(val,eachformat, eachstyle):
"Returns a excel cell with the data formated as specified"
new_cell = WriteOnlyCell(xls_sheet, value = "init")
new_cell.style = eachstyle
if val==None:
val="None"
elif eachformat == None:
pass
elif eachformat == "OLE":
val = ole_timestamp(val)
new_cell.number_format = 'YYYY MMM DD'
elif eachformat.startswith("OLE:"):
val = ole_timestamp(val)
val = val.strftime(eachformat[4:])
elif eachformat=="FILE":
val = file_timestamp(val)
new_cell.number_format = 'YYYY MMM DD'
elif eachformat.startswith("FILE:"):
val = file_timestamp(val)
val = val.strftime(eachformat[5:])
elif eachformat.lower() == "lookup_id":
val = id_table.get(val, "No match in srum lookup table for %s" % (val))
elif eachformat.lower() == "lookup_luid":
val = lookup_luid(val)
elif eachformat.lower() == "lookup_sid":
val = "%s (%s)" % (val, lookup_sid(val))
elif eachformat.lower() == "seconds":
val = val/86400.0
new_cell.number_format = 'dd hh:mm:ss'
elif eachformat.lower() == "md5":
val = hashlib.md5(str(val)).hexdigest()
elif eachformat.lower() == "sha1":
val = hashlib.sha1(str(val)).hexdigest()
elif eachformat.lower() == "sha256":
val = hashlib.sha256(str(val)).hexdigest()
elif eachformat.lower() == "base16":
if type(val)=="<type 'int'>":
val = hex(val)
else:
val = str(val).encode("hex")
elif eachformat.lower() == "base2":
if type(val)==int:
val = bin(val)
else:
try:
val = int(str(val),2)
except :
val = val
new_cell.comment = Comment("Warning: Unable to convert value %s to binary." % (val),"srum_dump")
elif eachformat.lower() == "interface_id" and options.reghive:
val = interface_table.get(str(val),"")
elif eachformat.lower() == "interface_id" and not options.reghive:
val = val
new_cell.comment = Comment("WARNING: Ignoring interface_id format command because the --REG_HIVE was not specified.", "srum_dump")
else:
val = val
new_cell.comment = Comment("WARNING: I'm not sure what to do with the format command %s. It was ignored." % (eachformat), "srum_dump")
new_cell.value = val
return new_cell
def up_comment(row, trunk_line, up_time, down_time, set_up_time):
"""非招商部门上班考勤异常自动填批注"""
comment = Comment(
f"考勤异常,上班打卡时间为{up_time.__format__('%H:%M')},下班打卡时间为{down_time.__format__('%H:%M')},"
f"迟到{up_time.hour - set_up_time.hour}小时{up_time.minute - set_up_time.minute}分钟",
"")
comment.width, comment.height = 120, 120
sheet_trunk[convert2title(row + 4) + str(trunk_line)].comment = comment
def _create_ws():
wb = Workbook()
ws = Worksheet(wb)
comment1 = Comment("text", "author")
comment2 = Comment("text2", "author2")
comment3 = Comment("text3", "author3")
ws.cell(coordinate="B2").comment = comment1
ws.cell(coordinate="C7").comment = comment2
ws.cell(coordinate="D9").comment = comment3
return ws, comment1, comment2, comment3
def _create_ws():
wb = Workbook()
ws = Worksheet(wb)
comment1 = Comment("text", "author")
comment2 = Comment("text2", "author2")
comment3 = Comment("text3", "author3")
ws["B2"].comment = comment1
ws["C7"].comment = comment2
ws["D9"].comment = comment3
return ws, comment1, comment2, comment3
def write_to_csv(data, file_name):
# create excel workbook with one worksheet
wb = Workbook()
# access worksheet
ws_data = wb.active
# name ws
ws_data.title = "main_data"
# create header of spreadsheet
ws_data['A1'] = 'Device'
ws_data['B1'] = 'Net_name'
ws_data['C1'] = 'Net_difficulty (in MACs (M))'
ws_data['C1'].comment = Comment(
'MAC = Multiplication and Accumulation operation, (M) = in millions',
'xbarna02')
ws_data['D1'] = 'Net_precision (top 5)%'
ws_data['D1'].comment = Comment(
'probability that correct answer occurs in top 5 predictions',
'xbarna02')
ws_data['E1'] = 'Net_dropout'
ws_data['F1'] = 'Net_input_dimension'
ws_data['G1'] = 'Initialization (μs)'
ws_data['H1'] = 'Loading (μs)'
ws_data['I1'] = 'Overall_execution_of_one_batch (μs)'
ws_data['J1'] = 'Individual_inference_execution (μs)'
# make them bold
for cell in ws_data["1:1"]:
cell.font = Font(bold=True)
# write data
row_nmbr = 2
for i, _ in enumerate(data):
network = next(j for j, item in enumerate(g_mobilenet_data)
if item["name"] == data[i]['network_name'])
for measurement in data[i]['exec_t']['individual']:
ws_data[f'A{row_nmbr}'] = 'Raspberry Pi: CPU'
ws_data[f'B{row_nmbr}'] = g_mobilenet_data[i]['name']
ws_data[f'C{row_nmbr}'] = g_mobilenet_data[i]['difficulty']
ws_data[f'D{row_nmbr}'] = g_mobilenet_data[i]['precision']
ws_data[f'E{row_nmbr}'] = g_mobilenet_data[i]['name'].rsplit(
'_', 2)[1]
ws_data[f'F{row_nmbr}'] = g_mobilenet_data[i]['name'].rsplit(
'_', 1)[1]
ws_data[f'G{row_nmbr}'] = int(round(data[i]['init_t'] * 1000000))
ws_data[f'H{row_nmbr}'] = 0
ws_data[f'I{row_nmbr}'] = int(
round(data[i]['exec_t']['overall'] * 1000000))
ws_data[f'J{row_nmbr}'] = int(round(measurement * 1000000))
row_nmbr += 1
wb.save(
filename=f'/root/face-detection/experiment_1/RaspberryPi/{file_name}')
wb.close()
def __get_error_comment(errors: List[str],
existing_comment: Comment = None):
stack = []
if existing_comment and existing_comment.text:
stack.append(existing_comment.text)
stack.extend(errors)
text = '\r\n'.join(stack)
comment = Comment(text, f'Validation')
comment.width = 500
comment.height = 100
return comment
def insert_commet(self, row, message, validation_result):
parameters = validation_result.copy()
parameters.pop('records')
message = message + "\n"
message = message.format(**parameters)
if row.comment is None:
row.comment = Comment("", "RecordValidator")
comment_text = row.comment.text + message
row.comment = Comment(comment_text, "RecordValidator")
return row
def process(path_src):
name_src = os.path.basename(path_src)
workbook_src = load_workbook(path_src, data_only=True)
shert_name = list(
filter(lambda x: x[0] != '@' and x[0] != '#',
workbook_src.sheetnames))[0]
print(shert_name)
sheet_src = workbook_src[shert_name]
target_cols = []
i = 1
while True:
cell = sheet_src["{}{}".format(get_column_letter(i), 2)]
if cell.value is None:
break
if cell.comment is not None and "@language" in cell.comment.content:
target_cols.append(get_column_letter(i))
i += 1
print(target_cols)
for target_letter in target_cols:
name_cell = sheet_src["{}{}".format(target_letter, 1)]
name_cell.value = name_cell.value + "_id"
type_cell = sheet_src["{}{}".format(target_letter, 2)]
type_cell.comment = Comment("language done!", "excel_tool")
type_cell.value = "int32"
for row in range(5, sheet_src.max_row + 1):
cell = sheet_src["{}{}".format(target_letter, row)]
print("{} cur:{} total:{} value:{}".format(name_src, row,
sheet_src.max_row,
cell.value))
if cell.value is not None:
respond = None
while True:
save = row == sheet_src.max_row
url = 'http://139.155.88.114:5000/query'
# url = 'http://127.0.0.1:5000/query'
respond = requests.get(url, {
"content": cell.value,
"save": save
})
if respond.status_code == 200:
break
cell.comment = Comment(cell.value, "excel_tool")
cell.value = respond.text
else:
cell.value = 0
workbook_src.save(name_src)
def test_comment_count(dummy_cell):
cell = dummy_cell
ws = cell.parent
assert ws._comment_count == 0
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = None
assert ws._comment_count == 0
cell.comment = None
assert ws._comment_count == 0
def test_comment_assignment():
wb = Workbook()
ws = Worksheet(wb)
c = Comment("text", "author")
ws.cell(coordinate="A1").comment = c
with pytest.raises(AttributeError):
ws.cell(coordinate="A2").commment = c
ws.cell(coordinate="A2").comment = Comment("text2", "author2")
with pytest.raises(AttributeError):
ws.cell(coordinate="A1").comment = ws.cell(coordinate="A2").comment
# this should orphan c, so that assigning it to A2 does not raise AttributeError
ws.cell(coordinate="A1").comment = None
ws.cell(coordinate="A2").comment = c
def test_comment_count():
wb = Workbook()
ws = Worksheet(wb)
cell = ws.cell(coordinate="A1")
assert ws._comment_count == 0
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = None
assert ws._comment_count == 0
cell.comment = None
assert ws._comment_count == 0
def writeCell(cell, data):
if isinstance(data, list):
row_num = cell.row
column_num = cell.column
for el in data:
writeCell(sheet.cell(row=row_num, column=column_num), el)
column_num += 1
return
elif isinstance(data, tuple) and not isinstance(data, HyperLink):
if data[1]:
cell.comment = Comment(data[1], 'Generator')
cell.comment.width = units.points_to_pixels(300)
data = data[0]
if isinstance(data, HyperLink):
cell.value = data.text
cell.hyperlink = data.link
cell.style = 'Hyperlink'
else:
cell.value = data
if isinstance(data, int) or isinstance(data, float):
cell.alignment = Alignment(horizontal='right')
else:
cell.alignment = Alignment(horizontal='left')
def from_dict(cls, name: str, col_data=None):
if col_data is None:
col_data = Box(default_box=True)
formatters = Box(default_box=True,
width=ColFormat.DEFAULT_WIDTH,
wrap=ColFormat.DEFAULT_WRAP,
read_only=ColFormat.DEFAULT_RO)
if 'formatting' in col_data:
col_data_fmtting = col_data.get('formatting')
formatters.width = col_data_fmtting.get('chars_wrap',
ColFormat.DEFAULT_WIDTH)
formatters.wrap = col_data_fmtting.get('wrap',
ColFormat.DEFAULT_WRAP)
formatters.locked = col_data_fmtting.get('read_only',
ColFormat.DEFAULT_RO)
formatters.dv = col_data_fmtting.get('dv', False)
comment = col_data_fmtting.get('comment', None)
if comment:
formatters.comment = Comment(text=comment.text,
author=comment.author,
width=comment.width_len,
height=comment.height_len)
tmp_ref = col_data.get('references', None)
if tmp_ref:
formatters.reference = ColRef.from_registry(
tmp_ref) if tmp_ref else None
return cls(name=name,
type=FormatType.COLUMN,
formatters=formatters,
column_number=col_data.get('column_number', 'A'))
def test_write_rows_comment(self, writer):
cell = writer.ws['F1']
cell._comment = Comment("comment", "author")
writer.write_rows()
assert len(writer.ws._comments) == 1
def template_view(request):
wb = Workbook()
ws = wb.active
ws.title = "Resources"
for idx, heading in enumerate(HEADINGS):
column_letter = get_column_letter(idx + 1)
ref = "%s1" % column_letter
# Value
ws[ref] = heading['label']
# Comment
if heading['comment']:
ws[ref].comment = Comment(heading['comment'], "vulekamali")
# Column width
column = ws.column_dimensions[column_letter]
column.width = 20
# Style
ws[ref].font = Font(bold=True)
response = HttpResponse(
content=save_virtual_workbook(wb),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename=vulekamali-bulk-upload.xlsx'
return response
def gerar_planilha_para_correcao(self, coordenada_celulas_com_problema):
for coordenada_celula, mensagem in coordenada_celulas_com_problema:
pos_tabela, pos_coluna, pos_linha = map(int, [
coordenada_celula['tabela'], coordenada_celula['coluna'],
coordenada_celula['linha']
])
celula = self._workbook._sheets[pos_tabela].cell(
row=pos_linha + 1, column=pos_coluna + 1)
comentario = Comment(mensagem, "Sistema")
celula.font = Font(color=colors.RED)
celula.comment = comentario
directory = os.path.abspath('dist/static/xl/')
if not os.path.exists(directory):
os.makedirs(directory, exist_ok=True)
else:
shutil.rmtree(directory)
os.makedirs(directory)
# name = "output"
name = uuid.uuid4().hex
uri = "static/xl/{0}.xlsx".format(name)
self._workbook.save(os.path.abspath("dist/" + uri))
return uri
def test_cell_comment(WriteOnlyWorksheet):
ws = WriteOnlyWorksheet
from openpyxl.comments import Comment
from ..write_only import WriteOnlyCell
cell = WriteOnlyCell(ws, 1)
comment = Comment('hello', 'me')
cell.comment = comment
ws.append([cell])
assert ws._comments == [comment]
ws.close()
with open(ws.filename) as src:
xml = src.read()
expected = """
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
<sheetData>
<row r="1" spans="1:1"><c r="A1" t="n"><v>1</v></c></row>
</sheetData>
<legacyDrawing r:id="commentsvml"></legacyDrawing>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def append_mode(self, w):
trans = self.initial_translation()
trans = merge(self._word_dict[w]["cell"].comment.content, trans)
if verify(w, trans):
self._word_dict[w]["cell"].comment = Comment(text=trans,
author="Lee Mist")
self.up_color_level(w)
def handle_nonhour_input(base_ws, value, work_start_cell, overtime_cell, work_end_cell, work_hours_cell, comment_day_num_cell):
"""Handles different type of input in source file then hour(int)
Parameters:
base_ws: activated loaded base file
value (str): nonhour input from source file
work_start_cell (str): start hour cell reference
overtime_cell (str): overtime hour cell reference
work_end_cell (str): end hour cell reference
work_hours_cell (str): sum of work cell reference
comment_day_num_cell (str): day number cell reference (for comment)"""
# capitalizing proper legend input
value_cap = value.upper()
if value_cap in const.defined_cell_occurence:
base_ws[work_start_cell] = value_cap
base_ws[overtime_cell] = value_cap
base_ws[work_end_cell] = value_cap
base_ws[work_hours_cell] = value_cap
else:
base_ws[comment_day_num_cell].comment = Comment(
f"{value}", "")
base_ws[work_start_cell] = ""
base_ws[overtime_cell] = ""
base_ws[work_end_cell] = ""
base_ws[work_hours_cell] = ""
def set_notation(path, sheet_name):
wb = load_workbook(path)
ws = wb[sheet_name] # sheet_name=wb.sheetnames[0]
comment = Comment('hello world', 'yunan') # 插入批注
ws.cell(2, 2).comment = comment
# ws["A1"].comment = comment
wb.save(path)
def addComment(self, diskCell, diskCommandText):
'''添加批注
这一步只是添加一个单元格的批注，在下一个模块中可以批量添加。
Args:
diskCell(str) : 表格的单元格坐标，对应 commentList 的 key
diskCommandText(str) : 批注信息，对应 commentList[key] 的 value
'''
diskComment = Comment(diskCommandText, 'NOC')
diskComment.width = 200
diskComment.height = 100
self.ws[diskCell].comment = diskComment
diskFill = PatternFill(patternType='solid', fgColor="FFC125")
self.ws[diskCell].fill = diskFill
def write_sheet(grid,
ws,
text_in_cells=True,
text_in_comments=False,
leave_white_blank=True):
styles = collections.defaultdict(_CellStyle)
for r, c, elt in grid.features:
cell = ws.cell(row=r + 1, column=c + 1)
if isinstance(elt, BackgroundElt):
if not (elt.color == 0xFFFFFF and leave_white_blank):
styles[r, c].color = elt.color
elif isinstance(elt, TextElt):
if text_in_cells:
cell.value = elt.text
if text_in_comments:
cell.comment = Comment(elt.text, '')
elif isinstance(elt, BorderElt):
styles[r, c].borders.update(elt.dirs)
for (r, c), s in styles.items():
s.set_style(ws.cell(row=r + 1, column=c + 1))
for c in range(grid.width):
ws.column_dimensions[get_column_letter(c + 1)].width = 3
# Google Sheets seems to truncate sheets with no data at 10 columns, so
# make sure the bottom right cell isn't empty
bottom_right = ws.cell(row=grid.height, column=grid.width)
if not bottom_right.value:
bottom_right.value = ' '
def process(workbook: Any, content: str) -> None:
"""Process dskgrp_summary worksheet
:param workbook:
:param content:
"""
worksheet_name = 'dskgrp_summary'
worksheet = workbook.get_sheet_by_name(worksheet_name)
headers = get_parser_header(DSKRGP_TMPL)
RowTuple = namedtuple('RowTuple', headers)
headers[5], headers[6], headers[8] = \
'diskspeed(RPM)', 'disksize(MB)', 'totalcapacity(MB)'
build_header(worksheet, headers)
worksheet['E1'].comment = Comment(legend, '')
dskgrp_summary_out = run_parser_over(content, DSKRGP_TMPL)
final_col, final_row = 0, 0
for row_n, row_tuple in enumerate(map(RowTuple._make, dskgrp_summary_out), 2):
for col_n, col_value in \
enumerate(row_tuple._asdict().values(), ord('A')):
cell = worksheet['{}{}'.format(chr(col_n), row_n)]
cell.value = str.strip(col_value)
style_value_cell(cell)
set_cell_to_number(cell)
final_col = col_n
final_row = row_n
sheet_process_output(
worksheet,
'DskgrpSummaryTable',
'dskgrp_summary',
final_col,
final_row)
def fill_workload(wb, classrooms):
ws = wb['Загрузка кабинетов']
weekday_index = None
lesson_index = None
weekday = None
lesson = None
for i, row in enumerate(ws.rows):
classroom_number = str(row[0].value) if re.fullmatch(r'\d+(-\d+)?', str(row[0].value)) else None
for j, col in enumerate(row):
if classroom_number and not col.value:
if not lesson_index and not weekday_index:
weekday_index = i - 1
lesson_index = i
weekday = ws[weekday_index][j].value or weekday
lesson = ws[lesson_index][j].value or lesson
if weekday in classrooms and lesson in classrooms[weekday] and classroom_number in classrooms[weekday][
lesson]['numbers']:
ws[i + 1][j].value = '+'
ws[i + 1][j].font = Font(color="000000")
if classrooms[weekday][lesson]['numbers'].count(classroom_number) > 1:
ws[i + 1][j].fill = PatternFill(fgColor="FFC7CE", fill_type="solid")
col_numbers = ",".join([classrooms[weekday][lesson]['address'][k] for k, cl_n in enumerate(
classrooms[weekday][lesson]['numbers']
) if cl_n == classroom_number])
ws[i + 1][j].comment = Comment(f'Адреса ячеек: {col_numbers}', 'Automatic comment')
def _excel_sheet_fill(self, sheet, rows, styles={}, row_offset=1):
# fill all cells
for row_idx in range(0, len(rows)):
row = rows[row_idx]
for col_idx in range(0, len(row)):
col = row[col_idx]
col_letter = get_column_letter(col_idx + 1)
cell_row_idx = row_idx + 1 + row_offset
cell = sheet.cell(cell_row_idx, col_idx + 1)
cell.value = '%s' % (col['result'])
if 'comment' in col and col['comment']:
cell.comment = Comment(col['comment'], '')
# validation styles
if 'valid' in col and col['valid'] in styles:
cell.style = styles[col['valid']]
# set numeric types for some test results
if isinstance(col['result'], (int, float, complex)):
#cell.value = str(col['result'])
cell.data_type = cell.TYPE_NUMERIC
#cell.number_format = '0.00E+00'
# set numeric types for some test results
elif isinstance(col['result'],
str) and col['result'].isdigit():
#cell.value = float(col['result']) if '.' in col['result'] else int(col['result'])
cell.data_type = cell.TYPE_NUMERIC
def demo_openpyxl():
# 一、新建一个工作薄
from openpyxl import Workbook
wb = Workbook()
# 取一个工作表
ws = wb.active # wb['Sheet']，取已知名称、下标的表格，excel不区分大小写，这里索引区分大小写
# 1、索引单元格的两种方法，及可以用.value获取值
ws['A2'] = '123'
dprint(ws.cell(2, 1).value) # 123
# 2、合并单元格
ws.merge_cells('A1:C2')
dprint(ws['A1'].value) # None，会把原来A2的内容清除
# print(ws['A2'].value) # AttributeError: 'MergedCell' object has no attribute 'value'
# ws.unmerge_cells('A1:A3') # ValueError: list.remove(x): x not in list，必须标记完整的合并单元格区域，否则会报错
ws['A1'].value = '模块一'
ws['A3'].value = '属性1'
ws['B3'].value = '属性2'
ws['C3'].value = '属性3'
ws.merge_cells('D1:E2')
ws['D1'].value = '模块二'
ws['D3'].value = '属性1'
ws['E3'].value = '属性2'
dprint(ws['A1'].offset(1, 0).coordinate) # A2
dprint(Openpyxl.down(ws['A1']).coordinate) # A3
# 3、设置单元格样式、格式
from openpyxl.comments import Comment
cell = ws['A3']
cell.font = Font(name='Courier', size=36)
cell.comment = Comment(text="A comment", author="Author's Name")
from openpyxl.styles.colors import RED
styles = [['Number formats', 'Comma', 'Comma [0]', 'Currency', 'Currency [0]', 'Percent'],
['Informative', 'Calculation', 'Total', 'Note', 'Warning Text', 'Explanatory Text'],
['Text styles', 'Title', 'Headline 1', 'Headline 2', 'Headline 3', 'Headline 4', 'Hyperlink', 'Followed Hyperlink', 'Linked Cell'],
['Comparisons', 'Input', 'Output', 'Check Cell', 'Good', 'Bad', 'Neutral'],
['Highlights', 'Accent1', '20 % - Accent1', '40 % - Accent1', '60 % - Accent1', 'Accent2', 'Accent3', 'Accent4', 'Accent5', 'Accent6', 'Pandas']]
for i, name in enumerate(styles, start=4):
ws.cell(i, 1, name[0])
for j, v in enumerate(name[1:], start=2):
ws.cell(i, j, v)
ws.cell(i, j).style = v
# 二、测试一些功能
ws = Worksheet(ws)
dprint(ws.search('模块二').coordinate) # D1
dprint(ws.search(['模块二', '属性1']).coordinate) # D3
dprint(ws.findcol(['模块一', '属性1'], direction=1)) # 0
wb.save("demo_openpyxl.xlsx")