Example #1
0
    def write_xls_price(self):
        if self.options['whole']:
            filename = path.join(settings.MEDIA_ROOT, 'upload/wprice.xls')
        else:
            filename = path.join(settings.MEDIA_ROOT, 'upload/price.xls')
        workBookDocument = Workbook()
        docSheet = workBookDocument.add_sheet(u'Прайс соло-парфюм')
        docSheet.col(1).width = 10000
        headerFont = Font()
        headerFont.bold = True
        headerFont.size = 400
        headerStyle = XFStyle()
        headerStyle.font = headerFont
        docSheet.row(0).set_style(headerStyle)
        if self.options['whole']:
            docSheet.write_merge(0, 0, 0, 2, u'Оптовый прайс-лист Соло-парфюм (%s)' % datetime.now().strftime('%d.%m.%Y'))
        else:
            docSheet.write_merge(0, 0, 0, 2, u'Прайс-лист Соло-парфюм (%s)' % datetime.now().strftime('%d.%m.%Y'))

        docSheet.write(2, 0, u'Артикул')
        docSheet.write(2, 1, u'Наименование', )
        docSheet.write(2, 2, u'Цена')

        sectionFont = Font()
        sectionFont.bold = True
        sectionStyle = XFStyle()
        sectionStyle.font = sectionFont
        align = Alignment()
        align.horz = Alignment.HORZ_CENTER
        sectionStyle.alignment = align

        row = 3
        for section in self.data.iterkeys():
            docSheet.write_merge(row, row, 0, 2, section, sectionStyle)
            row += 1
            for item in self.data[section]:
                docSheet.write(row, 0, item['identifier'])
                docSheet.write(row, 1, item['name'])
                docSheet.write(row, 2, item['price'])
                row += 1

        workBookDocument.save(filename)
Example #2
0
def sandbox():
    #set attributes for workbook
    fnt = Font()
    fnt.name, fnt.colour_index, fnt.bold, fnt._weight = 'Arial',0x08,False,0x0190
    borders = Borders()
    borders.left, borders.right, borders.top, borders.bottom = 0,0,0,0
    al = Alignment()
    al.horz, al.vert = Alignment.HORZ_CENTER,Alignment.VERT_CENTER
    style = XFStyle()
    style.font, style.borders,style.alignment = fnt,borders,al
    # create wb instance
    wb = Workbook()
    #add a sheet for each csv file being imported to excel
    s = util.GetFileListing('Note*.csv')
    csv_files = s.sys_cmd.output
    worksheet_titles = [ ':'.join(a_file.split(':')[1:-2]) for a_file in csv_files ]
    ws_list = [ wb.add_sheet(ws_title) for ws_title in worksheet_titles ]
    F = util.FileEdit(csv_titles)
    for ws,f in zip(ws_list,F.file_utils):
        a_list = [ a_row.rstrip().split(',') for a_row in f.contents ]
        for a_row,a_line in enumerate(a_list):
            for a_col,a_cell in enumerate(a_line): ws.write(a_row,a_col,a_cell,style)

    wb.save('chk_out.xls')
Example #3
0
    def write_xls_price(self):
        if self.options['whole']:
            filename = path.join(settings.MEDIA_ROOT, 'upload/wprice.xls')
        else:
            filename = path.join(settings.MEDIA_ROOT, 'upload/price.xls')
        workBookDocument = Workbook()
        docSheet = workBookDocument.add_sheet(u'Прайс соло-парфюм')
        docSheet.col(1).width = 10000
        headerFont = Font()
        headerFont.bold = True
        headerFont.size = 400
        headerStyle = XFStyle()
        headerStyle.font = headerFont
        docSheet.row(0).set_style(headerStyle)
        if self.options['whole']:
            docSheet.write_merge(
                0, 0, 0, 2, u'Оптовый прайс-лист Соло-парфюм (%s)' %
                datetime.now().strftime('%d.%m.%Y'))
        else:
            docSheet.write_merge(
                0, 0, 0, 2, u'Прайс-лист Соло-парфюм (%s)' %
                datetime.now().strftime('%d.%m.%Y'))

        docSheet.write(2, 0, u'Артикул')
        docSheet.write(
            2,
            1,
            u'Наименование',
        )
        docSheet.write(2, 2, u'Цена')

        sectionFont = Font()
        sectionFont.bold = True
        sectionStyle = XFStyle()
        sectionStyle.font = sectionFont
        align = Alignment()
        align.horz = Alignment.HORZ_CENTER
        sectionStyle.alignment = align

        row = 3
        for section in self.data.iterkeys():
            docSheet.write_merge(row, row, 0, 2, section, sectionStyle)
            row += 1
            for item in self.data[section]:
                docSheet.write(row, 0, item['identifier'])
                docSheet.write(row, 1, item['name'])
                docSheet.write(row, 2, item['price'])
                row += 1

        workBookDocument.save(filename)
Example #4
0
def write_excel(results):
    title = ['DATE', 'User ID', 'SCREEN NAME', 'Fans', 'Fan Growth', 'Fan Growth %', 'Tweets', 'Retweets', 'Comments', 'direct @',
                      'Likes', 'Impression', 'ER 30 days', 'ER 7 days', '#1 Post URL', '#1 Post ER', '#1 Post RT', '#1 Post CT', '#2 Post URL',
                      '#2 Post ER', '#2 Post RT', '#2 Post CT', '#3 Post URL', '#3 Post ER', '#3 Post RT', '#3 Post CT', '#4 Post URL',
                      '#4 Post ER', '#4 Post RT', '#4 Post CT', '#5 Post URL', '#5 Post ER', '#5 Post RT', '#5 Post CT', '#1 Influencer URL',
                      '#1 Influencer Tweets Count', '#1 Influencer Comments Count', '#1 Influencer Direct @ Count', '#2 Influencer URL',
                      '#2 Influencer Tweets Count', '#2 Influencer Comments', '#2 Influencer Direct @ Count', '#3 Influencer URL',
                      '#3 Influencer Tweets Count', '#3 Influencer Comments', '#3 Influencer Direct @ Count', '#4 Influencer URL',
                      '#4 Influencer Tweets Count', '#4 Influencer Comments', '#4 Influencer Direct @ Count', '#5 Influencer URL',
                      '#5 Influencer Tweets Count', '#5 Influencer Comments', '#5 Influencer Direct @ Count', '#1 Hashtag',
                      "#1 Hashtag's engagement rate", '#2 Hashtag', "#2 Hashtag's engagement rate", '#3 Hashtag',
                      "#3 Hashtag's engagement rate", 'Question Posted', 'Question Responded', 'Question Response Time', 'Response Share']
    w = Workbook()
    ws = w.add_sheet('Weekly Raw Data')
    wt = w.add_sheet('Fans Info')

    font = Font()
    font.height = 12 * 0x14
    font.name = str_to_unicode('微软雅黑')
    title_style =  Style.XFStyle()
    title_style.font = font

    fontbold = Font()
    fontbold.height = 12 * 0x14
    fontbold.bold = True
    fontbold.name = str_to_unicode('微软雅黑')
    boldstyle =  Style.XFStyle()
    boldstyle.font = fontbold

    percent = Style.XFStyle()
    percent.font = font
    percent.num_format_str = '0.00%'

    for i in range(len(title)):
        ws.col(i).width = 3600
    ws.write_cols(0, 0, title, title_style)
    abscissa = 0
    for res in range(len(results)):
        screen_name, uid, day, period, fans, fan_growth, tweets, retweets, comments, direct_at, likes, impressions, \
                   er_30, er_7, response_share, top_posts, top_influencer, top_hashtag, questions, responds, mean_res, active, interactive, \
                   verified, subfans, province, gender, age, tag, fans_week, fans_hour, brand_week, brand_hour = results[res]
        fan_percent = fan_growth / float(fans - fan_growth)
        top_posts, top_influencer, top_hashtag, verified, subfans, province, gender, age, tag, fans_week, \
                    fans_hour, brand_week, brand_hour = [format_data(p) for p in (top_posts, top_influencer, top_hashtag, \
                                                                                       verified, subfans, province, gender, age, tag, fans_week, \
                                                                                       fans_hour, brand_week, brand_hour)]
        mean_res = round(mean_res/60.0, 2)
        #print res
        t_posts = []
        top_posts = sorted(top_posts, key=lambda x:x[0], reverse=True)
        for post in top_posts:
            t_posts.extend([post[1]['url'], post[0], post[1]['nret'], post[1]['ncmt']])
        t_posts.extend(['N/A'] * (20 - len(t_posts)))

        t_influencer = []
        top_influencer = sorted(top_influencer, key=lambda x:x[0], reverse=True)
        for influ in top_influencer:
            weibourl = 'http://weibo.com/u/%s'%influ[1]['uid']
            t_influencer.extend([weibourl, influ[1].get('reposts', 0), influ[1].get('comments', 0), influ[1].get('direct_at', 0)])
        t_influencer.extend(['N/A'] * (20 - len(t_influencer)))

        t_hashtag = []
        if top_hashtag:
            top_hashtag = sorted(top_hashtag, key=lambda x:x[0], reverse=True)
            [t_hashtag.extend([hash[1], hash[0]]) for hash in top_hashtag]
        t_hashtag.extend(['N/A'] * (6 - len(t_hashtag)))

        account_index = [day.strftime('%Y-%m-%d'), uid, screen_name, fans, fan_growth, fan_percent, tweets, retweets, \
                         comments, direct_at, likes if likes else 0, \
                         impressions, er_30, er_7] + t_posts + t_influencer + t_hashtag + [questions, responds, mean_res,
                                                                                           response_share if response_share else 'N/A']
        #将第一页数据写入execl
        ws.write_cols(res+1, 0, account_index, title_style)

        tag.extend([('N/A', 0) for i in range(10 - len(tag))])
        province.extend([('N/A', 0) for i in range(10 - len(province))])
        subfans.extend([0 for i in range(12 - len(subfans))])

        vertical_title = [screen_name, 'Active', 'Active Fans', 'Other Fans', '', 'Interaction', 'Interactive Fans', 'Other Fans', '', 'Verified Type', \
         'Verified', 'Daren', 'Un-verified', '', 'Fan Number', '0~9', '10~49', '50~99', '100~199', '200~299', '300~399', '400~499',\
         '500~999', '1000~1999', '2000~4999', '5000~9999', '>=10000', '', 'Gender', 'Male', 'Female', '', 'Age', '<18', '18~24', \
         '25~29', '30~34', '35~39', '40~49', '50~59', '>=60', '', 'Tag'] + [i[0] for i in tag] + ['', 'Province'] + \
         [str_to_unicode(provincesdict.get(str(i[0]), '')) for i in province]+  ['', 'Hour', '0-1', '1-2', '2-3', '3-4', '4-5', '5-6',\
         '6-7', '7-8', '8-9', '9-10', '10-11', '11-12', '12-13', '13-14', '14-15', '15-16', '16-17', '17-18', '18-19', '19-20', '20-21', '21-22', \
         '22-23', '23-0', '', 'Days', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', '']
        wt.col(res*3).width = 4000
        ordinate = 0
        for ver in range(len(vertical_title)):
            if ver in (0, 1) or not vertical_title[ver-1]:
                wt.write(ordinate, abscissa, vertical_title[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, vertical_title[ver], title_style)
            ordinate += 1
        if verified:
            verified_count = float(sum(verified))
            verified_type = [verified[1]/verified_count, verified[2]/verified_count, verified[0]/verified_count] if verified_count > 0 else [0, 0, 0]
        else:
            verified_type = [0] * 3

        if len(gender)!=2: gender = [0, 0]
        else:
            gender.sort()
            gender = [gender[1][1], gender[0][1]]

        tagdata = [i[1] for i in tag]
        provincedata = [i[1] for i in province]
        if age:
            age = [i[1] for i in sorted(age.items())]
            age_type = [i/float(sum(age)) for i in age]
        else:
            age_type = [0] * 8

        if not brand_hour : brand_hour = [0] * 24
        if not brand_week : brand_week = [0] * 7
        subfans_type, gender_type, tag_type, province_type, fanshour_type, fansweek_type, brandhour_type, brandweek_type = \
               [div_data(j) for j in [subfans, gender, tagdata, provincedata, fans_hour, fans_week, brand_hour, brand_week]]

        gaps = ['', 'Percentage']
        vertical_data = ['', 'Percentage', active, 1-active if active else 0, '', 'Percentage', interactive, 1-interactive if interactive else 0, \
                         '', 'Percentage'] + verified_type + gaps + subfans_type + gaps + gender_type + gaps + age_type + gaps + \
                      tag_type + gaps + province_type + ['', 'Fans Activity'] + fanshour_type + ['', 'Fans Activity'] + fansweek_type

        wt.col(res*3+1).width = 4000
        ordinate = 0
        abscissa += 1
        for ver in range(len(vertical_data)):
            if ver in (0, 1) or not vertical_title[ver-1]:
                wt.write(ordinate, abscissa, vertical_data[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, vertical_data[ver], percent)
            ordinate += 1

        wt.col(res*3+2).width = 4000
        ordinate = 0
        rest_data = [''] * 66 + ['Brand Activity'] + brandhour_type + ['', 'Brand Activity'] + brandweek_type
        abscissa += 1
        for ver in range(len(rest_data)):
            if ver in (0, 1) or not vertical_title[ver-1]:
                wt.write(ordinate, abscissa, rest_data[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, rest_data[ver], percent)
            ordinate += 1
        abscissa += 2
    return w
Example #5
0
    def exportXlsData(self):
        """get XLS file for all folder objects"""
        filename = datetime.now().strftime('ict_%Y%m%d%H%M%S.xls')
        f_handle, f_name = tempfile.mkstemp(filename)
        wbook = Workbook()
        wb_hosts = wbook.add_sheet('ddd')
        style0 = XFStyle()
        font0 = Font()
        font0.height = 6 * 20
        style0.num_format_str = '@'
        style0.font = font0
        style1 = XFStyle()
        font1 = Font()
        font1.height = 6 * 20
        style1.num_format_str = '@'
        style1.font = font1
        heading_pattern = xl.Pattern()
        heading_pattern.pattern = xl.Pattern.SOLID_PATTERN
        heading_pattern.pattern_back_colour = 0x5
        heading_pattern.pattern_fore_colour = 0x5
        fields = self.fields
        attrList = [fname for fname, fval in fields.items()]
        itemList = self.context.items()
        pos_y = 0
        pos_x = 0
        for attr in attrList:
            wb_data = Formula(u'"%s"' % attr)
            style0.pattern = heading_pattern
            wb_hosts.write(pos_y, pos_x, wb_data, style0)
            pos_x += 1
        # IntID
        wb_data = Formula(u'"IntID"')
        wb_hosts.write(pos_y, pos_x, wb_data, style0)
        pos_x += 1
        # objectID
        wb_data = Formula(u'"objectID"')
        wb_hosts.write(pos_y, pos_x, wb_data, style0)
        wb_hosts.col(pos_x).width *= 3
        pos_x += 1
        pos_y = 1
        #
        allAttributes = {}
        for interface in implementedBy(self.factory):
            for i_attrName in interface:
                i_attr = interface[i_attrName]
                if IField.providedBy(i_attr):
                    allAttributes[i_attrName] = i_attr
        #
        for item_n, item_v in itemList:
            pos_x = 0
            for attr in attrList:
                #                from zope.interface import implementedBy
                #                ff=self.factory
                #                tt=[i for i in implementedBy(ff)]
                #                it=tt[-1]
                #attrField = self.attrInterface[attr]
                attrField = allAttributes[attr]
                #                tmpFieldProperty = getattr(self.factory, attr)
                #                if hasattr(tmpFieldProperty, '_FieldProperty__field'):
                #                    attrField = getattr(self.factory, attr)._FieldProperty__field
                attrDm = datamanager.AttributeField(item_v, attrField)
                v_style = XFStyle()
                v_font = Font()
                v_font.height = 6 * 20
                v_style.font = v_font
                value = None
                if IChoice.providedBy(attrField):
                    v_style.num_format_str = '@'
                    dateValue = attrDm.get()
                    v_widget = getMultiAdapter(\
                                    (attrField,self.request),
                                    interfaces.IFieldWidget)
                    v_widget.context = item_v
                    #                    dm = zope.component.getMultiAdapter(
                    #                        (self.content, field.field), interfaces.IDataManager)
                    #                    zope.component.getMultiAdapter(
                    #                        (self.context,
                    #                         self.request,
                    #                         self.form,
                    #                         getattr(widget, 'field', None),
                    #                         widget),
                    #                        interfaces.IValidator).validate(fvalue)
                    #                    dm = zope.component.getMultiAdapter(
                    #                        (self.__context__, field), interfaces.IDataManager)
                    v_dataconverter = queryMultiAdapter(\
                                    (attrDm.field, v_widget),
                                    interfaces.IDataConverter)
                    #print u"ddd55: %s: %s" % (attr, dateValue)
                    if dateValue is not None:
                        value = v_dataconverter.toWidgetValue(dateValue)[0]
                        #print "value3->   %s: %s " % (attr, value)


#                elif ICollection.providedBy(attrField):
#                    v_style.num_format_str = '@'
#                    value = getattr(item_v, attr)
#                    print "ddd66: %s: %s" % (attr, value)
#                elif IBool.providedBy(attrField):
#                    v_style.num_format_str = '@'
#                    value = getattr(item_v, attr)
#                    print "value2->   %s: %s " % (attr, value)
                else:
                    v_style.num_format_str = '@'
                    dateValue = attrDm.get()
                    v_widget = getMultiAdapter(\
                                    (attrField,self.request),
                                    interfaces.IFieldWidget)
                    v_widget.context = item_v
                    v_dataconverter = queryMultiAdapter(\
                                    (attrDm.field, v_widget),
                                    interfaces.IDataConverter)
                    #d2 = queryMultiAdapter((attrDm.field, v_widget),interfaces.IDataConverter)
                    if dateValue is not None:
                        value = v_dataconverter.toWidgetValue(dateValue)
                    if type(value) is list:
                        value = u";".join(value)
                    #print u"value1->   %s: %s " % (attr, value)
                if value is not None:
                    #print u"wb_hosts.write(%s, %s, %s, %s)" % (pos_y, pos_x, value, v_style)
                    wb_hosts.write(pos_y, pos_x, value, v_style)
                pos_x += 1
            # IntID
            uidutil = queryUtility(IIntIds)
            wb_data = Formula(u'"%s"' % uidutil.getId(item_v))
            wb_hosts.write(pos_y, pos_x, wb_data, style0)
            pos_x += 1
            # objectID
            wb_data = Formula(u'"%s"' % item_v.objectID)
            wb_hosts.write(pos_y, pos_x, wb_data, style0)
            pos_x += 1
            pos_y += 1
        wbook.save(f_name)
        self.request.response.setHeader('Content-Type',
                                        'application/vnd.ms-excel')
        self.request.response.setHeader(\
            'Content-Disposition',
            'attachment; filename=\"%s\"' % filename)
        setNoCacheHeaders(self.request.response)
        datafile = open(f_name, "r")
        dataMem = datafile.read()
        datafile.close()
        os.remove(f_name)
        return dataMem
Example #6
0
def write_excel(results):
    title = [
        'DATE', 'User ID', 'SCREEN NAME', 'Fans', 'Fan Growth', 'Fan Growth %',
        'Tweets', 'Retweets', 'Comments', 'direct @', 'Likes', 'Impression',
        'ER 30 days', 'ER 7 days', '#1 Post URL', '#1 Post ER', '#1 Post RT',
        '#1 Post CT', '#2 Post URL', '#2 Post ER', '#2 Post RT', '#2 Post CT',
        '#3 Post URL', '#3 Post ER', '#3 Post RT', '#3 Post CT', '#4 Post URL',
        '#4 Post ER', '#4 Post RT', '#4 Post CT', '#5 Post URL', '#5 Post ER',
        '#5 Post RT', '#5 Post CT', '#1 Influencer URL',
        '#1 Influencer Tweets Count', '#1 Influencer Comments Count',
        '#1 Influencer Direct @ Count', '#2 Influencer URL',
        '#2 Influencer Tweets Count', '#2 Influencer Comments',
        '#2 Influencer Direct @ Count', '#3 Influencer URL',
        '#3 Influencer Tweets Count', '#3 Influencer Comments',
        '#3 Influencer Direct @ Count', '#4 Influencer URL',
        '#4 Influencer Tweets Count', '#4 Influencer Comments',
        '#4 Influencer Direct @ Count', '#5 Influencer URL',
        '#5 Influencer Tweets Count', '#5 Influencer Comments',
        '#5 Influencer Direct @ Count', '#1 Hashtag',
        "#1 Hashtag's engagement rate", '#2 Hashtag',
        "#2 Hashtag's engagement rate", '#3 Hashtag',
        "#3 Hashtag's engagement rate", 'Question Posted',
        'Question Responded', 'Question Response Time', 'Response Share'
    ]
    w = Workbook()
    ws = w.add_sheet('Weekly Raw Data')
    wt = w.add_sheet('Fans Info')

    font = Font()
    font.height = 12 * 0x14
    font.name = str_to_unicode('微软雅黑')
    title_style = Style.XFStyle()
    title_style.font = font

    fontbold = Font()
    fontbold.height = 12 * 0x14
    fontbold.bold = True
    fontbold.name = str_to_unicode('微软雅黑')
    boldstyle = Style.XFStyle()
    boldstyle.font = fontbold

    percent = Style.XFStyle()
    percent.font = font
    percent.num_format_str = '0.00%'

    for i in range(len(title)):
        ws.col(i).width = 3600
    ws.write_cols(0, 0, title, title_style)
    abscissa = 0
    for res in range(len(results)):
        screen_name, uid, day, period, fans, fan_growth, tweets, retweets, comments, direct_at, likes, impressions, \
                   er_30, er_7, response_share, top_posts, top_influencer, top_hashtag, questions, responds, mean_res, active, interactive, \
                   verified, subfans, province, gender, age, tag, fans_week, fans_hour, brand_week, brand_hour = results[res]
        fan_percent = fan_growth / float(fans - fan_growth)
        top_posts, top_influencer, top_hashtag, verified, subfans, province, gender, age, tag, fans_week, \
                    fans_hour, brand_week, brand_hour = [format_data(p) for p in (top_posts, top_influencer, top_hashtag, \
                                                                                       verified, subfans, province, gender, age, tag, fans_week, \
                                                                                       fans_hour, brand_week, brand_hour)]
        mean_res = round(mean_res / 60.0, 2)
        #print res
        t_posts = []
        top_posts = sorted(top_posts, key=lambda x: x[0], reverse=True)
        for post in top_posts:
            t_posts.extend(
                [post[1]['url'], post[0], post[1]['nret'], post[1]['ncmt']])
        t_posts.extend(['N/A'] * (20 - len(t_posts)))

        t_influencer = []
        top_influencer = sorted(top_influencer,
                                key=lambda x: x[0],
                                reverse=True)
        for influ in top_influencer:
            weibourl = 'http://weibo.com/u/%s' % influ[1]['uid']
            t_influencer.extend([
                weibourl, influ[1].get('reposts', 0),
                influ[1].get('comments', 0), influ[1].get('direct_at', 0)
            ])
        t_influencer.extend(['N/A'] * (20 - len(t_influencer)))

        t_hashtag = []
        if top_hashtag:
            top_hashtag = sorted(top_hashtag, key=lambda x: x[0], reverse=True)
            [t_hashtag.extend([hash[1], hash[0]]) for hash in top_hashtag]
        t_hashtag.extend(['N/A'] * (6 - len(t_hashtag)))

        account_index = [day.strftime('%Y-%m-%d'), uid, screen_name, fans, fan_growth, fan_percent, tweets, retweets, \
                         comments, direct_at, likes if likes else 0, \
                         impressions, er_30, er_7] + t_posts + t_influencer + t_hashtag + [questions, responds, mean_res,
                                                                                           response_share if response_share else 'N/A']
        #将第一页数据写入execl
        ws.write_cols(res + 1, 0, account_index, title_style)

        tag.extend([('N/A', 0) for i in range(10 - len(tag))])
        province.extend([('N/A', 0) for i in range(10 - len(province))])
        subfans.extend([0 for i in range(12 - len(subfans))])

        vertical_title = [screen_name, 'Active', 'Active Fans', 'Other Fans', '', 'Interaction', 'Interactive Fans', 'Other Fans', '', 'Verified Type', \
         'Verified', 'Daren', 'Un-verified', '', 'Fan Number', '0~9', '10~49', '50~99', '100~199', '200~299', '300~399', '400~499',\
         '500~999', '1000~1999', '2000~4999', '5000~9999', '>=10000', '', 'Gender', 'Male', 'Female', '', 'Age', '<18', '18~24', \
         '25~29', '30~34', '35~39', '40~49', '50~59', '>=60', '', 'Tag'] + [i[0] for i in tag] + ['', 'Province'] + \
         [str_to_unicode(provincesdict.get(str(i[0]), '')) for i in province]+  ['', 'Hour', '0-1', '1-2', '2-3', '3-4', '4-5', '5-6',\
         '6-7', '7-8', '8-9', '9-10', '10-11', '11-12', '12-13', '13-14', '14-15', '15-16', '16-17', '17-18', '18-19', '19-20', '20-21', '21-22', \
         '22-23', '23-0', '', 'Days', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', '']
        wt.col(res * 3).width = 4000
        ordinate = 0
        for ver in range(len(vertical_title)):
            if ver in (0, 1) or not vertical_title[ver - 1]:
                wt.write(ordinate, abscissa, vertical_title[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, vertical_title[ver], title_style)
            ordinate += 1
        if verified:
            verified_count = float(sum(verified))
            verified_type = [
                verified[1] / verified_count, verified[2] /
                verified_count, verified[0] / verified_count
            ] if verified_count > 0 else [0, 0, 0]
        else:
            verified_type = [0] * 3

        if len(gender) != 2: gender = [0, 0]
        else:
            gender.sort()
            gender = [gender[1][1], gender[0][1]]

        tagdata = [i[1] for i in tag]
        provincedata = [i[1] for i in province]
        if age:
            age = [i[1] for i in sorted(age.items())]
            age_type = [i / float(sum(age)) for i in age]
        else:
            age_type = [0] * 8

        if not brand_hour: brand_hour = [0] * 24
        if not brand_week: brand_week = [0] * 7
        subfans_type, gender_type, tag_type, province_type, fanshour_type, fansweek_type, brandhour_type, brandweek_type = \
               [div_data(j) for j in [subfans, gender, tagdata, provincedata, fans_hour, fans_week, brand_hour, brand_week]]

        gaps = ['', 'Percentage']
        vertical_data = ['', 'Percentage', active, 1-active if active else 0, '', 'Percentage', interactive, 1-interactive if interactive else 0, \
                         '', 'Percentage'] + verified_type + gaps + subfans_type + gaps + gender_type + gaps + age_type + gaps + \
                      tag_type + gaps + province_type + ['', 'Fans Activity'] + fanshour_type + ['', 'Fans Activity'] + fansweek_type

        wt.col(res * 3 + 1).width = 4000
        ordinate = 0
        abscissa += 1
        for ver in range(len(vertical_data)):
            if ver in (0, 1) or not vertical_title[ver - 1]:
                wt.write(ordinate, abscissa, vertical_data[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, vertical_data[ver], percent)
            ordinate += 1

        wt.col(res * 3 + 2).width = 4000
        ordinate = 0
        rest_data = [''] * 66 + ['Brand Activity'] + brandhour_type + [
            '', 'Brand Activity'
        ] + brandweek_type
        abscissa += 1
        for ver in range(len(rest_data)):
            if ver in (0, 1) or not vertical_title[ver - 1]:
                wt.write(ordinate, abscissa, rest_data[ver], boldstyle)
            else:
                wt.write(ordinate, abscissa, rest_data[ver], percent)
            ordinate += 1
        abscissa += 2
    return w
Example #7
0
    def setup_courses_worksheet(self,keys_and_labels=None):
        h = []
        h.append(('course_number','Course no.',3000))
        h.append(('offered_this_year','Offered this year',3000))
        h.append(('course_subject','Course subject',6000))
        h.append(('course_title','Course title',6000))
        h.append(('instructor','Instructor',3000))
        h.append(('other_instructors','Other Instructors',4000))
        h.append(('format','Course format',3000))
        h.append(('term','Term offered',3000))
        h.append(('open_to','Student cohort',3000))
        h.append(('day_and_time','Day and time',3000))
        h.append(('credit','Credit',3000))
        h.append(('compulsory','Compulsory',3000))
        h.append(('room','Room',3000))
        h.append(('start_date','Start date',4000))
        h.append(('course_outline','Course outline',18000))
        h.append(('course_objective','Course objective',9000))
        h.append(('textbooks','Textbooks',6000))
        h.append(('references','Reference materials',6000))
        h.append(('evaluation','Evaluation methods',6000))
        h.append(('prerequisites','Prerequisites',5000))
        h.append(('notes','Notes',5000))
        h.append(('ss_code','SS Code',3000))
        h.append(('year_offered','Year offered',3000))
        h.append(('sessions','Sessions',6000))
        
        self.set_field_codes(h)

        # Set column widths
        for key in self.field_codes.keys():
            pos = self.field_codes[key][1]
            width = self.field_codes[key][2]
            self.ws.col(pos).width = width
            
        # Line wrapping
        align = Alignment()
        align.wrap = Alignment.WRAP_AT_RIGHT
        align.vert = Alignment.VERT_TOP
        
        # A boldface font
        hfont = Font()
        hfont.bold = True
        hfont.colour_index = 141

        # Heading style
        self.hstyle = XFStyle()
        self.hstyle.num_format_str = '@'
        self.hstyle.alignment = align
        self.hstyle.font = hfont
        
        # Write in heading
        for key in self.field_codes.keys():
            mycol = self.field_codes[key][1]
            mydata = self.field_codes[key][0]
            self.ws.write(self.row,mycol,mydata,self.hstyle)
        self.row = self.row + 1
            
        # Code style
        self.cstyle = XFStyle()
        self.cstyle.num_format_str = '@'
        self.hstyle.alignment = align
        
        # Write in codes
        for key in self.field_codes.keys():
            mycol = self.field_codes[key][1]
            self.ws.write(self.row,mycol,key,self.cstyle)
        self.row = self.row + 1

        # Data cell style
        self.rstyle = XFStyle()
        self.rstyle.num_format_str = '@'
        self.rstyle.alignment = align
Example #8
0
    def setup_timetable_worksheet(self,title,base_offsets,maxcells):
        h1 = []
        h2 = []
        h1.append(title)
        h2.extend(['Monday','Tuesday','Wednesday','Thursday','Friday'])
        labels = ['1st period','2nd period','3rd period','4th period','5th period']
        
        times = ['(8:45 - 10:15)','(10:30 - 12:00)','(13:00 - 14:30)','(14:45 - 16:15)','(16:30 - 18:00)']

        h1Style = XFStyle()
        
        font = Font()
        font.height = 300
        font.bold = True
        
        align = Alignment()
        align.horz = Alignment.HORZ_CENTER
        
        h1Style.font = font
        h1Style.alignment = align
        
        h2Style = XFStyle()
        align = Alignment()
        align.rota = 30
        align.horz = Alignment.HORZ_CENTER
        align.vert = Alignment.VERT_CENTER
        h2Style.alignment = align

        borders = Borders()
        borders.bottom = Borders.THIN
        h2Style.borders = borders        
        
        labelStyle = XFStyle()
        borders = Borders()
        borders.bottom = Borders.THIN
        borders.top = Borders.THIN
        borders.left = Borders.THIN
        borders.right = Borders.THIN
        labelStyle.borders = borders
        
        align = Alignment()
        align.rota = 90
        align.horz = Alignment.HORZ_CENTER
        align.vert = Alignment.VERT_CENTER
        labelStyle.alignment = align
            


        self.ws.write_merge(0,0,0,5,title,h1Style)
        
        for pos in range (0,5,1):
            self.ws.write(1,pos+1,h2[pos],h2Style)
        self.ws.write(1,0,'',h2Style)
        for pos in range(0,5,1):
            base_offset = base_offsets[pos]
            offset = maxcells[pos]

            for r in range(2+base_offset,2+base_offset+offset,1):
                row = self.ws.row(r)
                row.height = 5000/offset
                #row.has_default_height = False
            
            text = '%s\n%s' % (labels[pos],times[pos])
            print "base_offset: %d" % (base_offset,)
            print "offset: %d" % (offset,)
            self.ws.write_merge(2+base_offset,2+base_offset+offset-1,0,1,text,labelStyle)
            #self.ws.write_merge(2+base_offset,2+base_offset+offset,0,0,text,labelStyle)

        self.ws.col(0).width = 1200
        for pos in range(1,6,1):    
            self.ws.col(pos).width = 5500

        #self.ws.row(10).has_default_height = False
        self.ws.row(10).height = 400
Example #9
0
    def setup_instructors_worksheet(self,keys_and_labels):
        h = []
        h.append(('uid','UID',2500))
        h.append(('status','Status',2500))
        h.append(('profession','Profession',2500))
        h.append(('family_name','Family name',2500))
        h.append(('given_name','Given name',2500))
        h.append(('proper_name','Proper name',2500))
        h.append(('birthdate','Birthdate (未公開)'.decode('utf8'),3000))
        h.append(('field','Field',3000))
        h.append(('affiliation','Affiliation',2500))
        h.append(('phone','Phone',2500))
        h.append(('office','Office',2500))
        h.append(('office_hours','Office hours',6000))
        h.append(('email','email (未公開)'.decode('utf8'),4000))
        h.append(('recommended_readings','Recommended readings',6000))
        h.append(('preparation_suggestions','Preparation suggestions for prospective applicants',6000))
        h.append(('website','Website',3000))
        h.append(('degrees','Degrees',6000))
        h.append(('research_interests','Research interests',6000))
        h.append(('memberships','Memberships',6000))
        h.append(('Jmemberships','所属学会\n(日本語サイトより)'.decode('utf8'),6000))
        h.append(('publications','Publications',12000))
        h.append(('Jpublications','主要著作\n(日本語サイトより)'.decode('utf8'),12000))
        h.append(('subjects','Subject areas',6000))
        h.append(('career_history','Career history',9000))
        h.append(('Jcareer_history','略歴\n(日本語サイトより)'.decode('utf8'),9000))
        h.append(('visitorships','Visitorships',6000))

        # Jesus, what a mess
        for key,label in keys_and_labels:
            width = 3000
            for x in h:
                if x[0] == key:
                    width = x[2]
            self.set_field_codes((key,label,width))
        
        # Set column widths
        for key in self.field_codes.keys():
            pos = self.field_codes[key][1]
            width = self.field_codes[key][2]
            self.ws.col(pos).width = width
            
        # Line wrapping
        align = Alignment()
        align.wrap = Alignment.WRAP_AT_RIGHT
        align.vert = Alignment.VERT_TOP
        
        # A boldface font
        hfont = Font()
        hfont.bold = True
        hfont.colour_index = 140
        hpat = Pattern()
        hpat.pattern_fore_colour = 140
        hpat.pattern = Pattern.SOLID_PATTERN

        # Heading style
        self.hstyle = XFStyle()
        self.hstyle.num_format_str = '@'
        self.hstyle.alignment = align
        self.hstyle.font = hfont
        self.hstyle.pattern = hpat
        
        # Write in heading
        for key in self.field_codes.keys():
            mycol = self.field_codes[key][1]
            mydata = self.field_codes[key][0]
            self.ws.write(self.row,mycol,mydata,self.hstyle)
        self.row = self.row + 1
            
        # Code style
        self.cstyle = XFStyle()
        self.cstyle.num_format_str = '@'
        self.hstyle.alignment = align
        
        # Write in codes
        for key in self.field_codes.keys():
            mycol = self.field_codes[key][1]
            self.ws.write(self.row,mycol,key,self.cstyle)
        self.row = self.row + 1

        # Data cell style
        self.rstyle = XFStyle()
        self.rstyle.num_format_str = '@'
        self.rstyle.alignment = align
Example #10
0
    def exportXlsData(self, request, sheetName=u'ict', wbook=None):
        """get XLS file for all folder objects"""
        if wbook is None:
            localWbook = True
            filename = datetime.now().strftime('ict_%Y%m%d%H%M%S.xls')
            f_handle, f_name = tempfile.mkstemp(filename)
            wbook = Workbook()
        else:
            localWbook = False
        wb_hosts = wbook.add_sheet(getattr(self, 'ikName', sheetName))
        style0 = XFStyle()
        font0 = Font()
        font0.height = 6 * 20
        #style0.num_format_str = '@'
        style0.font = font0
        style1 = XFStyle()
        font1 = Font()
        font1.height = 6 * 20
        #style1.num_format_str = '@'
        style1.font = font1
        heading_pattern = xl.Pattern()
        heading_pattern.pattern = xl.Pattern.SOLID_PATTERN
        heading_pattern.pattern_back_colour = 0x5
        heading_pattern.pattern_fore_colour = 0x5
        fields = fieldsForFactory(self.contentFactory, ['objectID'])
        attrList = [fname for fname, fval in fields.items()]
        itemList = self.items()
        pos_y = 0
        pos_x = 0
        for attr in attrList:
            style0.pattern = heading_pattern
            wb_hosts.write(pos_y, pos_x, attr, style0)
            pos_x += 1
        # objectID
        wb_hosts.write(pos_y, pos_x, "objectID", style0)
        wb_hosts.col(pos_x).width *= 3
        pos_x += 1
        pos_y = 1
        #
        allAttributes = {}
        for interface in implementedBy(self.contentFactory):
            for i_attrName in interface:
                i_attr = interface[i_attrName]
                if IField.providedBy(i_attr):
                    allAttributes[i_attrName] = i_attr
        #
        for item_n, item_v in itemList:
            pos_x = 0
            for attr in attrList:
                attrField = allAttributes[attr]
                attrDm = datamanager.AttributeField(item_v, attrField)
                v_style = XFStyle()
                v_font = Font()
                v_font.height = 6 * 20
                v_style.font = v_font
                value = None
                if IChoice.providedBy(attrField):
                    v_style.num_format_str = '@'
                    #                    try:
                    dateValue = attrDm.get()
                    v_widget = getMultiAdapter(\
                                    (attrField,request),
                                    interfaces.IFieldWidget)
                    v_widget.context = item_v
                    v_dataconverter = queryMultiAdapter(\
                                    (attrDm.field, v_widget),
                                    interfaces.IDataConverter)
                    #                    except AttributeError, errText:
                    #                        print "Error2:  ### ", errText
                    #                        print "item_v: ", item_v
                    #                        print "item_v.ikName: ", item_v.ikName
                    #                        print "attr: ", attr
                    #                        dateValue = None
                    #                        #import pdb
                    #                        #pdb.set_trace()
                    if dateValue is not None:
                        #                        try:
                        valueVector = v_dataconverter.toWidgetValue(dateValue)
                        if len(valueVector) > 0:
                            value = valueVector[0]
#                        except IndexError, errText:
#                            print "Error3:  ### ", errText
#                            print "item_v: ", item_v
#                            print "item_v.ikName: ", item_v.ikName
#                            print "attr: ", attr
                else:
                    v_style.num_format_str = '@'
                    try:
                        #                        import pdb
                        #                        pdb.set_trace()
                        dateValue = attrDm.get()
                        v_widget = getMultiAdapter(\
                                        (attrField,request),
                                        interfaces.IFieldWidget)
                        v_widget.context = item_v
                        v_dataconverter = queryMultiAdapter(\
                                        (attrDm.field, v_widget),
                                        interfaces.IDataConverter)
                    except AttributeError, errText:
                        print "Error1:  ### ", errText
                        print "item_v: ", item_v
                        print "item_v.ikName: ", item_v.ikName
                        print "attr: ", attr
                        dateValue = None
                    if dateValue is not None:
                        try:
                            value = v_dataconverter.toWidgetValue(dateValue)
                        except AttributeError, errText:
                            print "Error4:  ### ", errText
                            print "item_v: ", item_v
                            print "item_v.ikName: ", item_v.ikName
                            print "attr: ", attr
                            value = None
                    if type(value) is list:
                        value = u";".join(value)
                if value is not None:
                    wb_hosts.write(pos_y, pos_x, value, v_style)
                pos_x += 1