Python operations for Excel tables

How to use python to make an excel table: first use the openpyxl module to open an excel document, specify the cell information of the row and column; then call [wb.sheetnames] and [wb.active] to obtain the workbook details ;Finally, use index reading to obtain the Cell object.

##1 python and Excel table

Excel is a popular and powerful spreadsheet application in the Windows environment. The openpyxl module allows Python programs to read and modify Excel spreadsheet files

1) Basic definition of excel document
- workbook (workbook)
- worksheet (sheet)
- active sheet (active sheet)
- Row: 1,2,3,4,5,6……..
- Column: A,B,C,D……..
- Cell (cell): B1, C1
2) python has many modules for Excel table operations. The openpyxl module is selected here
But the openpyxl module needs to be installed

pip install openpyxl

Use the above command To install the openpyxl module

This is the table selected for operation

1> Open an excel document

import openpyxl# 1. 打开一个excel文档, 
class 'openpyxl.workbook.workbook.Workbook'实例化出来的对象wb = openpyxl.load_workbook('Book.xlsx')
print(wb, type(wb))# 获取当前工作薄里所有的工作表，和正在使用的表;print(wb.sheetnames)
print(wb.active)

The output is an Object
2> Select the worksheet to be operated

# 2.选择要操作的工作表，返回工作表对象sheet=wb['Sheet1']
#获取工作表的名称print(sheet.title)

3> Specify the cell information of the specified row and column

# 3. 返回指定行指定列的单元格信息print(sheet.cell(row=1, column=2).value)
cell = sheet['B1']print(cell)print(cell.row, cell.column, cell.value)

4> Get the maximum value of rows and columns in the worksheet

# 4. 获取工作表中行和列的最大值print(sheet.max_column)print(sheet.max_row)
sheet.title = '学生信息'print(sheet.title)

5> Access all information of the cell

# 5. 访问单元格的所有信息print(sheet.rows)  
# 返回一个生成器， 包含文件的每一行内容， 可以通过便利访问.
# 循环遍历每一行for row in sheet.rows:    
# 循环遍历每一个单元格for cell in row:        
# 获取单元格的内容
   print(cell.value, end=',')
 print()

6> Save modified information

#6.保存修改信息wb.save(filename='Boom.xlsx')

Therefore, operating Excel tables can be summarized in detail as follows:
1. Import the openpyxl module.
2. Call the openpyxl.load_workbook() function.
3. Get the Workbook object.
4. Call wb.sheetnames and wb.active to get workbook details.
5. Get the Worksheet object.
6. Use the cell() method of the index or worksheet with the row and column keyword parameters.
7. Get the Cell object.
8. Read the value attribute of the Cell object

2 Excel simple example - Define a function, readwb(wbname, sheetname=None)
- If the user specifies sheetname Open the worksheet specified by the user. If not specified, open the active sheet;
- Sort according to the price of the product (from small to large) and save it to the file; Product name: Product price: Product quantity
- All Information and save it to the database

import osimport openpyxldef readwb(wbname, sheetname=None):
        # 打开工作薄
    wb = openpyxl.load_workbook(wbname)    # 获取要操作的工作表
    if not sheetname:
        sheet = wb.active    else:
        sheet = wb[sheetname]    # 获取商品信息保存到列表中
    #[ ['name', price, count]
    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[1])def save_to_excel(data, wbname, sheetname='sheet1'):
    """
    将信息保存到excel表中;
    [[' BOOK', 50, 3], ['APPLE', 100, 1], ['BANANA', 200, 0.5]]
    """
    print("写入Excel[%s]中......." %(wbname))    #打开excel表, 如果文件不存在， 自己实例化一个WorkBook对象
    wb = openpyxl.Workbook()    # 修改当前工作表的名称
    sheet = wb.active    # 修改工作表的名称
    sheet.title = sheetname    for row, item in enumerate(data):  # 0 [' BOOK', 50, 3]
        for column, cellValue in enumerate(item): #  0 ' BOOK'
        sheet.cell(row=row+1, column=column+1, value=cellValue) # ** 往单元格写入内容
    # sheet.cell['B1'].value = "value"
    # sheet.cell(row=1, column=2, value="value")
    # 保存写入的信息
    wb.save(filename=wbname)
    print("写入成功!")
data = readwb(wbname='Book1.xlsx')
save_to_excel(data, wbname='Book2.xlsx', sheetname="排序商品信息")

* 3. Change the content of the table* Each row represents a separate sale. The columns are the type of product sold (A), the price
of the product per pound (B), the number of pounds sold (C), and the total revenue from the sale. The TOTAL column is set up as an Excel formula that multiplies the cost per pound by the number of pounds sold, and rounds the result to the nearest cent. With this formula, if column B or C changes, the cells in the TOTAL column will automatically update.

The prices that need to be updated are as follows:

Celery 1.19

Garlic 3.07
Lemon 1.27

现在假设 Garlic、 Celery 和 Lemons 的价格输入的不正确。这让你面对一项无聊
的任务:遍历这个电子表格中的几千行,更新所有 garlic、celery 和 lemon 行中每磅
的价格。你不能简单地对价格查找替换,因为可能有其他的产品价格一样,你不希
望错误地“更正”。对于几千行数据,手工操作可能要几小时
下载文件 : produceSales.xlsx
原文件打开情况：

1> 首先需要打开电子表格文件
2> 然后查找每一行内容，检查列 A （即列表的第一个索引）的值是不是 Celery、Garlic 或 Lemon
3> 如果是，更新列 B 中的价格（即列表第二个索引）
4> 最后将该表格保存为一个新的文件

import osimport openpyxldef readwb(wbname, sheetname=None):
    # 打开工作薄
    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)        
        if child[0] == 'Celery':
            child[1] = 1.19
        if child[0] == 'Garlic':
            child[1] = 3.07
        if child[0] == 'Lemon':
            child[1] = 1.27
    return all_infodef save_to_excel(data, wbname, sheetname='sheet1'):
    """
    将信息保存到excel表中;
    """
    print("写入Excel[%s]中......." % (wbname))    
    # 打开excel表, 如果文件不存在， 自己实例化一个WorkBook对象
    wb = openpyxl.Workbook()    
    # 修改当前工作表的名称
    sheet = wb.active    
    # 修改工作表的名称
    sheet.title = sheetname    
    for row, item in enumerate(data):  # 0 [' BOOK', 50, 3]
        for column, cellValue in enumerate(item):  # 0 ' BOOK'
            sheet.cell(row=row + 1, column=column + 1, value=cellValue)    
            # ** 往单元格写入内容
    # sheet.cell['B1'].value = "value"
    # sheet.cell(row=1, column=2, value="value")
    # 保存写入的信息
    wb.save(filename=wbname)
    print("写入成功!")
data = readwb(wbname='/home/kiosk/Desktop/day17/produceSales.xlsx')
save_to_excel(data, wbname='new_Sales.xlsx', sheetname="商品信息")

表示写入新数据成功

这是更改后的保存的新表格

The above is the detailed content of Python operations for Excel tables. For more information, please follow other related articles on the PHP Chinese website!