Read and write excel file with format by pandas and xlwt libraries of python

    import xlrd
    import xlwt
    import pandas as pd
    path = 'd:\\tmp\\'
    file_name = 'games_report.xls'
    xls_df = pd.ExcelFile(path+file_name)
    print(xls_df.sheet_names)
    df1 = xls_df.parse('one')
    print(df1[3:8])
    for row in df1.values:
        print(row[0],' == ',row[1])
    
    df = pd.read_excel(open(path+file_name,'rb'),sheetname='one')   
    #df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
    writer = pd.ExcelWriter(path+'a.xls',engine='xlsxwriter')
    df.to_excel(writer,'Sheet1')
    writer.save()
    
    all_data = []
    excel = xlrd.open_workbook(path+file_name)
    excel = xlrd.open_workbook(path+file_name,formatting_info=True)
    sheet_1=excel.sheet_by_index(1)   
    prev_row = [None for i in range(sheet_1.ncols)]
    for row_index in range(sheet_1.nrows):
        row = []
        for col_index in range(sheet_1.ncols):
            value = sheet_1.cell(rowx=row_index,colx=col_index).value
            #print(value)
            if len(str(value)) == 0:
                value = prev_row[col_index]
            row.append(value)
        prev_row = row
        all_data.append(row)        
    #print(all_data)
    df = pd.DataFrame.from_dict(all_data)
    writer = pd.ExcelWriter(path+'b.xls',engine='xlsxwriter')
    df.to_excel(writer,'Sheet1')
    
    styleBoldBlack = xlwt.easyxf('font:color-index black,height 360')
    styleBlueColor = xlwt.easyxf('font:color-index light_blue')
    styleBlueBkg = xlwt.easyxf('pattern:pattern solid,fore_colour sky_blue')
    styleHeaderBlack = xlwt.easyxf('font:color-index black,bold on;pattern:pattern solid,fore_colour yellow;')
    headerStyle = styleBoldBlack
    styleTableHeader = xlwt.XFStyle()
    borderTalbeHeader = xlwt.Borders()
    borderTalbeHeader.right = xlwt.Borders.DASHED
    borderTalbeHeader.top = xlwt.Borders.DASHED
    borderTalbeHeader.left = xlwt.Borders.DASHED
    borderTalbeHeader.left_colour = 0x40
    borderTalbeHeader.right_colour = 0x40
    borderTalbeHeader.top_colour = 0x40
    borderTalbeHeader.bottom_colour = 0x40
    fontTableHeader = xlwt.Font()
    fontTableHeader.color_index = 0x40
    fontTableHeader.bold = True
    fontTableHeader.name = 'Times New Roman'
    fontTableHeader.height = 200
    patternTableHeader = xlwt.Pattern()
    patternTableHeader.pattern = xlwt.Pattern.SOLID_PATTERN
    patternTableHeader.pattern_fore_colour = 5   
    styleTableHeader.borders = borderTalbeHeader
    styleTableHeader.font = fontTableHeader
    styleTableHeader.pattern = patternTableHeader
    wb = xlwt.Workbook()
    ws = wb.add_sheet('one')
    ws.write_merge(0,0,0,2,"one",headerStyle)
    ws.write(1,0,'email : ')
    ws.write_merge(1,1,1,2,'xxxxx@yyy.com',styleBlueColor)
    ws.write(2,0,'list : ')
    ws.write_merge(2,2,1,2,xlwt.Formula('HYPERLINK("mailto:aaaa@bbb.com";"aaaa@bbb.com")'),styleBlueColor)
    ws.write(3,1,'date',styleTableHeader)
    ws.write(3,2,'checked num',styleTableHeader)
    ws.write(3,3,'num',styleTableHeader)
    ws.write(3,4,'Comments',styleTableHeader)
    wb.save(path+'c.xls')

The related resources:

https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966

http://nullege.com/codes/search/xlwt.Borders

http://blog.sina.com.cn/s/blog_5357c0af01019gjo.html

http://www.programering.com/a/MTMyQDNwATU.html

http://www.crifan.com/python_xlwt_set_cell_background_color/