**********************基本概念*************************
# 工作薄:workbook
# 工作表:sheet
# 活动表: 打开工作薄默认所在的工作表;
# 列(column): A B C D E
# 行(row): 1 2 3 4 5 6
# 单元格(cell)
*****************基本操作*******************************
# 导入工作薄
1. openpyxl.load_workbook('excelDemo/example.xlsx')
2. wb.sheetnames
3. wb.active
4. wb['Sheet1']
# 工作表
5. sheet.title
sheet.cell(row=1, column=2)
# 单元格
6. cell = sheet['A1']
7. cell_value = sheet['A1'].value
8. cell.row, cell.column cell.coordinate
import os
import openpyxl
def readwb(wbname,sheetname=None):
# 读取excel文档
wb=openpyxl.load_workbook(wbname)
if not sheetname:
sheet=wb.active
else:
sheet=wb[sheetname]
all_info=[]
for row in sheet.rows:
child=[cell.value for cell in row]
all_info.append(child)
return sorted(all_info,key=lambda item:item[2])
def createwb(wbname):
# 创建一个excel文档
if not wbname.endswith('.xlsx'):
wbname=wbname+'.xlsx'
wb=openpyxl.Workbook()
wb.save(filename=wbname)
print('新建Excel[%s]成功!'%(wbname))
def save_to_excel(data,wbname,sheetname='Sheet1'):
'''保存排好序的数据信息到新的excel表中'''
createwb(wbname)
print('写入Excel[%s]中'%(wbname))
wb=openpyxl.load_workbook(wbname)
sheet=wb.active
sheet.title=sheetname
for row,item in enumerate(data):
for column,cellValue in enumerate(item):
sheet.cell(row=row+1,column=column+1,value=cellValue)
wb.save(filename=wbname)
print('保存成功')
sorted_info=readwb('excelDemo/example.xlsx', 'example')
save_to_excel(sorted_info,'excelDemo/sorted_example.xlsx')