导出csv文件

最新推荐文章于 2024-09-11 11:42:24 发布

类猿人搬运工

最新推荐文章于 2024-09-11 11:42:24 发布

阅读量210

点赞数 1

文章标签： python 开发语言

本文链接：https://blog.csdn.net/qq_42751918/article/details/140913437

版权

import csv
import time
from io import StringIO, BytesIO
from django.http import HttpResponse
from .models import Boss, Department

# 定义枚举翻译映射
def get_translation_maps():
    return {
        'industry': {status: status.label for status in Boss.Industry},
        'department_type': {status: status.label for status in Department.DepartmentType},
    }

TRANSLATION_MAPS = get_translation_maps()

# 字段名到中文表头的映射
HEADER_TRANSLATIONS = {
    'id': '编号',
    'name': '名字',
    'industry': '行业',
    'department_name': '部门名称',
    'department_type': '部门类型',
}

def translate_value(key, value):
    """翻译字段值"""
    return TRANSLATION_MAPS.get(key, {}).get(value, value)

def translate_row(row):
    """翻译单行数据，包括外键字段的处理"""
    translated_row = {}
    for key, value in row.items():
        if key == 'department__name':  # 外键字段
            translated_row['department_name'] = value
        elif key == 'department__department_type':  # 外键字段
            translated_row['department_type'] = translate_value('department_type', value)
        elif key in TRANSLATION_MAPS:  # 枚举字段
            translated_row[key] = translate_value(key, value)
        else:
            translated_row[key] = value
    return translated_row

def row_generator(queryset):
    """生成器函数，逐行处理数据"""
    for row in queryset:
        yield translate_row(row)

def export_to_csv_without_pandas(request):
    # 查询数据
    queryset = Boss.objects.select_related('department').values('id', 'name', 'industry', 'department__name', 'department__department_type')

    # 导出为 CSV 文件
    start_time = time.time()

    # 使用 StringIO 来处理 CSV 数据
    string_io = StringIO()
    writer = csv.writer(string_io)

    # 处理表头
    try:
        first_row = next(row_generator(queryset))  # 读取一行以确定表头
        # 使用 HEADER_TRANSLATIONS 进行表头翻译
        translated_headers = [HEADER_TRANSLATIONS.get(header, header) for header in first_row.keys()]
        writer.writerow(translated_headers)
        
        # 写入数据
        for row in row_generator(queryset):
            writer.writerow(row.values())
    except StopIteration:
        pass  # 如果查询集为空，不做任何操作

    # 从 StringIO 获取 CSV 数据并转换为字节
    csv_data = string_io.getvalue().encode('utf-8-sig')  # UTF-8 编码并添加 BOM
    string_io.close()

    # 使用 BytesIO 来处理字节流
    output = BytesIO(csv_data)
    time_taken = time.time() - start_time

    # 输出时间
    print(f"Time taken without pandas: {time_taken} seconds")

    # 创建 HttpResponse
    response = HttpResponse(output.getvalue(), content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=my_data.csv'
    return response

优化

import csv
import time
from io import StringIO, BytesIO
from django.http import HttpResponse
from django.core.paginator import Paginator


# 定义枚举翻译映射
def get_translation_maps():
    return {
        'industry': {status: str(status.label) for status in Boss.Industry},
        # 'department_type': {status: status.label for status in Department.DepartmentType},
    }

TRANSLATION_MAPS = get_translation_maps()

# 字段名到中文表头的映射
HEADER_TRANSLATIONS = {
    'id': '编号',
    'name': '名字',
    'industry': '行业',
    'company':'公司',
    # 'department_name': '部门名称',
    # 'department_type': '部门类型',
}

def translate_values(key, value):
    """翻译字段值"""
    return TRANSLATION_MAPS.get(key, {}).get(value, value)

def translate_row(row):
    """翻译单行数据，包括外键字段的处理"""
    translated_row = {}
    for key, value in row.items():
        if key == 'department__name':  # 外键字段
            translated_row['department_name'] = value
        elif key == 'department__department_type':  # 外键字段
            translated_row['department_type'] = translate_values('department_type', value)
        elif key in TRANSLATION_MAPS:  # 枚举字段
            translated_row[key] = translate_values(key, value)
        else:
            translated_row[key] = value
    return translated_row

def export_to_csv_without_pandas(request):
    # 查询数据
    queryset = Boss.objects.values('id', 'name',"company", 'industry')

    # 导出为 CSV 文件
    start_time = time.time()

    # 使用 StringIO 来处理 CSV 数据
    string_io = StringIO()
    writer = csv.writer(string_io)

    # 分页处理数据
    paginator = Paginator(queryset, 50000)  # 每页处理1000条记录

    # 写入表头
    first_page = paginator.page(1)
    first_row = translate_row(first_page.object_list[0])
    translated_headers = [HEADER_TRANSLATIONS.get(header, header) for header in first_row.keys()]
    writer.writerow(translated_headers)

    # 写入数据
    for page_number in paginator.page_range:
        page = paginator.page(page_number)
        for row in page.object_list:
            translated_row = translate_row(row)
            writer.writerow(translated_row.values())

    # 从 StringIO 获取 CSV 数据并转换为字节
    csv_data = string_io.getvalue().encode('utf-8-sig')  # UTF-8 编码并添加 BOM
    string_io.close()

    # 使用 BytesIO 来处理字节流
    output = BytesIO(csv_data)
    time_taken = time.time() - start_time

    # 输出时间
    print(f"Time taken without pandas: {time_taken} seconds")

    # 创建 HttpResponse
    response = HttpResponse(output.getvalue(), content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=my_data.csv'
    return response```