废话少说直接上代码:
import xlrd #表格的读操作
import xlwt as wt #表格的写入操作
from xlutils.copy import copy #表格的追加操作
# Execl表的写入操作:
workbook = wt.Workbook(encoding='utf-8')
sheet = workbook.add_sheet('Sheet1',cell_overwrite_ok=True)
style = wt.XFStyle() #初始化表格样式
font = wt.Font() #为样式创建字体
font.name = '微软雅黑'
font.height = 0x00C8 #默认高度
font.bold = True #粗体
font.underline = False #下划线
font.italic = False #斜体
style.font = font #设定样式
alignment = wt.Alignment()
alignment.vert = alignment.VERT_CENTER #垂直居中
alignment.horz = alignment.HORZ_CENTER #水平居中
style.alignment = alignment
list1 = [
['编号','姓名','学校','性别'],
['1','于江涛','林州一中','男'],
['2','张晓莉','林州二中','女'],
['3','李连杰','林州三中','男'],
['4','成龙','林州四中','女'],
['5','张国明','林州五中','男'],
['6','文章','林州六中','女'],
['7','刘星','林州七中','男'],
]
# 循环写入数据:
for k,v in enumerate(list1):
for a,b in enumerate(v):
sheet.write(k,a,b,style)
# 保存表格:
workbook.save("pythonExcel1.xls")
# Excel表的读操作:
data = xlrd.open_workbook(r"pythonExcel1.xls")
table1 = data.sheets()[0]
# 表中有多少行:
rows = table1.nrows
# 表中有多少列
cols = table1.ncols
# print("行数为%s \n列数为%s"%(rows,cols))
# 循环得到每一行的数据:
for i in range(rows):
row = table1.row_values(i)
print(type(row),row)
# 循环得到每一列的数据:
for i in range(cols):
col = table1.col_values(i)
print(type(col),col)
# 获取某一个单元格的数据:
cellValue = table1.cell_value(0,0) #原点数据
print(cellValue)
cell_x =table1.cell_value(3,2) #(第三行,第四列数据)
print(cell_x)
list2 = [
['8','张志勇','林州一中','男'],
['9','陆琪','林州二中','女'],
['10','苏锐','林州三中','男'],
['11','李志江','林州四中','女'],
['12','张国明','林州五中','男'],
['13','刘俊奇','林州六中','女'],
['14','黄志忠','林州七中','男'],
]
#打开表格(文件)
workbook = xlrd.open_workbook('pythonExcel1.xls')
# 获取表的名字:
sheets = workbook.sheet_names()
worksheet = workbook.sheet_by_name(sheets[0])
# 获取原数据的总行数
rows_old = worksheet.nrows
# 获取数据的条数
index = len(list2)
# 复制旧的文件
new_workbook = copy(workbook)
# 获取新文件中的第一张表
new_worksheet = new_workbook.get_sheet(0)
# 循环写入表格
for i in range(0,index):
for j in range(0,len(list2[i])):
new_worksheet.write(i+rows_old, j, list2[i][j],style)
new_workbook.save('pythonExcel1.xls')
print('追加成功!!!')