Python读取Excel,日期列读出来是数字的处理

向右看齐 2023-10-11 13:38 142阅读 0赞

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

![934739-20190704165836948-1026069248.png][]

从第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
    import xlrd
    import os
    import time
    from datetime import datetime
    from xlrd import xldate_as_tuple
    
    def main():
        sPath = os.getcwd()
        sFile = "Book1.xlsx"
        wb = xlrd.open_workbook(filename=sPath + "\\" + sFile)
        sheet1 = wb.sheet_by_index(0)
        nrows = sheet1.nrows
        ncols = sheet1.ncols
        
        for iRow in range(1,nrows):
            for iCol in range(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: 
                    if isVaildDate(sCell):
                        t1 = time.strptime(sCell, "%Y-%m-%d")
                        sDate = changeStrToDate(t1,"yyyy-mm-dd")
                        print(sDate)
                else:
                        pass
    
    def formatDay(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 + "-" + sDay
            
        return sFormatDay
    
    """
    功能：判断是否为日期
    """
    def isVaildDate(sDate):
        try:
            if ":" in sDate:
                time.strptime(sDate, "%Y-%m-%d %H:%M:%S")
            else:
                time.strptime(sDate, "%Y-%m-%d")
            return True
        except:
            return False
    
    """
       功能：把字符串格式的日期转换为格式化的日期，如把2019-7-1转换为2019-07-01
    """
    def changeStrToDate(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 + "-" + sDay
            
        return sFormatDay
    
    if __name__ == "__main__":
        main()

3.执行结果：

![934739-20190704170341429-671300551.png][]

转载于:https://www.cnblogs.com/SH170706/p/11133525.html

[934739-20190704165836948-1026069248.png]: https://img2018.cnblogs.com/blog/934739/201907/934739-20190704165836948-1026069248.png
[934739-20190704170341429-671300551.png]: https://img2018.cnblogs.com/blog/934739/201907/934739-20190704170341429-671300551.png