import xlwings as xw
import datetime
starttime = datetime.datetime.now()
# test_data = [i for i in range(702)]
# workbook = xw.Book()
# workarea = workbook.sheets('Sheet1').range('A1:ZZ1000')
# for j in range(1,1001):
# workarea.rows(j).value = 1
#
# workbook.save(r'e:/xlwingstest_5.xlsx')
b = xw.Book(r'e:/pandastest1.xlsx')
data = b.sheets('Sheet1').range('ZZ1000').value
print(data)
# xw.Book(r'e:/openpyxltest_3.xlsx')
endtime = datetime.datetime.now()
print(endtime - starttime)
return
#reportgen: generate a list of the values that need to be checked
def reportgen():
return
if __name__ == '__main__':
#>>>Stage 1
#input initial variables
sdpath = query_YN()
bampath = (
input('Panel Metrics file location (Press enter for default): ')
or r'C:\Users\sroy\Documents\BevAl Metrics\2017 BevAl Metrics.xlsx')
book = xw.Book(bampath) #open the excel workbook
week, beforeday = week_init()
#write the spotfire data column
bam, sd, pt = spotextract(week, sdpath, bampath)
fillLoop()
print('Stage 1 Complete')
#>>>Stage 2
#>>>Stage 3
#>>>Stage 4
#>>>Stage 5
#Program End
import xlwings as xw
def main():
wb = xw.Book.caller()
sheet = wb.sheets[0]
if sheet["A1"].value == "Hello xlwings!":
sheet["A1"].value = "Bye xlwings!"
else:
sheet["A1"].value = "Hello xlwings!"
@xw.func
def hello(name):
return f"Hello {name}!"
if __name__ == "__main__":
xw.Book("permission.xlsm").set_mock_caller()
main()
WORD = re.compile(r'\w+')
def get_cosine(text1, text2):
count = 0
for i in range(len(text1)):
text0 = text1[i:i + 1]
if text0 in text2:
count += 1
return (count / len(text2))
name = open('thong_so.txt', 'r').read().split('\n')
print(len(name))
wb = xw.Book(u'thu.xlsx').sheets[0]
dong = [2, 9, 14, 78, 156, 337, 605]
for row in dong:
column = wb.range(row, 13).end('right').column
for col in range(13, column + 1, 2):
ts = wb.range(row, col).value
nd = wb.range(row, col + 1).value
list_ts = []
for na in name:
if ts in na:
list_ts.append(na)
if len(list_ts) == 1:
kq = list_ts[0]
elif len(list_ts) > 1:
tyle = []
text1 = nd
excel_weekly_summary_path: str = os.path.join(
excel_weekly_summary_dir, report_filename) + '.xlsx' # 每周汇总数据存放的文件夹
sheets: list = ["举报投诉核查工作", "维权工作", "督察工作情况"]
app: App = None
weekly_summary_wb: Book = None
gw: GenerateWeeklyReport = GenerateWeeklyReport()
copyfile(excel_weekly_summary_template_path, excel_weekly_summary_path)
try:
app = xw.App(visible=False)
app.display_alerts = False
app.screen_updating = False
weekly_summary_wb = xw.Book(excel_weekly_summary_path)
gw.merge_workbook(weekly_summary_wb, excels_dir_path)
# 举报投诉工作表
last_column = gw.get_sheet_last_column(weekly_summary_wb, sheets[0])
last_row = gw.get_sheet_last_row(weekly_summary_wb, sheets[0])
df_complaint = gw.deal_data_complaint(excel_weekly_summary_path,
sheets[0], last_column,
begin_date, end_date)
complaint_render: dict = gw.generate_render_dict(
df_complaint, last_row, last_column, '市局')
gw.dataframe_append_excel(df_complaint, all_summary_path, sheets[0])
# 维权工作表
last_column = gw.get_sheet_last_column(weekly_summary_wb, sheets[1])
last_row = gw.get_sheet_last_row(weekly_summary_wb, sheets[1])
columns=['Weight', 'Start', 'Length',
'Quote']) #Expandable swaption table
#Not yet implemented below, only assume swap rate and Normal swaptions
swptn_type = sht.range('type_swptn').value
swap_type = sht.range('type_rate').value
#Call yield construct
ycrv_base = ycrv_construct(rate_df['Term'], rate_df['Rate'])
#Put swaption data into the required format
CalibrationData = namedtuple("CalibrationData",
"start, length, volatility")
data_swaptn = [
CalibrationData(swptn_df.Start[i], swptn_df.Length[i],
swptn_df.Quote[i]) for i in swptn_df.index
]
#Call calibration algorithm
alpha, sigma, report = calibrate_hw1f(data_swaptn, swptn_type, ycrv_base)
param_calibrated = [alpha, sigma]
#Print results to Excel
sht.range('result_hw1f').value = param_calibrated
sht.range('result_error').options(index=False, header=False).value = report
if __name__ == '__main__':
# Expects the Excel file next to this source file, adjust accordingly.
xw.Book('HW_Calibration_Black.xlsm').set_mock_caller()
Calibrate()
def bhavcopy_processor(file_name):
'''Function to read and process bhavcopy
Convert it into a pivot_table
calculate product-far/ product-near and return that dataframe '''
# read bhav copy as workbook
logging.info('Reading bhavcopy')
app = xw.App(visible=False)
wb = app.books.open(download_dir + file_name)
# read sheet
sht = wb.sheets[file_name[:-4]]
# convert sheet to pandas dataframe
all_stocks = sht.range('A1').options(pd.DataFrame, expand='table').value
# select only FUTSTK and FUTIDX
new_index = ['FUTSTK', 'FUTIDX']
try:
logging.info('Selecting FUTSTK and FUTIDX')
all_stocks = all_stocks.loc[new_index]
except:
logging.info('Not able to select FUTSTK and FUTIDX')
# Categorization based on master document
# read master doc
logging.info('Reading master files...')
mast_book = xw.Book(master_dir + 'MasterData.xlsx')
# read sheet
mast_sheet = mast_book.sheets('Sheet1')
# convert sheet to pandas dataframe
master = mast_sheet.range('A1').options(pd.DataFrame, expand='table').value
# master.rename(columns={'SymbolName': 'SYMBOL'}, inplace=True)
# filter master for symbol, bloomcode and sector
master.set_index(keys='SYMBOL', inplace=True)
master = master[master['IsActiveFNO'] ==
True] # filter on active fno stocks
master.drop(columns=['IsActiveFNO'], axis=1, inplace=True)
# join dataframes master and all_stocks based on SYMBOL
all_stocks = all_stocks.join(master, on='SYMBOL')
#ignore fut weekly
monthly_exp = all_stocks[all_stocks['Type'] == 'SSF']['EXPIRY_DT'].unique()
all_stocks = all_stocks[all_stocks['EXPIRY_DT'].isin(monthly_exp)]
# create Pivot table
all_stocks = all_stocks.pivot_table(
values=['OPEN_INT', 'SETTLE_PR'],
index=['SYMBOL', 'Sector', 'IsNifty', 'Type'],
columns='EXPIRY_DT',
aggfunc={
'SETTLE_PR': np.sum,
'OPEN_INT': np.sum
})
# check for m2 and m3 months null values; if stocks go out of FNO
all_stocks['OPEN_INT'].iloc[:, 1].fillna(0, inplace=True)
all_stocks['OPEN_INT'].iloc[:, 2].fillna(0, inplace=True)
all_stocks['SETTLE_PR'].iloc[:, 1].fillna(0, inplace=True)
all_stocks['SETTLE_PR'].iloc[:, 2].fillna(0, inplace=True)
# calculate product-near and product-far
data = pd.DataFrame([
map(
round, all_stocks['OPEN_INT'].iloc[:, 0] *
all_stocks['SETTLE_PR'].iloc[:, 0]),
map(
round, all_stocks['OPEN_INT'].iloc[:, 1] *
all_stocks['SETTLE_PR'].iloc[:, 1] +
all_stocks['OPEN_INT'].iloc[:, 2] *
all_stocks['SETTLE_PR'].iloc[:, 2])
]).T
data.columns = ['Product-Near', 'Product-Far']
data.index = all_stocks.index
# apply roll over to all stocks and append ssf_rollover
data['SSF_rollover'] = data.apply(
lambda row: ssf_calculator(row['Product-Near'], row['Product-Far']),
axis=1)
pd.set_option('display.precision', 10)
print 'Bhavcopy processed successfully...'
logging.info('Bhavcopy processed successfully...')
wb.close()
return data, master
# -*- coding: utf-8 -*-
"""
Created on Thu Mar 22 18:09:58 2018
@author: cyrus
"""
import numpy as np
import xlwings as xw
wb = xw.Book(
r"D:\tower_geo\project\Python_171106_Tower_v1.15_loads_iter6_set1_full_geo - Copy.xlsm"
)
fatigue_sheet = wb.sheets['Fatigue']
des_array = fatigue_sheet.range('AJ11:AJ64').value
difference_points = np.zeros(shape=(len(des_array), 7))
difference_points[:, 0] = range(1, 55)
######### calculation for n_allowable_weld
des_array = fatigue_sheet.range('AJ11:AJ64').value
sigma_ref_weld_factored_array = fatigue_sheet.range('AN11:AN64').value
del_m = 4.
n_allowable_weld_array_expected = fatigue_sheet.range('AO11:AO64').value
n_allowable_weld_array = np.zeros(len(sigma_ref_weld_factored_array))
diff_percent_array_n_allowable = np.zeros(len(sigma_ref_weld_factored_array))
diff_array_n_allowable = np.zeros(len(sigma_ref_weld_factored_array))
#len(sigma_ref_weld_factored_array)
for index in range(0, len(sigma_ref_weld_factored_array)):
n_allowable_weld_array[index] = 10**(np.log10(2e6) + del_m * (np.log10(
sigma_ref_weld_factored_array[index]) - np.log10(des_array[index])))
# -*- coding: utf-8 -*-
"""
Created on Fri Oct 19 11:45:18 2018
@author: Nathan
@author: pingl
"""
import xlwings as xw
import math
import pandas as pd
file = r'C:\Users\Nathan\Desktop\Senior Fall 2018\Computational Methods\Project 2\specsheet.xls'
wb_costing = xw.Book()
sht_cost = wb_costing.sheets['Sheet1']
wb = xw.Book(file)
plant_type = input(
"What plant type are you designing? (solid, solid/fluid, fluid) ")
# Dictionaries for cost report
dictHX = {
"Heat Exchanger": [],
"Area": [],
"Tube Length": [],
"A": [],
"B": [],
"C": [],
"Cost": [],
}
dictHEAT = {
"Fired Heater": [],
def open(self):
if not self.wb:
xw.App(visible=self.visible)
self.wb = xw.Book(self._file)
logger.info('Case "%s" open' % self.name)
# Plot stackups in one figure, multiple plots
elif self.sht.api.Shapes("buttons1").OLEFormat.Object.Value > 0:
f, axs = plt.subplots(len(self.stack_plot_data), 1, sharex=True)
for i in range(0, len(self.stack_plot_data), 1):
axs[i].axvline(x=self.stack_plot_nominal[i],linewidth=.2, color='k')
axs[i].hist(self.stack_plot_data[i], alpha=0.5, bins=self.bin_size,
label=self.stack_plot_names[i], facecolor='g')
axs[i].hist(self.stack_plot_mask[i], alpha=1, bins=self.bin_size, facecolor='r')
axs[i].legend()
plt.tight_layout()
# Plot stackups in multiple figures
else:
for i in range(0, len(self.stack_plot_data)):
plt.figure(i+len(self.dim_plot_data))
plt.title(self.stack_plot_names[i])
plt.axvline(x=self.stack_plot_nominal[i],linewidth=.2, color='k')
plt.gca().hist(self.stack_plot_data[i], alpha=0.5, bins=self.bin_size, facecolor='g')
plt.gca().hist(self.stack_plot_mask[i], alpha=1, bins=self.bin_size, facecolor='r')
plt.tight_layout()
if plotted == True:
self.error_rng.value = "Waiting for figures to close..."
plt.show()
self.error_rng.value = "Ready"
if __name__ == "__main__":
# Used when running from Python
xw.Book('Dimension_standalone.xlsm').set_mock_caller()
# Used for frozen executable
plot = Plot()
plot.draw()
@xw.sub
def clean_num_with2dec_round():
"""clean the cells in selected range to nums with 2 decimal point, round if more than 2"""
clean_num(pre_formatter="", point_digit=2, truncate=False)
@xw.sub
def clean_num_with2dec_truncate():
"""clean the cells in selected range to nums with 2 decimal point, truncate if more than 2"""
clean_num(pre_formatter="", point_digit=2, truncate=True)
@xw.sub
def clean_num_with4dec_round():
"""clean the cells in selected range to nums with 4 decimal point, round if more than 4"""
clean_num(pre_formatter="", point_digit=4, truncate=False)
@xw.sub
def clean_num_with0dec_truncate():
"""clean the cells in selected range to nums with no decimal point, truncate if more than 0"""
clean_num(pre_formatter="", point_digit=0, truncate=True)
if __name__ == '__main__':
if False:
filepath = cwdPath.joinpath('res\\dev\\change_sign')
testfile_sum = filepath.joinpath('test_change_sign.xlsx')
xw.Book(testfile_sum).set_mock_caller()
change_selected_sign_preformat_text()
# @Date : 2021/4/8 20:48
# @Description : python自动生成excel数据报表
import pandas as pd
import xlwings as xw
import matplotlib.pyplot as plt
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
df = pd.read_csv(r"fruit_and_veg_sales.csv")
print(df)
# 创建原始数据表并复制数据
wb = xw.Book()
sht = wb.sheets["Sheet1"]
sht.name = "fruit_and_veg_sales"
sht.range("A1").options(index=False).value = df
# 查看所有列名
print(df.columns)
# 总利润透视表
pv_total_profit = pd.pivot_table(df, index='类别', values='总利润(美元)', aggfunc='sum')
print(pv_total_profit)
# 销售数量透视表
pv_quantity_sold = pd.pivot_table(df, index='类别', values='销售数量', aggfunc='sum')
print(pv_quantity_sold)
reader = csv.reader(out, delimiter=";", dialect="excel")
for row in reader:
#print(row)
try:
if len(row) > 7 and row[-6] is not None:
outputs[row[1]] = "O." + "{0:0=4d}".format(int(
row[4])) + "." + row[5] + "." + row[6]
except:
print("except 2")
pass
#Apriamo la lista IO e prendiamo le colonne dei mnemonici e degli input/output
wb = xw.Book('8FOS_IO008_V5.2.xls')
hilscher_tabs = {1: 1, 2: 2} #e.g. {1:1,2:2}
for hilscher_card in hilscher_tabs.keys():
print("elaborate hilscher card " + str(hilscher_card))
sht = wb.sheets[hilscher_tabs[hilscher_card]]
rownum = sht.range(
'E1'
).current_region.last_cell.row + 1 #lenght of document(num of rows) on the bit column
R_Column = sht.range(
'R1:R' +
import xlwings as xw
import pandas as pd
import openpyxl as op
from xlwings.constants import Direction # Testing the function of last cell
### reading files using xlwings
order = xw.Book(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\COH - SampleOrders_20190502.xlsx')
receive = xw.Book(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\AMDOCS - 行網收款紀錄_20190430.xlsx')
price = xw.Book(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\COH - 行網資費方案價格主檔.xlsx')
cust= xw.Book(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\MDM - 行網客戶主檔.xlsx')
#reading files using pandas to make it become dataframe
orderpd = pd.read_excel (r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\COH - SampleOrders_20190502.xlsx')
receivepd = pd.read_excel(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\AMDOCS - 行網收款紀錄_20190430.xlsx')
pricepd = pd.read_excel(r'C:\Users\YIEN\Desktop\COH - 行網資費方案價格主檔.xlsx')
custpd = pd.read_excel(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\MDM - 行網客戶主檔.xlsx')
billpd = pd.read_excel(r'C:\出帳.xlsx')
regionpd = pd.read_excel(r'C:\Users\YIEN\Desktop\to Will\行網 - QS Connection\Retail_Emp_For_NPV.xlsx')
### selecting main sheets
order_sheet = order.sheets['order']
receive_sheet = receive.sheets['工作表1']
price_sheet = price.sheets['sheet 1']
cust_sheet = cust.sheets['工作表1']
### Merging test
result = receivepd.merge(custpd, on="ACCOUNT_ID", how='left')
#print(result)
result2 = result.merge(orderpd, on="MSISDN", how='left')
print(result2)
result3 = result2.merge(pricepd, on="PROMOTIONCODE", how='left')
import os
import xlwings as xw
import time
from difflib import SequenceMatcher
os.chdir("/Users/arvid/Desktop/Router Information")
wb = xw.Book("/Users/arvid/Desktop/DOT Circuits tracking.xls")
sheet = wb.sheets("Circuit & IP info")
SiteNameCombos = {}
sitesNotMatched = []
def findDescription(siteFile, combo):
lineCounter = 0
for line in siteFile:
#print(line)
lineCounter += 1
#print(lineCounter)
#print (line)
if lineCounter < 5 and " Description:" in line:
#print("its in a line")
interfaceDescription = line.replace("Netname-","Netname ").replace("Netname:","Netname ")\
.replace("Netname-","Netname ").replace("Netname:","Netname ").replace("Circuit-","Circuit ")\
.replace("Circuit:","Circuit ").replace("***Netname","Netname").replace("***netname","Netname")\
.replace("***Circuit","Circuit").replace("***circuit#","Circuit").replace("circuit-","circuit ")\
.replace("Circuit#","Circuit").replace("Netname#","Netname").replace("**Circuit","Circuit").split(" ")
if " " in interfaceDescription:
import xlwings as xw
import pyautogui, sys, time
wb = xw.Book('C:\\Users\\craig\\Documents\\CS3398-Vulcans-S2020\\example.xlsm')
#wb = xw.Book(sys.argv[1]))
sheet = wb.sheets['Sheet1']
print('Press Ctrl-C to quit.')
print(str(sys.argv[0]))
try:
while True:
x, y = pyautogui.position()
sheet.range('L3').value = x
sheet.range('M3').value = y
except KeyboardInterrupt:
print('\n')
wb.save('C:\\Users\\craig\\Documents\\CS3398-Vulcans-S2020\\example.xlsm')
#wb.save(sys.argv[1])
date = []
remain_date = df['Unnamed: 13'].tolist()
for item in Name:
date.append(item.split('-')[2])
url = "http://mdapi.tadbirrlc.com/api/Symbol/all"
response = requests.get(url)
data = response.text
parsed = json.loads(data)
x = parsed['List'].split('],')
wb = xw.Book('View.xlsx')
worksheet = wb.sheets('Sheet1')
worksheet.range('A1').value = shifter(symbol, 'نماد')
worksheet.range('B1').value = shifter(Name, 'نام')
worksheet.range('C1').value = shifter(sell_price, 'فروش')
worksheet.range('D1').value = shifter(buy_price, 'خرید')
worksheet.range('E1').value = shifter(
list_maker_price(std_name(Name), name_collect(x), price_collect(x)),
"قیمت سهم")
worksheet.range('F1').value = shifter(place_price, 'قیمت اعمال')
worksheet.range('G1').value = shifter(date, 'سر رسید')
worksheet.range('H1').value = shifter(remain_date, 'روز تا سر رسید')
worksheet.range('I1').value = shifter(
percentage(
final_sell_price, final_place_price,
list_maker_price(std_name(Name), name_collect(x),
def get_workbook(self):
xw.App.visible = False
wb = xw.Book(self.path)
return wb
from openpyxl import load_workbook
import os
import gamry_parser as parser
import xlwings as xw
path_data = "./files/191016/"
path_format = "./files/PCFC template (2019)_DHK_v1_.xlsx"
path_file = "./files/111.xlsx"
file_list = os.listdir(path_data)
print(file_list)
book = xw.Book(path_format)
app = xw.apps.active
ws_iv = book.sheets['j-V-P']
ws_eis = book.sheets['EIS']
for files in file_list:
#j-V-P(iv)
if files.find('650') is not -1 and files.find('LSV') is not -1 and files.find('OCP') is -1:
data = 'I4'
print(files)
file_name = path_data + files
ca = parser.ChronoAmperometry(to_timestamp=True)
ca.load(filename=file_name)
dta = ca.get_curve_data()
del dta['T']
ws_iv.range(data).options(index=False).value = dta
if files.find('600') is not -1 and files.find('LSV') is not -1 and files.find('OCP') is -1:
data = 'P4'
import xlwings as xw
xlsx = r"\\arcserver-svr\D\PPA_v2_SVR\Tests\TestWorksheet2.xlsx"
def trace():
import traceback, inspect
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
# script name + line number
line = tbinfo.split(", ")[1]
filename = inspect.getfile(inspect.currentframe())
# Get Python syntax error
synerror = traceback.format_exc().splitlines()[-1]
return line, filename, synerror
arcpy.AddMessage(sys.executable)
arcpy.AddMessage(getpass.getuser())
try:
# make excel workbook with project outputs
xw.App.visible = True # must be set to True or else it won't work
wb = xw.Book(xlsx)
wb.close()
msg = "Success! xlwings workbook object created and closed!"
except:
msg = trace()
arcpy.SetParameterAsText(0, msg)
import os, xlwings, time, xlrd, openpyxl, win32com.client
import pandas as pd
import numpy as np
from pandas import Series, DataFrame
from openpyxl import Workbook, load_workbook
excel = win32com.client.Dispatch("Excel.Application")
excel.ScreenUpdating = False
### set your directory here
homedir = 'ahanway'
### set the path to the BBY Inventory file here; saving first in case you forgot to
filename = 'C:\\Users\\'+ homedir + '\\Desktop\\BBY TV inventory - FSM email V2-3.xlsm'
wb = xlwings.Book(filename)
wb.save()
#using an excel macro to unhide all sheets in the bby inv file
#if the sheets remained hidden, an error is generated in the final market file
a = wb.api.Application.Run("unhideSheets")
#starting script and timer
start = time.time()
print("\n\n ----------------> Process started: " + time.asctime( time.localtime(time.time()) ) + " <---------------- \n")
#the original bby inv file is too large and slows the script processing time; create a new temp saved to desktop
#with only admin, bby inv email, list, and template tabs from original bby inv file to pull from in the script.
print(" - Step 1: Creating valuedWb temp file saved as: \Desktop\BBY_TV_Inv_Temp.xlsx...")
print(" - This temp file will be deleted from the desktop upon script completion. This may take a few minutes...")
valuedWb = xlwings.Book()
import xlwings as xw
# app = xw.App()
# app.books['new_file.xlsx']
wb = xw.Book('new_file.xlsx')
wb.close()
def update_excel(r, column):
book = xw.Book(path)
sht = book.sheets['main']
cell = "{}".format(column) + str(r + 2)
sht.range(cell).value = 'done'
book.save()
import xlwings as xl
import pandas as pd
import numpy as np
wBook = xl.Book('experiment.xlsx')
data = wBook.sheets('data')
#________________dictionary converter______________#
#reading
k = data.range('A2:B12').options(dict).value #converts data from two coloumns into python dictionary
print(k) #where values in 1st coloumn are keys & in 2nd are dict values
#writing
#data.range('A2:B12').options(dict).value = dict() #does the inverse of line 10
#________________Numpy array converter________________#
#reading
j = data.range('B2:B12').options(np.array, transpose=True).value #puts the values in given the range into a numpy array
print(j)
#writing
data.range('E2:E12').options(transpose=True).value = np.array([1,2,3,4,5,6,7,8,9,10,11]) #inserts the given np array into the range
#_______________Pandas Series Converter_______________#
#reading
panda = data.range('A1',).options(pd.Series, expand='table', header=True).value
print('\n', panda)
#writing
data.range('A17').options(header=True).value = panda
#_______________Pandas DataFrame converter_____________#
#reading
frame = data.range('A1:C12').options(pd.DataFrame, header=1).value
def df_from_excel(path):
book = xw.Book(path)
book.save()
# book.close()
return pd.read_excel(path, header=0)
import xlwings as xw
file_path = '/Users/chenhaolin/PycharmProjects/SRT/发改委/NDRC/FILES/test_xls.xls'
wb = xw.Book(file_path)
sheet = wb.sheets[0]
RANGE = sheet.range('A1').expand('table')
row_count = RANGE.rows.count
col_count = RANGE.columns.count
print(str(row_count) + ' ' + str(col_count))
print(RANGE.rows)
for row in range(row_count):
print(sheet.range('A' + str(row + 1)).value)
import requests
import bs4
import xlwings as xw
import time
# 打开Excel程序，默认设置：程序可见，只打开不新建工作薄
app = xw.App(visible=True, add_book=False)
# 新建工作簿 (如果不接下一条代码的话，Excel只会一闪而过，卖个萌就走了）
wb = app.books.add()
# 打开已有工作簿（支持绝对路径和相对路径）
# wb = app.books.open('example.xlsx')
wb = xw.Book('test1.xlsx')
r = requests.get("http://zrzy.jiangsu.gov.cn/nt/gtzx/gzdt/")
html_doc = r.content.decode('utf-8')
soup = bs4.BeautifulSoup(html_doc, 'lxml')
a = len(soup.select('a[target="_blank"]'))
zong = soup.select('a[target="_blank"]')
n = 1
for cishu in range(0, a - 1, 1):
fen = zong[cishu].attrs["href"]
dingwei = fen
r2 = requests.get("http://zrzy.jiangsu.gov.cn/nt/gtzx/gzdt/" + dingwei)
html_doc = r2.content.decode('utf-8')
soup = bs4.BeautifulSoup(html_doc, 'lxml')
c = soup.find_all("p")
xw.Book(file_path).sheets[0].range('A1:D5')
# 写入数据
sht.range('A1').value = 'Hello Excel'
sht.range('A1').value = [1, 2, 3, 4, 5, 6, 7, 8]
# 按照列写入数据
sht.range('A2').options(transpose=True).value = [2, 3, 4, 5, 6, 7, 8]
# 二维列表写入数据
sht.range('A9').expand('table').value = [['a', 'b', 'c'], ['d', 'e', 'f'],
['g', 'h', 'i'], ['j', 'k', 'l']]
print(sht.range('A1:D5').value)
wb.save()
#def read_data():
if __name__ == '__main__':
#open_excel()
fig = plt.figure() # 指定画布
# plt.plot([1, 2, 3, 4, 5])
plt.plot([36, 5, 3, 25, 78])
plt.plot([9, 10, 31, 45])
plt.plot([6, 14, 45, 31])
sht = xw.Book(r'G:/test/test.xlsx').sheets[0]
sht.pictures.add(fig, name='myplt', update=True)
# -*- coding: utf-8 -*-
"""
Created on Thu Nov 3 14:22:15 2016
@author: ywang
"""
# Data Structure Tutorial
import xlwings as xw
# Single Cells
import datetime as dt
sht = xw.Book().sheets[0]
sht.range('A1').value = 1
sht.range('A1').value
sht.range('A2').value = 'Hello'
sht.range('A2').value
sht.range('A3').value is None
sht.range('A4').value = dt.datetime(2000, 1, 1)
sht.range('A4').value
# 1d List
sht = xw.books[0].sheets[0]
sht.range('A1').value = [[1], [2], [3], [4], [5]]
sht.range('A1:A5').value
sht.range('A1').value = [1, 2, 3, 4, 5]
sht.range('A1:E1').value
# force a single cell to be returned as list
sht.range('A1').options(ndim=1).value
