コード例 #1
0
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 بقي :)
コード例 #2
0
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)
コード例 #3
0
ファイル: xl.py プロジェクト: nellyXinwei/PWM-Python-20190218
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")
コード例 #4
0
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
コード例 #5
0
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)
コード例 #6
0
ファイル: add_chart.py プロジェクト: JBloodless/votebot
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
コード例 #7
0
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')
コード例 #8
0
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)
コード例 #9
0
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)
コード例 #10
0
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')
コード例 #11
0
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")
コード例 #12
0
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')
コード例 #13
0
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)
コード例 #14
0
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()
コード例 #15
0
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)
コード例 #16
0
ファイル: ToExcel.py プロジェクト: HaiAlison/analysis_excel
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)
コード例 #17
0
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)

    
コード例 #18
0
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")
コード例 #19
0
ファイル: app.py プロジェクト: Pawan0802/process_spreadsheets
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)
コード例 #20
0
ファイル: apps.py プロジェクト: devkizit/EXCEL-AUTOMATION
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)
コード例 #21
0
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)
コード例 #22
0
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)
コード例 #23
0
 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))
コード例 #24
0
ファイル: app.py プロジェクト: Ly0nZ/ws_automation
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)
コード例 #25
0
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')
コード例 #26
0
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)
コード例 #27
0
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)
コード例 #28
0
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)
コード例 #29
0
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)
コード例 #30
0
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')