• python读取excel,数字都是浮点型,日期格式是数字的解决办法


    excel文件内容:

    读取excel:

    # coding=utf-8
    import xlrd
    import sys
    
    reload(sys)
    sys.setdefaultencoding('utf-8')
    import traceback
    
    
    class excelHandle:
        def decode(self, filename, sheetname):
            try:
                filename = filename.decode('utf-8')
                sheetname = sheetname.decode('utf-8')
            except Exception:
                print traceback.print_exc()
            return filename, sheetname
    
        def read_excel(self, filename, sheetname):
            filename, sheetname = self.decode(filename, sheetname)
            rbook = xlrd.open_workbook(filename)
            sheet = rbook.sheet_by_name(sheetname)
            rows = sheet.nrows
            cols = sheet.ncols
            all_content = []
            for i in range(rows):
                row_content = []
                for j in range(cols):
                    cell = sheet.cell_value(i, j)
                    row_content.append(cell)
                all_content.append(row_content)
                print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'
            return all_content
    
    
    if __name__ == '__main__':
        eh = excelHandle()
        filename = r'G:	estctype.xls'
        sheetname = 'Sheet1'
        eh.read_excel(filename, sheetname)

    输出:

    ['整形','175.0']
    ['字符串','最后的骑士']
    ['浮点型','6.23']
    ['日期','42909.6461574']
    ['空值','']
    ['布尔型','1']

    可以看到,数字一律按浮点型输出,日期却输出成一串小数?!布尔型输出0或1

    代码稍做改动:来看一看表格的数据类型

            for i in range(rows):
                row_content = []
                for j in range(cols):
                    ctype = sheet.cell(i, j).ctype #表格的数据类型
                    print ctype,
                    cell = sheet.cell_value(i, j)
                    row_content.append(cell)
                all_content.append(row_content)
                print
                print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'

    输出:

    1 2
    ['整形','175.0']
    1 1
    ['字符串','最后的骑士']
    1 2
    ['浮点型','6.23']
    1 3
    ['日期','42909.6461574']
    1 0
    ['空值','']
    1 4
    ['布尔型','1']

    python读取excel中单元格的内容返回的有5种类型,即上面例子中的ctype:


    ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

    所以,判断一下ctype,然后再做相应处理就可以了。

    最终代码:

    # coding=utf-8
    import xlrd
    import sys
    
    reload(sys)
    sys.setdefaultencoding('utf-8')
    import traceback
    from datetime import datetime
    from xlrd import xldate_as_tuple
    
    
    class excelHandle:
        def decode(self, filename, sheetname):
            try:
                filename = filename.decode('utf-8')
                sheetname = sheetname.decode('utf-8')
            except Exception:
                print traceback.print_exc()
            return filename, sheetname
    
        def read_excel(self, filename, sheetname):
            filename, sheetname = self.decode(filename, sheetname)
            rbook = xlrd.open_workbook(filename)
            sheet = rbook.sheet_by_name(sheetname)
            rows = sheet.nrows
            cols = sheet.ncols
            all_content = []
            for i in range(rows):
                row_content = []
                for j in range(cols):
                    ctype = sheet.cell(i, j).ctype  # 表格的数据类型
                    cell = sheet.cell_value(i, j)
                    if ctype == 2 and cell % 1 == 0:  # 如果是整形
                        cell = int(cell)
                    elif ctype == 3:
                        # 转成datetime对象
                        date = datetime(*xldate_as_tuple(cell, 0))
                        cell = date.strftime('%Y/%d/%m %H:%M:%S')
                    elif ctype == 4:
                        cell = True if cell == 1 else False
                    row_content.append(cell)
                all_content.append(row_content)
                print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'
            return all_content
    
    
    if __name__ == '__main__':
        eh = excelHandle()
        filename = r'G:	estctype.xls'
        sheetname = 'Sheet1'
        eh.read_excel(filename, sheetname)

    输出:

    ['整形','175']
    ['字符串','最后的骑士']
    ['浮点型','6.23']
    ['日期','2017/23/06 15:30:28']
    ['空值','']
    ['布尔型','True']

    更多操作excel可参考:http://www.2cto.com/kf/201501/373655.html

  • 相关阅读:
    CentOS7 Python2.7.5升级3.7.1
    kubernets 集群和本地环境联调环境打通工具kt-connect
    Python学习指南
    Python爬虫(十九)_动态HTML介绍
    Python爬虫(十八)_多线程糗事百科案例
    Python爬虫(十七)_糗事百科案例
    Python爬虫(十六)_JSON模块与JsonPath
    python爬虫基本原理及入门
    Python操作数据库
    Python解析xml
  • 原文地址:https://www.cnblogs.com/xxiong1031/p/7069006.html
Copyright © 2020-2023  润新知