十分钟教会你使用Python操作excel,内附步骤和代码( 二 )


# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz.xlsx'# 加载工作本workbook = openpyxl.load_workbook(path)# 获取sheet对象sheet = workbook['zszxz']cells_range = sheet['A1':'C1']for cells in cells_range:for cell in cells:print(cell)输出

方式二
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz.xlsx'# 加载工作本workbook = openpyxl.load_workbook(path)# 获取sheet对象sheet = workbook['zszxz']# 获取指定范围的行row = sheet.iter_rows(min_row=1, max_col=3, max_row=1)for cell in row:print(cell)输出
(, , )
sheet.iter_cols(min_row, max_col, max_row)通用的道理 , 不赘述
3.7 获取全部行
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz.xlsx'# 加载工作本workbook = openpyxl.load_workbook(path)# 获取sheet对象sheet = workbook['zszxz']# 获取所有行for row in sheet.rows:print(row)输出
(, , )(, , )(, , )(, , )(, , )(, , )(, , )
同理 sheet.columns获取所有列不再赘述
3.8 获取值
cell.value获取属性值
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz.xlsx'# 加载工作本workbook = openpyxl.load_workbook(path)# 获取sheet对象sheet = workbook['zszxz']cells_range = sheet['A1':'C1']for cells in cells_range:for cell in cells:# 获取属性值print(cell.value)输出
idnumname
四 写入 excel
4.1 写入文本
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz_write.xlsx'# 创建一个工作本workbook = openpyxl.Workbook()# 创建一个 sheetsheet = workbook.create_sheet('zszxz')# 写入文本形式sheet['A1'] = 'zszxz666'# 读取print(sheet['A1'].value)# 保存workbook.save(path)输出
zszxz666
4.2 写入数字
# -*- coding: utf-8 -*-import openpyxlimport datetimepath = r'C:\mydata\generator\py\zszxz_write.xlsx'# 创建一个工作本workbook = openpyxl.Workbook()# 创建一个 sheetsheet = workbook.create_sheet('zszxz')# 写入值数字形式sheet['A2'] = datetime.datetime(2020, 10, 15)print(sheet['A2'].value)# 保存workbook.save(path)输出4.3 写入公式
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz_write.xlsx'# 创建一个工作本workbook = openpyxl.Workbook()# 创建一个 sheetsheet = workbook.create_sheet('zszxz')# 写入公式sheet['A3'] = '=SUM(1, 1)'print(sheet['A3'].value)# 保存workbook.save(path)4.5 取消合并单元格
# -*- coding: utf-8 -*-import openpyxlpath = r'C:\mydata\generator\py\zszxz_write.xlsx'# 创建一个工作本workbook = openpyxl.Workbook()# 创建一个 sheetsheet = workbook.create_sheet('zszxz')# 取消合并单元格sheet.unmerge_cells('A2:D2')# 保存workbook.save(path)


推荐阅读