Python将API返回JSON数据导出至Excel：解决filters字段拆分至独立列的问题

Got it, let's adjust your Python code to extract those nested filters fields into separate Excel columns. Here's how to do it step by step:

Key Changes Needed: #

• Expand your header labels to include the new filter-related columns
• Extract field, comparator, and value from the filters array (your sample data has one filter per entry, so we'll handle that case first—we'll also add a note for multiple filters)
• Update the records list to include these new values
• Adjust the Excel range to account for the extra columns

Modified Full Code #

import win32com.client as win32

# Your sample JSON response
response={ 
    "result": [ 
        { 
            "id": "1000", 
            "title": "Fishing Team View", 
            "sharedWithOrganization": True, 
            "ownerId": "324425", 
            "sharedWithUsers": ["1223","w2qee3"], 
            "filters": [ { "field": "tag5", "comparator": "==", "value": "fishing" } ] 
        }, 
        { 
            "id": "2000", 
            "title": "Farming Team View", 
            "sharedWithOrganization": False, 
            "ownerId": "00000", 
            "sharedWithUsers": [ "00000", "11111" ], 
            "filters": [ { "field": "tag5", "comparator": "!@", "value": "farming" } ] 
        } 
    ] 
}

records=[]
for data in response['result']:
    id = data['id']
    title = data['title']
    sharedWithOrganization = data['sharedWithOrganization']
    ownerId = data['ownerId']
    sharedWithUsers = '|'.join(data['sharedWithUsers'])
    
    # Extract filter fields (handle empty filters array to avoid errors)
    filter_field = ""
    filter_comparator = ""
    filter_value = ""
    if data.get('filters') and len(data['filters']) > 0:
        first_filter = data['filters'][0]
        filter_field = first_filter.get('field', "")
        filter_comparator = first_filter.get('comparator', "")
        filter_value = first_filter.get('value', "")
    
    # Add the new filter columns to the record
    records.append([id, title, sharedWithOrganization, ownerId, sharedWithUsers, filter_field, filter_comparator, filter_value])

# Initialize Excel
ExcelApp = win32.Dispatch('Excel.Application')
ExcelApp.Visible= True

# Create workbook and rename sheet
wb = ExcelApp.Workbooks.Add()
ws= wb.Worksheets(1)
ws.Name="Get_Views"

# Updated header labels with filter columns
header_labels=('Id','Title','SharedWithOrganization','OwnerId','sharedWithUsers','Filter Field','Filter Comparator','Filter Value')
for index,val in enumerate(header_labels):
    ws.Cells(1, index+1).Value=val

row_tracker = 2
column_size = len(header_labels)
for row in records:
    ws.Range(ws.Cells(row_tracker,1), ws.Cells(row_tracker,column_size)).Value = row
    row_tracker +=1

Notes for Edge Cases: #

• If some entries have multiple filters in the filters array, you could join the values with a separator (like |) instead of taking just the first one. For example:

  filter_fields = '|'.join([f.get('field', "") for f in data['filters']])
  filter_comparators = '|'.join([f.get('comparator', "") for f in data['filters']])
  filter_values = '|'.join([f.get('value', "") for f in data['filters']])
  

• We added checks for empty filters arrays to avoid errors if any entries don't have filters defined.

内容的提问来源于stack exchange，提问作者kakaji