1
 2
 3
 4
 5
 6
 7
 8
 9
10

# -*- coding: utf-8 -*-
from django.utils.translation import gettext_lazy
from rest_framework import serializers


class ExcelSerializer(serializers.Serializer):
    file = serializers.FileField(
        label=gettext_lazy("excel文件"),
        required=True,
    )

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

# -*- coding: utf-8 -*-
import copy
import tempfile
from io import BytesIO
from typing import List

import openpyxl
from django.core.files.uploadedfile import SimpleUploadedFile


class TempExcel:
    """生成一个内存中的临时excel"""

    suffix = ".xlsx"

    def __init__(self, titles: List[str], data: List[List[str]]):
        if len(titles) != len(data[0]):
            raise ValueError("标题和数据的列数不一致")
        self.data = copy.deepcopy(data)
        # 插入标题
        self.data.insert(0, titles)

    def __enter__(self):
        temp_file = tempfile.NamedTemporaryFile(delete=True, suffix=self.suffix)
        # 创建一个工作簿
        wb = openpyxl.Workbook()
        # 获取第一张表
        ws = wb.active
        # 插入数据
        rows = len(self.data)
        lines = len(self.data[0])
        for row in range(rows):
            for col in range(lines):
                ws.cell(row=row + 1, column=col + 1).value = self.data[row][col]
        # 存储到文件
        wb.save(filename=temp_file.name)
        byio = BytesIO(temp_file.read())
        self.file = SimpleUploadedFile(
            name=temp_file.name,
            content=byio.read(),
        )
        temp_file.close()
        return self.file

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.file.close()


if __name__ == "__main__":
    titles = ["MAC", "Accounts"]
    data = [["aa:bb:cc:dd:55:66", "aa,cc"], ["aa:cc:cc:dd:55:66", "bb,cc"]]
    with TempExcel(titles, data) as f:
        test_api(
            {
                "file": f,
            }
        )