python ecxcel处理

关注微信公众号(瓠悠笑软件部落)，一起学习，一起摸鱼

免费的office套件:
libreoffice
openoffice

Excel 文档

• workbook 一个　Excel　spreadsheet　文档，单个 workbook 保存的文件扩展名是 .xlsx.
• sheets 每个workbook 能够包含多个 sheets. sheets又称为 worksheets. 用户当前正在看的sheet或者最后关闭Excel时浏览的sheet，称为 active sheet.
• columns and rows 每个sheet都有 columns (以A开始命名地址)和 rows(以1开始命名地址).
• box 　　a box at a particular column and row is called a cell. 每个box 包含一个数字或者文本值，具有数据的 box　网格组成　sheet。

安装 openyxl Module

sudo pip3 install openpyxl 

penpyxl
首先得准备一份examle.xlsx文件

A	B	C
/5/2015 1:34:02 PM	Apples	73
4/5/2015 3:41:23 AM	Cherries	85
4/6/2015 12:46:51 PM	Pears	14
4/8/2015 8:59:43 AM	Oranges	52
4/10/2015 2:07:00 AM	Apples	152
4/10/2015 6:10:37 PM	Bananas	23
4/10/2015 2:40:46 AM	Strawberries	98

#! /usr/bin/python3
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')

print(wb.get_sheet_names())
sheet = wb.get_sheet_by_name('Sheet1')
print(sheet)
print(type(sheet))
print(sheet.title)
anotherSheet = wb.get_active_sheet()
print(anotherSheet)

print(sheet['A1'])
print(sheet['A1'].value)
c = sheet['B1']
print(c.value)
print('Row ' + str(c.row) + ', Column ' + str(c.column) + ' is ' + c.value)
print('Cell ' + c.coordinate + ' is ' + c.value)
print(sheet['c1'].value)

# print max row number
print(str(sheet.max_row))
# print max column number
print(str(sheet.max_column))

# 输出内容
['Sheet1']
<Worksheet "Sheet1">
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Sheet1
<Worksheet "Sheet1">
<Cell 'Sheet1'.A1>
4/5/2015 1:34:02 PM
 Apples
Row 1, Column 2 is  Apples
Cell B1 is  Apples
73
7
3

从 Sheets　中读取 Rows　和 Columns

#! /usr/bin/python3
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
print(tuple(sheet['A1':'C3']))
for rowOfCellObjects in sheet['A1':'C3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate, cellObj.value)
    print('--- END OF ROW ---')


# 输出内容
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
A1 4/5/2015 1:34:02 PM
B1  Apples
C1 73
--- END OF ROW ---
A2 4/5/2015 3:41:23 AM
B2  Cherries
C2 85
--- END OF ROW ---
A3 4/6/2015 12:46:51 PM
B3  Pears
C3 14
--- END OF ROW ---