def tableau_data(): Workbook.caller() save_path = str(dr_pivot_path()) save_path = save_path[:save_path.rindex('\\')] ddr_data = dr.raw_pivot() d = dr.tableau_campaign_data(ddr_data) s = search.merge_data() tableau = d.append(s) tableau['Quarter'] = qquarter() if Range('merged', 'A1').value is None: chunk_df(tableau, 'merged', 'A1') # If data is already present in the tab, the two data sets are merged together and then copied into the data tab. else: past_data = pd.read_excel(dr_pacing_path(), 'merged', index_col=None) past_data = past_data[past_data['Campaign'] != 'Search'] appended_data = past_data.append(tableau) Sheet('merged').clear() chunk_df(appended_data, 'merged', 'A1') #Range('Sheet3', 'AT1').value = pd.to_datetime(ddr_data['Date'].max()) + datetime.timedelta(days= 1) wb = Workbook() Sheet('Sheet1').name = 'DDR Data' chunk_df(ddr_data, 'DDR Data', 'A1') wb.save(save_path + '\\' + 'DR_Raw_Data.xlsx') wb.close()
def reshape_forecasts_for_reporting(): Workbook.caller() r_data = forecast.generate_forecasts() pacing_data = forecast.merge_pacing_and_forecasts(r_data) tab = dr.tableau_pacing(pacing_data) forecast.output_forecasts(tab)
def export_csv(streamp,startdt,enddt): # dc=DataClient() opt='EXCEL' # data=dc.load_data(streamp,startdt,enddt) if opt == 'EXCEL': wb=Workbook("test.xlsx") wb.caller() n = Range('Sheet1', 'B1').value # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n)
def combobox(): """ This populates the ComboBox with the values from the database """ # Make a connection to the calling Excel file wb = Workbook.caller() # Place the database next to the Excel file db_file = os.path.join(os.path.dirname(wb.fullname), 'chinook.sqlite') # Database connection and creation of cursor con = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) cursor = con.cursor() # Database Query cursor.execute("SELECT PlaylistId, Name FROM Playlist") # Write IDs and Names to hidden sheet Range('Source', 'A1').table.clear_contents() Range('Source', 'A1').value = cursor.fetchall() # Format and fill the ComboBox to show Names (Text) and give back IDs (Values) # TODO: implement natively in xlwings combo = "ComboBox1" wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \ 'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address)) wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0 # Close cursor and connection cursor.close() con.close()
def test_mock_caller(self): _skip_if_not_default_xl() Workbook.set_mock_caller(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')) wb = Workbook.caller() Range('A1', wkb=wb).value = 333 assert_equal(Range('A1', wkb=wb).value, 333)
def read(*args): data = args[0] if len(args) == 1: wb = Workbook.caller() elif len(args) == 2: filename = args[1] wb = Workbook(filename) else: return None data.alpha = ExcelReaderFunctions.importAlpha() data.eps0 = ExcelReaderFunctions.importEpsilon() data.demand, data.numberOfTimePeriods, data.demandAsArray = ExcelReaderFunctions.importDemand() data.forwardCharVectors, data.numberOfForwardProducts = ExcelReaderFunctions.importForwardCharVectors() data.hpfcVectors, data.numberOfHpfcVectors = ExcelReaderFunctions.importHpfcVectors() data.forwardPrices = ExcelReaderFunctions.importForwardPrices() data.timePeriodNames = ExcelReaderFunctions.importHedgingPeriodNames() data.forwardNames = ExcelReaderFunctions.importForwardProductNames() data.name = filename.split('\\')[-1].split('/')[-1] #data.initialise() if len(args) == 2: #wb.close() pass return data, wb
def rand_numbers(): """ produces standard normally distributed random numbers with shape (n,n)""" wb = Workbook.caller() # Creates a reference to the calling Excel file n = int(Range('Sheet1', 'B1').value) # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n) Range('Sheet1', 'C3').value = rand_num
def FillPrice(row): #Open original excel wb = Workbook(Excel1) wb = Workbook.caller() #fill in the price RangeText=('%s%d' % (UnoPriceCol,row['Item Row Num1'])) Range(RangeText).value=row['Item Price2']
def test_mock_caller(self): # Can't really run this one with app_visible=False _skip_if_not_default_xl() Workbook.set_mock_caller(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')) wb = Workbook.caller() Range('A1', wkb=wb).value = 333 assert_equal(Range('A1', wkb=wb).value, 333)
def publisher_performance_emails(): pacing_wb = Workbook.caller() performance.generate_publisher_tables(performance.publisher_tactic_data()) performance.generate_publisher_emails(performance.publisher_overall_data(), performance.publisher_contact_info(), performance.brand_remessaging()) Application(wkb=pacing_wb).xl_app.Run('Format_Tables')
def test_mock_caller(self): _skip_if_not_default_xl() Workbook.set_mock_caller( os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')) wb = Workbook.caller() Range('A1', wkb=wb).value = 333 assert_equal(Range('A1', wkb=wb).value, 333)
def VideoDescription(): url = Range('Sheet1', 'C3').value video = pafy.new(url) wb = Workbook.caller() # Creates a reference to the calling Excel file videoTitle = video.title Range('Sheet1', 'G3').value = videoTitle
def main(): wb = Workbook.caller() # User Inputs num_simulations = int(Range('E3').value) time = Range('E4').value num_timesteps = int(Range('E5').value) dt = time / num_timesteps # Length of time period vol = Range('E7').value mu = np.log(1 + Range('E6').value) # Drift starting_price = Range('E8').value perc_selection = [5, 50, 95] # percentiles (hardcoded for now) # Animation if Range('E9').value.lower() == 'yes': animate = True else: animate = False # Excel: clear output, write out initial values of percentiles/sample path and set chart source # and x-axis values Range('O2').table.clear_contents() Range('P2').value = [ starting_price, starting_price, starting_price, starting_price ] Chart('Chart 5').set_source_data(Range((1, 15), (num_timesteps + 2, 19))) Range('O2').value = np.round( np.linspace(0, time, num_timesteps + 1).reshape(-1, 1), 2) # Preallocation price = np.zeros((num_timesteps + 1, num_simulations)) percentiles = np.zeros((num_timesteps + 1, 3)) # Set initial values price[0, :] = starting_price percentiles[0, :] = starting_price # Simulation at each time step for t in range(1, num_timesteps + 1): rand_nums = np.random.randn(num_simulations) price[t, :] = price[t - 1, :] * np.exp((mu - 0.5 * vol**2) * dt + vol * rand_nums * np.sqrt(dt)) percentiles[t, :] = np.percentile(price[t, :], perc_selection) if animate: Range((t + 2, 16)).value = percentiles[t, :] Range((t + 2, 19)).value = price[t, 0] # Sample path if sys.platform.startswith('win'): wb.application.screen_updating = True if not animate: Range('P2').value = percentiles Range('S2').value = price[:, :1] # Sample path
def output_pacing_data_for_forecasts(): wb = Workbook.caller() dr_data = dr.raw_pivot() #pace = dr.pacing() #data_merged = data_transform.raw_pacing_and_dr(dr_data, pace) dr_forecasting = data_transform.transform_dr(dr_data) wb.set_current() Sheet('raw_pacing_data').clear_contents() Range('raw_pacing_data', 'A1', index=False).value = dr_forecasting performance.publishers(dr_data)
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 __init__(self, sheet="log"): """ create sheet and setup format """ logging.Handler.__init__(self) try: self.caller = Workbook.caller() except: self.caller = None return # create sheet if it does not exist self.sheet = sheet if sheet not in [s.name for s in Sheet.all()]: Sheet.add(sheet) Sheet(sheet).clear() self.row = 0
def playlist(): """ Get the playlist content based on the ID from the Dropdown """ # 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() # 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 xl_fibonacci(): """ This is a wrapper around fibonacci() to handle all the Excel stuff """ # Create a reference to the calling Excel Workbook wb = Workbook.caller() # Get the input from Excel and turn into integer n = Range('B1').options(numbers=int).value # Call the main function seq = fibonacci(n) # Clear output Range('C1').vertical.clear_contents() # Return the output to Excel in column orientation Range('C1').options(transpose=True).value = seq
def playlist(): """ Get the playlist content based on the ID from the Dropdown """ # 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() # 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 xl_fibonacci(): """ This is a wrapper around fibonacci() to handle all the Excel stuff """ # Create a reference to the calling Excel Workbook wb = Workbook.caller() # Get the input from Excel and turn into integer n = int(Range('B1').value) # Call the main function seq = fibonacci(n) # Clear output Range('C1').vertical.clear_contents() # Return the output to Excel # zip() is used to push a list over in column orientation (list() needed on PY3) Range('C1').value = list(zip(seq))
def main(): wb = Workbook.caller() # User Inputs num_simulations = Range('E3').options(numbers=int).value time = Range('E4').value num_timesteps = Range('E5').options(numbers=int).value dt = time/num_timesteps # Length of time period vol = Range('E7').value mu = np.log(1 + Range('E6').value) # Drift starting_price = Range('E8').value perc_selection = [5, 50, 95] # percentiles (hardcoded for now) # Animation animate = Range('E9').value.lower() == 'yes' # Excel: clear output, write out initial values of percentiles/sample path and set chart source # and x-axis values Range('O2').table.clear_contents() Range('P2').value = [starting_price, starting_price, starting_price, starting_price] Chart('Chart 5').set_source_data(Range((1, 15),(num_timesteps + 2, 19))) Range('O2').value = np.round(np.linspace(0, time, num_timesteps + 1).reshape(-1,1), 2) # Preallocation price = np.zeros((num_timesteps + 1, num_simulations)) percentiles = np.zeros((num_timesteps + 1, 3)) # Set initial values price[0,:] = starting_price percentiles[0,:] = starting_price # Simulation at each time step for t in range(1, num_timesteps + 1): rand_nums = np.random.randn(num_simulations) price[t,:] = price[t-1,:] * np.exp((mu - 0.5 * vol**2) * dt + vol * rand_nums * np.sqrt(dt)) percentiles[t, :] = np.percentile(price[t, :], perc_selection) if animate: Range((t+2, 16)).value = percentiles[t, :] Range((t+2, 19)).value = price[t, 0] # Sample path if sys.platform.startswith('win'): Application(wb).screen_updating = True if not animate: Range('P2').value = percentiles Range('S2').value = price[:, :1] # Sample path
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 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 hoge(): wb = Workbook.caller() Range('A1').value = "Call Python!"
def my_test(row): #create item data frame columns1=['Item Row Num1', 'Item Text1'] index1 = np.arange(250) df_item1 = pd.DataFrame(columns=columns1, index = index1) columns2=['Item Row Num2', 'Item Text2', 'Item Price2'] index2 = np.arange(250) df_item2 = pd.DataFrame(columns=columns2, index = index2) #read work book one wb = Workbook(r'C:\Python2.7.10\project_database\xlwings\NET.xls') wb = Workbook.caller() RowANum=row['Title Row Num1'] RangeText=('C%d' % RowANum) colorcode=Range(RangeText).color count = RowANum con=0 while (con!=colorcode): count = count + 1 RangeText=('C%d' % count) con=Range(RangeText).color ItemText = Range(RangeText).value df_item1.loc[count-RowANum-1, 'Item Row Num1']=count df_item1.loc[count-RowANum-1, 'Item Text1']=ItemText #print "Good bye!" df_item1 = df_item1[(pd.notnull(df_item1['Item Row Num1']))] #read work book two wb2 = Workbook(r'C:\Python2.7.10\project_database\xlwings\FINAL_PRICE.xlsx') wb2 = Workbook.caller() RowBNum=row['Title Row Num2'] RangeText=('L%d' % RowBNum) colorcode=Range(RangeText).color count = RowBNum con=0 while (con!=colorcode): count = count + 1 RangeText=('L%d' % count) RangePrice=('O%d' % count) con=Range(RangeText).color ItemText = Range(RangeText).value ItemPrice = Range(RangePrice).value df_item2.loc[count-RowBNum-1, 'Item Row Num2']=count df_item2.loc[count-RowBNum-1, 'Item Text2']=ItemText df_item2.loc[count-RowBNum-1, 'Item Price2']=ItemPrice df_item2 = df_item2[(pd.notnull(df_item2['Item Row Num2']))] df_item1['key']=df_item1['Item Text1'] df_item2['key']=df_item2['Item Text2'] #print df_item1 #print df_item2 MergeItem = pd.merge(df_item1, df_item2, on='key', how='left') del df_item1 del df_item2 gc.collect() #print MergeItem.loc[:,['Item Row Num1','Item Text1','Item Row Num2','Item Price2']] #fill in price def FillPrice(row): wb = Workbook(r'C:\Python2.7.10\project_database\xlwings\NET.xls') wb = Workbook.caller() RangeText=('L%d' % row['Item Row Num1']) Range(RangeText).value=row['Item Price2'] MergeItem.apply(FillPrice, axis=1) del MergeItem gc.collect() print row['Title Row Num1'],row['Title Text1'] return row['Title Row Num1'] + row['Title Row Num2']
def MergenFillPrice(Excel1, Excel2, TitleCheck1, TitleCheck2, UnoTitleCol, DueTitleCol, UnoPriceCol, DuePriceCol, UnoStart, UnoEnd, DueStart, DueEnd): UnoStart=int(UnoStart) UnoEnd=int(UnoEnd) DueStart=int(DueStart) DueEnd=int(DueEnd) #Open Original Excel File wb = Workbook(Excel1) wb = Workbook.caller() #Get title color #[Input] Range colorcode = Range(TitleCheck1).color #Create Empty Dataframe for original excel columns = ['Title Row Num1', 'Title Text1'] ArangeLen=(UnoEnd-UnoStart+200) index = np.arange(int(ArangeLen)) df1 = pd.DataFrame(columns=columns, index = index) #Put Title in Dataframe j=-1 for i in xrange(int(UnoStart),int(UnoEnd)): RangeName=('%s%d' % (UnoTitleCol,i)) colorread=Range(RangeName).color if colorread == colorcode: j+=1 TitleText=Range(RangeName).value df1.loc[j, 'Title Row Num1']=i df1.loc[j, 'Title Text1']=TitleText #remove the N/A row df1 = df1[(pd.notnull(df1['Title Row Num1']))] #Read the Excel including price wb2 = Workbook(Excel2) wb2 = Workbook.caller() #Get title color #[Input] Range colorcode=Range(TitleCheck2).color #Create Empty Dataframe columns=['Title Row Num2', 'Title Text2'] ArangeLen2=DueEnd-DueStart+200 index = np.arange(int(ArangeLen2)) df2 = pd.DataFrame(columns=columns, index = index) #Put Title in Dataframe j=-1 for i in range(DueStart,DueEnd): RangeName=('%s%d' % (DueTitleCol,i)) colorread=Range(RangeName).color if colorread == colorcode: j+=1 TitleText=Range(RangeName).value df2.loc[j, 'Title Row Num2']=i df2.loc[j, 'Title Text2']=TitleText #remove the N/A row df2 = df2[(pd.notnull(df2['Title Row Num2']))] def ReadItem(row): #create item data frame for two excel columns1=['Item Row Num1', 'Item Text1'] index1 = np.arange(600) df_item1 = pd.DataFrame(columns=columns1, index = index1) columns2=['Item Row Num2', 'Item Text2', 'Item Price2'] index2 = np.arange(600) df_item2 = pd.DataFrame(columns=columns2, index = index2) #Read Original Excel File wb = Workbook(Excel1) wb = Workbook.caller() #Get the title row number RowANum=row['Title Row Num1'] RangeText=('%s%d' % (UnoTitleCol,RowANum)) colorcode=Range(RangeText).color count = RowANum con=0 #read the row as item until next title color while (con!=colorcode or count==UnoEnd): count = count + 1 RangeText=('%s%d' % (UnoTitleCol,count)) con=Range(RangeText).color ItemText = Range(RangeText).value #put item row number and text into dataframe df_item1.loc[count-RowANum-1, 'Item Row Num1']=count df_item1.loc[count-RowANum-1, 'Item Text1']=ItemText #remove the N/A row df_item1 = df_item1[(pd.notnull(df_item1['Item Row Num1']))] #Read the excel which include price wb2 = Workbook(Excel2) wb2 = Workbook.caller() #Get the title row number RowBNum=row['Title Row Num2'] RangeText=('%s%d' % (DueTitleCol,RowBNum)) colorcode=Range(RangeText).color count = RowBNum con=0 #read the row as item until next title color while (con!=colorcode or count==DueEnd): count = count + 1 RangeText=('%s%d' % (DueTitleCol,count)) RangePrice=('%s%d' % (DuePriceCol,count)) con=Range(RangeText).color ItemText = Range(RangeText).value ItemPrice = Range(RangePrice).value #put item row number, text and price into dataframe df_item2.loc[count-RowBNum-1, 'Item Row Num2']=count df_item2.loc[count-RowBNum-1, 'Item Text2']=ItemText df_item2.loc[count-RowBNum-1, 'Item Price2']=ItemPrice #remove the N/A row df_item2 = df_item2[(pd.notnull(df_item2['Item Row Num2']))] #Generate the key to merge df_item1['key']=df_item1['Item Text1'] df_item2['key']=df_item2['Item Text2'] #item merge left MergeItem = pd.merge(df_item1, df_item2, on='key', how='left') #garbage collector del df_item1 del df_item2 gc.collect() #Fill the price into the original Excel def FillPrice(row): #Open original excel wb = Workbook(Excel1) wb = Workbook.caller() #fill in the price RangeText=('%s%d' % (UnoPriceCol,row['Item Row Num1'])) Range(RangeText).value=row['Item Price2'] #Apply fill in price MergeItem.apply(FillPrice, axis=1) del MergeItem gc.collect() #Generate the key to merge df1['key']=df1['Title Text1'] df2['key']=df2['Title Text2'] print "Start Merge" #title merge inner MergeTitle = pd.merge(df1, df2, on='key', how='inner') print MergeTitle del df1 del df2 gc.collect() if MergeTitle.empty: return "Empty" else: MergeTitle['Value'] = MergeTitle.apply(ReadItem, axis=1) return "FINISH"
def myfunction(): wb = Workbook.caller() Range('A1').value = "Call Python!"
def get_workbook_name(): """Writes the name of the Workbook into Range("D3") of Sheet 1""" wb = Workbook.caller() Range(1, 'D3').value = wb.name
def FillPrice(row): wb = Workbook(r'C:\Python2.7.10\project_database\xlwings\NET.xls') wb = Workbook.caller() RangeText=('L%d' % row['Item Row Num1']) Range(RangeText).value=row['Item Price2']
from IPython.display import IFrame #A few imports we will need later from xlwings import Workbook, Sheet, Range, Chart import pandas as pd import numpy as np import plotly.plotly as py import plotly.tools as tlsM from IPython.display import HTML from plotly.graph_objs import * #workbook connection wb = Workbook.caller() #Now we can use some of these controls to customize the folder_name = Range('Dashboard', 'B2').value graph_title = Range('Dashboard', 'B3').value #short function to create a new dataframe using xlwings def new_df(shtnm, startcell='A1'): data = Range(shtnm, startcell).table.value temp_df = pd.DataFrame(data[1:], columns=data[0]) return (temp_df) ###Make some dataframes from the workbook sheets #Core Product shtnm1 = Range('Dashboard', 'B6').value df = new_df(shtnm1) #2nd Product
def rand_numbers(): """ produces std. normally distributed random numbers with shape (n,n)""" wb = Workbook.caller() # Creates a reference to the calling Excel file n = int(Range('Sheet1', 'B1').value) # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n) Range('Sheet1', 'C3').value = rand_num
from xlwings import Workbook, Sheet, Range, Chart import win32com.client import re import pandas as pd import sqlite3 as lite import numpy as np import gc import os import datetime import Search_History #read in Excel UpdateArr = [] NotFoundPath = 'C:\\Search_Result\\Not_Found.xlsx' wb = Workbook(NotFoundPath) wb = Workbook.caller() print Range('A1').horizontal.value AllRow = Range('A1').vertical.value Max_Row = len(AllRow) print Max_Row for i in xrange(1,Max_Row+1): RName = ('A%d' % i) Col = Range(RName).horizontal.value if len(Col)>=3: for addcol in xrange(3,len(Col)+1): #or (255, 192, 0) #and (0, 176, 240) ConColor = Range((i,addcol)).color if ConColor == None: UpdateArr.append([Col[0], Col[1] , "None", Range((i,addcol)).value])
def SearchFile(Path,Search_Condition,Search_Type): #Create New DataFrame columns1=['File Name', 'Path', 'Folder'] index1 = np.arange(30000) df = pd.DataFrame(columns=columns1, index = index1) #Search => None if Search_Type == 'None': i=(-1) #Path=unicode(Path,'utf8') for pathinfile, subdirs, files in betterwalk.walk(Path): for name in files: if Search_Condition in name: i+=1 fullPath = os.path.join(pathinfile,name) df.loc[i, 'Path']=fullPath df.loc[i, 'File Name']=name #drop N/A df = df[(pd.notnull(df['File Name']))] #Search => OR if Search_Type == 'OR': SearchORArr=Search_Condition.split(',') i=(-1) for pathinfile, subdirs, files in betterwalk.walk(Path): for name in files: ORresult = map(lambda x:re.findall(x,name),SearchORArr) if not isListEmpty(ORresult): i+=1 fullPath = os.path.join(pathinfile,name) df.loc[i, 'Path']=fullPath df.loc[i, 'File Name']=name #drop N/A df = df[(pd.notnull(df['File Name']))] #Search => AND if Search_Type == 'AND': SearchANDArr=Search_Condition.split(',') i=(-1) for pathinfile, subdirs, files in betterwalk.walk(Path): for name in files: ANDresult = map(lambda x:re.findall(x,name),SearchANDArr) if ANDCheckEmpty(ANDresult)== True: i+=1 fullPath = os.path.join(pathinfile,name) df.loc[i, 'Path']=fullPath df.loc[i, 'File Name']=name #drop N/A df = df[(pd.notnull(df['File Name']))] if df.empty: return ('No Results') os.chdir('//ecsbks01/swap/DDD00/virtualenv/WinPython-32bit-2.7.10.2/python-2.7.10/Project_Evaluate_Excel/Search_History') #Search for files #word1=Search_Condition.decode('utf-8') #df['Search Result']=df['File Name'].str.contains(Search_Condition) #result = df[(df['Search Result']==True)] #search for files write into excel write_df=df.loc[:,['File Name','Path']] writer = ExcelWriter('Result-Output.xls') write_df.to_excel(writer,'Result',index=False) writer.save() #turn search to files into hyperlink CWPath = '\\\\ecsbks01\\swap\\DDD00\\virtualenv\\WinPython-32bit-2.7.10.2\\python-2.7.10\\Project_Evaluate_Excel\\Search_History' Excel_Path = os.path.join(CWPath, 'Result-Output.xls') wb = Workbook(Excel_Path) wb = Workbook.caller() checkArr = Range('B2').vertical.value i = 2 for check in checkArr: RangeName=('B%d' % (i)) displayRange=('A%d' % (i)) address=Range(RangeName).value display_name = Range(displayRange).value i+=1 try: Range(RangeName).add_hyperlink(address, text_to_display=address) except: pass return "FINISH"
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
def hietograma(): 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) wb = Workbook.caller() #### clear table Range('R5').table.clear_contents() #### introduccion de los datos ####periodo de retorno T = int(Range('INTERFACE', 'D8').value) #### duracion de lluvia dur = int(Range('INTERFACE', 'D10').value) #### intervalo de tiempo inval = int(Range('INTERFACE', 'D12').value) #### En este script I1/Id se denomina X X = int(Range('INTERFACE', 'D14').value) #### Precipitaciones maximas diarias P24 = Range('P24', 'C14:J14').value if Range('INTERFACE', 'D12').value == 'Ya tengo P24': P24 = Range('INTERFACE', 'D18').value else: #P24=Range('P24', 'C14:J14').value P24 = lluvia_max() print(P24) #### periodos de retorno disponibles Tr = [2, 5, 10, 25, 50, 100, 500, 1000] ind = Tr.index(T) #### generacion del tiempo eje X time = np.arange(inval, dur * 60 + inval, inval) Range('INTERFACE', 'R5').value = np.arange(inval, dur * 60 + inval, inval)[:, np.newaxis] #### Precipitaciones maximas diarias mm/hr #Id=[i/24 for i in P24] Id = P24 / 24 #### Intensidad de lluvia mm/hr I = Id * (X)**((28**0.1 - (time / 60)**0.1) / (28**0.1 - 1)) Range('INTERFACE', 'S5').value = I[:, np.newaxis] lluvia = I * time / 60 Range('INTERFACE', 'T5').value = lluvia[:, np.newaxis] x = lluvia[0] #### Incremental depth IncDep = [] for i in range(1, len(lluvia)): IncDep.append(lluvia[i] - lluvia[i - 1]) IncDep.insert(0, x) IncDep = np.asarray(IncDep) Range('INTERFACE', 'U5').value = IncDep[:, np.newaxis] #### Ordenacion methodo de bloques alternos nr = Range('U5').table.last_cell.row tt = sorted(Range('INTERFACE', 'U5:U' + str(nr)).value) new_tt = sorted(tt)[1::2] + sorted(tt)[::2][::-1] new_tt = np.asarray(new_tt) Range('INTERFACE', 'V5:V' + str(nr)).value = new_tt[:, np.newaxis] #### Chart chart = Chart('Wykres 4', source_data=Range('V5:V' + str(nr)).table)
# -*- coding: utf-8 -*- from xlwings import Workbook, Sheet, Range, Chart import win32com.client import re import pandas as pd import sqlite3 import numpy as np import gc gc.collect() wb = Workbook(r'C:\Python2.7.10\project_database\xlwings\NET.xls') wb = Workbook.caller() #get title color colorcode=Range('C2539').color print colorcode columns=['Title Row Num1', 'Title Text1'] index = np.arange(5000) df1 = pd.DataFrame(columns=columns, index = index) j=-1 #12028 #5435,9137 for i in range(8724,9126): RangeName=('C%d' % i) colorread=Range(RangeName).color if colorread == colorcode: j+=1 TitleText=Range(RangeName).value df1.loc[j, 'Title Row Num1']=i df1.loc[j, 'Title Text1']=TitleText