Python Learning-Working with Excel tables

To install a module for Excel operations: OPENPYXL

Basic definitions in 1.excel

• Workbook (Workbook): The entire Excel cousin file is called a workbook
• Worksheets (sheet): multiple worksheets in a workbook
• Activity Table (active sheet): When you save a file, it stays on that table, which is called the activity table.
• Line (ROW): 1,2,3,4,5,6 .....
• Column: A,b,c,d .....
• Cells (cell); b1,c1,c5.

Example 1

import openpyxl#打开一个excel文档wb = openpyxl.load_workbook(‘/home/kiosk/Desktop/student.xlsx‘)print(wb)print(type(wb))#查看工作簿的所有工作表print(wb.sheetnames)#查看当前活动表print(wb.active)"""运行结果：<openpyxl.workbook.workbook.Workbook object at 0x7fd69d613278><class ‘openpyxl.workbook.workbook.Workbook‘>[‘学生表‘, ‘Sheet2‘, ‘Sheet3‘]<Worksheet "Sheet3">"""#选择要操作的工作表，返回工作表对象sheet = wb[‘学生表‘]print(wb.active)#获取工作表的名称print(sheet.title)"""运行结果：<Worksheet "Sheet3">    #这里可以看到，活动表还是Sheet3，它并没有因为选择了其他操作的工作表而改变学生表"""cell = sheet[‘B1‘]print(cell)print(cell.row,cell.column)print(cell.value)"""运行结果：<Cell ‘学生表‘.B1>1 B张三"""

Example 2
There are Excel table files as follows:

Read the tabular data and write to the file stores.txt the data in column B is sorted from small to large

import openpyxldef readwb(wbname,sheetname=None):    li = []    wb = openpyxl.load_workbook(wbname)    if not sheetname:        sheet = wb[wb.active.title]    else:        sheet = wb[sheetname]    for row in sheet.rows:        row_li = [cell.value for cell in row]        li.append(row_li)    return lis = input(‘请输入工作簿名字 工作表名字（默认为活动表）：‘).split()if len(s) == 1:    li = readwb(s[0])else:    li = readwb(s[0],s[1])li = sorted(li,key=lambda x:x[1])with open(‘stores.txt‘,‘w‘) as f:    for i in li:        f.write(i[0]+‘:‘+str(i[1])+‘:‘+str(i[2])+‘\n‘)

Results

Python Learning-Working with Excel tables