def months_stat(self, q_months, year):
q_df = self.period_calc(q_months, year)
sum_q = self.period_stat(q_months, year)
wb = Workbook()
sh = Sheet.add("Summary", wkb = wb)
row_flag = write_to_excel(q_df, sh = sh)
row_flag = write_to_excel(sum_q, sh = sh, row_flag = row_flag)
sh = Sheet.add("Master", wkb = wb)
row_flag = write_to_excel(self.active_on_the_day(t_month_end(q_months[-1], year)) .data.pipe(ready_excel),
sh = sh)
sh1 = Sheet.add("Aggregate", wkb = wb)
row_flag = write_to_excel('New Leases During the Period', sh = sh1)
new_leases_list = self.new_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel)
row_flag = write_to_excel(new_leases_list, sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Expired During the Period', sh = sh1, row_flag = row_flag)
expired_leases_list = self.old_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel)
row_flag = write_to_excel(expired_leases_list, sh = sh1, row_flag = row_flag)
r_expired_leases_list, r_new_leases_list, period_rate = self.renewal_a(q_months, year)
sh1 = Sheet.add("Renewal", wkb = wb)
row_flag = write_to_excel('Renewed Leases During the Period', sh = sh1)
row_flag = write_to_excel('Original Leases', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(r_expired_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Renewed Leases', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(r_new_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Weighted Average Reversion Rate', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(period_rate, sh = sh1, row_flag = row_flag)
quarter = q_months[-1]//3
for tower in range(1,3):
sh_new = Sheet.add("Tower {tower} {year} Q{quarter}".format(tower = tower, year = year, quarter = quarter), wkb = wb)
row_flag = write_to_excel('Tower {tower} New Leases During the Period'.format(tower = tower), sh = sh_new)
new_leases_list_T = new_leases_list.loc[new_leases_list['BLDG'] == tower].copy()
row_flag = write_to_excel(new_leases_list_T, sh = sh_new, row_flag = row_flag)
row_flag = write_to_excel('Tower {tower} Expired Leases During the Period'.format(tower = tower), sh = sh_new, row_flag = row_flag)
expired_leases_list_T = expired_leases_list.loc[expired_leases_list['BLDG'] == tower].copy()
row_flag = write_to_excel(expired_leases_list_T, sh = sh_new, row_flag = row_flag)
Sheet('Sheet1').delete()
wb.save("Operating Statistics Q{quarter} {year}".format(quarter = quarter, year = year))
#wb.close()
return "OK"
def xlo(df, filename=None):
""" show pandas dataframe or series in excel sheet
uses xlwings which allows writing to open file
"""
if not filename:
filename = "_temp.xlsx"
if not os.path.isfile(filename):
wb = Workbook()
Sheet("Sheet2").delete()
Sheet("Sheet3").delete()
else:
wb = Workbook(filename)
Sheet.add()
Range("A1").value = df
wb.save(filename)
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 test_add_wkb(self):
# test use of add with wkb argument
# Connect to an alternative test file and make Sheet1 the active sheet
xl_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx')
wb_2nd = Workbook(xl_file, app_visible=False, app_target=APP_TARGET)
n_before = [sh.name for sh in Sheet.all(wkb=wb_2nd)]
Sheet.add(name="default", wkb=wb_2nd)
Sheet.add(name="after1", after=1, wkb=wb_2nd)
Sheet.add(name="before1", before=1, wkb=wb_2nd)
n_after = [sh.name for sh in Sheet.all(wkb=wb_2nd)]
n_before.append("default")
n_before.insert(1, "after1")
n_before.insert(0, "before1")
assert_equal(n_before, n_after)
wb_2nd.close()
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_add_before(self):
new_sheet = Sheet.add(before='Sheet1')
assert_equal(Sheet(1).name, new_sheet.name)
## CVS File check exist or not & Size
if os.path.isfile(file_itogo or file_ir or file_iv):
if os.stat(file_itogo or file_ir or file_iv).st_size > 0:
## Open new Workbook
wb = Workbook(app_visible=False )
# Prepare first sheet
sheet1 = pd.read_csv(file_itogo, delim_whitespace=True)
Sheet.add('csv_itogo',before='Sheet1')
Range('csv_itogo','A1',index=False).value =sheet1
Range('csv_itogo','A:ZZ').autofit('c')
# Prepare Second sheet
sheet2 = pd.read_csv( file_ir, delim_whitespace=True)
Sheet.add('csv_ir',after='csv_itogo')
Range('csv_ir','A1',index=False).value = sheet2
Range('csv_ir','A:ZZ').autofit('c')
def test_add_name_already_taken(self):
Sheet.add('Sheet1')
def test_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
# Any way to create a new worksheet using xlwings? from xlwings import Sheet Sheet.add()
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_add_before(self):
new_sheet = Sheet.add(before='Sheet1')
assert_equal(Sheet(1).name, new_sheet.name)
def test_add_name_already_taken(self):
Sheet.add('Sheet1')
def add_sheet(self, name):
Sheet.add(name)
self._i = 1
Sheet('Result',wkb=wb).name=NewSheetName
wb.set_current()
#Copy All Range
AllRRow = len(Range('A1').vertical.value)
AllRCol = len(Range('A1').horizontal.value)
print AllRCol,AllRRow
RangeLimit = ('A1:B%d' % AllRRow)
TempData = Range(RangeLimit).value
wbTarget.set_current()
lastSheetVal = Sheet.count()
OriginSArr=[]
for SheetName in xrange(1,lastSheetVal+1):
OriginSArr.append(Sheet(SheetName).name)
if NewSheetName not in OriginSArr:
print "I should Add new array"
Sheet.add('abc',after=lastSheetVal)
Sheet('abc').name=NewSheetName
#Paste Data
Range(RangeLimit).value=TempData
#hypelink
for hyperRow in xrange(2,AllRRow):
RangeName=('B%d' % (hyperRow))
address=Range(RangeName).value
try:
Range(RangeName).add_hyperlink(address, text_to_display=address)
except:
pass
wbTarget.save()
def test_add_default(self):
# TODO: test call without args properly
Sheet.add()
def test_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
def test_add_default(self):
# TODO: test call without args properly
Sheet.add()
