def xmind2xlsx(xmind_file):
"""Convert XMind file to a xlsx file"""
xmind_file = get_absolute_path(xmind_file)
logging.info('Start converting XMind file(%s) to zentao file...',
xmind_file)
testcases = get_xmind_testcase_list(xmind_file)
wb = Workbook()
ws = wb.active
ws.title = 'testcase sheet'
# TODO: modify excel style
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = 3
fileheader = [
"产品名称", "所属模块", "功能子模块", "用例标题", "步骤", "预期", "关键词", "优先级", "用例类型",
"适用阶段"
]
testcase_rows = []
for testcase in testcases:
row = gen_a_testcase_row(testcase)
testcase_rows.append(row)
ws.append(fileheader)
for row in testcase_rows:
ws.append(row)
column_widths = []
for row in ws:
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(str(cell.value)) > column_widths[i]:
column_widths[i] = len(str(cell.value))
else:
column_widths += [len(str(cell.value))]
for i, column_width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = column_width
style = NamedStyle(name='style')
style.alignment = Alignment(vertical='center', horizontal='left')
wb.add_named_style(style)
excel_file = xmind_file[:-6] + '.xlsx'
wb.save(excel_file)
# zentao_file = xmind_file[:-6] + '.csv'
# if os.path.exists(zentao_file):
# logging.info('The zentao csv file already exists, return it directly: %s', zentao_file)
# return zentao_file
#
# with open(zentao_file, 'w', encoding='utf-8') as f:
# writer = csv.writer(f)
# writer.writerows(testcase_rows)
# logging.info('Convert XMind file(%s) to a zentao csv file(%s) successfully!', xmind_file, zentao_file)
return excel_file
def to_excel(self, xlsx_path):
sites = copy.deepcopy(self.sites)
tests_config = self.get_tests()
book = Workbook()
styles = {
'default':
NamedStyle(name='default', font=Font(name='Courier New', size=10)),
'pass':
NamedStyle(name='pass',
fill=PatternFill(fill_type='solid',
start_color='C6EFCE',
end_color='C6EFCE'),
font=Font(color='006100')),
'warn':
NamedStyle(name='warn',
fill=PatternFill(fill_type='solid',
start_color='FFEB9C',
end_color='FFEB9C'),
font=Font(color='9C6500')),
'fail':
NamedStyle(name='fail',
fill=PatternFill(fill_type='solid',
start_color='FFC7CE',
end_color='FFC7CE'),
font=Font(color='9C0006'))
}
for name, style in styles.items():
book.add_named_style(style)
# Sheet "drupals"
sheet = book.active
sheet.title = 'site-info'
rows = []
# header fill
field_names = []
for col_idx, t in enumerate(tests_config):
col_name = get_column_letter(col_idx + 1)
field_names.append(t['name'])
cell = sheet.cell(1, col_idx + 1)
cell.value = t['name']
if 'comment' in t:
cell.comment = Comment(t['comment'], '')
# for test_config in self.tests_config:
# if test_config['name'] == col['name'] and 'comment' in test_config:
# #print 'comment for %s: %s' % (col['name'], test_config['comment'])
# cell.comment = Comment(test_config['comment'], '')
# fill absent tests with empty values
for site in sites:
row = []
site_info = site['site_info']
# site_info
named_results = {}
for t in site_info:
named_results[t['name']] = t
for name in field_names:
if name in named_results:
row.append(named_results[name])
else:
row.append({'result': ''})
rows.append(row)
self._excel_sheet_fill(sheet, rows, styles)
self._excel_sheet_tune(sheet, style=styles['default'])
# test groups sheets
# test_groups = self.get_test_groups()
# for test_group in test_groups:
# sheet = book.create_sheet(title=test_group)
# rows = []
# for test_suite in test_suites:
# rows.append(test_suite.get_data(group=test_group))
# self._excel_sheet_fill(sheet, rows, styles)
# self._excel_sheet_tune(sheet, style=sheet_style)
book.save(xlsx_path)
import openpyxl
import os
os.chdir(
"/home/fabio/Desktop/estudo_ti/Python/python_and_spreadsheets/Automate-the-boring-stuff-with-python"
)
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, NamedStyle
wb = Workbook()
ws = wb.active
sheet = wb.get_sheet_by_name('Sheet')
fontObj1 = NamedStyle("fontObj1")
fontObj1.font = Font(name="Times New Roman", bold=True)
wb.add_named_style(fontObj1)
ws['A1'].style = "fontObj1"
sheet["A1"] = "Bold Times New Roman"
fontObj2 = NamedStyle("fontObj2")
fontObj2.font = Font(size=24, italic=True, name="Arial", color="000111")
wb.add_named_style(fontObj2)
ws['B3'].style = 'fontObj2'
sheet["B3"] = '24pt Italic'
wb.save("styles.xlsx")
print("Done.")
def main():
input_file_dir = '../output/output.txt'
if os.path.exists(input_file_dir)==False:
print("Warning: File {} not exists, please check!".format(input_file_dir))
sys.exit()
output_file_name = 'Agent_Status.xlsx'
# Title Style
title_style = NamedStyle(name="title_style")
bd = Side(style='thin', color='000000')
title_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
title_style.fill = PatternFill("solid", fgColor=Color.TITLE.value)
title_style.font = Font(bold=True)
title_style.alignment = Alignment(horizontal="center", vertical="center")
# Running Style
running_style = NamedStyle(name="running_style")
bd = Side(style='thin', color='000000')
running_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
running_style.fill = PatternFill("solid", fgColor=Color.GREEN.value)
running_style.font = Font(bold=True)
running_style.alignment = Alignment(horizontal="center", vertical="center")
# Not Running Style
not_running_style = NamedStyle(name="not_running_style")
bd = Side(style='thin', color='000000')
not_running_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
not_running_style.fill = PatternFill("solid", fgColor=Color.YELLOW.value)
not_running_style.font = Font(bold=True)
not_running_style.alignment = Alignment(horizontal="center", vertical="center")
# Not Installed Style
not_installed_style = NamedStyle(name="not_installed_style")
bd = Side(style='thin', color='000000')
not_installed_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
not_installed_style.fill = PatternFill("solid", fgColor=Color.RED.value)
not_installed_style.font = Font(bold=True)
not_installed_style.alignment = Alignment(horizontal="center", vertical="center")
# Create new excel sheet
wb_output = Workbook()
wb_output.remove(wb_output["Sheet"])
# Add all style into workbook
wb_output.add_named_style(title_style)
wb_output.add_named_style(running_style)
wb_output.add_named_style(not_running_style)
wb_output.add_named_style(not_installed_style)
# Create Cover sheet
ws_output_cover = wb_output.create_sheet('Qualys Agent')
img = Image('../resources/logo.jpg')
img.width = 1800
img.height = 920
ws_output_cover.add_image(img, 'D2')
# Create multi sheet: Intalled - Running, Installed - NotRunning , NotInstall
ws_output_running = wb_output.create_sheet('Running')
ws_output_running.append(['Hostname', 'Version'])
ws_output_not_running = wb_output.create_sheet('Not Running')
ws_output_not_running.append(['Hostname', 'Version'])
ws_output_not_installed = wb_output.create_sheet('Not Installed')
ws_output_not_installed.append(['Hostname', 'Version'])
# Read input_file render into excel sheet
with open(input_file_dir) as input_file:
for cnt, line in enumerate(input_file):
line_array = line.split(',')
if line_array[1]=='running':
ws_output_running.append([line_array[0], line_array[2]])
elif line_array[1]=='not_running' and 'NA' in line_array[2]:
ws_output_not_installed.append([line_array[0], line_array[2]])
else:
ws_output_not_running.append([line_array[0], line_array[2]])
# adding style into Title
for i in range(ws_output_running.max_column):
ws_output_running[get_column_letter(i + 1) + str(1)].style = 'title_style'
ws_output_running.column_dimensions[get_column_letter(i + 1)].width = 40
for i in range(ws_output_not_running.max_column):
ws_output_not_running[get_column_letter(i + 1) + str(1)].style = 'title_style'
ws_output_not_running.column_dimensions[get_column_letter(i + 1)].width = 40
for i in range(ws_output_not_installed.max_column):
ws_output_not_installed[get_column_letter(i + 1) + str(1)].style = 'title_style'
ws_output_not_installed.column_dimensions[get_column_letter(i + 1)].width = 40
# adding style into Body
for i in range(ws_output_running.max_column):
for j in range(1, ws_output_running.max_row):
ws_output_running['%s%d' % (get_column_letter(i + 1), j+1)].style = 'running_style'
for i in range(ws_output_not_running.max_column):
for j in range(1, ws_output_not_running.max_row):
ws_output_not_running['%s%d' % (get_column_letter(i + 1), j+1)].style = 'not_running_style'
for i in range(ws_output_not_installed.max_column):
for j in range(1, ws_output_not_installed.max_row):
ws_output_not_installed['%s%d' % (get_column_letter(i + 1), j+1)].style = 'not_installed_style'
wb_output.save('../excel/'+ output_file_name)
