def test_autofit_col(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', 'A:D').autofit()
Range('Sheet1', 'A:D').autofit('r')
Range('Sheet1', 'A:D').autofit('c')
Range('Sheet1', 'A:D').autofit('rows')
Range('Sheet1', 'A:D').autofit('columns')
def set_book(ticker, line):
Range('A' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";bid_num_1\'"
Range('B' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";bid\'"
Range('C' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";ask\'"
Range('D' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";ask_num_1\'"
Range('E' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";last\'"
Range('F' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";volume\'"
#Add ref close for simplicity
Range('G' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";close\'"
return 0
def lluvia_max():
muestra = Range('LLUVIA', 'C5').table.value
#numero de datos
DataNr = len(muestra)
#cambiamos tipo de dato a 'array'
muestra = np.asarray(muestra)
#todos valores de los parametrod de Gumbel hasta 58 datos
Yn = Range('+', 'C4:C29').value
Sigma = Range('+', 'D4:D29').value
Indeks = Range('+', 'B4:B29').value.index(DataNr)
#Valores adoptados por los parametros de la funcion Gumbel:
Yn = Yn[Indeks]
Sigma = Sigma[Indeks]
### empty lists creation
s = []
m = []
alfa = []
mu = []
P = []
Prob = []
#los parametros de la funcion de Gumbel para cada estacion
#ddof: "Delta Degrees of Freedom" by default 0
for i in range(0, 4):
columna = muestra[:, i]
s.append(np.std(columna, ddof=1))
m.append(np.mean(columna))
alfa.append(s[i] / Sigma)
mu.append(m[i] - Yn * alfa[i])
P.append(mu[i] - alfa[i] * np.log(np.log(T / (T - 1))))
Prob.append(1 - 1 / T)
### PESOS
dist = np.asarray([3968, 14046, 13251, 12596]) #distancias...
pesos = 1 / dist**2
sq = [1 / i**2 for i in dist]
pesos = pesos / sum(sq)
### Pmax24
return sum(P * pesos)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx'),
app_visible=False,
app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def test_hyperlink(self):
address = 'www.xlwings.org'
# Naked address
Range('A1').add_hyperlink(address)
assert_equal(Range('A1').value, address)
if sys.platform.startswith('darwin'):
assert_equal(Range('A1').hyperlink, 'http://' + address)
else:
assert_equal(Range('A1').hyperlink, 'http://' + address + '/')
# Address + FriendlyName
Range('A2').add_hyperlink(address, 'test_link')
assert_equal(Range('A2').value, 'test_link')
if sys.platform.startswith('darwin'):
assert_equal(Range('A2').hyperlink, 'http://' + address)
else:
assert_equal(Range('A2').hyperlink, 'http://' + address + '/')
def test_cells():
cells = Range('C6:C441').value
count = 0
master_text = []
while count < len(cells):
if isinstance(cells[count], basestring):
current_cell = cells[count].split(" ")
current_text_count = 0
while current_text_count < len(current_cell):
master_text.append(current_cell[current_text_count])
current_text_count += 1
count += 1
else:
count += 1
return cell_word_count(master_text)
def combobox():
"""
This populates the ComboBox with the values from the database
"""
# Database Query
cursor.execute("SELECT PlaylistId, Name FROM Playlist")
# Write IDs and Names to hidden sheet
Range('Source', 'A1').table.clear_contents()
Range('Source', 'A1').value = cursor.fetchall()
# Format and fill the ComboBox to show Names (Text) and give back IDs (Values)
# TODO: implement natively in xlwings
combo = "ComboBox1"
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \
'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address))
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0
# Close cursor and connection
cursor.close()
con.close()
def playlist():
"""
Get the playlist content based on the ID from the Dropdown
"""
# Get PlaylistId from ComboBox
playlist_id = wb.xl_workbook.ActiveSheet.OLEObjects(
"ComboBox1").Object.Value
# Database query
cursor.execute(
"""
SELECT
t.Name AS Track, alb.Title AS Album, art.Name AS Artist, t.Composer
FROM PlaylistTrack pt
INNER JOIN Track t ON pt.TrackId = t.TrackId
INNER JOIN Album alb ON t.AlbumId = alb.AlbumId
INNER JOIN Artist art ON alb.ArtistId = art.ArtistId
WHERE PlaylistId = ?
""", (playlist_id, ))
# Get the result and column names
col_names = [col[0] for col in cursor.description]
rows = cursor.fetchall()
# Clear the sheet and write the column names and result to Excel
Range('A9').table.clear_contents()
Range('A9').value = col_names
if len(rows):
Range('A10').value = rows
else:
Range('A10').value = 'Empty Playlist!'
# Close cursor and connection
cursor.close()
con.close()
def combobox():
"""
This populates the ComboBox with the values from the database
"""
# Make a connection to the calling Excel file
wb = Workbook.caller()
# Place the database next to the Excel file
db_file = os.path.join(os.path.dirname(wb.fullname), 'chinook.sqlite')
# Database connection and creation of cursor
con = sqlite3.connect(db_file,
detect_types=sqlite3.PARSE_DECLTYPES
| sqlite3.PARSE_COLNAMES)
cursor = con.cursor()
# Database Query
cursor.execute("SELECT PlaylistId, Name FROM Playlist")
# Write IDs and Names to hidden sheet
Range('Source', 'A1').table.clear_contents()
Range('Source', 'A1').value = cursor.fetchall()
# Format and fill the ComboBox to show Names (Text) and give back IDs (Values)
# TODO: implement natively in xlwings
combo = "ComboBox1"
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \
'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address))
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2
wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0
# Close cursor and connection
cursor.close()
con.close()
def test_integers(self):
"""Covers GH 227"""
Range('A99').value = 2147483647 # max SInt32
assert_equal(Range('A99').value, 2147483647)
Range('A100').value = 2147483648 # SInt32 < x < SInt64
assert_equal(Range('A100').value, 2147483648)
Range('A101').value = 10000000000000000000 # long
assert_equal(Range('A101').value, 10000000000000000000)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'unicode_path.xlsx')
if sys.platform.startswith('darwin'):
dst = os.path.join(
os.path.expanduser("~") +
'/Library/Containers/com.microsoft.Excel/Data/',
'ünicödé_päth.xlsx')
else:
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx')
shutil.copy(src, dst)
wb = Workbook(dst, app_visible=False, app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
os.remove(dst)
def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
app = Application(wkb=self.wb)
app.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
app.calculation = Calculation.xlCalculationAutomatic
app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour)
assert_equal(Range('B1').value, 8)
Range('A1').value = 2
assert_equal(Range('B1').value, 4)
def summarize_sales():
"""
Retrieve the account number and date ranges from the Excel sheet
Read in the data from the sqlite database, then manipulate and return it to excel
"""
# Make a connection to the calling Excel file
wb = Workbook.caller()
# Connect to sqlite db
db_file = os.path.join(os.path.dirname(wb.fullname), 'pbp_proj.db')
engine = create_engine(r"sqlite:///{}".format(db_file))
# Retrieve the account number from the excel sheet as an int
account = Range('B2').options(numbers=int).value
# Get our dates - in real life would need to do some error checking to ensure
# the correct format
start_date = Range('D2').value
end_date = Range('F2').value
# Clear existing data
Range('A5:F100').clear_contents()
# Create SQL query
sql = 'SELECT * from sales WHERE account="{}" AND date BETWEEN "{}" AND "{}"'.format(
account, start_date, end_date)
# Read query directly into a dataframe
sales_data = pd.read_sql(sql, engine)
# Analyze the data however we want
summary = sales_data.groupby(["sku"])["quantity", "ext-price"].sum()
total_sales = sales_data["ext-price"].sum()
# Output the results
if summary.empty:
Range('A5').value = "No Data for account {}".format(account)
else:
Range('A5').options(index=True).value = summary
Range('E5').value = "Total Sales"
Range('F5').value = total_sales
def get_spot_purchase_values(year):
ROOT_DIR = os.path.dirname(os.getcwd())
if year < 2005:
raise ValueError('Data only goes back to 2005')
elif year < 2015:
wb = Workbook(os.path.join(ROOT_DIR,
'results/MomPop{0}.xlsm'.format(year)),
app_visible=False)
else:
wb = Workbook(os.path.join(
ROOT_DIR, 'results/notgrapefruit{0}.xlsm'.format(year)),
app_visible=False)
raw_price_df = pd.DataFrame(np.array(
Range('grove', 'C5:N10', atleast_2d=True).value),
columns=Range('grove', 'C4:N4').value,
index=Range('grove', 'B5:B10').value)
exchange_rate_df = pd.DataFrame(np.array(
Range('grove', 'C14:N15', atleast_2d=True).value),
columns=Range('grove', 'C13:N13').value,
index=Range('grove', 'B14:B15').value)
quantity_mat_weekly = np.array(
Range('grove', 'C38:AX43', atleast_2d=True).value)
# The matrix retrieved has values for each week. We want an average weekly
# quantity for the given month.
quantity_mat_av = np.zeros((6, 12))
for i in xrange(0, 12):
this_month = quantity_mat_weekly[:, (4 * i):(4 * i + 4)]
quantity_mat_av[:, i] = np.mean(this_month, 1)
quantity_df = pd.DataFrame(quantity_mat_av,
index=Range('grove', 'B38:B43').value,
columns=Range('grove', 'C4:N4').value)
wb.close()
return (raw_price_df, exchange_rate_df, quantity_df)
def test():
credentials = authorization.get_credentials(SCOPES, CLIENT_SECRET_FILE,
APPLICATION_NAME)
service = build('gmail', 'v1', http=credentials.authorize(Http()))
results = service.users().labels().list(userId='me').execute()
labels = results.get('labels', [])
if not labels:
print 'No labels found.'
else:
print 'Labels:'
i = 1
for label in labels:
try:
wb = Workbook('Book1')
Range((i, 1)).value = label['name'].encode("shift-jis")
except:
print "error"
i += 1
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug(
"insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'", len(df), len(df.columns),
str(top_left_cell),
Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def refresh():
"""
Refreshes the tables in Excel given the input parameters.
"""
# Connect to the Workbook
wb = Workbook.caller()
# Read input
start_date = Range(sheet_dashboard, 'start_date').value
end_date = Range(sheet_dashboard, 'end_date').value
account_name = Range(sheet_dashboard, 'account').value
property_name = Range(sheet_dashboard, 'property').value
profile_name = Range(sheet_dashboard, 'view').value
max_results = Range(sheet_dashboard, 'max_rows').value
# Clear Content
Range(sheet_dashboard, 'behavior').clear_contents()
Range(sheet_dashboard, 'effective').clear_contents()
# Behavior table
behavior(start_date, end_date, account_name, property_name, profile_name, max_results)
def test_resize(self):
r = Range('A1').resize(4, 5)
assert_equal(r.shape, (4, 5))
r = Range('A1').resize(row_size=4)
assert_equal(r.shape, (4, 1))
r = Range('A1:B4').resize(column_size=5)
assert_equal(r.shape, (4, 5))
r = Range('A1:B4').resize(row_size=5)
assert_equal(r.shape, (5, 2))
r = Range('A1:B4').resize()
assert_equal(r.shape, (4, 2))
assert_raises(AssertionError, Range('A1:B4').resize, row_size=0)
assert_raises(AssertionError, Range('A1:B4').resize, column_size=0)
def parse_variable_values(workbook, start_cell, end_cell):
"""
Given the workbook reference and START-END index pair, this function parses the values in the variable row
and saves it as a list of the same name.
input
-----
workbook: Workbook xlwings object
start_cell: Start cell dictionary
end_cell: End cell dictionary
returns: lists of variables, formulas, comments
"""
workbook.set_current() # sets the workbook as the current working workbook
variables = dict()
formulas = dict()
comments = dict()
start = (start_cell['row'], start_cell['col'])
end = (end_cell['row'], start_cell['col'])
start_column = Range(get_sheet(), start, end).value
# [1:] excludes 'START' element
start_column = start_column[1:]
for relative_index, element in enumerate(start_column):
current_index = start_cell['row'] + relative_index + 1
if element: # if non-empty
if not isinstance(element, str):
raise ValueError(
"The column below START can contain only strings")
# print(element)
element = element.strip()
if '=' in element:
formulas[element] = current_index
elif '#' == element[0]:
comments[element] = current_index
else:
variables[element] = current_index
return variables, formulas, comments
def test_array(self):
_skip_if_no_numpy()
# 1d array
Range('Sheet6', 'A1').value = array_1d
cells = Range('Sheet6', 'A1:D1', asarray=True).value
assert_array_equal(cells, array_1d)
# 2d array
Range('Sheet6', 'A4').value = array_2d
cells = Range('Sheet6', 'A4', asarray=True).table.value
assert_array_equal(cells, array_2d)
# 1d array (atleast_2d)
Range('Sheet6', 'A10').value = array_1d
cells = Range('Sheet6', 'A10:D10', asarray=True, atleast_2d=True).value
assert_array_equal(cells, np.atleast_2d(array_1d))
# 2d array (atleast_2d)
Range('Sheet6', 'A12').value = array_2d
cells = Range('Sheet6', 'A12', asarray=True, atleast_2d=True).table.value
assert_array_equal(cells, array_2d)
def test_array(self):
_skip_if_no_numpy()
# 1d array
Range('Sheet6', 'A1').value = array_1d
cells = Range('Sheet6', 'A1:D1').options(np.array).value
assert_array_equal(cells, array_1d)
# 2d array
Range('Sheet6', 'A4').value = array_2d
cells = Range('Sheet6', 'A4').options(np.array, expand='table').value
assert_array_equal(cells, array_2d)
# 1d array (atleast_2d)
Range('Sheet6', 'A10').value = array_1d
cells = Range('Sheet6', 'A10:D10').options(np.array, ndim=2).value
assert_array_equal(cells, np.atleast_2d(array_1d))
# 2d array (atleast_2d)
Range('Sheet6', 'A12').value = array_2d
cells = Range('Sheet6', 'A12').options(np.array, ndim=2, expand='table').value
assert_array_equal(cells, array_2d)
def check_cell(self, address, value):
# Active Sheet
Range(address).value = value
cell = Range(address).value
assert_equal(cell, value)
# SheetName
Range('Sheet2', address).value = value
cell = Range('Sheet2', address).value
assert_equal(cell, value)
# SheetIndex
Range(3, address).value = value
cell = Range(3, address).value
assert_equal(cell, value)
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(
cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def test_list(self):
# 1d List Row
Range('Sheet4', 'A27').value = list_row_1d
cells = Range('Sheet4', 'A27:C27').value
assert_equal(list_row_1d, cells)
# 2d List Row
Range('Sheet4', 'A29').value = list_row_2d
cells = Range('Sheet4', 'A29:C29', atleast_2d=True).value
assert_equal(list_row_2d, cells)
# 1d List Col
Range('Sheet4', 'A31').value = list_col
cells = Range('Sheet4', 'A31:A33').value
assert_equal([i[0] for i in list_col], cells)
# 2d List Col
cells = Range('Sheet4', 'A31:A33', atleast_2d=True).value
assert_equal(list_col, cells)
def importMethod(self):
print("import button clicked!")
self.filepath = str(QFileDialog.getOpenFileName())
self.wb = Workbook(self.filepath)
self.data = Range('Sheet1','A2', asarray=True).table.value
# print self.data
for m in np.arange(0,len(self.data)):
self.dataTable.insertRow(self.dataTable.rowCount())
for n in np.arange(0,len(self.colnames)-1):
newitem = QTableWidgetItem(str(self.data[m,n]))
self.dataTable.setItem(m,n,newitem)
# Enable convert button
self.convertPushButton.setEnabled(True)
self.plotPushButton.setEnabled(False)
def summarize_sales():
"""
Retrieve the account number and date ranges from the Excel sheet
"""
# Make a connection to the calling Excel file
wb = Workbook.caller()
# Retrieve the account number and dates
account = Range('B2').value
start_date = Range('D2').value
end_date = Range('F2').value
# Output the data just to make sure it all works
Range('A5').value = account
Range('A6').value = start_date
Range('A7').value = end_date
def get_excel_formula_as_string(right_side_expression, time_period, variables):
"""
Using the right-hand side of a math expression (e.g. a(t)=a(t-1)*a_rate(t)), converted to sympy
expression, and substituting the time index variable (t) in it, the function finds the Excel formula
corresponding to the right-hand side expression.
input
-----
right_side_expression: sympy expression, e.g. a(t-1)*a_rate(t)
time_period: value of time index variable (t) for time_periodtitution
output:
formula_string: a string of excel formula, e.g. '=A20*B21'
"""
right_dict = simplify_expression(right_side_expression, time_period,
variables)
for right_key, right_coords in right_dict.items():
excel_index = str(
Range(get_sheet(), tuple(right_coords)).get_address(False, False))
right_side_expression = right_side_expression.subs(
right_key, excel_index)
formula_str = '=' + str(right_side_expression)
return formula_str
def test_hyperlink(self):
address = 'www.xlwings.org'
# Naked address
Range('A1').add_hyperlink(address)
assert_equal(Range('A1').value, address)
hyperlink = Range('A1').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
# Address + FriendlyName
Range('A2').add_hyperlink(address, 'test_link')
assert_equal(Range('A2').value, 'test_link')
hyperlink = Range('A2').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
def test_get_address(self):
res = Range((1,1),(3,3)).get_address()
assert_equal(res, '$A$1:$C$3')
res = Range((1,1),(3,3)).get_address(False)
assert_equal(res, '$A1:$C3')
res = Range((1,1),(3,3)).get_address(True, False)
assert_equal(res, 'A$1:C$3')
res = Range((1,1),(3,3)).get_address(False, False)
assert_equal(res, 'A1:C3')
res = Range((1,1),(3,3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet1!$A$1:$C$3')
res = Range('Sheet2', (1,1),(3,3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet2!$A$1:$C$3')
res = Range((1,1),(3,3)).get_address(external=True)
assert_equal(res, '[test_range_1.xlsx]Sheet1!$A$1:$C$3')
def to_excel(self):
"""
Create an Excel spreadsheet from the device's point histories.
"""
his = {}
for name in list(self.points_name):
try:
his[name] = self._findPoint(name,
force_read=False).history.replace(
['inactive', 'active'],
[0, 1]).resample('1ms')
except TypeError:
his[name] = self._findPoint(
name, force_read=False).history.resample('1ms')
his['Notes'] = self.notes
df = pd.DataFrame(his).fillna(method='ffill').fillna(method='bfill')
if _XLWINGS:
wb = Workbook()
Range('A1').value = df
else:
df.to_csv()
