AliCloud product cost patrol, the general process is to log in to the account, and then fill out the item-by-item check. Although simple, but if the account number has more forms, the data between the accounts are related, fill up more laborious. A few forms, perhaps from the download of data to check and write, rolling half an hour.
Therefore it becomes important to automatically fill in the relevant values while keeping the original excel file format unchanged.
python manipulates excel with many modules.xlrd,pandas,xlwings,openpyxl. It's often confusing to figure out what the difference is between so many similar modules with so many functions, and here I found that xlwings can come in handy, as I have aPreservation of excel formatneeds of the file format:
Table 1-1
Note: The main changes are to lines 10 and 11, but nothing else.
Data sources
Log in to AliCloud via a crawler, download the data and write it to a csv. bring the date with you, e.g. data_07
Table 1-2
crawler script
# -*- coding: utf-8 -*-
from selenium import webdriver
from import Options
import time,os,glob,csv
from datetime import datetime
options = Options()
options.add_argument('--disable-infobars')
options.add_argument('--incognito')
# options.add_argument('--headless')
bro = (executable_path='C:\drf2\drf2\', chrome_options=options)
bro.maximize_window()
('/')
bro.implicitly_wait(10)
#Click the login button on the home page
bro.find_element_by_xpath('//*[@]/div/div/div[1]/div[2]/div[2]/a[4]').click()
(1)
#Click on RAM users
bro.find_element_by_xpath('//*[@]/div/div[2]/div/div[2]/div[1]/div[2]/div[2]/div/div[2]/div[2]/span/div').click()
u = bro.find_element_by_xpath('//*[@]/div[1]/div[2]/div/div/div[1]/div[2]/div/div/div[2]/div/div/div/form/div[1]/div[2]/div[1]/span/input')
#Username
u.send_keys('')
(5)
#Click Next
bro.find_element_by_xpath('//*[@]/div[1]/div[2]/div/div/div[1]/div[2]/div/div/div[2]/div/div/div/form/div[5]/button/span').click()
p = bro.find_element_by_xpath('//*[@]/div[1]/div[2]/div/div/div[1]/div[2]/div/div/div[2]/div/div/div/form/div[2]/div[2]/span/input')
#password
p.send_keys('')
(5)
# Click the login button
bro.find_element_by_xpath('//*[@]/div[1]/div[2]/div/div/div[1]/div[2]/div/div/div[2]/div/div/div/form/div[5]/button/span').click()
(3)
# Click on the console
bro.find_element_by_xpath(
'//*[@]/div/div/div[1]/div[2]/div[2]/a[3]').click()
(6)
#Switch windows
bro.switch_to.window(bro.window_handles[-1])
# Click costs
bro.find_element_by_xpath(
'/html/body/div[1]/div/div/nav/div[1]/a').click()
(3)
bro.switch_to.window(bro.window_handles[-1])
available_credit = bro.find_element_by_xpath('//*[@]/div/div/div/div[2]/div[1]/div[1]/div[2]/div/div[1]/span[1]/span').text
(3)
#Click on billing details
bro.find_element_by_xpath(
'//*[@]/div[1]/div/div[6]/div[3]/a').click()
(1.5)
#Click on the product volume and price summary
bro.find_element_by_xpath(
'//*[@]/div/div/div[1]/div[1]/div/div/div/ul/li[4]/div/span').click()
(1.5)
trs = bro.find_elements_by_xpath('//tbody/tr[position()> 1]')
for f in ('C:/Users/Administrator/Desktop/Cost Patrol/'):
if ('fee'):
('C:/Users/Administrator/Desktop/Cost Patrol/%s' % f)
with open('C:/Users/Administrator/Desktop/Cost Patrol/fee_%' % ().__format__('%m-%d'), 'a+', newline='', encoding='gb18030') as f:
f_csv = (f)
f_csv.writerow(['Available credit',available_credit.split(' ')[1]])
for tr in trs:
tr = ('\n')
f_csv.writerow([tr[0],tr[1].split(' ')[1]])
()
overhand (serve etc)
pandas reads data from table 1-2
In order to facilitate identification, the variable name is directly in Chinese
import pandas as pd
df = pd.read_csv('data_%' % ().__format__('%m-%d'), encoding='gbk', names=['Content', 'Amount'])
Content Security = eval([5, 1])
System SMS = 0
cloud serverECSflux = eval([4, 1])
object storage = eval([8, 1])
File Storage = eval([6, 1])
video on demand = eval([11, 1])
big data = eval([2, 1]) + eval([7, 1])
CDN = eval([1, 1])
logging service = eval([10, 1])
block storage = eval([3, 1])
add up the total = round(Content Security + System SMS + cloud serverECSflux + object storage + File Storage + video on demand + big data + CDN + logging service + block storage, 2)
balance (of an account, bill etc) = eval([0, 1].replace(',', ''))
xlwings get table 1-1sheet
import xlwings as xw from datetime import datetime import os app = (visible=False,add_book=False) app.display_alerts = False app.screen_updating = False wb = (filename) ws = [0]
xlwings modify the data in Table 1-1
# Modify line 10 so that the expand parameter can be written in one line in a convenient order.
('B10').options(expand='table').value = [Content Security, System SMS, cloud serverECSflux, object storage, File Storage, video on demand, big data, CDN, logging service, block storage, add up the total,
balance (of an account, bill etc)]
# Revise line 11
('e41').value = '%s of dollars have been used this month (%s month) and the actual account balance is %s of dollars.' % (().month, add up the total, balance (of an account, bill etc))
path = 'D:/Desktop/Patrol/%s' % ().__format__('%m-%d')
if not (path):
(path)
((path,'Education costs_%' % ().__format__('%m-%d')))
()
()
summarize
By using xlwings to automatically modify forms, my 6 forms have gone from a half hour of operation to being done in a few mouse duang~duang~duang~clicks. After reducing hundreds of copy and paste clicks, the work is much easier.
to this article on the Python operation xlwings example details of the article is introduced to this, more related Python xlwings content please search for my previous posts or continue to browse the following related articles I hope you will support me in the future more!