xlrd處理excel特殊cell類型

>最近遇到一個(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') #保存文件

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市胶哲,隨后出現(xiàn)的幾起案子畔塔,更是在濱河造成了極大的恐慌,老刑警劉巖鸯屿,帶你破解...
    沈念sama閱讀 206,968評(píng)論 6 482
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件澈吨,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡寄摆,警方通過(guò)查閱死者的電腦和手機(jī)谅辣,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,601評(píng)論 2 382
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)婶恼,“玉大人桑阶,你說(shuō)我怎么就攤上這事」窗睿” “怎么了蚣录?”我有些...
    開(kāi)封第一講書人閱讀 153,220評(píng)論 0 344
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)眷篇。 經(jīng)常有香客問(wèn)我包归,道長(zhǎng),這世上最難降的妖魔是什么铅歼? 我笑而不...
    開(kāi)封第一講書人閱讀 55,416評(píng)論 1 279
  • 正文 為了忘掉前任公壤,我火速辦了婚禮,結(jié)果婚禮上椎椰,老公的妹妹穿的比我還像新娘厦幅。我一直安慰自己,他們只是感情好慨飘,可當(dāng)我...
    茶點(diǎn)故事閱讀 64,425評(píng)論 5 374
  • 文/花漫 我一把揭開(kāi)白布确憨。 她就那樣靜靜地躺著,像睡著了一般瓤的。 火紅的嫁衣襯著肌膚如雪休弃。 梳的紋絲不亂的頭發(fā)上,一...
    開(kāi)封第一講書人閱讀 49,144評(píng)論 1 285
  • 那天圈膏,我揣著相機(jī)與錄音塔猾,去河邊找鬼。 笑死稽坤,一個(gè)胖子當(dāng)著我的面吹牛丈甸,可吹牛的內(nèi)容都是我干的糯俗。 我是一名探鬼主播,決...
    沈念sama閱讀 38,432評(píng)論 3 401
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼睦擂,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼得湘!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起顿仇,我...
    開(kāi)封第一講書人閱讀 37,088評(píng)論 0 261
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤淘正,失蹤者是張志新(化名)和其女友劉穎,沒(méi)想到半個(gè)月后臼闻,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體跪帝,經(jīng)...
    沈念sama閱讀 43,586評(píng)論 1 300
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,028評(píng)論 2 325
  • 正文 我和宋清朗相戀三年些阅,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片斑唬。...
    茶點(diǎn)故事閱讀 38,137評(píng)論 1 334
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡市埋,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出恕刘,到底是詐尸還是另有隱情缤谎,我是刑警寧澤,帶...
    沈念sama閱讀 33,783評(píng)論 4 324
  • 正文 年R本政府宣布褐着,位于F島的核電站坷澡,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏含蓉。R本人自食惡果不足惜频敛,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,343評(píng)論 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望馅扣。 院中可真熱鬧斟赚,春花似錦、人聲如沸差油。這莊子的主人今日做“春日...
    開(kāi)封第一講書人閱讀 30,333評(píng)論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)蓄喇。三九已至发侵,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間妆偏,已是汗流浹背刃鳄。 一陣腳步聲響...
    開(kāi)封第一講書人閱讀 31,559評(píng)論 1 262
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留钱骂,地道東北人铲汪。 一個(gè)月前我還...
    沈念sama閱讀 45,595評(píng)論 2 355
  • 正文 我出身青樓熊尉,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親掌腰。 傳聞我的和親對(duì)象是個(gè)殘疾皇子狰住,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,901評(píng)論 2 345

推薦閱讀更多精彩內(nèi)容