>最近遇到一個(gè)情景廉嚼,就是定期生成并發(fā)送服務(wù)器使用情況報(bào)表玫镐,按照不同維度統(tǒng)計(jì),涉及python對(duì)excel的操作怠噪,上網(wǎng)搜羅了一番恐似,大多大同小異,而且不太能滿足需求傍念,不過(guò)經(jīng)過(guò)一番對(duì)源碼的"研究"(用此一詞讓我覺(jué)得頗有成就感)之后矫夷,基本解決了日常所需。主要記錄使用過(guò)程的常見(jiàn)問(wèn)題及解決憋槐。
>python操作excel主要用到xlrd和xlwt這兩個(gè)庫(kù)双藕,即xlrd是讀excel,xlwt是寫excel的庫(kù)阳仔∮桥悖可從這里下載https://pypi.python.org/pypi。下面分別記錄python讀和寫excel.
#### python讀excel——xlrd
這個(gè)過(guò)程有幾個(gè)比較麻煩的問(wèn)題,比如讀取日期嘶摊、讀合并單元格內(nèi)容延蟹。下面先看看基本的操作:
首先讀一個(gè)excel文件,有兩個(gè)sheet叶堆,測(cè)試用第二個(gè)sheet阱飘,sheet2內(nèi)容如下:
python 對(duì) excel基本的操作如下:
````
# -*- coding: utf-8 -*-
import xlrd
import xlwt
from datetime import date,datetime
def read_excel():
? ? # 打開(kāi)文件
? ? workbook = xlrd.open_workbook(r'F:\demo.xlsx')
? ? # 獲取所有sheet
? ? print workbook.sheet_names() # [u'sheet1', u'sheet2']
? ? sheet2_name = workbook.sheet_names()[1]
? ? # 根據(jù)sheet索引或者名稱獲取sheet內(nèi)容
? ? sheet2 = workbook.sheet_by_index(1) # sheet索引從0開(kāi)始
? ? sheet2 = workbook.sheet_by_name('sheet2')
? ? # sheet的名稱,行數(shù)虱颗,列數(shù)
? ? print sheet2.name,sheet2.nrows,sheet2.ncols
? ? # 獲取整行和整列的值(數(shù)組)
? ? rows = sheet2.row_values(3) # 獲取第四行內(nèi)容
? ? cols = sheet2.col_values(2) # 獲取第三列內(nèi)容
? ? print rows
? ? print cols
? ? # 獲取單元格內(nèi)容
? ? print sheet2.cell(1,0).value.encode('utf-8')
? ? print sheet2.cell_value(1,0).encode('utf-8')
? ? print sheet2.row(1)[0].value.encode('utf-8')
? ? # 獲取單元格內(nèi)容的數(shù)據(jù)類型
? ? print sheet2.cell(1,0).ctype
if __name__ == '__main__':
? ? read_excel()
````
那么問(wèn)題來(lái)了沥匈,上面的運(yùn)行結(jié)果中紅框框中的字段明明是出生日期,可顯示的確實(shí)浮點(diǎn)數(shù)忘渔。好的高帖,來(lái)解決第一個(gè)問(wèn)題:
1、python讀取excel中單元格內(nèi)容為日期的方式
python讀取excel中單元格的內(nèi)容返回的有5種類型辨萍,即上面例子中的ctype:
ctype :?
```
0 empty,
1 string,
2 number,
3 date,
4 boolean,
5 error
```
即date的ctype=3棋恼,這時(shí)需要使用xlrd的xldate_as_tuple來(lái)處理為date格式,先判斷表格的ctype=3時(shí)xldate才能開(kāi)始操作⌒庥瘢現(xiàn)在命令行看下:
> sheet2.cell(2,2).ctype? #1990/2/22
3
> sheet2.cell(2,1).ctype? #24
2
> sheet2.cell(2,0).ctype? #小胖
1
> sheet2.cell(2,4).ctype? #空值(這里是合并單元格的原因)
0
> sheet2.cell(2,2).value? #1990/2/22
33656.0
> xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)
(1992, 2, 22, 0, 0, 0)
> date_value = xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)
> date_value
(1992, 2, 22, 0, 0, 0)
> date(*date_value[:3])
datetime.date(1992, 2, 22)
> date(*date_value[:3]).strftime('%Y/%m/%d')
'1992/02/22'
即可以做下簡(jiǎn)單處理爪飘,判斷ctype是否等于3,如果等于3拉背,則用時(shí)間格式處理:
if (sheet.cell(row,col).ctype == 3):
? ? date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),book.datemode)
? ? date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
那么問(wèn)題又來(lái)了师崎,上面 sheet2.cell(2,4).ctype 返回的值是0,說(shuō)明這個(gè)單元格的值是空值椅棺,明明是合并的單元格內(nèi)容"好朋友"犁罩,這個(gè)是我覺(jué)得這個(gè)包功能不完善的地方,如果是合并的單元格那么應(yīng)該合并的單元格的內(nèi)容一樣两疚,但是它只是合并的第一個(gè)單元格的有值床估,其它的為空。
>>> sheet2.col_values(4)
[u'\u5173\u7cfb', u'\u597d\u670b\u53cb', '', u'\u540c\u5b66', '', '', u'\u4e00\u4e2a\u4eba', '']
>>> for i in range(sheet2.nrows):
? ? print sheet2.col_values(4)[i]
關(guān)系
好朋友
同學(xué)
一個(gè)人
>>> sheet2.row_values(7)
[u'\u65e0\u540d', 20.0, u'\u6682\u65e0', '', '']
>>> for i in range(sheet2.ncols):
? ? print sheet2.row_values(7)[i]
無(wú)名
20.0
暫無(wú)
>>>
2诱渤、讀取合并單元格的內(nèi)容
這個(gè)是真沒(méi)技巧丐巫,只能獲取合并單元格的第一個(gè)cell的行列索引,才能讀到值勺美,讀錯(cuò)了就是空值递胧。
即合并行單元格讀取行的第一個(gè)索引,合并列單元格讀取列的第一個(gè)索引赡茸,如上述缎脾,讀取行合并單元格"好朋友"和讀取列合并單元格"暫無(wú)"只能如下方式:
>>> print sheet2.col_values(4)[1]
好朋友
>>> print sheet2.row_values(7)[2]
暫無(wú)
>>> sheet2.merged_cells # 明明有合并的單元格,為何這里是空
[]
疑問(wèn)又來(lái)了占卧,合并單元格可能出現(xiàn)空值遗菠,但是表格本身的普通單元格也可能是空值联喘,要怎么獲取單元格所謂的"第一個(gè)行或列的索引"呢?
這就要先知道哪些是單元格是被合并的辙纬!
3耸袜、獲取合并的單元格
讀取文件的時(shí)候需要將formatting_info參數(shù)設(shè)置為True,默認(rèn)是False,所以上面獲取合并的單元格數(shù)組為空散吵,
>>> workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)
>>> sheet2 = workbook.sheet_by_name('sheet2')
>>> sheet2.merged_cells
[(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]
merged_cells返回的這四個(gè)參數(shù)的含義是:(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一樣型宝,即(1, 3, 4, 5)的含義是:第1到2行(不包括3)合并,(7, 8, 2, 5)的含義是:第2到4列合并却汉。
利用這個(gè),可以分別獲取合并的三個(gè)單元格的內(nèi)容:
>>> print sheet2.cell_value(1,4)? #(1, 3, 4, 5)
好朋友
>>> print sheet2.cell_value(3,4)? #(3, 6, 4, 5)
同學(xué)
>>> print sheet2.cell_value(7,2)? #(7, 8, 2, 5)
暫無(wú)
發(fā)現(xiàn)規(guī)律了沒(méi)?是的昂儒,獲取merge_cells返回的row和col低位的索引即可! 于是可以這樣一勞永逸:
>>> merge = []
>>> for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:
? ? merge.append([rlow,clow])
>>> merge
[[7, 2], [1, 4], [3, 4]]
>>> for index in merge:
? ? print sheet2.cell_value(index[0],index[1])
暫無(wú)
好朋友
同學(xué)
>>>
python寫excel——xlwt
寫excel的難點(diǎn)可能不在構(gòu)造一個(gè)workbook的本身委可,而是填充的數(shù)據(jù)渊跋,不過(guò)這不在范圍內(nèi)。在寫excel的操作中也有棘手的問(wèn)題着倾,比如寫入合并的單元格就是比較麻煩的拾酝,另外寫入還有不同的樣式。這些要看源碼才能研究的透卡者。
我"構(gòu)思"了如下面的sheet1蒿囤,即要用xlwt實(shí)現(xiàn)的東西:
基本上看起來(lái)還算復(fù)雜,而且看起來(lái)"很正規(guī)"崇决,完全是個(gè)人杜撰材诽。
代碼如下:
'''
設(shè)置單元格樣式
'''
def set_style(name,height,bold=False):
? ? style = xlwt.XFStyle()? # 初始化樣式
? ? font = xlwt.Font()? # 為樣式創(chuàng)建字體
? ? font.name = name # 'Times New Roman'
? ? font.bold = bold
? ? font.color_index = 4
? ? font.height = height
? ? # borders= xlwt.Borders()
? ? # borders.left= 6
? ? # borders.right= 6
? ? # borders.top= 6
? ? # borders.bottom= 6
? ? style.font = font
? ? # style.borders = borders
? ? return style
#寫excel
def write_excel():
? ? f = xlwt.Workbook() #創(chuàng)建工作簿
? ? '''
? ? 創(chuàng)建第一個(gè)sheet:
? ? ? ? sheet1
? ? '''
? ? sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #創(chuàng)建sheet
? ? row0 = [u'業(yè)務(wù)',u'狀態(tài)',u'北京',u'上海',u'廣州',u'深圳',u'狀態(tài)小計(jì)',u'合計(jì)']
? ? column0 = [u'機(jī)票',u'船票',u'火車票',u'汽車票',u'其它']
? ? status = [u'預(yù)訂',u'出票',u'退票',u'業(yè)務(wù)小計(jì)']
? ? #生成第一行
? ? for i in range(0,len(row0)):
? ? ? ? sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))
? ? #生成第一列和最后一列(合并4行)
? ? i, j = 1, 0
? ? while i < 4*len(column0) and j < len(column0):
? ? ? ? sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列
? ? ? ? sheet1.write_merge(i,i+3,7,7) #最后一列"合計(jì)"
? ? ? ? i += 4
? ? ? ? j += 1
? ? sheet1.write_merge(21,21,0,1,u'合計(jì)',set_style('Times New Roman',220,True))
? ? #生成第二列
? ? i = 0
? ? while i < 4*len(column0):
? ? ? ? for j in range(0,len(status)):
? ? ? ? ? ? sheet1.write(j+i+1,1,status[j])
? ? ? ? i += 4
? ? f.save('demo1.xlsx') #保存文件
if __name__ == '__main__':
? ? #generate_workbook()
? ? #read_excel()
? ? write_excel()
需要稍作解釋的就是write_merge方法:
write_merge(x, x + m, y, w + n, string, sytle)
x表示行,y表示列恒傻,m表示跨行個(gè)數(shù)脸侥,n表示跨列個(gè)數(shù),string表示要寫入的單元格內(nèi)容盈厘,style表示單元格樣式睁枕。其中,x扑庞,y譬重,w,h罐氨,都是以0開(kāi)始計(jì)算的臀规。
這個(gè)和xlrd中的讀合并單元格的不太一樣。
如上述:sheet1.write_merge(21,21,0,1,u'合計(jì)',set_style('Times New Roman',220,True))
即在22行合并第1,2列栅隐,合并后的單元格內(nèi)容為"合計(jì)"塔嬉,并設(shè)置了style玩徊。
如果需要?jiǎng)?chuàng)建多個(gè)sheet,則只要f.add_sheet即可谨究。
如在上述write_excel函數(shù)里f.save('demo1.xlsx') 這句之前再創(chuàng)建一個(gè)sheet2恩袱,效果如下:
代碼也是真真的easy的了:
? ? '''
? ? 創(chuàng)建第二個(gè)sheet:
? ? ? ? sheet2
? ? '''
? ? sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #創(chuàng)建sheet2
? ? row0 = [u'姓名',u'年齡',u'出生日期',u'愛(ài)好',u'關(guān)系']
? ? column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'無(wú)名']
? ? #生成第一行
? ? for i in range(0,len(row0)):
? ? ? ? sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True))
? ? #生成第一列
? ? for i in range(0,len(column0)):
? ? ? ? sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220))
? ? sheet2.write(1,2,'1991/11/11')
? ? sheet2.write_merge(7,7,2,4,u'暫無(wú)') #合并列單元格
? ? sheet2.write_merge(1,2,4,4,u'好朋友') #合并行單元格
? ? f.save('demo1.xlsx') #保存文件