python 处理excel时间

最新推荐文章于 2024-07-24 17:01:10 发布
scan724
最新推荐文章于 2024-07-24 17:01:10 发布
阅读量3.1k
点赞数
本文链接：https://blog.csdn.net/zhaoyangjian724/article/details/90645953
版权
# !/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
import pycontrol.pycontrol as pc
import time
import csv
import ssl
from datetime import date, datetime
import urllib2
import urllib
import cookielib
import json
import httplib
import json
import datetime
import re
import requests
import StringIO
import time
import sys
import json
import re
import hashlib
import xlrd
class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)
def batch_upload(a, b, c):
    s = requests.session()
    cookies = requests.utils.dict_from_cookiejar(s.cookies)
    print cookies
    ##比如要批量关闭CNAPS的所有监控
    username = a
    password = b
    arr01=c
    #
    ctime=json.dumps(arr01[5], cls=DateEncoder)
    ctime=ctime.replace('\"','')
    dtime=json.dumps(arr01[9], cls=DateEncoder)
    dtime = dtime.replace('\"', '')
    print 'ctime'
    print ctime
    print 'ctime'
    print 'dtime'
    print dtime
    print 'dtime'
    myurl1 = 'http://xxxxxxx.171:8080/balantflow/restservices/addTaskApi'
    #data = {'j_username': username, 'j_password': password}
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0',
        #'Referer': 'http://xxxxxxx.171:8080/balantflow/',
        'Content-Type': 'application/json;charset=utf-8',
        'X-Requested-With': 'XMLHttpRequest'

    }
    data = {
        "title": arr01[0],
        "content": arr01[1],
        ##事件上报通道
        "channelId": "17",
        # 事件影响级别,默认8级
        "urgency": "1008",
        "owner": 'xxx',
        "properties": [
            {
                "key": "type",

                "value": arr01[3]

            },
            {
                "key": "system",

                "value": arr01[2]

            },
            # level系统级别
            {
                "key": "level",
                "value": "一般"
            },
            {
                "key": "source",

                "value": arr01[4]

            },
            {
                "key": "matrix",
                "value": "系统预警"
            },

            {
                "key": "alerttime",
                "value": ctime
            },
            {
                "key": "caseReason",
                "value": arr01[6]
            },
            {
                "key": "timeCost",
                "value": arr01[7]
            },
            {
                "key": "caseType",
                "value": arr01[8]
            },
            {
                "key": "recoverTime",
                "value": dtime
            }
        ]
    }
    responce = s.post(myurl1, data=json.dumps(data), headers=headers, timeout=10)
    print responce.content
    print responce.status_code
def read_excel(a):
    wb = xlrd.open_workbook(filename=a)  # 打开文件
    print wb.sheet_names()  ##获取所有sheet页名字
    print type(wb.sheet_names())
    for x in wb.sheet_names():
        print type(x)
        print  x.encode('utf-8')
    sheet1 = wb.sheet_by_name(u'批量提交')
    print sheet1.name, sheet1.ncols, sheet1.nrows
    b = sheet1.nrows
    list = [[] for i in range(b)]
    for i in range(sheet1.nrows):
        if i == 0:
            pass
        else:
            print sheet1.row(i)
            print type(sheet1.row(i))
            for j in xrange(len(sheet1.row(i))):
                # if j == 5:
                #     # print  sheet1.row(i)[j].value
                #     # print  sheet1.row(i)[j].value
                #     ##excel日期转换,
                #     print xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0)
                #     #time.sleep(10)
                #     list[i].append(xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0))
                #     # print  sheet1.row(i)[j].value.ctype
                #     # print date(sheet1.row(i)[j].value).strftime("%Y-%m-%d %H:%M:%S")
                # elif j == 9:
                #     print xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0)
                #     # time.sleep(10)
                #     list[i].append(xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0))
                # else:
                #     #print  sheet1.row(i)[j].value
                    list[i].append(sheet1.row(i)[j].value)
    return list


##删除空数组
newlist = read_excel('test.xlsx')
while [] in newlist:
    newlist.remove([])
#print newlist
for x in newlist:
    #print batch_upload('xxx','xxx',x)
    for y in x:
        print y

       
       
C:\Python27\python.exe C:/Users/tlcb/PycharmProjects/untitled/excel/t5.py
[u'\u6279\u91cf\u63d0\u4ea4', u'\u767b\u8bb0']
<type 'list'>
<type 'unicode'>
批量提交
<type 'unicode'>
登记
批量提交 10 2
[text:u'\u6570\u636e\u5e93SQL\u95ee\u9898\u534f\u52a9\u67e5\u770b', text:u'\u6570\u636e\u5e93SQL\u53cd\u5e94\u6162', text:u'\u589e\u503c\u7a0e\u7ba1\u7406\u7cfb\u7edf', text:u'\u5355\u4e2a\u7528\u6237', text:u'\u5176\u4ed6', xldate:43600.4930555556, text:u'\u4e1a\u52a1\u90e8\u9700\u8981\u8c03\u6574', number:60.0, text:u'\u54a8\u8be2\u5efa\u8bae', xldate:43600.5347222222]
<type 'list'>
数据库SQL问题协助查看
数据库SQL反应慢
增值税管理系统
单个用户
其他
43600.4930556
业务部需要调整
60.0
咨询建议
43600.5347222

Process finished with exit code 0


python 读取excel时间方式:
业务系统value    影响范围value    事件来源value    custom_alertime    事件原因与处置方式    处置耗时(分钟)    事件分类    恢复时间
增值税管理系统    单个用户    其他    2019-5-15 11:50    业务部需要调整    60    咨询建议    2019-5-15 12:50
                           

                   # !/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
import pycontrol.pycontrol as pc
import time
import csv
import ssl
from datetime import date, datetime
import urllib2
import urllib
import cookielib
import json
import httplib
import json
import datetime
import re
import requests
import StringIO
import time
import sys
import json
import re
import hashlib
import xlrd
class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)
def batch_upload(a, b, c):
    s = requests.session()
    cookies = requests.utils.dict_from_cookiejar(s.cookies)
    print cookies
    ##比如要批量关闭CNAPS的所有监控
    username = a
    password = b
    arr01=c
    #
    ctime=json.dumps(arr01[5], cls=DateEncoder)
    ctime=ctime.replace('\"','')
    dtime=json.dumps(arr01[9], cls=DateEncoder)
    dtime = dtime.replace('\"', '')
    print 'ctime'
    print ctime
    print 'ctime'
    print 'dtime'
    print dtime
    print 'dtime'
    myurl1 = 'http://xxxxxxx.171:8080/balantflow/restservices/addTaskApi'
    #data = {'j_username': username, 'j_password': password}
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0',
        #'Referer': 'http://xxxxxxx.171:8080/balantflow/',
        'Content-Type': 'application/json;charset=utf-8',
        'X-Requested-With': 'XMLHttpRequest'

    }
    data = {
        "title": arr01[0],
        "content": arr01[1],
        ##事件上报通道
        "channelId": "17",
        # 事件影响级别,默认8级
        "urgency": "1008",
        "owner": 'xxx',
        "properties": [
            {
                "key": "type",

                "value": arr01[3]

            },
            {
                "key": "system",

                "value": arr01[2]

            },
            # level系统级别
            {
                "key": "level",
                "value": "一般"
            },
            {
                "key": "source",

                "value": arr01[4]

            },
            {
                "key": "matrix",
                "value": "系统预警"
            },

            {
                "key": "alerttime",
                "value": ctime
            },
            {
                "key": "caseReason",
                "value": arr01[6]
            },
            {
                "key": "timeCost",
                "value": arr01[7]
            },
            {
                "key": "caseType",
                "value": arr01[8]
            },
            {
                "key": "recoverTime",
                "value": dtime
            }
        ]
    }
    responce = s.post(myurl1, data=json.dumps(data), headers=headers, timeout=10)
    print responce.content
    print responce.status_code
def read_excel(a):
    wb = xlrd.open_workbook(filename=a)  # 打开文件
    print wb.sheet_names()  ##获取所有sheet页名字
    print type(wb.sheet_names())
    for x in wb.sheet_names():
        print type(x)
        print  x.encode('utf-8')
    sheet1 = wb.sheet_by_name(u'批量提交')
    print sheet1.name, sheet1.ncols, sheet1.nrows
    b = sheet1.nrows
    list = [[] for i in range(b)]
    for i in range(sheet1.nrows):
        if i == 0:
            pass
        else:
            print sheet1.row(i)
            print type(sheet1.row(i))
            for j in xrange(len(sheet1.row(i))):
                if j == 5:
                    # print  sheet1.row(i)[j].value
                    # print  sheet1.row(i)[j].value
                    ##excel日期转换,
                    print xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0)
                    #time.sleep(10)
                    list[i].append(xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0))
                    # print  sheet1.row(i)[j].value.ctype
                    # print date(sheet1.row(i)[j].value).strftime("%Y-%m-%d %H:%M:%S")
                elif j == 9:
                    print xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0)
                    # time.sleep(10)
                    list[i].append(xlrd.xldate_as_datetime(sheet1.row(i)[j].value, 0))
                else:
                    #print  sheet1.row(i)[j].value
                    list[i].append(sheet1.row(i)[j].value)
    return list


##删除空数组
newlist = read_excel('test.xlsx')
while [] in newlist:
    newlist.remove([])
#print newlist
for x in newlist:
    #print batch_upload('xxx','xxx',x)
    for y in x:
        print y