Python3对Excel表格操作（写⼊、追加）
python3 对Excel操作
⽅法有很多，pandas可以读取Excel数据，创建Excel表格，写⼊数据，但是好像不能实现追加，后写⼊的数据会覆盖到以前数据；xlrd、xlwt、xlutils可是实现对xls格式的表格数据的读取、写⼊以及修改（如：追加）；openpyxl可以实现对xlsx格式的表格数据进⾏读、写、修改等操作。本例仅以表格的写⼊与追加为例，简单说明⼀下Python对Excel的操作，详细可参考上⾯这些⼯具包的说明⽂档。
import pandas as pd
import numpy as np
df = pd.DataFrame(
{
'name':['Jack','kate','Jim','Steve','Mike','Lucy'],
'Gender':['M','F','M','M','M','F'],
'Age':[17,15,22,34,18,23],
'Height':[173,168,164,180,182,190],
'Hobby':['Reading','Music','Football','Reading','Cooking','Running']
}
)
df
name Gender Age Height Hobby 0Jack M17173Reading
1kate F15168Music
2Jim M22164Football
3Steve M34180Reading
4Mike M18182Cooking
5Lucy F23190Running
df1 = df.copy()
df2 = df.copy()
df3 = df.copy()
df的追加，设置ignore_index=True可以⾃动递增索引号
df4 = df.append(df1, ignore_index=True)
df4
name Gender Age Height Hobby 0Jack M17173Reading
1kate F15168Music
2Jim M22164Football
3Steve M34180Reading
4Mike M18182Cooking
5Lucy F23190Running
6Jack M17173Reading
7kate F15168Music
8Jim M22164Football
9Steve M34180Reading name Gender Age Height Hobby 10Mike M18182Cooking
11Lucy F23190Running 操作xls格式的表格
import xlrd  # 读取
import xlwt  # 写⼊
py import copy  # 修改（追加写⼊）：xlutils
def write2xls(path, sheetname, value):
index =len(value)# 获取需要写⼊数据的⾏数
workbook = xlwt.Workbook()# 创建⼀个⼯作簿
sheet = workbook.add_sheet(sheetname)# 在⼯作簿中新建⼀个表格
for i in range(index):
for j in range(len(value[i])):
sheet.write(i, j, value[i][j])# 向表格中写⼊数据（对应的⾏和列）
workbook.save(path)
print("xls格式表格写⼊成功！")
def append2xls(path, sheetname, value):
index =len(value)# 获取需要写⼊的数据的⾏数
workbook = xlrd.open_workbook(path)# 打开⼯作簿
#    sheets = workbook.sheet_names()  # 获取⼯作簿⾥的所有表格
worksheet = workbook.sheet_by_name(sheetname)# 获取⼯作簿中所有表格中的第⼀个表格
rows_old = ws  # 获取表格中已存在的数据的⾏数
new_workbook = copy(workbook)# 将xlrd对象拷贝转化为xlwt对象
new_worksheet = _sheet(0)# 获取转化后⼯作簿中的第⼀个表格
for i in range(index):
for j in range(len(value[i])):
new_worksheet.write(i+rows_old, j, value[i][j])# 追加写⼊数据，注意从 i+rows_old ⾏开始
new_workbook.save(path)
print("xls格式表格追加写⼊成功！")
column_name = np.array([lumns])
write2xls(path='./zx_test.xls', sheetname='text', value=column_name)
append2xls(path='./zx_test.xls', sheetname='text', _numpy())
append2xls(path='./zx_test.xls', sheetname='text', _numpy())
xls格式表格写⼊成功！
xls格式表格追加写⼊成功！
xls格式表格追加写⼊成功！
操作xlsx格式的表格⽂件
def write2xlsx(path, sheetname, value):
index =len(value)
workbook = openpyxl.Workbook()# 实例化
sheet = workbook.active  # 激活worksheet
sheet.title = sheetname
for i in range(index):
for j in range(len(value[i])):
workbook.save(path)
print("xlsx格式表格数据写⼊成功！")
def append2xlsx(path, sheetname, value):
index =len(value)
workbook = openpyxl.load_workbook(path)
sheet = workbook[sheetname]
for i in range(index):
python怎么读入excel
sheet.append(list(value[i]))# append的内容必须是可迭代对象，⾥⾯的value必须是str类型    workbook.save(path)
print("xlsx格式表格数据追加成功！")
column_name = np.array([lumns])
write2xlsx(path='./zx_test_2.xlsx', sheetname='text', value=column_name)
data = _numpy()
append2xlsx(path='./zx_test_2.xlsx', sheetname='text', value=data)
data2 = _numpy()
append2xlsx(path='./zx_test_2.xlsx', sheetname='text', value=data2)
xlsx格式表格数据写⼊成功！
xlsx格式表格数据追加成功！
xlsx格式表格数据追加成功！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。