0001.Excel文件新建读取修改保存

一、文件读取

python办公自动化，会用到xlrd库，所以需要先安装xlrd库。

import xlrd
wb=xlrd.open_workbook('招生表.xls')     # 读取工作簿
ws=wb.sheets()        #  文件sheets包含项，列表式的键值对
print(ws)
wsname=wb.sheet_names()      # 列表式工作簿下的所有工作表对象
print(wsname)
ws1=wb.sheet_by_name('中山分校')    # 按指定名称读取工作表对象-方法1
print(ws1)
ws2=wb.sheet_by_index(0)      # 按指定名称读取工作表对象-方法2
print(ws2)
ws3=wb.sheets()[0]      # 按指定名称读取工作表对象-方法3
print(ws3.name)

 

二、行列单元格读取

import  xlrd
ws=xlrd.open_workbook('招生表.xls').sheet_by_name('中山分校')    # 获取工作表对象
crow=ws.nrows     # 获取行号
print(crow)   
ccol=ws.ncols     # 获取列号
print(ccol)
row_data=ws.row_values(3)    # 获取指定行数据
print(row_data)
col_data=ws.col_values(1)    # 获取指定列数据
print(col_data)
cell_data_1=ws.cell_value(2,1)   # 获取单元格数据
print(cell_data_1)
cell_data_2=ws.cell(1,3).value   # 获取单元格书籍
print(cell_data_2)

数据共计：44行4列；其中第4行（起始为0，所以3即为第4行）数据信息；第2列（起始为0，所以1即为第2列）数据信息；第3行2列数据；第2列4行数据。
根据上述数据读取方式，在需要进行多单元格数据读取时，即可以采用行、列指定范围后嵌套遍历即可完成需求数据读取。

三、文件保存

同样的，读取办公文件用到了xlrd库，那么写入即保存需要用到xlwt库，所以首先需要安装xlwt库。

import xlwt
nwb=xlwt.Workbook(encoding='utf-8')    # 新建工作簿
nws=nwb.add_sheet('成绩表')      # 添加工作簿，命名为‘成绩表’
nws.write(1,2,'Hello!Excel我来了！')   # 写入单元格，向2行3列单元格写入数据
nwb.save('成绩单.xls')     # 保存工作薄

四、修改excel信息

同样的，修改信息同样也是有一个库方法，即xlutils库。

import xlrd
from xlutils.copy import copy
wb=xlrd.open_workbook('招生表.xls')
print(wb)
nwb=copy(wb)
print(nwb)
nws1=nwb.add_sheet('上海分校')      #  添加工作簿，命名为"上海分校"
nws2=nwb.get_sheet(1)             # 获取第2个工作簿
nws3=nwb.get_sheet('黄河分校')      # 获取"黄河分校"工作簿
nws3.write(5,7,'我来也')            # 在"黄河分校"的第6行8列单元格写入"我来也"
nws1.write(0,0,'上海上海')          # 在新建的"上海分校"工作簿的第1行1列单元格写入“上海上海
nwb.save('招生表.xls')

五、openpyxl库

1. Excel的新建、读取、保存

import openpyxl
wb=openpyxl.Workbook()       #  新建Excel
wb.save('我的工作簿.xlsx')       # 保存


import openpyxl
wb=openpyxl.load_workbook('我的工作簿.xlsx')   # 读取
wb.save('我的工作簿-1.xlsx')      # 保存


import openpyxl
for m in range(1,13):            # 循环新建Excel保存
    wb=openpyxl.Workbook()    
    wb.save('%d月.xlsx'%m)     # 保存

2. 工作表的获取方法

import openpyxl
wb=openpyxl.load_workbook('各年业绩表.xlsx')
ws1=wb.active#获取活动工作表
ws2=wb.worksheets[2]#以索引值方式获取工作表
ws3=wb['2012年']#以工作表名获取
# for sh in wb.worksheets:
#     print(sh)
# print(wb.sheetnames)
wb.worksheets[1].title='demo'    # 修改第2个sheet的工作簿名为“demo”
wb.save('各年业绩表-1.xlsx')

import openpyxl
wb=openpyxl.load_workbook('各年业绩表.xlsx')
for sh in wb.worksheets:
    sh.title=sh.title+'-芝华公司'
wb.save('各年业绩表(修改后).xlsx')

3. 工作表的新建、复制、删除

import openpyxl
wb=openpyxl.Workbook()
wb.create_sheet()      
wb.create_sheet()
wb.create_sheet()      # 共计创建了3个工作表
wb.save('demo1.xlsx')

wb=openpyxl.load_workbook('demo2.xlsx')
wb.create_sheet('工资表',2)
wb.save('demo2.xlsx')


wb=openpyxl.load_workbook('demo3.xlsx')
wb.copy_worksheet(wb['工资表']).title='工资表1月'    # copy工作表并命名为“工作表1月”
wb.save('demo3-1.xlsx')

import openpyxl
wb=openpyxl.load_workbook('demo3-1.xlsx')
wb.remove(wb['工资表'])    # 删除了“工资表”工作表
wb.save('demo3-1.xlsx')

load_woekbook(read_only=False, guess_types=False, data_only=False) 包含3个选择参数：

read_only：False表示可读、可写；True表示只读、不能写；

guess_types：False表示转换数据；True表示不能转换数据；

data_only：False表示序单元格的真实信息；True表示只读取值。

4. 单元格数据获取

A1表示法：工作表['A1']，R1C1表示法：工作表.cell[行号，列号]

import openpyxl
wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.worksheets[0]
print(ws['b1'].value)             #  获取b1单元格数据
print(ws.cell(1, 2).value)          #  获取b1单元格数据
print(openpyxl.load_workbook('demo.xlsx').worksheets[0]['b1'].value)      #  获取每个sheet的b1单元格数据


wb=openpyxl.load_workbook('各年业绩表.xlsx')
print(sum([sh['b14'].value for sh in wb.worksheets]))     # 获取每个sheet的b14单元格数据之和

5. 单元格区域信息获取

① 工作表['起始单元格':'终止单元格'] 或 工作表['起始单元格:终止单元格'] ，如 ws['A1':'F3'] 或 ws['A1:F3']。此方法是按行读取的数据；

② 工作表['起始行号': '终止行号'] 或 工作表['起始行号: 终止行号'] ，如 ws['1': '3'] 或 ws['1: 3']。此方法是按行读取的数据；

③ 工作表['起始列号': '终止列号'] 或 工作表['起始列号: 终止列号'] ，如 ws['A': 'F'] 或 ws['A: F']。此方法是按列读取的数据；

④ 获取（按行）指定工作表所有已用数据：list(workbook.worksheets[索引值].values)

import openpyxl
wb=openpyxl.load_workbook('demo.xlsx', data_only=True)
ws=wb.active        # 获取所有工作表的活动区域
# print([[c.value for c in row] for row in ws['a1:d3']])  # 嵌套for循环
print(list(ws.values)[1:4])     # 获取第二行到第四行数据列表，先取list集再切片

# 方法1：
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.active
rngs=ws['a2:e71']       # 选择自己需要的区域
print(['%s-%d'%(row[0].value, sum([c.value for c in row][1:])) for row in rngs])      # 获取每行姓名与各科分数总和
print(['%s-%d'%(row[0],sum(row[1:])) for row in list(ws.values)[1:]])            # 与上述结果一致

# 方法2：
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.active
f=[sum(l)/len(l) for l in list(zip(*list(ws.values)[1:]))[1:]]
n=[c.value for c in ws['1']][1:]
print(['%s-%.2f'%c for c in list(zip(n,f))])    # 获取各科平均分
print(['%s-%.2f'%(l[0],sum(l[1:])/len(l[1:])) for l in list(zip(*list(ws.values)))[1:]])    # 获取各科平均分

 6. 行列信息获取，即通过行列动态获取对应区域信息

① 按行获取工作表使用区域数据： worksheet.rows

② 按列获取工作表使用区域数据：worksheet.columns

③ 获取工作表中最小行号：worksheet.min_row

④ 获取工作表中最小列号：worksheet.min_column

⑤ 获取工作表中最大行号：worksheet.max_row

⑥ 获取工作表中最大列号：worksheet.max_column

⑦ 获取单元格的行号：cell.row

⑧ 获取单元格的列号：cell.column

⑨ iter 方法获取指定区域：

a. 按行获取指定工作表单元格区域：worksheet.iter_rows(......)

b. 按列获取指定工作表单元格区域：worksheet.iter_cols(......)

注：......可以通过min_row，min_col，max_col，max_row这几个参数进行单元区域的控制。

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for row in list(ws.rows)[1:]:    #  ws.rows按行获取每行数据
    l=[v.value for v in row]
    print(l[0],sum(l[1:]))

for col in list(ws.columns)[1:]:  #  ws.columns按列获取每行数据
    l=[v.value for v in col]
    print(l[0],max(l[1:]))

for row in ws.iter_rows(min_row=36,min_col=2,max_col=4,max_row=40):
    print([c.value for c in row])   

7. 单元格的写入

A1表示法：工作表['A1']=值， R1C1表示法：工作表.cell(行号, 列号, 值)

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[1]
ws['a1']=123            
ws.cell(2,3,'我是中国人')
ws.cell(3,3).value='我是四川人'
wb.save('test.xlsx')

wb=openpyxl.Workbook()
ws=wb.active
ws.title='九九表'
for x in range(1,10):
    for y in range(1,x+1):
        ws.cell(x,y,'%d×%d=%d'%(y,x,x*y))
wb.save('九九表.xlsx')

8. 批量写入数据

在最后一行写入数据：工作表.append(列表)

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
ws.append([1,2,3,4,5])    # 在最后一行写入
ws.append((1,2,3,4,5))  
ws.append({1:'张三', 3:56, 6:'fdgsfg'})   #  依次在最后一行第1/3/6列写入
ws.append({'a':'张三','b':56,'c':'fdgsfg'})   # a,b,c同样表示列
wb.save('test.xlsx')

wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
# for r in [['%d*%d=%d'%(y,x,x*y) for y in range(1,x+1)] for x in range(1,10)]:
#     ws.append(r)
# ws.delete_rows(1)
# wb.save('demo.xlsx')
for row in ws['a1:c6']:
    for c in row:
        c.value=1
wb.save('demo.xlsx')

9. 循环方式批量写入数据

循环获取单元格的同时进行写入作业，如批量写入时的九九乘法方式。

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
for row in ws['a1:g9']:        # 指定区域
    for c in row:                  # 行循环
        c.value=100
wb.save('test.xlsx')

wb=openpyxl.load_workbook('demo.xlsx')
ws=wb.active
rngs=ws.iter_rows(min_row=2,min_col=2)
for row in rngs:       # 活动区域循环1
    for c in row:      # 行循环2
        if c.value>=90:     # 针对大于90的数据添加"(优秀)"
            c.value=str(c.value)+'（优秀）'
wb.save('demo1.xlsx')

10. 工作表行、列的插入和删除

① 插入列：worksheet.insert_cols(位置，列数)，其中位置是指在工作表的第几列前

② 插入行：worksheet.insert_rows(位置，列数)，其中位置是指在工作表的第几行前

③ 删除列：worksheet.delete_cols(位置，列数)，从指定位置开始向后删除指定的列

④ 删除行：worksheet.delete_rows(位置，列数)，从指定位置开始向下删除指定的行

import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
ws=wb.worksheets[0]
ws.insert_cols(3,2)        # 从第3列开始向后插入2列，第3列向后移动变成3+2=5列
ws.insert_rows(6,5)        # 从第6行开始向后插入5行，第6行向后移动变成6+5=11行
ws.delete_cols(4,2)        # 删除从第4列开始的2列数据，即第4、5列被删除
ws.delete_rows(6,3)        # 删除从第6行开始的3行数据，即第6、7、8行被删除
wb.save('test.xlsx')

wb=openpyxl.load_workbook('成绩表.xlsx')
ws=wb.active
for r in range(ws.max_row,1,-1):    # 从max_row以步长-1向1循环
    s=sum([c.value for c in ws[r]][1:])   # 求每行从第2列开始的数据和
    if s>=300:                
        ws.delete_rows(r)      # 针对数据和大于300的进行删除
wb.save('成绩表筛选结果.xlsx')