def __init__(self):
self.titles = [
"基金代码",
"基金全称",
"基金简称",
"成立日期",
"资产规模/亿元",
"跟踪标的",
"跟踪误差率",
"基金公司",
"基金公司规模/亿元",
"管理费率/%(年)",
"托管费率/%(年)",
]
self.font_title = Font(name='Arial',
size=14,
color=colors.BLACK,
bold=True)
self.font_content = Font(name='Arial',
size=12,
color=colors.BLACK,
bold=False)
self.alignment_title = Alignment(horizontal='center',
vertical='center')
self.alignment_content = Alignment(horizontal='left',
vertical='center')
def test_alignment_rotation(self):
self.worksheet.cell('A1').style = Style(
alignment=Alignment(vertical='center', text_rotation=90))
self.worksheet.cell('A2').style = Style(
alignment=Alignment(vertical='center', text_rotation=135))
self.worksheet.cell('A3').style = Style(alignment=Alignment(
text_rotation=-34))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'textRotation="90"' in xml
assert 'textRotation="135"' in xml
assert 'textRotation="124"' in xml
def test_alignment_indent(self):
self.worksheet.cell('A1').style = Style(alignment=Alignment(indent=1))
self.worksheet.cell('A2').style = Style(alignment=Alignment(indent=4))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=0))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=-1))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'indent="1"' in xml
assert 'indent="4"' in xml
#Indents not greater than zero are ignored when writing
assert 'indent="0"' not in xml
assert 'indent="-1"' not in xml
def excel_write(sheet):
#find column with IP addresses
for cols in sheet.iter_cols(min_row=1, max_col=10, max_row=1):
if cols[0].value == "IP":
ip_not_found = False
col_num = cols[0].col_idx
if cols[0].value == 'Status':
status_not_found = False
stat_col = cols[0].col_idx
#Notify if IP column not found
if ip_not_found == True:
print('Could not locate IP title in first row')
if status_not_found == True:
print('Could not locate Status title in first row')
#iterate through IPs and insert value
for rows in sheet.iter_rows(min_col=col_num,
min_row=2,
max_col=col_num,
max_row=50):
edit_ip = re.sub(r'[\n\t\s\r]+', '', str(rows[0].value))
if edit_ip in new_dict:
c = sheet.cell(row=rows[0].row, column=stat_col)
c.value = new_dict[edit_ip]
c.alignment = Alignment(horizontal='center')
def MakeFormat(self):
self.wb = openpyxl.Workbook()
self.ws = self.wb.active
side = Side(style='thin', color='000000')
border = Border(bottom=side)
width = [15, 7, 15, 15]
# 一行目の背景色と罫線の作成
for rows in self.ws['A1':'D1']:
i = 0
for cell in rows:
cell.fill = openpyxl.styles.PatternFill(patternType='solid',
fgColor='9FD9F6',
bgColor='9FD9F6')
cell.border = border
cell.value = self.header[i]
cell.alignment = Alignment(horizontal='center')
self.ws.column_dimensions[chr(i + 1 + 64)].width = width[i]
i = i + 1
# 二行目から五行目の背景色
for rows in self.ws['A2':'D5']:
for cell in rows:
cell.fill = openpyxl.styles.PatternFill(patternType='solid',
fgColor='D3EDFB',
bgColor='D3EDFB')
def set_word_wrap_across_columns(
worksheet: openpyxl.worksheet.worksheet.Worksheet, min_col: int, max_col: int
):
for row in worksheet.iter_cols(min_col=min_col, max_col=max_col):
for cell in row:
cell.alignment = Alignment(wrapText=True)
return worksheet
def set_horiz_vert_center_across_columns(
worksheet: openpyxl.worksheet.worksheet.Worksheet, min_col: int, max_col: int
):
for row in worksheet.iter_cols(min_col=min_col, max_col=max_col, min_row=4):
for cell in row:
cell.alignment = Alignment(horizontal="center", vertical="center")
return worksheet
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyleList
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0))
def _setup_styles(self):
"""Bootstrap styles"""
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._date_formats = {}
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0))
self._table_styles = TableStyleList()
self._differential_styles = DifferentialStyleList()
def format_components_tab_row_3(workbook: openpyxl.Workbook):
'''
Formats row 2 in the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
field_bg_fill = PatternFill(patternType='solid',
fgColor=Color(rgb='FF48545D', type='rgb'),
bgColor=Color(indexed=64, type='indexed', theme=0))
field_font = Font(name='Calibri', family=2, sz=11, b=True,
scheme='minor', vertAlign=None, color=Color(theme=0, type='theme'))
for col in range(1, 17):
active_cell = worksheet.cell(row=3, column=col)
active_cell.fill = field_bg_fill
active_cell.font = field_font
active_cell.alignment = Alignment(wrapText=True)
util_borders.set_outside_borders(active_cell)
return workbook
def config_xlsx(data, ll, sheetname, ws):
ws['A1'] = sheetname
ws['A1'].font = openpyxl.styles.Font(bold=True, size=16)
ws['A2'] = ''
ws['A3'] = now
ws['A3'].alignment = Alignment(horizontal='left')
ws['A4'] = ''
ws.append(data)
# sort column
if sheetname != 'IP':
for i in sorted(ll):
ws.append(i)
else:
j = 6
for i in ll:
ws['C' + str(j)] = i
j = j + 1
# adjust column dimension
max_len = 0
for i in ws.columns:
column = i[0].coordinate[:-1]
for j in i:
if len(str(j.value)) > max_len:
max_len = len(str(j.value))
adj_width = (max_len) * 1.1
ws.column_dimensions[column].width = adj_width
max_len = 0
def setAlignment(self,
range,
vertical='top',
horizon='left',
wrapText=False):
self.sheet[range].alignment = Alignment(vertical=vertical,
horizontal=horizon,
wrapText=wrapText)
def createOutputExcel(outputData):
wb = Workbook()
ws = wb.active
ws.title = 'Budget Projection'
columnNames = [
"Name", "SA Rate", "Deltek Rate", "Avg Run", "Hours", "Dollars"
]
for w in range(1, nTotalNumWeeks + 1):
nThYear = int(w / 54)
nYear = 2020 + int(nThYear)
nWeek = w - (53 * nThYear)
columnNames.append(
"Week " + str(nWeek) + " \n[" +
date.fromisocalendar(nYear, nWeek, 7).strftime("%m/%d/%y") + "]")
ws.freeze_panes = 'G3'
headerCnt = len(columnNames)
for cells in ws.iter_rows(1, 1, 1, headerCnt):
for cell in cells:
cell.value = columnNames[cell.column - 1]
cell.alignment = Alignment(wrapText=True, horizontal='center')
rowNum = 2
rowCnt = len(outputData) + 1
for name, stats in outputData.items():
for cells in ws.iter_rows(rowNum, rowNum, 1, headerCnt):
cells[0].value = name
ws.column_dimensions[cells[0].column_letter].width = 25
cells[1].value = stats['SpringAhead Rate']
cells[1].number_format = '"$"#'
ws.column_dimensions[cells[1].column_letter].width = 8
cells[2].value = stats['Deltek Rate']
cells[2].number_format = '"$"#'
ws.column_dimensions[cells[2].column_letter].width = 8
cells[3].value = stats['Avg Run']
cells[3].number_format = '0.00'
ws.column_dimensions[cells[3].column_letter].width = 7
cells[4].value = stats['Hours']
cells[4].number_format = '#,##0.00'
ws.column_dimensions[cells[4].column_letter].width = 9
cells[5].value = stats['Dollars']
cells[5].number_format = '"$"#,##0.00'
ws.column_dimensions[cells[5].column_letter].width = 14
for w in range(1, nTotalNumWeeks + 1):
cells[w + 5].value = float(stats[w])
if rowNum > rowCnt:
cells[w + 5].number_format = '"$"#,##0.00'
else:
cells[w + 5].number_format = '#,##0.00'
ws.column_dimensions[cells[w + 5].column_letter].width = 12
ws.column_dimensions[cells[w + 5].column_letter].height = 29
rowNum = rowNum + 1
setExcelFormulas(ws, rowCnt, headerCnt)
wb.save(outputFile)
def add_ind_to_cell(
active_cell: openpyxl.cell.cell.Cell) -> openpyxl.cell.cell.Cell:
"""
If the attribute is an indicator (such as chemo_ind, persistent note), this
will add the value and format the cells appropriately
"""
active_cell.value = "x"
active_cell.alignment = Alignment(horizontal="center")
return active_cell
def export_data(ws, students_dict, term, export_all=False):
# Initialise sheet headers
col_headers = [
"First Names", "Last Name", "zID", "College", "Type", "Program",
"Term", "Code", "Course", "Mark", "Grade", "WAM"
]
for row in ws.iter_rows(min_row=1, max_col=len(col_headers), max_row=1):
for i, cell in enumerate(row):
cell.value = col_headers[i]
cell.font = Font(bold=True)
r = 2
# print each student's data
for zid, student in students_dict.items():
# start = r
min_rows = 3
for c, (prop, val) in enumerate(student.__dict__.items(), 1):
# print courses
if prop == "terms":
if term in val.keys():
# print Term
ws.cell(row=r, column=c, value=term.title())
# print WAM
wam = student.wams[term]
if wam:
wam = float(student.wams[term])
ws.cell(row=r, column=c + 5, value=wam)
# print each course from course.__dict__
courses = val[term]
for course in courses:
for i, (c_prop,
c_val) in enumerate(course.__dict__.items(),
1):
if c_prop == 'grade' and c_val:
c_val = float(c_val)
if not c_val:
c_val = '-'
ws.cell(row=r, column=c + i,
value=c_val).alignment = Alignment(
horizontal='left')
r += 1
min_rows -= 1
break
#print other data
else:
ws.cell(row=r, column=c, value=val)
# pad rows
while min_rows > 0:
r += 1
min_rows -= 1
if not export_all:
r += 1
# Format columns
for i, (_, w) in enumerate(col_widths.items(), 1):
ws.column_dimensions[get_column_letter(i)].width = w
def test_alignment(self):
st = Style(alignment=Alignment(horizontal='center', vertical='center'))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'applyAlignment="1"' in xml
assert 'horizontal="center"' in xml
assert 'vertical="center"' in xml
def setAlignmentMulti(self,
range,
vertical='top',
horizon='left',
wrapText=False):
startRange = range.split(':')[0]
endRange = range.split(':')[1]
for rows in self.sheet[startRange:endRange]:
for cell in rows:
self.sheet[cell.coordinate].alignment = Alignment(
vertical=vertical, horizontal=horizon, wrapText=wrapText)
def FillInfo(self):
i = 0
for info in self.data:
for j in range(4):
self.ws.cell(row=i + 2, column=j + 1).value = info[j]
self.ws.cell(row=i + 2, column=j +
1).alignment = Alignment(horizontal='center')
self.ws.cell(row=i + 2, column=3).hyperlink = info[4]
i = i + 1
self.wb.save(self.TITLE)
os.chmod(self.TITLE, 0o666)
def create_pattern_xlsx():
"""This function to create template xlsx file with style. This file is called Match_Statistic.xlsx."""
bold_side: Side = Side('hair', color=Color())
bold_font: Font = Font('Roboto', bold=True)
bold_border: Border = Border(left=bold_side,
right=bold_side,
top=bold_side,
bottom=bold_side)
column_number: int = 1
match_statistic_xlsx: Workbook = openpyxl.Workbook()
match_statistic_xlsx.remove(match_statistic_xlsx['Sheet'])
match_statistic: Worksheet = match_statistic_xlsx.create_sheet(
'Match statistic')
match_statistic.merge_cells('H1:J2')
match_statistic.merge_cells('L1:AF2')
match_statistic['H1'].font = bold_font
match_statistic['L1'].font = bold_font
match_statistic['H1'].alignment = Alignment('center')
match_statistic['L1'].alignment = Alignment('center')
match_statistic['H1'].border = bold_border
match_statistic['L1'].border = bold_border
match_statistic['H1'].value = 'Average odds'
match_statistic[
'L1'].value = 'Названия букмекерских контор и коэффициенты на победу фаворита'
for col_name in COLUMNS:
match_statistic[
f'{get_column_letter(column_number)}3'].font = bold_font
match_statistic[
f'{get_column_letter(column_number)}3'].border = bold_border
match_statistic[f'{get_column_letter(column_number)}3'] = col_name
column_number += 1
match_statistic_xlsx.save('Match_Statistic.xlsx')
match_statistic_xlsx.close()
def formattingCells(self, rowNo: int):
self.firstRow = 16
while rowNo <= (self.firstRow + self.service_len +
1) or rowNo == 16 or rowNo == 17:
print(rowNo)
self.activeWorkSheet.merge_cells('B' + str(rowNo) + ':' + 'D' +
str(rowNo))
self.activeWorkSheet.cell(
row=rowNo, column=1).alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
self.activeWorkSheet.cell(
row=rowNo, column=2).alignment = Alignment(horizontal='left',
vertical='top',
wrap_text=True)
self.activeWorkSheet.cell(
row=rowNo, column=5).alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
self.activeWorkSheet.cell(
row=rowNo, column=7).alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
self.activeWorkSheet.cell(
row=rowNo, column=8).alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
# Increasing the size of height of row
if (len(str(self.activeWorkSheet.cell(row=rowNo,
column=2).value))) > 36:
self.activeWorkSheet.row_dimensions[rowNo].height = 31.50
# fixed
rowNo = rowNo + 1
self.chargesQtyCell = 'E' + str(rowNo + 2)
self.visitTypeCell = 'B' + str(rowNo + 2)
self.visitRatecell = 'G' + str(rowNo + 2)
def add_links_header(link: str,
col_start: int,
col_end: int,
worksheet: openpyxl.worksheet.worksheet.Worksheet,
output_file: str = None):
"""
Adds linked cells to the header for any tab on the PowerPlan DCW. The
output_file is needed so that the links will point to the different tabs
of the same file, and it needs the file name for some reason
"""
if output_file is None:
output_file = 'test_output.xlsx'
link_bg_fill = PatternFill(patternType='solid',
fgColor=Color(tint=-0.25, type='theme',
theme=0),
bgColor=Color(indexed=64,
type='indexed',
theme=0))
link_font = Font(name='Calibri',
family=2,
sz=11,
u='single',
scheme='minor',
vertAlign=None,
color=Color(theme=10, type='theme'))
link_alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
initial_cell = worksheet.cell(row=1, column=col_start)
initial_cell.value = link
initial_cell.hyperlink = '{}#\'{}\'!A1'.format(output_file, link)
initial_cell.style = 'Hyperlink'
for x in range(col_start, col_end + 1):
active_cell = worksheet.cell(row=1, column=x)
active_cell.font = link_font
active_cell.alignment = link_alignment
active_cell.fill = link_bg_fill
active_cell = util_borders.set_outside_borders(active_cell)
worksheet.merge_cells(start_row=1,
start_column=col_start,
end_row=1,
end_column=col_end)
return worksheet
def add_frame(self, all_sheet):
side = Side(style='thin', color='000000')
border = Border(top=side, bottom=side, left=side, right=side)
pass_A = re.compile(r'A[0-9]') #A列のみ省きたいので
for sheet in all_sheet: #シートごとに枠線付け
for row in sheet:
for cell in row: #セルごとに回す
if sheet[cell.
coordinate].value != None: #該当のセルがNone以外==何かしらある
cell.alignment = Alignment(
horizontal='center', #全てを中央揃えに
vertical='center',
wrap_text=False)
if pass_A.match(cell.coordinate): #A行には枠線をつけない
continue
sheet[cell.coordinate].border = border #枠線つけ
def close_spider(self, spider: scrapy.Spider):
print('close_spider', self.ws.max_row, self.ws.max_column)
for row in range(1, self.ws.max_row + 1):
for col in range(1, self.ws.max_column + 1):
try:
# print(col)
cell_index = '%s%d' % (self.num_to_letter(col), row)
print(cell_index)
self.ws[cell_index].font = Font(
size=12 if row > 1 else 16,
color=colors.BLACK,
)
self.ws[cell_index].alignment = Alignment(
horizontal='left' if row > 1 else 'center',
vertical='center',
)
except Exception as e:
print("Exception", e)
self.wb.save('csi_500.xlsx')
def write_operations_to_cell(excelfilename,
sheettitle,
cell_num,
operation,
weapTextopt=True,
fill_line_color=None,
fix_size=13,
bold=False):
wb = load_workbook(excelfilename)
ws = wb.active
ws.title = sheettitle
ws[cell_num].alignment = Alignment(wrapText=weapTextopt, vertical='center')
ws[cell_num] = operation
if fill_line_color:
for i in range(12):
color_fill_cellnum = "".join([chr(65 + i), cell_num[1:]])
ws[color_fill_cellnum].fill = PatternFill(fill_type='solid',
fgColor=fill_line_color)
ws[cell_num].font = Font(size=fix_size, bold=bold)
wb.save(filename=excelfilename)
def optimize(excel):
"""
测试报告优化，包括文字居中和边框设置
:param excel:
:return:
"""
if not os.path.isfile(excel):
logging.warning(f'optimize excel fail , file : {excel}')
return
wwb = openpyxl.load_workbook(excel)
side = Side(style='thin', color='FF000000')
for sheet in wwb.worksheets:
for row in sheet.iter_rows():
for cell in row:
cell.border = Border(left=side,
right=side,
top=side,
bottom=side)
cell.alignment = Alignment(horizontal='left',
vertical='center',
wrapText=True)
wwb.save(excel)
def style_range(worksheet, cell_range, bg_color=None, align=None):
"""
Apply styles to a range of cells
https://openpyxl.readthedocs.io/en/default/styles.html#applying-styles
:param worksheet: Excel worksheet instance
:param cell_range: An excel range to style (e.g. A1:F20)
:param bg_color: Background color in hex (e.g. ffffff or d7e4bc)
:param align: An openpyxl Alignment object
:return: None
"""
bg_color = bg_color or 'ffffff'
align = align or Alignment(
horizontal='left', vertical='center', wrapText=True)
bd = Side(style='thin', color='000000')
cell_border = Border(left=bd, right=bd, top=bd, bottom=bd)
for row in worksheet[cell_range]:
fill_color = PatternFill('solid', fgColor=bg_color)
for cell in row:
cell.border = cell_border
cell.fill = fill_color
cell.alignment = align
def write_day(day, xl_parser):
"""Setup day to table cells"""
sheet = xl_parser.work_book.get_active_sheet()
# Count summary rows in the day
sum_rows = 0
for it in day.notes + day.kvants:
sum_rows += len(it.teachers)
# Shift the pointer to setup the table without splitting days
while xl_parser.cur_row % SHIFT_ROW == 0 or (
xl_parser.cur_row % SHIFT_ROW) + sum_rows > SHIFT_ROW:
xl_parser.cur_row += 1
# Merge cells to setup the date
sheet.merge_cells(start_column=COLUMNS['title'],
end_column=COLUMNS['class'],
start_row=xl_parser.cur_row,
end_row=xl_parser.cur_row)
# Setup the date of the day at the center and bold it
cell = sheet.cell(row=xl_parser.cur_row, column=COLUMNS['title'])
cell.value = day.date
cell.alignment = Alignment(horizontal='center')
cell.font = Font(bold=True)
# Setup title's borders
for column in range(COLUMNS['title'], COLUMNS['class'] + 1):
sheet.cell(row=xl_parser.cur_row,
column=column).border = border('medium')
# Iterate rows of the table
xl_parser.cur_row += 1
for kvant in day.kvants:
write_note(kvant, xl_parser)
for note in day.notes:
write_note(note, xl_parser)
pays["A1"] = "Days"
pays["A2"] = "Date\\Type"
# Merge cells and set names
for i in range(1, 8):
# Find two side columns to merge
curr_col = i * 3 - 1
next_col = i * 3 + 1
pays.merge_cells(start_row=1,
end_row=1,
start_column=curr_col,
end_column=next_col)
curr_cell = pays.cell(row=1, column=curr_col, value=days[i - 1])
curr_cell.alignment = Alignment(horizontal='center')
curr_cell.fill = PatternFill(bgColor=colors[i - 1],
fill_type="gray0625")
# Give them spec
for i in range(1, 8):
for j in range(3):
curr_cell = pays.cell(row=2,
column=i * 3 - 1 + j,
value=spec[j])
curr_cell.alignment = Alignment(horizontal='center')
curr_cell.fill = PatternFill(bgColor=colors[i - 1],
fill_type="gray0625")
correct_table(pays)
encoding="cp932",
names=("JANcode", "商品名", "分析値", "構成比", "累計構成比", "判定"))
#dfを降順に並び替え
df_s = df.sort_values("分析値", ascending=False)
#エクセルファイルを作成・データフレームの書き込み
df_s.to_excel("ABCanalysis.xlsx", sheet_name="ABCanalysis", index=False)
wb = px.load_workbook("ABCanalysis.xlsx")
ws = wb["ABCanalysis"]
#最終行を取得
max_row = ws.max_row
#最終行の次の行に合計・合計値（数式）を記入
ws.cell(row=max_row + 1, column=2).value = "合計"
ws.cell(row=max_row + 1, column=2).alignment = Alignment(horizontal='center',
vertical='bottom')
ws.cell(row=max_row + 1, column=3).value = "=SUM(C{}:C{})".format(2, max_row)
for row in range(2, max_row + 2):
#書式設定を変更
ws.cell(row=row, column=1).number_format = "0"
ws.cell(row=row, column=1).alignment = Alignment(horizontal='center')
ws.cell(row=row, column=3).number_format = "#,##0"
ws.cell(row=row, column=6).alignment = Alignment(horizontal='center')
#構成比の数式の記入・書式設定（％）
ws.cell(row=row, column=4).value = "=C{}/$C${}".format(row, max_row + 1)
ws.cell(row=row, column=4).number_format = "0.00%"
ws.cell(row=row, column=5).number_format = "0.00%" #累計構成比の書式設定
#累計構成比を記入
excelbook="geoparam.xlsx"
wb = Workbook()
ws = wb.create_sheet(title=version)
font = Font(name="Arial", size=12)
maxchar = [0,0,0,0]
header = ["Category", "Varname", "value(cm)", "Meaning"]
for ic in range(0, len(header)):
ws.cell(row=1, column=ic+1).value=header[ic]
ws.cell(row=1, column=ic+1).font=font
maxchar[ic] = len(header[ic])
for ir in range(0, len(comments)):
for ic in range(0, len(header)):
ws.cell(row=ir+2, column=ic+1).value = comments[ir][ic]
ws.cell(row=ir+2, column=ic+1).font = font
if len(comments[ir][ic]) > maxchar[ic]:
maxchar[ic] = len(comments[ir][ic])
if ic == 2:
ws.cell(row=1, column=ic+1).alignment = Alignment(horizontal="right")
colname=["A","B","C","D"]
for ic in range(0, len(header)):
ws.column_dimensions[colname[ic]].width = int(maxchar[ic]*1.4)
wb.save(excelbook)
print "Geometry parameters are written in "+excelbook
