使用pandas操作csv和excel

csv

import pandas as pd
import csv

def save_contents(file_path, data_array):
    pd.json_normalize(data_array).to_csv(file_path, index=False, quotechar='\t', quoting=csv.QUOTE_ALL, sep=',')
    print(f"[+] {len(data_array)+1} rows written on {file_path}")


json_data = [
    {'name': 'person2', 'age': 20, 'card_id': '34878347838282', 'number': '84723948724287847', 'country': 'usa'}, 
    {'name': 'person1', 'age': 35, 'card_id': '95864793980408', 'number': '68944837110437831', 'country': 'germany'}
]

save_contents("output.csv", json_data)

excel

import pandas as pd
from pathlib import Path
import json

class ExcelHelper:

    def __init__(self, file_path, sheet_default="Sheet1"):
        self.file_path = file_path
        self.sheet_default = sheet_default

    def add_data_list(self, data_array):
        df_new = pd.DataFrame(data_array)
        if Path(self.file_path).is_file():
            df_old = pd.read_excel(self.file_path, sheet_name=self.sheet_default)
            df_new = pd.concat([df_old, df_new])

        df_new = df_new.astype(str)
        
        with pd.ExcelWriter(self.file_path, mode="w", engine="openpyxl") as fp:
            df_new.to_excel(fp, sheet_name=self.sheet_default, index=False)
            
        print(f'Added new rows {len(data_array)}')
        print(f'Total rows {len(df_new)}')

data_list = [
    {'name': 'person3', 'age': 25, 'fdsf': 34878347838282, 'card_id': '34878347838282', 'country': 'uk', 'fe': '84723948724287847',
     'data': {'a':1,'b':'234'}, 't': {"a":1,"b":"fsdfsdkfl"}, 'z': json.dumps({"a":1,"b":"fsdfsdkfl"})},
    {'name': 'person4', 'age': 32, 'fdsf': 34878347838282, 'card_id': '95864793980408', 'country': 'france', 'fe': '8472394872\n4287847',
     'data': {'a':1,'b':'234'}, 't': {"a":1,"b":"fsdfsdkfl"}, 'z': json.dumps({"a":1,"b":"fsdfsdkfl"})}
]
eh = ExcelHelper('data.xlsx')
eh.add_data_list(data_list)