Beispiel #1
0
    def get_default_style():
        """get the default style.
        Args:
        Example:
        Return:
        Author: cai, yong
        IsInterface: False
        ChangeInfo: cai, yong 2019-08-26
        """

        style = XFStyle()

        return style
Beispiel #2
0
def genAttendanceSheet(lis):
    """ lis is UserRecord list """
    sheet2 = wb.add_sheet(ATTENDANCE_SHEET_NAME, True)
    percentStyle = XFStyle()
    percentStyle.num_format_str = '0%'
    integerStyle = XFStyle()
    integerStyle.num_format_str = '0'
    titles = ('姓名', '总工作日(天)', '总工时(小时)', '个人总出勤(小时)', '个人日均(小时)', '个人出勤率')
    row = 0
    for i, item in enumerate(titles):
        sheet2.write(row, i, item)
    row += 1
    workdays = workdayIntVar.get()
    workhours = workdays * 8
    lis.sort(key=attrgetter('totalTime'), reverse=True)
    for u in lis:
        hours = round(u.totalTime / 3600.0)
        sheet2.write(row, 0, u.userName)
        sheet2.write(row, 1, workdays)
        sheet2.write(row, 2, workhours, integerStyle)
        sheet2.write(row, 3, hours, integerStyle)
        sheet2.write(row, 4, hours / workdays, integerStyle)
        sheet2.write(row, 5, round(hours / workhours, 2), percentStyle)
        row += 1
Beispiel #3
0
    def _get_header_styles(self):
        s1 = XFStyle()
        al = Alignment()
        al.horz = Alignment.HORZ_CENTER
        s1.alignment = al

        s2 = XFStyle()
        borders = Borders()
        borders.bottom = 2
        s2.borders = borders

        return s1, s2
Beispiel #4
0
# -*- coding: utf-8 -*-
from xlwt.Formatting import Borders, Font, Alignment, Pattern
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook

wb = Workbook('utf-8')
sheet = wb.add_sheet('测试报告')
title = ['编号', '姓名', '职业', '上级', '入职日期']

title_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN

font = Font()
font.bold = True

alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER

pattern = Pattern()
pattern.pattern = pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x35

title_style.borders = border
title_style.font = font
title_style.alignment = alignment
title_style.pattern = pattern
Beispiel #5
0
    def style_rd2wt(self, rdbook, rdxf):

        wtxf = XFStyle()
        #
        # number format
        #
        wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
        #
        # font
        #
        wtf = wtxf.font
        rdf = rdbook.font_list[rdxf.font_index]
        wtf.height = rdf.height
        wtf.italic = rdf.italic
        wtf.struck_out = rdf.struck_out
        wtf.outline = rdf.outline
        wtf.shadow = rdf.outline
        wtf.colour_index = rdf.colour_index
        wtf.bold = rdf.bold  #### This attribute is redundant, should be driven by weight
        wtf._weight = rdf.weight  #### Why "private"?
        wtf.escapement = rdf.escapement
        wtf.underline = rdf.underline_type  ####
        # wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
        wtf.family = rdf.family
        wtf.charset = rdf.character_set
        wtf.name = rdf.name
        #
        # protection
        #
        wtp = wtxf.protection
        rdp = rdxf.protection
        wtp.cell_locked = rdp.cell_locked
        wtp.formula_hidden = rdp.formula_hidden
        #
        # border(s) (rename ????)
        #
        wtb = wtxf.borders
        rdb = rdxf.border
        #            wtb.left   = rdb.left_line_style
        #            wtb.right  = rdb.right_line_style
        #            wtb.top    = rdb.top_line_style
        #            wtb.bottom = rdb.bottom_line_style
        wtb.left = 1
        wtb.right = 1
        wtb.top = 1
        wtb.bottom = 1
        wtb.diag = rdb.diag_line_style
        wtb.left_colour = rdb.left_colour_index
        wtb.right_colour = rdb.right_colour_index
        wtb.top_colour = rdb.top_colour_index
        wtb.bottom_colour = rdb.bottom_colour_index
        wtb.diag_colour = rdb.diag_colour_index
        wtb.need_diag1 = rdb.diag_down
        wtb.need_diag2 = rdb.diag_up
        #
        # background / pattern (rename???)
        #
        wtpat = wtxf.pattern
        rdbg = rdxf.background
        wtpat.pattern = rdbg.fill_pattern
        wtpat.pattern_fore_colour = rdbg.pattern_colour_index
        wtpat.pattern_back_colour = rdbg.background_colour_index
        #
        # alignment
        #
        wta = wtxf.alignment
        rda = rdxf.alignment
        wta.horz = rda.hor_align
        wta.vert = rda.vert_align
        wta.dire = rda.text_direction
        # wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
        wta.rota = rda.rotation
        wta.wrap = rda.text_wrapped
        wta.shri = rda.shrink_to_fit
        wta.inde = rda.indent_level
        # wta.merg = ????
        #
        return wtxf
Beispiel #6
0
# -*- coding: utf-8 -*-

from requests.api import request
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Formatting import Borders
from xlwt.Style import XFStyle


wb = open_workbook(r'd:\emp.xls',formatting_info=True)
sheet = wb.sheet_by_name('手机号码归属地查询')
row_value = sheet.row_values(1,1)
print(row_value)

content_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN

content_style.borders = border

url = row_value[1]
params = row_value[3]
response = request(method=row_value[2],url=url, params=params)
print(response.status_code)
print(response.text)
if response.status_code==row_value[5]:
    #{"resultcode":"101","reason":"错误的请求KEY","result":null,"error_code":10001}写入对应的单元格
    new_wb = copy(wb)
Beispiel #7
0
    def add_ideogram_table(self, analyses,
                           widths=None,
#                           configure_table=True,
                           add_title=False, add_header=False, tablenum=1, **kw):

        sh = self._wb.add_sheet(analyses[0].labnumber)
#                                'sheet{:02n}'.format(self._sheet_count))
        self._sheet_count += 1

        cols = ['Status', 'N', 'Power', 'Moles_40Ar',
                'Ar40', 'Ar40Er',
                'Ar39', 'Ar39Er',
                'Ar38', 'Ar38Er',
                'Ar37', 'Ar37Er',
                'Ar36', 'Ar36Er',
                'Blank',
                'Ar40', 'Ar40Er',
                'Ar39', 'Ar39Er',
                'Ar38', 'Ar38Er',
                'Ar37', 'Ar37Er',
                'Ar36', 'Ar36Er'
                ]
        # write header row
        hrow = 0
        borders = Borders()
        borders.bottom = 2
        style = XFStyle()
        style.borders = borders
        for i, ci in enumerate(cols):
            sh.write(hrow, i, ci, style=style)

        numstyle = XFStyle()
        numstyle.num_format_str = '0.00'
        from operator import attrgetter
        def getnominal_value(attr):
            def get(x):
                v = getattr(x, attr)
                return float(v.nominal_value)
            return get

        def getstd_dev(attr):
            def get(x):
                v = getattr(x, attr)
                return float(v.std_dev)
            return get

        def getfloat(attr):
            def get(x):
                v = getattr(x, attr)
                return float(v)

            return get

        def null_value():
            return lambda x: ''
        attrs = [(attrgetter('step'),),
                 (getfloat('extract_value'),),
                 (null_value(),),
                 (getnominal_value('Ar40'),),
                 (getstd_dev('Ar40'),),
                 (getnominal_value('Ar39'),),
                 (getstd_dev('Ar39'),),
                 (getnominal_value('Ar38'),),
                 (getstd_dev('Ar38'),),
                 (getnominal_value('Ar37'),),
                 (getstd_dev('Ar37'),),
                 (getnominal_value('Ar36'),),
                 (getstd_dev('Ar36'),),
                 (null_value(),),
                 (getnominal_value('Ar40_blank'),),
                 (getstd_dev('Ar40_blank'),),
                 (getnominal_value('Ar39_blank'),),
                 (getstd_dev('Ar39_blank'),),
                 (getnominal_value('Ar38_blank'),),
                 (getstd_dev('Ar38_blank'),),
                 (getnominal_value('Ar37_blank'),),
                 (getstd_dev('Ar37_blank'),),
                 (getnominal_value('Ar36_blank'),),
                 (getstd_dev('Ar36_blank'),),

                 ]

        for i, ai in enumerate(analyses):
            for j, arg in enumerate(attrs):
                if len(arg) == 2:
                    func, style = arg
                else:
                    func, style = arg[0], Style.default_style
                sh.write(i + hrow + 1, 1 + j, func(ai),
                         style
                         )
Beispiel #8
0
    for row in csv.reader(f, dialect='excel', quoting=csv.QUOTE_NONNUMERIC):
        sheet.append(row)
wb.save(output_file)
print(".csv convert to .xls")

#获取xls数据
data = xlrd.open_workbook(output_file)
sheet1 = data.sheets()[0]

#新建excel并导入数据
new_excel = xlwt.Workbook(encoding='utf-8')
new_sheet = new_excel.add_sheet("test")

#设置列宽
for i in range(13):
    style = XFStyle()
    style.alignment
    new_sheet.col(i).width = 256 * 16

#获取所需列数据
c = 0
for col in range(5, 39, 3):
    cols = sheet1.col_values(col)
    for i in range(sheet1.nrows):  #
        if 0 == i:
            continue
        new_sheet.write(i, c, (cols[i] / 1024 / 1024 / 1024 * 100))
    c += 1
new_excel.save(output_file)

#求和,填充数据
Beispiel #9
0
def genTotalSheet(lis):
    """ lis is UserRecord list """
    global wb
    wb = Workbook(encoding='gbk')

    timeStyle = XFStyle()
    timeStyle.num_format_str = 'h:mm'

    lateStyle = XFStyle()
    lateStyle.num_format_str = 'h:mm'
    lateStyle.font = Font()
    ### when isLate = false, set colour_index = 0 will still get red font in Excel.
    lateStyle.font.colour_index = 2

    earlyStyle = XFStyle()
    earlyStyle.num_format_str = 'h:mm'
    earlyStyle.font = Font()
    earlyStyle.font.colour_index = 7

    sheet1 = wb.add_sheet(TOTAL_SHEET_NAME)
    rowNum = 0
    for i, s in enumerate(totalSheetTitles):  # write header
        sheet1.write(0, i, str(s))
    rowNum += 1

    for a in lis:
        for colNum, colName in enumerate(totalSheetTitles):
            if(colNum == 0):
                sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userId)
            elif(colNum == 1):
                sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userName)
            else:
                onTime = ''
                offTime = ''
                if(colName in a.recDict):
                    times = a.recDict[colName]
                    if(len(times) >= 2):
                        if(len(times) > 2):
                            print '! ' + a.userName + ' ' + monthStr + str(colName) + ' 两条以上打卡记录.'
                        onTime = times[0]
                        offTime = times[len(times) - 1]
                    elif(len(times) == 1):
                        onlyTime = strToTime(times[0])
                        if(onlyTime.hour < divider):
                            onTime = times[0]
                        else:
                            offTime = times[0]

                onTime = strToTime(onTime)
                offTime = strToTime(offTime)

#                if(onTime and offTime):
#                    a.totalTime += (offTime - onTime).seconds / 3600
                isLate, isEarly = checkTime(onTime, offTime, colName)
                if(isLate):  # late
#                    a.late += 1
                    sheet1.write(rowNum, colNum, onTime, lateStyle)
                else:
                    sheet1.write(rowNum, colNum, onTime, timeStyle)

                if(isEarly):  # early
#                    a.early += 1
                    sheet1.write(rowNum + 1, colNum, offTime, earlyStyle)
                else:
                    sheet1.write(rowNum + 1, colNum, offTime, timeStyle)

        rowNum += 2

    getTargetFileName()

    try:
        wb.save(targetFileName)
        addLog('"' + targetFileName + '" is in the same directory of the .txt file.')
    except IOError:  #IOError: [Errno 13] Permission denied: '***.xls'
        addLog('please close ' + targetFileName + ' and retry')
Beispiel #10
0
    def conv_xf_xfstyle(self, xf):
        wtxf = XFStyle()

        wtf = wtxf.font
        rdf = self.rbook.font_list[xf.font_index]
        wtf.height = rdf.height
        wtf.italic = rdf.italic
        wtf.struck_out = rdf.struck_out
        wtf.outline = rdf.outline
        wtf.shadow = rdf.outline
        wtf.colour_index = rdf.colour_index
        wtf.bold = rdf.bold  #### This attribute is redundant, should be driven by weight
        wtf._weight = rdf.weight  #### Why "private"?
        wtf.escapement = rdf.escapement
        wtf.underline = rdf.underline_type  ####
        # wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
        wtf.family = rdf.family
        wtf.charset = rdf.character_set
        wtf.name = rdf.name
        #
        # protection
        #
        wtp = wtxf.protection
        rdp = xf.protection
        wtp.cell_locked = rdp.cell_locked
        wtp.formula_hidden = rdp.formula_hidden
        #
        # border(s) (rename ????)
        #
        wtb = wtxf.borders
        rdb = xf.border
        wtb.left = rdb.left_line_style
        wtb.right = rdb.right_line_style
        wtb.top = rdb.top_line_style
        wtb.bottom = rdb.bottom_line_style
        wtb.diag = rdb.diag_line_style
        wtb.left_colour = rdb.left_colour_index
        wtb.right_colour = rdb.right_colour_index
        wtb.top_colour = rdb.top_colour_index
        wtb.bottom_colour = rdb.bottom_colour_index
        wtb.diag_colour = rdb.diag_colour_index
        wtb.need_diag1 = rdb.diag_down
        wtb.need_diag2 = rdb.diag_up
        #
        # background / pattern (rename???)
        #
        wtpat = wtxf.pattern
        rdbg = xf.background
        wtpat.pattern = rdbg.fill_pattern
        wtpat.pattern_fore_colour = rdbg.pattern_colour_index
        wtpat.pattern_back_colour = rdbg.background_colour_index

        wta = wtxf.alignment
        rda = xf.alignment
        wta.horz = rda.hor_align

        wta.vert = rda.vert_align

        wta.dire = rda.text_direction
        wta.rota = rda.rotation
        wta.wrap = rda.text_wrapped
        wta.shri = rda.shrink_to_fit
        wta.inde = rda.indent_level

        return wtxf
Beispiel #11
0
# -*-coding:UTF-8 -*-
from xlwt.Workbook import Workbook
from xlwt.Style import XFStyle
from importlib.resources import contents

Workbook

wb=Workbook('utf-8')
sheet=wb.add_sheet('测试')

title = ['编号', '姓名', '职业', '上级', '入职日期']

title_style = XFStyle()

for i in range(len(title)):
    sheet.write(4,i + 4,title[i],title_style)
    
content = [7369,'SMITH','CLERK',7902,12/17/1980]  ,[7499,'ALLEN','SALESMAN',7698,2/20/1981] 
for i in range(len(content)):
    for j in range(len(content[i])):
Beispiel #12
0
# -*-coding:UTF-8 -*-
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Formatting import Borders
from xlwt.Style import XFStyle


yuanshi_excel=open_workbook(r'd:\自动化测试.xls',formatting_info=True)
new_excel=copy(yuanshi_excel)
sheet=new_excel.get_sheet(0)
new_content=XFStyle()
border=Borders()
border.left=border.THIN
border.right=border.THIN
border.top=border.THIN
border.bottom=border.THIN

new_content.borders=border

sheet.write(3,5,5,new_content)
sheet.write(3,8,'mingyue',new_content)
new_excel.save(r'd:\新表副本.xls')


Beispiel #13
0
    def create(self, params, filename=None):
        """
        IN
          params    <dict>
          filename  <str> (opcional)
          
        OUT
          El XML del informe
          <str>
        """
        
        util = self.Util()
        self.tmpl.globals['util'] = util
        self.tmpl.globals['xlrd'] = xlrd

        informe_xml = self.tmpl.render(**params).encode('utf-8')
        
        root = etree.fromstring(informe_xml)
        
        wb = Workbook()
        
        estilos = {}
        
        xfs0 = XFStyle()
        
        n_sheet = 0
        for sheet in root.iter('sheet'):
            
            n_sheet += 1
            cols_width = None
            
            # title
            title = sheet.attrib.get('title', 'Sheet-%d' % n_sheet)
            
            # auto-width
            sheet_width = sheet.attrib.get('width')
            cols_width = {}
                
            ws = wb.add_sheet(title, True)
            
            util.init_count()
            for item in sheet:
                
                # style
                if item.tag == 'style':
                    estilos[item.attrib['name']] = easyxf(';'.join(self.calcular_estilo(item)))
                    
                # image
                if item.tag == 'image':
                    bm_path = item.find('path').text
                    col = int(item.attrib.get('col', 0))
                    scale_x = float(item.attrib.get('scale_x', '1'))
                    scale_y = float(item.attrib.get('scale_y', '1'))
                    ws.insert_bitmap(bm_path, util.current_line(), col, scale_x=scale_x, scale_y=scale_y) 
                
                # cell
                if item.tag == 'cell':
                    
                    col = int(item.attrib['col'])
                    
                    num_format = None
                    
                    # width
                    width = item.attrib.get('width', sheet_width)
                        
                    if width is not None:
                        if not cols_width.get(col):
                            cols_width[col] = dict(auto=width == 'auto', 
                                                   width=int(width) if width != 'auto' else 0)
                        
                    # value
                    value = item.find('value')
                    dato = value.text
                    # type: aplicar una conversión (str, int, float, ...)
                    type_ = item.attrib.get('type')
                    if type_:
                        tipo = type_.split(';')
                        
                        # date
                        if tipo[0] == 'date':
                            try:
                                dato = strtodate(dato, fmt='%Y-%m-%d')
                            except:
                                dato = None
    
                        # time
                        elif tipo[0] == 'time':
                            try:
                                dato = strtotime(dato)
                            except:
                                dato = None
                        
                        # formula
                        elif tipo[0] == 'formula':
                            dato = Formula(dato)
                        
                        # resto de tipos
                        else:
                            if dato != '':
                                try:
                                    f = lambda t,v: t(v)
                                    dato = f(eval(tipo[0]), dato)
                                except:
                                    dato = None
                        
                        # date;dd/mm/yyyy
                        # date;dd/mmm/yyyy
                        # date;NN D MMMM YYYY
                        # time;hh:mm
                        # float;#,##0.00
                        # float;+#,##0.00
                        # float;#,##0.00;[RED]-#,##0.00
                        if len(tipo) > 1:
                            num_format = ';'.join(tipo[1:])
                            
                        #print dato, tipo, num_format
    
                    # style: aplicar un estilo
                    estilo = item.find('style')
                    xfs = xfs0
                    if estilo != None:
                        if not estilos.has_key(estilo.attrib['name']):
                            xfs = easyxf(';'.join(self.calcular_estilo(estilo))) 
        
                            estilos[estilo.attrib['name']] = xfs
                            
                        else:
                            xfs = estilos[estilo.attrib['name']]
                        
                    xfs.num_format_str = 'General'    
                    if num_format:
                        if not xfs:
                            xfs = XFStyle()

                        xfs.num_format_str = num_format
                        
                    # merge
                    if item.attrib.has_key('merge') and item.attrib['merge']:
                        try:
                            _merge = re.search(r'(\d+)\s+(\d+)', item.attrib['merge'], re.U)
                            if _merge:
                                hg = int(_merge.group(1))
                                wd = int(_merge.group(2))
                            
                                _current_line = util.current_line()
                                ws.write_merge(_current_line, _current_line + hg - 1,
                                               col, col + wd -1, 
                                               dato, xfs)
                            else:
                                raise Exception(u'merge: Invalid format')
                        
                        except Exception:
                            ws.write(util.current_line(), col, dato, xfs)
                            
                    else:
                        ws.write(util.current_line(), col, dato, xfs)
                        
                    # width
                    colw = cols_width.get(col, None)
                    if colw is not None:
                        
                        if colw['auto']:
                            try:
                                width_ = len(str(dato))
                            except:
                                width_ = len(unicode(dato))
                                
                            if width_ > colw['width']:
                                cols_width[col]['width'] = width_
                                
                elif item.tag == 'line_feed':
                    n = int(item.attrib.get('n', 1))
                    util.line_feed(n)
                    
                elif item.tag == 'bookmark':
                    util.add_bookmark(item.attrib['name'])
            
            # width
            if cols_width is not None:
                for col, colw in cols_width.iteritems():
                    
                    if colw['width'] < 10:
                        colw['width'] = colw['width'] + 2
                        
                    ws.col(col).width = colw['width']*256
                
        if filename:
            wb.save(filename)
            
        return informe_xml
Beispiel #14
0
    def create(self, params, filename=None):
        """
        IN
          params    <dict>
          filename  <str> (opcional)
          
        OUT
          El XML del informe
          <str>
        """
        
        util = self.Util()
        self.tmpl.globals['util'] = util
        self.tmpl.globals['xlrd'] = xlrd

        informe_xml = self.tmpl.render(**params).encode('utf-8')
        
#        f = file('/home/leon/informe.xml', 'wb')
#        f.write(informe_xml)
#        f.close()
        
        root = etree.fromstring(informe_xml)
        
        wb = Workbook()
        
        estilos = {}
        
        xfs0 = XFStyle()
        
        n_sheet = 0
        for sheet in root.iter('sheet'):
            
            n_sheet += 1
            
            # title
            title = 'Sheet-%d' % n_sheet
            if sheet.attrib.has_key('title') and sheet.attrib['title']:
                title = sheet.attrib['title']
                
            ws = wb.add_sheet(title, True)
            
            util.init_count()
            for item in sheet:
                
                # style
                if item.tag == 'style':
                    estilos[item.attrib['name']] = easyxf(';'.join(self.calcular_estilo(item)))
                    
                # image
                if item.tag == 'image':
                    bm_path = item.find('path').text
                    col = int(item.attrib.get('col', 0))
                    scale_x = float(item.attrib.get('scale_x', '1'))
                    scale_y = float(item.attrib.get('scale_y', '1'))
                    ws.insert_bitmap(bm_path, util.current_line(), col, scale_x=scale_x, scale_y=scale_y) 
                
                # cell
                if item.tag == 'cell':
                    
                    col = int(item.attrib['col'])
                    
                    num_format = None
                    
                    value = item.find('value')
                    dato = value.text
                    # type: aplicar una conversión (str, int, float, ...)
                    if item.attrib.has_key('type') and item.attrib['type']:
                        tipo = item.attrib['type'].split(';')
                        
                        # date
                        if tipo[0] == 'date':
                            try:
                                dato = strtodate(dato, fmt='%Y-%m-%d')
                            except:
                                dato = None
    
                        # time
                        elif tipo[0] == 'time':
                            try:
                                dato = strtotime(dato)
                            except:
                                dato = None
                        
                        # formula
                        elif tipo[0] == 'formula':
                            dato = Formula(dato)
                        
                        # resto de tipos
                        else:
                            if dato:
                                try:
                                    f = lambda t,v: t(v)
                                    dato = f(eval(tipo[0]), dato)
                                except:
                                    dato = 'ERROR! (%s)' % dato
                        
                        # date;dd/mm/yyyy
                        # date;dd/mmm/yyyy
                        # date;NN D MMMM YYYY
                        # time;hh:mm
                        # float;#,##0.00
                        # float;+#,##0.00
                        # float;#,##0.00;[RED]-#,##0.00
                        if len(tipo) > 1:
                            num_format = ';'.join(tipo[1:])
                            
                        #print dato, tipo, num_format
    
                    # style: aplicar un estilo
                    estilo = item.find('style')
                    xfs = xfs0
                    if estilo != None:
                        if not estilos.has_key(estilo.attrib['name']):
                            xfs = easyxf(';'.join(self.calcular_estilo(estilo))) 
        
                            estilos[estilo.attrib['name']] = xfs
                            
                        else:
                            xfs = estilos[estilo.attrib['name']]
                        
                    xfs.num_format_str = 'General'    
                    if num_format:
                        if not xfs:
                            xfs = XFStyle()

                        xfs.num_format_str = num_format
                        
                    # merge
                    if item.attrib.has_key('merge') and item.attrib['merge']:
                        try:
                            _merge = re.search(r'(\d+)\s+(\d+)', item.attrib['merge'], re.U)
                            if _merge:
                                hg = int(_merge.group(1))
                                wd = int(_merge.group(2))
                            
                                _current_line = util.current_line()
                                ws.write_merge(_current_line, _current_line + hg - 1,
                                               col, col + wd -1, 
                                               dato, xfs)
                            else:
                                raise Exception(u'merge: Invalid format')
                        
                        except Exception:
                            ws.write(util.current_line(), col, dato, xfs)
                            
                    else:
                        ws.write(util.current_line(), col, dato, xfs)
                
                elif item.tag == 'line_feed':
                    n = int(item.attrib.get('n', 1))
                    util.line_feed(n)
                    
                elif item.tag == 'bookmark':
                    util.add_bookmark(item.attrib['name'])
                
        if filename:
            wb.save(filename)
        
        return informe_xml
Beispiel #15
0
def template_download(request):
    """
    导出模板数据到excel
    """
    pk = request.GET.get('pk', '')
    business_name = request.GET.get('business_name', '')
    template_name = request.GET.get('template_name', '')
    flag = request.GET.get('flag', '')
    res_json = {'result': True, 'isNotPass': True, 'data': u""}
    if pk or flag == Flag.EDIT or flag == Flag.TASK or flag == 'False':
        business = BUSINESS_MODEL['template']
        cc_name = business_name
    elif flag == Flag.BUSINESS:
        business = BUSINESS_MODEL['instance']
        cc_name = business_name.split('_')[0]
    else:
        res_json['data'] = u'参数错误'
        return render_mako_context(request, '/manager/403.html', res_json)

    # 获取对应的模版并校验权限
    try:
        if pk:
            template = business.objects.get(id=pk)
            Application.objects.get(cc_name=template.business_name,
                                    operator__username=request.user.username)
        else:
            template = business.objects.get(business_name=business_name,
                                            template_name=template_name)
            Application.objects.get(cc_name=cc_name,
                                    operator__username=request.user.username)
    except ObjectDoesNotExist:
        res_json['data'] = u'您无操作权限,请联系系统管理员'
        return render_mako_context(request, '/manager/403.html', res_json)

    # 表格初始化
    response = HttpResponse(content_type="application/vnd.ms-excel")
    response['Content-Disposition'] = 'attachment; filename="%s_%s.xls"' % (
        template.business_name.encode('utf-8'),
        template.business_type.encode('utf-8'))
    wbk = xlwt.Workbook(encoding='utf-8')
    sheet = wbk.add_sheet('sheet1')
    sheet.col(2).width = 20000
    sheet.col(3).width = 20000

    # 枚举参数初始化
    if flag == Flag.EDIT or flag == Flag.TASK or flag == 'False':
        head_list = HeadList.TEMPLATE
        excel_list = ExcelList.TEMPLATE
        business_step = BUSINESS_STEP_MODEL["template"]
        business_detail = BUSINESS_DETAIL_MODEL['template']
        q = Q()
    else:
        q = ~Q(stat=1)
        head_list = HeadList.INSTANCE
        excel_list = ExcelList.INSTANCE
        business_step = BUSINESS_STEP_MODEL['instance']
        business_detail = BUSINESS_DETAIL_MODEL['instance']
    try:
        for i, data in enumerate(head_list):
            sheet.write(0, i, data)
        row = 1
        algn_center_hv = Alignment()
        algn_center_hv.horz = Alignment.HORZ_CENTER
        algn_center_hv.vert = Alignment.VERT_CENTER
        style_cdata = XFStyle()
        style_cdata.alignment = algn_center_hv

        # 获取步骤详情并填充表格
        steps = business_step.objects.filter(business=template).order_by('id')
        for step in steps:
            details = business_detail.objects.filter(
                q, business_step=step).order_by('step_xh')
            sheet.write_merge(row, row + details.count() - 1, 0, 0, step.name,
                              style_cdata)
            details = business_detail.to_dict(details)
            for detail in details:
                for i, value_key in enumerate(excel_list):
                    sheet.write(row, i + 1, detail[value_key])
                row += 1
        wbk.save(response)
        return response
    except Exception, e:
        logger.error(u'下载模版失败%s' % e)
        res_json['data'] = u'下载模版失败'
        return render_mako_context(request, '/manager/403.html', res_json)
    def style_rd2wt(self, rdbook, rdxf):

        wtxf = XFStyle()
        #
        # number format
        #
        wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
        #
        # font
        #
        wtf = wtxf.font
        rdf = rdbook.font_list[rdxf.font_index]
        wtf.height = rdf.height
        wtf.italic = rdf.italic
        wtf.struck_out = rdf.struck_out
        wtf.outline = rdf.outline
        wtf.shadow = rdf.outline
        wtf.colour_index = rdf.colour_index
        wtf.bold = rdf.bold  #### This attribute is redundant, should be driven by weight
        wtf._weight = rdf.weight  #### Why "private"?
        wtf.escapement = rdf.escapement
        wtf.underline = rdf.underline_type  ####
        # wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
        wtf.family = rdf.family
        wtf.charset = rdf.character_set
        wtf.name = rdf.name
        #
        # protection
        #
        wtp = wtxf.protection
        rdp = rdxf.protection
        wtp.cell_locked = rdp.cell_locked
        wtp.formula_hidden = rdp.formula_hidden
        #
        # border(s) (rename ????)
        #
        wtb = wtxf.borders
        rdb = rdxf.border
        #            wtb.left   = rdb.left_line_style
        #            wtb.right  = rdb.right_line_style
        #            wtb.top    = rdb.top_line_style
        #            wtb.bottom = rdb.bottom_line_style
        wtb.left = 1
        wtb.right = 1
        wtb.top = 1
        wtb.bottom = 1
        wtb.diag = rdb.diag_line_style
        wtb.left_colour = rdb.left_colour_index
        wtb.right_colour = rdb.right_colour_index
        wtb.top_colour = rdb.top_colour_index
        wtb.bottom_colour = rdb.bottom_colour_index
        wtb.diag_colour = rdb.diag_colour_index
        wtb.need_diag1 = rdb.diag_down
        wtb.need_diag2 = rdb.diag_up
        #
        # background / pattern (rename???)
        #
        wtpat = wtxf.pattern
        rdbg = rdxf.background
        wtpat.pattern = rdbg.fill_pattern
        wtpat.pattern_fore_colour = rdbg.pattern_colour_index
        wtpat.pattern_back_colour = rdbg.background_colour_index
        #
        # alignment
        #
        wta = wtxf.alignment
        rda = rdxf.alignment
        wta.horz = rda.hor_align
        wta.vert = rda.vert_align
        wta.dire = rda.text_direction
        # wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
        wta.rota = rda.rotation
        wta.wrap = rda.text_wrapped
        wta.shri = rda.shrink_to_fit
        wta.inde = rda.indent_level
        # wta.merg = ????
        #
        return wtxf
Beispiel #17
0
 def _style_factory(self):
     return XFStyle()
#实际在这里开始for,循环的取excel中的动态值

if sheet_read_only.cell_value(6, 2) == 'form':
    content_type = 'application/x-www-form-urlencoded'
else:
    content_type = 'application/json'

#把取出的值进行转字典
params = eval(sheet_read_only.cell_value(6, 3))
response = request(request_type, url, params=params)

rs = eval(response.text)  # 仅仅获得所有的返回值

if response.status_code == sheet_read_only.cell_value(
        6, 4) and rs['error_code'] == sheet_read_only.cell_value(6, 5):
    original_wb = open_workbook(r'd:\聚合数据.xls', formatting_info=True)
    new_wb = copy(original_wb)
    sheet = new_wb.get_sheet(0)
    border = Borders()
    border.left = border.THIN
    border.right = border.THIN
    border.top = border.THIN
    border.bottom = border.THIN

    style = XFStyle()
    style.borders = border

    sheet.write(6, 6, '通过', style)
    new_wb.save(r'd:\聚合数据测试报告.xls')
Beispiel #19
0
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook

connection = connect(host='192.168.1.4',
                     user='******',
                     password='******',
                     database='cms',
                     port=3306)
cursor = connection.cursor()
cursor.execute('select * from qin_student')
result = cursor.fetchall()

wb = Workbook('utf-8')
sheet = wb.add_sheet('学生信息')

title_style = XFStyle()
content_style = XFStyle()

alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER

title_style.alignment = alignment
content_style.alignment = alignment

title = ['编号', '姓名', '生日', '性别']
for i in range(len(title)):
    sheet.write(0, i, title[i], title_style)
content = result
for i in range(len(content)):
    for j in range(len(content[i])):
        sheet.write(1 + i, 0 + j, content[i][j], content_style)