操作excel文件
来源:互联网 发布:软件开发里程碑计划 编辑:程序博客网 时间:2024/05/15 04:51
http://www.python-excel.org/
相关库
xlwt、xlrd 只能简单读写xls(office 2003),不支持xlsx
xlutils 依赖于xlwt、xlrd,实现改写文件,目前已被pyopenxl融合
xlsxwriter 支持图表,xlsx,目前pyopenxl也已支持
openpyxl 功能比较完善,但不支持xls文件
----- -----
openpyxl
----- -----
只支持xlsx,不支持xls文件!
安装库:
pip install openpyxl
概念:
Workbooks 一个excel工作表
Sheets 工作表中的一张表页
Cells 简单的一个格
<*>创建表格
wb = openpyxl.Workbooks() 例化一个excel工作表
ws = wb.active # 获得当前激活的表页(默认第一张)
wb.sheetnames #获得所有的表页名称
wb.get_sheet_names() #获得所有的表页名称
ws1 = wb.create_sheet("Mysheet") # 在最后新建一张表页
ws2 = wb.create_sheet("Mysheet", 0) # 在起始新建一张表页
ws.title = "New Title" # 修改表标题
ws.sheet_properties.tabColor = "1072BA" # 修改标题的背景颜色
ws3 = wb["New Title"] #根据表名称获取表
target = wb.copy_worksheet(ws1) # 复制表页,不能复制图、表格等属性
<*>操作单元格
ws['A4'] = xxx # 根据坐标直接操作
d = ws.cell(row=4, column=2, value=10) # 调用方法操作
cell_range = ws['A1':'C2'] # 操作多个单元格
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
tuple(ws.rows) # 按行排列,返回所有的单元格索引
tuple(ws.columns) # 按行排列,返回所有的单元格索引
wb = Workbook(guess_types=True) #设置自动识别单元格类型
cell.number_format #查看单元格格式
cell.value = xxx # 修改单元格的值
ws['A4'] "=SUM(1, 1)" #使用公式
ws.merge_cells('A2:D2') #合并单元格
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells('A2:D2') #取消合并单元格
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
from openpyxl.drawing.image import Image
img = Image('logo.png')
ws.add_image(img, 'A1') # 插入图片
ws.column_dimensions.group('A','D', hidden=True) #隐藏列
cell.font = Font(name='Courier', size=36) #修改格式
ws.append([cell, 3.14, None]) # 插入单元格
data = [["Fruit", "Quantity"],["Kiwi", 3],]
for r in data:
ws.append(r)
<*>保存文件
wb.save('new.xlsx')
<*>打开文件
wb2 = openpyxl.load_workbook('test.xlsx')
<*>插入批注
from openpyxl.comments import Comment
ws["A1"].comment = Comment('This is the comment text', 'Comment Author')
<*>单元格格式
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from copy import copy
font = Font(name='Calibri',
size=11,
bold=False,
italic=None,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,start_color='FFFFFFFF',end_color='FF000000')
border = Border(left=Side(border_style=None,color='FF000000'),
right=Side(border_style=None,color='FF000000'),
top=Side(border_style=None,color='FF000000'),
bottom=Side(border_style=None,color='FF000000'),
diagonal=Side(border_style=None,color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,color='FF000000'),
vertical=Side(border_style=None,color='FF000000'),
horizontal=Side(border_style=None,color='FF000000'))
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True,hidden=False)
cell.font = font
#好像不起作用,还是手动设置好点
col = ws.column_dimensions['A']
col.font = Font(color='FF000000'x) # 整列修改格式,但单元格必须创建好,且要等到关闭文件才生效
row = ws.row_dimensions[1]
row.font = Font(underline="single")
自定义单元格样式
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight") #例化一个自定义的样式
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000") #边框
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight) #加入格式
ws['A1'].style = highlight #应用格式,第一次则会自动注册
ws['D5'].style = 'highlight' #之后直接使用名称即可
ws['D5'].style = 'Normal' #无格式
<*>页面设置???
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
<*>打印设置
ws.print_options.horizontalCentered = True # 居中
ws.print_options.verticalCentered = True
# 如果没使用首页不同、奇偶页不同,则默认使用奇页的配置
# 页眉功能不是很全,部分格式会被忽略,导致结果为空
ws.oddHeader.left.text = "Page &[Page] of &N"
ws.oddHeader.left.size = 14
ws.oddHeader.left.font = "Tahoma,Bold"
ws.oddHeader.left.color = "CC3366"
ws.firstHeader.xxx
ws.firstHeader.
print(ws.title+'左页眉:\n'+ws.oddHeader.left.text)
print(ws.title+'中页眉:\n'+ws.oddHeader.center.text)
print(ws.title+'右页眉:\n'+ws.oddHeader.right.text)
ws.print_title_cols = 'A:B' # the first two cols #打印标题
ws.print_title_rows = '1:1' # the first row
ws.print_area = 'A1:F10' #设置打印范围
<*>筛选、过滤器
ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15"
----- -----
pandas 处理二维表格的工具
----- -----
DataFrame
相关库
xlwt、xlrd 只能简单读写xls(office 2003),不支持xlsx
xlutils 依赖于xlwt、xlrd,实现改写文件,目前已被pyopenxl融合
xlsxwriter 支持图表,xlsx,目前pyopenxl也已支持
openpyxl 功能比较完善,但不支持xls文件
----- -----
openpyxl
----- -----
只支持xlsx,不支持xls文件!
安装库:
pip install openpyxl
概念:
Workbooks 一个excel工作表
Sheets 工作表中的一张表页
Cells 简单的一个格
<*>创建表格
wb = openpyxl.Workbooks() 例化一个excel工作表
ws = wb.active # 获得当前激活的表页(默认第一张)
wb.sheetnames #获得所有的表页名称
wb.get_sheet_names() #获得所有的表页名称
ws1 = wb.create_sheet("Mysheet") # 在最后新建一张表页
ws2 = wb.create_sheet("Mysheet", 0) # 在起始新建一张表页
ws.title = "New Title" # 修改表标题
ws.sheet_properties.tabColor = "1072BA" # 修改标题的背景颜色
ws3 = wb["New Title"] #根据表名称获取表
target = wb.copy_worksheet(ws1) # 复制表页,不能复制图、表格等属性
<*>操作单元格
ws['A4'] = xxx # 根据坐标直接操作
d = ws.cell(row=4, column=2, value=10) # 调用方法操作
cell_range = ws['A1':'C2'] # 操作多个单元格
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
tuple(ws.rows) # 按行排列,返回所有的单元格索引
tuple(ws.columns) # 按行排列,返回所有的单元格索引
wb = Workbook(guess_types=True) #设置自动识别单元格类型
cell.number_format #查看单元格格式
cell.value = xxx # 修改单元格的值
ws['A4'] "=SUM(1, 1)" #使用公式
ws.merge_cells('A2:D2') #合并单元格
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells('A2:D2') #取消合并单元格
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
from openpyxl.drawing.image import Image
img = Image('logo.png')
ws.add_image(img, 'A1') # 插入图片
ws.column_dimensions.group('A','D', hidden=True) #隐藏列
cell.font = Font(name='Courier', size=36) #修改格式
ws.append([cell, 3.14, None]) # 插入单元格
data = [["Fruit", "Quantity"],["Kiwi", 3],]
for r in data:
ws.append(r)
<*>保存文件
wb.save('new.xlsx')
<*>打开文件
wb2 = openpyxl.load_workbook('test.xlsx')
<*>插入批注
from openpyxl.comments import Comment
ws["A1"].comment = Comment('This is the comment text', 'Comment Author')
<*>单元格格式
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from copy import copy
font = Font(name='Calibri',
size=11,
bold=False,
italic=None,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,start_color='FFFFFFFF',end_color='FF000000')
border = Border(left=Side(border_style=None,color='FF000000'),
right=Side(border_style=None,color='FF000000'),
top=Side(border_style=None,color='FF000000'),
bottom=Side(border_style=None,color='FF000000'),
diagonal=Side(border_style=None,color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,color='FF000000'),
vertical=Side(border_style=None,color='FF000000'),
horizontal=Side(border_style=None,color='FF000000'))
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True,hidden=False)
cell.font = font
#好像不起作用,还是手动设置好点
col = ws.column_dimensions['A']
col.font = Font(color='FF000000'x) # 整列修改格式,但单元格必须创建好,且要等到关闭文件才生效
row = ws.row_dimensions[1]
row.font = Font(underline="single")
自定义单元格样式
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight") #例化一个自定义的样式
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000") #边框
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight) #加入格式
ws['A1'].style = highlight #应用格式,第一次则会自动注册
ws['D5'].style = 'highlight' #之后直接使用名称即可
ws['D5'].style = 'Normal' #无格式
<*>页面设置???
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
<*>打印设置
ws.print_options.horizontalCentered = True # 居中
ws.print_options.verticalCentered = True
# 如果没使用首页不同、奇偶页不同,则默认使用奇页的配置
# 页眉功能不是很全,部分格式会被忽略,导致结果为空
ws.oddHeader.left.text = "Page &[Page] of &N"
ws.oddHeader.left.size = 14
ws.oddHeader.left.font = "Tahoma,Bold"
ws.oddHeader.left.color = "CC3366"
ws.firstHeader.xxx
ws.firstHeader.
print(ws.title+'左页眉:\n'+ws.oddHeader.left.text)
print(ws.title+'中页眉:\n'+ws.oddHeader.center.text)
print(ws.title+'右页眉:\n'+ws.oddHeader.right.text)
ws.print_title_cols = 'A:B' # the first two cols #打印标题
ws.print_title_rows = '1:1' # the first row
ws.print_area = 'A1:F10' #设置打印范围
<*>筛选、过滤器
ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15"
----- -----
pandas 处理二维表格的工具
----- -----
DataFrame
阅读全文