Python xlwt 模块，XFStyle() 实例源码

def _update_excel(self,parent_path):
        '''??????????'''
        rb=xlrd.open_workbook(parent_path+"/test.xls",formatting_info=True)
        wb =copy(rb)
        summary = wb.get_sheet(11)#???
        basemessage=wb.get_sheet(1)#????
        protection=xlwt.Protection()
        protection.cell_locked=0
        cellStyle1=xlwt.easyxf("protection:cell_locked false;")
        cellStyle2=xlwt.XFStyle()

        librarylogger.info(u"?????????????")
        try:
            summary.write(2, 2, u'?????????'+self._rnumber+u'?')
            librarylogger.info(u"?????????????")
            basemessage.write(7,1,self._rnumber,cellStyle1)
            librarylogger.info(u"???????????????")
        except Exception, e:
            print traceback.format_exc()
            librarylogger.info(u"?????????????")

        wb.save(parent_path+"/test.xls")

def set_style(name, height, bold=False, center=True):
    style = xlwt.XFStyle()  # ?????

    font = xlwt.Font()  # ???????
    font.name = name  # 'Times New Roman'
    font.bold = False
    font.color_index = 0
    font.height = height

    # borders= xlwt.Borders()
    # borders.left= 6
    # borders.right= 6
    # borders.top= 6
    # borders.bottom= 6

    style.font = font
    # style.borders = borders
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    if center == True:
        style.alignment = alignment
    return style

def set_excel_style(name, height, bold=False):
    """Set excel style.
    """
    style = xlwt.XFStyle() # ?????
    font = xlwt.Font() # ???????
    font.name = name # ??'Times New Roman'
    font.bold = bold
    font.color_index = 4
    font.height = height
    if bold:
        borders = xlwt.Borders()
        borders.left = 6
        borders.right = 6
        borders.top = 6
        borders.bottom = 6
        style.borders = borders
    style.font = font
    return style

def create_simple_xls(self, **kw):
        font0 = xlwt.Font()
        font0.name = 'Times New Roman'
        font0.colour_index = 2
        font0.bold = True

        style0 = xlwt.XFStyle()
        style0.font = font0

        style1 = xlwt.XFStyle()
        style1.num_format_str = 'D-MMM-YY'

        wb = xlwt.Workbook(**kw)
        ws = wb.add_sheet('A Test Sheet')

        ws.write(0, 0, 'Test', style0)
        ws.write(1, 0, datetime(2010, 12, 5), style1)
        ws.write(2, 0, 1)
        ws.write(2, 1, 1)
        ws.write(2, 2, xlwt.Formula("A3+B3"))
        return wb, ws

def setup(self):
        if self.report.ftype == REPORT.XLS:
            font_h = xlwt.Font()
            font_h.bold = True
            style_h = xlwt.XFStyle()
            style_h.font = font_h

            self.xls_styles = {
                'datetime': xlwt.easyxf(num_format_str='D/M/YY h:mm'),
                'date': xlwt.easyxf(num_format_str='D/M/YY'),
                'time': xlwt.easyxf(num_format_str='h:mm'),
                'default': xlwt.Style.default_style,
                'bold': style_h
            }

            self.wb = xlwt.Workbook()
            self.ws = self.wb.add_sheet('Data')
            if self.make_sub_reports:
                self.section_ws = self.wb.add_sheet('Section')

def _convert_to_style(cls, style_dict, num_format_str=None):
        """
        converts a style_dict to an xlwt style object
        Parameters
        ----------
        style_dict: style dictionary to convert
        num_format_str: optional number format string
        """
        import xlwt

        if style_dict:
            xlwt_stylestr = cls._style_to_xlwt(style_dict)
            style = xlwt.easyxf(xlwt_stylestr, field_sep=',', line_sep=';')
        else:
            style = xlwt.XFStyle()
        if num_format_str is not None:
            style.num_format_str = num_format_str

        return style

def create_simple_xls(self, **kw):
        font0 = xlwt.Font()
        font0.name = 'Times New Roman'
        font0.colour_index = 2
        font0.bold = True

        style0 = xlwt.XFStyle()
        style0.font = font0

        style1 = xlwt.XFStyle()
        style1.num_format_str = 'D-MMM-YY'

        wb = xlwt.Workbook(**kw)
        ws = wb.add_sheet('A Test Sheet')

        ws.write(0, 0, 'Test', style0)
        ws.write(1, 0, datetime(2010, 12, 5), style1)
        ws.write(2, 0, 1)
        ws.write(2, 1, 1)
        ws.write(2, 2, xlwt.Formula("A3+B3"))
        return wb, ws

def set_style(name, height, bold=False):
    style = xlwt.XFStyle()  # ?????

    font = xlwt.Font()  # ???????
    font.name = name  # 'Times New Roman'
    font.bold = bold
    font.color_index = 4
    font.height = height

    # borders= xlwt.Borders()
    # borders.left= 6
    # borders.right= 6
    # borders.top= 6
    # borders.bottom= 6
    style.font = font
    # style.borders = borders
    return style

def writeErrorRec(record):
  """
  Exception report spreadsheet output format same as input format and
  assumes first column is of type Date - adds an extra, last column
  for error message.
  """
  dateFmt = XFStyle()
  dateFmt.num_format_str='MM-DD-YYYY'
  defaultFmt = XFStyle()
  global erroutRow

  for colnum in range(0, len(headers)):
    erroutSheet.write(erroutRow, colnum, record[headers[colnum][0]], 
    tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, defaultFmt))

  # add extra column for error message                                    
  erroutSheet.row(erroutRow).write(len(headers), record['Error'])  

  erroutRow += 1

  # flush every 200 rows...
  #if ((erroutRow % 200) + 1 == 200):
  # since, xlwt uses aggressive caching, we must
  # write each row manually, or the data may be lost upon exception
  erroutSheet.flush_row_data()  
  errorsWorkbook.save('errors.xls')

def get_title(self):
        # ????
        font = xlwt.Font()
        # font.name = "Arial"    #????
        font.colour_index = 1  # ?????
        font.bold = True  # ????
        font.height = 20 * 11  # ??????????????????????
        # ?????
        patterni = xlwt.Pattern()
        patterni.pattern = xlwt.Pattern.SOLID_PATTERN
        patterni.pattern_fore_colour = 24  #???????

        # ????
        style = xlwt.XFStyle()
        style.font = font
        style.pattern = patterni

        for r,title in zip(range(12),["??","??","????","??","????","????","????","????","?????",
                                      "????","????","????"]):
            self.sheet.write(0,r,title,style)

        for x in [0,3,4,5,6,7,9]:
            self.sheet.col(x).width = 256*8
        self.sheet.col(1).width = 250*16
        self.sheet.col(2).width = 256*30
        self.sheet.col(8).width = 256*40
        self.sheet.col(10).width = 256*20
        self.sheet.col(11).width = 256*50

    # ???????i??????????????

def generate_xls(urls, keys, result,filename):
    """?????excel??"""
    xls_file = xlwt.Workbook()
    sheet = xls_file.add_sheet(unicode(filename), cell_overwrite_ok=True)
    font0 = xlwt.Font()
    font0.name = 'Times New Roman'
    font0.colour_index = 2
    font0.bold = True
    style0 = xlwt.XFStyle()
    style0.font = font0
    row = 0
    sheet.col(0).width = 256 * 20
    sheet.col(1).width = 256 * 30
    sheet.col(2).width = 256 * 20

    sheet.write(0, 0, u"??", style0)
    sheet.write(0, 1, u"???", style0)
    sheet.write(0, 2, u"??", style0)
    if urls != None and keys != None and result != None:
        for url, key, res in zip(urls, keys, result):
            row += 1
            sheet.write(row, 0, url.decode("utf-8"))
            sheet.write(row, 1, key.decode("utf-8"))
            sheet.write(row, 2, res.decode("utf-8"))
    fpath=os.path.join(os.path.dirname(sys.argv[0]),u"{}.xls".format(unicode(filename))) #??????
    print u"[??]???????{}".format(fpath)
    xls_file.save(fpath)

def export_users_xls(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="customers.xls"'

    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('Customers')

    # Sheet header, first row
    row_num = 0

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    columns = ['Nome', 'Sobrenome', 'E-mail', 'Nascimento', 'Criado em']

    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)

    # Sheet body, remaining rows
    default_style = xlwt.XFStyle()

    rows = Customer.objects.all().values_list('first_name',
                                              'last_name',
                                              'email',
                                              'birthday',
                                              'created')
    for row, rowdata in enumerate(rows):
        row_num += 1
        for col, val in enumerate(rowdata):
            if isinstance(val, datetime):
                val = val.strftime('%d/%m/%Y %H:%M')
            elif isinstance(val, date):
                val = val.strftime('%d/%m/%Y')
            ws.write(row_num, col, val, default_style)

    wb.save(response)
    return response

def handle_excel():   #??excel?????
    dataall = xlrd.open_workbook(file_path)
    dataall.sheet_names()
    sheetzb_name = dataall.sheet_names()[0]
    sheetzb = dataall.sheet_by_name(sheetzb_name)
    f = xlwt.Workbook()  # ?????
    sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)  # ??sheet
    style = xlwt.XFStyle()  # ?????
    style.alignment.wrap = 1
    row0 = [u'????', u'????', u'????', u'???', u'??',u'??', u'?????',u'?????', u'???',u'?????']
    # ?????
    for x in range(len(row0)):
        sheet1.write(0, x, row0[x], set_style('Times New Roman', 200, True))

    for i in range(sheetzb.nrows):
        for j in range(sheetzb.ncols):
            match=re.search(pattern,str(sheetzb.cell(i,5).value))
            if match:
                    sheet1.write(i,0,sheetzb.cell(i,0).value)
                    sheet1.write(i,1,sheetzb.cell(i,1).value)
                    sheet1.write(i,2,sheetzb.cell(i,2).value)
                    sheet1.write(i,3,sheetzb.cell(i,3).value)
                    sheet1.write(i,4,sheetzb.cell(i,4).value)
                    sheet1.write(i,5,sheetzb.cell(i,5).value)
                    sheet1.write(i,6,sheetzb.cell(i,6).value)
                    sheet1.write(i,7,sheetzb.cell(i,7).value)
                    sheet1.write(i,8,sheetzb.cell(i,8).value)
                    sheet1.write(i,9,sheetzb.cell(i,9).value)
    f.save('Data2017.xls')

def set_style(name, height,bold=False):
    style = xlwt.XFStyle()  # ?????
    font = xlwt.Font()  # ???????
    font.name = name  # 'Times New Roman'
    font.bold = bold
    font.color_index = 4
    font.height = height

    return style

def set_style(name, height,bold=False):
    style = xlwt.XFStyle()  # ?????
    font = xlwt.Font()  # ???????
    font.name = name  # 'Times New Roman'
    font.bold = bold
    font.color_index = 4
    font.height = height

    return style

def set_style(font_name,height,bold=False):
    style = xlwt.XFStyle() #?????

    font = xlwt.Font() #??
    font.name = font_name
    font.bold = bold
    font.colour_index = 4
    font.height  = height

    style.font = font
    return style

def get_header_style(self):
        """style of header"""
        style = xlwt.XFStyle()
        style.pattern = xlwt.Pattern()
        style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        style.pattern.pattern_fore_colour = 22
        return style

def create_sheet(raw_sheet, new_sheet, serv_id, poles):
    for sheet in raw_sheet:
        for time_i in range(0, sheet.ncols - 5):
            # ??????
            service_id = serv_id
            trip_id = f'{sheet.name}_{service_id}_{time_i + 1}'
            curr_sheet = new_sheet.add_sheet(trip_id)
            # stop_name?????
            stop_names = sheet.col_values(1)[6:]
            # ???2????????????
            if sheet.name == '130200':
                del(stop_names[39])
            elif sheet.name == '130210':
                del(stop_names[38])
            stop_names.insert(0, 'stop_name')
            col = 0
            for i, name in enumerate(stop_names):
                curr_sheet.write(i, col, name)
            # stop_id?????
            stop_ids = [find_stop_id(normalize('NFKC', name)) for name in stop_names]
            stop_ids[0] = ['stop_id']
            col += 1
            for i, stop_id in enumerate(stop_ids):
                if len(stop_id) == 2 and is_noboribetsu_civic_center(sheet.name):
                    stop_id = stop_id[1]
                else:
                    stop_id = stop_id[0]
                curr_sheet.write(i, col, stop_id)
            # pole_id?????
            pole_ids = [get_pole_id(p) for p in sheet.col_values(2)[6:]]
            if sheet.name == '130200':
                del(pole_ids[39])
            elif sheet.name == '130210':
                del(pole_ids[38])
            pole_ids.insert(0, 'pole_id')
            col += 1
            for i, pole_id in enumerate(pole_ids):
                if (sheet.name == '109210' and i == 45) or (sheet.name == '110210' and i == 34):
                    # 2?????2???????
                    pole_id = 'C'
                else:
                    pole_id = pole_id if pole_id is not '' else find_pole(poles, sheet.name, stop_ids[i][0], i)
                curr_sheet.write(i, col, pole_id)
            # departure_time?????
            times = [s + ':00' for s in sheet.col_values(time_i + 5)[6:]]
            if sheet.name == '130200':
                del(times[39])
            elif sheet.name == '130210':
                del(times[38])
            curr_sheet.write(0, 3, f'departure_time')
            date_format = xlwt.XFStyle()
            date_format.num_format_str = 'hh:mm:ss'
            col += 1
            for j, t in enumerate(times):
                curr_sheet.write(j + 1, col, datetime.strptime(t, '%X').time(), date_format)

def export_xls(modeladmin, request, queryset):
    response = HttpResponse(content_type="application/ms-excel")
    response["Content-Disposition"] = "attachment; filename=products.xls"
    wb = xlwt.Workbook(encoding="utf-8")
    ws = wb.add_sheet("Products")

    row_num = 0

    columns = [
        ("ID", 2000),
        ("Title", 6000),
        ("Description", 8000),
        ("Price (€)", 3000),
        ("Preview", 10000),
    ]

    header_style = xlwt.XFStyle()
    header_style.font.bold = True

    for col_num, (item, width) in enumerate(columns):
        ws.write(row_num, col_num, item, header_style)
        # set column width
        ws.col(col_num).width = width

    text_style = xlwt.XFStyle()
    text_style.alignment.wrap = 1

    price_style = xlwt.XFStyle()
    price_style.num_format_str = "0.00"

    styles = [text_style, text_style, text_style, price_style, text_style]

    for obj in queryset.order_by("pk"):
        row_num += 1
        project_photos = obj.productphoto_set.all()[:1]
        url = ""
        if project_photos:
            url = "http://{0}{1}".format(
                request.META['HTTP_HOST'],
                project_photos[0].photo.url,
            )
        row = [
            obj.pk,
            obj.title,
            obj.description,
            obj.price,
            url,
        ]
        for col_num, item in enumerate(row):
            ws.write(row_num, col_num, item, styles[col_num])

    wb.save(response)
    return response

def get_default_style(self):
        """

        * Colour index
        8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
        7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown),
        20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... sty

        * Borders
        borders.left, borders.right, borders.top, borders.bottom
        May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED,
        THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED,
        SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.

        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        borders.left_colour = 0x00
        borders.right_colour = 0x00
        borders.top_colour = 0x00
        borders.bottom_colour = 0x00
        style.borders = borders

        * Fonts
        style.font = xlwt.Font()
        style.font.height = 8 * 20
        style.font.colour_index = 22

        * Alignment
        style.alignment = xlwt.Alignment()
        style.alignment.horz = xlwt.Alignment.HORZ_LEFT
        style.alignment.vert = xlwt.Alignment.VERT_CENTER

        * Pattern
        May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12

        style.pattern = xlwt.Pattern()
        style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        style.pattern.pattern_fore_colour = 23

        """

        style = xlwt.XFStyle()
        return style