def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet']
for row in range(1, 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, )
wb.save(filename)
# الحمد لله ياكش نعدي في ال entry بقي :)
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 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 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 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 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 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 correct_excel(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
cell = sheet['a1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = 0.9 * cell.value
corrected_cell = sheet.cell(row, 4)
corrected_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(filename)
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 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 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(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 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):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
#iterate over all rows having data in the xlsx doc
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
#Getting a BarChart data and adding it to 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 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):
print("Start process_workbook")
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
print(cell.value)
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, "g5")
wb.save("har1.xlsx")
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
# Add new discounted value 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
# add bar 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"]
#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 * 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']
# 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 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 draw_incident_inflow_graph(self):
chart = BarChart()
chart.type = "col"
chart.title = "IR Inflow - Last 7 Days - Top 5 Apps"
chart.style = 10
chart.x_axis.title = 'Applications'
chart.y_axis.title = 'Inflow_Value'
data = Reference(self.graph_workbook_sheet,
min_col=2,
min_row=1,
max_row=6,
max_col=8)
cats = Reference(self.graph_workbook_sheet,
min_col=1,
min_row=2,
max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 6
self.graph_workbook_sheet.add_chart(chart, "K2")
#self.graph_workbook.save('final_graph.xlsx')
#print ("######SUCCESSFUL......GRAPH IS READY######")
print("--- %s seconds for inflow---" % (time.time() - self.start_time))
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']
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 process_workbook(filename):
wb = xl.load_workbook(filename)
# sheets could be duynamic too
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
corrected_price = sheet.cell(row, 3).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)
# injecting column can be dynamic too
sheet.add_chart(chart, 'E2')
wb.save(filename)
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']
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 process_workbook(filename):
print(filename)
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
cell_value = float(cell.value.replace('$', ''))
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('response.xlsx')
