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()