def __init__(self, file_name='Plantilla_Declaraciones'):
try:
# If this is a standalone program the defaul dir is in Temp Folder
path = os.path.abspath(
os.path.join(os.path.dirname(__file__), file_name + '.xlsx'))
#print(path)
wb = Book(path)
except:
print("seleccione el archivo Plantilla.xlsx")
path = easygui.fileopenbox(None,
'Seleccione el archivo de Plantilla')
if path == '.':
quit()
wb = Book(path)
self.current_dir = os.path.split(path)[0]
#print(self.current_dir)
#quit()
working_sheet = Sheet('breakdown')
self.data_dict = working_sheet.range('A2:B100').options(dict).value
""":type : dict"""
print("Data values from Plantilla_Breakdown:")
for key, values in self.data_dict.items():
print(key, ": ", values)
print("#####################################")
wb.app.quit()
def build_addins():
# transform code for addin use
with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, \
open(os.path.join(this_dir, "xlwings_addin.bas"), "w") as vba_addin:
content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook")
content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"')
vba_addin.write(content)
# create addin workbook
wb = Book()
# remove unneeded sheets
for sh in list(wb.xl_workbook.Sheets)[1:]:
sh.Delete()
# rename vbproject
wb.xl_workbook.VBProject.Name = "xlwings"
# import modules
wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas"))
# save to xla and xlam
wb.xl_workbook.IsAddin = True
wb.xl_workbook.Application.DisplayAlerts = False
# wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn)
wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn)
wb.xl_workbook.Application.DisplayAlerts = True
# clean up
wb.close()
os.remove(os.path.join(this_dir, 'xlwings_addin.bas'))
def __init__(self, path_and_file_to_book: str = None, visible=True):
'''
:param path_and_file_to_book: Path and file name or just filename in base directory.
:param visible: If excel shall open in windows or run in the background.
'''
super(PulsExcel, self).__init__()
self.app = App(visible=visible)
self.book = Book(path_and_file_to_book)
self.names_sp = {
'Identification': 1,
'Length of panel': 3,
'Stiffener spacing': 4,
'Plate thickness': 5,
'Number of primary stiffeners': 6,
'Stiffener type (L,T,F)': 7,
'Stiffener boundary': 8,
'Stiff. Height': 9,
'Web thick.': 10,
'Flange width': 11,
'Flange thick.': 12,
'Tilt angle': 13,
'Number of sec. stiffeners': 14,
'Modulus of elasticity': 21,
"Poisson's ratio": 22,
'Yield stress plate': 23,
'Yield stress stiffener': 24,
'Axial stress': 25,
'Trans. stress 1': 26,
'Trans. stress 2': 27,
'Shear stress': 28,
'Pressure (fixed)': 29,
'In-plane support': 30
}
self.names_up = {
'Identification': 1,
'Length of plate': 3,
'Width of c': 4,
'Plate thickness': 5,
'Modulus of elasticity': 6,
"Poisson's ratio": 7,
'Yield stress plate': 8,
'Axial stress 1': 9,
'Axial stress 2': 10,
'Trans. stress 1': 11,
'Trans. stress 2': 12,
'Shear stress': 13,
'Pressure (fixed)': 14,
'In-plane support': 15,
'Rot left': 16,
'Rot right': 17,
'Rot upper': 18,
'Rot lower': 19
}
def open_ssrs_report_file():
last_modified = 0
last_modified_path = None
# scan through folder looking for most recent SSRS report file
for dir_entry in scandir(expanduser(r"~\Downloads")):
if dir_entry.name.startswith(WORKORDER_SHEET_NAME):
if dir_entry.stat().st_mtime > last_modified:
last_modified = dir_entry.stat().st_mtime
last_modified_path = dir_entry.path
# no SSRS report file found
if last_modified_path is None:
MessageBox(0, "Please download report from SSRS", "Report Not Found")
raise FileNotFoundError
return Book(last_modified_path)
def main():
print('checkmyxl project generator')
parser = ArgumentParser(add_help=False)
parser.add_argument('-s', '--sample', action='store_true')
args = parser.parse_args()
try:
run(['xlwings', 'quickstart', 'book'], check=True)
except FileNotFoundError:
print('Command not found: `xlwings`.',
'xlwings package is not installed.',
'Online installation guide:',
'https://docs.xlwings.org/en/stable/installation.html.',
sep='\n')
exit(1)
try:
run(['mv', join_path('book', 'book.xlsm'), 'book.xlsm'], check=True)
except CalledProcessError:
print('The project `book` has not been created.')
exit(1)
try:
run(['rm', '-r', 'book'], check=True)
except CalledProcessError:
print('Temporary directory `book` cannot be removed.')
exit(1)
if apps.count == 0:
App()
Book('book.xlsm').set_mock_caller()
book = Book.caller()
sheets = book.sheets
sheets.add('Sheet1.code', after='Sheet1')
sheets.add('checkmyxl.conf', after='Sheet1.code')
book.save()
print('Successfully generated `book.xlsm` file.')
if args.sample:
sheet1, sheet1_code, checkmyxl_conf = \
sheets['Sheet1'], sheets['Sheet1.code'], sheets['checkmyxl.conf']
sheet1['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample.csv'))
sheet1_code['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample_code.csv'))
checkmyxl_conf['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample_conf.csv'))
print('Sample added.')
import os
FileName = os.path.basename(__file__)[:-3]
from xlwings import Book
wb = Book(
FileName) # connect to an existing file in the current working directory
AT_sheet = wb.sheets['Actual Test Data']
TP_sheet = wb.sheets['Test Procedure Data']
FD_sheet = wb.sheets['DataSheet']
CF_sheet = wb.sheets['CalcFlow']
AT_sheet['F36'].value = 'Carregando bibliotecas...'
TP_sheet['J23'].value = 'Carregando bibliotecas...'
CF_sheet['A1'].value = 'Carregando bibliotecas...'
import xlwings as xw
from scipy.optimize import newton
import os
from time import sleep
try:
aux = os.environ['RPprefix']
except:
os.environ['RPprefix'] = 'C:\\Users\\Public\\REFPROP'
import ccp
from ccp import State, Q_
import numpy as np
global P_FD_eff
AT_sheet['H35'].value = None
from xlwings import Book, Range
from Autocall import *
from Underlying import *
from DataManager import *
from Curve import *
import os
wb = Book('Exam Data.xlsm') # Creates a reference to the calling Excel file
sht = wb.sheets['Excel Connect']
os.chdir(wb.fullname[:-15])
def convert_to_bool(txt):
if txt == "True":
return True
elif txt == "False":
return False
dict_names = {
"Eurostoxx 50 Price Index":
"STOXX",
"Eurostoxx 50 Equal Weight decrement 5% Price Index ( 5% dividend)":
"STOXX_DEC"
}
Nb_Sim = int(sht.range("C13").value)
DM = DataManager()
for lang in self.lang_select_list:
sht_raw = self.excel_raw.loc[:, ['类型', self.base_lang.get()]]
sht_trans = self.excel_trans.loc[:,
[self.base_lang.get(), lang]]
pool.apply_async(translate, (
sht_raw,
sht_trans,
self.roles,
self.return_dict,
))
pool.close()
pool.join()
# self.destroy()
self.quit()
except BaseLangError:
messagebox.showerror('Error', '翻译文档的第一列不是基本语言。')
if __name__ == "__main__":
freeze_support()
root = Root()
root.mainloop()
wb_raw = Book(root.raw_file.get())
sht_raw = wb_raw.sheets('Sheet1')
for lang, result in root.return_dict.items():
index = root.column_index_raw[lang]
sht_raw.range(3, index).options(transpose=True).value = result
# 翻译结束后弹窗告知
messagebox.showinfo('Info', "已经成功完成翻译，并写入原始文档中，请查看。未匹配单元格已标注为\'$ERROR$\'")
from xlwings import Book, Sheet, Range, Chart
import time
import matplotlib.pyplot as plt
wb = Book()
def getQuote(ticker, type):
Range('B1').value = '=RTD("tos.rtd", ,"%s", "%s")' % (type, ticker)
time.sleep(2.5)
return Range('B1').value
def quoteOption(type, ticker, cp, exp, strike):
Range('B1').value = '=RTD("tos.rtd", ,"%s", ".%s%s%s%s")' % (
type, ticker, exp, cp, strike)
time.sleep(
2.5
) #wait for excel to update the cell, usually a couple of seconds. return Range('b1')
return (Range('B1').value)
#getQuote("AMD", "LAST")
count = 0
# while (count < 10):
# quoteOption('LAST','AMD','C',170804, 14)
# quoteOption('LAST','AMD','C',170804, 15)
# quoteOption('LAST','AMD','C',170804, 16)
# quoteOption('LAST','AMD','C',170804, 17)
from checkmyxl import main
from xlwings import App, apps, Book
if __name__ == '__main__':
if apps.count == 0:
App()
Book('book.xlsm').set_mock_caller()
main()
from xlwings import Book, Range
from deposit import *
from fra import *
from swap import *
from swaption import *
from CMS import *
from convention import *
#from workalendar.usa import NewYork
import os
wb = Book('Input-Group7.xlsm') # Creates a reference to the calling Excel file
sht = wb.sheets['Excel Connect']
os.chdir(wb.fullname[:-18])
def convert_to_bool(txt):
if txt == "True":
return True
elif txt == "False":
return False
type_of_product = sht.range('B2').value
notional = sht.range('B3').value
pricing_date = sht.range('B5').value
start_date = sht.range('B6').value
end_date = sht.range('B7').value
def projekt():
sheet_name = "Zakupy"
wb = Book(r'Dane.xlsm')
table = wb.sheets[sheet_name].range('A1').current_region
[ids, dates, size, cost] = table.options(transpose=True).value
ids = [int(x) for x in ids[1:]
] # ids będą liczbami całkowitymi (bez tej pętli byłyby typu float)
dates_zakupy = [
datetime.strptime(data, '%d.%m.%Y').date() for data in dates[1:]
]
size = size[1:]
cost = cost[1:]
sheet_name = "Klient"
table = wb.sheets[sheet_name].range('A1').current_region
[names, birth, ids_klient] = table.options(transpose=True).value
names = names[1:]
birth = [datetime.strptime(data, '%d-%m-%Y') for data in birth[1:]]
ids_klient = [int(x) for x in ids_klient[1:]]
firstnames, surnames, ages = [], [], []
names[0] = ' nancy\xa0 davolio\xa0'
for i in range(0, len(birth)):
ages.append(date.today().year - birth[i].year -
((date.today().month, date.today().day) <
(birth[i].month, birth[i].day)))
for data in names:
# dzielimy napisy na spacjach, wyrzucamy puste, powiększamy 1. literę
parts = [part.capitalize() for part in data.split(" ") if part]
# zapamiętanie danych
firstnames.append(" ".join(parts[:-1]))
surnames.append(parts[-1])
# 1. zadanie: -------------------------
with open(get_path('zad1.htm'), 'wb') as f:
def pisz(tekst):
tekst += "\r\n"
f.write(tekst.encode("utf-8"))
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Posumowanie</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<div class="card-container">')
pisz('<div class="card">')
pisz('<div class="front">')
pisz('<h2>Touch me!</h2>')
pisz('</div>')
pisz('<div class="back">')
pisz('<h1>Liczba klientów: ' + str(len(set(ids))) + '</h1>')
pisz('<h1>Całkowity obrót: ' + str(round(sum(cost), 2)) + ' zł</h1>')
pisz('<h1>Ostatnia sprzedaż: ' + str(max(dates_zakupy)) + '</h1>')
pisz('</div>')
pisz('</div>')
pisz('</div>')
pisz('</body>')
pisz('</html>')
#--------------------------------------
# 2. zadanie: -------------------------
l = []
for i in set(ids):
l.extend(
[i, 0, 0, 0]
) # stworzenie listy na zadanie 2. przechowuje dane w sposób: [id_klienta , obecnosc_w_sklepie , ilosc_produktow , suma_pieniędy , id_klienta , obecnosc_w_sklepie , ilosc_produktow , suma_pieniędy itd.]
for row in range(len(ids)):
j = l.index(ids[row])
l[j + 1] += 1
l[j + 2] += size[row]
l[j + 3] += cost[row] # uzupełnia listę o dane z excela
#print(l)
#for k in range(int(len(l)/4)):
#print('ID: ',l[4*k],'Imię: ',firstnames[ids_klient.index(l[4*k])],'Nazwisko: ',surnames[ids_klient.index(l[4*k])],'Ile razy był w sklepie: ',l[4*k+1],'Ile produktów kupił: ',int(l[4*k+2]),'Ile pieniędzy zostawił: ',round(l[4*k+3],2),'zł')
#--------------------------------------
with open(get_path('Tabelka.htm'), 'wb') as f:
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Tabelka</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<table>')
# Nagłówki
pisz('<tr>')
pisz('<td>ID</td>')
pisz('<td>Imię</td>')
pisz('<td>Nazwisko</td>')
pisz('<td>Wiek</td>')
pisz('<td>Ile razy w sklepie</td>')
pisz('<td>Suma produktów</td>')
pisz('<td>Ilość pieniędzy</td>')
pisz('</tr>')
# Zawartość tabelki
for k in range(int(len(l) / 4)):
pisz('<tr>')
pisz('<th>' + str(l[4 * k]) + '</th>')
pisz('<th>' + firstnames[ids_klient.index(l[4 * k])] + '</th>')
pisz('<th>' + surnames[ids_klient.index(l[4 * k])] + '</th>')
pisz('<th>' + str(ages[ids_klient.index(l[4 * k])]) + '</th>')
pisz('<th>' + str(l[4 * k + 1]) + '</th>')
pisz('<th>' + str(int(l[4 * k + 2])) + '</th>')
pisz('<th>' + str(round(l[4 * k + 3], 2)) + 'zł</th>')
pisz('</tr>')
pisz('</table>')
pisz('</body>')
pisz('</html>')
# Zadanie dodatkowe: ---------------------------------------
d = {}
for key in set(ages):
d[key] = 0 # stworzenie słownika łączącego wiek i wydane pieniądze
for i in range(int(len(l) / 4)):
age = ages[ids_klient.index(
l[4 * i])] # wyszukanie wieku korzystając z id klienta z listy l
d[age] += l[4 * i + 3] # dodanie pieniędzy do słownika
for i in d:
d[i] = d[i] / ages.count(i)
x = list(d.keys()) # zbiór X: wiek klienta
y = list(d.values()
) # zbiór Y: suma wydanych pieniędzy dla danego wieku klienta
plt.plot(x, y, 'ro')
plt.xlabel('wiek')
plt.ylabel('Średnia kwota')
plt.savefig(get_path('wykres.jpg'))
print(get_path('wykres.jpg'))
with open(get_path('wykres.htm'), 'wb') as f:
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Wykres</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('<li><a href="Tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<img src="wykres.jpg" alt="Wykres" height="500" width="700">')
pisz('</body>')
pisz('</html>')
with open(get_path('autor.htm'), 'wb') as f:
def pisz(tekst):
tekst += "\r\n"
f.write(tekst.encode("utf-8"))
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Autorzy</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<h3>Damian Matusiak</h3>')
pisz('<h4>Numer indeksu: 243027</h4>')
pisz(
'<a href="mailto:[email protected]" target="_top">Napisz email</a>'
)
pisz('<h3>Jakub Jasiński</h4>')
pisz('<h4>Numer indeksu: 243019</h4>')
pisz(
'<a href="mailto:[email protected]" target="_top">Napisz email</a>'
)
pisz('</body>')
pisz('</html>')
return_value = col_number
if modify_index is True:
col_number = col_number - 1 - offset
for row in refdata:
for value in range(size):
row.insert(col_number, "")
return return_value
# print("New:", refdata)
if __name__ == '__main__':
path_file = fileopenbox()
wb = Book(path_file)
from read_xl import ExcelReadtoList
references = ExcelReadtoList(path_file, close=False)
print(references.get("Sheet1"))
ref_data = references.get("Sheet1")
print(ref_data)
insert_col_after("SubTotal", wb, 4, ref_data, 0, True)
print(ref_data)
# col_remove_name("A:A", wb)
