def manage_standard_2G(sheet,pos):
"""
Draw "2.4G standard" express from template
"""
s = Range(sheet,pos).value
if "\n" in s:
standard_rate, stream = s.split("\n")
standard_rate = standard_rate.strip()
standard, rate = standard_rate.split(" ")
stream = stream.split(" ")[0]
rate = rate.split("M")[0]
## print (standard,rate,stream)
##************************************************
## from sheet will get "11gac" but data is "11ac"
if standard == "11gac":
standard = "11ac"
##************************************************
return (standard,rate,stream)
else:
## print s
##************************************************
## for match data "11g" actually got "11ag
if s == "11g":
s = "11ag"
##************************************************
return s
def test_dataframe_1(self):
_skip_if_no_pandas()
df_expected = df_1
Range('Sheet5', 'A1').value = df_expected
df_result = Range('Sheet5', 'A1:C5').options(pd.DataFrame).value
df_result.index = pd.Int64Index(df_result.index)
assert_frame_equal(df_expected, df_result)
def populate(self, statement, start_row):
row_num = start_row
for transaction in statement.transactions:
range_string = 'A' + str(row_num) + ':D' + str(row_num)
row = Range(self.ledger, range_string)
self._format(row, type='column')
row.value = transaction
row_num += 1
return
def setCell(sheet, cell, value):
currentCell = Range(Sheet(sheet), cell)
if __debug__:
print('Setting Cell:' + cell + 'in sheet ' + Sheet(sheet).name + '->' + str(value))
if currentCell and value:
try:
currentCell.value = str(value)
except:
if __debug__:
print ('Error with value: ' + value)
def manage_modulation(sheet,pos):
"""
Draw "modulation" express from template
"""
##************************************************
## for match 2.4G 11b "DSSS-CCK". data will get "CCK" instead of "DSSS"
if "CCK" in Range(sheet,pos).value:
modulation = Range(sheet,pos).value.split("-")[1]
##************************************************
else:
modulation = Range(sheet,pos).value.split("-")[0]
return modulation.strip()
def _get_account_holder(self):
'''
Populates self.account_holder with the account holder as a string.
If no account_holder in statement the returns None.
:return:
'''
cell = 'B2'
account_holder_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value
if account_holder_string:
self.account_holder = account_holder_string[account_holder_string.index('Prepared for: ') + 14:]
else:
self.account_holder = None
def _get_account_number(self):
'''
Populates self.account_number with the statement account number as a string.
If no account number on statement returns None.
:return:
'''
cell = 'B3'
account_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value
if account_string:
self.account_number = account_string[account_string.index('Account Number: ') + 16:]
else:
self.account_number = None
def test_read_df_2header_1unnamedindex(self):
_skip_if_no_pandas()
Range('A1').value = [[None, 'a', 'a', 'b'],
[None, 'c', 'd', 'c'],
[1, 1, 2, 3],
[2, 4, 5, 6]]
df1 = pd.DataFrame([[1., 2., 3.], [4., 5., 6.]],
index=pd.Index([1, 2]),
columns=pd.MultiIndex.from_arrays([['a', 'a', 'b'], ['c', 'd', 'c']]))
df2 = Range('A1:D4').options(pd.DataFrame, header=2).value
df2.index = pd.Int64Index(df2.index)
assert_frame_equal(df1, df2)
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 _get_statement_dates(self):
'''
Populates self.start_date and self.end_date with the statement start and end dates respectively in datetime
date format.
If no statement dates available sets both fields to None.
:return:
'''
cell = 'C2'
dates_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value
if dates_string:
start_date_string = dates_string[:dates_string.index(' to')]
self.start_date = datetime.strptime(start_date_string, self.date_format).date()
end_date_string = dates_string[dates_string.index(' to') + 4:]
self.end_date = datetime.strptime(end_date_string, self.date_format).date()
else:
self.start_date, self.end_date = None, None
def _ledger_setup(self):
self.ledger = Sheet('Sheet1', wkb=self.workbook)
self.ledger.name = 'Ledger'
# Title
A1 = Range(self.ledger, 'A1')
A1.value = 'Ledger'
self._format(A1, type='title')
# Column headers
headers = Range(self.ledger, 'A3:D3')
headers.value = ['Date', 'Account', 'Description', 'Amount']
self._format(headers, type='header')
# First data row
data_range = Range(self.ledger, 'A4:D4')
self._format(data_range, type='column')
def test_color(self):
rgb = (30, 100, 200)
Range('A1').color = rgb
assert_equal(rgb, Range('A1').color)
Range('A2').color = RgbColor.rgbAqua
assert_equal((0, 255, 255), Range('A2').color)
Range('A2').color = None
assert_equal(Range('A2').color, None)
Range('A1:D4').color = rgb
assert_equal(rgb, Range('A1:D4').color)
def behavior(start_date, end_date, account_name, property_name, profile_name, max_results):
"""
Writes a DataFrame with the number of pageviews per half-hours x weekdays
to the Range "behavior"
"""
# Let pandas fetch the data from Google Analytics, returns a generator object
df_chunks = ga.read_ga(secrets=client_secrets,
account_name=account_name,
property_name=property_name,
profile_name=profile_name,
dimensions=['date', 'hour', 'minute'],
metrics=['pageviews'],
start_date=start_date,
end_date=end_date,
index_col=0,
parse_dates={'datetime': ['date', 'hour', 'minute']},
date_parser=lambda x: datetime.strptime(x, '%Y%m%d %H %M'),
max_results=max_results,
chunksize=10000)
# Concatenate the chunks into a DataFrame and get number of rows
df = pd.concat(df_chunks)
num_rows = df.shape[0]
# Resample into half-hour buckets
df = df.resample('30Min', how='sum')
# Create the behavior table (half-hour x weekday)
grouped = df.groupby([df.index.time, df.index.weekday])
behavior = grouped['pageviews'].aggregate(np.sum).unstack()
# Make sure the table covers all hours and weekdays
behavior = behavior.reindex(index=pd.date_range("00:00", "23:30", freq="30min").time,
columns=range(7))
behavior.columns = ['MO', 'TU', 'WE', 'TH', 'FR', 'SA', 'SU']
# Write to Excel.
# Time-only values are currently a bit of a pain on Windows, so we set index=False.
Range(sheet_dashboard, 'behavior', index=False).value = behavior
Range(sheet_dashboard, 'effective').value = num_rows
def manage_standard_5G(sheet,pos):
"""
Draw "5G standard" express from template
"""
s = Range(sheet,pos).value
if "\n" in s:
standard_rate, stream = s.split("\n")
standard_rate = standard_rate.replace(" ","")
standard, rate = standard_rate.split("-")
## print standard_rate
stream = stream.split(" ")[0]
rate = rate.split("T")[-1]
## print (standard,rate,stream)
return (standard,rate,stream)
else:
## print s
##************************************************
## for match data "11a" actually got "11ag
if s == "11a":
s = "11ag"
##************************************************
return s
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_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
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') and os.path.isdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'):
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_autofit_row(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', '1:1000000').autofit()
Range('Sheet1', '1:1000000').autofit('r')
Range('Sheet1', '1:1000000').autofit('c')
Range('Sheet1', '1:1000000').autofit('rows')
Range('Sheet1', '1:1000000').autofit('columns')
def test_hyperlink(self):
address = 'www.xlwings.org'
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 + '/')
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 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 get_column_header_color(header_cell: xw.Range) -> int:
header_str: str = str(header_cell.value).lower()
value_cell = header_cell.offset(1, 0)
if header_str.startswith('used_'):
return Color.USED_IDENTIFIER
elif header_is_index(header_str):
return Color.INDEX
elif value_cell.value is None:
return Color.INPUT
elif str(value_cell.formula)[0] != '=':
return Color.INPUT
else:
return Color.CALCULATION
def test_offset(self):
o = Range('A1:B3').offset(3, 4)
assert_equal(o.get_address(), '$E$4:$F$6')
o = Range('A1:B3').offset(row_offset=3)
assert_equal(o.get_address(), '$A$4:$B$6')
o = Range('A1:B3').offset(column_offset=4)
assert_equal(o.get_address(), '$E$1:$F$3')
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 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 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 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_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 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 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 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():
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 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').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 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 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 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 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 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
wb = Workbook.caller()
SheetA = u"預算平衡表-Phase Summary A"
SheetE = u"預算平衡表-Phase Summary E"
SheetP = u"預算平衡表-Phase Summary P"
SheetC = u"預算平衡表-Phase Summary C"
SheetArr = [SheetA, SheetE, SheetP, SheetC]
SheetAlphabetArr = ["A", "E", "P", "C"]
ColumnAlphabet = ["D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"]
AllRow = []
for SName in SheetArr:
AllRange = Range(SName, "D10").vertical.get_address()
LastRow = AllRange.split(":")[1].split("$")[2]
LastRow = int(LastRow)
AllRow.append(LastRow)
print AllRow
for SName in SheetArr:
print SheetArr.index(SName)
# Create Empty Dataframe for Balance Sheet
columns = [
"Req No",
"Req Description",
"A",
"W",
"Z",
"Current Budget",
"Exe Budget",
def execute(Balance_Sheet_Path, Tre0Uno_Sheet_Path, ManHour_Path, ACHour_Path, Pro_Name):
# # Balance Sheet
wb = Workbook(Balance_Sheet_Path)
wb = Workbook.caller()
SheetA = u'預算平衡表-Phase Summary A'
SheetE = u'預算平衡表-Phase Summary E'
SheetP = u'預算平衡表-Phase Summary P'
SheetC = u'預算平衡表-Phase Summary C'
SheetArr=[SheetA, SheetE, SheetP, SheetC]
SheetAlphabetArr = ['A', 'E', 'P','C']
ColumnAlphabet=['D','E','F','G','H','I', 'J', 'K', 'L', 'M',
'N', 'O', 'P']
AllRow = []
for SName in SheetArr:
AllRange = Range(SName, 'D10').vertical.get_address()
LastRow = AllRange.split(':')[1].split('$')[2]
LastRow = int(LastRow)
AllRow.append(LastRow)
print AllRow
for SName in SheetArr:
print SheetArr.index(SName)
#Create Empty Dataframe for Balance Sheet
columns = ['Req No', 'Req Description', 'A', 'W', 'Z',
'Current Budget', 'Exe Budget', 'C',
'PO Num', 'Excepted Budget', 'EAC Now', 'G', 'H']
ArangeLen=(sum(AllRow)+100)
index = np.arange(int(ArangeLen))
df_bs = pd.DataFrame(columns=columns, index = index)
def BSreadRange(row, SheetIX):
SName = SheetArr[SheetIX]
SAlphabet = SheetAlphabetArr[SheetIX]
Val = Range(SName, row).value
if (Val != 0):
for j in xrange(0,13):
if(row[0] == ColumnAlphabet[j]):
rowNum = '%s%d' % (SAlphabet, int(row[1:]))
df_bs.loc[rowNum, columns[j]]=Val
for SName in SheetArr:
SheetIX = SheetArr.index(SName)
SheetLastRow = AllRow[SheetIX]
for i in xrange(11,(SheetLastRow+1)):
CellNameArr = map(lambda x:('%s%d' % (x,i)), ColumnAlphabet)
[BSreadRange(x, SheetIX) for x in CellNameArr]
df_bs = df_bs[(pd.notnull(df_bs['Req No']))]
# # 讀取工時
d = datetime.date.today()- timedelta(days=20)
SheetMonth = '{:02d}'.format(d.month)
SheetYear = format(d.year)[2:]
MH_SheetName=('%s%s' % (SheetYear, SheetMonth))
print MH_SheetName
MH_Arr=[]
#Pro_Name = ['14A1701A', '14C1701A', '14P1701A', '14E1701A']
#ManHour_Path=r'C:\Python2.7.10\Scripts\notebook\xlwings\MH.xls'
wb = Workbook(ManHour_Path)
wb = Workbook.caller()
AllRange_MH = Range(MH_SheetName, 'A6').vertical.get_address()
LastRow_MH = AllRange_MH.split(':')[1].split('$')[2]
LastRow_MH = int(LastRow_MH)
print LastRow_MH
for i in xrange(6,LastRow_MH+1):
SearchLocate = ('A%d'% (i))
s = Range(MH_SheetName, SearchLocate).value
if s in Pro_Name:
TargetLocate = ('B%d'% (i))
MH_Arr.append([s[2], Range(MH_SheetName, TargetLocate).value])
print MH_Arr
Workbook(ManHour_Path).close()
# # 其他費用
#ACHour_Path=r'C:\Python2.7.10\Scripts\notebook\xlwings\fa.xlsx'
ACSheetName='acc303'
AC_Arr=[]
wb = Workbook(ACHour_Path)
wb = Workbook.caller()
AllRange_ACC = Range(ACSheetName, 'A2').vertical.get_address()
LastRow_ACC = AllRange_ACC.split(':')[1].split('$')[2]
LastRow_ACC = int(LastRow_ACC)
print LastRow_ACC
for i in xrange(1,LastRow_ACC+1):
SearchLocate = ('A%d'% (i))
s = Range(ACSheetName, SearchLocate).value
if s in Pro_Name:
CatLocate = ('D%d'% (i))
TargetLocate = ('H%d'% (i))
AC_Arr.append([s[2], Range(ACSheetName, CatLocate).value,
Range(ACSheetName, TargetLocate).value])
print len(AC_Arr)
columns_AC = ['Type', 'Amount']
ACrangeLen=(len(AC_Arr))
index_AC = np.arange(int(ACrangeLen))
df_ac = pd.DataFrame(columns=columns_AC, index = index_AC)
j=0
for i in AC_Arr:
df_ac.loc[j, 'Type']=('%s%d' % (i[0],i[1]))
df_ac.loc[j, 'Amount']=i[2]
j+=1
#print df_ac
AC_Result_df = df_ac.groupby(by=['Type'])['Amount'].sum()
for i in range(0,len(AC_Result_df)-1):
print AC_Result_df.index[i], AC_Result_df[i]
Workbook(ACHour_Path).close()
# # 301表
wb = Workbook(Tre0Uno_Sheet_Path)
wb = Workbook.caller()
ColumnAlphabet301 = ['B', 'C', 'D', 'E', 'F', 'G', 'H']
AllRange301 = Range('D10').vertical.get_address()
LastRow301 = AllRange301.split(':')[1].split('$')[2]
LastRow301 = int(LastRow301)
LastRow301 = LastRow *3
Tre0Uno_columns = ['PO Num', 'Req No', 'Req Description', 'A',
'Current Budget', 'Exe Budget', 'C']
Tre0UnorangeLen=(LastRow301+100)
Tre0Uno_index = np.arange(int(Tre0UnorangeLen))
df_T0U = pd.DataFrame(columns=Tre0Uno_columns, index = Tre0Uno_index)
# #### 讀取原始301資料
#Read 301 data into the Dataframe function
def T0UreadRange(row):
Val = Range(row).value
if (Val != 0):
for j in xrange(0,7):
if(row[0] == ColumnAlphabet301[j]):
rowNum = int(row[1:])
df_T0U.loc[rowNum, Tre0Uno_columns[j]]=Val
df_T0U.loc[rowNum, 'RowID']=rowNum
for i in xrange(7,(LastRow301+1)):
#Change This month EAC to last month EAC
BeforeEACName = ('J%d' % (i))
AfterEACName = ('K%d' % (i))
Range(AfterEACName).value = Range(BeforeEACName).value
#Generate all columns name
CellNameArr301 = map(lambda x:('%s%d' % (x,i)), ColumnAlphabet301)
#Read 301 data into the Dataframe action
map(T0UreadRange, CellNameArr301)
#drop N/A
df_T0U = df_T0U[(pd.notnull(df_T0U['Req Description']))]
# #### 寫入Balance Sheet
def BSintoT0U(row):
XColumnName = ['PO Num_x', 'A_x', 'Current Budget_x', 'Exe Budget_x', 'C_x']
YColumnName = ['PO Num_y', 'A_y', 'Current Budget_y', 'Exe Budget_y', 'C_y']
for checkCol in YColumnName:
Col_IX = YColumnName.index(checkCol)
bs_Val = row[checkCol]
T0U_Val = row[XColumnName[Col_IX]]
if (bs_Val != None) and (pd.isnull(bs_Val) != True) and (bs_Val != T0U_Val):
#301 Column Name to Column Alphabet
ColAnchor = Tre0Uno_columns.index(checkCol[:-2])
Input_Location = ('%s%d' % (ColumnAlphabet301[ColAnchor], row['RowID']))
Range(Input_Location).value = bs_Val
Range(Input_Location).color = (136, 153, 238)
#Generate the key to merge
df_T0U['key']=df_T0U['Req No']
df_bs['key']=df_bs['Req No']
#merge left
MergeItem = pd.merge(df_T0U, df_bs, on='key', how='left')
#if Balance sheet has diff between 301
diff = MergeItem[(MergeItem['Current Budget_x'] != MergeItem['Current Budget_y']) |
(MergeItem['PO Num_x'] != MergeItem['PO Num_y']) |
(MergeItem['Exe Budget_x'] != MergeItem['Exe Budget_y']) |
(MergeItem['C_x'] != MergeItem['C_y'])]
#Merge and Fill in
diff['RELSULT'] = diff.apply(BSintoT0U, axis=1)
# #### 填入其他費用及自辦工時
FeeArr=[u'其他費用',u'自辦工時', u'自辦工時(MH)', u'間接分攤']
FeeSymbol=['3','4','mh', '5']
TypeAnchor = 0
FillInQuery=[]
mhTitle = map(lambda x:('%smh' % (x[0])), MH_Arr)
for i in xrange(7,(LastRow301+1)):
CheckRName = ('D%d' % (i))
CheckTName = ('A%d' % (i))
InputName = ('H%d' % (i))
CheckRValue = Range(CheckRName).value
if CheckRValue in FeeArr:
CheckTValue = Range(CheckTName).value
if CheckTValue != None:
TypeAnchor = CheckTValue[2]
SymbolIX = FeeArr.index(CheckRValue)
SymbolName = ('%s%s' % (TypeAnchor, FeeSymbol[SymbolIX] ))
mhComment = Range(InputName).comment
FillInQuery.append([SymbolName,CheckRName,InputName, mhComment])
else:
SymbolIX = FeeArr.index(CheckRValue)
SymbolName = ('%s%s' % (TypeAnchor, FeeSymbol[SymbolIX] ))
mhComment = Range(InputName).comment
FillInQuery.append([SymbolName,CheckRName,InputName, mhComment])
print FillInQuery
#Fill in MH
QueryTitleAnchor = map(lambda x:x[0], FillInQuery)
for mhcheck in QueryTitleAnchor:
if mhcheck in mhTitle:
mhTrueIX = mhTitle.index(mhcheck)
mhQueryIX = QueryTitleAnchor.index(mhcheck)
FillinTargetRange = FillInQuery[mhQueryIX][2]
FillinTargetComment =FillInQuery[mhQueryIX][3]
MH_Data = int(MH_Arr[mhTrueIX][1])
print mhcheck, MH_Arr[mhTrueIX][1] ,FillInQuery[mhQueryIX][2]
#Check the Comment Month
if str(format(d.month)) not in FillinTargetComment:
# Update and Change the Comment
print "Update"
OValue = Range(FillinTargetRange).value
Range(FillinTargetRange).value = OValue + MH_Data
Range(FillinTargetRange).comment = str(format(d.month))
#fill in other fee
for i in range(0,len(AC_Result_df)-1):
if AC_Result_df.index[i] in QueryTitleAnchor:
QueryFeeIX = QueryTitleAnchor.index(AC_Result_df.index[i])
QueryTargetRange = FillInQuery[QueryFeeIX][2]
FeeFillInValue = AC_Result_df[i]
Range(QueryTargetRange).value = FeeFillInValue
Range(QueryTargetRange).color = (136, 153, 238)
print FillInQuery[QueryFeeIX][0], QueryTargetRange, FeeFillInValue
from xlwings import Workbook, Range
import numpy as np
import pandas as pd
ROOT_DIR = os.path.dirname(os.getcwd())
wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx'))
# Storage and Markets
storages = Range('S->M', 'C1:BU1').value
markets = ['{0} ({1})'.format(row[1], row[0])
for row in Range('S->M', 'A2:B101',
atleast_2d=True).value]
regions = Range('S->M', 'A2:A101').value
D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value)
dist_df = pd.DataFrame({'region': regions, 'market': markets,
'S15_dist': D[:, storages.index('S15')],
'S61_dist': D[:, storages.index('S61')]})
av_dists = dist_df.groupby('region').mean()
av_dists['d'] = np.array(av_dists.min(1))
av_dists.to_csv('region_storage_dists.csv')
fig,ax = plt.subplots()
s = styles[0]
m = 's'
mcount = 0
print col
# ax = fig.add_subplot(nc-1,1,i)
l1 =ax.plot(data[:,xc],data[:,yc],marker='o', linewidth=2,label= col[yc])
# ax.set_title(col[0],fontsize=10)
ax2 = ax.twinx()
l2= ax2.plot(data[:,xc],data[:,yc+1],'D:r',label=col[yc+1])
ax2.set_ylabel('time(s)')
ax.set_xlabel('Grid Size',fontsize=12)
mcount += 1
# ax.set_ylabel(col[i])
ax.grid(1)
ax.legend(loc=2)
ax2.legend(loc=1)
plt.tight_layout()
#wb = Workbook(r'E:\Academic\ECN\CFD\Resistance Report\q2.xlsx')
wb = Workbook(r'E:\Academic\ECN\Python Scripts\book.xlsx')
cols = Range(1,"A1:D1").value
data = Range(1,'A2',asarray=1).table.value
data =data.astype('float')
#multiplot(cols,data)
singleplot_custom(cols,data,0,2)
from xlwings import Workbook, Range
import numpy as np
import pandas as pd
import os
import itertools
ROOT_DIR = os.path.dirname(os.getcwd())
wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx'))
# Storage and Plants
storages = Range('P->S', 'A2:A72').value
plants = Range('P->S', 'B1:K1').value
D = np.array(Range('P->S', 'B2:K72', atleast_2d=True).value)
# Only include the 4 storages from previous analysis.
storages_fixed = (u'S35', u'S51', u'S59', u'S73')
D_fixed = D[[storages.index(s) for s in storages_fixed], :]
# Seek to minimize the sum of the distance from each storage
# to its closest open processing plant.
# P = 1
total_distances = {}
for p in xrange(0, D_fixed.shape[1]):
total_distances[p] = sum(D_fixed[:, p])
best = min(total_distances, key=total_distances.get)
# Return set of best plants for a given plant size P, as well as
# total distance for that plant size P.
def get_best_plants(P):
combs = list(itertools.combinations(xrange(0, len(plants)), P))
def generate_publisher_emails(pubs_combined, contacts, br):
week_end = Range('Publisher Performance', 'C7').value + datetime.timedelta(days=7)
week_end = week_end.strftime('%m/%d')
week_start = Range('Publisher Performance', 'C7').value.strftime('%m/%d')
headerstyle = '<p style = "font-family: Calibri; font-size: 11pt; font-weight: bold; text-decoration: underline;">'
bodystyle = '<p style = "font-family: Calibri; font-size: 11pt;">'
boldstyle = '<p style = "font-family: Calibri; font-size: 11pt; font-weight: bold;">'
merged = pd.merge(pubs_combined, contacts, how= 'left', on= 'Publisher')
pub_list = list(merged['Publisher'].unique())
pub_emails = list(merged['cc_emails'].unique())
contact_emails = list(merged['Contact Email'].unique())
outlook = win32.Dispatch('Outlook.Application')
for i in range(0, len(pub_list)):
if pub_list[i] == 'ASG' or pub_list[i] == 'AOD':
br_performance = headerstyle + \
'Brand Remessaging</p>' + \
bodystyle + \
'Traffic Yield - ' + str(float(br['Traffic Yield'])) + '%' + \
'<br>' + \
'Traffic Actions - ' + str(br['Traffic Actions'][1]) + \
'</p>' + \
boldstyle + \
'Brand Remessaging Performance Chart ' + quarter_start() + ' - ' + week_end + \
'</p><br><br><br>'
else:
br_performance = ''
mail = outlook.CreateItem(0)
df = merged[merged['Publisher'] == pub_list[i]]
if pub_list[i] == 'ASG' or pub_list[i] == 'AOD':
greeting = 'Hi Dan and Team,'
elif pub_list[i] == 'Amazon':
greeting = 'Hi Lauren and Team,'
elif pub_list[i] == 'eBay':
greeting = 'Hi Katie,'
elif pub_list[i] == 'Magnetic':
greeting = 'Hi Melissa,'
elif pub_list[i] == 'Yahoo!':
greeting = 'Hi Krystal,'
elif pub_list[i] == 'Bazaar Voice':
greeting = 'Hi Alexa and Todd,'
else:
greeting = 'Hello,'
mail.To = str(contact_emails[i]).encode('utf-8')
mail.CC = str(pub_emails[i]).encode('utf-8')
mail.subject = main.qquarter() + ' 2016 DDR Performance Update: ' + \
week_start + '-' + week_end + ' - ' + str(pub_list[i])
mail.HTMLBody = '<body>' + \
bodystyle + \
greeting + \
'<br><br>' + \
'Below you will find your campaign performance breakout for the beginning of ' + \
main.qquarter() + ' through the week ' + \
'ending ' + week_end + '.</p>' + \
headerstyle + \
'DDR Performance ' + quarter_start() + ' - ' + week_end + ' (All Tactics Combined) </p>' + \
bodystyle + \
'CPGA - $' + str(float(df['CPGA']))[:-2] + '<br>' + 'GAs - ' + str(float(df['GAs'])) + \
'</p>' + \
boldstyle + \
'DDR Performance Chart by Tactic ' + quarter_start() + ' - ' + week_end + \
'<br><br><br><br><br><br><br>' + \
'Optimization Notes</p>' + \
br_performance + \
bodystyle + \
'<br><br><br>' + \
'Let me know if you have any questions.' + \
'<br><br>' + \
'Best,' + \
'</body>'
mail.Display()
from xlwings import Workbook, Range
import numpy as np
import pandas as pd
ROOT_DIR = os.path.dirname(os.getcwd())
wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx'))
# Storage and Markets
storages = Range('S->M', 'C1:BU1').value
markets = ['{0} ({1})'.format(row[1], row[0])
for row in Range('S->M', 'A2:B101',
atleast_2d=True).value]
regions = Range('S->M', 'A2:A101').value
D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value)
# [S35, S51, S59, S73], [P02, P03, P05, P09]
dist_df = pd.DataFrame({'region': regions, 'market': markets,
'S35_dist': D[:, storages.index('S35')],
'S51_dist': D[:, storages.index('S51')],
'S59_dist': D[:, storages.index('S59')],
'S73_dist': D[:, storages.index('S73')]})
av_dists = dist_df.groupby('region').mean()
av_dists['d'] = np.array(av_dists.min(1))
av_dists.to_csv('region_storage_dists_opt.csv')
def update_program_dashboard(program):
filename = LOCAL_DASH_PATH + PROGRAM_DASH_TEMPLATE
print('Excel program template filename: ', filename)
save_filename = LOCAL_DASH_PATH + 'program_dashboard_' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
wb = Workbook(filename)
excel = wb.xl_app
excel.visible = True
### milestone dashboard
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
row = 4 + 2 * file_key[0]
due_date_row = Range('team milestones', (row,3), (row,19))
status_row= Range('team milestones', (row+1,3), (row+1,19))
milestones = get_milestones(project)
due_date_row.value = [task.get_due_date() for task in milestones]
status_row.value = [task.status() for task in milestones]
### CAPA Status
row=2
clear_range = Range('CAPA status', (2,1), (500,11))
clear_range.value = ''
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
project_status_row = Range('CAPA status', (row,1), (row,10))
project_status_row.value = get_project_status(project)
capa_status_row = Range('CAPA status', (row+1,1), (row+1,10))
capa_status_row.value = get_capa_status(project)
row = row + 2
next_row = Range('CAPA status', (row,1), (row,10))
IC_cat_dash = get_cat_dash(project, 'IC')
for task_fields in IC_cat_dash:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### CA / PA dashboard
for task_fields in get_cat_dash(project, 'CA'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
for task_fields in get_cat_dash(project, 'PA'):
if task_fields != None:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### remediation
for task_fields in get_cat_dash(project, 'RM'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
row = row +1
### Implementation
row=2
clear_range = Range('implementation', (2,1), (500,11))
clear_range.value = ''
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
for procedure in get_implementation(project):
next_row = Range('implementation', (row,1), (row,10))
next_row.value = procedure
row = row + 1
wb.save(save_filename)
wb.close()
excel.quit()
from xlwings import Workbook, Range
import numpy as np
import pandas as pd
import os
ROOT_DIR = os.path.dirname(os.getcwd())
wb = Workbook(os.path.join(ROOT_DIR, "reference/StaticData-mod.xlsx"))
# Storage and Markets
storages = Range("S->M", "C1:BU1").value
markets = ["{0} ({1})".format(row[1], row[0]) for row in Range("S->M", "A2:B101", atleast_2d=True).value]
D = np.array(Range("S->M", "C2:BU101", atleast_2d=True).value)
# Could do this recursively / with DP but this is quicker.
# S = 1
total_distances = {}
for s in xrange(0, D.shape[1]):
total_distances[s] = sum(D[:, s])
best = min(total_distances, key=total_distances.get)
# S = 2
total_distances_2 = {}
for i in xrange(0, D.shape[1]):
for j in xrange(i + 1, D.shape[1]):
min_dists = [min(D[r, i], D[r, j]) for r in xrange(0, len(D))]
total_distances_2[(storages[i], storages[j])] = sum(min_dists)
best2 = min(total_distances_2, key=total_distances_2.get)
# S = 3
from xlwings import Workbook, Range
import numpy as np
import pandas as pd
import os
ROOT_DIR = os.path.dirname(os.getcwd())
wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx'))
# Storage and Markets
storages = Range('S->M', 'C1:BU1').value
markets = Range('S->M', 'B2:B101').value
regions = Range('S->M', 'A2:A101').value
D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value)
opt_storages = ['S35', 'S51', 'S59', 'S73']
i = storages.index('S35')
j = storages.index('S51')
k = storages.index('S59')
l = storages.index('S73')
min_dists = [min(D[r, i], D[r, j], D[r, k], D[r, l])
for r in xrange(0, len(D))]
closest_storage_ind = [[D[r, i], D[r, j], D[r, k], D[r, l]].index(
min(D[r, i], D[r, j], D[r, k], D[r, l])) for r in xrange(0, len(D))]
closest_df = pd.DataFrame(
{'market': markets,
'region': regions,
'closest_storage': [opt_storages[s]
for s in closest_storage_ind]})
def update_excel_dashboard(project):
print('Started function.')
### filekey requires at least first two characters filename
file_key = return_file_key(project.name)
dash_filename = return_filename(file_key, dash_list)
### dispatch project class object, importing data into Project Class
if dash_filename != None:
filename = LOCAL_DASH_PATH + dash_filename
save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
else:
filename = LOCAL_DASH_PATH + PP_DASH_TEMPLATE
save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
wb = Workbook(filename)
excel = wb.xl_app
excel.visible = True
pending_tasks = Sheet('pending_tasks')
project_plan = Sheet('project_plan')
sheet_dict = {'project_plan': (project_plan, project.Task_list), 'pending_tasks': (pending_tasks, get_pending_tasks(project))}
cat_task_dict = get_cat_task_dict(project)
for sheet_name in list(sheet_dict.keys())[:2]:
row =2
next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,11))
clear_range = Range(sheet_dict[sheet_name][0].name, (2,1), (500,11))
clear_range.value = ''
for item in sheet_dict[sheet_name][1]:
task = item.task
task_fields = [task.WBS, item.action, project.get_phase(item), task.Name, task.Start, task.Finish, task.Deadline, task.PercentComplete, \
item.status(), item.status_override, item.recovery_plan]
next_row.value = task_fields
row = row +1
next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,9))
print('Updated project plan and pending tasks.')
### milestone dashboard
row = 5
next_row = Range('milestones', (row,2), (row,4))
for item in get_milestones(project):
due_date = "Not assigned"
task = item.task
if task.Deadline != 'NA':
if task.Deadline.date() < task.Finish.date() and task.PercentComplete != 100:
due_date = task.Deadline
mitigation = 'New target date: ' + task.Finish.strftime("%d-%b-%Y") + '. ' + item.recovery_plan
else:
due_date = task.Finish
mitigation = item.recovery_plan
else:
due_date = task.Finish
mitigation = item.recovery_plan
task_fields = [task.Name, due_date, item.status(), mitigation]
next_row.value = task_fields
row = row +1
next_row = Range('milestones', (row,2), (row,4))
print('Updated milestones.')
row = 2
clear_range = Range('CAPA status', (2,1), (500,11))
clear_range.value = ''
### project
project_status_row = Range('CAPA status', (row,1), (row,10))
task_fields = get_project_status(project)
project_status_row.value = task_fields
project_status = Range('milestones', (1,4), (1,4))
project_status.value = task_fields[6]
capa_status_row = Range('CAPA status', (row+1,1), (row+1,10))
task_fields = get_capa_status(project)
capa_status_row.value = task_fields
capa_status = Range('milestones', (2,4), (2,4))
capa_status.value = task_fields[6]
row = row + 2
### interim controls
next_row = Range('CAPA status', (row,1), (row,11))
IC_cat_dash = get_cat_dash(project, 'IC')
for task_fields in IC_cat_dash:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### CA / PA dashboard
for task_fields in get_cat_dash(project, 'CA'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
for task_fields in get_cat_dash(project, 'PA'):
if task_fields != None:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### remediation
for task_fields in get_cat_dash(project, 'RM'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
print('Updated CAPA status.')
#### implementation
row = 2
clear_range = Range('implementation', (2,1), (500,11))
clear_range.value = ''
next_row = Range('implementation', (row,1), (row,11))
for procedure in get_implementation(project):
next_row.value = procedure
row = row +1
next_row = Range('implementation', (row,1), (row,11))
print('Updated implementation.')
wb.save(save_filename)
#print(save_filename)
wb.close()
