python对excel详细操作

Question

如题所述

Answer 1

Python 中，通过第三方库 openpyxl 可实现对 Excel 文件的深入操作。以下列举了几个典型操作：

首先，安装 openpyxl 库：

pip install openpyxl

创建 Excel 文件，添加数据：

import openpyxl

workbook = openpyxl.Workbook()

sheet = workbook.active

sheet['A1'] = 'Name'

sheet['B1'] = 'Age'

sheet['A2'] = 'John Doe'

sheet['B2'] = 30

sheet['A3'] = 'Jane Smith'

sheet['B3'] = 25

workbook.save('example.xlsx')

读取 Excel 文件数据：

import openpyxl

workbook = openpyxl.load_workbook('example.xlsx')

sheet = workbook.active

name = sheet['A2'].value

age = sheet['B2'].value

print(f'Name: {name}, Age: {age}')

追加数据到工作表：

import openpyxl

workbook = openpyxl.load_workbook('example.xlsx')

sheet = workbook.active

sheet.append(['Bob Johnson', 35])

workbook.save('example.xlsx')

遍历 Excel 数据：

import openpyxl

workbook = openpyxl.load_workbook('example.xlsx')

sheet = workbook.active

for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=2, values_only=True):

print(row)

设置单元格样式：

from openpyxl import Workbook

from openpyxl.styles import Font, Alignment

workbook = Workbook()

sheet = workbook.active

cell = sheet['A1']

cell.value = 'Bold and Centered'

cell.font = Font(bold=True)

cell.alignment = Alignment(horizontal='center')

workbook.save('styled_example.xlsx')

以上仅为 openpyxl 常用操作示例，实际应用中，该库支持更多复杂功能，如单元格合并、图表创建、公式设置等。如需详细了解，请查阅官方文档。