ServiceNow导出XLXS格式：

 

 核心字段：

• Number
• Caller
• Store/Site
• Description
• Severity
• State
• Assignment group
• Assigned to Reported for
• Created
• Opened
• Resolved
• Resolution notes
• Category
• Configuration
• item
• Created by
• Priority
• Comments and Work notes
• PagerDuty incident
• PagerDuty incident id
• GDS

核心处理代码：

from collections import OrderedDict
import json
import codecs
import xlrd
import socket
from datetime import datetime
from xlrd import xldate_as_tuple


wb = xlrd.open_workbook('ServiceNow20211227_20220102.xlsx')

#sock=socket.socket(socket.AF_INET,socket.SOCK_DGRAM)
f = open("ServiceNow.txt", "w", encoding='utf-8')

sh = wb.sheet_by_index(0)
title = sh.row_values(0)
i = 1
for rownum in range(1, sh.nrows):
  rowvalue = sh.row_values(rownum)
  single = OrderedDict()
  for colnum in range(0, len(rowvalue)):
    #print(title[colnum], rowvalue[colnum])
    single[title[colnum]] = rowvalue[colnum]
    if colnum >= 9 and colnum <= 11:
        #print
        if sh.cell_type(rownum, colnum) != 3:
            cellValue = ""
        else:
            cellValue = sh.cell_value(rownum,colnum)
            #print("================转化=================")
            cellValue = xldate_as_tuple(float(cellValue), 0)
            cellValue = datetime(*cellValue).strftime('%Y/%m/%d %H:%M:%S')
        #print(cellValue)
        single[title[colnum]] = cellValue
    else :
        single[title[colnum]] = rowvalue[colnum]
  single = json.dumps(single,ensure_ascii=False)
  #single = single.replace("\'","\"")
  print(single)
  print(i)
  f.writelines(single + "\n")
  i = i + 1
  #print(sh.cell_type(1,9))
  #sock.sendto(single.encode(),("192.168.100.209",10517))
f.close()

生成文本样式：

1 {"Number": "INC2158192", "Caller": "PagerDuty Integration", "Store/Site": "", "Description": "[LINUX]Used disk space is more than 90% on volume /var : PROBLEM for cfdcce4498p_[GDS_P_MiddleLayer]", "Severity": "3 - Low", "State": "Closed", "Assignment group": "China NOC Monitoring", "Assigned to": "Sun Song Zhang", "Reported for": "", "Created": "2021/12/27 00:01:34", "Opened": "2021/12/27 00:01:34", "Resolved": "2021/12/27 00:02:31", "Resolution notes": "Resolved via PagerDuty\n", "Category": "", "Configuration item": "", "Created by": "pagerduty", "Priority": "4 - Low", "Comments and Work notes": "2021-12-30 01:00:03 - System (Work notes)\nPagerDuty error during resolve: Incident Already Resolved\n\n2021-12-27 00:02:31 - PagerDuty Integration (Work notes)\nPagerDuty incident was resolved\n\n2021-12-27 00:02:17 - PagerDuty Integration (Work notes)\nPagerDuty incident was acknowledged by Sun Song Zhang (PSP)\n\n2021-12-27 00:01:34 - PagerDuty Integration (Work notes)\nPagerDuty incident was triggered for nonexistent incident, new incident created\n\n", "PagerDuty incident": "Q2E9JV10D8C1BU", "PagerDuty incident id": "", "GDS": 1.0}
2 {"Number": "INC2158193", "Caller": "PagerDuty Integration", "Store/Site": "", "Description": "[LINUX]Used disk space is more than 90% on volume /var : PROBLEM for cfdcce4508p_[GDS_P_MiddleLayer]", "Severity": "3 - Low", "State": "Closed", "Assignment group": "China NOC Monitoring", "Assigned to": "Sun Song Zhang", "Reported for": "", "Created": "2021/12/27 00:01:35", "Opened": "2021/12/27 00:01:35", "Resolved": "2021/12/27 00:02:40", "Resolution notes": "Resolved via PagerDuty\n", "Category": "", "Configuration item": "", "Created by": "pagerduty", "Priority": "4 - Low", "Comments and Work notes": "2021-12-30 01:00:13 - System (Work notes)\nPagerDuty error during resolve: Incident Already Resolved\n\n2021-12-27 00:02:40 - PagerDuty Integration (Work notes)\nPagerDuty incident was resolved\n\n2021-12-27 00:02:19 - PagerDuty Integration (Work notes)\nPagerDuty incident was acknowledged by Sun Song Zhang (PSP)\n\n2021-12-27 00:01:35 - PagerDuty Integration (Work notes)\nPagerDuty incident was triggered for nonexistent incident, new incident created\n\n", "PagerDuty incident": "Q2TH1E1TQ5P7SK", "PagerDuty incident id": "", "GDS": 1.0}

按照标准json解析即可。