用 Python 幫運營妹紙快速搞定 Excel 文檔

留言有禮，每天都會從留言中選出三位抽取6.6元紅包

• Spreadsheet（電子表格） 或者 Workbook（工作簿） – 指文件本身(.xls or .xlsx).
• Worksheet（工作表） 或者 Sheet（表）–工作簿中的單個內(nèi)容表，電子表格可以包含多個工作表。
• Column（列） – 用英文字母標記的垂直數(shù)列，以“ A”開頭。
• Row（行） – 從1開始以數(shù)字標記的水平數(shù)列。
• Cell（單元格） – 列和行的組合，例如“ A1”。

• Python 讀寫 Excel 的第三方庫
• 從工作簿中獲取工作表
• 讀取單元格數(shù)據(jù)
• 遍歷行和列
• 寫入 Excel 電子表格
• 添加和刪除工作表
• 添加和刪除行和列

• https://openpyxl.readthedocs.io/en/stable/

• xlrd – 用于讀取舊格式的 Excel (.xls) 文件
• xlwt – 用于寫入舊格式的 Excel (.xls) 文件
• xlwings – 用于新格式的Excel格式并具有宏功能

$?python?-m?pip?install?openpyxl

• https://github.com/driscollis/python101code/tree/master/chapter38_excel

#?open_workbook.py
from?openpyxl?import?load_workbook
def?open_workbook(path):
????workbook?=?load_workbook(filename=path)
????print(f'Worksheet?names:?{workbook.sheetnames}')
????sheet?=?workbook.active
????print(sheet)
????print(f'The?title?of?the?Worksheet?is:?{sheet.title}')
if?__name__?==?'__main__':
????open_workbook('books.xlsx')

Worksheet?names:?['Sheet?1?-?Books']
"Sheet?1?-?Books">
The?title?of?the?Worksheet?is:?Sheet?1?-?Books

#?workbook_cells.py
from?openpyxl?import?load_workbook
def?get_cell_info(path):
????workbook?=?load_workbook(filename=path)
????sheet?=?workbook.active
????print(sheet)
????print(f'The?title?of?the?Worksheet?is:?{sheet.title}')
????print(f'The?value?of?{sheet["A2"].value=}')
????print(f'The?value?of?{sheet["A3"].value=}')
????cell?=?sheet['B3']
????print(f'{cell.value=}')
if?__name__?==?'__main__':
????get_cell_info('books.xlsx')

"Sheet?1?-?Books">
The?title?of?the?Worksheet?is:?Sheet?1?-?Books
The?value?of?sheet["A2"].value='Title'
The?value?of?sheet["A3"].value='Python?101'
cell.value='Mike?Driscoll'

def?get_info_by_coord(path):
????workbook?=?load_workbook(filename=path)
????sheet?=?workbook.active
????cell?=?sheet['A2']
????print(f'Row?{cell.row},?Col?{cell.column}?=?{cell.value}')
????print(f'{cell.value=}?is?at?{cell.coordinate=}')
if?__name__?==?'__main__':
????get_info_by_coord('books.xlsx')

在此示例中，您將使用單元格對象的行和列屬性來獲取行和列信息。注意，“ A”列映射為“ 1”，“ B”映射為“ 2”，等等。如果要遍歷Excel文檔，則可以使用坐標屬性來獲取單元格名稱。

運行此代碼時，輸出如下所示：

Row?2,?Col?1?=?Title
cell.value='Title'?is?at?cell.coordinate='A2'

#?iterating_over_cells.py
from?openpyxl?import?load_workbook
def?iterating_range(path):
????workbook?=?load_workbook(filename=path)
????sheet?=?workbook.active
????for?cell?in?sheet['A']:
????????print(cell)
if?__name__?==?'__main__':
????iterating_range('books.xlsx')

'Sheet?1?-?Books'.A1>
'Sheet?1?-?Books'.A2>
'Sheet?1?-?Books'.A3>
'Sheet?1?-?Books'.A4>
'Sheet?1?-?Books'.A5>
'Sheet?1?-?Books'.A6>
'Sheet?1?-?Books'.A7>
'Sheet?1?-?Books'.A8>
'Sheet?1?-?Books'.A9>
'Sheet?1?-?Books'.A10>
#?output?truncated?for?brevity

• min_row
• max_row
• min_col
• max_col

#?iterating_over_cell_values.py
from?openpyxl?import?load_workbook
def?iterating_over_values(path):
????workbook?=?load_workbook(filename=path)
????sheet?=?workbook.active
????for?value?in?sheet.iter_rows(
????????????min_row=1,?max_row=3,
????????????min_col=1,?max_col=3,
????????????values_only=True,
????????):
????????print(value)
if?__name__?==?'__main__':
????iterating_over_values('books.xlsx')

('Books',?None,?None)
('Title',?'Author',?'Publisher')
('Python?101',?'Mike?Driscoll',?'Mouse?vs?Python')

#?writing_hello.py
from?openpyxl?import?Workbook
def?create_workbook(path):
????workbook?=?Workbook()
????sheet?=?workbook.active
????sheet['A1']?=?'Hello'
????sheet['A2']?=?'from'
????sheet['A3']?=?'OpenPyXL'
????workbook.save(path)
if?__name__?==?'__main__':
????create_workbook('hello.xlsx')

#?creating_sheets.py
import?openpyxl
def?create_worksheets(path):
????workbook?=?openpyxl.Workbook()
????print(workbook.sheetnames)
????#?Add?a?new?worksheet
????workbook.create_sheet()
????print(workbook.sheetnames)
????#?Insert?a?worksheet
????workbook.create_sheet(index=1,
??????????????????????????title='Second?sheet')
????print(workbook.sheetnames)
????workbook.save(path)
if?__name__?==?'__main__':
????create_worksheets('sheets.xlsx')

['Sheet']
['Sheet',?'Sheet1']
['Sheet',?'Second?sheet',?'Sheet1']

#?delete_sheets.py
import?openpyxl
def?create_worksheets(path):
????workbook?=?openpyxl.Workbook()
????workbook.create_sheet()
????#?Insert?a?worksheet
????workbook.create_sheet(index=1,
??????????????????????????title='Second?sheet')
????print(workbook.sheetnames)
????del?workbook['Second?sheet']
????print(workbook.sheetnames)
????workbook.save(path)
if?__name__?==?'__main__':
????create_worksheets('del_sheets.xlsx')

['Sheet',?'Second?sheet',?'Sheet1']
['Sheet',?'Sheet1']

#?remove_sheets.py
import?openpyxl
def?remove_worksheets(path):
????workbook?=?openpyxl.Workbook()
????sheet1?=?workbook.create_sheet()
????#?Insert?a?worksheet
????workbook.create_sheet(index=1,
??????????????????????????title='Second?sheet')
????print(workbook.sheetnames)
????workbook.remove(sheet1)
????print(workbook.sheetnames)
????workbook.save(path)
if?__name__?==?'__main__':
????remove_worksheets('remove_sheets.xlsx')

workbook.remove(workbook['Sheet1'])

['Sheet',?'Second?sheet',?'Sheet1']
['Sheet',?'Second?sheet']

• .insert_rows()
• .delete_rows()
• .insert_cols()
• .delete_cols()

• idx –插入行或列的索引
• amount–要添加的行數(shù)或列數(shù)

#?insert_demo.py
from?openpyxl?import?Workbook
def?inserting_cols_rows(path):
????workbook?=?Workbook()
????sheet?=?workbook.active
????sheet['A1']?=?'Hello'
????sheet['A2']?=?'from'
????sheet['A3']?=?'OpenPyXL'
????#?insert?a?column?before?A
????sheet.insert_cols(idx=1)
????#?insert?2?rows?starting?on?the?second?row
????sheet.insert_rows(idx=2,?amount=2)
????workbook.save(path)
if?__name__?==?'__main__':
????inserting_cols_rows('inserting.xlsx')

#?delete_demo.py
from?openpyxl?import?Workbook
def?deleting_cols_rows(path):
????workbook?=?Workbook()
????sheet?=?workbook.active
????sheet['A1']?=?'Hello'
????sheet['B1']?=?'from'
????sheet['C1']?=?'OpenPyXL'
????sheet['A2']?=?'row?2'
????sheet['A3']?=?'row?3'
????sheet['A4']?=?'row?4'
????#?Delete?column?A
????sheet.delete_cols(idx=1)
????#?delete?2?rows?starting?on?the?second?row
????sheet.delete_rows(idx=2,?amount=2)
????workbook.save(path)
if?__name__?==?'__main__':
????deleting_cols_rows('deleting.xlsx')

• Python 處理 Excel的第三方軟件包
• 從工作簿中獲取工作表
• 讀取單元格數(shù)據(jù)
• 遍歷行和列
• 寫入 Excel 電子表格
• 添加和刪除工作表
• 添加、刪除行和列

轉(zhuǎn)發(fā)，點贊，在看，安排一下？