python自动化之excel

import openpyxl

wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopsl.xlsx')

type(wb)

wb.get_sheet_names()

sheet=wb.get_sheet_by_name('o')

type(sheet)

sheet.title

anotherSheet=wb.get_active_sheet()  #######得到活动页sheet

anotherShee

##################################################################################

>>> import openpyxl

>>> wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

>>> sheet=wb.get_sheet_by_name('Sheet1')

>>> sheet['A1']

<Cell u'Sheet1'.A1>

>>> sheet['A1'].value

u'4/5/2015 1:34:02 PM'

>>> c=sheet['B1']

>>> c.value

u'Apples'

>>> 'Row '+str(c.row)+',Column'+c.column+' is '+c.value

u'Row 1,ColumnB is Apples'

>>> 'Cell '+c.coordinate+' is '+c.value

u'Cell B1 is Apples'

>>> sheet['C1'].value

73L

>>> sheet.cell(row=1,column=2)

<Cell u'Sheet1'.B1>

>>> sheet.cell(row=1,column=2).value

u'Apples'

>>> for i in range(1,8,2):

...     print(i,sheet.cell(row=i,column=2).value)

...

(1, u'Apples')

(3, u'Pears')

(5, u'Apples')

(7, u'Strawberries')

>>> sheet.max_column

3

>>> sheet.max_row

7

#############################列字母与数字之间的转换##############################################

>>> import openpyxl

>>> from openpyxl.utils import get_column_letter,column_index_from_string

>>> get_column_letter(1)

'A'

>>> get_column_letter(2)

'B'

>>> get_column_letter(27)

'AA'

>>> get_column_letter(900)

'AHP'

>>> wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

>>> sheet=wb.get_sheet_by_name('Sheet1')

>>> get_column_letter(sheet.max_column)

'C'

>>> column_index_from_string('A')

1

>>> column_index_from_string('AA')

27

#####################################从表中取得行和列###########################################

>>> import openpyxl

>>> wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

>>> sheet=wb.get_sheet_by_name('Sheet1')

>>> tuple(sheet['A1':'C3'])

((<Cell u'Sheet1'.A1>, <Cell u'Sheet1'.B1>, <Cell u'Sheet1'.C1>), (<Cell u'Sheet1'.A2>, <Cell u'Sheet1'.B2>, <Cell u'Sheet1'.C2>), (<Cell u'Sheet1'.A3>, <Cell u'Sheet1'.B3>, <Cell u'Sheet1'.C3>))

>>> for rowOfCellObjects in sheet['A1':'C3']:

...     for cellObj in rowOfCellObjects:

...         print(cellObj.coordinate,cellObj.value)

...     print('---END OF ROW---')

...

('A1', u'4/5/2015 1:34:02 PM')

('B1', u'Apples')

('C1', 73L)

---END OF ROW---

('A2', u'4/5/2015 3:41:23 AM')

('B2', u'Cherries')

('C2', 85L)

---END OF ROW---

('A3', u'4/6/2015 12:46:51 PM')

('B3', u'Pears')

('C3', 14L)

---END OF ROW---

###########################################################################################

>>> import openpyxl

>>> wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

>>> sheet=wb.get_sheet_by_name('Sheet1')

>>> for cellObj in sheet.columns:

...     for rowObj in cellObj:

...         print(rowObj.value)

...

4/5/2015 1:34:02 PM

4/5/2015 3:41:23 AM

4/6/2015 12:46:51 PM

4/8/2015 8:59:43 AM

4/10/2015 2:07:00 AM

4/10/2015 6:10:37 PM

4/10/2015 2:40:46 AM

Apples

Cherries

Pears

Oranges

Apples

Bananas

Strawberries

73

85

14

52

152

23

98

#############################################################################

'''

1、导入openpyxl模块

2、调用openpyxl.load_workbook()函数

3、取得Workbook对象

4、调用get_sheet_by_name()或get_active_sheet()工作薄方法

5、取得Worksheet对象

6、使用索引或工作表的cell()方法,带上row和column关键字参数

7、取得cell对象

8、读取cell对象的value属性

'''

#############################################################################

'''

openpyxl默认大小为11，字体为Calibri

'''

import openpyxl

from openpyxl.styles import Font,NamedStyle

wb=openpyxl.Workbook()

sheet=wb.get_sheet_by_name('Sheet')

italic24Font=Font(size=24,italic=True)

styleObj=NamedStyle(font=italic24Font)

sheet.cell(row=1,column=1).font=italic24Font

sheet['A1']='Hello World'

fontObj2=Font(name='Times New Roman',bold=True)

sheet.cell(row=1,column=2).font=fontObj2

sheet['B1']='Bold Times New Roman'

wb.save('styled.xlsx')

##############################################################################

import openpyxl

wb=openpyxl.Workbook()

sheet=wb.get_active_sheet()

sheet['A1']=200

sheet['A2']=300

sheet['A3']='=SUM(A1:A2)'

wb.save('writeFormula.xlsx')

#############################################################################

import openpyxl

wbFormulas=openpyxl.load_workbook('writeFormula.xlsx')

sheet=wbFormulas.get_active_sheet()

sheet['A3'].value

import openpyxl

wb=openpyxl.load_workbook('writeFormula.xlsx',data_only=True)#######没效果

sheet=wb.get_active_sheet()

sheet['A3'].value

#############################################################################

##########################设置行高和列宽#####################################

import openpyxl

wb=openpyxl.Workbook()

sheet=wb.get_active_sheet()

sheet['A1']='Tall row'

sheet['B2']='Wide column'

sheet.row_dimensions[1].height=70     ###########默认行高是12.75  

sheet.column_dimensions['B'].width=20  ###########默认行高是8.43

wb.save('dimensions.xlsx')

#############################################################################

##########################合并与拆分单元格###################################

'''

设置这些合并后单元格的值,只要设置这一组合并单元格左上角的值

'''

import openpyxl

wb=openpyxl.Workbook()

sheet=wb.get_active_sheet()

sheet.merge_cells('A1:D3')

sheet['A1']='Twelve cells merged together.'

sheet.merge_cells('C5:D5')

sheet['C5']='Two merged cells'

wb.save('merged.xlsx')

import openpyxl

wb=openpyxl.load_workbook('merged.xlsx')

sheet=wb.get_active_sheet()

sheet.unmerge_cells('A1:D3')

sheet.unmerge_cells('C5:D5')

wb.save('merged.xlsx')

#############################################################################

#############################冻结窗口########################################

'''

freeze_panes的设置          冻结的行和列

sheet.freeze_panes='A2'                  行1

sheet.freeze_panes='B1'                  列A

sheet.freeze_panes='C1'                  列A和列B

sheet.freeze_panes='C2'                  行1和列A和列B

sheet.freeze_panes='A1'或   没有冻结窗口

sheet.freeze_panes=None

'''

import openpyxl

wb=openpyxl.load_workbook('freezeExample.xlsx')

sheet=wb.get_active_sheet()

sheet.freeze_panes='A1'

wb.save('freezeExample.xlsx')

############################################################################

'''

1、从一个矩形区域选择的单元格,创建一个Reference对象.

2、通过传入Reference对象,创建一个Series对象.

3、创建一个Chart对象

4、将Series对象添加到Chart对象

5、可选地设置Chart对象的drawing.top、drawing.left、drawing.width和drawing.height变量

6、将Chart对象添加到Worksheet对象

openpyxl.charts.Reference()函数传入3个参数：

1、包含图表数据的Worksheet对象

2、左上角单元格

3、右下角单元格

条形图：openpyxl.charts.BarChart()

折线图：openpyxl.charts.LineChart()

散点图：openpyxl.charts.ScatterChart()

饼图：openpyxl.charts.PieChart()

'''

import openpyxl

wb=openpyxl.Workbook()

sheet=wb.get_active_sheet()     ####create some data in column A

for i in range(1,11):

         sheet['A'+str(i)]=i

refObj=openpyxl.chart.Reference(sheet,min_col=1, min_row=1, max_col=10, max_row=1)

seriesObj=openpyxl.chart.Series(refObj,title='First series')

chartObj=openpyxl.chart.BarChart()

chartObj.append(seriesObj)

chartObj.top=50     ###set the position

chartObj.left=100

chartObj.width=300

chartObj.height=200

sheet.add_chart(chartObj)

wb.save('sampleChart.xlsx')

###########################测试###################################

import openpyxl

wb=openpyxl.Workbook()

wb.get_sheet_names()

sheet=wb.get_active_sheet()

sheet.title

sheet.title='Spam Bacon Eggs Sheet'

wb.get_sheet_names()

###########################修改名称################################

import openpyxl

wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

sheet=wb.get_active_sheet()

sheet.title='Spam Spam Spam'

wb.save('C:UsersAdministratorDesktopexample.xlsx')

###########################创建和删除工作表########################

import openpyxl

wb=openpyxl.Workbook()

wb.get_sheet_names()

wb.create_sheet()

wb.get_sheet_names()

wb.create_sheet(index=0,title=u'第1')

wb.get_sheet_names()

wb.create_sheet(index=2,title=u'第2')

wb.get_sheet_names()

wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet'))

wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))

wb.get_sheet_names()

###################################################################

import openpyxl

wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopexample.xlsx')

wb.create_sheet(index=0,title=u'第1')

wb.get_sheet_names()

wb.create_sheet(index=2,title=u'第2')

wb.get_sheet_names()

wb.save('C:UsersAdministratorDesktopexample.xlsx')

#########################将值写入单元格############################

import openpyxl

wb=openpyxl.Workbook()

sheet=wb.get_sheet_by_name('Sheet')

sheet['A1']='Hello world'

sheet['A1'].value

###########################更新一个电子表格#########################

'''

1、循环遍历所有行

2、如果该行是Garlic、Celey或Lemons,更新价格

'''

import openpyxl

wb=openpyxl.load_workbook(r'C:UsersAdministratorDesktopPrice.xlsx')

sheet=wb.get_sheet_by_name('Sheet1')

PRICE_UPDATES={'Garlic':3.07,'Celery':1.19,'Lemon':1.27}

for rowNum in range(2,sheet.get_highest_row()):

         produceName=sheet.cell(row=rowNum,column=1).value

         if produceName in PRICE_UPDATES:

                   sheet.cell(row=rowNum,column=2).value=PRICE_UPDATES[produceName]

wb.save('C:UsersAdministratorDesktopPrice.xlsx')

###########################从电子表格中读取表格#########################