import os
import sys
import datetime

sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'project name'))
os.environ['DJANGO_SETTINGS_MODULE'] = 'project name.settings'
BASE_DIR = os.path.dirname(os.path.abspath(__file__))

import django
django.setup()
from <app name>.models import Ticket
import openpyxl

# 数据导出
def excel_export():
    list_obj = Ticket.objects.all()[1:10]
    ws = openpyxl.Workbook()
    w = ws.create_sheet(index=0)
    ticket_titel = list_obj[0].__dict__

    excel_titel = []

    filed_count = 1
    for field in ticket_titel:
        if field == '_state':
            continue
        w.cell(1, filed_count).value = field
        excel_titel.append(field)
        filed_count += 1

    line_count = 2
    for ticket in list_obj:
        print(ticket.id)
        ticket_dict = ticket.__dict__
        filed_count = 1
        for field in excel_titel:
            try:
                w.cell(line_count, filed_count).value = str(ticket_dict[field])
                filed_count += 1
            except Exception as e:
                print(e)
        line_count += 1

    ws.save("test.xlsx")

# 数据导入
def excel_import():
    ws = openpyxl.load_workbook(filename='~/Downloads/test.xlsx')
    sheetnames = ws.get_sheet_names()
    sheet = ws.get_sheet_by_name(sheetnames[0])
    max_row = sheet.max_row
    max_column = sheet.max_column

    excel_titel = []
    for column in range(1, max_column + 1):
        excel_titel.append(sheet.cell(row=1, column=column).value)

    for row in range(2, max_row + 1):
        ticket = {}
        for column in range(0, max_column):
            ticket.setdefault(excel_titel[int(column)])
            ticket[excel_titel[int(column)]] = sheet.cell(row=row, column=column+1).value
        print(ticket['id'])

        try:
            ticket_new = Ticket.objects.create(
                id=ticket['id']
            )

            if ticket['ip'] and ticket['ip'] != 'None':
                ticket_new.ip = ticket['ip']
            ticket_new.save()
        except Exception as e:
            print(e)