我有一个脚本,它读取一列单元格,如果每个单元格上的值都有特定的值,则更新另一列
以下是脚本:
statusclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols = "V")
telclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols = "AF")
marcacoes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="AN")
nomedosclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="B")
print(marcacoes)
print(statusclientes)
x=0
while x < len(statusclientes):
if (statusclientes.iloc[x][0] == "Offline"):
p=marcacoes.iloc[x][0]
p=p+1
marcacoes.iat[x,0]= p
print(nomedosclientes.iloc[x,0])
tel_off.append(telclientes.iloc[x][0])
if (statusclientes.iloc[x][0] == "Online"):
marcacoes.iat[x,0]= 0
x=x+1
这个脚本的问题是,它无法读取带有公式的单元格,而不是读取它们的值,而是读取为“NaN”(当前数据帧“StatusClients”中出现了这种情况)
我已经研究过了,显然这不是熊猫通常的行为,那么,可能出了什么问题
编辑:
1-单元格内的公式无法读取:
=SEERRO(SE(F682="Não"; "Indefinido";SE(G682="Fronius";PROCV(B682;'DADOS SOLARWEB'!$B$2:$G$999;5;FALSO);SE(W682<>"N/A";SE(X682<>"--";SE(DATADIF(W682;$A$3;"d")=0;"Online";"Offline");"Indefinido");"Offline")));"Indefinido")
2-以下是数据帧的工作原理:
[685 rows x 1 columns]
Unnamed: 21
0 NaN
1 NaN
2 Status
3 Nan
4 NaN
.. ...
680 NaN
681 NaN
682 NaN
683 NaN
684 NaN
“status”是一个文本单元格,“status”下面的每个单元格都有上面提到的公式。除此之外,我想不出还有什么值得一提的。工作表有宏,但删除它似乎没有任何效果
这是单元格的外观:
重要编辑
还有一点很重要,就是每次代码运行时都会从驱动器下载此工作表,然后在代码完成时将其上载到驱动器。这可能很重要,因为每当我在驱动器上手动保存工作表时,此代码都可以正常工作,如果它只是由代码本身上传的,并且我尝试再次运行代码,那么就会发生这种情况,有人知道为什么吗
既然有人问过,下面是我用来下载并更新驱动器上文件的代码
def download(self):
SCOPES='https://www.googleapis.com/auth/drive'
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'H:\Meu Drive\DriveAPI\key.json', SCOPES)
service = build('drive', 'v3', credentials=credentials)
request=service.files().get_media(fileId=file_id)
fh = io.FileIO('test.xlsm', mode='wb')
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print ("Download %d%%." % int(status.progress() * 100))
def updatar(self):
"""Inicializa a instância do google drive
Essa função pode retornar a instância como um objeto para a classe, ou pode realizar a operação direto sempre que a instância é iniciada
"""
SCOPES='https://www.googleapis.com/auth/drive'
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'.\key.json', SCOPES)
# Builda o objeto service
service = build('drive', 'v3', credentials=credentials)
#Essa parte vai definir os parametros do request à api do drive
file_metadata = {'name': 'test.xlsm','parents':['insert here your parent folder']}
file_name='test.xlsm'
mime_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
media = MediaFileUpload('./{0}'.format(file_name),mimetype=mime_type)
#updata o arquivo no drive
file = service.files().update(media_body=media,
addParents='insert here your parent folder',
fileId=file_id).execute()
目前没有回答
相关问题 更多 >
编程相关推荐