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

叁歲伎倆 2022-08-21 14:40 26阅读 0赞

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/