最近一直在使用python操作Excel,包括按行读取,按列读取,写入等操作,故记录下来,需要的时候直接拿来用。
-
简单的几个方法
from openpyxl import Workbook, load_workbook
# 加载Excel
wb = load_workbook(filedDir)
sheetnames = wb.sheetnames # Excel中存在的所有工作簿
print('Worksheet name(s)', sheetnames)
ws = wb[sheetnames[0]] # 操作第一个工作簿
print('Worksheet max row', ws.max_row) # 最大行
print('Worksheet max column', ws.max_column) # 最大列
- 按行读取,按列读取,按行写入简单实现,可直接用作模板
# coding:utf-8
class HandleExcel():
# Excel相关操作类
def __init__(self,filedDir,sheetname=None,titleraw=1,rawRange=[],columnRange=[]):
'''
:param filedDir: Excel地址, 必须
:param sheetname: Excel中表的名称, 非必须, 默认读取第一个
:param titleraw: 列的表头在哪一行
:param rawall: 行数
:param rawRange: list, 读取的行数列表,默认[1, allRaw+1]
:param columnRange: list, 读取的列数列表,默认[1, allColumn+1]
'''
from openpyxl import Workbook,load_workbook
self.workBook = Workbook()
self.wb = load_workbook(filedDir) # 加载Excel
self.sheetname = sheetname if sheetname else self.wb.sheetnames[0] #工作簿name
self.ws = self.wb[self.sheetname] # 使用当前工作簿
self.rawRange = rawRange if rawRange else [1, self.ws.max_row] # 需要读取的行区间
self.rawRange[1] += 1
self.columnRange = columnRange if columnRange else [1, self.ws.max_column]
self.columnRange[1] += 1
self.titleraw = titleraw # title所在的行,
# 按行读取
def read_excel_with_raw(self):
'''
:return: raw data list
'''
# 显示表名
print("Work Sheet Titile:", self.sheetname.title)
# 按行读取, 建立存储数据的列表
data_list = []
for row in range(self.rawRange[0], self.rawRange[1]):
temp_list = []
for col in range(self.columnRange[0], self.columnRange[1]):
cell = self.ws.cell(row, col).value
temp_list.append(cell)
data_list.append(temp_list)
return data_list
# 按列读取
def read_excel_with_column(self):
'''
:return: raw data dict
'''
# 显示表名
print("Work Sheet Titile:", self.sheetname.title)
# 按列读取, 建立存储数据的字典, 默认第一行为每个列的key
data_dic = {}
for col in range(self.columnRange[0], self.columnRange[1]):
title_name = self.ws.cell(self.titleraw, col).value
data_dic[title_name] = [self.ws.cell(i, col).value for i in range(self.rawRange[0], self.rawRange[1])]
return data_dic
def write_data_to_excel(self,records_list,save_excel_name="save.xlsx"):
# 按行写入数据, 简单实现,以后再完善
wba = self.workBook.active
for line in records_list:
wba.append(line)
self.workBook.save(save_excel_name)
'''
filedDir 为必须参数
sheetname 为读取Excel中工作簿的名称, 非必须,默认为第一个
rawRange 需要读取行数的区间, 非必须,默认为全部
columnRange 需要读取列数的区间 非必须,默认为全部
'''
handle_excel = HandleExcel(filedDir='信息.xlsx', sheetname='Sheet1', rawRange=[1,2], columnRange=[1, 2])
handle_excel.read_excel_with_raw()
handle_excel.write_data_to_excel(records_list=[['a', 'b'], ['c', 'd']])