Pythonexcel日期格式

在python中操作execl进⾏数据读写的时候，可以使⽤xlrd进⾏⽂件的读取，使⽤xlwt将数据写⼊execl中。1.xlrd

xlwt⽤来读取execl中的数据，常见的⽤法如下。

（1）打开execl⽂件并获取所有的sheet

>>> import xlrd

>>> workbook = xlrd.open_workbook(r'D:\\Program Files\\Notepad++\\Student.xlsx')>>> print workbook.sheet_names()[u'Sheet1', u'Sheet2', u'Sheet3']

（2）根据下标获取sheet名称

>>> sheet2_name=workbook.sheet_names()[1]>>> print sheet2_nameSheet2

（3）根据sheet索引或者名称获取sheet内容，同时获取sheet名称、⾏数、列数

>>> sheet2 = workbook.sheet_by_index(1)

>>> print sheet2.name,sheet2.nrows,sheet2.ncolsSheet2 6 5

>>> sheet2 = workbook.sheet_by_name('Sheet2')>>> print sheet2.name,sheet2.nrows,sheet2.ncolsSheet2 6 5

（4）根据sheet名称获取整⾏和整列的值

>>> sheet2 = workbook.sheet_by_name('Sheet2')>>> rows = sheet2.row_values(3)>>> cols = sheet2.col_values(2)>>> print rows

[u'lisi2', 19.0, 41462.0, u'basketball', u'friend2'] #标红部分为⽇期2013/7/7，实际却显⽰为浮点数。后⾯有描述如何纠正>>> print cols

[u'\出\生\日\期', 42129.0, 41796.0, 41462.0, 40941.0, u''] # 问题同上

（5）获取指定单元格的内容

>>> print sheet2.cell(1,0).value.encode('utf-8')xiaoming2

>>> print sheet2.cell_value(1,0).encode('utf-8')xiaoming2

>>> print sheet2.row(1)[0].value.encode('utf-8')xiaoming2

（6）获取单元格内容的数据类型

>>> print sheet2.cell(1,0).ctype #第2⾏第1列:xiaoming2 为string类型1

>>> print sheet2.cell(1,1).ctype #第2⾏第2列:12 为number类型2

>>> print sheet2.cell(1,2).ctype #第2⾏第3列:2015/5/5 为date类型3

（7）获取单元格内容为⽇期类型的⽅式

使⽤xlrd的xldate_as_tuple处理为date格式，先判断表格的ctype=3时xlrd才能执⾏操作，如下：

>>> from datetime import datetime,date>>> sheet2.cell(1,2).ctype3

>>> sheet2.cell(1,2).value42129.0

>>> xlrd.xldate_as_tuple(sheet2.cell_value(1,2),workbook.datemode)(2015, 5, 5, 0, 0, 0)

>>> date_value = xlrd.xldate_as_tuple(sheet2.cell_value(1,2),workbook.datemode)>>> date(*date_value[:3])datetime.date(2015, 5, 5)

>>> date(*date_value[:3]).strftime('%Y/%m/%d')'2015/05/05'

那么如果是在脚本中需要获取并显⽰单元格内容为⽇期类型的，可以先做⼀个判断。判断ctype是否等于3，如果等于3，则⽤时间格式处理：

if (sheet.cell(row,col).ctype == 3):

date_value = xlrd.xldate_as_tuple(sheet.cell_value(row,col),book.datemode)

date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')

（8）获取合并单元格的内容

>>> sheet2.cell(1,4).value #第4列的第2⾏和第3⾏是合并单元格u'friend'

>>> sheet2.cell(2,4).valueu''

>>> sheet2.cell(5,1).value #第6⾏的第2和第3第4列是合并单元格，这⾥我们只获取到第6⾏第2列的值⽽第3列第4列获取的内容为空，如何处理?u'None'

>>> sheet2.cell(5,2).valueu''

>>> sheet2.cell(5,3).valueu''

从实验结果可以看出来，第6⾏的第2和第3第4列是合并单元格，但这⾥我们只获取到第6⾏第2列的值⽽第3列第4列获取的内容为空，理论上来说合并的单元格内容应该是⼀样的，但是现在只有合并的第⼀个单元格可以获取到值，其他为空，如何处理? 再⽤⼀种更直观的⽅式显⽰

>>> sheet2.row_values(5)

[u'zhaoliu2', u'None', u'', u'', u''] #标红的部分为合并单元格>>> sheet2.col_values(4)

[u'\关\系', u'friend', u'', u'friend2', u'', u''] #标红的部分为合并单元格，注意这⾥是两个合并单元格

可以利⽤merged_cells⽅法进⾏处理，处理的⽅法是只能获取合并单元格的第⼀个cell的⾏列索引，才能读到值，读错了就是空值。即合并⾏单元格读取⾏的第⼀个索引，合并列单元格读取列的第⼀个索引。这⾥，需要在读取⽂件的时候添加个参数，将formatting_info参数设置为True，默认是False，否则可能调⽤merged_cells⽅法获取到的是空值。

>>> workbook = xlrd.open_workbook(r'D:\\Program Files\\Notepad++\\Student.xlsx',formatting_info=True) >>> sheet2 = workbook.sheet_by_name('sheet2') >>> sheet2.merged_cells

[(1, 3, 4, 5), (3, 5, 4, 5), (5, 6, 1, 5)]

merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是⼀样，下标从0开始。即(1, 3, 4, 5)的含义是：第2到3⾏（不包括第4⾏）合并，(5, 6, 1, 5)的含义是：第2到5列合并。利⽤这个，可以分别获取合并的三个单元格的内容：

>>> print sheet2.cell_value(1,4) #(1, 3, 4, 5)friend

>>> print sheet2.cell_value(3,4) #(3, 5, 4, 5)friend2

>>> print sheet2.cell_value(5,1) #(5, 6, 1, 5)None

2.xlwt

xlwt⽤来往execl中写⼊数据，功能还是⽐较丰富的。先来看⼀个⽐较简单的例⼦。

import xlwt

# 创建⼀个workbook 设置编码

workbook = xlwt.Workbook(encoding = 'utf-8')# 创建⼀个worksheet

worksheet = workbook.add_sheet('My Worksheet')# 写⼊excel

# 参数对应 ⾏, 列, 值

worksheet.write(1,0, label = 'this is test')# 保存

workbook.save('Excel_test.xls')

运⾏之后，就会在当前⽬录之下⽣成⼀个叫做“Excel_test.xls”的⽂件。当然你还可以做⼀些样式上⾯的更改。（1）设置字体的样式

import xlwt

workbook = xlwt.Workbook(encoding = 'ascii')

worksheet = workbook.add_sheet('My Worksheet')

style = xlwt.XFStyle() # 初始化样式font = xlwt.Font() # 为样式创建字体font.name = 'Times New Roman' font.bold = True # ⿊体

font.underline = True # 下划线font.italic = True # 斜体字style.font = font # 设定样式

worksheet.write(0, 0, 'Unformatted value') # 不带样式的写⼊worksheet.write(1, 0, 'Formatted value', style) # 带样式的写⼊workbook.save('formatting.xls') # 保存⽂件

（2）设置单元格宽度

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')

worksheet.write(0, 0,'My Cell Contents')# 设置单元格宽度

worksheet.col(0).width = 3333workbook.save('cell_width.xls')

（3）输出⼀个⽇期到单元格

import xlwt

import datetime

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')style = xlwt.XFStyle()

#设置⽇期的格式

style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0worksheet.write(0, 0, datetime.datetime.now(), style)workbook.save('Excel_Workbook.xls')

（4）向单元格添加⼀个公式

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')worksheet.write(0, 0, 5) # Outputs 5worksheet.write(0, 1, 2) # Outputs 2

worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output \"10\" (A1[5] * A2[2])

worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output \"7\" (A1[5] + A2[2])workbook.save('Excel_Workbook.xls')

（5）向单元格添加⼀个超链接

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')

worksheet.write(0, 0, xlwt.Formula('HYPERLINK(\"http://www.google.com\";\"Google\")')) # Outputs the text \"Google\" linking to http://www.google.comworkbook.save('Excel_Workbook.xls')

（6）合并列和⾏

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')

worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3. 合并第⼀⾏的0到3列font = xlwt.Font() # Create Font #创建⼀个样式font.bold = True # Set font to Boldstyle = xlwt.XFStyle() # Create Stylestyle.font = font # Add Bold Font to Style

worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # 合并1到2⾏的0列到3列workbook.save('Excel_Workbook.xls')

（7）设置单元格内容的对齐⽅式

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')alignment = xlwt.Alignment() # Create Alignment

alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTEDalignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTEDstyle = xlwt.XFStyle() # Create Style

style.alignment = alignment # Add Alignment to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')

（8）为单元格添加边框

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')borders = xlwt.Borders() # Create Bordersborders.left = xlwt.Borders.DASHED DASHED虚线 NO_LINE没有 THIN实线

# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUMborders.right = xlwt.Borders.DASHEDborders.top = xlwt.Borders.DASHEDborders.bottom = xlwt.Borders.DASHEDborders.left_colour = 0x40borders.right_colour = 0x40borders.top_colour = 0x40borders.bottom_colour = 0x40

style = xlwt.XFStyle() # Create Style

style.borders = borders # Add Borders to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')

（9）为单元格设置背景⾊

import xlwt

workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('My Sheet')pattern = xlwt.Pattern() # Create the Pattern

pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12

pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenstyle = xlwt.XFStyle() # Create the Patternstyle.pattern = pattern # Add Pattern to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')