xlrd和xlwt

#不支持xls 这个低版本的。
from  openpyxl import  Workbook
from openpyxl import load_workbook

c="abcdefghijklmnopqrstuvwxyz"
def key_function1(x):
    return x[6]#经理的字段
def wirte2xls(list1,col_num,outfile='out'):
    from openpyxl import workbook    #导入库
         
    wb=workbook.Workbook()          #创建一个新工作表
    sheet=wb.active                 #找到活动的sheet页
    sheet.title='新表名'         
    '''
    这个属性是可读可写的。当然，这个只针对当前活动页，别的页的话，可以用create_sheet和remove_sheet进行添加和删除。
    往sheet页里面写内容就比较简单了，跟读一样.SUBSTITUTE(ADDRESS(1,10,4),1,"")
    '''
    #sheet['C3'] = 'Hello world!'
    char1=c[col_num-1]+str(1)
    sheet[char1] = 'Hello world!' #一定要触达
    #sheet[1][3] = '3'
    print(outfile + str(len(list1)))
    i= 1
    for item1 in list1:         
        for jj in range(col_num):
            #print(item1[jj])
            sheet[i][jj].value=item1[jj]
 
        i= i+1
       
    #sheet["E1"].value = "=SUM(A:A)"   #我们还可以进行花式操作，比如写写公式：
     
    wb.save('.\out\\'+outfile+'.xlsx')  #最后记得保存
def read_xlsx(filename = ''):    
    wb2 = load_workbook(r'.\底稿500个客户.xlsx')
    print(wb2.sheetnames)
    #遍历 sheets 
    sheetn =wb2.sheetnames
    sheet1 = wb2.get_sheet_by_name(sheetn[0])
    print(sheet1.max_row,sheet1.max_column)
    #print(sheet1["1"][1].value)
    #print(sheet1[1][0].value)
    '''
    for head in sheet1["1"]:
        print(head.value)
    '''
    list1 = []
    for row1 in range(1,sheet1.max_row+1):
        list2 = []
        for item1 in sheet1[str(row1)]:
            list2.append(item1.value)
        list1.append(list2)
         
    list1.sort(key=key_function1)
    print(len(list1))
    return list1
list1 = read_xlsx("")
list_wr = []
list_wr.append(list1[0])
flag1 = list1[1][6]
print(len(list1[0]))
for list2 in list1[1:]:
    if list2[6] != flag1:
        wirte2xls(list_wr,len(list1[0]),flag1)
        list_wr.clear()
        flag1 =list2[6]
        list_wr.append(list1[0])
        list_wr.append(list2)
    else:
        list_wr.append(list2)
wirte2xls(list_wr,len(list1[0]),flag1)

import xlwt
import datetime
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding = 'utf-8') #print(workbook) %结果%<xlwt.Workbook.Workbook object at 0x005F4630>
# 创建一个worksheet
worksheet = workbook.add_sheet('小马过河')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
# 写入excel
# 参数对应 行, 列, 值
worksheet.write(1,0, label = '第二行第一列')
worksheet.write(3, 0, xlwt.Formula('HYPERLINK("https://www.baidu.com";"百度")')) # 输出文本百度，为超链接第一行第一列

#workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('魔降风云变笔记')
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman'
font.bold = True # 黑体
font.underline = True # 下划线
font.italic = True # 斜体字
style.font = font # 设定样式
worksheet.write(0, 0, '不带样式的写入') # 不带样式的写入，第一行第一列
worksheet.write(1, 0, '带样式的写入', style) # 带样式的写入，第二行第一列



sheet = workbook.add_sheet('My sheet1')  # Create sheet
first_col = sheet.col(0)
sec_col = sheet.col(1)
 
 
first_col.width = 256 * 20  # Set the column width
tall_style = xlwt.easyxf('font:height 720')  # 36pt
first_row = sheet.row(0)
first_row.set_style(tall_style)

worksheet = workbook.add_sheet('测试工作表')
worksheet.write(0, 0, 5) # Outputs 5  #第一行第一列5
worksheet.write(0, 1, 2) # Outputs 2  #第一行第二列2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])  第二行第一列，输出第一行数字乘积
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])  第二行第二列，输出第一行数字之和

worksheet = workbook.add_sheet('小马dddd')
worksheet.write_merge(0, 1, 0, 3, '合并从第一行到第二行，第一列到第四列') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font   #创建字体示例
font.bold = True # Set font to Bold  #bold设置为黑体字
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(2,3, 0, 3, '合并从第三行到第四行，第一列到第四列', style) # Merges row 1 through 2's columns 0 through 3.

worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # 创建对其格式的对象 Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER #我猜是左右的对其，水平居中 May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER #我猜是上下的对其 May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() #创建样式对象 Create Style
style.alignment = alignment # 将格式Alignment对象加入到样式对象Add Alignment to Style
worksheet.write(0, 0, '单元居中', style)  #写入的时候调用样式style

worksheet = workbook.add_sheet('背景')
pattern = xlwt.Pattern() # 创建模式对象Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 #设置模式颜色 May be: 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...
style = xlwt.XFStyle() # 创建样式对象Create the Pattern
style.pattern = pattern # 将模式加入到样式对象Add Pattern to Style
worksheet.write(0, 0, '单元格内容', style)#向单元格写入内容时使用样式对象style

worksheet = workbook.add_sheet('边界的')
borders = xlwt.Borders()  # 创建边框对象Create Borders
borders.left = xlwt.Borders.DASHED  #对边框对象进行操作，指定边框上下左右的边框类型为虚线
# DASHED虚线
# NO_LINE没有
# THIN实线
# 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.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x40      #指定上下左右的边框颜色为0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle()  # Create Style   #创建样式对象
style.borders = borders  # 将设置好的边框对象borders 加到样式对象style中。Add Borders to Style
worksheet.write(0, 0, '单元格内容', style)   #向单元格第一行第一列写入“单元格内容”，并使用创建好的样式对象style
workbook.save('我的笔记.xls') # 保存文件

实例操作

import xlrd
import xlwt
workbook  = xlrd.open_workbook('多客户相同地址委托-导出_(20190923-20191231)20200304_154224757.xls')

date_all={}
date_all2={}
sheet1 = workbook .sheet_by_index(0)#指标明细_1
rows = sheet1.nrows
cols = sheet1.ncols
print('指标明细_1工作表有%d行，%d列.'%(rows,cols))

#print('第三行内容为:',sheet1.row_values(2))#表头

#读取 预警日期(需要减1)，委托地址 以这2个为key，内容是：营销人员姓名，生成数组
for hang in range(3,rows):
    date_all[sheet1.row(hang)[1].value +" "+ sheet1.row(hang)[8].value]=sheet1.row(hang)[5].value

sheet2 = workbook .sheet_by_index(2)#预警流水_1
rows = sheet2.nrows
cols = sheet2.ncols
print('预警流水_1工作表有%d行，%d列.'%(rows,cols))

#print('第三行内容为:',sheet2.row_values(2))#表头
#读取 客户号，客户姓名 以这2个为key 获取委托时间，委托地址
for hang in range(3,rows):
    date_all2[sheet2.row(hang)[4].value + " "+sheet2.row(hang)[5].value]=sheet2.row(hang)[0].value +" "+ sheet2.row(hang)[17].value


# 创建一个workbook 设置编码
workbookw = xlwt.Workbook(encoding = 'utf-8')
# 创建一个worksheet
worksheet = workbookw.add_sheet('回访结果')
biaotou = ["风险项时间","客户号","客户姓名","风险项","回访时间","回访电话","回访结果","回访异常备注","客户经理","委托地址"]
    # 写第一行
for i in range(0,len(biaotou)):
    worksheet.write(0,i,biaotou[i])
hang =1
for key,values in  date_all2.items():
    values1 = values.split(' ')
    worksheet.write(hang,0, label = values1[0])
    worksheet.write(hang,9, label = values1[1])
    keys = key.split(' ');
    worksheet.write(hang,1, label = keys[0])
    worksheet.write(hang,2, label = keys[1])
    hang +=1

for key,values in  date_all.items():
    worksheet.write(hang,1, label = values)
    worksheet.write(hang,0, label = key)
    hang +=1

workbookw.save('【已处理】重新识别工作记录底稿（交易MAC地址相同20190923-20191231）.xls') # 保存文件

实例2

import xlrd
import xlwt
workbook  = xlrd.open_workbook('2019产品明细.xlsx')

date_all={} #营销人员，产品=金额
date_all2=[] #date_all的列表

for sheeti in range(0,11):
        sheet1 = workbook .sheet_by_index(sheeti)
        rows = sheet1.nrows
        cols = sheet1.ncols
        #print('指标明细_1工作表有%d行，%d列.'%(rows,cols))

        date_all2.clear()
        #读取 预警日期(需要减1)，委托地址 以这2个为key，内容是：营销人员姓名，生成数组
        for hang in range(2,rows):
                
                for lie in range(1,cols):
                        str1 = sheet1.row(hang)[0].value+"\t"+sheet1.row(1)[lie].value
                        if len(str(sheet1.row(hang)[lie].value))>0:
                                
                                if str1 in date_all:
                                        date_all[str1]= date_all[str1] + sheet1.row(hang)[lie].value
                                else:
                                        date_all[str1]= sheet1.row(hang)[lie].value
                        else:
                                if str1 in date_all:
                                        date_all[str1]= date_all[str1] + 0
                                else:
                                        date_all[str1]=0
                                
#print(date_all)
#sheet1 = workbook .sheet_by_index(12)
date_all2= sorted(date_all.items())

#for v1 in date_all2:
 #       print(v1[0]+"  "+str(v1[1]))
#for key,values in  date_all.items():
#    print(key,values)
    
# 创建一个workbook 设置编码
workbookw = xlwt.Workbook(encoding = 'utf-8')
# 创建一个worksheet
worksheet = workbookw.add_sheet('结果')
biaotou = ["蔡1","何1","劳1","李1","谭1","许1","钟1","钟2","李2","无主","合计"]
    # 写第一行
#for i in range(0,len(biaotou)):
#    worksheet.write(i,0,biaotou[i])
hang =0
lie=1
flagname = ""
for v1 in date_all2:
        values1 = v1[0].split('\t')
        #print(values1[0]+" "+values1[1]+"  "+str(v1[1]))
        if values1[0] == flagname:
                worksheet.write(hang,lie,  values1[1])
                worksheet.write(hang+1,lie,  v1[1])
                lie +=1
        else:
                hang +=2
                lie=1
                flagname =values1[0]
                print(flagname)
                worksheet.write(hang,0, flagname)
                


workbookw.save('【已处理】2019产品明细.xls') # 保存文件

合并多个xls和 xlsx文件

# -*- coding: utf-8 -*-
import  pandas as pd
from os import walk
# 利用walk函数取得文件名，并判断是否为xlsx文件
files = []
path  = 'D:/jhui/xls/'
for f, _, i in walk(path):
    for j in i:
        if (j.split('.')[1] == 'xlsx') or (j.split('.')[1] == 'xls'):
            j= path+j
            print(j)
            files.append(j)  
file= files     
li=[]
for i in file:
    li.append(pd.read_excel(i))
writer = pd.ExcelWriter(path+'output.xlsx')
pd.concat(li).to_excel(writer,'Sheet1',index=False)
 
writer.save()
print("合并完成")

操作两个xls文件合并成一个

# -*- coding: utf-8 -*-
"""
根据目标文件把数据文件里面的佣金分类并且计算。

"""
import xlrd,xlwt
import copy
import time
# 获取列表的第二个元素
def takeSecond(elem):
    return elem[2]

file = r'.\202104客户明细.xls'
file_disc = r'.\sh3c2020(1).xls'
rb = xlrd.open_workbook(filename = file_disc)  # 打开文件
#print(rb.sheet_names())                   # 获取所有表格名字
sheet1 = rb.sheet_by_index(1)             # 通过索引获取表格

# 读取表中的数据
nrow=sheet1.nrows;ncol=sheet1.ncols # 找到行列总数
titles =[]
date =[]
# 从表格中选择并复制部分数据  写入到新的文件中
rows=[sheet1.row_values(row,0,) for row in range(sheet1.nrows)] # 按行读取表中的所有数据
titles = rows[0]  # 把标题行另存到一个list中
del rows[0]       # 从数据中删掉标题行

cols = sheet1.col_values(0,0)
#del cols[0]
cols = list(map(str, cols))

rb = xlrd.open_workbook(filename = file)  # 打开文件
# 读取表中的数据
sheet2 = rb.sheet_by_index(0)
nrow=sheet2.nrows;ncol=sheet2.ncols # 找到行列总数
titles =[]
# 从表格中选择并复制部分数据  写入到新的文件中
rows1=[sheet2.row_values(row,0,) for row in range(sheet2.nrows)] # 按行读取表中的所有数据
titles = rows1[0]  # 把标题行另存到一个list中

del rows1[0]       # 从数据中删掉标题行
yue = rows1[0][0]
#print(type(rows1[0][titles.index('资金帐号')]))
desc_titles =['资金帐号','客户姓名','客户开发关系对应人姓名','客户开户日','手续费率（用于算费率系数）','净手续费（已扣规费，已扣增值税及附加）']
templist = []
nowdate = time.strftime('%Y%m%d',time.localtime(time.time()))
for r in rows1:
    if  cols.count(r[titles.index('资金帐号')])>0:
        for name in desc_titles:
            templist.append(r[titles.index(name)])
        
        templist.append(int(nowdate)-int(templist[3])<30000)
        date.append(copy.deepcopy( templist ))
        templist.clear()
    
date.sort(key=takeSecond)                 


wb = xlwt.Workbook()      # 创建文件

ws = wb.add_sheet(yue)   # 增加sheet
t = 0
desc_titles.append('开户是否大于3年')
#worksheet.write_formula(0, 0, '=if(20210909-d2>30000),F2*0.2,F2*0.1)')
desc_titles.append('当季系数')
for title in desc_titles:           # 这个循环用来在sheet中首行写入数据标题行
    ws.write(0,t,title)
    t+=1
row_idx = 1
for new_r in date:              # 这个循环用来在新的文件中按行、列写入数据
    col_idx = 0
    for v in new_r:
        ws.write(row_idx,col_idx,v)
        col_idx = col_idx+1
    row_idx = row_idx+1
wb.save('结算_'+yue + '.xls')