def build_graph_in_excel(self, ws, table, start_row_in_excel, start_column_in_excel):
columns_list_from_db = self.get_columns_list_without_id(table)[1:-1].split(', ')
count_rows_in_table_db = self.get_count_rows(table)+1
start_column_number = self.get_number_for_letter(start_column_in_excel) + 1
chart = BarChart()
chart.title = '{}'.format(table)
chart.style = 10
#chart.x_axis.title = 'TEst'
chart.y_axis.title = 'Percentage'
cats = Reference(ws,
min_col=start_column_number,
min_row=start_row_in_excel + 1,
max_row='{}'.format(count_rows_in_table_db)
)
data1 = Reference(ws,
min_col=10,
min_row=1,
max_row='{}'.format(count_rows_in_table_db)
)
data2 = Reference(ws, min_col=14, min_row=1, max_row='{}'.format(count_rows_in_table_db))
chart.add_data(data1, titles_from_data=True)
chart.add_data(data2, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'A15')
def add_charts(sheet, mark, model, data, cats):
"""draw charts with median and average mileages for each year"""
chart = BarChart()
chart.title = mark + " " + model
chart.type = "col"
chart.y_axis.title = 'Mileage'
chart.x_axis.title = 'Year of prod.'
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, "E3")
def ExportToExcel():
h = open('final.txt','r')
h = h.read()
book = openpyxl.Workbook()
sheet1 = book.active
sheet1.cell(column=1,row=1,value='Server')
sheet1.cell(column=2,row=1,value='Consumption')
sheet1.cell(column=3,row=1,value='Output')
sheet1.cell(column=4,row=1,value='Average')
sername = [sername.split()[0] for sername in h.splitlines()]
consump = [float(consump.split()[1].replace(',','')) for consump in h.splitlines()]
output = [int(output.split()[2]) for output in h.splitlines()]
for row in range(len(sername)):
_ = sheet1.cell(column=1, row=row+2, value="%s" %sername[row])
_ = sheet1.cell(column=2, row=row+2, value=consump[row])
_ = sheet1.cell(column=3, row=row+2, value=output[row])
_ = sheet1.cell(column=4, row=row+2, value="=B%d/C%d" %(row+2,row+2))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=2, min_row=1, max_row=len(sername)+1, max_col=3)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I1")
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=4, min_row=1, max_row=len(sername)+1, max_col=4)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I20")
global name
name = "EnergyConsumption_{}.xlsx".format(datetime.datetime.now().date())
book.save(name)
return
def chart_openpyxl(worksheet):
global TOTAL_DAYS
print 'generate Chart...'
# create open-high-low-close chart
stock = StockChart()
dates = Reference(worksheet, min_col=1, min_row=31, max_row=TOTAL_DAYS+1)
data = Reference(worksheet, min_col=2, max_col=5, min_row=31, max_row=TOTAL_DAYS+1)
stock.add_data(data, titles_from_data= True)
stock.set_categories(dates)
for s in stock.series:
s.graphicalProperties.line.noFill = True
stock.hiLowLines = ChartLines()
stock.upDownBars = UpDownBars()
# Excel is broken and needs a cache of values in order to display hiLoLines :-/
from openpyxl.chart.data_source import NumData, NumVal
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
# add dummy cache
stock.series[-1].val.numRef.numCache = cache
# create 5-30MA chart
bar = BarChart()
data = Reference(worksheet, min_col=9, min_row=31, max_row=TOTAL_DAYS+1)
bar.add_data(data, titles_from_data=False)
bar.set_categories(dates)
# merge K-Line & 5-30MA chart
stock_tmp = deepcopy(bar)
bar_tmp = deepcopy(stock)
stock_tmp.title = "TWSE Mometum Chart"
stock_tmp.height = 15
stock_tmp.width = 25
stock_tmp.y_axis.axId = 20
stock_tmp.z_axis = bar_tmp.y_axis
stock_tmp.y_axis.crosses = "max"
stock_tmp.legend = None # hidden series label name
bar_tmp.y_axis.majorGridlines = None
bar_tmp.y_axis.title = "Price"
stock_tmp += bar_tmp
worksheet.add_chart(stock_tmp, "A{}".format(TOTAL_DAYS+2))
def excel_mtf_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "MTF Chart"
chart1.y_axis.title = 'MTF'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=2, min_row=1, max_row=19, max_col=2)
# Select data only
cats = Reference(ws, min_col=1, min_row=2, max_row=18)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 18
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G1")
def excel_sfr_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "SFR Chart"
chart1.y_axis.title = 'SFR'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=5, min_row=1, max_row=37, max_col=5)
# Select data only
cats = Reference(ws, min_col=4, min_row=2, max_row=37)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 37
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G21")
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'E2')
wb.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
corrected_cell_heading = sheet.cell(1, 4)
corrected_cell_heading.value = 'prices'
for row in range(2, sheet.max_row + 1):
first_price = sheet.cell(row, 3)
corrected_price = first_price.value * 10
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'f5')
wb.save(filename)
def file_process(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
#take values of column 3 of each row and procees it and save it in new column
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
#create a chart in excel sheet
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def processWorkbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
price = sheet.cell(row, 3)
newPrice = price.value * 0.8
newPriceCell = sheet.cell(row, 4)
newPriceCell.value = newPrice
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart)
wb.save(filename)
def process_workbook(filename):
wb=xl.load_workbook(filename)
wb['Sheet1'] = sheet
#How to use on any values
for row in range(2,sheet.max_row + 1)
cell=sheet.cell(row,3)
corrected_price= cell.value * 0.9
sheet.cell(row, 4) = corrected_price_cell
#cell object in the spreadsheet
corrected_price_cell.value=corrected_price
#after the for loop
values=Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4
max_col=4)
#select rows from two to four
chart=BarChart()
chart.add_data(values)
#add our values
sheet.add_chart(chart,"e2")
#e2 is here the top left corner of the chart will be
#Open py excel
wb.save(filename)
def update_workbook(file):
book = xls.load_workbook(file)
sheet = book['Sheet1']
print(sheet.max_row)
for row in range(2, sheet.max_row + 1):
corrected_value = sheet.cell(row, 3).value * 0.9
sheet.cell(row, 4).value = corrected_value
book.save('trans01.xlsx')
ref = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(ref)
sheet.add_chart(chart, 'A15')
book.save('trans02.xlsx')
def get_spreadsheet(filename, name_of_sheet):
# as xl is an alias of openpyxl
wb = xl.load_workbook(filename)
# a var contain the 1st sheet
sheet = wb[name_of_sheet]
# It is case sensitive
# two method to get data from the cell in spreadsheet
cell = sheet['A1']
cell2 = sheet['b1']
cell3 = sheet['C1']
# cell = sheet.cell(1,1)
# .value to get the value in the spreadsheet
print('*' * 53)
print('EXCEL INFOR'.center(53))
print('*' * 53)
print(
str(cell.value).ljust(20),
str(cell2.value).ljust(20),
str(cell3.value).rjust(10))
# print(cell2.value)
# print(cell3.value)
print('*' * 53)
# the number of rows in spreadsheet
for row in range(2, sheet.max_row + 1):
data = sheet.cell(row, 3) # (row, column)
discount = data.value * 0.9
new_row = sheet.cell(row, 4)
new_row.value = round(discount, 2)
# Draw the barchart
values = Reference(
sheet, min_row=2, max_row=sheet.max_row, min_col=4,
max_col=4) # Get the data from row to row, and from column to column
chart = BarChart() # declare the type of chart
chart.add_data(values) # fill the data in the chart
sheet.add_chart(chart, 'e2') # where to add the drew chart
# method to save new modified spreaddsheet
wb.save('transaction.xlsx')
def fun_transaction(filename):
# loading workbook and sheets
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
print(".........................................................")
# print value from sheet of specific cell
# cell = sheet["a1"] # both statements mean same value
cell = sheet.cell(1, 1) # 1st row, 1st column
print(cell.value)
print("...........................................................")
# to print number of rows
for i in range(1, sheet.max_row + 1):
print(i)
print("...........................................................")
# to print values of 3rd row
for i in range(2, sheet.max_row + 1):
cell = sheet.cell(i,
3) # i = row(iterates from 2 3 4 and column is 3rd)
print(cell.value)
# to add a column with some defines operations
correct_price = cell.value * 0.9
correct_cell = sheet.cell(i, 4)
correct_cell.value = correct_price
# adding charts in sheet using 4th column only
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4) # assigning values for chart
chart = BarChart()
chart.add_data(values) # giving chart with values to be displayed
sheet.add_chart(
chart, "a6") # commanding to add chart to the sheet with coordinates
wb.save(filename) # correct prices and chart updated
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Plan1']
for row in range(2, sheet.max_row):
cell = sheet.cell(row, 3)
correct_price = float(cell.value) * 0.9
correct_price_cell = sheet.cell(row, 5)
correct_price_cell.value = correct_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=5,
max_col=5)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'f2')
wb.save('items2.xlsx')
def process_workbook(filename):
workbook = xl.load_workbook(filename)
sheet = workbook['Sheet1']
# finds price values in the cells and take out 10% and store them on a new column
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
# creates a barchart to displayed the fixed prices from column 4
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
workbook.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.1
corrected_price_cell = sheet.cell(row, 4) #this makes a new column
corrected_price_cell.value = corrected_price
#reference class allows you to select the range of values you want to use
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "e2")
wb.save('transactions_new2.xlsx')
def write_xlsx(data, file_name):
"""Method to write xls given data."""
try:
wb = load_workbook('%s/case_load.xltm' % (MEDIA_ROOT))
sheets = wb.get_sheet_names()
for sheet in sheets:
ws = wb.get_sheet_by_name(sheet)
if sheet != 'Graph':
ws['C3'] = file_name
else:
# print 'TTTTT', ws['B2'].value
values = Reference(
ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart)
# ws0 = wb["Jul-15"]
# ws0.title = "July-15"
xls_name = '%s/%s.xlsx' % (MEDIA_ROOT, file_name)
wb.save(xls_name)
# Test sheet rename after save
wb1 = load_workbook(xls_name)
ws1 = wb1["Jul-15"]
ws1.title = "Jul-17"
ws2 = wb1["Qtr1"]
cell_range = ws2['C9':'O139']
# for row in ws.iter_rows('A1:C2'):
for row in cell_range:
for cell in row:
value = cell.value
if value and "Jul-15'!" in value:
new_value = value.replace("Jul-15'!", "Jul-17'!")
print new_value
cell.value = new_value
xlsm_name = '%s/%s.xlsm' % (MEDIA_ROOT, file_name)
wb1.save(xlsm_name)
except Exception, e:
raise e
def chart():
wb = load_workbook('/home/votebot/mysite/commands/voting.xlsx')
ws = wb.active
#pie = PieChart()
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Результаты зрительского голосования"
chart1.y_axis.title = ''
chart1.x_axis.title = ''
labels = Reference(ws, min_col=1, min_row=2, max_row=11)
data = Reference(ws, min_col=2, min_row=2, max_row=11)
#pie.add_data(data, titles_from_data=True)
#pie.set_categories(labels)
#pie.title = "Результаты"
#ws.add_chart(pie, "D1")
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(labels)
chart1.shape = 4
ws.add_chart(chart1, "D1")
wb.save('/home/votebot/mysite/commands/voting.xlsx')
return 'chart added', '', None
def create_bar_chart_1(file_path):
"""
插入柱形图
:param file_path: Excel 文件路径
:return: None
"""
wb = load_workbook(file_path)
st = wb.active
data1 = Reference(st, min_col=2, min_row=1, max_row=7, max_col=3)
cats1 = Reference(st, min_col=1, min_row=2, max_row=7)
chart1 = BarChart()
chart1.type = "col"
chart1.title = "日均值对比"
# chart1.y_axis.title = '数值'
chart1.x_axis.title = st.cell(column=1, row=1).value
chart1.add_data(data1, titles_from_data=True)
chart1.set_categories(cats1)
st.add_chart(chart1, 'A8')
wb.save(file_path)
def designgraphs(wbpath):
wbpath = wbpath + 'Results.xlsx'
wb = load_workbook(wbpath)
ws = wb.active
StatusData = Reference(ws, min_col=1, min_row=3, max_col=3, max_row=4)
StatusChart = BarChart()
StatusChart.style = 10
StatusChart.title = "Test Execution Status"
StatusChart.y_axis.title = 'Test cases executed'
StatusChart.x_axis.title = 'Test case status'
StatusChart.add_data(StatusData, titles_from_data=True)
ws.add_chart(StatusChart, "A7")
BugsData = Reference(ws, min_col=7, min_row=3, max_col=10, max_row=4)
BugsPriorityChart = BarChart()
BugsPriorityChart.style = 10
BugsPriorityChart.title = "Test Execution Status"
BugsPriorityChart.y_axis.title = 'Test cases executed'
BugsPriorityChart.x_axis.title = 'Test case status'
BugsPriorityChart.add_data(BugsData, titles_from_data=True)
ws.add_chart(BugsPriorityChart, "F7")
wb.save(wbpath)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
for each_row in range(2, sheet.max_row + 1):
cell = sheet.cell(each_row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(each_row, 4)
corrected_price_cell.value = corrected_price
print(cell.value)
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "e2")
wb.save("transactions2.xlsx")
def excel_file(filename):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
cell = sheet["a1"]
cell = sheet.cell(1, 1)
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 4
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "f2")
wb.save("transactions.xlsx")
def process_workbook(input_filename, output_filename):
wb = xl.load_workbook(input_filename)
sheet = wb['Sheet1']
cell = sheet.cell(2, 1)
for row in range(2, sheet.max_row + 1):
print(sheet.cell(row, 5).value)
cell = sheet.cell(row, 5)
correct_price = cell.value * 1.14
correct_price_cell = sheet.cell(row, 6)
correct_price_cell.value = correct_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=6,
max_col=6
)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'H1')
wb.save(output_filename)
def turn_filter(bar_dict):
file_path = base_path + file_name
if file_path:
# 用pandas读取Excel,获取指定sheet的里的数据
df = pd.read_excel(file_path, sheet_name=sheet_name)
# 创建时间筛选数据,根据数据生成多个条件,key为tag显示,len()为需要展示的数据
rows = [('轮次', '缺陷数量')]
char_index = 1
for key in bar_dict.keys():
start_date = bar_dict[key]['start_date'] # 开始时间
end_date = bar_dict[key]['end_date'] # 结束时间
# 筛选列表数据
data = df[(df['创建日期'] >= start_date) & (df['创建日期'] <= end_date)]
rows.append((key, len(data)))
# print(key, start_date, end_date, len(data))
char_index = char_index + 1
# 打开文件，创建新的sheet
wb = load_workbook(file_path)
ws = wb.create_sheet('缺陷轮次统计')
# 添加数据
for row in rows:
ws.append(row)
histogram_chart = BarChart()
histogram_chart.type = "col"
histogram_chart.style = 10
histogram_chart.title = "缺陷轮次统计"
histogram_chart.y_axis.title = '数量'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=2)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
histogram_chart.add_data(data, titles_from_data=True)
histogram_chart.set_categories(cats)
histogram_chart.shape = 4
# 设置图表位置
ws.add_chart(histogram_chart, "A{0}".format(char_index + 6))
wb.save(file_path)
def A():
sheetA = wb.create_sheet('전체 출원동향', 0)
A그래프data = Data.전체출원동향()
for r in dataframe_to_rows(A그래프data, index=False, header=True):
sheetA.append(r)
sheetA.insert_cols(2)
for row, cellobj in enumerate(list(sheetA.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
chartA1 = BarChart()
dataA1 = Reference(sheetA, min_col=3, min_row=1, max_row=21)
catsA1 = Reference(sheetA, min_col=2, min_row=2, max_row=21)
chartA1.add_data(dataA1, titles_from_data=True)
chartA1.set_categories(catsA1)
chartA1.y_axis.majorGridlines = None
chartA2 = LineChart()
dataA2 = Reference(sheetA, min_col=4, min_row=1, max_row=21)
chartA2.add_data(dataA2, titles_from_data=True)
chartA2.y_axis.majorGridlines = None
chartA2.y_axis.axId = 2000
# y축 위치 변경
chartA2.y_axis.crosses = 'max'
# 그래프 합치기
chartA1 += chartA2
chartA1.width = 15
chartA1.height = 10
chartA1.legend.position = 't'
chartA1.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True)) # 테두리 제거
sheetA.add_chart(chartA1, 'F2')
global savepath
savepath = Data.Save()
def process_workbook(filename, column_to_modify, column_data, chart_boolean,
name):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
column_name = sheet.cell(1, column_data)
column_name.value = name
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, column_to_modify)
corrected_price = cell.value * 0.5
corrected_price_cell = sheet.cell(row, column_data)
corrected_price_cell.value = corrected_price
if chart_boolean:
values = Reference(sheet,
min_col=column_data,
max_col=column_data,
max_row=sheet.max_row,
min_row=2)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
cell = sheet.cell(1, 4)
cell.value = 'Corrected Price'
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_column = sheet.cell(row, 4)
corrected_price_column.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'A7')
wb.save('FinalResult.xlsx')
def insertarGraficoMercadoTC(ws):
maxCol = col2num(getMaxCol(ws, 'B', 46))
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=47, max_col=maxCol)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.y_axis.scaling.min = 0
c1.y_axis.majorGridlines = None
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=48, max_col=maxCol)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c1.z_axis = c2.y_axis
categories = Reference(ws, min_col=2, min_row=46, max_col=maxCol)
c1.set_categories(categories)
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2
ws.add_chart(c1, "A54")
def process_work_book(filename):
wortk_book = xl.load_workbook(filename)
sheet = wortk_book['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
corrected_price_heading = sheet.cell(1, 4)
corrected_price_heading.value = 'Corrected_Price'
value = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(value)
sheet.add_chart(chart, 'e2')
wortk_book.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for i in range(2, 52):
for j in range(3, 8):
cell = sheet.cell(i, j)
cell.value = random.randint(0, 100)
total_value_cell = sheet.cell(i, 8)
total_value_cell.value = sheet.cell(i, 3).value + sheet.cell(
i, 4).value + sheet.cell(i, 5).value + sheet.cell(
i, 6).value + sheet.cell(i, 7).value
avg_value_cell = sheet.cell(i, 9)
avg_value_cell.value = total_value_cell.value / 5
values = Reference(sheet, min_row=2, max_row=51, min_col=9, max_col=9)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'j2')
wb.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
cell = sheet["a1"] # to show how to access a cell
cell = sheet.cell(1, 1) # to show how to access a cell
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "e2")
wb.save(filename)
def update_workbook(filename):
# saves given filename passed in as workbook
workbook = oxl.load_workbook(filename)
# stores the workbook sheet in 'sheet'
sheet = workbook['Sheet1']
# Loop runs through each of the sales figures, generates the commission, and stores in a new cell
for row in range(3, sheet.max_row + 1):
cell = sheet.cell(row, 3)
commission = cell.value * 0.1
new_cell = sheet.cell(row, 4)
new_cell.value = commission
# creates boundaries for the bar chart accessible values
values = Reference(sheet,
min_row=3,
max_row=sheet.max_row,
min_col=4,
max_col=4)
# forms the bar chart and saves the changes to the workbook
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'F3')
workbook.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
print("*" * 15)
sheet['d1'].value = "New Price"
for row in range(2, sheet.max_row + 1):
cell = sheet['c' + str(row)]
corrected_price = cell.value * 0.9
corrected_price_cell = sheet['d' + str(row)]
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def ChartDraw(ws, ChartName, MinRow, MinColumn, MaxRow, MaxColumn, CatsCol,
CatsMinRow, CatsMaxRow, ChartPos, xTitle, yTitle):
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = ChartName
chart.y_axis.title = yTitle
chart.x_axis.title = xTitle
data = Reference(ws,
min_row=MinRow,
min_col=MinColumn,
max_row=MaxRow,
max_col=MaxColumn)
cats = Reference(ws,
min_row=CatsMinRow,
max_row=CatsMaxRow,
min_col=CatsCol)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, ChartPos)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
#Add chart to current sheet (use the vaues in the new column added for the chart)
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
# max_row = number of rows in the file
# start at 2 because 1st line is for headers
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3) # cells from the 3rd column
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def save_excel_data():
count = 0
wb=openpyxl.Workbook()
ws1=wb.active
ws1=wb.create_sheet("mysheet2")
ws1.append(['이름','국어','영어'])
for n in myDataList:
count += 1
ws1.append([n['name'],n['korean'],n['english']])
#Chart 1
chart1 = BarChart()
chart1.style=11
chart1.title='Bar chart'
chart1.x_axis.title='이름'
chart1.y_axis.title='점수'
data = Reference(ws1, min_col=1, min_row=1, max_row=count+1, max_col=3)
cat = Reference(ws1, min_col=1, min_row=2, max_row=count+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cat)
ws1.add_chart(chart1, 'F1')
wb.save('day2result.xlsx')
print('write...done')
)
wb = Workbook()
ws = wb.active
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 10, 40, 50, 20, 10, 50],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# Create a second chart
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Humans"
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Bar Chart"
chart.y_axis.title = 'Test number'
chart.x_axis.title = 'Sample length (mm)'
xData = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
yData = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(xData, titles_from_data=True)
chart.set_categories(yData)
chart.shape = 4
ws.add_chart(chart, "B10")
wb.save('data/barCharts.xlsx')
(2,),
(3,),
(2,),
(3,),
(3,),
(1,),
(2,),
]
for r in rows:
ws.append(r)
c = BarChart()
data = Reference(ws, min_col=1, min_row=1, max_row=8)
c.add_data(data, titles_from_data=True)
c.title = "Chart with patterns"
# set a pattern for the whole series
series = c.series[0]
fill = PatternFillProperties(prst="pct5")
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
# set a pattern for a 6th data point (0-indexed)
pt = DataPoint(idx=5)
pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz")
series.dPt.append(pt)
ws.add_chart(c, "C1")
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Membership'
chart1.x_axis.title = 'Month'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
chart3 = deepcopy(chart1)
for s in c2.series:
s.graphicalProperties.line.noFill = True
c2.hiLowLines = ChartLines()
c2.upDownBars = UpDownBars()
c2.title = "Open-high-low-close"
# add dummy cache
c2.series[-1].val.numRef.numCache = cache
ws.add_chart(c2, "G10")
# Create bar chart for volume
bar = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=6)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
from copy import deepcopy
# Volume-high-low-close
b1 = deepcopy(bar)
c3 = deepcopy(c1)
c3.y_axis.majorGridlines = None
c3.y_axis.title = "Price"
b1.y_axis.axId = 20
b1.z_axis = c3.y_axis
b1.y_axis.crosses = "max"
b1 += c3
c3.title = "High low close volume"
continue
else:
wb = Workbook()
sheet = wb.active
monty = opassage[0:8]
preview = "".join([s for s in monty if s != " "])
sheet.title = "{}".format(preview)
i = 1
for word, occur in ordered_dict:
sheet['A' + str(i)] = word
sheet['B' + str(i)] = occur
i += 1
values = Reference(sheet, min_col=1,min_row=1,max_col=2,max_row=len(ordered_dict))
chart = BarChart()
chart.type = "col"
chart.style = 11
chart.title = "Word Frequency"
chart.y_axis.title = "Frequency"
chart.x_axis.title = "Word"
chart.add_data(values, titles_from_data=True)
sheet.add_chart(chart, "D2")
if not(isdir("C:/WordCounterExports")):
mkdir("C:/WordCounterExports/")
name = "wc{}.xlsx".format(preview)
wb.save("C:/WordCounterExports/{}".format(name))
print("Workbook successfully created at C:/WordCounterExports/ named {}".format(name))
continue
def pop_excl (sv_dict, ClusName):
wb = openpyxl.Workbook ()
sh = wb.active
count1 = 0
count2 = 2
alph = ['a', 'b', 'c', 'd']
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
sh.title = 'HighLevel'
sh['a1'] = 'StorageView'
sh['b1'] = 'Size(G)'
for i in sv_dict:
sh[alph[count1] + str (count2)] = i
count1 += 1
sh[alph[count1] + str (count2)] = float (sv_dict[i][-1][-1])
count2 += 1
count1 = 0
count2 = 2
for i in sv_dict:
sh = wb.create_sheet (i)
sh = wb.get_sheet_by_name (i)
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
f = sh['c1']
f.font = Font (bold=True)
f = sh['d1']
f.font = Font (bold=True)
sh['a1'] = 'LunID'
sh['b1'] = 'Name'
sh['c1'] = 'VPD'
sh['d1'] = 'Size(G/T)'
for j in range (len (sv_dict[i])):
for k in range (4):
sh[alph[count1] + str (count2)] = sv_dict[i][j][k]
count1 += 1
count2 += 1
count1 = 0
count2 = 2
logging.debug('Start of chart')
l = len(sv_dict)
sh = wb.get_sheet_by_name ('HighLevel')
logging.debug('sheets: %s' % (wb.get_sheet_names ()))
logging.debug('sh: %s' % (sh.title))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 11
chart1.title = "VPlex Capacity Report"
chart1.y_axis.title = 'Size'
chart1.x_axis.title = 'View Name'
logging.debug('len of sv_dict: %d' % (l))
data = Reference(sh, min_col=2, min_row=2, max_row=l + 1, max_col=2)
cats = Reference(sh, min_col=1, min_row=2, max_row=l + 1)
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(cats)
chart1.top = 100
chart1.left = 30
chart1.width = 27
chart1.height = 10
chart1.shape = sh.add_chart(chart1, "D2")
wb.save (ClusName)
return 0
def ronava_bar_chart(writingSheet, dataSheet, params):
# TODO add dictionary in parameters to avoid overlapping
if params["use"] == "bars":
data = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"],
max_row=params["data_max_row"],
max_col=params["data_max_col"],
)
cats = Reference(
dataSheet,
min_col=params["cats_min_col"],
min_row=params["cats_min_row"],
max_row=params["cats_max_row"],
max_col=params["cats_max_col"],
)
chart = BarChart()
chart.type = params["type"]
chart.style = 12
# chart.grouping = "stacked"
chart.title = params["title"]
chart.y_axis.title = params["y_axis"]
chart.x_axis.title = params["x_axis"]
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height = params["heigth"]
chart.width = params["width"]
writingSheet.add_chart(chart, "D2")
elif params["use"] == "single":
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.style = 12
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
writingSheet.add_chart(c1, "D4")
else:
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.y_axis.majorGridlines = None
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
c1.style = 12
# Create a second chart
c2 = LineChart()
v2 = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"] + 1,
max_col=params["data_max_col"],
)
c2.series = [Series(v2, title_from_data=True)]
c2.y_axis.axId = 20
c2.y_axis.title = "Porcentaje Produccion"
# Assign the y-axis of the second chart to the third axis of the first chart
c1.z_axis = c2.y_axis
c1.y_axis.crosses = "max"
c1 += c2
writingSheet.add_chart(c1, "D4")
def draw_excel_charts(sblog_dict, excel_filename):
"""
This function accepts a defaultdict which contains all the data of tps and rt
an will create an Microsoft Excel spreadsheet with charts.
:param sblog_dict:
:param excel_filename:
:return:
"""
clean_dict = data_cleansing(sblog_dict)
tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data = [], [], [], [], []
workload_cols_rows = {}
workload_types = set()
col_name_parsed = False
for key in clean_dict.keys():
data_list = clean_dict[key]
col_name, tps, rt, avg_rt, tps_std, rt_std = zip(*data_list)
if not col_name_parsed:
rt_data.append(col_name)
tps_data.append(col_name)
avg_rt_data.append(col_name)
tps_std_data.append(col_name)
rt_std_data.append(col_name)
workload_types = set(x.split('_')[1] for x in col_name[1:])
workload_cols_rows.update({wl_type: {'cols': 0, 'rows': 0} for wl_type in workload_types})
col_name_parsed = True
tps_data.append(tps)
rt_data.append(rt)
avg_rt_data.append(avg_rt)
tps_std_data.append(tps_std)
rt_std_data.append(rt_std)
# print('tps_data: {}'.format(tps_data))
# print('rt_data: {}'.format(rt_data))
# print('avg_rt_data: {}'.format(avg_rt_data))
wb = Workbook(write_only=True)
for wl_type in workload_types:
wb.create_sheet(title=get_sheetname_by_workload(wl_type))
merged_rows = []
for tps, rt, avg_rt, tps_std, rt_std in zip(tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data):
merged_rows.append(tps + rt + avg_rt + tps_std + rt_std)
# print(merged_rows)
# The tps chart:
# print('merged_rows: {}\n'.format(merged_rows))
for row in merged_rows:
for wl_type in workload_types:
wl_row = [row[i] for i in range(len(row)) if
wl_type in merged_rows[0][i].split('_') or i == 0 or merged_rows[0][i] == 'Thread']
# print('wl_row: {}'.format(wl_row))
wb.get_sheet_by_name(get_sheetname_by_workload(wl_type)).append(wl_row)
workload_cols_rows[wl_type]['cols'] = len(wl_row)
workload_cols_rows[wl_type]['rows'] += 1
for ws in wb:
global_max_row = workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows']
# Chart of TPS
chart_tps = BarChart(gapWidth=500)
chart_tps.type = "col"
chart_tps.style = 10
chart_tps.title = "TPS chart of {}".format(ws.title)
chart_tps.y_axis.title = 'tps'
chart_tps.y_axis.scaling.min = 0
chart_tps.x_axis.title = 'tps'
data_tps = Reference(ws, min_col=2, min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5)
cats_tps = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps.add_data(data_tps, titles_from_data=True)
chart_tps.set_categories(cats_tps)
chart_tps.shape = 4
ws.add_chart(chart_tps, "A{}".format(global_max_row + 5))
# Chart of Response Time
chart_rt = BarChart(gapWidth=500)
chart_rt.type = "col"
chart_rt.style = 10
chart_rt.title = "Response Time(95%) chart of {}".format(ws.title)
chart_rt.y_axis.title = 'rt'
chart_rt.y_axis.scaling.min = 0
chart_rt.x_axis.title = 'response time'
data_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5)
cats_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt.add_data(data_rt, titles_from_data=True)
chart_rt.set_categories(cats_rt)
chart_rt.shape = 4
ws.add_chart(chart_rt, "I{}".format(global_max_row + 5))
# Chart of avg response time
chart_avg_rt = BarChart(gapWidth=500)
chart_avg_rt.type = "col"
chart_avg_rt.style = 10
chart_avg_rt.title = "Average Response Time chart of {}".format(ws.title)
chart_avg_rt.y_axis.title = 'avg rt'
chart_avg_rt.y_axis.scaling.min = 0
chart_avg_rt.x_axis.title = 'avg resp time'
data_avg_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5)
cats_avg_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_avg_rt.add_data(data_avg_rt, titles_from_data=True)
chart_avg_rt.set_categories(cats_avg_rt)
chart_avg_rt.shape = 4
ws.add_chart(chart_avg_rt, "Q{}".format(global_max_row + 5))
# Chart of tps standard deviation
chart_tps_std = BarChart(gapWidth=500)
chart_tps_std.type = "col"
chart_tps_std.style = 10
chart_tps_std.title = "tps standard deviation chart of {}".format(ws.title)
chart_tps_std.y_axis.title = 'std'
chart_tps_std.y_axis.scaling.min = 0
chart_tps_std.x_axis.title = 'tps std'
data_tps_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5)
cats_tps_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps_std.add_data(data_tps_std, titles_from_data=True)
chart_tps_std.set_categories(cats_tps_std)
chart_tps_std.shape = 4
ws.add_chart(chart_tps_std, "A{}".format(global_max_row + 20))
# Chart of response time standard deviation
chart_rt_std = BarChart(gapWidth=500)
chart_rt_std.type = "col"
chart_rt_std.style = 10
chart_rt_std.title = "response time standard deviation chart of {}".format(ws.title)
chart_rt_std.y_axis.title = 'std'
chart_rt_std.y_axis.scaling.min = 0
chart_rt_std.x_axis.title = 'rt std'
data_rt_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 5 / 5)
cats_rt_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt_std.add_data(data_rt_std, titles_from_data=True)
chart_rt_std.set_categories(cats_rt_std)
chart_rt_std.shape = 4
ws.add_chart(chart_rt_std, "I{}".format(global_max_row + 20))
wb.save(excel_filename)
