Python办公自动化学习笔记---Excel操作

一、Excel操作

1.对单元格的读写操作

*写操作

import xlwt  #先下载:win+R打开命令框输入pip install xlwt
new_writebook=xlwt.Workbook()
worksheet=new_writebook.add_sheet('new_test')
worksheet.write(0,0,"test")
new_writebook.save('d:/test.xls')

        解释：xlwt.workbook新建一个test.xls表格，

        add_sheet表示整个Excel文件的第一页，叫做new_test

        write在0行0列（也就是左上角第一个单元格）写入test

        save方法后上面的操作才生效

*读操作

import xlrd  #先下载:win+R打开命令框输入pip install xlrd
xlsx=xlrd.open_workbook('d:/test.xls')
table=xlsx.sheet_by_index(0)
#table=xlsx.sheet_by_name('new_test')
print(table.cell_value(0,0))
#print(table.cell(0,0).value)
#print(table.row(0)[0].value)

        解释：选择xlsx文件中的某一个表格：byname或者byindex

        打印的三种方式！

2.写入带样式的数据

from xlutils.copy import copy
import xlrd
import xlwt
​
new_tem=xlrd.open_workbook('d:/python_test/test.xls', formatting_info=True)
#一定要带formatting_info=True，不然原有的数据格式无法复制到新的文件中
​
new_excel=copy(new_tem)   #新的xlsx文件
new_sheet=new_excel.get_sheet(0) #新文件的第一页是所copy文件的第一页
​
style1=xlwt.XFStyle()  #新建一个样式，写入数据时使用
​
#设置字体
font1=xlwt.Font()
font1.name='微软雅黑'
font1.bold=True
font1.height=320  #在Excel中所设置的字体大小*20
style1.font=font1  #加入到style1样式中
​
#单元格边框样式
borders=xlwt.Borders()
borders.top=xlwt.Borders.THIN  #细线
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style1.borders=borders #加入到style1样式中
​
#位置：居中或其他方式
alignment1=xlwt.Alignment()
alignment1.horz=xlwt.Alignment.HORZ_CENTER #水平居中
alignment1.vert=xlwt.Alignment.VERT_CENTER #垂直居中
style1.alignment=alignment1 #加入到style1样式中
​
#写数据
new_sheet.write(1,1,62,style1)  #一定要带样式才能写入带样式的数据
new_sheet.write(2,1,63,style1)  #单元格左上角的格子坐标为（0,0）
new_sheet.write(3,1,64,style1)
​
new_excel.save('d:/python_test/test2.xls') #保存这个新文件

        注意一点：打开的时候要写上formatting_info=True

3.习题：生成一个统计数表

​

#生成数据统计表格带样式：也就是第一列可能在表格中重复出现，他右边的单元格是他的数值
#                      将他们的数值加在一起进行统计，只保留第一列的一个数据
import xlrd
import xlwt
from xlutils.copy import copy
​
​
xlsx=xlrd.open_workbook('d:/python_test/test2.xls') #test2是原始要进行统计的xls文件
table=xlsx.sheet_by_index(0)  #打开所要读取数据的表格：test的第一页
all_data=[] #存储所有读到的数据
for n in range(1,table.nrows):   #第一行到最后一行
    date=table.cell(n,0).value #第n行第0列的单元格
    points=table.cell(n,1).value #第n行第一列的单元格
    data={'date':date,'points':points}  #每一行的数据封装，'date'和'points'相当于变量
    all_data.append(data)    #[]里面是{}的集合
#我的test2中只有三个年份的数据，所以定义三个
apoints=0
bpoints=0
cpoints=0
​
for i in all_data:
    if i['date']==2000:
       apoints=apoints+i['points']   #重复数据相加
    if i['date']==2001:
       bpoints=bpoints+i['points']
    if i['date']==2002:
       cpoints=cpoints+i['points']
​
    
#定义样式：这里我使用的是test表格中的样式，也就是说，样式表和原始表格可以不是同一个
new_tem=xlrd.open_workbook('d:/python_test/test.xls', formatting_info=True)
#一定要带formatting_info=True，不然原有的数据格式无法复制到新的文件中
new_excel=copy(new_tem)   #新的xlsx文件
new_sheet=new_excel.get_sheet(0) #新文件的第一页是所copy文件的第一页
style1=xlwt.XFStyle()  #新建一个样式，写入数据时使用
#设置字体
font1=xlwt.Font()
font1.name='微软雅黑'
font1.bold=True
font1.height=320  #在Excel中所设置的字体大小*20
style1.font=font1  #加入到style1样式中
​
#单元格边框样式
borders=xlwt.Borders()
borders.top=xlwt.Borders.THIN  #细线
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style1.borders=borders #加入到style1样式中
​
#位置：居中或其他方式
alignment1=xlwt.Alignment()
alignment1.horz=xlwt.Alignment.HORZ_CENTER #水平居中
alignment1.vert=xlwt.Alignment.VERT_CENTER #垂直居中
style1.alignment=alignment1 #加入到style1样式中
​
#写数据
new_sheet.write(1,0,'2000',style1)  #一定要带样式才能写入带样式的数据
new_sheet.write(1,1,apoints,style1)
​
new_sheet.write(2,0,'2001',style1)  #单元格左上角的格子坐标为（0,0）
new_sheet.write(2,1,bpoints,style1)
​
new_sheet.write(3,0,'2002',style1)
new_sheet.write(3,1,cpoints,style1)
​
new_excel.save('d:/python_test/test3.xls') #另保存为新文件

​

表格test3如下：

表格test2如下：

4.xlwt的限制以及解决方式

xlwt列数超过256就会报错

如果超过250列则有两个工具可以使用：

 （1）xlsxwriter不支持带格式的写        

import xlsxwriter as xw  #pip install xlsxwriter
workbook=xw.Workbook('d:/python_test/test.xlsx')
sheet1=workbook.add_worksheet('sheet1') #表格的页名
for i in range(0,300):
    sheet1.write(0,i,i)
workbook.close()

 

（2）openpyxl性能不稳定，有时会报错 

import openpyxl  #pip install openpyxl
workbook=openpyxl.load_workbook('d:/python_test/test3.xlsx')
sheet1=workbook['sheet1']
sheet1['B3']='5'
sheet1['B4']='6'
sheet1['B5']=7
sheet1['B6']=8
workbook.save('d:/python_test/test4.xlsx')