python读写Excel
openpyxl读写Excel
读取Excel内容
from openpyxl import load_workbook
wb = load_workbook(filename='test.xlsx')
ws = wb['Sheet1']
for row in ws.rows:
print(row， row[0].value)
若想按行标读取：
from openpyxl import load_workbook
wb = load_workbook(filename='test.xlsx')
ws = wb['Sheet1']
rows = list(ws.rows)
for index in range(start, end):
row = rows[index]
内容写入Excel
from openpyxl import load_workbook
wb = load_workbook(filename='test.xlsx')
ws = wb['Sheet1']
ws.cell(row=1, column=1).value = 'new value'
wb.save('new_test.xlsx')
wb.close()
xlrd、xlwt读写Excel
读取Excel内容
读取excel使用库xlrd，直接pip安装即可，示例代码：
import xlrd
data = xlrd.open_workbook('text.xls')
def excel_table_by_name(col_name_index=0, by_name=u'Sheet1'):
"""
根据名称获取Excel表格中的数据
:param col_name_index: 表头列名所在行的索引
:param by_name: Sheet名称
:return:
"""
try:
table = data.sheet_by_name(by_name)
rows = table.nrows # 行数
col_names = table.row_values(col_name_index) # 某一行数据
lst = []
for row_num in range(1, rows):
row = table.row_values(row_num)
if row:
app = {}
for i in range(len(col_names)):
app[col_names[i]] = row[i]
lst.append(app)
return lst
except Exception as e:
return None
tables = excel_table_by_name(0, 'Sheet1')
if tables:
for row in tables:
print(row['名称'])
使用xlrd读取的行中包含日期时间时，需要进行特殊转化：
from datetime import datetime
tables = excel_table_by_name(0, 'Sheet1')
if tables:
for row in tables:
date_values = xlrd.xldate_as_tuple(row['时间'], data.datemode)
print(date_values, type(date_values))
print(datetime(*date_values))
内容写入Excel
xlrd库对应有一个xlwt库，支持写入内容到Excel。当读取和写入的是同一个Excel文件时，需要对读取的Excel文件进行拷贝，然后将变更的内容写入拷贝的Excel文件，示例如下：
from xlutils.copy import copy
from xlrd import open_workbook
rb = open_workbook('test.xlsx')
wb = copy(rb)
w_sheet = wb.get_sheet(0)
w_sheet.write(1, 1, 'test')
wb.save('new_test.xlsx')
但是xlwt最多可写入65500行数据。