Python读取Excel,里面如果是日期,直接读出来是float类型,无法直接使用。
通过判断读取表格的数据类型ctype,进一步处理。
返回的单元格内容的类型有5种:
ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
ctype =sheet1.cell(iRow,iCol).ctype
参考示例如下:
1.准备一个Excel文件,文件名Book1.xlsx
从第2行的第1列开始向右,分别是2019年的7月的1、2、3、4日,2019-07-01、2019-07-02、2019-07-03、2019-07-04
A列单元格的类型:date
B列单元格的类型:Text
C列单元格的类型:Text
D列单元格的类型:Custom里的一种日期格式
2.Python文件,ReadExcelDemo.py,代码如下:
#! -*- coding utf-8 -*-#! @Time :2019/7/4 15:46#! Author :Frank Zhang#! @File :ReadExcelDemo.py#!SoftWare PyChart 5.0.3#! Python Version 3.7
importxlrdimportosimporttimefrom datetime importdatetimefrom xlrd importxldate_as_tupledefmain():
sPath=os.getcwd()
sFile= "Book1.xlsx"wb= xlrd.open_workbook(filename=sPath + "\" +sFile)
sheet1=wb.sheet_by_index(0)
nrows=sheet1.nrows
ncols=sheet1.ncolsfor iRow in range(1,nrows):for iCol inrange(ncols):
sCell=sheet1.cell_value(iRow,iCol)#Python读Excel,返回的单元格内容的类型有5种:
#ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
ctype =sheet1.cell(iRow,iCol).ctype#ctype =3,为日期
if ctype == 3:
date= datetime(*xldate_as_tuple(sCell, 0))
cell= date.strftime("%Y-%m-%d") #("%Y/%m/%d %H:%M:%S")
print(cell)#ctype =1,为字符串
elif ctype == 1:ifisVaildDate(sCell):
t1= time.strptime(sCell, "%Y-%m-%d")
sDate= changeStrToDate(t1,"yyyy-mm-dd")print(sDate)else:pass
defformatDay(sDay,sFormat):
sYear=str(sDay.year)
sMonth=str(sDay.month)
sDay=str(sDay.day)if sFormat == "yyyy-mm-dd":
sFormatDay= sYear +"-" +sMonth.zfill(2)+"-" +sDay.zfill(2)elif sFormatStyle == "yyyy/mm/dd":
sFormatDay= sYear +"/" +sMonth.zfill(2)+"/" +sDay.zfill(2)else:
sFormatDay= sYear+"-" + sMonth + "-" +sDayreturnsFormatDay"""功能:判断是否为日期"""
defisVaildDate(sDate):try:if ":" insDate:
time.strptime(sDate,"%Y-%m-%d %H:%M:%S")else:
time.strptime(sDate,"%Y-%m-%d")returnTrueexcept:returnFalse"""功能:把字符串格式的日期转换为格式化的日期,如把2019-7-1转换为2019-07-01"""
defchangeStrToDate(sDate,sFormat):
sYear=str(sDate.tm_year)
sMonth=str(sDate.tm_mon)
sDay=str(sDate.tm_mday)if sFormat == "yyyy-mm-dd":
sFormatDay= sYear +"-" +sMonth.zfill(2)+"-" +sDay.zfill(2)elif sFormatStyle == "yyyy/mm/dd":
sFormatDay= sYear +"/" +sMonth.zfill(2)+"/" +sDay.zfill(2)else:
sFormatDay= sYear+"-" + sMonth + "-" +sDayreturnsFormatDayif __name__ == "__main__":
main()
3.执行结果:
原文地址:https://www.cnblogs.com/SH170706/p/11133525.html