Python处理Excel文档之openpyxl （二）简单的使用-CSDN博客

简单的使用

写一个工作表

 1 #coding:utf-8
 2 '''
 3 Created on 2018年8月18日
 4 
 5 @author: Administrator
 6 '''
 7 
 8 from openpyxl import Workbook
 9 from openpyxl.compat import range
10 from openpyxl.utils import get_column_letter
11 
12 
13 wb=Workbook()
14 
15 ws1=wb.active
16 ws1.title="range names"
17 for i in range(1,10):
18     for j in range(1,5):
19         ws1.cell(i,j)
20         print(get_column_letter(j))

根据索引，得到列的索引字符。

A
B
C
D....
...
...
...

例子：

 1 #coding:utf-8
 2 '''
 3 Created on 2018年8月18日
 4 
 5 @author: Administrator
 6 '''
 7 
 8 from openpyxl import Workbook
 9 from openpyxl.compat import range
10 from openpyxl.utils import get_column_letter
11 
12 wb=Workbook()
13 new_filename="empty_book.xlsx"
14 ws1=wb.active
15 ws1.title="range names"
16 
17 for row in range(1,40):
18     ws1.append(range(600))#使用了列表生成式
19 
20 ws2=wb.create_sheet("pi")
21 ws2["F5"]=3.14
22 
23 ws3=wb.create_sheet("data")
24 for row in range(10,20):
25     for col in range(27,54):
26         _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
27 wb.save(filename=new_filename)
28 
29 # 
30 # print(list(range(600)))使用了列表生成式

读取一个存在的工作簿

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = 'empty_book.xlsx')
>>> sheet_ranges = wb['range names']
>>> print(sheet_ranges['D18'].value)
3

 1 #coding:utf8
 2 '''
 3 Created on 2018年8月18日
 4 
 5 @author: Administrator
 6 '''
 7 from openpyxl import load_workbook
 8 
 9 wb=load_workbook("empty_book.xlsx")
10 sheet_ranges=wb['range names']
11 print(sheet_ranges['D18'].value)

View Code

参数设置：

load_workbook(filename, read_only, keep_vba, data_only, guess_types, keep_links)

　　guess_types:将在读取单元格时启用或禁用(默认)类型推断。

data_only控制有公式的单元格是否具有公式(默认值)或上次Excel读取表格时存储的值

keep_vba控制是否保留了任何Visual Basic元素(默认)。如果它们被保存，它们仍然是不可编辑的

重要的事说：三遍。说：“三遍。OK”

　　　　openpyxl目前没有读取Excel文件中的所有可能项，因此如果以相同的名称打开和保存现有文件，图像和图表就会丢失。

Using number formats

>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'
>>> # You can enable type inference on a case-by-case basis
>>> wb.guess_types = True
>>> # set percentage using a string followed by the percent sign
>>> ws['B1'] = '3.14%'
>>> wb.guess_types = False
>>> ws['B1'].value
0.031400000000000004
>>>
>>> ws['B1'].number_format
'0%'

 1 #coding:utf-8
 2 '''
 3 Created on 2018年8月18日
 4 
 5 @author: Administrator
 6 '''
 7 import datetime
 8 from openpyxl import Workbook
 9 
10 
11 
12 wb=Workbook()
13 ws=wb.active
14 
15 ws['A1']=datetime.datetime(2010,7,20)
16 
17 print(ws['A1'].value)
18 #2010-07-20 00:00:00
19 a1=ws['A1'].number_format
20 print(a1)
21 #yyyy-mm-dd h:mm:ss
22 wb.guess_types=True#您可以根据具体情况启用类型推断
23 b1=ws['B1']='3.14%'#使用百分号的字符串设置百分数
24 print(b1)#3.14%
25 wb.guess_types=False
26 b1_1=ws['B1'].value
27 print(b1_1)
28 #0.031400000000000004
29 print(ws['B1'].number_format)
30 #0%

View Code

使用表格公式

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")

注意:你必须使用英文名作为函数名，函数参数必须用逗号分隔，而不是其他标点符号，如分号。

openpyxl从不计算公式，但可以检查公式的名称: 检查函数的名称是否可用。

>>> from openpyxl.utils import FORMULAE
>>> "HEX2DEC" in FORMULAE
True

　　如果您试图使用一个不知道的公式，这可能是因为您使用的公式没有包含在最初的规范中。这些公式必须以_xlfn为前缀。去工作。

合并单元格、拆分单元格

当您合并单元格时，除左上角单元格外的所有单元格都将从工作表中删除。有关格式化合并单元格的信息，请参阅样式合并单元格。

>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>>
>>> # or equivalently
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

插入图像

>>> from openpyxl import Workbook
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = 'You should see three logos below'

>>> # create an image
>>> img = Image('logo.png')

>>> # add to worksheet and anchor next to cells
>>> ws.add_image(img, 'A1')
>>> wb.save('logo.xlsx')

分级显示。组合，取消组合，分类汇总

1 >>> import openpyxl
2 >>> wb = openpyxl.Workbook()
3 >>> ws = wb.create_sheet()
4 >>> ws.column_dimensions.group('A','D', hidden=True)
5 >>> ws.row_dimensions.group(1,10, hidden=True)
6 >>> wb.save('group.xlsx')

只读模式

有时候，您需要打开或编写非常大的XLSX文件，而openpyxl中的通用例程无法处理这种负载。幸运的是，有两种模式允许您使用(接近)恒定的内存消耗读写无限数量的数据

隆重介绍一下：`openpyxl.worksheet.read_only.ReadOnlyWorksheet`

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
    for cell in row:
        print(cell.value)

返回的单元格不是常规的 openpyxl.cell.cell ，是 openpyxl.cell.read_only.ReadOnlyCell 。

工作表维度

只读模式依赖于创建文件的应用程序和库，这些应用程序和库提供关于工作表的正确信息，特别是它使用的部分，即维度。有些应用程序设置错误。可以使用ws.calculate_dimension()检查工作表的表观维数。如果这返回了一个您知道不正确的范围，比如A1:A1，那么只需重新设置max_row和max_column属性就可以使用文件: