<> How to use python handle Excel What about the form
Don't party , Rego taught you .
<> one , understand python And Excel form
Excel yes Windows Popular in the environment , Powerful spreadsheet application .openpyxl Module let Python The program can be read and modified Excel Spreadsheet file
（1）excel Basic attribute definitions in documents ：
* Workbook (workbook)
* Worksheet (sheet)
* Activity table (active sheet)
* that 's ok (row): 1,2,3,4,5,6………
* column (column): A,B,C,D………
* Cell (cell): B1, C1
（2）python about Excel There are many kinds of table operation modules , Here choose openpyxl modular , however openpyxl Module to be installed （ because openpyxl Third party Library ）
It's simple , Enter a command on the command line to download and install ：
pip install openpyxl
Let's use the following table as an example ：
Operation steps ：
（1） Open a excel file
import openpyxl # Open a excel file , class
'openpyxl.workbook.workbook.Workbook' Instantiated object wb = openpyxl.load_workbook(
'Book.xlsx') print(wb, type(wb)) # Get all worksheets in the current workbook , And tables in use ; print(wb.sheetnames)
print(wb.active)
result （ The output is an object ）：
（2） Select the worksheet you want to work on
# Select the worksheet to work on , Return worksheet object sheet = wb['Sheet1'] # Gets the name of the worksheet print(sheet.title)
result ：
（3） Specifies the cell information for the specified row or column
# Returns cell information for a specified row or column print(sheet.cell(row=1, column=2).value) cell = sheet['B1']
print(cell) print(cell.row, cell.column, cell.value)
result ：
（4） Gets the maximum number of rows and columns in the worksheet
# Gets the maximum number of rows and columns in the worksheet print(sheet.max_column) print(sheet.max_row) sheet.title =
' Student information ' print(sheet.title)
result ：
（5） Access all information about the cell
# Access all information about the cell print(sheet.rows) # Returns a generator , Contains each line of the file , Access can be facilitated through . # Loop through each line for
rowin sheet.rows: # Loop through each cell for cell in row: # Gets the contents of the cell print(cell.value, end
=',') print()
result ：
（6） Save modification information
# Save modification information wb.save(filename='Boom.xlsx')
result ：
To sum up ：
operation Excel The table can be summarized in detail as follows ：
1. Import openpyxl modular .
2. call openpyxl.load_workbook() function .
3. obtain Workbook object .
4. call wb.sheetnames and wb.active Get workbook details .
5. obtain Worksheet object .
6. Using indexes or worksheets cell() method , close row and column Keyword parameters .
7. obtain Cell object .
8. read Cell Object value attribute
<> two , Excel Simple example
demand ：
* Define a function , readwb(wbname, sheetname=None)
* If user specified sheetname Open the user specified worksheet , If not specified , open active sheet;
* Sort by commodity price ( From small to large ), Save to file ; Trade name : commodity price : Quantity of goods
* All information , And save it to the database
Look at the code ： import os import openpyxl def readwb(wbname, sheetname=None): # Open workbook wb =
openpyxl.load_workbook(wbname) # Get the worksheet to operate on if not sheetname: sheet = wb.active
else: sheet = wb[sheetname] # Get product information and save it to the list #[ ['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'): """ Save information to excel In the table ; [[' BOOK', 50, 3], ['APPLE',
100, 1], ['BANANA', 200, 0.5]] """ print(" write in Excel[%s] in ......." %(wbname)) #
open excel surface , If the file does not exist , Instantiate one yourself WorkBook object wb = openpyxl.Workbook() # Modify the name of the current sheet sheet
= wb.active # Modify the name of the worksheet 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) # ** Write content to cell #
sheet.cell['B1'].value = "value" # sheet.cell(row=1, column=2, value="value") #
Save written information wb.save(filename=wbname) print(" Write successful !") data = readwb(wbname=
'Book1.xlsx') save_to_excel(data, wbname='Book2.xlsx', sheetname=" Sort product information ")
result ：
<> three , Change the contents of the table
Each line represents a separate sale . Columns are the types of products sold (A), Price per pound of product
(B), Pounds sold ©, And the total revenue from this sale .TOTAL Set column to Excel formula , Multiply the cost per pound by the number of pounds sold ,
And round the results to points . With this formula , If column B or C undergo changes ,TOTAL The cells in the column are automatically updated .
The prices to be updated are as follows :
Celery 1.19
Garlic 3.07
Lemon 1.27
Now suppose Garlic, Celery and Lemons The price entered is incorrect . It makes you face a boring task
Task : Traverse thousands of rows in this spreadsheet , Update all garlic,celery and lemon Per pound in row
Price of . You can't simply find and replace the price , Because there may be other products with the same price , You don't like it
Hope wrongly “ corrections ”. For thousands of rows of data , Manual operation may take several hours
Download File : produceSales.xlsx
The original file is opened like this ：
Solution steps ：
1> First, you need to open the spreadsheet file
2> Then find each line , Check column A （ The first index of the list ） Is the value of Celery,Garlic or Lemon
3> If it is , Update column B Price in （ The second index of the list ）
4> Finally, save the form as a new file
Look at the code ：
import os import openpyxl def readwb(wbname, sheetname=None): # Open workbook wb =
openpyxl.load_workbook(wbname) # Get the worksheet to operate on if not sheetname: sheet = wb.active
else: sheet = wb[sheetname] # Get product information and save it to the list 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_info def save_to_excel(data, wbname,
sheetname='sheet1'): """ Save information to excel In the table ; """ print(" write in Excel[%s] in ......." % (
wbname)) # open excel surface , If the file does not exist , Instantiate one yourself WorkBook object wb = openpyxl.Workbook() #
Modify the name of the current sheet sheet = wb.active # Modify the name of the worksheet 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) # **
Write content to cell # sheet.cell['B1'].value = "value" # sheet.cell(row=1, column=2,
value="value") # Save written information wb.save(filename=wbname) print(" Write successful !") data = readwb(
wbname='/home/kiosk/Desktop/day17/produceSales.xlsx') save_to_excel(data, wbname
='new_Sales.xlsx', sheetname=" Commodity information ")
result ：
here , Write new data succeeded .
The newly generated table is as follows ：
All right , of python handle Excel That's all the content of the form , You can leave a message if you don't understand , thank you .
Technology