您当前的位置:首页 > 计算机 > 编程开发 > Python

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

时间:08-18来源:作者:点击数:
城东书院 www.cdsy.xyz

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

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import xlrd
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import traceback
from datetime import datetime
from xlrd import xldate_as_tuple
from collections import OrderedDict


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)

                if ctype == 2 and cell % 1 != 0:  # 如果是浮点型
                    cell = round(cell,1)

                elif ctype == 3:
                    # 转成datetime对象
                    date = datetime(*xldate_as_tuple(cell, 0))
                    # cell = date.strftime('%Y/%d/%m %H:%M:%S')
                    cell = date.strftime('%Y/%d/%m')
                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 = '单包收入明细_new.xlsx'
    sheetname = '工作表1'


    all_list_data = eh.read_excel(filename, sheetname)

 

城东书院 www.cdsy.xyz
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐