Esempio n. 1
0
 def __init__(self, filename, sheetnumber, share1, month):
     self.wb = open_workbook(filename, formatting_info=True)
     # 0铁塔 1基站
     self.s = self.wb.sheets()[sheetnumber]
     self.share1 = Utils.col_by_name(share1)
     self.share2 = self.share1 + 1
     self.month = Utils.col_by_name(month)
     print "Sheet:", self.s.name
Esempio n. 2
0
 def __init__(self, filename):
     self.wb = open_workbook(filename, formatting_info=True)
     self.s = self.wb.sheets()[1]
     self.datecol = Utils.col_by_name('B')   #日期
     self.owner = Utils.col_by_name('M')     #主建方
     self.receiver = self.owner + 1          #共享方
     self.sharetype = Utils.col_by_name('O') #共享/共建
     self.TieTaType = Utils.col_by_name('I') #铁塔类型
     self.height    = Utils.col_by_name('J') #高度
     print 'Sheet:',self.s.name
Esempio n. 3
0
def write_total(sheet):
    count_ages = 20
    col = FIRST_COLUMN + 1
    row = FIRST_ROW
    count_do = get_institution(0).count()
    count_school = get_institution(1).count()
    count_row = get_institution().count() + 3

    for i, age in enumerate(get_list_age()):
        formula_do = 'SUM({}:{})'.format(
            Utils.rowcol_to_cell(row - 1 + i, col + 2),
            Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do)
        )
        formula_school = 'SUM({}:{})'.format(
            Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + 1),
            Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + count_school)
        )
        formula_total_row = '{} + {} + {}'.format(
            Utils.rowcol_to_cell(row - 1 + i, col + 1),
            Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 1),
            Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 2 + count_school),
        )
        sheet.write(col + i, row - 1, Formula(formula_total_row), style_bold)
        sheet.write(col + i, row, Formula(formula_do), style_bold)
        sheet.write(col + i, row + count_do + 1, Formula(formula_school), style_bold)

    for j in range(count_row + 1):
        formula_total_col = 'SUM({}:{})'.format(
            Utils.rowcol_to_cell(row - 1, col + j),
            Utils.rowcol_to_cell(row - 1 + count_ages - 1, col + j)
        )
        sheet.write(col + count_ages, row - 1 + j, Formula(formula_total_col), style_bold)
Esempio n. 4
0
def _get_range(A1range):
    (begin_col,begin_row,end_col,end_row,begin_row_abs,begin_col_abs,end_row_abs,end_col_abs) = (None,None,None,None,False,False,False,False)
    m = rx_range.match(A1range)
    if m:
        groups = m.groups()
        if groups[0]:
            (begin_row,begin_col,begin_row_abs,begin_col_abs) = Utils.cell_to_rowcol(groups[0])
        if groups[1]:
            (end_row,end_col,end_row_abs,end_col_abs) = Utils.cell_to_rowcol(groups[1])
    else:
        raise ValueError, "A1range mismatch (%s)" % A1range
    return (begin_col,begin_row,end_col,end_row,begin_row_abs,begin_col_abs,end_row_abs,end_col_abs)
Esempio n. 5
0
 def CountAv(self):
     numstyle = XFStyle()
     numstyle.num_format_str = "0.0000"
     if self.mode == 0:
         i = 0
         while i <= (self.finSize - self.startSize) / 100:
             formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
                 i, self.repeat + 1))
             self.res.write(i, 2 + self.repeat, xlwt.Formula(formula),
                            numstyle)
             i += 1
     elif self.mode == 2:
         formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
             0, self.repeat + 1))
         self.res.write(0, 2 + self.repeat, xlwt.Formula(formula), numstyle)
Esempio n. 6
0
	def add_plates_sheet(self,sheetname,cell_overwrite_ok=False):
		if not isinstance(sheetname, unicode_type):
			sheetname			= sheetname.decode(self.encoding)
		if not Utils.valid_sheet_name(sheetname):
			raise Exception("invalid worksheet name %r" % sheetname)
		lower_name = sheetname.lower()
		if lower_name in self._Workbook__worksheet_idx_from_name:
			raise Exception("duplicate worksheet name %r" % sheetname)
		self._Workbook__worksheet_idx_from_name[lower_name] = len(self._Workbook__worksheets)
		self._Workbook__worksheets.append(PlatesSheet(sheetname,self,cell_overwrite_ok))
		return self._Workbook__worksheets[-1]
Esempio n. 7
0
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
    ws_sit.row(str_row).height_mismatch = 1
    ws_sit.row(str_row).height = height
    cont_list = lines[csvlineno].split(',')
    ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
    for (i, content) in enumerate(cont_list[1:]):
        if content == 'Non':
            cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
            ws_sit.write(str_row, i + cross_col_no + 1,
                         Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
        else:
            ws_sit.write(str_row, i + cross_col_no + 1, content, style)
Esempio n. 8
0
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
    ws_sit.row(str_row).height_mismatch = 1
    ws_sit.row(str_row).height = height
    cont_list = lines[csvlineno].split(',')
    ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
    for (i, content) in enumerate(cont_list[1:]):
        if content == 'Non':
            cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
            ws_sit.write(str_row, i + cross_col_no + 1,
                         Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
        else:
            ws_sit.write(str_row, i + cross_col_no + 1, content, style)
Esempio n. 9
0
 def add_sheet(self, sheetname, cell_overwrite_ok=False):
     from xlwt import Worksheet, Utils
     if not isinstance(sheetname, str):
         sheetname = sheetname.decode(self.encoding)
     if not Utils.valid_sheet_name(sheetname):
         raise Exception("invalid worksheet name %r" % sheetname)
     lower_name = sheetname.lower()
     if lower_name in self.__worksheet_idx_from_name:
         raise Exception("duplicate worksheet name %r" % sheetname)
     self.__worksheet_idx_from_name[lower_name] = len(self.__worksheets)
     self.__worksheets.append(Worksheet(sheetname, self, cell_overwrite_ok))
     return self.__worksheets[-1]
from xlwt import Utils

print Utils.col_by_name('AA')
print Utils.col_by_name('A')

print Utils.cell_to_rowcol('A1')
print Utils.cell_to_rowcol('$A$1')

print Utils.cell_to_rowcol2('A1')

print Utils.rowcol_to_cell(0,0)
print Utils.rowcol_to_cell(0,0,False,True)
print Utils.rowcol_to_cell(
    row=0,col=0,row_abs=True,col_abs=True
    )

print Utils.cellrange_to_rowcol_pair('1:3')
print Utils.cellrange_to_rowcol_pair('B:G')
print Utils.cellrange_to_rowcol_pair('A2:B7')
print Utils.cellrange_to_rowcol_pair('A1')

print Utils.valid_sheet_name('')
print Utils.valid_sheet_name("'quoted'")
print Utils.valid_sheet_name("O'hare")
print Utils.valid_sheet_name("X"*32)
print Utils.valid_sheet_name("[]:\\?/*\x00")
Esempio n. 11
0
def tail_match(s,t):
    s = re.sub(u'_高铁$', '', s)
    t = re.sub(u'_高铁$', '', t)
    s = re.sub(u'居委会$','', s)
    t = re.sub(u'居委会$','', t)
    return s[-1] == t[-1] and s[-2] == t[-2]

#WCDMA BASIC DATABASE
print 'loading wdb'
wdb = open_workbook("wdb.xlsx").sheet_by_index(0)
wdb_names = []
last_name = ""
for i in range(1, wdb.nrows):
    new_name = wdb.cell(i,9).value
    lac = wdb.cell(i, Utils.col_by_name('G') ).value
    nodebid  = int( wdb.cell(i, Utils.col_by_name('BF') ).value )
    if new_name == last_name:
        continue
    new_record = {}
    new_record["line_number"] = i
    new_record["nodebid"] = nodebid
    new_record["orig_string"] = new_name
    new_record["lac"] = lac
    last_name = new_name
    new_name = re.sub(u'^泉州|\s','',new_name)
    new_name = re.sub(u'\$.*$','',new_name)
    new_name = re.sub(u'_BBU[0-9]?', '' , new_name)
    new_name = re.sub(u'_TEST', '', new_name)
    new_name = re.sub(u'_临时', '', new_name)
    new_name = re.sub(u'_[0-9]+$','',new_name)
Esempio n. 12
0
 def excel_table(self):
     items_info = self.items_info_all()
     excel_table = []
     temp_row = []
     data_start_row = 2
     data_start_col = 4
     # 表头
     for header_cn in HEADER_BEFORE_DATE:
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
     for m, m_len in items_info['months'].items():
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
         for i in range(0, m_len - 1):
             temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for header_cn in HEADER_AFTER_DATE:
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
     excel_table.append(temp_row)  # 第一行写完
     temp_row = []
     for i in range(0, len(HEADER_BEFORE_DATE)):
         temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for d in items_info['dates']:
         if d.isoweekday() in [6, 7]:
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
         else:
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
     for i in range(0, len(HEADER_AFTER_DATE)):
         temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     excel_table.append(temp_row)  # 第二行写完
     # 填表
     temp_row = []
     for v, sale_type_cn, sale_type_items in items_info['items']:
         if not len(sale_type_items):
             break
         if sale_type_cn == u"配送":
             item_type = StyleTypes.gift
             item_weekend_type = StyleTypes.gift_weekend
         else:
             item_type = StyleTypes.base
             item_weekend_type = StyleTypes.base_weekend
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
         index = 1
         for item in sale_type_items:
             if index != 1:
                 temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
             for i in range(0, len(items_info['dates'])):
                 d = items_info['dates'][i]
                 if d.isoweekday() in [6, 7]:
                     if item.schedule_by_date(d):
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
                     else:
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
                 else:
                     if item.schedule_by_date(d):
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
                     else:
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
             formula = 'SUM(%s:%s)' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, data_start_col),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 总预订量
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type))  # 刊例单价
             formula = '%s*%s' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, len(temp_row) - 2),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 刊例总价
             if sale_type_cn == u"配送":
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
             elif sale_type_cn == u"补量":
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
             else:
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
             formula = '%s*%s' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, len(temp_row) - 2),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 净价
             excel_table.append(temp_row)
             index += 1
             temp_row = []
     # totle
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for i in range(0, len(items_info['dates']) + 1):
         formula = 'SUM(%s:%s)' % (
             Utils.rowcol_to_cell(data_start_row, data_start_col + i),
             Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
     formula = 'SUM(%s:%s)' % (
         Utils.rowcol_to_cell(data_start_row, len(temp_row)),
         Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
     formula = 'SUM(%s:%s)' % (
         Utils.rowcol_to_cell(data_start_row, len(temp_row)),
         Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     excel_table.append(temp_row)
     return excel_table
Esempio n. 13
0
def prepare_each_day(wb,start_date,end_date):
    sheet = wb.add_sheet('일별 Feed 개수(기간 내)')
    sheet.write(0,0,'시작',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
    sheet.write(0,1,str(start_date))
    sheet.write(1,0,'종료',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
    sheet.write(1,1,str(end_date))
    
    
    header_style=easyxf( 'font: name Arial, bold True, height 220;' 'border: bottom thin;' 'pattern: pattern solid, fore_color light_green;')
    header_style2=easyxf( 'font: name Arial, bold True, height 220;' 'border: top thin;' 'pattern: pattern solid, fore_color light_green;')
    header_style3=easyxf( 'font: name Arial, bold True, height 220;' 'pattern: pattern solid, fore_color light_green;')
    sheet.write(3,0,'시간',header_style3)
    sheet.write(3,1,'사용자',header_style3)
    sheet.write(4,0,'날짜',header_style)
    user_profiles = UserProfile.objects.filter(is_deactivated=False)
    
    row=4
    col=0
    for user_profile in user_profiles:
        try:
            user = user_profile.user
            col+=1
            if col != 1:
                sheet.write(row-1,col,'',header_style3)
            sheet.write(row,col,user.last_name,header_style)
        except Exception as e:
            print str(e)
            pass
    
    col+=1
    sheet.write(row-1,col,'',header_style3)
    sheet.write(row,col,'합계',header_style)
    
    
    time = start_date
    
    row=4
    col=0
    while time < end_date:
        next_time = time + dt.timedelta(1) - dt.timedelta(seconds=1)
        col=0
        row+=1
        sheet.write(row,col,time.strftime('%Y.%m.%d'),easyxf('font: bold False, height 220;', num_format_str='YYYY.MM.DD'))
        row_total=0
        for user_profile in user_profiles:
            try:
                user = user_profile.user
                col+=1
                count = Message.objects.filter(is_deleted=False, reg_date__range=(time,next_time), author=user).count()
                row_total+=count
                if count != 0:
                    sheet.write(row,col,count,font_style)
            except Exception as e:
                print str(e)
                pass
        col+=1
        start = Utils.rowcol_to_cell(row,1)
        end = Utils.rowcol_to_cell(row,col-1)
        sheet.write(row,col,Formula("SUM("+start+":"+end+")"),font_style)
        time = time + dt.timedelta(1)
    
    row+=1
    col=0
    sheet.write(row,col,'합계',header_style2)
    for user_profile in user_profiles:
        try:
            col+=1
            start = Utils.rowcol_to_cell(5,col)
            end = Utils.rowcol_to_cell(row-1,col)
            count = Formula("SUM("+start+":"+end+")")
            sheet.write(row,col,count,header_style2)
        except Exception as e:
            print str(e)
            pass
    col+=1
    start = Utils.rowcol_to_cell(5,col)
    end = Utils.rowcol_to_cell(row-1,col)
    count = Formula("SUM("+start+":"+end+")")
    sheet.write(row,col,count,header_style2)
Esempio n. 14
0
from xlwt import Utils

print 'AA ->', Utils.col_by_name('AA')
print 'A ->', Utils.col_by_name('A')

print 'A1 ->', Utils.cell_to_rowcol('A1')
print '$A$1 ->', Utils.cell_to_rowcol('$A$1')

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,
Esempio n. 15
0
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)
Esempio n. 16
0
from xlwt import Utils

print 'AA ->',Utils.col_by_name('AA')
print 'A ->',Utils.col_by_name('A')

print 'A1 ->',Utils.cell_to_rowcol('A1')
print '$A$1 ->',Utils.cell_to_rowcol('$A$1')

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
          )
Esempio n. 17
0
from xlwt import Utils

print Utils.col_by_name('AA')
print Utils.col_by_name('A')

print Utils.cell_to_rowcol('A1')
print Utils.cell_to_rowcol('$A$1')

print Utils.cell_to_rowcol2('A1')

print Utils.rowcol_to_cell(0, 0)
print Utils.rowcol_to_cell(0, 0, False, True)
print Utils.rowcol_to_cell(row=0, col=0, row_abs=True, col_abs=True)

print Utils.cellrange_to_rowcol_pair('1:3')
print Utils.cellrange_to_rowcol_pair('B:G')
print Utils.cellrange_to_rowcol_pair('A2:B7')
print Utils.cellrange_to_rowcol_pair('A1')

print Utils.valid_sheet_name('')
print Utils.valid_sheet_name("'quoted'")
print Utils.valid_sheet_name("O'hare")
print Utils.valid_sheet_name("X" * 32)
print Utils.valid_sheet_name("[]:\\?/*\x00")
Esempio n. 18
0
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)
Esempio n. 19
0
# -*- coding: UTF-8 -*- 
#!/bin/python

import re 

from xlrd import open_workbook
from xlwt import Utils

#WCDMA xiaoqu DATABASE
print 'loading xiaoqu'
wdb_book = open_workbook("xiaoqu.xlsx")
wdb = wdb_book.sheet_by_index(0)
wdb_names = []
for i in range(1, wdb.nrows):
    new_id = wdb.cell(i, Utils.col_by_name('D') ).value
    new_record = {}
    new_record["line_number"] = i
    new_record["nodebid"] = int(new_id)
    wdb_names.append( new_record)
print `len(wdb_names)` + " xiaoqu"

#WCDMA automatch
print 'loading automatch'
wbbu_book = open_workbook("automatch.xls")
wbbu = wbbu_book.sheet_by_index(0)
wbbu_names = []
for i in range(1, wbbu.nrows):
    new_id = wbbu.cell(i, Utils.col_by_name('C') ).value
    new_record = {}
    new_record["line_number"] = i
    new_record["nodebid"] = int(new_id)
Esempio n. 20
0
 def excel_table(self):
     items_info = self.items_info_all()
     excel_table = []
     temp_row = []
     data_start_row = 2
     data_start_col = 4
     # 表头
     for header_cn in HEADER_BEFORE_DATE:
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
     for m, m_len in items_info['months'].items():
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
         for i in range(0, m_len - 1):
             temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for header_cn in HEADER_AFTER_DATE:
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
     excel_table.append(temp_row)  # 第一行写完
     temp_row = []
     for i in range(0, len(HEADER_BEFORE_DATE)):
         temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for d in items_info['dates']:
         if d.isoweekday() in [6, 7]:
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
         else:
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
     for i in range(0, len(HEADER_AFTER_DATE)):
         temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     excel_table.append(temp_row)  # 第二行写完
     # 填表
     temp_row = []
     for v, sale_type_cn, sale_type_items in items_info['items']:
         if not len(sale_type_items):
             break
         if sale_type_cn == u"配送":
             item_type = StyleTypes.gift
             item_weekend_type = StyleTypes.gift_weekend
         else:
             item_type = StyleTypes.base
             item_weekend_type = StyleTypes.base_weekend
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
         index = 1
         for item in sale_type_items:
             if index != 1:
                 temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
             for i in range(0, len(items_info['dates'])):
                 d = items_info['dates'][i]
                 if d.isoweekday() in [6, 7]:
                     if item.schedule_by_date(d):
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
                     else:
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
                 else:
                     if item.schedule_by_date(d):
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
                     else:
                         temp_row.append(
                             ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
             formula = 'SUM(%s:%s)' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, data_start_col),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 总预订量
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type))  # 刊例单价
             formula = '%s*%s' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, len(temp_row) - 2),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 刊例总价
             if sale_type_cn == u"配送":
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
             elif sale_type_cn == u"补量":
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
             else:
                 temp_row.append(
                     ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
             formula = '%s*%s' % (
                 Utils.rowcol_to_cell(
                     data_start_row + len(excel_table) - 2, len(temp_row) - 2),
                 Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
             temp_row.append(
                 ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type))  # 净价
             excel_table.append(temp_row)
             index += 1
             temp_row = []
     # totle
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
     for i in range(0, len(items_info['dates']) + 1):
         formula = 'SUM(%s:%s)' % (
             Utils.rowcol_to_cell(data_start_row, data_start_col + i),
             Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
         temp_row.append(
             ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
     formula = 'SUM(%s:%s)' % (
         Utils.rowcol_to_cell(data_start_row, len(temp_row)),
         Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
     formula = 'SUM(%s:%s)' % (
         Utils.rowcol_to_cell(data_start_row, len(temp_row)),
         Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
     temp_row.append(
         ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
     excel_table.append(temp_row)
     return excel_table