在本地目录创建一个doc目录:(不用自己新建表格)
import openpyxl
def create_to_excel(wbname, data, sheetname='Sheet1', ):
"""
将制定的信息保存到新建的excel表格中;
:param wbname:
:param data: 往excel中存储的数据;
:param sheetname:
:return:
"""
print("正在创建excel表格%s......" % (wbname))
# wb = openpyxl.load_workbook(wbname)
# 如果文件不存在, 自己实例化一个WorkBook的对象;
wb = openpyxl.Workbook()
# 获取当前活动工作表的对象
sheet = wb.active
# 修改工作表的名称
sheet.title = sheetname
# 将数据data写入excel表格中;
print("正在写入数据........")
for row, item in enumerate(data): # data发现有4行数据, item里面有三列数据;
for column, cellValue in enumerate(item):
cell = sheet.cell(row=row + 1, column=column + 1, value=cellValue)
# cell = sheet.cell(row=row+1, column=column + 1)
# cell.value = cellValue
wb.save(wbname)
print("保存工作薄%s成功......." % (wbname))
if __name__ == '__main__':
data = [
['书记名称', '数量', '价格'],
['python核心编程', 50, 90],
['Java核心编程', 60, 80],
['Php核心编程', 100, 90],
]
create_to_excel('/home/kiosk/Documents/python/python0116/doc/excel2.xlsx',data,'书籍统计信息')
先要按照图书的数量大小排序新生成一个表格:
import openpyxl
def create_to_excel(wbname, data, sheetname='Sheet1', ):
"""
将制定的信息保存到新建的excel表格中;
:param wbname:
:param data: 往excel中存储的数据;
:param sheetname:
:return:
"""
print("正在创建excel表格%s......" % (wbname))
# wb = openpyxl.load_workbook(wbname)
# 如果文件不存在, 自己实例化一个WorkBook的对象;
wb = openpyxl.Workbook()
# 获取当前活动工作表的对象
sheet = wb.active
# 修改工作表的名称
sheet.title = sheetname
# 将数据data写入excel表格中;
print("正在写入数据........")
for row, item in enumerate(data): # data发现有4行数据, item里面有三列数据;
for column, cellValue in enumerate(item):
cell = sheet.cell(row=row + 1, column=column + 1, value=cellValue)
# cell = sheet.cell(row=row+1, column=column + 1)
# cell.value = cellValue
wb.save(wbname)
print("保存工作薄%s成功......." % (wbname))
def readwb(wbname, sheetname=None):
# 1. 加载工作薄
wb = openpyxl.load_workbook(filename=wbname)
# 2. 选择操作的工作表
if not sheetname:
sheet = wb.active
else:
sheet = wb[sheetname]
# 读取数据, 存储为python的数据结构
goodsInfo = []
for row in sheet.rows:
rowValues = [cell.value for cell in row]
goodsInfo.append(rowValues)
return goodsInfo
if __name__ == '__main__':
data = [
['书记名称', '数量', '价格'],
['python核心编程', 50, 90],
['Java核心编程', 60, 80],
['Php核心编程', 100,90],
]
goodsInfo = readwb('doc/excel2.xlsx', '书籍信息统计')
numSortInfo = [goodsInfo[0]]+ sorted(goodsInfo[1:], key=lambda x: x[1]) # 由小到大排序
# numSortInfo = goodsInfo[0:1]+sorted(goodsInfo[1:],key=lambda x:x[1],reverse=True) 由大到小排序
print(numSortInfo)
#numSortInfo内容: [['书籍名称', '数量', '价格'], ['python核心编程', 50, 90], ['Java核心编程', 60, 80], ['php核心编程', 100, 90]]
create_to_excel('/home/kiosk/Documents/python/python0116/doc/sortednum.xlsx', numSortInfo, '书籍信息统计按照数量排序') #重新生成表格
# 也可以按照商品价格进行排序 sorted()
priceSortInfo = [goodsInfo[0]] + sorted(goodsInfo[1:], key=lambda x: x[2])
print(priceSortInfo)
create_to_excel('doc/sorted_by_price.xlsx', numSortInfo, '书籍信息统计按照价格排序')