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