Python Excel格式处理(ZK代码)

客户临时提出更改方案,虽然不算复杂,不过回应了阮一峰先生记录的一句话。
可能也是由于其他原因吧,整个数据对接并不顺利,希望接下来能尽量投入精力扭转目前的项目进度。

一个美国程序员分享自己的工作方法,其中有一条是 “要么不做,要做就做完”
他的意思是,不要给自己留下做了一半的活。因为这意味着你需要再回来,继续把它做完;你会挂念这件事情,它就像一个钟摆,过一段时间就会重新出现在你的脑海,时不时烦扰着你。
你的目标应该是,当天就把这件事情做完,从此不必再去碰它,第二天继续做下一件事就行了。如果遇到一天做不完的大项目,那就把它分解成一个个小步骤,每天完成一个步骤。
我觉得他说得很好,但是这句话比较重要的,其实是前半句:有些事情不要去做。事情是做不完的,而你的时间和精力是有限的,不要只想着如何才能把事情做完,还要学会不做那些不应该做的事情。
某种程度上,不做比做完更困难。因为通常来说,做完一件事,多多少少都有一些好处,不做意味着你要放弃一些眼前的好处,这并不容易。
最糟糕的一种情况是,某个项目不值得做,但是你已经做了,为了不要浪费已经投入的成本,于是你进一步投入,在泥坑里面越陷越深。等到项目最后失败的时候,你大伤元气,一蹶不振。项目管理有一个很重要的原则,叫做”尽快失败”,就是为了防止这种情况。胡适先生原来学农科,专业是果树栽培,他觉得实在没意思,大学读到一半就改学哲学,后来当上了北大校长。如果他没有放弃,想着拿完农科文凭再说,那他大概就不会有以后的成就了。总之,对于那些没希望的项目,放弃得越早越好。

import copy
import datetime
import os
import re
import shutil
import time
import xlrd

from django.contrib.auth.decorators import login_required
from django.http import HttpResponse
from django.shortcuts import render

from xlutils.copy import copy as excel_copy
from educational_affairs import settings
from management_account.forms import FileForm

excel_school = {'XXX大学': 1, 'XXX大学': 2}
excel_tag = {'学期': 1, '校区': 2, '课程': 3, '学院': 4, '专业': 5, '教室': 6, '班级': 7, '学生': 8, '教师': 9, '学生考试': 11, '教师监考': 12, '学生课程': 13, '教师任课': 14, '学期开课': 15, '考试信息': 16}
excel_read_mark = {'1001': 1 , '1002': 1, '1003': 3, '1004': 3, '1005': 3, '1006': 2, '1007': 2, '1008': 2, '1009': 0, '1011': 1, '1012': 1, '1013': 1, '1014': 2, '1015': 1, '1016': 1}
obj_class_grade = obj_class_specialty = obj_class_college = obj_class_name = None # 班级数据
obj_student_grade = obj_student_specialty = obj_student_college = None # 学生数据
obj_teacher_college = None # 教师数据
obj_stuexam_term = obj_stuexam_class = None # 学生考试数据
obj_teaexam_term = obj_teaexam_type = obj_teaexam_course_no = obj_teaexam_course_name = obj_teaexam_classroom = obj_teaexam_exam_date = obj_teaexam_starttime = obj_teaexam_overtime = None # 教师监考
obj_exam_term = obj_exam_type = obj_exam_course_no = obj_exam_course_name = obj_exam_classroom = obj_exam_exam_date = obj_exam_starttime = obj_exam_overtime = None # 教师监考
obj_stucourse_term = None # 学生课程
obj_teacourse_term = obj_teacourse_teacher = None # 教师任课
obj_semester_term = obj_semester_teacher = obj_semester_college = None # 学期开课


class TagsObj(object):
    def __init__(self, school, excel_id):
        '''
        标签
        '''
        self.excel_tag = excel_tag
        self.school = school
        self.excel_id = excel_id
        self.tags = {}
        for k, v in self.excel_tag.items():
            self.tags[k] = self.excel_id*1000 + v


class ExcelBasisObj(object):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        导表父类
        '''
        self.school = school
        self.tag = tag
        self.excel_id = excel_id
        self.start_time = time.time()
        self.over_time = None
        self.send_time = None
        self.status = False
        self.start_mark = excel_read_mark[excel_id]  # Excel开始执行行:默认第2行
        # self.lack = 0 # 缺失数据:默认0条
        self.nonexistent = 0 # 空白行:默认0条
        self.file_head = time_now
        self.file_tail = school+tag+'.xls'
        self.link = self.file_head + self.file_tail
        self.file_name_basis = '%s/%s' % (settings.STATIC_EXCEL, self.file_tail)
        self.excel_file = '%s/%s' % (settings.MEDIA_ROOT, self.link)
        shutil.copyfile(self.file_name_basis, self.excel_file)

    def write_excel(self, excel_content):
        rb = xlrd.open_workbook(self.excel_file)
        wb = excel_copy(rb)
        ws = wb.get_sheet(0)
        mark_link = 1
        for i in excel_content:
            if i:
                if i.excel_id == '1011' or i.excel_id == '1012':
                    for j in i.exam_no:
                        i.write_row(ws, i, mark_link, j)
                        mark_link += 1
                else:
                    i.write_row(ws, i, mark_link)
                    mark_link += 1
        wb.save(self.excel_file)
        self.status = True
        return self.link


class GzzhongkaiTermObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学期
        '''
        super(GzzhongkaiTermObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        term = row[0] if row[0] else None
        semester_time = row[1] if row[1] else None
        self.term = term
        self.semester_time = semester_time
        return self

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.term)
        ws.write(mark_row, 1, label=i.semester_time)


class GzzhongkaiCampusObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        校区
        '''
        super(GzzhongkaiCampusObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        campus_no = row[0] if row[0] else None
        campus_name = row[1] if row[1] else None
        campus_time = row[2] if row[2] else None
        self.campus_no = campus_no
        self.campus_name = campus_name
        self.campus_time = campus_time
        return self

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.campus_no)
        ws.write(mark_row, 1, label=i.campus_name)
        ws.write(mark_row, 2, label=i.campus_time)


class GzzhongkaiCourseObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        课程
        '''
        super(GzzhongkaiCourseObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if '代码' in row[1]:
            pass
        elif '页' in row[1]:
            pass
        else:
            course_no = row[1] if row[1] else None
            course_name = row[2] if row[2] else None
            course_type = row[3] if row[3] else None
            self.course_no = course_no
            self.course_name = course_name
            self.course_type = course_type
            return self

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.course_no)
        ws.write(mark_row, 1, label=i.course_name)
        ws.write(mark_row, 2, label=i.course_type)


class GzzhongkaiCollegeObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学院
        '''
        super(GzzhongkaiCollegeObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if row[1] and row[2]:
            college_no = row[1] if row[1] else None
            college_name = row[2] if row[2] else None
            self.college_no = college_no
            self.college_name = college_name
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.college_no)
        ws.write(mark_row, 1, label=i.college_name)


class GzzhongkaiSpecialtyObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        专业
        '''
        super(GzzhongkaiSpecialtyObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if '页' in row[1]:
            pass
        else:
            specialty_no = row[4] if row[4] else None
            specialty_name = row[5] if row[5] else None
            college_name = row[1] if row[1] else None
            self.specialty_no = specialty_no
            self.specialty_name = specialty_name
            self.college_name = college_name
            return self

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.specialty_no)
        ws.write(mark_row, 1, label=i.specialty_name)
        ws.write(mark_row, 2, label=i.college_name)


class GzzhongkaiClassroomObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        教室
        '''
        super(GzzhongkaiClassroomObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if '校区' in str(row[1]):
            pass
        elif '楼层' in str(row[1]):
            pass
        else:
            classroom_no = row[2] if row[2] else None
            classroom_name = row[3] if row[3] else None
            classroom_ssid = None
            classroom_passwd = None
            classroom_mac = None
            self.classroom_no = classroom_no
            self.classroom_name = classroom_name
            self.classroom_ssid = classroom_ssid
            self.classroom_passwd = classroom_passwd
            self.classroom_mac = classroom_mac
            return self

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.classroom_no)
        ws.write(mark_row, 1, label=i.classroom_name)
        ws.write(mark_row, 2, label=i.classroom_ssid)
        ws.write(mark_row, 3, label=i.classroom_passwd)
        ws.write(mark_row, 4, label=i.classroom_mac)


class GzzhongkaiClassObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        班级
        '''
        super(GzzhongkaiClassObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if row[1] == '年级:':
            global obj_class_grade
            obj_class_grade = row[2]
            global obj_class_college
            obj_class_college = row[6]
        elif row[1] == '专业:':
            global obj_class_specialty
            obj_class_specialty = row[2]
            global obj_class_name
            obj_class_name = row[6].split()[0]
            self.class_no = None
            self.class_name = obj_class_name
            self.class_grade = obj_class_grade
            self.class_college = obj_class_college
            self.class_specialty = obj_class_specialty
            self.class_teacher = None
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.class_no)
        ws.write(mark_row, 1, label=i.class_name)
        ws.write(mark_row, 2, label=i.class_grade)
        ws.write(mark_row, 3, label=i.class_college)
        ws.write(mark_row, 4, label=i.class_specialty)
        ws.write(mark_row, 5, label=i.class_teacher)


class GzzhongkaiStudentObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学生
        '''
        super(GzzhongkaiStudentObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        if row[1] == '年级:':
            global obj_student_college
            obj_student_college = row[6]
        elif row[1] == '专业:':
            global obj_student_specialty
            global obj_student_class
            obj_student_specialty = row[2]
            obj_student_class = row[6].split()[0]
        elif row[1] == '序号':
            pass
        elif row[2] and row[3]:
            self.student_no = row[2]
            self.student_name = row[3]
            self.student_passwd = None
            self.student_college = obj_student_college
            self.student_specialty = obj_student_specialty
            self.student_class = obj_student_class
            self.student_integrity = None
            self.student_face = None
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.student_no)
        ws.write(mark_row, 1, label=i.student_name)
        ws.write(mark_row, 2, label=i.student_passwd)
        ws.write(mark_row, 3, label=i.student_college)
        ws.write(mark_row, 4, label=i.student_specialty)
        ws.write(mark_row, 5, label=i.student_class)
        ws.write(mark_row, 6, label=i.student_integrity)
        ws.write(mark_row, 7, label=i.student_face)


class GzzhongkaiTeacherObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        教师
        '''
        super(GzzhongkaiTeacherObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        teacher_title_head = '******工程学院'
        teacher_title_tail = '教职工信息'

        if teacher_title_head in row[1]:
            global obj_teacher_college
            obj_teacher_college = row[1].split(teacher_title_head)[1].split(teacher_title_tail)[0]
        elif row[2] in '工号':
            pass
        elif row[2]:
            self.teacher_no = row[2]
            self.teacher_name = row[3]
            self.teacher_passwd = None
            self.teacher_email = None
            self.teacher_card_type = row[5]
            self.teacher_class_director = None
            self.teacher_teaching_manage = None
            self.teacher_academic_manage = None
            self.teacher_pic = None
            self.teacher_college = obj_teacher_college
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.teacher_no)
        ws.write(mark_row, 1, label=i.teacher_name)
        ws.write(mark_row, 2, label=i.teacher_passwd)
        ws.write(mark_row, 3, label=i.teacher_email)
        ws.write(mark_row, 4, label=i.teacher_card_type)
        ws.write(mark_row, 5, label=i.teacher_class_director)
        ws.write(mark_row, 6, label=i.teacher_teaching_manage)
        ws.write(mark_row, 7, label=i.teacher_academic_manage)
        ws.write(mark_row, 8, label=i.teacher_pic)
        ws.write(mark_row, 9, label=i.teacher_college)


class GzzhongkaiStudentExamObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学生考试
        '''
        super(GzzhongkaiStudentExamObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):
        str_term = '学期'
        str_class = '行政班级:'
        if str_term in row[1]:
            global obj_stuexam_term
            obj_stuexam_term = row[1].split(str_term)[0] + str_term
        elif str_class in row[4]:
            global obj_stuexam_class
            obj_stuexam_class = row[4].split(str_class)[1].split('人数')[0].strip()
        elif  ']' in row[1]:
            exam_datetime = row[4].split(' ')
            student_list = re.split('[,\n]', row[7])
            str_list = []
            for i in student_list:
                if '-' in i:
                    top_int = int(i.split('-')[0])
                    tail_int = int(i.split('-')[1])
                    for x in list(range(top_int, tail_int + 1)):
                        str_list.append(str(x))
                else:
                    str_list.append(i)
            self.stuexam_term = obj_stuexam_term
            self.stuexam_course = row[1]
            self.exam_no = str_list
            self.stuexam_class = obj_stuexam_class
            self.stuexam_exam_date = exam_datetime[0]
            self.stuexam_starttime = exam_datetime[1].split('-')[0]
            self.stuexam_overtime = exam_datetime[1].split('-')[1]
            self.stuexam_marktime = None
            self.stuexam_classroom = row[5]
            self.stuexam_type = None
            self.stuexam_status = None
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row, exam_no):
        ws.write(mark_row, 0, label=i.stuexam_term)
        ws.write(mark_row, 1, label=i.stuexam_course)
        ws.write(mark_row, 2, label=exam_no)
        ws.write(mark_row, 3, label=i.stuexam_class)
        ws.write(mark_row, 4, label=i.stuexam_exam_date)
        ws.write(mark_row, 5, label=i.stuexam_starttime)
        ws.write(mark_row, 6, label=i.stuexam_overtime)
        ws.write(mark_row, 7, label=i.stuexam_marktime)
        ws.write(mark_row, 8, label=i.stuexam_classroom)
        ws.write(mark_row, 9, label=i.stuexam_type)
        ws.write(mark_row, 10, label=i.stuexam_status)


class GzzhongkaiTeacherExamObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        教师监考
        '''
        super(GzzhongkaiTeacherExamObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):

            teaexam_class = None
            str_term = '学期'
            str_list = None
            if str_term in row[1]:
                global obj_teaexam_term
                global obj_teaexam_type
                obj_teaexam_term = row[1].split(str_term)[0] + str_term
                obj_teaexam_type = row[1].split(str_term)[1]
            elif '考试时间' == row[1]:
                pass
            elif '承担单位' in row[1]:
                pass
            elif row[7]:
                if '-' in row[1]:
                    global obj_teaexam_exam_date
                    global obj_teaexam_starttime
                    global obj_teaexam_overtime
                    exam_datetime = row[1].split(' ')
                    obj_teaexam_exam_date = exam_datetime[0].split('(')[0]
                    exam_time_list = exam_datetime[2].split('-')
                    obj_teaexam_starttime = exam_time_list[0]
                    obj_teaexam_overtime = exam_time_list[1]

                if '[' in row[3]:
                    course = row[3].split('[')[1].split(']')
                    global obj_teaexam_course_no
                    global obj_teaexam_course_name
                    obj_teaexam_course_no = course[0]
                    obj_teaexam_course_name = course[1]

                if ' ' in row[6] and row[6]:
                    global obj_teaexam_classroom
                    obj_teaexam_classroom = row[6]

                if row[8]:
                    teaexam_class = row[8]

                if row[10] or row[11]:
                    str_row10_11 = (row[10] + row[11]).replace(']', '-')
                    str_list = str_row10_11.split('[')[1:]
                    self.teaexam_term = obj_teaexam_term
                    self.teaexam_course_no = obj_teaexam_course_no
                    self.teaexam_course_name = obj_teaexam_course_name
                    self.exam_no = str_list
                    self.teaexam_class = teaexam_class
                    self.teaexam_exam_date = obj_teaexam_exam_date
                    self.teaexam_starttime = obj_teaexam_starttime
                    self.teaexam_overtime = obj_teaexam_overtime
                    self.teaexam_marktime = None
                    self.teaexam_classroom = obj_teaexam_classroom
                    self.teaexam_type = obj_teaexam_type
                    self.teaexam_status = None
                    return self
                else:
                    pass

    def write_row(self, ws, i, mark_row, exam_no):
        ws.write(mark_row, 0, label=i.teaexam_term)
        ws.write(mark_row, 1, label=i.teaexam_course_no)
        ws.write(mark_row, 2, label=i.teaexam_course_name)
        ws.write(mark_row, 3, label=exam_no)
        ws.write(mark_row, 4, label=i.teaexam_class)
        ws.write(mark_row, 5, label=i.teaexam_exam_date)
        ws.write(mark_row, 6, label=i.teaexam_starttime)
        ws.write(mark_row, 7, label=i.teaexam_overtime)
        ws.write(mark_row, 8, label=i.teaexam_marktime)
        ws.write(mark_row, 9, label=i.teaexam_classroom)
        ws.write(mark_row, 10, label=i.teaexam_type)
        ws.write(mark_row, 11, label=i.teaexam_status)


class GzzhongkaiStudentCourseObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学生课程
        '''
        super(GzzhongkaiStudentCourseObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):

        if '学期' in row[1]:
            global obj_stucourse_term
            obj_stucourse_term = row[1]

        elif '[' in row[1]:
            stucourse_course = row[1]
            stucourse_no = row[8]
            self.stucourse_term = obj_stucourse_term
            self.stucourse_course = stucourse_course
            self.stucourse_no = stucourse_no
            self.stucourse_classroom = None
            self.stucourse_cycle = None
            self.stucourse_week = None
            self.stucourse_node = None
            self.stucourse_type = None
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.stucourse_term)
        ws.write(mark_row, 1, label=i.stucourse_course)
        ws.write(mark_row, 2, label=i.stucourse_no)
        ws.write(mark_row, 3, label=i.stucourse_classroom)
        ws.write(mark_row, 4, label=i.stucourse_cycle)
        ws.write(mark_row, 5, label=i.stucourse_week)
        ws.write(mark_row, 6, label=i.stucourse_node)
        ws.write(mark_row, 7, label=i.stucourse_type)


class GzzhongkaiTeacherCourseObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        教师任课
        '''
        super(GzzhongkaiTeacherCourseObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):

        if '学期' in row[1]:
            global obj_teacourse_term
            obj_teacourse_term = row[1]

        elif '[' in str(row[9]):
            global obj_teacourse_teacher
            obj_teacourse_teacher = row[9]

        elif '[' in row[1]:
            course = row[1].split('[')[1].split(']')
            teacourse_course_no = course[0]
            teacourse_course_name = course[1]
            if row[5]:
                teacourse_teach_num = row[6]
            if row[12]:
                teacourse_people = row[13]
            if row[16]:
                teacourse_course_type  = row[16]
            if row[18]:
                teacourse_cycle = row[18]
            if row[20]:
                teacourse_week = row[20].split('[')[0]
                teacourse_node = row[20].split('[')[1].split(']')[0]
            if row[21]:
                teacourse_classroom = row[21]
            else:
                teacourse_classroom = None
            self.teacourse_term = obj_teacourse_term
            self.teacourse_course_no = teacourse_course_no
            self.teacourse_course_name = teacourse_course_name
            self.teacourse_teacher = obj_teacourse_teacher
            self.teacourse_classroom = teacourse_classroom
            self.teacourse_cycle = teacourse_cycle
            self.teacourse_week = teacourse_week
            self.teacourse_node = teacourse_node
            self.teacourse_people = teacourse_people
            self.teacourse_course_type = teacourse_course_type
            self.teacourse_teach_num = teacourse_teach_num
            return self

        elif '[' in row[3]:
            teacourse_cycle = row[2]
            teacourse_week = row[3].split('[')[0]
            teacourse_node = row[3].split('[')[1].split(']')[0]
            teacourse_course_name = row[4]
            teacourse_people = row[22]
            teacourse_course_no = None
            teacourse_classroom = None
            teacourse_course_type = None
            teacourse_teach_num = None
            self.teacourse_term = obj_teacourse_term
            self.teacourse_course_no = teacourse_course_no
            self.teacourse_course_name = teacourse_course_name
            self.teacourse_teacher = obj_teacourse_teacher
            self.teacourse_classroom = teacourse_classroom
            self.teacourse_cycle = teacourse_cycle
            self.teacourse_week = teacourse_week
            self.teacourse_node = teacourse_node
            self.teacourse_people = teacourse_people
            self.teacourse_course_type = teacourse_course_type
            self.teacourse_teach_num = teacourse_teach_num
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.teacourse_term)
        ws.write(mark_row, 1, label=i.teacourse_course_no)
        ws.write(mark_row, 2, label=i.teacourse_course_name)
        ws.write(mark_row, 3, label=i.teacourse_teacher)
        ws.write(mark_row, 4, label=i.teacourse_classroom)
        ws.write(mark_row, 5, label=i.teacourse_cycle)
        ws.write(mark_row, 6, label=i.teacourse_week)
        ws.write(mark_row, 7, label=i.teacourse_node)
        ws.write(mark_row, 8, label=i.teacourse_people)
        ws.write(mark_row, 9, label=i.teacourse_course_type)
        ws.write(mark_row, 10, label=i.teacourse_teach_num)


class GzzhongkaiSemesterCourseObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        学期开课
        '''
        super(GzzhongkaiSemesterCourseObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):

        if '部门' in row[1]:
            global obj_semester_college
            obj_semester_college = row[1].split('学院')[0]+'学院'
            obj_semester_college = obj_semester_college.split('部门:')[1]

        elif '学期' in row[1]:
            global obj_semester_term
            obj_semester_term = row[1]

        elif '[' in str(row[9]):
            global obj_semester_teacher
            obj_semester_teacher = row[9]

        elif '[' in row[1]:
            course = row[1].split('[')[1].split(']')
            semester_course_no = course[0]
            semester_course_name = course[1]
            if row[12]:
                semester_people = row[13]
            if row[16]:
                semester_course_type  = row[16]
            if row[17]:
                semester_class = row[17]
            else:
                semester_class = None
            if row[18]:
                semester_cycle = row[18]
            if row[20]:
                semester_week = row[20].split('[')[0]
                semester_node = row[20].split('[')[1].split(']')[0]
            if row[21]:
                semester_classroom = row[21]
            else:
                semester_classroom = None
            self.semester_term = obj_semester_term
            self.semester_college = obj_semester_college
            self.semester_course_no = semester_course_no
            self.semester_course_name = semester_course_name
            self.semester_teacher = obj_semester_teacher
            self.semester_classroom = semester_classroom
            self.semester_class = semester_class
            self.semester_cycle = semester_cycle
            self.semester_week = semester_week
            self.semester_node = semester_node
            self.semester_people = semester_people
            self.semester_course_type = semester_course_type
            return self

        elif '[' in row[3]:
            semester_cycle = row[2]
            semester_week = row[3].split('[')[0]
            semester_node = row[3].split('[')[1].split(']')[0]
            semester_course_name = row[4]
            semester_people = row[22]
            semester_course_no = None
            semester_classroom = None
            semester_course_type = None
            semester_class = None
            self.semester_term = obj_semester_term
            self.semester_college = obj_semester_college
            self.semester_course_no = semester_course_no
            self.semester_course_name = semester_course_name
            self.semester_class = semester_class
            self.semester_teacher = obj_semester_teacher
            self.semester_classroom = semester_classroom
            self.semester_cycle = semester_cycle
            self.semester_week = semester_week
            self.semester_node = semester_node
            self.semester_people = semester_people
            self.semester_course_type = semester_course_type
            return self
        else:
            pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.semester_term)
        ws.write(mark_row, 1, label=i.semester_college)
        ws.write(mark_row, 2, label=i.semester_course_no)
        ws.write(mark_row, 3, label=i.semester_course_name)
        ws.write(mark_row, 4, label=i.semester_class)
        ws.write(mark_row, 5, label=i.semester_teacher)
        ws.write(mark_row, 6, label=i.semester_classroom)
        ws.write(mark_row, 7, label=i.semester_cycle)
        ws.write(mark_row, 8, label=i.semester_week)
        ws.write(mark_row, 9, label=i.semester_node)
        ws.write(mark_row, 10, label=i.semester_people)
        ws.write(mark_row, 11, label=i.semester_course_type)


class GzzhongkaiExamObj(ExcelBasisObj):
    def __init__(self, school, tag, excel_id, time_now):
        '''
        考试信息
        '''
        super(GzzhongkaiExamObj, self).__init__(school, tag, excel_id, time_now)

    def get_row(self, row=None):

            exam_class = exam_student_num = exam_teacher = None
            str_term = '学期'
            if str_term in row[1]:
                global obj_exam_term
                global obj_exam_type
                obj_exam_term = row[1].split(str_term)[0] + str_term
                obj_exam_type = row[1].split(str_term)[1]
            elif '考试时间' == row[1]:
                pass
            elif '承担单位' in row[1]:
                pass
            elif row[7]:
                if '-' in row[1]:
                    global obj_exam_exam_date
                    global obj_exam_starttime
                    global obj_exam_overtime
                    exam_datetime = row[1].split(' ')
                    obj_exam_exam_date = exam_datetime[0].split('(')[0]
                    exam_time_list = exam_datetime[2].split('-')
                    obj_exam_starttime = exam_time_list[0]
                    obj_exam_overtime = exam_time_list[1]
                if '[' in row[3]:
                    course = row[3].split('[')[1].split(']')
                    global obj_exam_course_no
                    global obj_exam_course_name
                    obj_exam_course_no = course[0]
                    obj_exam_course_name = course[1]
                if ' ' in row[6] and row[6]:
                    global obj_exam_classroom
                    obj_exam_classroom = row[6]
                if row[8]:
                    exam_class = row[8]
                if row[9]:
                    exam_student_num = row[9]
                if row[10] or row[11]:
                    exam_teacher = row[10] + row[11]
                self.exam_term = obj_exam_term
                self.exam_course_no = obj_exam_course_no
                self.exam_course_name = obj_exam_course_name
                self.exam_teacher = exam_teacher
                self.exam_class = exam_class
                self.exam_student_num = exam_student_num
                self.exam_exam_date = obj_exam_exam_date
                self.exam_starttime = obj_exam_starttime
                self.exam_overtime = obj_exam_overtime
                self.exam_classroom = obj_exam_classroom
                self.exam_type = obj_exam_type
                return self
            else:
                pass

    def write_row(self, ws, i, mark_row):
        ws.write(mark_row, 0, label=i.exam_term)
        ws.write(mark_row, 1, label=i.exam_course_no)
        ws.write(mark_row, 2, label=i.exam_course_name)
        ws.write(mark_row, 3, label=i.exam_teacher)
        ws.write(mark_row, 4, label=i.exam_class)
        ws.write(mark_row, 5, label=i.exam_student_num)
        ws.write(mark_row, 6, label=i.exam_exam_date)
        ws.write(mark_row, 7, label=i.exam_starttime)
        ws.write(mark_row, 8, label=i.exam_overtime)
        ws.write(mark_row, 9, label=i.exam_classroom)
        ws.write(mark_row, 10, label=i.exam_type)


@login_required
def index(request):
    """
    Excel格式转换
    """
    lists = []

    for k, v in excel_school.items():
        obj = TagsObj(k, v)
        lists.append(obj)

    return render(request, 'deal_excel_index.html', {'lists': lists})


def file_import(request, excel_file, school, tag, excel_id, time_now):
    '''
    Excel对象匹配
    '''
    excel_tag = {'1001': GzzhongkaiTermObj(school, tag, excel_id, time_now),
                 '1002': GzzhongkaiCampusObj(school, tag, excel_id, time_now),
                 '1003': GzzhongkaiCourseObj(school, tag, excel_id, time_now),
                 '1004': GzzhongkaiCollegeObj(school, tag, excel_id, time_now),
                 '1005': GzzhongkaiSpecialtyObj(school, tag, excel_id, time_now),
                 '1007': GzzhongkaiClassObj(school, tag, excel_id, time_now),
                 '1008': GzzhongkaiStudentObj(school, tag, excel_id, time_now),
                 '1009': GzzhongkaiTeacherObj(school, tag, excel_id, time_now),
                 '1011': GzzhongkaiStudentExamObj(school, tag, excel_id, time_now),
                 '1012': GzzhongkaiTeacherExamObj(school, tag, excel_id, time_now),
                 '1013': GzzhongkaiStudentCourseObj(school, tag, excel_id, time_now),
                 '1014': GzzhongkaiTeacherCourseObj(school, tag, excel_id, time_now),
                 '1015': GzzhongkaiSemesterCourseObj(school, tag, excel_id, time_now),
                 '1016': GzzhongkaiExamObj(school, tag, excel_id, time_now),
                 '1006': GzzhongkaiClassroomObj(school, tag, excel_id, time_now)}

    if excel_id in excel_tag:
        ExcelBasisStatus = excel_tag[excel_id]
        with xlrd.open_workbook(excel_file) as data:
            table = data.sheet_by_index(0)
            ExcelBasisStatus.start_time
            n = ExcelBasisStatus.start_mark
            WorkList = []

            for line in range(n, table.nrows):
                row = table.row_values(line)
                if row:
                    ExcelBasisCopy = copy.deepcopy(ExcelBasisStatus)
                    WorkList.append(ExcelBasisCopy.get_row(row))
                else:
                    ExcelBasisStatus.nonexistent += 1

            excel_file_dowm = ExcelBasisStatus.write_excel(WorkList)
            operating_status = '成功转换(%s-%s)Excel格式!' %(school, tag)

    else:
        operating_status = '暂不支持(%s-%s)Excel格式转换!' %(school, tag)
    return render(request, 'excel_down.html', {'excel_file_down': excel_file_dowm, 'operating_status': operating_status, 'io_status': ExcelBasisStatus.status})


def remove_file(excel_file):
    if os.path.exists(excel_file):
        os.remove(excel_file)
    else:
        pass


@login_required
def excel_excel(request, school, tag, excel_id):
    '''
    excel格式转换
    :param request:
    :param school:
    :param tag:
    :param excel_id:
    :return:
    '''
    excel_open = ['1003', '1004', '1005', '1006', '1007', '1008', '1009', '1010', '1011', '1012', '1013', '1014', '1015', '1016']

    if request.method == 'POST':
        form = FileForm(request.POST, request.FILES)
        if form.is_valid():
            if excel_id in excel_open:
                excel_excel = request.FILES['file']
                time_now = datetime.datetime.now().strftime("%H%M%s")
                excel_excel_name = '%s/%s' % (settings.MEDIA_ROOT, time_now+str(excel_excel.name))
                with open(excel_excel_name, 'wb') as f:
                    for i in excel_excel.chunks():
                        f.write(i)
                operating_status = file_import(request, excel_excel_name, school, tag, excel_id, time_now)
                remove_file(excel_excel_name)
                return HttpResponse(operating_status)
            else:
                e = '暂不支持(%s-%s)Excel格式转换!' %(school, tag)
                return HttpResponse(e)
        else:
            return HttpResponse("导入失败,请选择有效excel文件!")
    else:
        form = FileForm()
        return render(request, 'deal_excel_remove.html', {'form': form})
点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Title - Artist
0:00