print (0,0),'->',Utils.rowcol_to_cell(0,0) print (0,0,False,True),'->', print Utils.rowcol_to_cell(0,0,False,True) print (0,0,True,True),'->', print Utils.rowcol_to_cell( row=0,col=0,row_abs=True,col_abs=True ) print '1:3 ->',Utils.cellrange_to_rowcol_pair('1:3') print 'B:G ->',Utils.cellrange_to_rowcol_pair('B:G') print 'A2:B7 ->',Utils.cellrange_to_rowcol_pair('A2:B7') print 'A1 ->',Utils.cellrange_to_rowcol_pair('A1') print (0,0,100,100),'->', print Utils.rowcol_pair_to_cellrange(0,0,100,100) print (0,0,100,100,True,False,False,False),'->', print Utils.rowcol_pair_to_cellrange( row1=0,col1=0,row2=100,col2=100, row1_abs=True,col1_abs=False, row2_abs=False,col2_abs=True ) for name in ( '',"'quoted'","O'hare","X"*32,"[]:\\?/*\x00" ): print 'Is %r a valid sheet name?' % name, if Utils.valid_sheet_name(name): print "Yes" else: print "No"
def export_events(events, out_file, user=None, encoding='utf-8'): book = Workbook(encoding=encoding) sheet1 = book.add_sheet('Events') style_head = easyxf('font: bold true; borders: bottom thin') style_italic = easyxf('font: italic true') style_bold = easyxf('font: bold true') sheet1.write(0,0,'Event', style_head) #sheet1.col(0).width = 256 * 10 #doesn't work #sheet1.col(0).width = 0x0d00 + 15 # doesn't work sheet1.write(0,1,'Date', style_head) sheet1.write(0,2,'Days', style_head) sheet1.write(0,3,'Hours', style_head) sheet1.write(0,4,'Description', style_head) row = 1 days_spent = defaultdict(float) hours_spent = defaultdict(float) for event in events: sheet1.write(row, 0, event['title']) sheet1.write(row, 1, event['start'], easyxf( num_format_str='YYYY-MM-DD' )) #sheet1.row(row).write(1, event['start']) #sheet1.row(row).set_cell_date(1, event['start']) if event['all_day']: days = event['end'] - event['start'] sheet1.write(row, 2, days.days + 1) sheet1.write(row, 3, '') for tag in event['tags']: days_spent[tag] += days.days + 1 if not event['tags']: days_spent[''] += days.days + 1 else: sheet1.write(row, 2, '') hours = (event['end'] - event['start']).seconds / 3600.0 sheet1.write(row, 3, round(hours,1)) for tag in event['tags']: hours_spent[tag] += hours if not event['tags']: hours_spent[''] += hours sheet1.write(row, 4, event['description']) row += 1 if row > 1: style_bold = easyxf('font: bold true') days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=2, row2=row - 1, col2=2) sheet1.write(row, 2, Formula('SUM(%s)' % days_cell_range), style_bold) days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=3, row2=row - 1, col2=3) sheet1.write(row, 3, Formula('SUM(%s)' % days_cell_range), style_bold) ## Now write the summations by tags if '' in days_spent: days_spent['Untagged'] = days_spent.pop('') days_spent = days_spent.items() days_spent.sort(lambda x,y: cmp(y[1], x[1])) if '' in hours_spent: hours_spent['Untagged'] = hours_spent.pop('') hours_spent = hours_spent.items() hours_spent.sort(lambda x,y: cmp(y[1], x[1])) sheet2 = book.add_sheet('Report') sheet2.write(0,0,'Days', style_head) row = 1 if days_spent: row += 1 sheet2.write(row,0, 'Tag', style_bold) sheet2.write(row,1, 'Days', style_bold) total = 0.0 for tag, days in days_spent: row += 1 if tag == 'Untagged': sheet2.write(row, 0, tag, style_italic) else: sheet2.write(row, 0, tag) sheet2.write(row, 1, days) total += days row += 1 sheet2.write(row, 0, 'Total', style_bold) sheet2.write(row, 1, total, style_bold) row += 1 if hours_spent: row += 1 sheet2.write(row,0, 'Tag', style_bold) sheet2.write(row,1, 'Hours', style_bold) total = 0.0 for tag, hours in hours_spent: row += 1 if tag == 'Untagged': sheet2.write(row, 0, tag, style_italic) else: sheet2.write(row, 0, tag) sheet2.write(row, 1, hours) total += hours row += 1 sheet2.write(row, 0, 'Total', style_bold) sheet2.write(row, 1, total, style_bold) row += 1 book.save(out_file)
print 'A1 ->', Utils.cell_to_rowcol2('A1') print(0, 0), '->', Utils.rowcol_to_cell(0, 0) print(0, 0, False, True), '->', print Utils.rowcol_to_cell(0, 0, False, True) print(0, 0, True, True), '->', print Utils.rowcol_to_cell(row=0, col=0, row_abs=True, col_abs=True) print '1:3 ->', Utils.cellrange_to_rowcol_pair('1:3') print 'B:G ->', Utils.cellrange_to_rowcol_pair('B:G') print 'A2:B7 ->', Utils.cellrange_to_rowcol_pair('A2:B7') print 'A1 ->', Utils.cellrange_to_rowcol_pair('A1') print(0, 0, 100, 100), '->', print Utils.rowcol_pair_to_cellrange(0, 0, 100, 100) print(0, 0, 100, 100, True, False, False, False), '->', print Utils.rowcol_pair_to_cellrange(row1=0, col1=0, row2=100, col2=100, row1_abs=True, col1_abs=False, row2_abs=False, col2_abs=True) for name in ('', "'quoted'", "O'hare", "X" * 32, "[]:\\?/*\x00"): print 'Is %r a valid sheet name?' % name, if Utils.valid_sheet_name(name): print "Yes" else:
def export_events(events, out_file, user=None, encoding='utf-8'): book = Workbook(encoding=encoding) sheet1 = book.add_sheet('Events') style_head = easyxf('font: bold true; borders: bottom thin') style_italic = easyxf('font: italic true') style_bold = easyxf('font: bold true') sheet1.write(0, 0, 'Event', style_head) #sheet1.col(0).width = 256 * 10 #doesn't work #sheet1.col(0).width = 0x0d00 + 15 # doesn't work sheet1.write(0, 1, 'Date', style_head) sheet1.write(0, 2, 'Days', style_head) sheet1.write(0, 3, 'Hours', style_head) sheet1.write(0, 4, 'Description', style_head) row = 1 days_spent = defaultdict(float) hours_spent = defaultdict(float) for event in events: sheet1.write(row, 0, event['title']) sheet1.write(row, 1, event['start'], easyxf(num_format_str='YYYY-MM-DD')) #sheet1.row(row).write(1, event['start']) #sheet1.row(row).set_cell_date(1, event['start']) if event['all_day']: days = event['end'] - event['start'] sheet1.write(row, 2, days.days + 1) sheet1.write(row, 3, '') for tag in event['tags']: days_spent[tag] += days.days + 1 if not event['tags']: days_spent[''] += days.days + 1 else: sheet1.write(row, 2, '') hours = (event['end'] - event['start']).seconds / 3600.0 sheet1.write(row, 3, round(hours, 1)) for tag in event['tags']: hours_spent[tag] += hours if not event['tags']: hours_spent[''] += hours sheet1.write(row, 4, event['description']) row += 1 if row > 1: style_bold = easyxf('font: bold true') days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=2, row2=row - 1, col2=2) sheet1.write(row, 2, Formula('SUM(%s)' % days_cell_range), style_bold) days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=3, row2=row - 1, col2=3) sheet1.write(row, 3, Formula('SUM(%s)' % days_cell_range), style_bold) ## Now write the summations by tags if '' in days_spent: days_spent['Untagged'] = days_spent.pop('') days_spent = days_spent.items() days_spent.sort(lambda x, y: cmp(y[1], x[1])) if '' in hours_spent: hours_spent['Untagged'] = hours_spent.pop('') hours_spent = hours_spent.items() hours_spent.sort(lambda x, y: cmp(y[1], x[1])) sheet2 = book.add_sheet('Report') sheet2.write(0, 0, 'Days', style_head) row = 1 if days_spent: row += 1 sheet2.write(row, 0, 'Tag', style_bold) sheet2.write(row, 1, 'Days', style_bold) total = 0.0 for tag, days in days_spent: row += 1 if tag == 'Untagged': sheet2.write(row, 0, tag, style_italic) else: sheet2.write(row, 0, tag) sheet2.write(row, 1, days) total += days row += 1 sheet2.write(row, 0, 'Total', style_bold) sheet2.write(row, 1, total, style_bold) row += 1 if hours_spent: row += 1 sheet2.write(row, 0, 'Tag', style_bold) sheet2.write(row, 1, 'Hours', style_bold) total = 0.0 for tag, hours in hours_spent: row += 1 if tag == 'Untagged': sheet2.write(row, 0, tag, style_italic) else: sheet2.write(row, 0, tag) sheet2.write(row, 1, hours) total += hours row += 1 sheet2.write(row, 0, 'Total', style_bold) sheet2.write(row, 1, total, style_bold) row += 1 book.save(out_file)