python向excel写数据_Python从Excel读数据和向Excel写入数据

openpyxl

# 读数据

import openpyxl

book = openpyxl.load_workbook('1.xlsx')

Sheet = book.get_sheet_by_name('Sheet1')

rows = table.rows

cols = table.columns

data = []

for row in rows:

line = [col.value for col in row]

data.append(line)

# 写数据

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

for i in range(len(data)):

for j in range(len(data[i])):

ws[i+1][j].value = data[i][j]

wb.save(filename = dest_filename)

pandas

# 读数据

# 1.读数据到列表中

df = pd.read_excel(sorce_excel_path, sheet_name='sheet1')

rows = df.shape[0]

row_array = []

for row in range(rows):

row_array.append(row)

# 2.获取具体的数据

df = pd.read_excel(new_excel_path, sheetname='sheet1')

# 1、查看文件行数与列数

print(df) # 文件内容

print( df.shape) # 文件行列

print(df.shape[0]) # 文件行数

print(df.shape[1]) # 文件列数

# 2、获取文件列名行号

list(df.columns) # 获取列名

list(df.index) # 获取行号

df.columns.get_loc('姓名') # 根据列名获取列号、下标

df['任务编号'] # 根据列名获取内容 （方式一）

df.任务编号 # 根据列名获取内容 （方式二）

# 3、根据[行][列]来获取值

df_1['姓名'][002]

# 根据[行号][列号]来获取值

# [第0行][第1列]

df.iloc[0][1]

# [第0行][第2列]

df.iloc[0][2]

# 4、获取多列

df[['姓名', '电话']]

# 从第2列取到最后一列

df.iloc[:, 2:]

# 取第2列到第4列

df.iloc[:, 2:5]

# 3 写数据

# 这种写法可避免pandas每次向Excel写入sheet，覆盖之前的sheet

book = openpyxl.load_workbook(excel_path)

writer = pd.ExcelWriter(excel_path, engine='openpyxl')

writer.book = book

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

# sheet = writer.sheets.get('sheetname')

# 将data写入writer

data.to_excel(writer,sheet_name="",index=False)

writer.save(）

rpa

data = [[],[],[]] # 二维列表，每一个子列表是要写入的一行数据

excel = openpyxl.load_workbook('excel_Path')

sheet = excel.worksheets[0]

# 循环写入数据

for i in range(0, len(data)):

sheet.write(str(i), data[i])

excel.save(file='save_path')