Multiple excel operations with Python

Question

Multiple excel operations with Python

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have an excel file where I keep information, more than 10,000 data. I need to be able to realize 1 massive account that is a division between two cells. The problem I have is that I do it but not for all the data. To this I mean that I have all the script made for 1 single cell and I need to do it for all those that have data:

The account I have to do is (D/E)*100 .

import openpyxl
from openpyxl import Workbook

doc = openpyxl.load_workbook(j)
a = doc.get_sheet_names()
a = a[9]

hoja = doc.get_sheet_by_name(a)

cell = hoja.cell(row=2, column=6)
cell.value ='=(D2/E2)*100'

As the example shows, I only made the formula for the first cell and now what I need is for all the cells that have data

Xcel data:

Cliente,Importe_a_pagar_a_medio,Impresiones_exchange,Subastas,Fill_rate,ECPM_medio
AAA,11,256,245825,0.607272727,0.443636364
BBB,9.37,258,5252,0.403636364,0.537272727
CCC,0.05,5282,5252,0.109090909,0.120909091
DDD,2.74,52,31660,12.66272727,0.640909091
EEE,18.94,241,536,3.167272727,0.831818182
FFF,0,0,0,0,0
GGG,0.23,4242,331680279,1.290909091,0.429090909
HHH,1.61,25,25363,0.636,0.742
iii,18.64,20,2525,1.328181818,0.478181818
JJJ,14.1,636,4569,5.964,0.148
KKK,0.12,428,253,2.794,0.32
LLL,2,125,469,0.280909091,0.36
MMM,0.34,310,22,0.826363636,1.049090909

python excel

asked by Martin Bouhier 15.01.2018 в 13:27

source

3 answers

how to see the alerts of my page in an android webview help with modal window in css so that it appears in front of the whole window and not behind?

score 1 · Answer 1

You can also try the following:

for i,cell in enumerate(hoja["D"],1):
    if i > 1:
       hoja["$G${0}".format(i)] = '=IF($E${0}=0,0,$D${0}/$E${0})'.format(i)

We go through all the cells in column "D" (except the first one in case they are titles)
In cell G<n> we configure the formula. Note that I added a IF to your formula to avoid the error by division by 0.

score 0 · Answer 2

You can use a loop for :

import openpyxl
from openpyxl import Workbook

doc = openpyxl.load_workbook(j)
a = doc.get_sheet_names()
a = a[9]

hoja = doc.get_sheet_by_name(a)

row_count = hoja.max_row

for i in range(2, row_count)
    cell = hoja.cell(row=i, column=6)
    D = hoja.cell(row=i, column=4)
    E = hoja.cell(row=i, column=5)
    if cell.value != None and E.value <> 0:
        cell.value = D.value/E.value*100

score 0 · Answer 3

You could try using Pandas.

Define your function before:

def funcion(fila):
    return (fila[0]/fila[1])*100

Imagine that "row" is each row of your excel. The value [0] will be that of field 1 and the value [1] of the other field.

import pandas as pd
df = pd.read_excel('nombre_del_archivo.xlsx')
df[nuevo_campo] = df[[campo1,otrocampo]].apply(lambda fila: funcion(fila), axis = 1)