1.如何用Python打开Excel文档?如何从工作簿中取得工作表?
书上写的是import openpyxl,但是我实际操作的时候还安装了jdcal和et_xmlfile两个包才能导入openpyxl。
>>> import openpyxl
>>> wb=openpyxl.load_workbook('C:\\Users\\DELL\\Desktop\\Python data\\example.xlsx')
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet=wb.get_sheet_by_name('Sheet3')
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>> anothersheet=wb.get_active_sheet()
>>> anothersheet
<Worksheet "Sheet1">
为什么总是弹出“warning message”???
2.如何从表中取得单元格(两种方法)?已知列字母(数字)如何得到列数字(字母)?
emmmmm书上又错了。。。get_column_letter方法已经在Openpyxl 的2.4版本中重写了,如今想要用此方法需要从openpyxl.utils中导入,而非从openpyxl.cell。
- 从表中取的单元格:
字母列+数字行:
>>> sheet=wb.get_sheet_by_name('Sheet1')
>>> sheet['A1'].value
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c=sheet['A1']
>>> c.value
datetime.datetime(2015, 4, 5, 13, 34, 2)
整数行列:
>>> sheet.cell(row=1,column=2)
<Cell 'Sheet1'.B1>
>>> sheet.cell(row=1,column=2).value
'Apples'
>>> for i in range(1,8,3):
print(i,sheet.cell(row=i,column=2).value)
1 Apples
4 Oranges
7 Strawberries
-
列字母和数字的转换:
>>> from openpyxl.utils import get_column_letter,column_index_from_string >>> column_index_from_string('FG') 163 >>> get_column_letter(755) 'ACA'
3.如何从表中取得某行、某列或某个区域的切片?
书上的方法又双不行了,参开stackoverflow网站给出的解答
https://stackoverflow.com/questions/42603795/typeerror-generator-object-is-not-subscriptable
两种方法提取某列:
>>> sheet["A"]
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>, <Cell 'Sheet1'.A7>)
>>> for cellObj in sheet["A"]:
print(cellObj.value)
2015-04-05 13:34:02
2015-04-05 03:41:23
2015-04-06 12:46:51
2015-04-08 08:59:43
2015-04-10 02:07:00
2015-04-10 18:10:37
2015-04-10 02:40:46
>>> list(sheet.columns)[1]
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cellObj in list(sheet.columns)[0]:
print(cellObj.value)
2015-04-05 13:34:02
2015-04-05 03:41:23
2015-04-06 12:46:51
2015-04-08 08:59:43
2015-04-10 02:07:00
2015-04-10 18:10:37
2015-04-10 02:40:46
行也同理~
>>> sheet["1"]
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
>>> for cellObj in sheet["1"]:
print(cellObj.value)
2015-04-05 13:34:02
Apples
73
去的某区域切片:用两个for循环~
>>> for rowOfCellObjects in sheet['A1':'C3']:
for CellObj in rowOfCellObjects:
print(cellObj.coordinate,cellObj.value)
print('---end of row---')
C1 73
C1 73
C1 73
---end of row---
C1 73
C1 73
C1 73
---end of row---
C1 73
C1 73
C1 73
---end of row---
emmmmmm我也不知道哪错了T^T
4.实践项目:从2019年美国人口普查表格中读取数据。
import openpyxl
wb = openpyxl.load_workbook('C:\\Users\\DELL\\Desktop\\Python data\\censuspopdata.xlsx')
sheet = wb.active
tonji = {}
for rowA in range(2, sheet.max_row):
zhou = sheet.cell(row=rowA, column=2).value
xian = sheet.cell(row=rowA, column=3).value
if zhou not in tonji.keys():
tonji[zhou] = {}
if xian not in tonji[zhou].keys():
tonji[zhou][xian] = {'pop': 0, 'qushu': 0}
tonji[zhou][xian]['pop'] += int(sheet.cell(row=rowA, column=4).value)
tonji[zhou][xian]['qushu'] += 1
print(tonji)
5.如何用Python新建一个Excel文档并保存?如何创建和删除工作表?如何编辑单元格?
##建立Excel文档
>>> import openpyxl
>>> wb=openpyxl.Workbook()
>>> wb.get_sheet_names()
['Sheet']
>>> sheet=wb.get_active_sheet()
>>> sheet.title='python table'
##建立工作表
>>> wb.create_sheet()
<Worksheet "Sheet">
>>> wb.get_sheet_names()
['python table', 'Sheet']
>>> wb.create_sheet(index=0,title='sheet1')
<Worksheet "sheet1">
>>> wb.get_sheet_names()
['sheet1', 'python table', 'Sheet']
##编辑单元格
>>> sheet['A1']='hello kidult!'
>>> sheet['A1'].value
'hello kidult!'
6.实践项目:更新产品销售表格中的单元格。
7.如何利用font对象设置表格在单元格中的显示方式?
https://blog.csdn.net/weixin_41569319/article/details/80807006
import os, openpyxl
from openpyxl.styles import Font
italic24Font = Font(size = 24, italic = True)
sheet['A1'].font = italic24Font
sheet['A1'] = 'hello kidult!'
8.如何为单元格设置公式?
sheet['B9']='=SUM(B1:B8)'
9.如何对行高、列宽、单元格合并和拆分做处理?如何冻结窗格?
使用.freeze_panes属性